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>
1.9 KiB
1.9 KiB
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:
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.