The commercial schema, and why the margin floor stayed in code

Migration 0003 adds the tables a quote needs before it can be a commercial document rather than a calculation: customers, rate_cards, rate_card_items, margin_rules, quote_lines, and three nullable columns on quotes. It is purely additive — no drops, no type changes, no backfill — and that is a constraint the migration was written to satisfy, not a description of how it happened to turn out. The entries below are the places where the obvious choice was the wrong one.

The margin floor did not become data. The tempting design is a margin_rules table that sets the floor, replacing MARGIN_FLOOR_STRING in both engines. It is tempting because it is what a rules table is for, and because it would end the two-engine duplication that check:margin exists to police. It was rejected. The floor is the one number in this system that a bad afternoon must not be able to move, and making it a row moves it from something two guard scripts assert about at build time to something an UPDATE statement decides at runtime. check:margin would still pass — the constant it parses would still be 0.50 — while every quote priced beneath it. A guard that passes while the thing it guards is broken is worse than no guard, because it is believed.

So margin_rules may raise the floor and never lower it, and the database says so rather than the application:

check ("min_margin" >= 0.50 and "min_margin" <= 0.9999)

For this decision to be wrong, the 50% floor would have to stop being a company-wide policy and start being a per-customer negotiation. If that day comes, the change is to relax this constraint deliberately — one line, in a migration, in a diff someone reviews — not to discover the floor was already adjustable.

Rate cards live on the server only, and nowhere else (PRD open question C). A rate card is the wholesale cost of every SKU the company buys. Anything the browser can fetch, a competitor can fetch, and this is the same class of data as the quote-row leak D-008 opened with. lib/cpq-engine.ts keeps its hardcoded constants and stays what it is: an indicative marketing sandbox, labelled as such. Real prices resolve server-side against rate_card_items and are never serialised toward a client.

The schema extends rather than replaces (PRD open question D). The brief described a products table; the database already has inventory_skus doing that job. rate_card_items references a SKU instead of duplicating one, and no parallel product table was created. Two ideas of what a SKU is would begin drifting the day the second one landed, and the drift would surface as a cut list that disagrees with a price for a reason nobody can find.

Overlapping rate cards are prevented by an exclusion constraint, not a trigger. Two active rate cards covering the same day is an unanswerable question — the system cannot know which cost applies — so it is forbidden:

exclude using gist (
    "organization_id" with =,
    daterange("effective_from", "effective_to", '[)') with &&
) where ("is_active")

A before insert trigger that selects for conflicts is a lock-free check with a race inside it: two sessions each see no overlap, each commits, and the table now holds the state the trigger existed to prevent. An exclusion constraint is an index, so the second writer loses at the storage layer where there is nothing to race.

`quotes.payload_json` stays authoritative; `quote_lines` is a projection. The payload is the audit record — the resolved SKU snapshots, the inputs, the engine version — and it must remain the thing that says what was quoted. quote_lines is derived from it and rebuildable from it, and exists for one reason: the shop needs to answer "which jobs this week need a tube over 90 inches" without a GIN index over a document whose shape is not a contract. When the two disagree, the payload is right and the projection is stale. The four cut columns carry the same values check:cutlist protects (D-002).

The cut-size constraints are inequalities, not the numbers. A quote line's tube, hembar and fabric width must all be less than the finished width, and the fabric drop must exceed the finished drop. What the migration does *not* do is pin 1.25, 1.00, 1.50 and 4.00 into a check constraint. Those are hardware facts, and a hardware change is a purchasing decision, not a schema change. Encoding them here would mean a new hembar profile requires a migration, which is exactly the friction that gets solved by disabling the constraint.

`customers` has no unique index on `(organization_id, primary_email)`. Two records for one person is a data-quality problem, and it is real. But a unique index does not solve it; it converts it into a 500 on the button a salesperson just pressed, at the moment they are on the phone with a customer. The workaround they will find within a week is a plus-address or a trailing space, and the duplicate now exists *and* is undetectable. A non-unique index plus a merge tool is the honest shape. customer_name and customer_email stay on quotes beside the new customer_id for the same reason a printed invoice does not change when someone gets married: the quote records who it was addressed to, the customer record holds who they are now.

Prisma's schema language cannot express the safety features, and that is accepted. The composite tenant-scoped foreign keys — the ones that prove a referenced row belongs to the *same* organization rather than merely existing — and the GiST exclusion constraint have no representation in schema.prisma. Consequently prisma migrate dev will offer to drop the unique indexes they depend on, and that offer must be declined every time; production deploys with migrate deploy, which replays files and never diffs. The migrations CI job therefore runs prisma migrate status and deliberately not prisma migrate diff, which would report every one of these protections as drift and fail permanently.

How this is verified. Two things guard 0003, and neither is a list somebody has to remember to update. The migration ends with a do $$ block that queries pg_catalog at deploy time and raises if any table in public carrying an organization_id column lacks both relrowsecurity and relforcerowsecurity — because a tenant table with no policy fails *open* and fails *silently*, since 0002's alter default privileges already granted the application role select on it. And test/rls-policies.test.mjs now reads the migration list off disk rather than holding it inline. It held an inline list until this change, which meant 0003 would have been deployed by production and skipped by the only test that checks policies hold — with every existing assertion still green, because they only touch tables 0001 created. A hardcoded list inside a test whose whole job is catching omissions is the one omission it cannot catch.