Money and ledger data model

Definition

Representation

Every monetary amount is a signed integer count of the currency's minor unit (cents) plus an explicit currency code. There is no floating-point money anywhere in the system — not in a column, not in an intermediate calculation, not in an API payload. Display formatting is a presentation concern applied once, at the edge.

The no-floating-point rule is a discipline enforced in code review and by tests, not a database constraint — the database cannot stop application code from casting to float mid-calculation, so it is guarded at the type and review layer.

Entity model

Entity-relationship model

A double-entry ledger: an account holds a balance, a transaction is an atomic financial event, and every transaction is composed of two or more lines that reference accounts. The sum of a transaction's line amounts is always zero — value moves between accounts, it is never created or destroyed on a line.

Definition

Account types

Every account has a type, and the type fixes its normal balance side — the sign a healthy balance carries under the convention used here, where `amount_minor` is `+` for a debit and `−` for a credit. Assets and expenses increase with debits (normal balance positive); liabilities, equity, and revenue increase with credits (normal balance negative). A temporary excursion to the other side is not forbidden (an overdrawn asset, a contra account), but the type is what a report and a constraint reason about.

The five types are the standard double-entry set. A system MAY subdivide them (current vs fixed assets) but each account MUST map to exactly one of the five, and the accounting equation — assets = liabilities + equity — holds across the whole ledger precisely because every transaction nets to zero (the balancing constraint below).

Each account MUST map to exactly one of the five types; the type fixes the normal balance side and what a report and the balancing constraint reason about.

Contract

Account balance

An account's balance is, by definition, the sum of its ledger lines — `balance = Σ amount_minor over the account's lines`. The lines are the source of truth; a stored `balance_minor` on the account is a materialised cache of that sum, never an independent value.

Two implementations are valid, and a system chooses one and holds to it. Compute-on-read is simplest and always correct — the balance is the aggregate query, so it cannot drift from the lines. Materialised is faster for hot balances — `balance_minor` is updated in the same transaction as each line — but it MUST be periodically reconciled back to the sum of the lines (as in the inventory spec), because a materialised total that drifts from its lines is a silent corruption. A materialised balance is a cache; the lines are the ledger.

Where the domain forbids a negative balance — a cash or asset account that must not go overdrawn, a prepaid liability that must not go negative — a `CHECK (balance_minor >= 0)` (or an equivalent guard in the posting transaction) refuses the line that would breach it, so an overdraw is a write error, not a figure discovered later. Accounts that may legitimately go negative (a contra account, a credit line) are exempt by an explicit `allows_negative` flag, not by omission.

-- The definition. A materialised balance_minor is a reconcilable cache of this.
SELECT COALESCE(SUM(amount_minor), 0) AS balance_minor
FROM   ledger_lines WHERE account_id = :account;

-- Non-negative where the domain requires it — an overdraw fails the write.
ALTER TABLE accounts
  ADD CONSTRAINT balance_non_negative
  CHECK (allows_negative OR balance_minor >= 0);

Balance is the sum of the account's lines; a stored balance_minor is a reconcilable cache, never independent. Accounts the domain forbids from going negative MUST enforce balance_minor >= 0; accounts that may go negative are exempt by an explicit flag, not by omission.

Definition

The balancing constraint

The core invariant — every transaction's lines sum to zero — is enforced by the database, not by application discipline. A transaction that does not balance cannot commit, turning a whole class of reconciliation bugs into an immediate, un-ignorable write failure.

It MUST be a deferred constraint trigger, not a row-level CHECK. A CHECK sees one row at a time and cannot express a condition over all of a transaction's lines — the balance is a property of the set, not of any single line. And it MUST be DEFERRABLE INITIALLY DEFERRED, evaluated once at COMMIT after every line is inserted: an immediate check would fire after the first line, when the running sum is non-zero by construction, and reject every legitimate multi-line posting. Deferred timing is what lets the lines be written in any order and validated as a whole.

-- Amounts are exact integers, and non-negative where the domain requires it.
ALTER TABLE products
  ADD CONSTRAINT price_non_negative CHECK (price_minor >= 0);

-- A DEFERRED constraint trigger asserts each transaction nets to zero at COMMIT,
-- after all its lines are inserted. A per-row CHECK cannot express this.
CREATE CONSTRAINT TRIGGER transaction_balances
  AFTER INSERT OR UPDATE ON ledger_lines
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION assert_transaction_balances();
-- assert_transaction_balances():
--   SELECT SUM(amount_minor) FROM ledger_lines WHERE transaction_id = NEW.transaction_id;
--   IF <> 0 THEN RAISE EXCEPTION 'transaction % does not balance', NEW.transaction_id;

The balance check MUST be a DEFERRABLE INITIALLY DEFERRED constraint trigger (evaluated at COMMIT over all a transaction's lines), not a per-row CHECK — a CHECK sees one line at a time, and an immediate check would reject the first line of every balanced posting.

Contract

Mixed-currency handling

A single transaction MUST NOT mix currencies on its lines without an explicit conversion — “the lines sum to zero” is only meaningful within one currency. When value crosses currencies, the transaction carries the conversion as its own balanced legs: the source-currency lines net to zero in the source currency, the target-currency lines net to zero in the target currency, and a conversion (FX) account absorbs the difference so each currency balances independently.

The rate is stored on the transaction, not inferred later: the rate, the pair (from/to), a timestamp, and the source (which provider or reference rate) are recorded, so the conversion is reproducible and auditable — “why is this €→R line at this figure” is answered by the stored rate, not by re-fetching today's. A rate is data captured at posting time, never a live lookup at read time. Conversion of minor units uses the exact rate and is rounded to integer minor units by the allocation discipline, so no fraction of a cent is invented.

Balances are reported per currency; an account holds one currency, and a cross-currency “total” is a presentation-time conversion at a stated rate, clearly not a ledger figure. The ledger itself never holds a mixed-currency sum.

CREATE TABLE fx_rates (
    from_ccy   char(3)     NOT NULL,
    to_ccy     char(3)     NOT NULL,
    rate       numeric     NOT NULL,   -- exact (not float); minor units integer-rounded
    source     text        NOT NULL,   -- provider / reference-rate identity
    as_of      timestamptz NOT NULL,
    PRIMARY KEY (from_ccy, to_ccy, as_of)
);
-- The transaction records the rate it used; each currency's lines net to zero.

Cross-currency transactions MUST convert via an explicit FX account so each currency's lines net to zero independently; the rate (pair, value, source, timestamp) MUST be stored on the transaction, so the conversion is reproducible — never a read-time lookup.

Contract

Transaction reversals

A posted transaction is immutable. An erroneous transaction MUST NOT be deleted or mutated — deleting it destroys the audit trail, mutating it rewrites history. It is corrected by posting a reversal: a new transaction whose lines are the exact negation of the original's, referencing the original by id, so the two together net to zero and both the error and its correction remain in the ledger.

The reversal references the original (`reverses_transaction_id`), and the original MAY be flagged as reversed, so a report shows a net position while an audit sees the full sequence — original, reversal, and any re-posting of the corrected figures. This is the ledger equivalent of the append-only audit log: nothing is erased; corrections are additive.

A reversal is itself a balanced transaction subject to every rule above (sums to zero, one currency per leg, rate stored if it crosses currencies), and it is idempotent on the original's id — a transaction MUST NOT be reversed twice.

INSERT INTO transactions (id, reverses_transaction_id, reference, posted_at)
VALUES (:new_id, :original_id, 'reversal of ' || :original_id, now());

INSERT INTO ledger_lines (transaction_id, account_id, amount_minor)
SELECT :new_id, account_id, -amount_minor           -- exact negation
FROM   ledger_lines WHERE transaction_id = :original_id;

An erroneous transaction MUST be reversed — a new transaction negating the original and referencing it — never deleted or mutated; the reversal is balanced, subject to all posting rules, and idempotent on the original's id.

Definition

Rounding and allocation

When a value must be split — tax, discount, revenue share — the remainder is distributed deterministically so the parts always re-sum to the whole. No cent is lost to independent rounding. Allocation is a defined operation, not an ad-hoc `round()` per part.

def allocate(total_minor: int, weights: list[int]) -> list[int]:
    """Split total_minor across weights so the result sums back to total_minor."""
    w = sum(weights)
    base = [total_minor * x // w for x in weights]
    remainder = total_minor - sum(base)          # the cents rounding dropped
    # Hand the leftover cents to the largest fractional parts, deterministically.
    order = sorted(range(len(weights)),
                   key=lambda i: (total_minor * weights[i]) % w, reverse=True)
    for i in order[:remainder]:
        base[i] += 1
    assert sum(base) == total_minor              # invariant, always holds
    return base