Skip to content

Database Migrations

App-name uses SQLModel models as the source of truth for the database schema and Alembic to version and apply changes to PostgreSQL (Supabase). You edit a model, autogenerate a migration, review it, and apply it with alembic upgrade head. This page explains the model; the canonical, step-by-step procedure is the /db-migration skill.

Use the /db-migration skill

Whenever the schema changes — a new column, a new table, any edit under backend/app/models/ — run the /db-migration agent workflow. It walks the full safe sequence: update the model, autogenerate, review, apply, downgrade-test, then sync the frontend types. See Agent Workflows.

Where the schema lives

SQLModel models are the single source of truth — you never hand-write the schema in raw SQL. Editing a model and autogenerating a revision produces an Alembic migration script that Alembic tracks and applies in order.

Path Purpose
backend/app/models/*.py SQLModel table definitions — source of truth for the schema
backend/app/schemas/*.py Pydantic request/response schemas (update when field shapes change)
backend/alembic/versions/ Generated Alembic migration scripts, each with upgrade() / downgrade()
backend/alembic.ini, backend/alembic/env.py Alembic configuration and metadata/connection wiring

Note

Treat files under backend/alembic/versions/ as committed artifacts. They are reviewed, version-controlled, and applied verbatim in every environment — never edit a migration that has already been merged and deployed.

The migration lifecycle

flowchart LR
    A[Edit backend/app/models/*.py] --> B[alembic revision --autogenerate]
    B --> C[Review script in alembic/versions/]
    C --> D[alembic upgrade head<br/>local DB]
    D --> E[Downgrade test:<br/>alembic downgrade -1 && upgrade head]
    E --> F[Open PR — reviewer<br/>inspects the migration]
    F --> G[Merge to main]
    G --> H[Deploy: alembic upgrade head<br/>in the backend container]
    H --> I[(Test / Production DB)]

Generating a migration

After editing a model in backend/app/models/, autogenerate a revision:

cd backend
alembic revision --autogenerate -m "describe your change here"

Alembic diffs the SQLModel metadata against the current database and writes a new script into backend/alembic/versions/ containing an upgrade() and a downgrade(). Open and read it before committing — autogenerate is a starting point, not the final word.

Review the autogenerated migration carefully

  • Confirm there are no unexpected DROP statements — autogenerate can misread a rename as drop-then-create and silently lose data.
  • Scrutinise destructive or locking operations: column drops, type narrowing, and new NOT NULL columns without a default on populated tables.
  • Add IF NOT EXISTS / IF EXISTS guards to table, column, and index operations so the migration is idempotent.
  • Wrap complex constraint logic in DO $$ ... $$ blocks.

Tip

Keep each migration small and focused. A revision that renames a column and backfills data and adds a constraint is hard to review and hard to downgrade. Split them.

Applying migrations

Command When Target
alembic upgrade head Local development Your local Supabase database
alembic upgrade head (in the backend container) Deploy / CI Test or production Supabase (env-driven connection)

Locally

Spin up the database from Supabase (Local), then apply pending migrations:

cd backend
alembic upgrade head

Always test the downgrade too — a migration whose downgrade() is broken cannot be cleanly reverted:

cd backend
alembic downgrade -1 && alembic upgrade head

During deploy

You do not run migrations by hand against test or production. The deploy pipeline applies them inside the backend container — using the exact dependencies and connection config the running app uses — before the new version serves traffic:

docker compose run --rm app alembic upgrade head

This runs on every deploy against the environment's configured Supabase database. See Deploy to Test, Deploy to Production, and Supabase (Hosted).

Pooler vs. direct connection

Supabase exposes two connection paths, and migrations must use the direct connection.

Connection Port Use for
Transaction pooler 6543 The running application's normal query traffic (DATABASE_URL)
Direct connection 5432 Migrations and any DDL (DATABASE_URL_DIRECT)

The transaction pooler (PgBouncer in transaction mode) does not support the session-level state and prepared statements migrations rely on, so DDL run through it can fail or behave unpredictably. Point Alembic (via sqlalchemy.url in backend/alembic/env.py) at the direct 5432 connection through DATABASE_URL_DIRECT; let the app use the 6543 pooler at runtime. See the Env Var Matrix.

Warning

A connection string ending in :6543 with pgbouncer=true is the pooler. Using it for migrations is a common cause of mysterious "prepared statement" and "unsupported startup parameter" failures. Always migrate over :5432.

Reviewing migrations in PRs

Because the migration script is committed, every change is reviewable. When reviewing (or opening) a PR that touches backend/alembic/versions/:

  • Read the migration script, not just the model diff — the upgrade() body is what actually runs.
  • Flag destructive operations: DROP COLUMN, DROP TABLE, narrowing type changes, and new NOT NULL columns without defaults on populated tables.
  • Confirm idempotency guards (IF NOT EXISTS) are present where the change touches tables, columns, or indexes.
  • Confirm the change is additive where possible, so rolling the app back doesn't immediately break against the new schema.
  • Confirm downgrade() is implemented and was tested (alembic downgrade -1).
  • Check that exactly one new revision file was added and its down_revision chains correctly onto the previous head — multiple or re-pointed heads signal a rebase or generation mistake.

Rollback

Unlike forward-only tools, Alembic generates a downgrade() for each revision, so a clean revert is usually possible:

cd backend
alembic downgrade -1          # step back one revision
# or target a specific revision:
alembic downgrade <revision>

For production, prefer these in order:

  1. Step down with a tested downgrade() — only when the downgrade was verified and is non-destructive. Confirm a recent backup exists first.
  2. Write a corrective forward migration — edit the model to the desired state, autogenerate, review, and deploy forward. This keeps history linear and auditable and is the safest path for changes that already shipped.
  3. Restore from a dump — if a migration corrupted or destroyed data, restore from a backup. See Dump & Load and Supabase (Hosted) for managed point-in-time recovery.

For coordinating a rollback with an app deploy — ordering, comms, incident steps — see Rollback & Incidents.

Danger

Migrations run automatically on deploy, so a destructive migration can drop production data the moment it ships. A downgrade() does not bring back rows that a DROP already deleted. Review destructive DDL carefully, prefer additive changes, and confirm a recent backup exists before deploying a migration that drops or rewrites data.

After the schema changes

Two follow-ups keep the rest of the stack in sync — both are part of the /db-migration flow:

  • Update Pydantic schemas in backend/app/schemas/ so request/response shapes include the new fields.
  • Regenerate the frontend API types with the /openapi-sync workflow, which rewrites frontend/src/types/api.ts from the updated backend OpenAPI spec so the frontend stays type-safe against the new schema.