Status: Adopted for every schema change to a shared workbench entity
(agent-recorded) Date: 2026-08-03 Ledger:
V1_DOMAIN_WORKBENCHES_TODOS_2026-07-23.md §S3.15
Machine-checked companion:
evidence/v1-workbenches/migration-templates.json, produced byscripts/v1-workbenches/generate-migration-templates.mjs. It reads the four templates in this directory and fails when one of them loses a required section, so the contract below and the files beside it cannot drift apart.Rehearsal:
node scripts/v1-workbenches/migration-rehearsal.mjs, which runs a rename through all four phases against a real PostgreSQL with an old and a new writer running concurrently, an interruption mid-backfill, a resume, a rollback, and the final query plans.
0. Why there are four files and not one#
A schema change that can be deployed without downtime is not one migration. It is four, separated by deployments, and the separation is the whole mechanism: between any two of them there is a moment where the old code and the new code are both running, against the same database, and both have to work.
The failure this shapes against is the flag day — the deploy where the schema and the code change together and every process must switch at the same instant. It works in staging, where there is one process. In production there are dozens, the rollout takes minutes, and for those minutes half the fleet is running against a schema it was not built for. A flag-day migration does not usually fail loudly; it fails as a few minutes of errors that nobody can reproduce afterwards, because by then the fleet is consistent again.
| Phase | Deploy boundary before it | Reversible |
|---|---|---|
expand |
none — additive only | yes, by dropping what it added |
backfill |
new writer deployed | yes, the old column is intact |
verify |
none — read only | n/a, it changes nothing |
contract |
new reader soaked | no |
contract is a one-way door. Everything before it can be undone with a
configuration change; after it the old column is gone and the only recovery is a
restore. That asymmetry is why the contract template asks for a proof rather
than a checklist.
1. The eight sections every phase must carry#
These are required in all four templates, and the checker fails a template that drops one. They are not ceremony: each one is a question that gets asked during an incident, at the point where nobody has time to work out the answer.
1.1 ## Owner#
A rota or runbook URL, never a person's name. A named person is on holiday the week it matters, and a name in a document is a claim about an org chart that was true when it was written.
1.2 ## Approvals#
Who accepted the risk, and when. A migration on a shared entity crosses a team boundary by definition — that is what "shared" means — so the approval is a record that the crossing was noticed.
1.3 ## Lock budget#
The maximum lock hold and the maximum statement duration, as numbers,
enforced with lock_timeout and statement_timeout in the template itself
rather than in a person's intention.
This is the section that prevents the most common production incident in this
whole area: a DDL statement that waits for a lock behind a long-running query,
and queues every subsequent query behind itself while it waits. The table is
not locked yet and is already unusable. A lock_timeout turns that from an
outage into a failed migration you run again.
1.4 ## Observability#
What to watch WHILE it runs, named specifically enough to open before starting: replication lag, lock waits, error rate, the backfill's own progress. "Watch the dashboards" is not this section.
1.5 ## Abort threshold#
The number at which you stop, decided before starting. Written down because the decision to abort is made by a tired person at 03:00 who has already invested two hours, and every threshold agreed in advance is one they do not have to argue about with themselves.
1.6 ## Incident and rollback#
What to do when it goes wrong, including — for contract — the explicit
statement that the answer is a restore and not a rollback.
1.7 ## Evidence#
Where the artifacts land: the counts, the plans, the checkpoint state. A migration whose evidence is in a terminal that has since been closed is a migration nobody can audit.
1.8 ## Completion criteria#
How you know it is done. Specifically: not "the script exited 0", because a backfill that exited 0 having processed nothing also exited 0.
2. The rule the four phases exist to enforce#
Every intermediate state has to be one both binaries can serve. That is checkable, phase by phase:
- After
expand: the old binary must not see the new column at all. This is whyexpandis additive-only and why every added column is nullable or default-safe — an addedNOT NULLcolumn without a default breaks the old binary'sINSERT, which does not name it. - After
backfill: both columns hold the same value for every row, and both binaries write both. The dual-write is deployed before the backfill starts, not after; a backfill without it walks a snapshot while rows written behind the cursor stay unconverted, and the counts at the end agree with the rows the backfill touched. - After
verify: nothing has changed, and the evidence says the two columns agree. - After
contract: the old binary is gone. Proven, not assumed — see the contract template's precondition.
3. What "default-safe" means, and the version it depends on#
ALTER TABLE t ADD COLUMN c type NOT NULL DEFAULT x rewrote the entire table
before PostgreSQL 11. On 11 and later it does not: the default is stored in
pg_attribute and applied on read. The templates target 11+ and say so, because
the same statement is a metadata change on one version and an exclusive lock
over a hundred million rows on another — and nothing in the statement says
which.
A DEFAULT that is volatile (now(), gen_random_uuid()) still rewrites,
on every version, because each row needs its own value. The expand template
refuses those in the additive step and directs them to the backfill.
4. Dual-version coexistence, and why a rename is the worked example#
The worked example is a rename — story_source.contact becoming
story_source.contact_email — and it lives in
scripts/v1-workbenches/migration-rehearsal.mjs rather than in a sample/
directory beside these files, because a sample that is only read decays and one
that is EXECUTED on every run cannot. §5 is what it does.
A rename is the worked example because it is the change that most invites a flag
day. ALTER TABLE ... RENAME COLUMN is instant, takes a trivial lock, and looks
free. It is also the single statement that guarantees every running process
built against the old name starts failing at the moment it commits.
The four-phase version of a rename is: add the new column, write both, backfill the old rows, verify, switch reads, remove the old column. Six deploys' worth of patience to avoid one instant of breakage, and the reason it is worth it is that the instant of breakage is unbounded — it lasts until the rollout finishes, which is exactly when you are least able to make it finish faster.
5. The rehearsal, and what it found#
scripts/v1-workbenches/migration-rehearsal.mjs runs the sample rename over
10,000 rows against a real PostgreSQL, with an old writer inserting and updating
throughout, a new writer from the dual-write deploy, an interruption
mid-backfill, a resume from the checkpoint, a rollback rehearsal, and the query
plans before and after the drop. Two controls run beside it and both must break
the old writer, because a rehearsal with no failing arm shows that the sequence
ran rather than that it was necessary:
- The flag day.
ALTER TABLE ... RENAME COLUMN, committed. The old writer's next INSERT fails withcolumn "contact" ... does not exist. Instant, trivially locked, and every process built against the old name fails from the moment it commits. - The impatient expand. The engine refuses
ADD COLUMN ... NOT NULLon a non-empty table outright — so the form that actually reaches production is a column added with a default and the default dropped later, and that one breaks the old writer with a not-null violation on an INSERT that does not name it.
The rehearsal found a real bug in its own dual-write trigger, which is now
documented in the backfill template §4b: a trigger written as "fill whichever
column is NULL" is correct on INSERT and does nothing on UPDATE, because
both columns already hold a value. The two diverge from the first update onward
and nothing notices until the verify phase's write-parity check — which is what
caught it.
6. Limits of the checker#
The companion checks structure: that each template carries its eight common
sections and its phase-specific ones, that the SQL templates set both timeouts,
that CREATE INDEX is CONCURRENTLY, and that the contract template names its
precondition. It cannot check that the numbers in a filled-in copy are right,
that the abort threshold is sensible, or that the person who approved understood
what they were approving. Those are review obligations and are listed here
rather than left as silently-unchecked prose.