Skip to content

Migrations & schema

Schema changes are managed with Alembic. Migrations are append-only history — once a migration is committed, you never edit it; you write a new one.

The workflow

From backend/:

# 1. change the ORM models, then generate a migration
uv run alembic revision --autogenerate -m "<description>"

# 2. READ the generated migration before applying it (see the gotcha below)

# 3. apply it
uv run alembic upgrade head

Alembic's env.py points at the ORM metadata and runs with compare_type and compare_server_default on, so autogenerate notices column-type and default drift as well as added/removed columns.

The native_enum=False gotcha — read this before generating a migration

Several columns (task.status, the membership role, the audit action, the user's dark-mode preference) are SQLAlchemy Enums declared native_enum=False. That stores the value as a varchar guarded by a CHECK constraint, rather than a PostgreSQL ENUM type.

Alembic autogenerate silently drops those CHECK constraints. A generated migration will happily omit the constraint, and nothing fails loudly — you just end up with an unconstrained column. So:

  • Always read the generated migration, don't trust it blindly.
  • Hand-write the CHECK with op.create_check_constraint(...), naming it to match the Enum(name="…") so the ORM and the database agree on the constraint's identity.

Existing migrations follow this pattern and are the reference to copy from. This is the single most common way to get a migration subtly wrong here.

Verify what actually landed

Autogenerate produces an artefact; it isn't the truth. After applying, inspect the live schema rather than trusting the generated file — list the table, check the constraints, confirm the column types. A CHECK that autogenerate dropped is invisible until something depends on it. Reviewing at this point is cheap; reworking after data depends on the shape is not.

Applying migrations in production

In production, migrations are run manually, not from the deploy pipeline. At pilot scale a migration runs rarely, and the safety case for a human reviewing the plan before it touches the production database outweighs the automation. The operator previews the SQL (alembic upgrade head --sql), then applies it over an authenticated shell on the App Service. The detailed operational procedure lives in the operator's internal runbook (not part of these public docs).

The deploy-time migration gate

Because code and schema deploy on separate tracks, the pipeline guards against shipping code that's ahead of its migration:

  • GET /health reports the live Alembic revision (read straight from alembic_version).
  • The deploy compares that to the head revision the code being shipped expects (read from the migration files).
  • If they differ, the deploy is blocked with an instruction to apply the migration first.

This caught a real failure mode where code shipped cleanly but 500ed on every query because its table didn't exist yet. The /health/data probe (Architecture) is the post-deploy half of the same safety net.

Rolling back

Migrations are written to be reversible, and the test suite exercises a full downgrade base → upgrade head round-trip so a rollback path is always known-good. For a genuinely destructive migration, the operator runs a backup drill first — again, operational detail that lives in the runbook.