Runbook — Database Migration Deploy

Field	Value
Severity	P1 (production data path)
Owner	@aphaiboon
Last verified working	2026-05-16
Estimated time	10–30 min depending on migration size
Required access	GitHub write, Neon dashboard, Railway deploys, psql

When to use this runbook

Whenever you ship a database migration to production. Use every time — there’s no “small” migration. Most production DB incidents we’ve had were “small” migrations.

Prerequisites

• Migration file lives in migrations/ and is registered in _journal.json
• Migration uses idempotent SQL (IF NOT EXISTS, DO $$ ... EXCEPTION WHEN duplicate_object)
• --> statement-breakpoint between every SQL statement
• PR reviewed and approved
• You’ve run npm run db:migrate on local Postgres successfully
• You’ve tested the rollback path mentally (or in code)

Steps

Step 1 — Pre-flight checks (before merging the PR)

# In your feature branch, verify migration is well-formed
npm run db:status                # JSON output — confirms no pending migrations on local
npm run db:gen "<desc>"          # Should produce NO new files (your migration already exists)
# If db:gen produces extra changes → SNAPSHOT DRIFT, fix before merging
npm run db:migrate:check         # CI verification
npm run check:smart              # No TS errors in schema files

Common gotcha: if db:gen prompts about unrelated tables/enums, the snapshot has drifted. Run npm run db:snapshot:patch and re-verify. See Database Migrations standard.

Step 2 — Snapshot the production DB (within 10 min of deploy)

# In Neon dashboard:
# Project → Branches → Create branch from main
# Name: pre-migration-YYYY-MM-DD-HH-MM

This is your fast rollback path. Takes seconds in Neon. Always do it.

Step 3 — Merge the PR + watch the deploy

Railway runs npm run db:migrate && npm start on every deploy. The migration runs BEFORE the new server boots.

# Watch Railway deploy logs
# Expected output:
#   "Running migrations..."
#   "Migration 0XXX_<desc> applied"
#   "All migrations applied"
#   "Starting server on port 3000"

Timeouts: if a migration takes >2 minutes on Railway, something is wrong. Most migrations should be <30s.

Step 4 — Verify (do not skip)

After Railway deploy succeeds:

# Verify migration applied
psql $PROD_DATABASE_URL -c "SELECT * FROM drizzle.__drizzle_migrations ORDER BY created_at DESC LIMIT 5;"

# Verify the schema looks right
psql $PROD_DATABASE_URL -c "\d <new_or_changed_table>"

# Run a sample query through the new schema
psql $PROD_DATABASE_URL -c "SELECT count(*) FROM <new_or_changed_table>;"

# Sample app-level check
curl -s https://cmmd.ai/api/health

Then exercise the actual feature in the app — log in, hit the page that uses the new schema, confirm it works.

Step 5 — Backfill (if applicable)

For migrations that add a NOT NULL column or split a table:

# Backfill script lives in scripts/migrations/ for non-trivial backfills
npx tsx scripts/migrations/backfill-<feature>.ts --prod

Critical: Backfill migrations with JOINs MUST handle the NULL FK case (FM-031). Docs with collectionId IS NULL are invisible to JOINs and will be silently missed. Always add an explicit WHERE x IS NULL query.

Step 6 — Confirm

• Migration applied to production
• New schema visible via \d
• Sample query works
• App-level functionality works
• No 5xx spike in PostHog post-deploy

Post in #deploys:

✅ Migration <0XXX_desc> applied to prod at HH:MM UTC
Backfill: [none / N rows updated]
Verification: [what you checked]

Rollback

If migration succeeded but app is broken

Rollback the app, leave the migration in place:

# Railway → Deployments → Rollback to previous deploy

Most migrations are backward-compatible (additive). The new column / table can sit there harmlessly while you fix the app code.

If migration broke the DB

This is the dangerous case. Stop and call @aphaiboon if you’re unsure.

Options:

1. Restore from Neon branch (created in Step 2): swap the main branch endpoint to the pre-migration branch via Neon dashboard. Near-zero downtime, full rollback.
2. Manual reverse migration — only if you know exactly what to undo:

-- Example: dropping a column added by the migration
ALTER TABLE <table> DROP COLUMN IF EXISTS <new_column>;
-- Remove from migrations table
DELETE FROM drizzle.__drizzle_migrations WHERE hash = '<migration_hash>';

3. Last resort — point-in-time recovery via Neon (covers up to 30 days back). This is the “everything is on fire” lever; coordination with @aphaiboon required.

Troubleshooting

Problem: “Migration shows pending on db:status but Railway deploy succeeded”

Cause: db:status uses journal entries; Railway uses hash-based detection. Can drift. Fix: Run npm run db:migrate against the prod URL (it’s idempotent) — confirms truth.

Problem: “Drizzle says ‘data is malformed’”

Cause: Snapshot drift (someone used --custom without patching the snapshot). Fix: npm run db:snapshot:patch then regenerate.

Problem: “Migration takes forever on production but was instant locally”

Cause: Production table size. A ALTER TABLE ADD COLUMN on a 10M-row table is fast; a CREATE INDEX without CONCURRENTLY locks the table. Fix: Cancel via pg_cancel_backend(<pid>), write a new migration using CREATE INDEX CONCURRENTLY (incompatible with transactions — special handling needed).

Problem: “App can’t find a column that exists in the DB”

Cause: Likely the table was renamed but the pgTable() string in shared/schema/ wasn’t updated (FM-032). All 7 layers must be updated atomically. Fix: Update pgTable() string, rebuild, redeploy.

Problem: “Statement breakpoint missing in DO $$ ... END $$”

Cause: You added a breakpoint inside a dollar-quoted block — Neon rejects with “unterminated dollar-quoted string.” Fix: Remove breakpoint from inside DO $$ ... END $$ (Drizzle requires breakpoints OUTSIDE only).

After the procedure

• #deploys post with timestamp + verification
• If anything unusual happened, file a postmortem (24h)
• If you discovered a documentation gap, update Database Migrations standard
• If the migration needs to be re-run elsewhere (E2E DB, staging), do that next

Related

• Database Migrations standard
• ADR-0001 — Drizzle ORM
• Runbook — Production Incident Response
• FM-031 and FM-032 in Incidents