Kernel Milestone 3 — Monetary Domain Migration

Kernel Milestone: 3 of 6 Previous: Milestone 2 — Financial Immutability and State Machines (v5.12.x) Next: Milestone 4 — Provenance and Audit Chain

System Impact
-------------
Ledger integrity:         strengthened
Ledger mutation:          all monetary columns converted to integer pence storage
Tenant isolation:         strengthened — FORCE RLS gap on core identity tables closed
Financial immutability:   enforced at database layer
State machine:            unchanged
Breaking changes:         API layer must not apply pound-to-pence conversion to DB results
Replay determinism:       preserved

Invariant Change
----------------
All monetary amounts are now stored as exact integers in pence. Fractional penny
storage, non-canonical rounding, and internally inconsistent invoice arithmetic
are rejected by the database on every write — not by application code.

Why This Matters

Every financial system makes implicit choices about how it stores money. Most store decimal values and rely on application code to round consistently. The problem is not floating-point error — decimal storage is exact. The problem is inconsistent state: when different code paths perform the same calculation differently, the stored results diverge silently. An invoice line subtotal that doesn’t match its quantity times unit price. A VAT figure with a different sign than the net amount it belongs to. A total that doesn’t equal the sum of its components. These aren’t hypothetical edge cases — they’re the natural consequence of arithmetic scattered across an application without a single enforced canonical form.

Milestone 3 closes all of these paths. Every monetary column in SpeyBooks is now an integer count of pence. Every invoice line’s arithmetic — net, VAT, gross — is verified correct by the database on every write against four canonical functions that are the sole permitted source of monetary computation. No application code path, no import engine, no SQL query can produce a stored amount that doesn’t conform to these functions without the database immediately rejecting the write with a hard constraint violation.

This is the foundation that makes Milestones 4 through 6 meaningful. Provenance, audit chains, and cryptographic ledger integrity are only valuable if the values they protect were correct to begin with.

Integer Pence Storage

The Monetary Foundation

All monetary amounts are now stored as exact integers representing pence. Twenty-five monetary columns across nine tables were migrated. Where a column was previously optional (the amount may legitimately be absent — for example, a bank feed row where the feed didn’t supply a running balance), the column stores NULL rather than an integer, preserving the distinction between “zero” and “unknown.”

Three domain types were introduced to the database schema:

• pence — a signed integer, not-null by construction, used for all amounts that must exist
• pence_nonneg — a non-negative variant for future use where amounts cannot be negative
• iso_currency — a three-character currency code, enforced at the storage layer

These are not application conventions. They are database-level types. A value that violates them cannot be stored.

Canonical Rounding

A single rounding function governs all monetary arithmetic. It implements symmetric half-up rounding: ties (exactly halfway values) round away from zero, consistently, for both positive and negative amounts. This eliminates the class of bugs where rounding a positive amount and rounding the equivalent negative amount produce asymmetric results — a silent correctness failure that affects credit notes and refunds.

No other rounding method may be used in the monetary computation path. This prohibition is a kernel doctrine, not a convention.

Invoice Line Algebraic Closure

Three Columns, Three Invariants

Every invoice line now carries three monetary figures: the net amount, the VAT amount, and the gross total. All three are stored as integers in pence. Three database constraints enforce their algebraic relationship on every write:

• The net amount must equal the result of applying the canonical net computation function to the line’s quantity and unit price
• The VAT amount must equal the result of applying the canonical VAT function to the net amount and VAT rate
• The gross amount must equal net plus VAT

These constraints mean it is impossible to store an invoice line where the arithmetic doesn’t balance. There is no code path, no import, no direct database write that can produce an internally inconsistent line. The database rejects it.

Precision Boundaries

Unit prices are enforced to penny granularity. A unit price cannot be stored with sub-penny precision in v1 — this is a deliberate conservative choice that aligns with how HMRC and standard UK accounting expect prices to be expressed. Fractional pricing (for high-volume per-unit billing scenarios) is a planned future capability with a defined extension path.

Quantities are enforced to three decimal places. VAT rates are enforced to the range 0–1 (decimal multiplier form). Percentage form VAT rates are rejected at the database layer.

VAT Sign Algebra

Credit notes produce negative net amounts. The VAT on a credit note must be negative — it is VAT being reversed, not VAT being charged. Before this release, sign consistency between net and VAT figures was an application-level responsibility. From this release forward, the canonical VAT function propagates sign from net to VAT mathematically, and the stored result is verified by constraint.

A zero net amount must produce zero VAT. A positive net must produce non-negative VAT. A negative net must produce non-positive VAT. These are now database invariants, not coding conventions.

Reporting Views

Five reporting views — account balances, balance sheet, profit and loss, aged receivables, and monthly P&L — were rebuilt to operate on integer pence throughout. Views return pence; the display layer is responsible for formatting. This is consistent with the API boundary specification established in Milestone 1: the kernel works in pence, the presentation layer converts for humans.

Tenant Isolation Gap Closure

A gap from Milestone 1 was closed as part of this release. Two core identity tables were missing forced row-level security — a configuration that ensures no query, regardless of how it is constructed, can bypass tenant isolation on those tables. This gap has been closed. All tenant-scoped tables in the accounting schema now have both row-level security enabled and forced.

Threat Closure

Security Posture Change

Prior to this release, monetary correctness was a Class O guarantee: it depended on application code following conventions consistently across all code paths. Any service, import engine, or direct database operation that didn’t follow those conventions could produce inconsistent financial state without any database-level rejection. From this release, monetary correctness is Class M: the database enforces algebraic closure on every write, independent of which code path performs the write. The constraint violation is immediate, the transaction is aborted, and no inconsistent state reaches committed storage.

Enforced by database domain types, CHECK constraints referencing IMMUTABLE canonical functions, and forced row-level security — migration 071.

Verification Record

Pre-flight:
  9/9 validation checks clean
  PF-01: RLS enforcement confirmed on all tenant tables
  PF-02 through PF-09: schema state, data integrity, vat_scheme values verified

Data corrections (applied before migration):
  8 rows: VAT rate stored in percentage form — corrected to decimal multiplier
  8 rows: VAT-inclusive gross stored as net subtotal — invalid test data, removed

Migration:
  COMMIT
  All 10 invariant verification blocks passed
  INV-01: No decimal monetary columns remain
  INV-02: 4 canonical functions, all deterministic and parallelism-safe
  INV-03: invoice_lines net/VAT/gross columns carry correct domain types
  INV-04: No silent zero-defaults on monetary columns
  INV-05: All invoice lines satisfy gross = net + VAT
  INV-06: All invoices satisfy total = subtotal + VAT
  INV-07: All three domain types created
  INV-08: Unit price penny constraint installed
  INV-09: VAT scheme columns and constraints verified
  INV-10: All 5 reporting views recreated

Post-commit schema signature:
  eeea9331618a60af5d62d6be029de014

Adversarial review:
  Two independent kernel-level reviews conducted during development session.
  Review 1: surfaced unit_price constraint vs ADR contradiction, NUMERIC
             vs float claim, axiom ID drift, nullable column domain gap.
  Review 2: confirmed action plan correct, added VAT sign constraint as
             required (migration 072), confirmed API double-conversion as
             highest operational risk item.
  All critical findings accepted and actioned or formally deferred with rationale.

Amendment:
  VAT sign algebraic constraint deferred to migration 072 (constraint addition
  on already-committed table — clean separation).
  ADR-003 corrections pending (unit_price policy, rounding rationale,
  no-alternate-arithmetic doctrine).

Architectural Context

The kernel milestone sequence has a strict dependency order. Tenant isolation (M1) had to precede everything — without it, no financial guarantee has meaning because data from one tenant could contaminate another. Financial immutability (M2) had to precede monetary correctness (M3) — there is no value in enforcing that amounts are correct integers if those amounts can be silently mutated after the fact.

Milestone 3 enables Milestones 4 and 5. Provenance (M4) — recording the origin of every financial value — only matters if the values being recorded are themselves correct. Schema-derived categorical boundaries (M5) — enforcing that account categorisation follows structural rules — requires the monetary foundation to be settled first, because categorisation errors manifest as wrong amounts in wrong accounts.

Operational Impact

API layer: The database now returns integer pence directly for all monetary columns. Any code path that previously converted decimal pounds to pence must not apply that conversion to values already returned as integers. A verification pass of the conversion boundary is required before this release is considered fully complete.

Import engines: The bank import pipeline, invoice import engine, and opening balance engine all write to tables covered by this migration. All monetary writes now go through the canonical function path enforced by constraints. Malformed amounts that previously stored silently will now produce constraint violations — which is the correct behaviour.

VAT scheme: Organisations previously recorded as “not VAT registered” using a legacy scheme value have been migrated to the canonical scheme vocabulary. The service layer must supply an explicit VAT scheme on every new organisation — no silent default is permitted.

Kernel Status

Files Changed

Database migrations:

• api/db/migrations/071-preflight-data-fix.sql — pre-migration data corrections: VAT rate normalisation and invalid test data removal
• api/db/migrations/071-preflight.sql — 9-check pre-flight validation suite
• api/db/migrations/071-milestone-3-monetary.sql — 17-phase migration: domain types, canonical functions, 25 column migrations, 5 view reconstructions, 8 algebraic constraints, 10 in-transaction invariant verifications

Milestone 4 (Provenance and Audit Chain) will establish cryptographic proof of origin for every financial value entering the ledger, completing the traceability guarantee from source document to committed transaction.