# Database migrations (Alembic)

## Overview

A migration is a versioned, ordered change to the database schema — adding a column, renaming a table, changing a type. Each change lives in a Python file under `backend/alembic/versions/`. Alembic records applied migrations in an `alembic_version` table inside Postgres and only runs files that have not yet been applied.

Before Alembic was introduced, the schema was bootstrapped via `Base.metadata.create_all()` in `backend/app/main.py`, which only creates *missing tables* — it never alters existing ones. Every column change required a manual `ALTER TABLE`. Alembic replaces that workflow: schema changes become versioned, reversible, and reproducible across environments.

## Current setup

- `backend/alembic.ini` contains no hardcoded URL; `backend/alembic/env.py` injects `DATABASE_URL` from the container's environment.
- The live production DB is stamped at the latest revision in `backend/alembic/versions/`.
- `Base.metadata.create_all()` is still called at startup as a safety net for fresh deploys. Do **not** remove it without first generating a complete baseline migration from the current live schema.

## Workflow

```bash
# Current revision / available heads
docker compose exec backend alembic current
docker compose exec backend alembic heads

# Create a new migration (auto-diffs SQLAlchemy models against the live DB)
docker compose exec backend alembic revision --autogenerate -m "add some column"
# Review the generated file under backend/alembic/versions/ BEFORE applying

# Apply pending migrations
docker compose exec backend alembic upgrade head

# Roll back the last migration
docker compose exec backend alembic downgrade -1

# Mark the DB as being at a revision without running anything (use with care)
docker compose exec backend alembic stamp <revision>
```

After adding a migration in development, rebuild the backend image so the new file is baked into the container:

```bash
docker compose build backend celery
docker compose up -d backend celery --force-recreate
```

## When to write a migration

Any schema change:
- New column, dropped column, renamed field
- New table, dropped table
- Altered index
- New foreign key
- Changed nullability or default value

Always: edit model → generate migration → review → apply → commit both files together.

## Gotchas

- Migrations run inside a transaction. A failed migration rolls back cleanly, so the DB stays consistent.
- `--autogenerate` does not catch everything. The following must be hand-edited into the generated file:
  - `server_default` changes
  - CHECK constraints
  - Enum value additions
  - Data migrations (moving rows around as part of a schema change)
- The `alembic_version` table should only ever have one row. Multiple rows indicate branched heads — run `alembic merge` to reconcile.
- Never edit a migration after it has been applied to a shared environment — write a new migration instead.

## Rollback patterns

```bash
# Undo the last migration
docker compose exec backend alembic downgrade -1

# Jump to a specific revision (by hash prefix)
docker compose exec backend alembic downgrade 9bac7bf02e38

# Jump all the way back to an empty schema (rarely desired in production)
docker compose exec backend alembic downgrade base
```