Data Model Ledger

Data Model - Ledger Schema

Part of the Data Model reference. Schema: ledger. Companion to Domain-Model, ADR-0007, ADR-0010, ADR-0011.

Tables in this schema

Table	Role
`ledger.accounts`	Account master record (mutable, versioned)
`ledger.transactions`	Immutable posting envelope
`ledger.entries`	Immutable double-entry lines (range-partitioned by quarter)
`ledger.account_balances`	Incremental balance row, one per (account, currency)

Table: `accounts`

CREATE TABLE IF NOT EXISTS ledger.accounts (
    id              UUID PRIMARY KEY,
    name            VARCHAR(255) NOT NULL,
    type            VARCHAR(32)  NOT NULL,
    currency        CHAR(3)      NOT NULL,
    status          VARCHAR(16)  NOT NULL DEFAULT 'ACTIVE',
    metadata        JSONB        NOT NULL DEFAULT '{}',
    version         BIGINT       NOT NULL DEFAULT 0,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    created_by      VARCHAR(64)  NOT NULL,
    updated_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_by      VARCHAR(64)  NOT NULL,

    CONSTRAINT ck_accounts_type CHECK (type IN (
        'ASSET','LIABILITY','EQUITY','REVENUE','EXPENSE',
        'USER_WALLET','FEE','RESERVE','SUSPENSE'
    )),
    CONSTRAINT ck_accounts_status  CHECK (status IN ('ACTIVE','FROZEN','CLOSED')),
    CONSTRAINT ck_accounts_currency CHECK (currency ~ '^[A-Z]{3}$'),
    CONSTRAINT ck_accounts_name_length CHECK (char_length(name) BETWEEN 1 AND 255)
);

CREATE INDEX IF NOT EXISTS idx_accounts_currency ON ledger.accounts(currency);
CREATE INDEX IF NOT EXISTS idx_accounts_type     ON ledger.accounts(type);
CREATE INDEX IF NOT EXISTS idx_accounts_status   ON ledger.accounts(status);
CREATE INDEX IF NOT EXISTS idx_accounts_created  ON ledger.accounts(created_at DESC);

CREATE OR REPLACE TRIGGER trg_accounts_updated_at
    BEFORE UPDATE ON ledger.accounts
    FOR EACH ROW EXECUTE FUNCTION shared.set_updated_at();

Query patterns:

Get account by ID: PK lookup, p99 < 1ms
List accounts by currency / type / status: indexed, p99 < 50ms for up to 1000 results
Update account: PK + WHERE version = :v (optimistic locking via @Version)

Notes:

id is stored as a 128-bit UUID value (ULID-compatible, time-sortable). The API edge layer serializes it as acc_<crockford-base32> (see ADR-0010). The prefix is never stored in the database.
version maps to Hibernate @Version for optimistic locking. No @Version on immutable tables (transactions, entries).

Table: `transactions`

CREATE TABLE IF NOT EXISTS ledger.transactions (
    id              UUID PRIMARY KEY,
    reference       VARCHAR(255)  NOT NULL,
    description     VARCHAR(2048),
    status          VARCHAR(16)   NOT NULL,
    reverses_id     UUID,
    metadata        JSONB         NOT NULL DEFAULT '{}',
    posted_at       TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    created_by      VARCHAR(64)   NOT NULL,

    CONSTRAINT uq_transactions_reference UNIQUE (reference),
    CONSTRAINT ck_transactions_status CHECK (status IN ('POSTED','REVERSED')),
    CONSTRAINT fk_transactions_reverses FOREIGN KEY (reverses_id)
        REFERENCES ledger.transactions(id) ON DELETE RESTRICT
);

CREATE INDEX IF NOT EXISTS idx_transactions_status    ON ledger.transactions(status);
CREATE INDEX IF NOT EXISTS idx_transactions_posted_at ON ledger.transactions(posted_at DESC);
-- Partial unique constraint: at most one reversal per original transaction (A6)
CREATE UNIQUE INDEX IF NOT EXISTS uq_transactions_reverses_id
    ON ledger.transactions(reverses_id)
    WHERE reverses_id IS NOT NULL;

Query patterns:

Get transaction by ID: PK lookup
Find by reference (idempotency): unique index uq_transactions_reference
List recent by date: idx_transactions_posted_at
Reversal guard: uq_transactions_reverses_id prevents duplicate reversals at DB level

Notes:

transactions is insert-only. No @Version. Status transitions are append-only (a reversal inserts a new transaction that references the original via reverses_id).
The partial unique index on reverses_id (decision A6) ensures that each original transaction can only be reversed once, enforced without a full-table scan.
id is exposed at the API as tx_<crockford-base32> (see ADR-0010).

Table: `entries` (range-partitioned by quarter)

-- Parent partitioned table - created from migration 012 (decision A5)
CREATE TABLE IF NOT EXISTS ledger.entries (
    id              UUID            NOT NULL,
    transaction_id  UUID            NOT NULL,
    account_id      UUID            NOT NULL,
    amount          NUMERIC(38, 18) NOT NULL,
    currency        CHAR(3)         NOT NULL,
    direction       VARCHAR(8)      NOT NULL,
    posted_at       TIMESTAMPTZ     NOT NULL DEFAULT NOW(),
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW(),

    CONSTRAINT pk_entries PRIMARY KEY (id, created_at),
    CONSTRAINT fk_entries_transaction FOREIGN KEY (transaction_id)
        REFERENCES ledger.transactions(id) ON DELETE RESTRICT,
    CONSTRAINT fk_entries_account FOREIGN KEY (account_id)
        REFERENCES ledger.accounts(id) ON DELETE RESTRICT,
    CONSTRAINT ck_entries_direction CHECK (direction IN ('DEBIT','CREDIT')),
    CONSTRAINT ck_entries_currency  CHECK (currency ~ '^[A-Z]{3}$'),
    CONSTRAINT ck_entries_amount_nonzero CHECK (amount <> 0)
) PARTITION BY RANGE (created_at);

-- Initial partitions - created in the same migration; future partitions via pg_partman
CREATE TABLE IF NOT EXISTS ledger.entries_2026_q2 PARTITION OF ledger.entries
    FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

CREATE TABLE IF NOT EXISTS ledger.entries_2026_q3 PARTITION OF ledger.entries
    FOR VALUES FROM ('2026-07-01') TO ('2026-10-01');

CREATE TABLE IF NOT EXISTS ledger.entries_2026_q4 PARTITION OF ledger.entries
    FOR VALUES FROM ('2026-10-01') TO ('2027-01-01');

CREATE TABLE IF NOT EXISTS ledger.entries_2027_q1 PARTITION OF ledger.entries
    FOR VALUES FROM ('2027-01-01') TO ('2027-04-01');

-- Indexes on the parent are inherited by all partitions
-- Cursor pagination: (account_id, currency, created_at DESC, id DESC) - decision A9
CREATE INDEX IF NOT EXISTS idx_entries_account_cursor
    ON ledger.entries(account_id, currency, created_at DESC, id DESC);

-- Time-travel (asOf) queries: WHERE posted_at <= :asOf - decision D4
CREATE INDEX IF NOT EXISTS idx_entries_account_posted
    ON ledger.entries(account_id, currency, posted_at);

-- FK support
CREATE INDEX IF NOT EXISTS idx_entries_transaction_id
    ON ledger.entries(transaction_id);

Query patterns:

List entries for account with cursor pagination: idx_entries_account_cursor, p99 < 30ms for 50 rows
asOf balance query: SUM(amount) WHERE account_id = :id AND currency = :c AND posted_at <= :t, uses idx_entries_account_posted
Invariant check: per transaction, covered by transaction_id index

Notes:

Partitioned by quarter from the first migration (decision A5). Empty future partitions cost nothing and avoid the offline rename-copy-drop migration needed to partition an existing table.
pg_partman creates subsequent quarterly partitions automatically; initial partitions are pre-declared in the migration.
The composite PK includes created_at (the partition key) because PostgreSQL requires the partition key to be part of a unique constraint on a partitioned table.
id exposed at the API as ent_<crockford-base32> (see ADR-0010).

THE INVARIANT: statement-level deferred trigger on `entries`

-- Function checks sum-to-zero once per transaction at COMMIT time (decision A4)
CREATE OR REPLACE FUNCTION ledger.verify_double_entry_invariant()
RETURNS TRIGGER AS $$
DECLARE
    v_bad_currency TEXT;
    v_bad_sum      NUMERIC(38,18);
BEGIN
    -- Runs once per INSERT statement (FOR EACH STATEMENT), so we must check
    -- all currencies in the transactions that were touched by this statement.
    SELECT e.currency, SUM(e.amount)
    INTO   v_bad_currency, v_bad_sum
    FROM   ledger.entries e
    WHERE  e.transaction_id = ANY(
               SELECT DISTINCT transaction_id FROM ledger.entries
               -- tg_argv[0] is unused for statement-level; we aggregate all
               -- entries modified in this statement via a transition table
           )
    GROUP BY e.currency
    HAVING SUM(e.amount) <> 0
    LIMIT 1;

    -- Alternatively (cleaner with transition tables): see note below
    SELECT e.currency, SUM(e.amount)
    INTO   v_bad_currency, v_bad_sum
    FROM   new_entries n
    JOIN   ledger.entries e USING (transaction_id)
    GROUP  BY e.transaction_id, e.currency
    HAVING SUM(e.amount) <> 0
    LIMIT  1;

    IF v_bad_currency IS NOT NULL THEN
        RAISE EXCEPTION
            'double-entry invariant violated: currency=%, sum=%',
            v_bad_currency, v_bad_sum
            USING ERRCODE = '22000';
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

-- Statement-level deferred constraint trigger - fires once per INSERT statement
-- at COMMIT time (decision A4: not per-row, to avoid partial-insert false failures)
CREATE CONSTRAINT TRIGGER trg_entries_invariant
    AFTER INSERT ON ledger.entries
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH STATEMENT
    EXECUTE FUNCTION ledger.verify_double_entry_invariant();

Why statement-level, not row-level:

A row-level trigger fires after each individual INSERT INTO entries. At that point only one leg of the double-entry exists, so the sum is always non-zero and the trigger would always raise.
A statement-level trigger fires once after all entries for the INSERT batch are written. Combined with DEFERRABLE INITIALLY DEFERRED, it fires at COMMIT time, after the application has inserted all legs.
This cuts one trigger invocation per entry to one invocation per posting transaction (decision A4). The latency claim in ADR-0007 should be re-benchmarked after this change.

Note on transition tables: PostgreSQL 10+ supports REFERENCING NEW TABLE AS new_entries in statement-level triggers, which is the clean way to identify which transaction_id values were touched. The migration should use that syntax; the pseudocode above is illustrative.

Table: `account_balances` (incremental row - not a view)

-- Real table, not a materialized view (ADR-0011 / decision D1)
CREATE TABLE IF NOT EXISTS ledger.account_balances (
    account_id      UUID            NOT NULL,
    currency        CHAR(3)         NOT NULL,
    balance         NUMERIC(38, 18) NOT NULL DEFAULT 0,
    last_posted_at  TIMESTAMPTZ     NOT NULL,
    version         BIGINT          NOT NULL DEFAULT 0,

    CONSTRAINT pk_account_balances PRIMARY KEY (account_id, currency),
    CONSTRAINT fk_account_balances_account FOREIGN KEY (account_id)
        REFERENCES ledger.accounts(id) ON DELETE RESTRICT,
    CONSTRAINT ck_account_balances_currency CHECK (currency ~ '^[A-Z]{3}$')
);

Posting protocol (must run in the same transaction as the entries insert):

-- 1. Lock the balance row for the duration of the posting transaction
SELECT balance FROM ledger.account_balances
WHERE account_id = :account_id AND currency = :currency
FOR UPDATE;

-- 2. Apply the delta (sum of all entries for this account in this transaction)
INSERT INTO ledger.account_balances (account_id, currency, balance, last_posted_at, version)
VALUES (:account_id, :currency, :delta, NOW(), 0)
ON CONFLICT (account_id, currency)
DO UPDATE SET
    balance        = ledger.account_balances.balance + EXCLUDED.balance,
    last_posted_at = EXCLUDED.last_posted_at,
    version        = ledger.account_balances.version + 1;

Query patterns:

Current balance: PK lookup (account_id, currency), O(1), p99 < 5ms
asOf balance: NOT this table - compute SUM(entries.amount) WHERE posted_at <= :t from entries directly (decision D4)

Reconciliation:

A scheduled job should periodically assert account_balances.balance = SUM(entries.amount) per (account_id, currency) to catch any application-layer bugs. This is also run as a correctness test.
A full-refresh materialized view is available as an optional, off-by-default reporting mode:

-- Optional reporting-only MV (not used for live balance reads)
CREATE MATERIALIZED VIEW IF NOT EXISTS ledger.account_balances_snapshot AS
SELECT
    account_id,
    currency,
    SUM(amount)  AS balance,
    MAX(created_at) AS last_entry_at,
    NOW()           AS computed_at
FROM ledger.entries
GROUP BY account_id, currency;

CREATE UNIQUE INDEX IF NOT EXISTS idx_account_balances_snapshot_unique
    ON ledger.account_balances_snapshot(account_id, currency);

-- Refresh on demand: REFRESH MATERIALIZED VIEW CONCURRENTLY ledger.account_balances_snapshot;

Design rationale: see ADR-0011. The account_balances table replaced the materialized view because MV full-refresh re-aggregates all entries on every post and cannot run inside the posting transaction. The incremental row is updated atomically in the same transaction under a SELECT ... FOR UPDATE row lock.

Partitioning management

pg_partman handles quarterly partition creation for entries automatically once configured:

-- pg_partman configuration (run after schema creation)
SELECT partman.create_parent(
    p_parent_table  => 'ledger.entries',
    p_control       => 'created_at',
    p_type          => 'range',
    p_interval      => '3 months',
    p_premake       => 4   -- pre-create 4 future quarters
);

Partition pruning works automatically when queries include created_at in the WHERE clause or range. Always include created_at in filters on entries for partition elimination.

Isolation and locking

Operation	Isolation	Lock
Post transaction	READ COMMITTED	`SELECT ... FOR UPDATE` on `account_balances` rows (decision D2)
Read balance	READ COMMITTED	none
`asOf` query	READ COMMITTED	none
Reverse transaction	READ COMMITTED	`SELECT ... FOR UPDATE` on the original `transactions` row

No REPEATABLE_READ or SERIALIZABLE required for the ledger posting path. Correctness is achieved by the FOR UPDATE row lock on the balance row plus the deferred sum-to-zero trigger.

Liquibase changelogs for this schema

v0.1/
  010-ledger-accounts.xml
  011-ledger-transactions.xml
  012-ledger-entries.xml           -- creates partitioned table + initial partitions
  013-ledger-invariant-trigger.xml -- statement-level deferred trigger (A4)
  014-ledger-balances.xml          -- account_balances real table (ADR-0011)

Changelog 014 supersedes the earlier 014-ledger-balances-mv.xml (MV-based approach). If upgrading an existing schema that has the MV, a separate migration drops the MV and creates the table.

Data Model Ledger

Data Model - Ledger Schema

Tables in this schema

Table: accounts

Table: transactions

Table: entries (range-partitioned by quarter)

THE INVARIANT: statement-level deferred trigger on entries

Table: account_balances (incremental row - not a view)

Partitioning management

Isolation and locking

Liquibase changelogs for this schema

Related

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Product

Architecture

Decisions (ADR)

Engineering

Delivery

Risk and Ops

Clone this wiki locally

Table: `accounts`

Table: `transactions`

Table: `entries` (range-partitioned by quarter)

THE INVARIANT: statement-level deferred trigger on `entries`

Table: `account_balances` (incremental row - not a view)