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.

---

Related

• Data-Model - conventions, ERD, cross-cutting sections
• ADR-0007 - invariant enforcement rationale
• ADR-0010 - id format at the API edge
• ADR-0011 - balance strategy
• Domain-Model - aggregate definitions