# Database migrations — runbook

**Authoring rule:** every schema change follows **expand-then-contract** sequencing. A breaking change is split into ≥2 migrations with a rollback point between them.

## Pattern

| Phase | Migration files | What it does | Rollback |
|---|---|---|---|
| 1. Expand | `NNNN_expand_<change>.sql` | Add new nullable column / new table / new index. Old code keeps working. | Drop the new addition. |
| 2. Backfill | `NNNN_backfill_<change>.sql` (if data migration needed) | Populate the new column from existing data. Idempotent. | Re-run or delete column. |
| 3. Constrain | `NNNN_constrain_<change>.sql` | Add NOT NULL / UNIQUE / FK / CHECK now that data is populated. | Drop the constraint. |
| 4. Contract | `NNNN_contract_<change>.sql` | Remove the old column / table once no code reads it. **Only after ≥1 deploy cycle of the new code.** | Restore from `pg_dump`. |

## Mandatory for every migration

- `migrations/NNNN_<name>.sql` — the forward migration.
- `migrations/NNNN_<name>.down.sql` — the rollback SQL.
- An entry below in the **Registry** with rationale, linked plan, and rollback cost estimate.

## Registry

| # | Date | Name | Plan | Forward cost | Rollback cost | Notes |
|---|---|---|---|---|---|---|
| 0000 | 2026-04-26 | initial_schema | Phase 1 Stage 2.1 | Low (empty DB; CREATE TABLE only) | Low (DROP TABLE on 7 tables, no data loss possible on empty DB) | Establishes 7 tables: content, publications, approvals, metrics, linkedin_tokens, audit, outlet_feature_flags. Generated from `packages/schema/src/db.ts` via drizzle-kit. |

## Rehearsal

Stage 2.2 DoD: every schema change is rehearsed end-to-end on staging before production. Command:

```bash
bun run db:migrate:rehearse
```

Rehearsal must cover: forward → backfill (if applicable) → constrain → contract, with a row-count + row-hash check at each step.

## Incidents

Any migration incident gets a post-mortem in `docs/incidents/YYYY-MM-DD-migration-<name>.md`.