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 choice is how far down the stack to push immutability and balance enforcement — from app-only checks to a DB trigger with a hash chain. Each step down costs a little write latency and buys a stronger guarantee. The map ranks them by tamper resistance.

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.

The hash-chain row in the table earns its place only when an external party audits the raw rows and you cannot ask them to trust your Postgres access controls. It stores, per entry, a SHA-256 of the previous entry’s hash concatenated with the current row’s canonical fields, so editing any historical amount breaks every downstream hash and the tampering is detectable by re-walking the chain. The cost is not just the 0.5–1 ms of hashing on the write path; it is that the chain serializes inserts (each entry needs its predecessor’s hash), which caps posting throughput and makes the ledger a contention point under concurrent load. Unless a regulator or an outside auditor genuinely reads these rows, the guaranteed balance from the trigger plus revoked DML already covers the failure modes that actually bite billing teams — duplicated webhooks, half-applied refunds, and app bugs that write one leg but not the other.

There is a subtler reason to prefer database-level enforcement over application checks: application checks are only as trustworthy as the process that ran them. A backfill script run from a psql prompt during an incident, a data-migration job, or a second service that learns to write to ledger_entries all bypass the app’s validation layer. The constraint trigger does not care which connection or which codebase issued the INSERT; it fires for every writer. That property is what lets you treat the ledger sum as ground truth in reconciliation rather than as one more number that might have drifted.

Step-by-Step Implementation

The five steps stack the guarantees in the database: minor-unit money, append-only via revoked DML plus a trigger, a deferred zero-sum constraint, then balanced posting and sum-based balance reads. The diagram shows the two triggers that make edits impossible and imbalances uncommittable.

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.

A recurring question is why not NUMERIC, which is also exact. NUMERIC is exact but slow — it is a variable-length, software-implemented decimal, and a SUM over a few million NUMERIC rows runs several times slower than the same sum over BIGINT, which the CPU adds natively. Because every balance read on this design is a SUM, that difference compounds. The one place NUMERIC earns its keep is a currency with more than the usual minor-unit precision or an internal unit finer than the smallest coin (fractional-cent usage metering, say $0.0001 per API call). For those, keep the ledger in whole micro-units — store amount as BIGINT micro-units and only divide for display — so the zero-sum trigger still operates on native integers.

Note that currency appears on both ledger_accounts and ledger_entries. The redundancy is deliberate: an entry’s currency must match its account’s currency, and carrying it on the entry lets the zero-sum trigger group by currency without a join back to the account table on every commit. Enforce the agreement with a composite foreign key or a trigger check so a USD entry can never be posted against an EUR account.

Why a transaction_id instead of an explicit debit/credit pair

A double-entry system is sometimes modelled as one row per journal with debit_account_id and credit_account_id columns. That works for the simplest case — one debit, one credit — but breaks the moment a real billing event splits across more than two accounts. A single invoice payment might land as one debit to cash_in_transit, one credit to accounts_receivable, and a separate credit to processor_fees for the gateway’s cut. Modelling each posting as its own signed row grouped by transaction_id handles any number of legs uniformly, and the only invariant to enforce is that the legs sharing a transaction_id net to zero. It also keeps every leg a first-class row you can index, filter, and sum, rather than hiding half the money in a column you have to negate at read time.

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.

The reversing transaction is not a delete in disguise — it is a new, fully balanced transaction whose legs are the sign-flipped legs of the original. If a 2900 charge was posted against the wrong customer_id, you post -2900 against the same accounts (which drives the erroneous balance back to zero) and then post the correct 2900 transaction. All three transactions survive. Auditors and support engineers can see exactly what happened and when, which is the whole point of an append-only ledger: the history is the source of truth, and a corrected mistake tells a truer story than a silently overwritten one. Link the reversal to the original with a reverses_transaction_id column so tooling can pair them without string-matching on external_ref.

The append-only rule also shapes how you handle schema evolution. Because you never UPDATE, adding a column later means new entries carry it and old entries have NULL — you cannot backfill by editing rows. Design for that: make new columns nullable or give them a default that is meaningful for historical rows, and derive any richer classification of old entries in a view rather than by mutating the base table.

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

The tests you care about are the negative ones — the guarantees are about what the database refuses. Assert an unbalanced commit fails, an UPDATE fails, a DELETE fails, and a zero-amount leg fails. Plus a CI property test that the whole ledger sums to zero. The panel lists them.

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

The pitfalls are Postgres-specific traps around the trigger and the money type: a non-deferred trigger rejects the first leg, multi-currency breaks the zero-sum, revoked DML doesn’t stop superusers, and a missing external_ref makes reconciliation impossible. The map groups them.

• 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.

Frequently Asked Questions

Does a billing system really need double entry? Once money moves in more than one direction — charges, refunds, credits, payouts, fees — yes. Single-entry records cannot be checked, and the first unexplained discrepancy is when that matters.

How is immutability enforced in practice? By revoking update and delete on the entries table and correcting with reversing entries instead. Application-level discipline alone does not survive a data-fix script.

What granularity should entries have? One entry per economic event, keyed by the identifier of that event, so replay is idempotent. Aggregating several events into one entry loses the traceability the ledger exists to provide.

How should the ledger be checked? A periodic assertion that every transaction’s debits equal its credits, and that account balances derived from entries match any cached balances. Both are cheap and catch a whole class of defects immediately.

Related

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