Migration Plan Spec
Everything else you build with an agent has an undo button. Bad code: revert the commit. Bad deploy: roll back to the last one. A migration is the exception, because it touches the one thing that can't be regenerated: the data. Run an UPDATE with a missing WHERE clause on a live database and git cannot help you. This is the single place in this whole curriculum where I will tell you to be slow on purpose.
The plan has one structural idea: never make a change that breaks the currently running code. That is the expand-and-contract pattern. Add the new column while the old one still works. Ship code that writes both and reads the old. Backfill. Flip reads to the new column. Only when nothing has touched the old column for a comfortable while do you contract: drop it. At every point in that sequence, the live version of the app and the live shape of the database are compatible, which means at every point you can stop, and stopping safely is the entire feature.
Two disciplines make the plan real rather than ceremonial. Every step gets a verification: a command you run, and the output you expect, written down before you start, because mid-migration is the worst possible moment to decide what "looks right" means. And every step gets a rollback, written before you execute the step, because writing the rollback is how you find out whether the step is actually reversible. Some aren't. Dropping a column isn't. Those steps wait until the end, after everything else has been verified, when rolling back is something you no longer need.
Hand this plan to your agent to help draft and to write the scripts. Do not hand it the production credentials and walk away. You run the irreversible parts, awake, with the backup verified first.
# Migration Plan: [one line, e.g. "split users.name into first_name / last_name"]
Date planned: [YYYY-MM-DD]
Executed by: [you. A human. Named here so it's nobody's ambient job.]
System: [which database, which app, which environment]
## What is changing and why
[2-4 sentences. Current shape, target shape, and the reason. If the reason
is cosmetic, stop here and don't run a migration for it.]
## Blast radius
- Tables touched: [list]
- Rows affected: [run the SELECT COUNT(*) now, paste the number]
- Code that reads/writes these fields: [files/endpoints; ask the agent to
grep for every usage and list them here. The one you miss is the outage.]
- Downtime expected: [none if expand-and-contract is followed / otherwise
how long and when]
## Preconditions (all checked before step 1)
- [ ] Backup taken: [exact command, e.g. `pg_dump ... > backup-YYYY-MM-DD.sql`]
- [ ] Backup RESTORED somewhere and spot-checked. An unrestored backup is
a hope, not a backup.
- [ ] Full plan rehearsed on a copy of production data: [where]
- [ ] Traffic is low: [when, and how you know]
- [ ] Rollback section below is written completely. No rollback, no start.
## Steps
[Each step: small, one concern, verified before the next begins. Scripts
must be idempotent (safe to run twice) because reruns happen.]
### Step 1: Expand (add new columns, safe and additive)
- Run: `[migration adding first_name, last_name as NULLABLE; required
comes later, after backfill]`
- Verify: `[\d users]` → shows both columns, existing rows unaffected.
- App status: old code still runs untouched. Nothing reads the new columns.
- Rollback: `[DROP COLUMN both; safe, nothing uses them yet]`
### Step 2: Deploy dual-write code
- Deploy: app writes old AND new fields, still reads old.
- Verify: create a test record; confirm all three fields populated.
- Rollback: redeploy previous version. New columns just go stale; nothing reads them.
### Step 3: Backfill existing rows
- Run: `[backfill script; batched (e.g. 1000 rows per iteration), resumable,
and idempotent so a crash mid-run means rerun, not restore]`
- Verify: `[SELECT COUNT(*) WHERE first_name IS NULL AND name IS NOT NULL]` → 0.
Spot-check [5-10] known records by eye, including the weird ones:
single-word names, unicode.
- Rollback: none needed. Old columns untouched; a bad backfill is rerun, not reverted.
### Step 4: Flip reads to new columns
- Deploy: app reads new fields everywhere. Still dual-writing.
- Verify: click through the [3-5] core flows that display this data. Watch
error logs for [15+] minutes.
- Rollback: redeploy previous version. Dual-writes kept both shapes current,
so flipping back loses nothing.
### Step 5: Contract (drop the old column, IRREVERSIBLE)
- Wait: [days, not minutes] after step 4 with clean logs.
- Precondition: fresh backup taken NOW, verified restorable.
- Run: `[stop writing old field; then DROP COLUMN name]`
- Verify: full app click-through; logs clean for [an hour].
- Rollback: restore from the step-5 backup. That is the whole plan for this
step, which is why it goes last and waits longest.
## Abort criteria (decided now, not during)
Stop and roll back the current step if:
- Any verification does not match its expected output. Not "close." Match.
- Error rate rises at all after a deploy step.
- Anything surprising happens that the plan didn't predict. Surprise during
a migration is information; the migration will still be there tomorrow.
## Aftermath
- [ ] Delete or archive the backfill script; it must never run again by accident.
- [ ] Update the data-model spec to the new shape, same day.
- [ ] Note in the ADR folder if this reversed or amended a recorded decision.Adaptation notes:
- The same skeleton covers more than schema changes: moving hosts, swapping an auth provider, changing a payment integration. Anything live with state gets steps, verification, rollback, abort criteria.
- For a solo project with ten users, you keep every section but shrink the waits: the step-4 soak can be an evening instead of days. What you never shrink is the restored-backup precondition. Restore it. Actually restore it.
- Batched and resumable matters for the backfill even at modest scale: a script that locks the table for four minutes is an outage with extra steps.
- If your app framework has a migrations system (Alembic, Prisma, Django), each "Run" step becomes a migration file in the repo. Numbered migration files in git are the difference between a database whose history you know and one you archaeologize.
- The classic mistake is collapsing expand-and-contract into one deploy because the multi-step version feels bureaucratic. The multi-step version is what lets you stop halfway. The one-shot version is a coin flip you can't call back.