Supabase Migration Strategy

This document defines how Barbelic moved from manually-run patch SQL to tracked Supabase migrations. Those files now live under supabase/legacy/, each carrying a do-not-apply banner (2026-08-20 RPC surface audit, SCR-4).

Schema Snapshot (2026-09-04, issue #1252)

supabase/schema.sql is the state of the database after every file in supabase/migrations/ has been applied to an empty database — a dump, not a transcript. It is generated by scripts/migrations/build-schema-snapshot.mjs (npm run schema:snapshot) and never edited by hand.

Why: between the second baseline (2026-08-21) and 2026-09-04 the file was rebuilt as the migrations concatenated in version order. That made it a history again — 152 files, 8.2 MB, 359 function definitions (37% of the text) that a later migration had already replaced, while the README promised "the current state". The snapshot below cannot regress into a transcript: it is produced from a replayed database, and three checks keep it honest.

What the file contains, in this order:

1. Header — the generator name and the input fingerprint: a SHA-256 over every migration file name and content (LF-normalised). migrations-fingerprint ties the snapshot to the exact set of migrations it was built from.
2. Schema — pg_dump --schema-only --schema=public --quote-all-identifiers --no-owner of the replayed database, rewritten into the idempotent forms the second baseline used (CREATE TABLE IF NOT EXISTS, CREATE [UNIQUE] INDEX IF NOT EXISTS, CREATE OR REPLACE FUNCTION/TRIGGER/VIEW, DROP POLICY IF EXISTS before each CREATE POLICY, DROP CONSTRAINT IF EXISTS before each ADD CONSTRAINT). Dump comments (-- Name: …) and \restrict lines are removed; -- comments inside function bodies are kept. Extensions come from pg_extension (all but plpgsql).
3. Objects a public-schema dump cannot carry — auth.users triggers, storage.buckets rows, storage.objects policies, cron.job rows, and the default-privilege state of schema public (from pg_default_acl).
4. Reference data — rows of the tables listed in supabase/contracts/reference-data-tables.json, one insert … on conflict do nothing per table, rows ordered by primary key (or by every emitted column when the primary key itself is omitted, e.g. exercise_synonyms.id), every literal cast to its column type. Columns whose value the database assigns at replay time are omitted because it differs on every replay: a default that is a clock or random function (now(), clock_timestamp(), gen_random_uuid(), uuid_generate_v4()), a sequence (nextval(…)) or an identity column (exercise_strength_standards.id — the seed inserts select … from jsonb rows without an order, so which row gets which number differs between Linux CI and Windows); so is any timestamp column that holds a replay-time value in at least one row (a migration wrote now() into it, e.g. exercise_strength_standards.cutlines_generated_at) — "replay time" is the earliest value among the now()-defaulted columns. Any table outside the list that holds rows after a clean replay fails the build — the list is the authority on what counts as reference data; user and operational tables never appear.

The generator builds the text twice from the same database and refuses to write unless both runs are byte-identical, so a non-deterministic dump order shows up as a build failure instead of as churn in the file.

Three checks:

Check	Where it runs	What it proves
tests/react/schemaSnapshotContract.test.mjs	npm test (no Docker)	the header fingerprint equals the fingerprint of the current supabase/migrations/; the file has no CRLF; no function signature is defined twice
build-schema-snapshot.mjs --check	CI migration-smoke right after supabase db reset, npm run ci:local db step, npm run db:preflight	regenerating from the replayed database reproduces the committed file byte for byte
npm run migrations:check / migrations:renumber	landing	the fingerprint header follows a renumber (a rename changes file names and self-headers, so the fingerprint is recomputed and rewritten — the body does not change)

Workflow after adding or changing a migration: start the sandbox stack (docs/process/ci-local.md), then npm run schema:snapshot -- --sandbox <dir> --reset (replays the migrations there and rewrites schema.sql), commit both. npm run ci:local will refuse a snapshot that no longer matches.

SQL contract tests keep reading the snapshot through tests/support/schemaSql.mjs (functionBody, policiesFor, seededTable, …); the derived privilege ledger and the migration-idiom rendering it provides are unchanged. Migration history lives in supabase/migrations/ and in git; the snapshot carries none of it.

Domain Sources (2026-09-07, issue #1283, v0.18.0 D03)

The snapshot is generated text nobody edits, and migrations are immutable history — so until D03 there was no file a person could open to read and change the current definition of one function. supabase/definitions/ is that file set: sections 1 and 2 of the snapshot split into one file per object (<domain>/functions/<name>.sql, tables/<table>.sql with its constraints, indexes, policies, triggers and grants, views/, and six db-platform/platform/ files for extensions, schema settings, default privileges, auth.users triggers, storage and cron). The text is the dump text verbatim; overloaded functions get separate files named by argument types. rules.json assigns every object to a domain (first matching rule wins; an unmatched object fails the extraction), registry.json lists every object by qualified signature with its layer (door / engine / core / trigger), security, search_path, privileges, dependencies and file, and exceptions.json is the closed list of layer-rule violations found at the first extraction (24, all call-direction).

Four things are kept equal, and each link has its own check:

Equality	Check
migrations → replayed DB	CI migration-smoke, npm run ci:local
replayed DB → schema.sql	build-schema-snapshot --check
schema.sql → supabase/definitions/** + registry.json	npm run sql:check, tests/react/sqlDefinitionsContract.test.mjs (statement multiset equality, no Docker)
layer rules ↔ exceptions.json	same test — a new violation fails, and so does a stale exception

Changing a function therefore means: edit its file → npm run sql:candidate -- --slug <name> (writes one migration with exactly the difference: full function bodies, drop policy if exists + create policy, grant/revoke; table, column, index, constraint and data changes are never guessed and must be passed as explicit SQL with --ddl <file>) → npm run schema:snapshot -- --sandbox <dir> --reset → npm run sql:extract (registry) → npm run sql:check. After a rebase, npm run sql:extract brings the sources back to the snapshot in seconds. Canon: sql-definitions.md.

Populated-DB Upgrade (2026-09-07, issue #1287, v0.18.0 D13)

An empty replay proves syntax and order. It says nothing about what the same file does to a database that already holds user data: how long it locks, whether it rewrites the table, how much WAL it writes, what happens when it dies halfway, and whether the previous release's app keeps working while it runs. D13 adds the three pieces that answer those questions, and wires them into the existing landing gates. Canon: populated-db-upgrade.md.

Piece	Command	What it produces
Risk classification	npm run migrations:risk -- --write	Per-statement class on six axes (lock mode, table rewrite, full scan, WAL/disk, re-run safety, old-app coexistence) and one machine-checked header line, -- migration-risk: v1 level=… classes=… fingerprint=…, whose fingerprint hashes the executable statements so a renumber keeps it and an edit stales it
Upgrade harness	npm run db:upgrade -- --from <previous release> --sandbox <dir> --fixture user-data.sql	Builds the previous release's schema in an isolated DB, loads a fixture (the daily user-data copy or a G04 workload), digests facts / canonical ids / parent-child edges / source identity, applies the pending migrations one file per transaction while a second session probes old-app RPCs and a third samples lock waits, optionally aborts one file mid-way and re-applies it, digests again, diffs the two refs' schema.sql for coexistence breaks, and writes upgrade-evidence.json plus the -- upgrade-evidence: line
Backfill primitive	npm run db:backfill -- --spec <spec.json>	Keyset-cursor batches, one transaction each under lock_timeout, a checkpoint file for resume, an advisory lock against a second runner, and an idempotent predicate so no row is touched twice — no database object is created

Gates: check:migrations requires the header on every migration numbered after the rule's introduction tail and, for level=high, an evidence line; db:preflight and landing:lock acquire additionally require that the evidence came from the Supabase sandbox (env=supabase-sandbox). Low-risk files (functions, policies, grants, new tables) need the header and nothing else. A bare-Postgres shim (env=bare-postgres-shim) exists for validating the tools where Docker is unavailable; its evidence is never accepted for landing.

Second Baseline (2026-08-21)

supabase/migrations/20260821000000_baseline_v2.sql replaced the 125 migrations from 20260622000100 to 20260820250000 with one file. It records the state those migrations produced; it does not replay them.

Why: a local or CI reset ran 125 files in sequence, and schema.sql had grown into a 70k-line transcript of the whole history — including statements that later statements undid, and 40-odd places where the text that actually ran was computed at migration time rather than written in the file. A new contributor could not read the drawing and know the database.

What the squash is, precisely:

• No SQL runs against Production. The only Production contact is rewriting the ledger (supabase_migrations.schema_migrations) with supabase migration repair, which is metadata. Zero downtime, no user data touched.
• The baseline body came from a Production dump, rewritten to be idempotent (create index if not exists, drop policy if exists before create policy, drop constraint if exists before add constraint).
• Reference data — the canonical catalog, archetypes, strength standards, estimation policy versions — is seeded with the row set a fresh replay produces and the values Production holds.
• Six blocks a dump cannot carry are declared by hand: the two auth.users triggers, three storage buckets and their object policies, four pg_cron jobs, the default-privilege revokes, and the policy provenance markers.
• The history is sealed, not deleted. Every original migration is at the git tag pre-squash-v2.

Parity evidence, measured before landing: a fresh database replaying only the baseline differs from the Production dump in 2 statements out of 1,952, both of them PostgreSQL's own parenthesisation of an existing CHECK. Object counts (61 tables, 227 functions, 102 policies, 208 indexes), the four cron jobs, the three buckets, the two auth triggers, the default ACLs, the row counts of all 15 reference tables, and the md5 of the canonical catalog all match Production. A fresh replay holds zero user rows.

Drift the squash exposed

The comparison found ten differences between Production and a full replay of the migrations. They existed before this work and were invisible because check:remote-schema counts only missing objects as failures, and never looked at constraint names, on delete behaviour, column order, or comments. The baseline records Production, so all ten stop being drift the moment it lands — but four of them describe objects nobody meant to keep, and they are listed in the landing PR as follow-up removals:

• public.sets — a table no migration creates, still alive in Production with four RLS policies, left over from the sets → exercise_sets rename.
• Two indexes on user_exercise_pr_records still carrying pre-rename names.
• Seven constraints holding pre-rename names (movements_pkey, movement_external_mappings_*, and others), because alter table ... rename does not rename constraints.
• exercise_archetypes.note, a column no migration adds.
• exercises_archetype_id_fkey — same name, different behaviour: on delete set null in a replay, no action in Production. This one is a real semantic difference and needs a decision, not just a cleanup.
• Column order differs on 8 tables (66 columns), because Production added them with add column where a fresh replay declares them in create table.
• import_wodup_batch_to_canonical_engine(uuid) is executable by service_role in Production and by nobody in a replay. 20260820130000_import_chain_verbatim_collapse.sql revokes it from public, anon, authenticated where its two sibling helpers (refresh_user_session_timing_stats_from, canonical_seoul_report_as_of_v1) also name service_role. A hosted project's default privileges grant service_role execute on public functions, so the omission leaves the internal import engine reachable by the secret key; the local CLI stack has no such default privilege, which is why every local run was green. This is a real gap, not a rendering difference, and needs a revoke — not a baseline edit, because the baseline records Production. Re-measured at regeneration (2026-08-21): the same shape holds for three more engines (stage_wodup_import_batch_engine, update_completed_session_v4_engine, delete_completed_session_v4_engine — the latter two were dropped by issue #1215 on 2026-09-04; save_session_v5_engine / delete_session_v5_engine replaced them), and main's internal_surface_grant_hygiene.test.sql now asserts against it, so the repayment ships with the squash as 20260821000100_engine_grant_parity.sql.
• Two functions are wider in Production than in a replay: refresh_wodup_complex_interpretations_v1() also grants authenticated, and training_effective_load(numeric,numeric,numeric) also grants anon. Both are a decision, not a cleanup.

What it changed for tests

schema.sql is no longer append-only, so a contract can no longer assert against text that a later migration removed. That form of rot was real: 61 assertions in tests/react/schemaRls.test.mjs were passing against objects that had been dropped, including a trigger removed by the measured-PR cutover.

SQL contracts now read state through tests/support/schemaSql.mjs, which narrows to one object before matching, renders the dump back into this repo's SQL idiom, and derives the effective privileges as the grant/revoke pairs the migrations used to write. The three hand-maintained tail locks were replaced by one generic contract — schema.sql equals the migrations concatenated in version order — which is exactly the rule that let the file grow back into a transcript; it was retired on 2026-09-04 by the Schema Snapshot section above (fingerprint header + build-schema-snapshot --check).

First Baseline (2026-06-22, historical record)

The sections below record the first baseline and how it was adopted. They are kept because the ledger repair procedure and the idempotency rules still apply; the four-file split they describe was folded into the second baseline.

Current State

• Production Supabase project: kobxeylancdimqhfkbnl
• The production DB already has the important patch objects applied.
• supabase_migrations.schema_migrations was not available/visible during the audit, so the DB has objects but does not have reliable migration history.

Transition Rule

1. Do not rerun old patch SQL files against production.
2. Treat the current supabase/schema.sql state as the baseline.
3. Register the baseline as applied in production after comparing it with the live DB.
4. Use supabase/migrations/*.sql for every DB change after the baseline.
5. Keep supabase/legacy/*.sql as historical references only; never apply them.

Baseline Migration Split

The baseline is intentionally split by dependency order instead of copying schema.sql into one giant migration.

Order	File	Role
1	supabase/migrations/20260622000100_lift_guild_baseline_schema.sql	Extensions, storage bucket config, reference data, tables, columns, constraints, and indexes
2	supabase/migrations/20260622000110_lift_guild_baseline_operations.sql	Import pipeline functions, stats refresh functions, validation, and repair utilities
3	supabase/migrations/20260622000120_lift_guild_baseline_functions.sql	App-facing functions and screen RPCs
4	supabase/migrations/20260622000130_lift_guild_baseline_permissions.sql	Grants, revokes, RLS enablement, and policies

This order keeps dependencies explicit:

• Tables exist before functions reference them.
• Operational functions exist before app write RPCs that call them.
• Functions exist before grants and RLS policies reference them.
• Operational utilities are isolated from the app-facing RPC contract.
• Permission changes are last, so object ownership and access are easy to review.

Legacy Patch Scope Covered By The Baseline

The baseline represents the final applied state of these legacy patches:

• rename_movement_to_exercise_patch.sql
• daily_conditions_patch.sql
• exercises_patch.sql
• exercises_admin_patch.sql
• wodup_exercises_patch.sql
• exercise_external_mappings_patch.sql
• user_exercise_stats_patch.sql
• write_rpc_patch.sql
• private_user_data_rls_patch.sql
• planned_sessions_rls_fix.sql
• wodup_import_batches_patch.sql
• wodup_import_staging_patch.sql
• wodup_import_canonical_patch.sql
• user_exercise_stats_integrity_patch.sql
• wodup_placeholder_resolution_patch.sql
• app_screen_rpc_patch.sql

The following files were operational support scripts, not baseline migrations. The wodup training-complex preview/confirm pair is spent (its one-shot operations ran in 2026-08) and now sits in supabase/legacy/ with the rest:

• legacy/*_preview.sql
• legacy/*_confirm.sql
• wodup_exercises_mapping_import_20260615/*
• seed_dummy_account.sql

Production Adoption

Production already has the baseline objects. Therefore production adoption is:

1. Generate the baseline migration files.
2. Verify the baseline against a fresh local DB.
3. Compare production objects with the baseline result.
4. Mark the four baseline migrations as applied in production without rerunning their SQL.
5. Apply only later migrations with Supabase migration tooling.

Production Baseline Repair Log

Applied on 2026-06-22 against linked project kobxeylancdimqhfkbnl.

The four baseline versions were marked as applied without rerunning SQL:

npx supabase migration repair --linked --status applied \
  20260622000100 \
  20260622000110 \
  20260622000120 \
  20260622000130

Verification:

npx supabase migration list --linked
npx supabase db push --linked --dry-run

Result: local and remote migration histories match, and dry-run reports Remote database is up to date.

Baseline Validation Log

Validation run on 2026-06-22.

Local fresh DB validation could not be completed in this Windows workspace because Docker was not available/running for the Supabase local stack:

• docker was not available on PATH.
• npx supabase status and npx supabase db dump could not connect to the Docker engine.

Remote validation was completed with a catalog-based check instead:

npm run check:remote-schema

Initial remote catalog comparison found four missing baseline indexes:

• sessions_user_date_status_idx
• planned_sessions_user_date_idx
• planned_sessions_status_idx
• planned_sets_planned_session_id_idx

They were restored with:

• supabase/migrations/20260622000200_restore_missing_baseline_indexes.sql

After applying that migration:

• npx supabase migration list --linked showed local/remote history aligned through 20260622000200.
• npm run check:remote-schema reported missing_count: 0.

The remote DB still has extra legacy objects that are not part of the baseline, including public.sets and related policies. They were not removed during this baseline validation because cleanup should be handled by a separate preview/confirm operational plan.

Future Migration Rules

• Prefer idempotent DDL where possible:
  • create table if not exists
  • alter table ... add column if not exists
  • create index if not exists
  • create extension if not exists
  • create or replace function
• RLS policies are not naturally idempotent. Use a safe drop policy if exists
  • create policy pattern or an equivalent guarded block.
• add constraint is not naturally idempotent. Guard it with a catalog check, drop constraint if exists, or exception when duplicate_object then null.
• Explicitly include permission changes in the same migration:
  • revoke all ... from public
  • revoke all ... from anon
  • grant only the minimum needed roles.
• Screen RPCs should be treated as BFF contracts. Frontend code should depend on RPC response shapes, not on raw table layouts.
• Data repair scripts should stay separate as preview/confirm operational SQL unless they are a permanent schema change.
• Run npm run check:migrations before opening or updating DB migration PRs.

Ledger Repair For The Second Baseline

The second baseline is adopted the same way the first was, with the reverse step added: the replaced versions are marked reverted, and the new one is marked applied. No SQL runs; this rewrites metadata only.

supabase migration repair --status reverted <every replaced version>
supabase migration repair --status applied 20260821000000

Verify with supabase migration list --linked -- local and remote must line up 1:1 with no row present in only one column -- and npm run check:remote-schema, which must exit 0.

Until that repair runs, validateRemoteReleaseContract compares the local version list against the remote ledger and reports a mismatch. That is the expected state between landing and repair, and it does not block CI: the contract is not part of the npm run check chain.

Rollback is symmetric -- repair the old versions back to applied, the baseline to reverted, and revert the commit. Production schema is never touched by this procedure, so there is no data risk.

Two conditions must hold before starting, both learned the hard way:

• The remote ledger and the local migration list must have the same count. Production is routinely ahead of main for a few minutes, because a migration is pushed before its PR merges. A baseline generated inside that window carries schema that has not landed yet.
• supabase migration list --linked must show no local-only rows other than the baseline itself. A migration that failed to apply sits at the head of the queue and blocks every later push.

Next Steps

1. Validate the generated baseline migrations on a fresh local database when a Docker-enabled Supabase local stack is available.
2. Keep worker entry points deployable and cron-ready. Wodup import now uses wodup-start-import for enqueue and wodup-process-import-jobs for heavy processing. Stats refresh uses stats-process-refresh-jobs, which calls process_user_exercise_stats_refresh_jobs(...).
3. Harden app screen RPC contracts and response-size guardrails.
4. Remove or archive obsolete manual patch references only after all tests and docs no longer depend on them.