CFN Migration Rehearsal
Purpose: cfn-ops DESIGNS the rollback (the up+down pair and the rollback rehearsal step). This skill EXECUTES that rehearsal: it actually applies the up migration, applies the down migration, and proves the schema round-trips back to its starting state. A down migration that does not fully reverse its up is caught here, before merge, on a disposable database, not in production during an incident.
Wired by: this skill is invoked as an executable gate by cfn-loop-task Phase 4 (when the db build flag is set AND migration files appear in the diff — a hard exit-code gate, not advisory), and it is cited as the executable rollback evidence designed by cfn-ops Phase 6 and asserted by cfn-test-plan Phase 3 (the AC-mig row). It runs only against CFN_SCRATCH_DATABASE_URL, never DATABASE_URL.
SAFETY CONTRACT (read first)
This skill runs DDL against a real database. Every guard below runs BEFORE any database connection is opened. If any guard trips, the skill exits non-zero with a clear message and touches nothing.
- Requires
CFN_SCRATCH_DATABASE_URL. Without it, the skill refuses. It never falls back to DATABASE_URL, .env, or any ambient connection. The scratch database must be explicitly named by you.
- Refuses prod look-alikes. It refuses if the scratch URL equals
DATABASE_URL or SUPABASE_DIRECT_CONNECTION (read from .env without sourcing it) or the environment DATABASE_URL, and refuses any URL pointing at a Supabase pooler endpoint (*pooler.supabase.com*, *.pooler.*).
- Never writes unscoped DELETE/TRUNCATE. The skill itself issues no destructive SQL. It also refuses to run a migration FILE that contains an unscoped
DELETE or a TRUNCATE, per the test-database safety rules.
- Disposable target only. Use a throwaway database you can drop and recreate. The rehearsal applies real DDL and rolls it back; if the down is wrong, the scratch DB is left in the post-down state for inspection.
The scratch database is YOUR responsibility to provision and dispose. A good pattern: a local Postgres container or a dedicated Supabase project used for nothing else.
How It Works
- Run all safety guards. Refuse on any failure.
- Verify
psql and pg_dump are installed (exit non-zero if not).
- Capture the baseline schema snapshot (
s0) with pg_dump --schema-only, normalized (comments/SET/blank lines stripped, sorted).
- Apply the up migration with
psql -v>. On error, stop and report.
- Capture
s1. Note if up produced no schema change.
- Apply the down migration. On error, report that up applied but down failed to roll back.
- Capture
s2.
- Diff
s0 vs s2:
- identical to baseline -> ROUND-TRIP CLEAN (exit 0).
- any drift -> ROUND-TRIP DIRTY, print the diff, exit non-zero. The down does not reverse the up.
Inputs
--up <file> (or first positional): the up migration SQL file. Required.--down <file> (or second positional): the down migration SQL file. Required. A migration with no down is not rehearsable.CFN_SCRATCH_DATABASE_URL (env): the disposable scratch database. Required.
Outputs
- Verdict to stdout:
ROUND-TRIP CLEAN or ROUND-TRIP DIRTY with the schema diff.
- Exit codes:
0 clean, 2 safety refusal, 3 missing psql/pg_dump, 4 up failed, 5 down failed, 6 round-trip dirty.
Usage
export CFN_SCRATCH_DATABASE_URL="postgres://user:pass@localhost:5433/scratch"
$HOME/.claude/skills/cfn-migration-rehearsal/execute.sh \
--up supabase/migrations/0007_orders.up.sql \
--down supabase/migrations/0007_orders.down.sql
Rules
- Never point this at production. The guards exist precisely because a migration rehearsal that hits prod is catastrophic.
- A clean round-trip is necessary but not sufficient. It proves the down reverses the up structurally; it does not prove data is preserved. Data-preservation rehearsal is a separate concern.
- If you cannot provide a scratch database, do not work around the guard. The correct response to "I have no scratch DB" is to provision one, not to disable the check.
- After a real migration lands, run
supabase-schema-sync so the db-query skill's schema context stays current.
Related
cfn-ops - designs the up+down pair and the rollback rehearsal step that this skill executes.supabase-schema-sync - refreshes the schema context after a migration is actually applied.db-query skill - for querying the real DB (this skill deliberately bypasses it because it targets a scratch DB, not DATABASE_URL).
1---2name: cfn-migration-rehearsal3description: Rehearses a DB migration up+down round-trip against an EXPLICIT scratch database only. Applies up, captures schema, applies down, re-captures, and diffs to prove up and down are inverses. Refuses to run without CFN_SCRATCH_DATABASE_URL and refuses anything that looks like prod. Executes what cfn-ops only designs.4---56# CFN Migration Rehearsal78**Purpose:** `cfn-ops` DESIGNS the rollback (the up+down pair and the rollback rehearsal step). This skill EXECUTES that rehearsal: it actually applies the up migration, applies the down migration, and proves the schema round-trips back to its starting state. A down migration that does not fully reverse its up is caught here, before merge, on a disposable database, not in production during an incident.910**Wired by:** this skill is invoked as an executable gate by `cfn-loop-task` Phase 4 (when the `db` build flag is set AND migration files appear in the diff — a hard exit-code gate, not advisory), and it is cited as the executable rollback evidence designed by `cfn-ops` Phase 6 and asserted by `cfn-test-plan` Phase 3 (the `AC-mig` row). It runs only against `CFN_SCRATCH_DATABASE_URL`, never `DATABASE_URL`.1112## SAFETY CONTRACT (read first)1314This skill runs DDL against a real database. Every guard below runs BEFORE any database connection is opened. If any guard trips, the skill exits non-zero with a clear message and touches nothing.1516- **Requires `CFN_SCRATCH_DATABASE_URL`.** Without it, the skill refuses. It never falls back to `DATABASE_URL`, `.env`, or any ambient connection. The scratch database must be explicitly named by you.17- **Refuses prod look-alikes.** It refuses if the scratch URL equals `DATABASE_URL` or `SUPABASE_DIRECT_CONNECTION` (read from `.env` without sourcing it) or the environment `DATABASE_URL`, and refuses any URL pointing at a Supabase pooler endpoint (`*pooler.supabase.com*`, `*.pooler.*`).18- **Never writes unscoped DELETE/TRUNCATE.** The skill itself issues no destructive SQL. It also refuses to run a migration FILE that contains an unscoped `DELETE` or a `TRUNCATE`, per the test-database safety rules.19- **Disposable target only.** Use a throwaway database you can drop and recreate. The rehearsal applies real DDL and rolls it back; if the down is wrong, the scratch DB is left in the post-down state for inspection.2021The scratch database is YOUR responsibility to provision and dispose. A good pattern: a local Postgres container or a dedicated Supabase project used for nothing else.2223## How It Works24251. Run all safety guards. Refuse on any failure.262. Verify `psql` and `pg_dump` are installed (exit non-zero if not).273. Capture the baseline schema snapshot (`s0`) with `pg_dump --schema-only`, normalized (comments/SET/blank lines stripped, sorted).284. Apply the **up** migration with `psql -v ON_ERROR_STOP=1`. On error, stop and report.295. Capture `s1`. Note if up produced no schema change.306. Apply the **down** migration. On error, report that up applied but down failed to roll back.317. Capture `s2`.328. Diff `s0` vs `s2`:33 - identical to baseline -> **ROUND-TRIP CLEAN** (exit 0).34 - any drift -> **ROUND-TRIP DIRTY**, print the diff, exit non-zero. The down does not reverse the up.3536## Inputs3738- `--up <file>` (or first positional): the up migration SQL file. Required.39- `--down <file>` (or second positional): the down migration SQL file. Required. A migration with no down is not rehearsable.40- `CFN_SCRATCH_DATABASE_URL` (env): the disposable scratch database. Required.4142## Outputs4344- Verdict to stdout: `ROUND-TRIP CLEAN` or `ROUND-TRIP DIRTY` with the schema diff.45- Exit codes: `0` clean, `2` safety refusal, `3` missing psql/pg_dump, `4` up failed, `5` down failed, `6` round-trip dirty.4647## Usage4849```bash50export CFN_SCRATCH_DATABASE_URL="postgres://user:pass@localhost:5433/scratch"5152$HOME/.claude/skills/cfn-migration-rehearsal/execute.sh \53 --up supabase/migrations/0007_orders.up.sql \54 --down supabase/migrations/0007_orders.down.sql55```5657## Rules5859- Never point this at production. The guards exist precisely because a migration rehearsal that hits prod is catastrophic.60- A clean round-trip is necessary but not sufficient. It proves the down reverses the up structurally; it does not prove data is preserved. Data-preservation rehearsal is a separate concern.61- If you cannot provide a scratch database, do not work around the guard. The correct response to "I have no scratch DB" is to provision one, not to disable the check.62- After a real migration lands, run `supabase-schema-sync` so the `db-query` skill's schema context stays current.6364## Related6566- `cfn-ops` - designs the up+down pair and the rollback rehearsal step that this skill executes.67- `supabase-schema-sync` - refreshes the schema context after a migration is actually applied.68- `db-query` skill - for querying the real DB (this skill deliberately bypasses it because it targets a scratch DB, not `DATABASE_URL`).
masharratt