ADR-0006: Migrations and backfills are deploy-safe by construction

​

Context

• Locks. A blocking ALTER or an unbatched UPDATE on a hot table takes an ACCESS EXCLUSIVE lock, which can stall live API traffic until it finishes.
• Deploy order. The API deploys before the workers. If a migration ships in the same PR as the code that needs it, that code can go live before its schema exists.

​

Decision

• Generate migrations with alembic revision --autogenerate. Every migration inherits SET LOCAL lock_timeout = '5s' from the template. Raise it for a busy table.
• Add a NOT NULL column across separate PRs: add it nullable, backfill with a batched script (run_batched_update), then enforce NOT NULL.
• The enforce migration runs an unconditional UPDATE ... WHERE col IS NULL immediately before SET NOT NULL. The batched script does the real work first, so by the time the migration runs this UPDATE matches nothing and rewrites nothing.
• Keep migration PRs isolated from application code.

def upgrade() -> None:
    op.execute("UPDATE organizations SET sso_enforced = false WHERE sso_enforced IS NULL")
    op.alter_column("organizations", "sso_enforced", nullable=False)

​

Consequences

• A lock-holding migration fails fast at the lock timeout instead of taking the database down.
• Large backfills run outside the deploy path, in controlled batches.
• Code never ships ahead of the schema it needs.
• CI enforces this: a “Migration Isolation Check” gates which files travel together, and alembic check catches model-versus-migration drift.
• The batched script stays mandatory. The migration’s UPDATE is only cheap because the script has already left it nothing to do.

​

Alternatives considered

• Add NOT NULL in one step and let the migration’s UPDATE backfill every row: rewrites the table under lock during deploy. The batched script runs first precisely so the migration’s UPDATE has nothing left to do.
• Ship a migration in the same PR as the code that depends on it: breaks the API-before-workers ordering.

​

References

• server/migrations/script.py.mako, server/migrations/env.py, server/migrations/README.md.
• server/scripts/helper.py (run_batched_update) and the server/scripts/backfill_*.py scripts. CI: .github/workflows/test_server.yaml (migration-check).