Experiments on integrity of ledgers using constraints

ledger.sql

BEGIN;

CREATE TABLE transaction (
    id              uuid                PRIMARY KEY,
    time            timestamptz         NOT NULL DEFAULT (now()),
    ledger_id       uuid                NOT NULL,
    prev_id         uuid,
    prev_time       timestamptz,
    prev_balance    decimal             NOT NULL,
    genesis         bool,   -- It cannot be false, but only true or NULL.
    amount          decimal             NOT NULL,
    balance         decimal             NOT NULL,

    -- A unique index to guarantee every transaction has only one child at most.
    UNIQUE (prev_id),

    -- A unique index to be referenced by (ledger_id, prev_id, prev_time,
    -- balance).
    UNIQUE (ledger_id, id, time, balance),

    -- Guarantee that a transaction is chained from a previous transaction.
    FOREIGN KEY (ledger_id, prev_id, prev_time, prev_balance)
        REFERENCES transaction (ledger_id, id, time, balance),

    -- Gurantee a transaction cannot be a child of a future transaction.
    CHECK (prev_time < time),

    -- Guarantee a transaction delta amount cannot be zero unless it's genesis.
    CHECK (amount != 0 OR genesis IS NOT NULL AND amount = 0),

    -- Guarantee integrity between balance and its and its previous' balance.
    CHECK (balance = amount + coalesce(prev_balance, 0)),

    -- Guarantee genesis must not be false, but either only true or NULL.
    CHECK (genesis IS NULL OR genesis),

    -- Guarantee there is only one genesis transaction for each ledger.
    UNIQUE (ledger_id, genesis)
);

CREATE INDEX ON transaction (time);

-- Get the current balance of a ledger:
SELECT balance
FROM transaction
WHERE ledger_id = '00000000-0000-0000-0000-000000000000'
ORDER BY time DESC
LIMIT 1;

ROLLBACK;