Database Migrations

On this page

Canopy is forward-only for application-level schema migrations. The architectural rationale lives in ADR-016; this page is the contributor-facing how-to.

TL;DR

  • Write up.sql only. Don’t write down.sql.

  • If your migration is wrong, write a new forward migration that fixes it.

  • For destructive changes (rename, drop, retype), use the expand-contract pattern below.

  • Broke your local dev DB? cargo xtask migrate rollback.

  • Broke production? PITR via pg_basebackup + WAL replay (out of scope of this page; see ops runbook).

Writing a migration

Migration files live under each service’s migrations/ directory:

services/canopy-snap/migrations/
  20260413000000_create_caps_tables.sql
  20260420000000_add_abawd_clock.sql
  ...

Filename format: <UTC timestamp>_<descriptive_name>.sql. The timestamp orders execution; the name is for humans.

Each file contains forward DDL only:

-- 20260502120000_add_quality_review_flag.sql

ALTER TABLE caps_authorizations
    ADD COLUMN quality_review_required BOOLEAN NOT NULL DEFAULT false;

CREATE INDEX idx_caps_authorizations_qa
    ON caps_authorizations (quality_review_required)
    WHERE quality_review_required = true;

Run locally:

cargo xtask dev refresh    # picks up new migrations automatically

When a migration is wrong

The fix is a new migration that corrects it forward, not a down. Examples:

Bug Wrong fix Right fix

Column was misnamed

db:rollback, edit the up, re-apply

New migration: ALTER TABLE … RENAME COLUMN

Default value is wrong

Edit the up, force-rerun

New migration: ALTER TABLE … ALTER COLUMN … SET DEFAULT

CHECK constraint was too strict

Drop and re-add manually

New migration: ALTER TABLE … DROP CONSTRAINT, ADD CONSTRAINT

Index was on the wrong column

Edit the up

New migration: DROP INDEX, CREATE INDEX

Migration shipped to prod but data is bad

Application-level rollback

New migration backfills correct values; PITR if data is unrecoverable

Once a migration has merged to main, it’s history. Don’t edit it.

Expand-contract for destructive changes

A schema change is destructive if:

  • It removes a column, table, index, or constraint that existing code reads.

  • It renames a column or table.

  • It changes a column’s type incompatibly (e.g., TEXT → INTEGER).

  • It tightens a NOT NULL / CHECK against rows that don’t yet satisfy it.

For these, split the change into separate forward migrations. The classic shape — column rename — illustrates:

  1. Expand (migration _add_new_column.sql): add the new column, both old and new code paths see a valid schema.

  2. Code change: deploy app version that writes both the old and new column, reads from the old.

  3. Backfill (migration _backfill_new_column.sql): copy historical values from old to new.

  4. Code change: deploy app version that writes both the old and new column, reads from the new.

  5. Code change: deploy app version that writes only the new column.

  6. Contract (migration _drop_old_column.sql): drop the old column once nothing reads it.

Each step is independently deployable and rollback-safe — at any point you can revert the application deploy without touching the database, because the schema continues to satisfy both N-1 and N versions of the app.

This is more migration files than a one-shot RENAME COLUMN would be. That’s the cost. The benefit is that none of those files can break a running deploy in flight.

When not to expand-contract

Trivial additive changes don’t need it:

  • New column with a default and no NOT NULL: just add it.

  • New table that no code reads yet: just add it.

  • New index: just add it.

If the change is purely additive and old code keeps working unchanged, ship it as one forward migration.

Audit-sensitive tables

Some tables in canopy carry compliance-critical invariants that no migration — forward or otherwise — should casually disturb:

Table Invariant

fti_audit_log (canopy-tanf, canopy-medicaid)

SHA-256 hash chain over previous_hash / event_hash. Break = Pub 1075 §9 reportable to IRS. ADR-014

audit_events (canopy-security)

Wildcard-subscribed event log with its own hash chain.

*_determinations (signed) tables

JWS-signed column shape verified by cross-service consumers. ADR-002

idempotency_keys (per service)

24-hour TTL; single-flight execution — an atomic claim elects one winner per key (fresh-insert / TTL-recover / hash-fenced steal) holding a claim_id-fenced, renewed lease, so each key’s handler runs once per key across concurrent requests and replicas (epic #1003). Evolved via additive, forward-only expand/contract columns (state, request_hash, claim_id, lease_expires_at, response_headers, replayable; nullable response_*) with state-invariant CHECKs. DDL is owned by the migration path (#1463): canopy_db::ensure_idempotency_schema, run by bootstrap’s migration phase and by cargo xtask migrate apply — never by the runtime.

fti_chain_verifications (canopy-security)

Daily verify-job results; restoring from a stale snapshot would re-emit historical breach events.

Migrations against these tables get an explicit pre-merge review pass for hash-chain compatibility. If you’re touching column shapes a hash includes, the migration must extend the chain forward, not break it.

Recovery paths

"I broke my local dev DB"

cargo xtask migrate snapshot   # before a risky migration
# … run the migration, things go wrong …
cargo xtask migrate rollback   # restore from the most recent snapshot

migrate rollback --db <name> rolls back a single service’s database without touching the others.

"I broke a CI pipeline"

CI starts with a clean devstack on every run; nothing to recover. The bad migration just fails the pipeline. Push the corrective migration and re-run.

"I broke production"

This is the path that backs the forward-only stance. Production rollback is PITR (point-in-time recovery) via pg_basebackup + WAL replay. The runbook lives in operations docs (separately tracked); ADR-016 calls out that the runbook must actually exist and be tested before forward-only is fully load-bearing.

If the schema is wrong but the data is still intact, the right move is usually a forward migration that corrects the schema, deployed under a feature flag. Reach for PITR only if data has been corrupted or destroyed, since PITR rolls everything back including unrelated work.

Why no down.sql

Short version: see ADR-016. Long version: down migrations create more problems than they solve at canopy’s compliance and operational posture. Forward-only forces every fix through the same review and test gates that the original migration went through, which is what we want.

See also

Edit this page · default