Double-Entry Ledger Design: Immutable Postings, Idempotency, and Balances That Always Reconcile

Why the balance column fails

A mutable balance holds no history. When it is wrong — and concurrency bugs, partial failures, and duplicate webhooks guarantee it eventually will be — there is no record of how it got there. Worse, a mutable balance cannot represent money in flight: funds that have left one account and not yet arrived at another have nowhere to exist, so systems built this way tend to make money vanish and reappear during failures.

Double-entry solves both. Every movement is recorded twice, from and to, so the system is self-checking, and intermediate accounts give in-flight money a place to sit where it is visible and auditable.

The schema

CREATE TABLE accounts (
  id            BIGSERIAL PRIMARY KEY,
  name          TEXT NOT NULL,
  type          TEXT NOT NULL CHECK (type IN
                  ('asset','liability','equity','revenue','expense')),
  currency      CHAR(3) NOT NULL,
  UNIQUE (name, currency)
);

CREATE TABLE transactions (
  id              BIGSERIAL PRIMARY KEY,
  idempotency_key TEXT NOT NULL UNIQUE,   -- the whole safety story
  description     TEXT NOT NULL,
  occurred_at     TIMESTAMPTZ NOT NULL,   -- when it happened
  recorded_at     TIMESTAMPTZ NOT NULL DEFAULT now()  -- when we learned
);

CREATE TABLE postings (
  id             BIGSERIAL PRIMARY KEY,
  transaction_id BIGINT NOT NULL REFERENCES transactions(id),
  account_id     BIGINT NOT NULL REFERENCES accounts(id),
  -- Minor units. Positive = debit, negative = credit. Never a float.
  amount         BIGINT NOT NULL,
  currency       CHAR(3) NOT NULL
);

CREATE INDEX ON postings (account_id, transaction_id);

There is no UPDATE and no DELETE on postings, ever. A mistake is corrected by writing a reversing transaction, which leaves both the error and the correction in the record — that is a feature, and it is what an auditor or a regulator will ask you for.

The invariant, enforced by the database

Debits must equal credits within every transaction and every currency. Enforce that in the database rather than in application code, because application code is where the exception always turns out to be.

CREATE OR REPLACE FUNCTION assert_balanced() RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM postings
    WHERE transaction_id = NEW.transaction_id
    GROUP BY currency
    HAVING SUM(amount) <> 0
  ) THEN
    RAISE EXCEPTION 'Unbalanced transaction %', NEW.transaction_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER postings_balanced
  AFTER INSERT ON postings
  DEFERRABLE INITIALLY DEFERRED   -- checked at COMMIT, after all legs land
  FOR EACH ROW EXECUTE FUNCTION assert_balanced();

The deferred constraint is what allows a multi-leg transaction to be inserted row by row inside one database transaction and still be validated as a whole at commit. Without DEFERRABLE, the check fires after the first leg and every transaction fails.

Idempotency and money in flight

Payment processors retry webhooks. Clients retry on timeout. Queues deliver at least once. Without an idempotency key on transactions, every one of those retries doubles a payment. The unique constraint above turns a duplicate into a harmless no-op: catch the violation, return the existing transaction, done.

Money in flight is the other half. An outbound payment is not a single movement from user to outside world — it is two: user account to a clearing account when submitted, and clearing account to external when settlement confirms. If settlement fails, you reverse the second leg only. At any moment, the clearing account's balance tells you exactly how much money is in transit, which is a number operations teams need and mutable-balance systems cannot produce.

• Never post directly between a user account and an external account; always route through a clearing or suspense account.
• Fees are their own posting to a revenue account, not a silent reduction of the transfer amount.
• Foreign exchange creates two currency-balanced legs joined by an FX gain or loss account — never one posting with two currencies.
• Keep occurred_at and recorded_at distinct so late-arriving information does not corrupt historical reporting.

Derived balances and reconciliation

Balance is a query: the sum of postings for an account. That is correct by construction but slow at volume, so add periodic snapshots — a materialized balance as of a checkpoint — and compute the current balance as the snapshot plus postings since. The snapshot is a cache that can always be rebuilt from postings, never a source of truth.

• Run a daily job asserting that every snapshot equals a full recomputation. A divergence means a bug, and you want to find it that day, not that quarter.
• Reconcile against external truth — bank statements, processor settlement reports, on-chain balances — and post any genuine difference to an explicit reconciliation account rather than adjusting a balance quietly.
• Alert on the age and size of unreconciled items; a growing suspense balance is the earliest signal that something upstream broke.
• The accounting equation across all accounts must sum to zero at all times. Monitor it as a single number — if it is ever non-zero, stop and investigate before anything else.