c73b7e4aad
What ships (verifiable without live DB, 64 new tests): - db/migrations/0000_initial_schema.sql (Drizzle-generated, 7 tables) + .down.sql + registry entry - db/migrations/rehearse.ts: forward-then-rollback round-trip with row-count hash check (DoD 2.2) - infra/docker-compose.yml: postgres 17 + redis 7 + openobserve for local dev (5433/6380/5080) - packages/schema/src/rate-limit.ts: pluggable store; 4 tests including 21st-of-20 reject (DoD 2.4) - packages/schema/src/csrf.ts: HMAC double-submit token; 8 tests covering forgery + tamper + malformed - packages/schema/src/authz.ts: 3-role Cerbos-equivalent rules (operator/approver/viewer); 6 tests - packages/schema/src/api-contracts.ts: Zod schemas for /api/content, /api/approvals, /api/publications, /api/feature-flags + idempotencyKeyOf; 11 tests What defers to live-DB session: - 2.3 admin route handlers integration tests (401/403/200/422 contract suite) - 2.2 actual rehearsal execution against staging DB Total: 79/79 tests pass across 9 files in 4 packages. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
39 lines
1.9 KiB
Markdown
39 lines
1.9 KiB
Markdown
# 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`.
|