Building an Immutable Double-Entry Billing Ledger in PostgreSQL

You reach for an immutable ledger the moment a single mutable balance column stops being trustworthy — usually the first time a duplicated webhook or a botched refund leaves your reported revenue out of sync with the bank. This page is the concrete PostgreSQL build behind Reconciliation & Double-Entry Ledger: the exact tables, the append-only constraints, the trigger that makes an unbalanced transaction impossible to commit, and the queries that read balances back out. Everything here uses signed BIGINT minor units so money arithmetic is exact.

Trade-offs

The design choice on this page is how strictly to enforce immutability and balance at the database layer versus in application code. Pushing it into PostgreSQL costs a little flexibility but buys guarantees that survive buggy app deploys.

For most production billing systems the third row is the sweet spot: a deferred constraint trigger guarantees every transaction balances, and revoked UPDATE/DELETE makes entries append-only without the write-cost of per-row hashing.

Step-by-Step Implementation

1. Define accounts and entries with money as BIGINT minor units

CREATE TABLE ledger_accounts (
  ledger_account_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code     TEXT NOT NULL UNIQUE,        -- 'accounts_receivable', 'cash_in_transit'
  kind     TEXT NOT NULL CHECK (kind IN ('asset','liability','revenue','expense','equity')),
  currency CHAR(3) NOT NULL
);

CREATE TABLE ledger_entries (
  ledger_entry_id   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  transaction_id    UUID NOT NULL,
  ledger_account_id UUID NOT NULL REFERENCES ledger_accounts,
  amount            BIGINT NOT NULL CHECK (amount <> 0),   -- minor units, signed; no no-op legs
  currency          CHAR(3) NOT NULL,
  external_ref      TEXT,                                  -- gateway charge/refund/payout id
  occurred_at       TIMESTAMPTZ NOT NULL,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_entries_txn ON ledger_entries (transaction_id);
CREATE INDEX idx_entries_acct ON ledger_entries (ledger_account_id);
CREATE INDEX idx_entries_ext  ON ledger_entries (external_ref);

Storing amount as BIGINT cents (or the currency’s smallest unit) keeps every operation exact. A $29.00 charge is 2900; there is no float, so no 0.1 + 0.2 surprises and no rounding drift across millions of rows.

2. Make entries append-only

Immutability is enforced by removing the ability to mutate, not by trusting callers. Grant only INSERT and SELECT to the application role.

REVOKE UPDATE, DELETE, TRUNCATE ON ledger_entries FROM billing_app;
GRANT  INSERT, SELECT           ON ledger_entries TO   billing_app;

-- Belt and suspenders: a trigger that hard-blocks UPDATE/DELETE even from a privileged role.
CREATE OR REPLACE FUNCTION forbid_entry_mutation() RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION 'ledger_entries is append-only (% blocked)', TG_OP;  -- ✗ no edits, no deletes
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_entries_immutable
  BEFORE UPDATE OR DELETE ON ledger_entries
  FOR EACH ROW EXECUTE FUNCTION forbid_entry_mutation();

To correct a mistake you post a reversing transaction, never an edit. The error and its reversal both stay on the record.

3. Enforce balanced transactions with a deferred constraint trigger

The core invariant — debits and credits of one transaction_id sum to zero — is checked at commit time using a DEFERRABLE INITIALLY DEFERRED constraint trigger. Deferral is essential: it lets you insert each leg in turn and validates the whole transaction only when you COMMIT.

CREATE OR REPLACE FUNCTION assert_transaction_balanced() RETURNS trigger AS $$
DECLARE
  net BIGINT;
BEGIN
  SELECT COALESCE(SUM(amount), 0) INTO net
  FROM ledger_entries
  WHERE transaction_id = NEW.transaction_id;

  IF net <> 0 THEN
    RAISE EXCEPTION 'transaction % is unbalanced: net = %', NEW.transaction_id, net;  -- ✗ rollback
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER trg_balanced
  AFTER INSERT ON ledger_entries
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION assert_transaction_balanced();

4. Post a balanced transaction

BEGIN;
  INSERT INTO ledger_entries (transaction_id, ledger_account_id, amount, currency, external_ref, occurred_at)
  VALUES
    ('a1f0c2d4-0000-0000-0000-000000000001',
     (SELECT ledger_account_id FROM ledger_accounts WHERE code='cash_in_transit'),
      2900, 'USD', 'ch_3PqX...', now()),       -- ✅ debit
    ('a1f0c2d4-0000-0000-0000-000000000001',
     (SELECT ledger_account_id FROM ledger_accounts WHERE code='accounts_receivable'),
     -2900, 'USD', 'ch_3PqX...', now());        -- ✅ credit
COMMIT;   -- balanced trigger fires here: net = 0, commit succeeds

If you tried to commit with only the +2900 leg, the deferred trigger would compute net = 2900 and abort the whole transaction.

5. Query balances

A balance is a sum, not a stored field. Account balance is the sum of its entries; a trial balance proves the entire ledger sums to zero.

-- Single account balance
SELECT a.code, SUM(e.amount) AS balance_minor_units
FROM ledger_entries e
JOIN ledger_accounts a USING (ledger_account_id)
WHERE a.code = 'accounts_receivable'
GROUP BY a.code;

-- Trial balance: the whole ledger must net to zero
SELECT SUM(amount) AS should_be_zero FROM ledger_entries;

For hot balances at scale, refresh a per-account summary table inside the posting transaction (or a materialized view after each reconciliation close) rather than re-summing millions of rows per read.

Verification & Testing

Assert the negative cases, since those are the guarantees you care about. Insert a single unbalanced leg and assert COMMIT raises transaction % is unbalanced. Attempt an UPDATE ledger_entries SET amount = ... and assert it raises the append-only exception. Attempt a DELETE and assert the same. Insert a zero-amount leg and assert the amount <> 0 check rejects it.

-- Must fail: unbalanced transaction
BEGIN;
  INSERT INTO ledger_entries (transaction_id, ledger_account_id, amount, currency, occurred_at)
  VALUES ('d4...balance-test', (SELECT ledger_account_id FROM ledger_accounts WHERE code='cash_in_transit'),
          5000, 'USD', now());
COMMIT;   -- expect: ERROR transaction ... is unbalanced: net = 5000

Add a global property test in CI: after running the full billing fixture, assert SELECT SUM(amount) FROM ledger_entries = 0. If that ever fails, a balanced-transaction guarantee has been broken upstream. This pairs with the matching tests described in Reconciliation & Double-Entry Ledger.

Gotchas & Production Pitfalls

• Non-deferred triggers reject the first leg. A plain AFTER INSERT trigger sees net <> 0 after the first row and aborts. You must use DEFERRABLE INITIALLY DEFERRED so the check runs at COMMIT.
• Multi-currency in one transaction breaks the zero-sum. Summing USD and EUR amounts is meaningless. Either keep one currency per transaction_id, or extend the trigger to assert zero-sum per currency within the transaction.
• BIGINT overflow is real but distant. BIGINT caps near 9.2 quintillion minor units. That is fine for amounts, but a naive SUM over a giant table is the same type and still safe; just do not store major units by mistake.
• Revoking DML does not stop superusers. The REVOKE protects the app role; a superuser or migration can still edit. Keep the belt-and-suspenders mutation trigger and restrict superuser access in production.
• Forgetting external_ref makes reconciliation impossible. The matcher in the parent guide joins ledger entries to gateway settlements on external_ref. Always populate it from the gateway id when posting payment, refund, and payout legs.

Related

• Reconciliation & Double-Entry Ledger
• PostgreSQL vs Event-Sourced Ledger for Billing
• Using the Outbox Pattern for Reliable Billing Events
• Tax, Compliance & Revenue Recognition