BreezyCorp Handbook

Appearance

Runbook: Database Migration Rollback ​

Prisma migrations are forward-only by default. This runbook covers the procedure for backing out a migration that went wrong, using the hand-written rollback.sql convention from Phase 3E.

Prerequisites ​

  • Destructive migrations (DROP COLUMN, ALTER TYPE, DATA DELETE) ship with a rollback.sql in the same migration directory
  • You've verified the target rollback SQL reproduces the prior schema on a scratch DB first

Decision tree ​

Is the migration already applied to production? ​

  • No → just revert the commit and deploy. No SQL needed.
  • Yes → proceed below.

Is the migration additive-only (ADD COLUMN, CREATE TABLE)? ​

  • Yes → rollback is optional. Deploy the previous app version; the new schema is a superset, so the old code ignores the extras. File a cleanup ticket to drop the unused columns later.
  • No → you need the rollback SQL.

Procedure ​

1. Pause writes ​

bash
# Set the API to read-only mode (flag-flag based — requires app support)
kubectl set env deployment/spade-api READ_ONLY_MODE=true

If read-only mode isn't implemented yet, scale down to 0 replicas and accept the downtime.

2. Back up the current state ​

bash
pg_dump --format=custom --file=/tmp/pre-rollback-$(date +%s).dump $DATABASE_URL

This is the last-resort restore if the rollback SQL has bugs.

3. Apply the rollback SQL ​

bash
psql $DATABASE_URL -f packages/db/prisma/migrations/<timestamp>_<name>/rollback.sql

Read the output carefully. Any error = stop and investigate.

4. Update the Prisma migration history ​

The _prisma_migrations table tracks which migrations are applied. After a rollback, mark the migration as rolled back:

bash
DATABASE_URL=... pnpm --filter @breezycorp/db exec prisma migrate resolve \
  --rolled-back <migration-name>

5. Deploy the previous app version ​

bash
kubectl rollout undo deployment/spade-api
kubectl rollout undo deployment/spade-worker

6. Resume writes ​

bash
kubectl set env deployment/spade-api READ_ONLY_MODE-

Verify /health/ready returns 200.

7. Write a post-mortem ​

Required for any migration rollback. The template lives in docs/templates/postmortem.md (TODO).

What if the rollback SQL is wrong? ​

  1. Don't panic, don't run more SQL
  2. Restore from the pre-rollback dump taken in step 2
  3. Restore from the latest nightly snapshot if the pre-rollback dump is also corrupt (see runbooks/restore-drill.md)
  4. Declare an incident and escalate

Prevention ​

  • Test the rollback locally before merging any destructive migration
  • Reviewer requirement: PRs with destructive migrations need a second reviewer who has run the rollback on a scratch DB
  • CI check: db:migrate:diff ensures forward migrations apply; we don't yet automate rollback testing — TODO

Known gotchas ​

  • Column drops with data: the rollback SQL can't recreate lost data. Destructive migrations must either be preceded by a data-export migration OR never merged without explicit data loss approval
  • Foreign key direction changes: if a rollback changes FK direction, ordering matters — drop FKs first, then alter, then re-add
  • _prisma_migrations staleness: if migrate resolve --rolled-back is skipped, migrate deploy will try to apply the migration again on the next deploy
  • docs/slo.md § Data Durability (RPO/RTO)
  • runbooks/restore-drill.md — worst-case recovery
  • CLAUDE.md § Database Migration Workflow

Internal use only — BreezyCorp