The problem

A diamond manufacturer works two kinds of lab-grown material, CVD and HPHT, and buys it as rough parcels. A parcel arrives with a landed cost attached to it. It is then split into stones, and each stone goes down a factory line: manual sine, Galaxy scanning, Sarine planning, laser sawing, 4P, HPHT treatment, polish. At a saw the stone stops being one thing and becomes several. Two facts have to survive that journey. The first is weight: what went into a step, what came out, and what was lost. The second is cost: what this specific piece has absorbed so far, including its share of the parcel it came from and the charge for every step it has been through. The existing way of working kept both in spreadsheets. Weight was recorded by the floor. Cost was reconstructed afterwards by the owner, from memory and from the same sheets the floor could read. That is the real constraint. The people who must enter carats are the people who must never see money. The brief was a multi-tenant operations app that records rough parcels and landed cost, splits a parcel into stones, follows each stone through the factory, and keeps weight history and accumulated cost attached as the stone is cut into pieces. I was the engineering lead on the engagement and principal author of the schema and the architecture gate.

A money boundary the auth model said was impossible

The platform is Supabase. Supabase issues every signed-in user the same Postgres role. Whether you are the owner of the tenant or a clerk at the sine table, the database sees one role. Postgres column grants operate on roles. So the obvious answer, grant the cost columns to owners and withhold them from staff, cannot be expressed. There is nothing to separate. The tempting answer is to filter in the application: fetch the row, blank the money fields, render. That fails the brief in a way that matters. The row still leaves the database with the number in it. Anyone with the anon key and a network tab has the number. A boundary that exists only in the render path is not a boundary. What shipped instead:

• Money columns were revoked from the shared role entirely. Not filtered, not masked. The role has no grant on them, so no query from a client session can name them and succeed.
• Cost is re-exposed through security-definer views. Each view runs with the privileges of its owner, not the caller, and carries its own test: is this caller a member of this tenant, and does this caller hold a role that is allowed to see cost.
• A staff request returns no rows. Not a row of nulls, not a row with dashes where the number would be. Nothing. There is no shape to infer a value from, no row count to compare against, no null that tells you a number exists.
• Row-level security carries the tenant boundary underneath all of it, so the cost test never has to double as the tenancy test.

The result is a boundary the client cannot talk its way around, because the client is not holding the key. Fifty-four row-level security policies enforce the tenant and role split across the schema.

Cost that follows weight

Landed cost enters at the parcel. It has to reach each stone, accumulate as the stone is worked, and then divide correctly the moment the stone stops being one stone. The rule is simple to state and unforgiving to implement. At a saw, the cost carried by the parent, plus the charge for the sawing itself, splits across the resulting pieces by weight. Every piece leaves the saw with a share, and the shares sum back to what went in. Three decisions made that hold: Exact decimal arithmetic, everywhere. Carat weights and money are numeric, never float. A split that uses binary floating point loses value in the last place, and those losses accumulate across a line with many steps. Rounding happens once, at display, and never in storage or in the split itself. The split is conservative by construction. The shares are derived so that they add back to the parent total rather than being computed independently and hoped to agree. Residue from rounding has one defined home rather than being scattered. Cost accumulates per step, not at the end. Each factory step attaches its charge to the piece as it happens. Asking what a piece has cost is a read, not a recomputation, so the answer cannot drift depending on who asks or when. Billing is append-only. A rate that changes next month does not rewrite what was already billed last month, because the billed row is a fact that was recorded, not a formula that is re-evaluated. Worker-level rate overrides supersede by effective date, so an individual arrangement is expressed as a dated row rather than an edit to history.

Making the rules machine-decidable

The stone journey is not a linear chain. A stone can go from planning to sawing, or to polish, or be held. What it cannot do is arrive somewhere nobody planned for it to be. That is modelled as a table of allowed moves rather than as branching code. The database refuses an unplanned transition, and it refuses it in words: the error names the state the stone is in, the state that was attempted, and why the move is not allowed. A clerk who mis-scans gets a sentence, not a constraint violation code. Thirteen triggers enforce the moves and the invariants that go with them. Around the schema sits an architecture gate. Every written rule in it is machine-decidable, which is the whole point: a rule that needs a human to interpret it is a rule that lapses on a busy week. The gate refuses:

• Float types in migrations. Weight and money are decimal or the migration does not land.
• Numeric coercion of carat or price fields in application code. No parsing a carat into a JavaScript number on the way through.
• The privileged service key outside a single file. One place holds it, and the gate fails the build if a second place imports it.
• A migration without a paired rollback.
• Destructive DDL without a stated reason recorded alongside it.

Eleven architecture decision records carry the reasoning, so a future contributor reads why the gate refuses something before deciding to weaken it.

Proving the schema

Applying a migration proves it parses. It does not prove the constraint does its job. Schema verification in CI does the harder thing. A throwaway Postgres is booted. The schema is applied, rolled back, and applied again, which catches the rollback that was written but never run. Then each constraint is exercised against the violation it exists to stop: the negative weight, the transition that is not in the allowed-moves table, the money read from a role that should not have one. Forty-two constraint assertions run this way. A constraint that fails to reject its violation fails the build. CI is also built to refuse a false green. The usual failure mode is environmental: Docker is unavailable, the database job quietly skips, the pipeline goes green, and the schema was never checked. Here an unavailable Docker turns "database not checked" into a failed run. The pipeline cannot report success on work it did not do. Deploys apply migrations before shipping the application, in that order, so a live app never queries a column that does not exist yet. Six CI jobs cover the gate, the schema proof, unit tests, end-to-end tests, and the deploy path. The design layer is held to the same standard. No raw colour literals, and a 12px type floor, enforced across both apps rather than agreed in a review comment. Floor staff read this on shop-floor hardware, and text below that floor is text that gets guessed at.

Scale and delivery

Delivered in 21 days.

• 27 migrations, each with a paired rollback
• 7,933 lines of migration SQL
• 54 row-level security policies
• 13 triggers
• 42 constraint assertions run against real violations
• 18,120 lines of TypeScript and TSX across 161 files
• 13 Playwright specs
• 9 Vitest files, 34 cases
• 11 architecture decision records
• 6-job CI pipeline

Stack: TypeScript, SQL, Next.js 16, React 19, Tailwind 4, shadcn/ui, Zod 4, PostgreSQL via Supabase with row-level security, Vitest, Playwright, pnpm workspaces, GitHub Actions, Vercel. The weight of this build sits in the database rather than in the application. That is deliberate. A rule in the schema applies to every caller, including the next application someone writes against it, and including the one written in a hurry. A rule in a component applies to the one screen that remembers it.