Architecting an Immutable Double-Entry Accounting Engine in PostgreSQL

-- Strict immutable ledger schema
CREATE TABLE journal_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    posted_at TIMESTAMPTZ NOT NULL DEFAULT clock_timestamp(),
    reference_id TEXT NOT NULL,
    memo TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'posted' CHECK (status IN ('posted', 'reversed')),
    hash BYTEA NOT NULL,
    previous_hash BYTEA NOT NULL
);

CREATE TABLE journal_postings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    journal_entry_id UUID NOT NULL REFERENCES journal_entries(id) ON DELETE RESTRICT,
    account_id UUID NOT NULL REFERENCES chart_of_accounts(id),
    amount NUMERIC(18, 4) NOT NULL CHECK (amount != 0), -- positive = debit, negative = credit
    currency CHAR(3) NOT NULL DEFAULT 'USD'
);

-- Deferred constraint checking to guarantee atomic zero-sum balancing
CREATE OR REPLACE FUNCTION check_journal_zero_sum()
RETURNS TRIGGER AS $$
DECLARE
    v_sum NUMERIC(18, 4);
BEGIN
    SELECT COALESCE(SUM(amount), 0)
    INTO v_sum
    FROM journal_postings
    WHERE journal_entry_id = NEW.journal_entry_id;

    IF v_sum != 0 THEN
        RAISE EXCEPTION 'Double-entry violation: Journal entry % postings do not sum to 0.0000 (Current sum: %)',
            NEW.journal_entry_id, v_sum
            USING ERRCODE = 'data_exception';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER trigger_enforce_zero_sum
AFTER INSERT OR UPDATE ON journal_postings
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION check_journal_zero_sum();