# Zero-Downtime Migration Templates

**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 by `scripts/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`](./expand.template.sql)     | none — additive only      | yes, by dropping what it added |
| [`backfill`](./backfill.template.md)  | new writer deployed       | yes, the old column is intact  |
| [`verify`](./verify.template.sql)     | none — read only          | n/a, it changes nothing        |
| [`contract`](./contract.template.sql) | 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 why
  `expand` is additive-only and why every added column is nullable or
  default-safe — an added `NOT NULL` column without a default breaks the old
  binary's `INSERT`, 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 with `column "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 NULL` on 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.
