Why
The provision_workspace_membership trigger migration (PR #60) passed the 5-agent review and the existing CI, then failed at prod apply with:
ERROR: 42P17: INSERT trigger's WHEN condition cannot reference OLD values
Fixed forward in f3920ab (moved the company_id-changed guard into the function body, gated by TG_OP = 'UPDATE'). The point: this class of error — anything that only fails when the SQL hits a real Postgres (DDL validation, missing column/table, bad FK/CHECK/type, index violation) — is invisible to static review and to our current CI (Deno type-check + offline pytest). The only net that caught it was the manual prod apply. We should have a net before prod.
The constraint that shapes the design
The prod DB zgkvbjqlvebttbnkklpo is shared (hal + edifice). This repo holds only its own halcrm_* migrations — a partial set. On top of that, auth.users (which this trigger binds to) is created by Supabase's own auth service, not by any migration file in either repo. So:
- Replaying only hal's 19 migrations on a blank Postgres does not reconstruct prod.
supabase db start / db reset does not work cleanly here (partial ledger, no edifice schema, db push is already banned — see CLAUDE.md §Migrations).
Any ephemeral-DB approach has to sidestep the partial ledger.
Recommended approach — committed schema snapshot + ephemeral Postgres in CI
- One-time (+ refresh on material schema changes): dump the real prod schema (no data) to a committed file:
supabase db dump --schema auth,public -f supabase/schema.snapshot.sql # auth as renaud@bluegreen.ai
- New CI job
migration-smoke with a postgres service container (pin the same major as prod — verify via select version()):
psql -f supabase/schema.snapshot.sql — load the prod schema into the ephemeral DBpsql -f the pending migration(s) under supabase/migrations/ — apply on top- assert exit 0
- Refresh
supabase/schema.snapshot.sql when the schema changes materially (slow-moving; a re-dump alongside each DDL migration is enough).
Catches: 42P17, missing column/table, bad FK/CHECK/type, index/constraint violations — everything that only fails against a real DB. Generalizes to edifice too (same shared DB).
Minimal alternative (cheaper, lower fidelity)
A tests/fixtures/pg_bootstrap.sql that stubs only what a migration binds to (auth.users(id, raw_app_meta_data jsonb, …) + workspace_members + halcrm_workspaces + the workspace_members_one_default_per_user partial unique index). CI: bootstrap → migration → assert. Dead simple, would have caught this 42P17 — but the stub drifts from the real schema and only covers touched objects.
Honest limitation
The snapshot captures tables/constraints/triggers but not Supabase's internal GoTrue auth migrations. For DDL on auth.users (our case) this is inconsequential; flagging it in case a future migration depends on fine auth-service behavior.
Acceptance criteria
Links
Priority: P2.
Why
The
provision_workspace_membershiptrigger migration (PR #60) passed the 5-agent review and the existing CI, then failed at prod apply with:Fixed forward in
f3920ab(moved the company_id-changed guard into the function body, gated byTG_OP = 'UPDATE'). The point: this class of error — anything that only fails when the SQL hits a real Postgres (DDL validation, missing column/table, bad FK/CHECK/type, index violation) — is invisible to static review and to our current CI (Deno type-check + offline pytest). The only net that caught it was the manual prod apply. We should have a net before prod.The constraint that shapes the design
The prod DB
zgkvbjqlvebttbnkklpois shared (hal + edifice). This repo holds only its ownhalcrm_*migrations — a partial set. On top of that,auth.users(which this trigger binds to) is created by Supabase's own auth service, not by any migration file in either repo. So:supabase db start/db resetdoes not work cleanly here (partial ledger, no edifice schema,db pushis already banned — see CLAUDE.md §Migrations).Any ephemeral-DB approach has to sidestep the partial ledger.
Recommended approach — committed schema snapshot + ephemeral Postgres in CI
supabase db dump --schema auth,public -f supabase/schema.snapshot.sql # auth as renaud@bluegreen.aimigration-smokewith apostgresservice container (pin the same major as prod — verify viaselect version()):psql -f supabase/schema.snapshot.sql— load the prod schema into the ephemeral DBpsql -fthe pending migration(s) undersupabase/migrations/— apply on topsupabase/schema.snapshot.sqlwhen the schema changes materially (slow-moving; a re-dump alongside each DDL migration is enough).Catches:
42P17, missing column/table, bad FK/CHECK/type, index/constraint violations — everything that only fails against a real DB. Generalizes to edifice too (same shared DB).Minimal alternative (cheaper, lower fidelity)
A
tests/fixtures/pg_bootstrap.sqlthat stubs only what a migration binds to (auth.users(id, raw_app_meta_data jsonb, …)+workspace_members+halcrm_workspaces+ theworkspace_members_one_default_per_userpartial unique index). CI: bootstrap → migration → assert. Dead simple, would have caught this42P17— but the stub drifts from the real schema and only covers touched objects.Honest limitation
The snapshot captures tables/constraints/triggers but not Supabase's internal GoTrue auth migrations. For DDL on
auth.users(our case) this is inconsequential; flagging it in case a future migration depends on fine auth-service behavior.Acceptance criteria
supabase/schema.snapshot.sqlcommitted (schemasauth+public, schema-only) with a documented refresh procedure.migration-smokeloads the snapshot into an ephemeral Postgres (major pinned to prod) and applies the newest migration, failing the build on any SQL error.WHEN (OLD…)form makes the job go red.docs/operations.mdnote pointing to the snapshot refresh step.Links
f3920ab.Priority: P2.