DB Migrate
Generate and apply a database migration the safe way — using the project's migration tool, with expand-contract discipline for breaking changes, lock-free DDL, and a reversible up/down.
/db-migrate<the schema change to make, or a path to a pending migration to review>Install to ~/.claude/commands/db-migrate.md
Scope
Treat $ARGUMENTS as the schema change to make (e.g. "add a status column to orders", "rename user.name to full_name") or a path to a pending migration to review. Restate the change and whether it's additive (safe) or breaking (needs expand-contract) in one sentence before writing anything.
Goal: produce a migration that's safe on a live database — uses the project's migration tool, avoids long locks, and is reversible — not a hand-run ALTER that locks a hot table mid-deploy.
NOTE
This command writes and applies a migration with safe-migration discipline. For a brand-new pgvector schema specifically, use Scaffold a pgvector Schema; for planning a large, multi-step breaking change end to end, hand off to the postgres-migration-engineer.
Step 1 — Detect the migration tool
Find the project's migration framework (Prisma, Drizzle, Alembic, Flyway, golang-migrate, Rails, Knex, …) and match its file naming, format, and up/down conventions. Never hand-run DDL outside the tool that owns the schema. If pgroll is in use, generate its JSON migration instead.
Step 2 — Classify the change
Decide if the change is additive (new nullable column, new table, new index — safe to apply directly) or breaking (rename, retype, NOT NULL, drop, new constraint on existing data). Breaking changes on a table with real data/traffic must be decomposed.
Step 3 — Decompose breaking changes (expand-contract)
For a breaking change, split it into separate, reversible migrations: expand (additive) → backfill (batched) → dual-write (app) → migrate reads (app) → contract (drop old, a later release). Don't collapse add and remove into one migration. See Zero-Downtime Postgres Migrations.
Step 4 — Use lock-free DDL
Substitute online operations for the ones that lock:
CREATE INDEX CONCURRENTLY(not plainCREATE INDEX).ADD CONSTRAINT … NOT VALIDthenVALIDATE CONSTRAINT(not a constraint that scans under lock).- Add columns nullable with a constant default (a volatile default rewrites the table).
- Batched, resumable backfills (never one giant
UPDATE).
Step 5 — Make it reversible
Write the down/rollback for the migration (or confirm the tool generates a correct one). For expand-contract, ensure the old path survives until the contract step, so any phase can be rolled back without data loss.
Step 6 — Plan, apply, verify
Show the SQL the tool will run (a dry-run/plan where supported) and call out any statement that would take an ACCESS EXCLUSIVE lock. Apply it, then verify: the migration recorded, a CONCURRENTLY-built index is VALID, and the schema matches intent.
WARNING
The migrations that cause outages take a long lock or rewrite a large table: a plain CREATE INDEX, SET NOT NULL directly, an ALTER TYPE rewrite, a volatile-default column add, or a single huge UPDATE. If the change implies any of these on a table with data, stop and decompose it before applying.
Related
- Zero-Downtime Postgres Migrations: The Expand-Contract Playbook for 2026How to change a live Postgres schema without downtime or broken deploys — the expand-contract pattern, safe column changes, batched backfills, and CONCURRENTLY.
- pgrollAn open-source CLI for zero-downtime, reversible Postgres schema migrations using the expand-contract pattern behind versioned schema views.
- Postgres Migration EngineerUse this agent to plan and execute a zero-downtime Postgres schema migration — decomposing a breaking change into expand-contract steps, writing batched backfills, building indexes CONCURRENTLY, validating constraints online, and keeping every step reversible with the project's migration tooling. Examples — "add a NOT NULL column to a 200M-row table without downtime", "rename a column safely across a rolling deploy", "split this risky migration into reversible expand/contract steps".
- Scaffold a pgvector Schema & HNSW IndexScaffold a production-ready pgvector schema and HNSW index for a corpus — matching the project's migration tooling, distance metric, and embedding dimensions.
- Profile Postgres QueriesProfile a Postgres workload to find the queries actually costing you — rank by total time with pg_stat_statements, EXPLAIN the worst offenders, and recommend the highest-leverage fix.