v5.32.0 27 May 2026 Improvement

Quote Domain Schema Separation — Migration 087 Live

Why This Matters

Quotes have been silently broken since March. Before this release, the quote domain was implemented as a discriminated row inside the invoice table — a quote was an invoice row with invoice_type='quote' and a status outside the normal invoice lifecycle. That choice made the invoice constraint surface mean two different things at once: every constraint had to accommodate both invoice rules and quote rules, which meant neither was enforced cleanly. The route layer carried the gap as runtime conditionals that drifted out of sync with the schema.

Step 2 of the quote domain rebuild ships the schema half of the fix: quotes and quote lines now live in their own tables with their own constraints, and the invoice constraint surface has been stripped of quote accommodations. The runtime layer (service rewrite, atomic convert function, audit-chain integration) ships in a follow-on release. This release is honest about that boundary — the schema is correct, the route is still broken, and the API still throws on every quote write until the service rebuild lands.

The session itself proved the value of dry-run-first migrations and multi-pass audit. Six audit rounds caught ten findings before any DDL ran against the live database; two of those findings would have shipped silently against in-transaction safety nets in plausible data states. The pattern is documented and standing for the remaining quote-domain work and for every subsequent migration.

Schema Separation

New Tables

  • Quote header table Mirrors the invoice header structure with quote-specific columns and constraints. Five CHECK constraints enforce the monetary algebra and non-negative envelope. Row-level security is enabled and forced under the same per-tenant predicate as the existing financial tables. A unique constraint on the combination of organisation and quote number prevents duplicates within a tenant.

  • Quote line table Mirrors the invoice line structure exactly for column types, precision, and constraint surface. Eleven CHECK constraints enforce the same reduction invariants the invoice lines carry: net pence derived from quantity and unit price by an immutable function, VAT pence derived from net pence and VAT rate by an immutable function, gross conservation, magnitude envelope, decimal precision bounds. Foreign key to the quote header with cascade delete.

Invoice Constraint Cleanup

The four invoice constraints that previously accommodated quote rows have been recreated without those accommodations:

  • The sign-algebra constraint dropped its quote clause. Sales and purchase invoices remain non-negative; credit notes remain non-positive. There is no longer a third path for quote rows.

  • The payment-bound constraint dropped its quote clause. Payment bounds now hold for every row in the invoice table without exception.

  • The status check dropped four quote-only statuses (accepted, declined, expired, converted). The eight legitimate invoice statuses (draft, sent, viewed, paid, partial, overdue, cancelled, written_off) remain.

  • The invoice type check dropped the quote member. The three legitimate types (sales, purchase, credit_note) remain.

Every existing invoice row was verified against the recreated constraints inside the same transaction as the recreation; zero violations.

Provenance Wiring

Bidirectional provenance between quotes and invoices is now wired at the schema level. Each side carries a foreign key to the other, both nullable. When a quote is converted to an invoice (runtime layer follows in a future release), the convert transaction will write both links in the same atomic step — the invoice records which quote it came from, the quote records which invoice it became. This makes the provenance invariant mechanically verifiable rather than relying on application-level conventions.

The forward link was reconciled with a pre-existing column on the invoice table that had been left behind by an earlier aborted implementation attempt. Rather than create a parallel column, the migration retargeted the existing one to point at the new quote table. A second pre-existing placeholder column was dropped (it belonged on the quote table, where it now lives correctly).

Operational Impact

  • Quote rows in the invoice table reduced from three (pre-launch test data) to zero. No customer ever held a quote in production because the quote route has been broken since the column-rename migration earlier this year.

  • Invoice constraint surface reduced in scope: four constraints recreated without quote accommodations. The invoice domain’s constraint definitions now express exactly the invoice business rules, with no carve-outs.

  • Vestigial schema cleaned up: one foreign key retargeted from a semantically nonsensical self-reference to the correct cross-table reference, one misplaced column dropped from the invoice table.

  • Migration verification surface expanded: ten pre-flight assertions and sixteen post-verify assertions inside the migration transaction itself, in addition to the dry-run/commit pair pattern that exercises every assertion against the live database before any state change persists.

  • Zero unintended state changes to the live database before commit. Six audit rounds caught ten findings; the dry-run gate caught the two findings that would otherwise have committed silently in plausible live row distributions.

Known Issues

  • Quote API endpoints still throw on writes. The schema half of the rebuild is live; the route and service half is owed in a follow-on release. Until then, quote create, update, and convert endpoints return errors as they have done since March. No regression — same behaviour, now with the correct schema waiting underneath. The runtime layer lands next.

  • Provenance invariant not yet runtime-enforced. The schema can represent bidirectional quote↔invoice provenance correctly, but no service code yet writes both sides of the link inside an atomic transaction. The convert function ships with the runtime release; the integrity-harness reconciliation query becomes a Class M enforcement layer at that point. Currently this is an operational expectation, not a mechanical guarantee.

Files Changed

Backend:

  • Two migration files — the dry-run twin and the commit version of migration 087, body byte-identical except for their terminal transactional verb. Together they form the dry-run-first pattern used for every schema-touching migration: the dry-run is exercised against the live database first, rolled back, and only after a clean ROLLBACK with every assertion passing does the commit version run.

The runtime layer (quote service, route rewrite, atomic convert function with audit-chain integration) ships next and completes the quote domain rebuild.