Range Day Pro

migration-reset-gate

4 min read

Migration clean-room integrity gate (reset_target)

Summary

An opt-in clean-room integrity hard-gate for re-rehearsing the ETL against the shared staging DB: reset the target to a clean (schema + seeds only) state, then run rangeday db migrate-legacy --entity all --fail-on-integrity, so any integrity violation is a real ETL bug — not a pre-existing staging app row orphaning against the legacy source-maps. Surfaces as a reset_target input on staging-migration-deploy’s run-migration-etl dispatch.

Deferred from the 2026-06-23 integrity-surfacing change (the --fail-on-integrity flag + INTEGRITY_VIOLATIONS=N line + the workflow’s always-warn step already shipped — those kill green-washing). This item is the clean-room hard-gate half, parked behind a real blocker (below). It is wanted for staging validation around Phase 4 — not now.

Requirements

  • In-cluster re-migrate path (the blocker). The migration image (Dockerfile.migration) copies only cli/apps/sync/libs/*/proto and installs no sqlx-cli; the 46 migrations live in apps/server/migrations/ and are embedded only in the server via sqlx::migrate!("./migrations") (apps/server/src/main.rs). rangeday db reset shells to the sqlx binary (cli/src/main.rs), so the image cannot re-migrate at all. Fix: embed apps/server/migrations into the rangeday binary via sqlx::migrate! (a built-in migrator path that does not shell to sqlx-cli) and COPY that dir into Dockerfile.migration — making the image self-sufficient for schema + seed re-application.
  • DROP SCHEMA, not DROP DATABASE. Managed Postgres blocks dropping the connected DB (and the role may lack the privilege), so sqlx database reset (DROP DATABASE) is a no-op trap. Reset via DROP SCHEMA public CASCADE; CREATE SCHEMA public; + re-run migrations (re-applies schema + seeds), or — if the role does not own public — a targeted TRUNCATE of ETL-written tables that preserves seeds and clears legacy_id_map.
  • Fail-loud. The reset step must verify the target is actually clean (assert row counts / seeds-only) and fail the dispatch otherwise — reset_target must never silently no-op (the trap that motivated this item).
  • reset_target dispatch input (default false), labeled unmistakably destructive (it wipes the shared staging app DB) in the input description + a job-summary warning; when true → reset → run with --fail-on-integrity.

Context

Not on the cutover path: at cutover the production target is clean by construction (fresh DB), so --fail-on-integrity is already a meaningful hard gate there with no reset. reset_target exists solely to get a clean room in the shared staging DB for repeat rehearsals. The 2 integrity orphans seen in the 2026-06-23 rehearsal can be confirmed benign by querying those rows directly (no reset needed), and the always-warn keeps the count visible on every run in the meantime.

Details

Dispatch flow when reset_target: true (on the staging run-migration-etl):

  1. Reset the target in-cluster (a step / init using the self-sufficient image): DROP SCHEMA public CASCADE; CREATE SCHEMA public;, then the embedded migrator re-applies schema + seeds. Connect as the target role; never DROP DATABASE.
  2. Verify clean — assert the target holds only seed rows (e.g. 0 scores, 0 hit_factor_entries, empty legacy_id_map) and fail the dispatch otherwise, so a restricted-privilege no-op can never masquerade as a clean room.
  3. Run migrate-legacy --entity all --fail-on-integrity → the binary exits non-zero on any violation → the Job’s failed condition trips the result-gate → the dispatch fails loudly, with the count already surfaced by the always-warn step.

Default (reset_target: false) is unchanged: no reset, no --fail-on-integrity; the always-warn step still reports the count.

Open Questions

  • Does the staging target’s migration role own the public schema (can it DROP SCHEMA public CASCADE)? If not, the reset falls back to the targeted-TRUNCATE variant — confirm with James when this is picked up.
  • Should the embedded-migrator change also replace the sqlx-cli shell-out in rangeday db migrate/rollback (consistency), or only add a self-sufficient reset path?

Cross-ref: e2e squad fixture re-assertion

Migration 0024_e2e_squad_fixture is self-gated on the e2e user existing and runs ONCE per database — any reset flow built here must re-assert that fixture after re-registering the e2e user (re-execute the 0024 body — it is idempotent — or move to boot-time env-gated seeding if resets become routine).

Graph View