cohort_algo Migration Squash Cleanup — babylon

The IMPLEMENTATION of the shared-DB migration-ownership decision on cohort_algo’s side: cohort_algo’s 44 inherited sqlx migrations were squashed into ONE idempotent CREATE TABLE IF NOT EXISTS baseline covering only the 12 tables cohort owns/reads, and the sqlx Migrator was set to ignore_missing(true) so the deployed DB’s 44 now-missing applied versions are tolerated. This closes cohort’s half of [[cohort-algo-shared-db-migrator-ownership|babylon #333]]. Shipped to github.com/wowjeeez/pmv2-cohort-algo main @ a3329dc (subagent-driven with review).

For Agents — state at a glance

• Repo: private github.com/wowjeeez/pmv2-cohort-algo, main @ a3329dc.
• What shipped: all 44 migrations → ONE supabase/migrations/20260624000000_baseline.sql (idempotent CREATE TABLE IF NOT EXISTS for the 12 tables cohort owns/reads only, cumulative deployed schema, RLS preserved) + set_ignore_missing(true) on the sqlx Migrator (sqlx 0.8.6) in db.rs and in the schema test.
• The key realization: cohort is the SOLE sqlx owner of the shared DB (PM uses Supabase, not sqlx). That single fact is why the original [[cohort-algo-shared-db-migrator-ownership|#333]] ask — a separate sqlx history table — was unnecessary; squash + ignore_missing is cleaner and supersedes it (and supersedes the approach in sqlx-shared-migrations-table-trap).
• Validated: the testcontainers + insta schema_snapshot test (golden match on a fresh PG, independently re-confirmed); gate green (310 tests, clippy, fmt); reviewed clean (no DROP-set leak, idempotent, RLS confined to kept tables).
• NOT a prod operation. The actual prod DB surgery (PM migrator first → ~25 pm_* DROPs via psql → _sqlx_migrations reset) is @deploy’s job, out of scope here. Coordinated babylon #380 (resolved) → #397.
• Mode is unchanged. This is schema/migration hygiene; cohort remains OFF.

The problem — cohort is the sole sqlx owner of a shared DB it didn’t fully own

cohort_algo inherited the 44 sqlx migrations of the retiring polymarket_fetch monorepo (see polymarket-fetch). That inherited set contained two classes of table that cohort does not own:

1. The pmv2_autotrade_* tables — now owned by position_manager per the [[cohort-algo-shared-db-migrator-ownership|#333/#331 ownership decision]] (PM is the execution authority). cohort only READS pmv2_autotrade_config + pmv2_autotrade_sources; it never touches pmv2_autotrade_orders.
2. ~28 dead legacy pm_* tables — leftovers from the monorepo with no reader in cohort_algo.

The trap: PM uses Supabase, NOT sqlx → cohort is the ONLY sqlx migrator on this DB

position_manager does not run sqlx::migrate! at all (it reads/writes via Supabase). So on the shared Postgres database, cohort_algo is the sole owner of the default _sqlx_migrations table. That changes the whole calculus: there is no second sqlx migrator to collide with, so the “one migrator per database” cross-trip (VersionMissing) can’t even arise from PM. The remaining hazard is purely cohort-internal: editing the inherited migration files in place to remove the dropped tables would change their stored checksums and throw VersionMismatch against the deployed DB where those 44 versions already applied — bricking boot. (This is failure mode #3 in sqlx-shared-migrations-table-trap.)

The solution — squash to an idempotent baseline + ignore_missing

Two moves, together:

1. Squash 44 → ONE idempotent baseline

supabase/migrations/20260624000000_baseline.sql replaces all 44 prior migration files. It is:

• CREATE TABLE IF NOT EXISTS for only the 12 tables cohort owns or reads (see table below).
• The cumulative deployed schema — every ALTER that the 44 migrations layered on over time is folded into the CREATE so a fresh DB lands at the exact deployed shape in one step.
• RLS policies preserved for the kept tables.

The 12 tables in the baseline (everything else dropped from cohort's set)

9 pmv2_* producer tables cohort owns/reads + 3 kept pm_* that still have cohort readers:

Kept pm_* (3)	Why kept
pm_scheduled_runs	cohort’s scheduled-run gating
pm_category_consensus_rows	category-consensus output (see category-consensus)
pm_category_consensus_runs	category-consensus run gating

Dropped from cohort’s migration set:

• pmv2_autotrade_config / pmv2_autotrade_sources / pmv2_autotrade_orders — now PM’s. (See the safety note below on why cohort’s READS survive.)
• ~25 dead pm_* — no cohort reader.

Why dropping the pmv2_autotrade_* DDL is safe for cohort's READS

cohort READS pmv2_autotrade_config + pmv2_autotrade_sources and must keep reading them. Dropping their DDL from cohort’s baseline is safe because PM’s migrator (4712de9) recreates them byte-identical — so on a fresh DB the tables still exist for cohort to read; on the deployed DB they already exist. cohort’s read surface is intact either way. This is exactly the [[cohort-algo-shared-db-migrator-ownership|#333]] “read-only precondition” contract made physical.

2. set_ignore_missing(true) on the sqlx Migrator

set_ignore_missing(true) is THE technique for editing a deployed sqlx migration set

When you squash away migration files that have already applied on a deployed DB, that DB’s _sqlx_migrations table still has rows for all 44 old versions — versions the new (squashed) source no longer knows about. By default sqlx’s validate_applied_migrations (ignore_missing = false) treats those unrecognized version rows as a fatal inconsistency and fails boot (VersionMissing). Calling set_ignore_missing(true) on the Migrator (sqlx 0.8.6) tells sqlx to tolerate applied versions it has no source file for — which is precisely the post-squash state. Set in db.rs (the runtime Migrator) and in the schema_snapshot test (so the test exercises the same config).

This is the general, reusable lever for editing a deployed sqlx migration set without checksum/version errors — squash the files, then ignore_missing(true) so the now-orphaned applied rows are accepted.

How the two paths resolve

• Fresh DB (e.g. CI testcontainer, a new environment): _sqlx_migrations is empty → the single baseline runs → all 12 tables created at deployed shape. ignore_missing is a no-op here (nothing missing).
• Deployed DB: all 44 old versions are already applied; the baseline’s version (20260624000000) is newer, but its DDL is IF NOT EXISTS so it’s a safe no-op against tables that already exist; ignore_missing(true) swallows the 44 orphaned applied rows so boot succeeds.

Validation

schema_snapshot golden match on a fresh PG — independently re-confirmed

The squash was validated with the testcontainers + insta schema_snapshot test: spin a fresh Postgres, run the baseline, snapshot the resulting schema, and assert it matches the golden. It passed, and was independently re-confirmed. Gate is green — 310 tests, clippy, fmt. The review pass found it clean: no leak of dropped tables into the DROP set, the baseline is idempotent, and RLS is confined to the kept tables.

schema_snapshot coverage gap — validates COLUMNS only, and omits 2 kept tables

The schema_snapshot test asserts columns only — it does not verify constraints, indexes, or RLS policies. It also omits 2 of the kept tables from its coverage. So a golden match proves the column shape of the baseline matches the deployed schema, but does not prove constraint/index/RLS parity, and leaves 2 kept tables unsnapshotted. Treat the golden as a strong column-level signal, not a total-schema proof. (Documented here as a known coverage gap, not a blocker — the prod surgery below is transcript-first and operator-gated regardless.)

Out of scope — the prod DB surgery is @deploy’s job

cohort did NOT touch the prod database — this is @deploy / operator prod-ops

The squash is a migration-source change. The actual reconciliation of the deployed DB is a coordinated prod operation owned by @deploy, sequenced strictly:

1. PM’s migrator runs FIRST — stands up / confirms the pmv2_autotrade_* family it now owns (4712de9).
2. Then ~25 pm_* DROPs via psql — BEGIN/COMMIT, transcript-first (capture the plan before executing); PM confirmed zero refs to these tables.
3. Then reset _sqlx_migrations to the baseline (so the deployed bookkeeping matches the squashed source and ignore_missing is no longer load-bearing going forward).

Coordination ran on babylon #380 (resolved) → #397. Per the no-direct-prod-SSH rule, cohort does not run any of this — it’s handed to @deploy.

Reusable lessons

Lesson 1 — "sole sqlx owner" makes a separate history table unnecessary

The original [[cohort-algo-shared-db-migrator-ownership|#333]] plan (and the “one migrator per (distinct) history table” refinement) assumed two sqlx migrators would share the DB and needed isolated history tables to avoid the VersionMissing cross-trip. In reality PM uses Supabase, not sqlx, so cohort is the only sqlx migrator. With a single sqlx owner there is no cross-migrator collision to design around — squash + ignore_missing is the cleaner solution, and it supersedes the distinct-history-table approach for this DB. Verify the actual number of sqlx migrators before reaching for history-table isolation.

Lesson 2 — set_ignore_missing(true) = edit a deployed sqlx set safely

To change an already-deployed sqlx migration set (squash, prune, re-baseline) without the checksum (VersionMismatch) and missing-version (VersionMissing) errors: replace the files with an idempotent baseline and set ignore_missing(true) on the Migrator. The orphaned applied rows on deployed DBs are then tolerated. This is the practical escape hatch from the immutable-applied-migration rule when you genuinely must restructure history.

Lesson 3 — schema_snapshot tests prove columns, not the whole schema

A schema_snapshot/golden test that diffs column shape is necessary but not sufficient: it silently ignores constraints, indexes, and RLS, and can omit tables (here, 2 kept tables). When relying on such a test to validate a migration squash, remember what it does NOT cover and gate the irreversible prod step on a transcript + operator review, not the golden alone.

Related

• cohort-algo-shared-db-migrator-ownership — the DECISION this note IMPLEMENTS (babylon 331: PM owns the FULL pmv2_autotrade_* migrator, cohort DROPs that DDL + treats the tables as a read-only precondition). The decision proposed a distinct sqlx history table; this implementation found cohort is the sole sqlx owner and used squash + ignore_missing instead — which resolves/supersedes that ask. Also closes the “prune ~28 dead pm_*” action item from that note.
• sqlx-shared-migrations-table-trap — the gotcha note this work supersedes for cohort’s DB: the “one migrator per (distinct) history table” framing assumed two sqlx migrators; with a single sqlx owner the simpler answer is squash + set_ignore_missing(true) (which is also the documented escape hatch from that note’s “never edit an applied migration” rule, used deliberately here)
• cohort-algo-component — cohort_algo’s standing architecture; the “Shared DB surface” section (the 12 tables cohort owns/reads) is what the baseline now codifies
• cohort-pre-live-signal-gating — the sibling pre-live work; its min_round_trip_edge_bps “loaded-but-unused” decision is downstream of the same pmv2_autotrade_config schema FREEZE (you can’t drop a column from a PM-owned, frozen table — you just stop reading it)
• cohort-algo-v2-wire-contract-cutover — the v2 wire-contract cutover; the prior a3329dc-era cohort milestone in the same arming sequence
• position-manager-interface-design — the EXECUTOR side; PM owns the pmv2_autotrade_* migrator (4712de9) that recreates the dropped tables byte-identical, which is why cohort’s reads survive the DROP
• position-manager-execution-migration-implemented — PM Plan 1 outcome; confirms PM’s Supabase (not sqlx) path, the fact that makes cohort the sole sqlx owner
• polymarket-fetch — the retiring monorepo whose 44 migrations + pm_* tables cohort inherited