DB-layer correctness and observability: automate the sidecar apply, attribute constraint violations, and settle the one remaining trigger

[teetangh]

Summary

The goal behind this issue is to stop investigating correctness failures through the Supabase dashboard and start investigating them through logs and code we control. That goal is achievable, and it is much closer than expected — but the framing needs one correction before we plan the work, because most of what we assumed we had to migrate does not exist.

I audited the live familiarise Postgres instance directly rather than inferring from the repository. The results change the shape of this project substantially: there are no Supabase RLS policies to move, no Supabase Edge Functions to move, and exactly one database trigger. The real problem is not that correctness logic lives in the database. It is that the small amount which does live there is applied by a manual step, and that when it fires it produces errors nobody can attribute without opening the SQL console.

Verified current state

The table below is a live audit of the public schema on project familiarise, taken 2026-08-01 by querying pg_trigger, pg_constraint, pg_policies and pg_index directly. It supersedes docs/supabase/rls-policies-triggers/, which describes a state that no longer exists.

Object	Count	Detail
Supabase RLS policies	0	Zero policies; zero of 139 tables have row-level security enabled
Supabase Edge Functions	0	No supabase/functions directory, no Supabase CLI project
Application triggers	1	ledger_txn_balanced on LedgerEntry
Exclusion constraints	1	slot_no_confirmed_overlap on SlotOfAppointment
Partial unique indexes	1	invitations_org_email_pending_key
CHECK constraints	29	Money non-negativity, bps sum, referral-credit arithmetic, FY format
Foreign keys / primary keys	213 / 139	15 foreign keys use Restrict to preserve money history

Supabase is being used for exactly two things: it hosts Postgres, and it hosts object storage. Authentication is Better Auth running against our own tables (lib/auth.ts:35), not Supabase Auth. Realtime is not used anywhere; every .channel( call in the repository belongs to Stream. Storage is a genuine dependency, but it sits behind a single module (lib/supabase.ts) covering six buckets, which is already the right seam for a future migration.

What this means for the stated goal

Moving away from "Supabase policies, Functions and triggers" is, in practice, a decision about one trigger. Everything else in that sentence is already true today. This is worth stating plainly because it turns a large migration project into three tractable pieces of work.

It is also worth separating two things that are easy to conflate. Inngest, Temporal, QStash and GitHub Actions are job orchestration engines: they decide when work runs, retry it, and give us logs for it. A database constraint is not a job. ledger_txn_balanced is a DEFERRABLE INITIALLY DEFERRED constraint trigger that runs at COMMIT, after every insert in the transaction, and rejects the whole transaction if debits do not equal credits. No orchestration engine can host that, because it has to run inside the transaction it is validating. So the orchestration question and the database-integrity question need to be answered separately, and only the first of them is about Inngest or Temporal.

On the orchestration half specifically, ADR 22 (docs/enterprise/70-design-decisions/22-queue-posture-revisited-with-measurements.md, last reviewed 2026-07-28) already re-tested both engines with measurements and concluded that Temporal and Inngest remain out, with QStash as the approved escalation, tracked in #866 and #1010. Notably it also recorded the criteria under which Inngest becomes correct — a flow that needs durable state across hours, chunked work exceeding Netlify's 15-minute ceiling, or more than roughly five sequential steps with long waits. If we want to revisit that, the honest path is a new ADR that argues against those criteria, not a second decision recorded in an issue. Nothing in this issue requires reversing ADR 22.

The three real gaps

Gap 1 — the money invariants can silently not exist (pre-MVP)

prisma db push does not manage CHECK constraints, exclusion constraints, or triggers. Those live in prisma/sql/ and are applied by a separate manual step:

"db:push":       "npm run db:push:schema && npm run db:sidecars",
"db:push:schema": "prisma db push",
"db:sidecars":   "npm run db:triggers && npm run db:constraints",

Anyone who runs prisma db push directly, or npm run db:push:schema, gets a database with no double-entry enforcement, no double-booking guard, and none of the 29 money CHECK constraints. Nothing in the deploy pipeline applies them: netlify.toml runs only npm run build.

CI has a guard (.github/workflows/ci.yaml:131, running scripts/ci/check-db-sidecars.ts and scripts/ci/check-db-drift.ts) but it only checks, and by design it exits 0 when DATABASE_URL is absent. So the guard is silent exactly when a fork or a secretless PR runs it.

The database is currently in sync — all three sidecar objects verified present — so this is a latent risk rather than an active incident. But it is the single highest-value thing in this issue, because the failure mode is silent absence of money guarantees.

Proposed fix. Make the sidecar apply automatic rather than manual, so that applying the schema and applying its constraints cannot come apart. Concretely: fold db:sidecars into the deploy path, and make db:push:schema private so the only documented entry point is the one that does both. Then change the CI guard from advisory to blocking whenever DATABASE_URL is present, and emit an explicit skip annotation when it is not, so a green check never silently means "not checked".

Gap 2 — constraint violations are unattributed, which is what forces UI debugging (pre-MVP)

This is the direct cause of the symptom in the title of this issue. When a database constraint rejects a write, the application currently maps it to a generic message and loses the constraint name, so diagnosing it means opening the SQL console.

There is a live example in #1091. The new in-place slot rewrite can collide with itself: when a webinar moves by less than its own duration, the first UPDATE lands an atom on a range a later atom still occupies, slot_no_confirmed_overlap fires 23P01, and isExclusionViolation maps it to "That time conflicts with another confirmed session on your calendar." The message blames a booking that does not exist, and there is nothing in the logs naming the constraint or the rows involved. That is precisely the investigation-in-the-UI problem.

Proposed fix. Capture the constraint name from the Postgres error (PrismaClientKnownRequestError.meta, or the driver's constraint field) and attach it to the Sentry event as a tag, alongside the table and the attempted range or key. Keep the user-facing message generic, but make the operator-facing record self-describing. Once the constraint name is in Sentry, "which invariant rejected this write, on which row" stops being a database question and becomes a log query, which is the actual goal here.

Gap 3 — the one trigger raises an error nothing is prepared to interpret (post-MVP)

assert_ledger_txn_balanced() raises a bare PL/pgSQL exception:

RAISE EXCEPTION 'Ledger transaction % is unbalanced by % paise (Sum(DEBIT) - Sum(CREDIT) != 0)', txn_id, imbalance;

Because the trigger is deferred, this surfaces at COMMIT rather than at the offending statement, so the stack trace points at the commit, not at the writer that unbalanced the ledger. There is no SQLSTATE we can branch on, and no structured payload.

Proposed fix — and this one needs a decision, not just an implementation. There are two defensible options and they should not be blended.

The first is to keep the trigger and make it legible: give it a custom ERRCODE via RAISE EXCEPTION ... USING ERRCODE = 'ledger_unbalanced', catch that specific code in postLedgerTxn(), and log the transaction id and imbalance to Sentry with the writer's call site. This keeps the guarantee that no writer, present or future, can commit an unbalanced ledger, and costs roughly a day.

The second is to move the assertion into postLedgerTxn() and drop the trigger. This gives full control over logging, but it only protects transactions that go through that function. Any future writer that posts ledger entries by another path — a backfill script, a migration, a new service — is unguarded, and the failure is silent rather than loud.

My recommendation is the first, because double-entry balance is the one invariant in the system where a silent violation is unrecoverable: you cannot reconstruct which side of a transaction was wrong after the fact. But this is a judgement call about how much we trust future writers, and it is the team's to make.

What should not move to application code

Worth recording explicitly so this does not get re-opened every quarter. Constraints fall into three classes, and only one of them is safely replaceable.

Single-row predicates — the 29 CHECK constraints and most foreign keys — are pure functions of one row, and application code can enforce them correctly. They are also nearly free and catch buggy writers rather than malicious ones, so removing them costs a backstop and buys nothing.

Cross-row concurrency invariants cannot be enforced in application code at all. This covers slot_no_confirmed_overlap, invitations_org_email_pending_key, and the idempotency unique indexes (Payment.clientIdempotencyKey, LedgerTransaction.idempotencyKey, Appointment.allocationIdempotencyKey, WebhookEvent.eventId — all verified live). Between the SELECT that checks and the INSERT that writes, another transaction commits. The substitutes are Redis locks, which fail open when Redis is unavailable or when two code paths derive the key differently, and Serializable isolation, which only helps if every writer opts in. A unique index is true regardless of which writer produced the row.

Commit-time invariants — currently just the ledger trigger — cannot be replicated in Prisma at all, because there is no commit hook to attach to.

Proposed work

• Fold db:sidecars into the deploy path so schema and constraints cannot be applied separately (Gap 1, pre-MVP)
• Make the CI sidecar guard blocking when DATABASE_URL is present, and explicitly annotate the skip when it is not (Gap 1, pre-MVP)
• Tag constraint name, table and attempted key onto Sentry events for 23P01, 23505 and 40001 (Gap 2, pre-MVP)
• Decide between custom ERRCODE on the ledger trigger versus moving the assertion into postLedgerTxn() (Gap 3, needs a decision)
• Delete docs/supabase/rls-policies-triggers/, which documents 42 RLS policies that do not exist and references NextAuth (documentation)
• Review the PaymentGateway entry in prisma/sql/known-drift.json, which expires 2026-09-30 (maintenance)
• Optional, decoupled: extract lib/supabase.ts behind a storage interface so a future object-storage migration is a one-module change

Related

Part of #1072. Orchestration posture is settled in ADR 22 and tracked in #866 and #1010; this issue deliberately does not reopen it. The constraint-attribution example is live in #1091.

[teetangh]

Cross-link: the concurrency audit that informed this issue also surfaced five verified defects — writes where the guarantee we assume covers them does not actually apply. Filed separately as #1093 so this issue stays scoped to the apply pipeline and observability.

The two are coupled in one direction worth noting: the Sentry constraint-attribution work proposed here (Gap 2) is what makes the failures in #1093 diagnosable from logs rather than from the SQL console. #1093 §5 also demonstrates the wider point — claimProgramAssignment rejects an overlap in application code with the comment "Postgres exclusion constraints aren't Prisma-expressible", having treated that as meaning unavailable. The sidecar this issue is about makes them available, and we already ship exactly that shape for slots.