Skip to main content

Database Migration Policy

Last updated: 2026-04-21 · Ticket: NQU-652 (staging-to-prod ADR audit)

Policy​

Every new migration file must be paired with a rollback file in the same directory. The existing 86 pre-policy migrations are not being backfilled — retrofitting rollbacks for every historical migration is impractical and higher-risk than the policy is worth.

Going forward, PRs that add a new file under supabase/migrations/ without a matching .down.sql should be sent back for revision.

File format​

One migration = a pair of files under supabase/migrations/:

<timestamp>_<short-name>.sql         # forward migration — runner applies this
<timestamp>_<short-name>.down.sql    # rollback — humans apply manually

Use the templates as starting points:

  • supabase/migrations/TEMPLATE.up.sql.example
  • supabase/migrations/TEMPLATE.down.sql.example

The runner (scripts/run-migration.ts) glob-filters out *.down.sql from the forward run. Both files remain in the repo so the paired rollback is discoverable next to its forward migration.

When rollback is genuinely impossible​

Some migrations cannot be cleanly reversed — e.g. dropping a column with data, merging tables, or lossy transformations. In that case:

  1. State it explicitly in a comment inside the @DOWN section.
  2. Document the data-loss implication (what gets lost, who would notice).
  3. Add a manual pre-apply snapshot step to the PR description: "Before merging this to main, take an RDS snapshot named pre-NQU-XXX via AWS console."

Before running a migration against production​

  1. Take a manual RDS snapshot (invapp-dev-rds-<timestamp>-pre-<ticket>). The nightly automated snapshots aren't frequent enough for surgical rollback of a just-applied migration.
  2. Review the migration one more time against production state. Migrations that passed CI can still fail in prod if rows violate new constraints, indexes conflict, etc.
  3. Run during a low-traffic window if the migration holds locks.

Testing the DOWN section​

Before landing a migration, verify the rollback by:

  1. Running the UP section against a local or staging DB.
  2. Running the DOWN section immediately after.
  3. Confirming the schema looks like it did before (pg_dump --schema-only diff, for example).

If the DOWN section throws errors or leaves residue, fix it before merging.

CI / runner behavior​

  • npm run db:migrate executes only the UP section of each pending migration.
  • The _migrations tracking table stores the filename. Re-running a migration after rollback requires manually clearing that row.
  • CI runs migrations against a fresh Postgres container, so UP-only verification catches most issues — but does NOT verify that DOWN works. Test DOWN locally.
  • NQU-652 (staging-to-prod ADR) — parent ticket
  • docs/decisions/2026-04-03-staging-to-production-transition.md — original ADR
  • docs/admin/ops/deployment-flow.md — end-to-end deploy pipeline