Apply the transactions INSERT lockdown to cloud and make migration drift detectable

[guillermoscript]

Part of EPIC #540. §1.1.

What's wrong

The transactions INSERT lockdown shipped in #538 / PR #539 is not applied to the cloud project. Verified by query against cloud:

select has_table_privilege('authenticated','public.transactions','INSERT');  -- true
select policyname, with_check from pg_policies
 where tablename='transactions' and cmd='INSERT';
-- "Users can create own transactions"
-- ((SELECT auth.uid()) = user_id) AND (tenant_id = (SELECT get_tenant_id())) AND (status = 'pending'::transaction_status)

That is the #528 policy shape. supabase/migrations/20260725180000_transactions_insert_lockdown.sql does two things that are both absent on cloud:

• :116 — REVOKE INSERT ON TABLE public.transactions FROM authenticated, anon
• :150-158 — pins settlement_base, settlement_currency, settlement_rate, settlement_quoted_at to IS NULL on the INSERT policy

So the scenario #538 was filed for is open in production. A caller opens a pending row quoting settlement_base: 1 against a $49 product, pays that amount on-chain, and /api/payments/solana/verify — which verifies the transfer against the row's own settlement_base (lib/payments/solana-reconcile.ts:104) — flips it to successful on the service-role client and grants the entitlement. The same grant leaves amount caller-controlled, and getPayoutsOwed sums amount into real payout liability.

PR #539 already moved every legitimate insert to createAdminClient(), so nothing depends on the grant. It is pure attack surface.

Root cause — why it got lost

Cloud migration stamps have drifted from the repo filenames. Cloud supabase_migrations.schema_migrations holds:

20260724120000, 20260724130000, 20260724140000, 20260724150000, 20260725100000,
20260725192946, 20260725213441, 20260725213508, 20260725213610

The repo holds 20260725110000_transaction_split_snapshot_backstop, 20260725160000_entitlement_gated_enrollment_inserts, 20260725170000_transactions_column_hardening, 20260725180000_transactions_insert_lockdown. The content of three of those was applied ad hoc via the MCP apply_migration tool, which stamps a fresh timestamp, instead of via supabase db push. The fourth was never applied at all.

Consequence: schema_migrations cannot answer "what is missing from cloud". Comparing the local supabase/migrations/ listing against it produces false positives for the re-stamped ones and hides the genuinely-missing one. That is exactly how 20260725180000 was lost — and #500 and the epic's §3.1 note record the same class of gap twice before.

Scope

In scope

• Apply supabase/migrations/20260725180000_transactions_insert_lockdown.sql to cloud.
• Reconcile the four drifted stamps so schema_migrations reflects reality (repair the ledger; do not re-run already-applied DDL).
• Add a check that fails loudly on drift — a scripts/verify-cloud-schema.ts (or a supabase/snippets/ SQL file wired into a documented step) asserting at minimum:
  • has_table_privilege('authenticated','public.transactions','INSERT') is false
  • the INSERT policy's with_check contains all four settlement_* NULL pins
  • the authenticated UPDATE column grant is exactly (status, provider_subscription_id, stripe_payment_intent_id)
  • before_transaction_split_snapshot_insert / _update triggers exist and are enabled
• Document the rule in docs/MIGRATIONS.md: cloud changes go through supabase db push, never through ad-hoc apply_migration, precisely because the latter breaks drift detection.

Out of scope

• Any change to the migration's SQL. It is correct; it was simply never run.
• The 20260725110000 / 160000 / 170000 content — already live on cloud under different stamps, confirmed by querying the grants, policies and pg_trigger.

Acceptance criteria

• select has_table_privilege('authenticated','public.transactions','INSERT') returns false on cloud.
• Cloud's INSERT policy with_check includes the four settlement_* IS NULL pins.
• The drift check exists, is runnable in one command, and fails if you manually re-grant INSERT (prove it by trying, then reverting).
• npm run test:unit stays green (370/370 baseline at 146a8cfa).
• A dry run of supabase db push against cloud reports no pending migrations.
• docs/MIGRATIONS.md states the push-only rule and the reason.

Notes

There are no real users and no deployed app — the cloud Supabase project is the whole of "production". Breaking changes are fine; if repairing the ledger is easier by resetting and re-pushing, do that.

tests/unit/transaction-split-snapshot-backstop.test.ts:1-17 says in its own header that it greps migration text and that "a green run of this file means nothing about whether the migration was ever applied". That is the gap this issue closes — the new check must query the live database, not the repo.

Effort: S · Risk: LOW/MED (every insert path is service-role or SECURITY DEFINER; a user-scoped insert missed in the #538 inventory would start failing loudly with permission denied for table transactions — that is the intended signal, not a regression)