ADR-016: Forward-Only Schema Migrations

On this page

Context

Op-infra plan Step 4 sub-task 4 originally listed "Create down migration templates for critical tables (persons, determinations, enrollments)". It was deferred when the snapshot/rollback tooling shipped, and the deferred row was tracked at #345 until this ADR closed it.

Most popular Rust + web framework migration tools (sqlx, Diesel, Rails ActiveRecord, Django) ship with both up.sql and down.sql per migration. The convention dates from a single-developer / single-database era when db:rollback was a viable incident response. At canopy’s scale and with canopy’s compliance posture, that convention no longer earns its review cost.

Three concrete forces:

  1. High-scale operational shops have converged on forward-only. Stripe, GitHub, Shopify, Heroku/Salesforce’s DB team, Notion, and dbt-cloud have all written publicly about removing down migrations from their workflow. Cloud database vendor docs (AWS RDS, GCP Cloud SQL, Azure DB) build their rollback narrative around point-in-time recovery, not application-level downs.

  2. Canopy’s compliance surface punishes naive rollback. Three load-bearing tables would silently break under a down migration:

    • FTI audit hash chain (ADR-014) — fti_audit_log rows in canopy-tanf and canopy-medicaid carry SHA-256 previous_hash / event_hash columns chained over the previous row. A down migration that disturbs those columns breaks the chain — and a broken chain is Pub 1075 §9 reportable to the IRS as a compliance event. The chain is the entire point of the table; running a down would manufacture the exact problem the chain was designed to detect.

    • JWS-signed determinations (ADR-002) — every program service stores determinations with cryptographic signatures over a stable column shape. Cross-service consumers (canopy-eligibility orchestrator, canopy-reporting) verify those signatures. Down migrations that reshape signed columns invalidate already-signed history without leaving a trace.

    • Cross-program audit subscribers (ADR-004) — canopy-security audits all events via the wildcard # routing key into audit_events, which has its own hash chain. Down migrations on event-source tables can reintroduce IDs the audit log already attests didn’t exist.

  3. The "back-out plan" is itself a risk. Believing a down migration is available shifts the failure mode from "we don’t ship migration X" to "we ship X with less scrutiny because we can roll back." When the down is then needed it often doesn’t actually work — data has drifted, dependent rows exist that didn’t at write time, or destructive changes (DROP COLUMN) have permanently removed the values the down would need to restore. The cheap escape hatch turns out to be more expensive than the discipline it displaced.

The operational-infrastructure plan already shipped the tooling that makes forward-only viable in practice:

  • cargo xtask migrate snapshot / migrate rollback (Step 4) — full per-database snapshot + restore for the dev / CI rollback case.

  • PITR via pg_basebackup + WAL replay (Step 3) — production point-in-time recovery, the cluster-level escape hatch.

Both recover state, not just schema, which is what’s actually needed during a real incident.

Decision

Canopy is forward-only for application-level schema migrations. New migrations ship as up.sql only; no down.sql siblings.

When a migration has a bug, the fix is a new forward migration that corrects the schema. The corrective migration goes through the same review and CI as any other.

Schema rollouts that destructively change column shape (rename, drop, retype) follow the expand-contract pattern (also called parallel change):

  1. Expand — first migration adds the new shape without removing the old. Both old and new application code see a valid schema.

  2. Deploy new code that reads / writes the new shape (and writes the old shape too, if needed for backward compatibility).

  3. Backfill if the new shape needs historical values.

  4. Cut over traffic to the new code.

  5. Contract — another forward migration drops the old shape once nothing reads it.

Rollback during the cutover is by traffic shift, not by schema change. The old code still works against the expanded schema, so reverting the deploy reverts the user-visible behaviour without touching the database.

The dev / CI rollback path is cargo xtask migrate snapshot (before the risky migration) → cargo xtask migrate rollback (if it goes wrong). The production rollback path is PITR.

Consequences

Positive

  • Compliance integrity preserved. FTI audit hash chain, JWS determination history, and audit_events chain stay intact under all schema changes. No down migration can manufacture a Pub 1075 §9 reportable event.

  • Half the review surface. Every migration carries one SQL file, not two. Reviewers focus on the forward path; tests exercise it.

  • Blue-green and rolling deploys work without choreography. Expand-contract is the natural shape for both deployment models. Contributors who later adopt blue-green inherit a forward-only foundation rather than retrofitting one.

  • Closes a structural foot-gun for the audit-sensitive tables specifically. The "hybrid" alternative (down migrations only for compliance tables) is precisely backwards: those tables are where down migrations cause the most damage, not the least.

  • Aligns with cloud-vendor rollback expectations. AWS RDS, Cloud SQL, Azure DB all build their guidance around PITR. Canopy’s escape hatches match.

Negative

  • Forward-fix discipline required. When a bad migration ships, the team has to be willing to write a corrective migration quickly rather than reach for a down. Slower than a one-line db:rollback for trivial cases.

  • Expand-contract takes more migration files. A column rename that would be one up/down pair becomes (typically) three forward migrations: add new, backfill, drop old. Each is small but the surface is larger.

  • PITR runbook must actually exist and be tested. The forward-only stance assumes PITR is the production escape hatch. Resolved 2026-05-03: the PITR section of the database-backup-restore runbook now covers the decision tree (PITR vs. forward-fix), pre-PITR checklist, single-database and cross-service procedures, post-recovery validation including ADR-014 hash-chain integrity and signed-determination row-count checks, and a tested execution log. End-to-end PITR was exercised against a one-off postgres:18-alpine container on 2026-05-03; the recovered cluster discarded the simulated post-incident state as expected. Issue #353 closed.

  • Snapshot maintenance cost. Dev snapshots take disk and benefit from periodic refresh; that’s already true today.

Neutral

  • Tool support unchanged. sqlx-cli accepts forward-only directories; no tooling change required.

  • No retroactive sweep. Existing migrations stay as they are; no historical down files exist to remove.

Alternatives Considered

  1. Hybrid: down templates for FTI / determinations / compliance tables only. Most dangerous of the three options. Those tables are precisely where running a down would manufacture a compliance event. Having the templates available is a foot-gun pretending to be a safety device. Rejected.

  2. Full down migrations for every table. Doubles review burden in exchange for code that, on this team’s trajectory, will rot before it’s used. Existing high-scale shops who tried this report graveyards of untested down code. Rejected.

  3. Forward-only with a tooling-enforced gate. A cargo xtask migrate check that rejects any down.sql could enforce the policy at CI. Marginal benefit since no down files exist today; revisit if a contributor accidentally ships one. Deferred.

Related ADRs

Edit this page · default