Double-Entry Ledger

Design the schema for the double-entry ledger behind a wallet or payments product. Every money movement posts balanced debit and credit entries across accounts, balances must be correct at any point in time, and posted history is never edited.

Clarifying Questions

Is this ledger the system of record for money, or a mirror of a bank's books?
Does each account hold one currency, or can an account hold several?
Do we need an account's balance as of any past moment, or only the current balance?
How hot are the busiest accounts, such as a platform fees account that every payment touches?
How are mistakes corrected: reversing transactions, or adjustments with a reason code?

Core Requirements

Model accounts with a type (asset, liability, equity, revenue, or expense) and a currency.
Post a transaction as two or more entries whose debits equal its credits.
Reject duplicate postings from retried requests.
Read an account's current balance in constant time.
Read an account's balance as of any past posting.
Never update or delete posted entries; correct them with reversals.

Canonical schema (PostgreSQL)

CREATE TABLE accounts (
  id BIGSERIAL PRIMARY KEY,
  owner_id UUID,
  code TEXT UNIQUE NOT NULL,
  type TEXT NOT NULL CHECK (type IN ('asset', 'liability', 'equity', 'revenue', 'expense')),
  normal_side TEXT NOT NULL CHECK (normal_side IN ('debit', 'credit')),
  currency CHAR(3) NOT NULL,
  status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'frozen', 'closed')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE journal_transactions (
  id BIGSERIAL PRIMARY KEY,
  idempotency_key TEXT UNIQUE NOT NULL,
  kind TEXT NOT NULL,
  description TEXT,
  reverses_id BIGINT REFERENCES journal_transactions(id),
  effective_at TIMESTAMPTZ NOT NULL,
  posted_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE entries (
  id BIGSERIAL PRIMARY KEY,
  transaction_id BIGINT NOT NULL REFERENCES journal_transactions(id),
  account_id BIGINT NOT NULL REFERENCES accounts(id),
  direction TEXT NOT NULL CHECK (direction IN ('debit', 'credit')),
  amount_minor BIGINT NOT NULL CHECK (amount_minor > 0),
  currency CHAR(3) NOT NULL,
  account_version BIGINT NOT NULL,
  balance_after_minor BIGINT NOT NULL,
  effective_at TIMESTAMPTZ NOT NULL
);

CREATE TABLE account_balances (
  account_id BIGINT PRIMARY KEY REFERENCES accounts(id),
  balance_minor BIGINT NOT NULL DEFAULT 0,
  version BIGINT NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- assert_transaction_balanced() sums debits and credits per currency for the
-- transaction and raises if they differ. Deferred, so it runs at COMMIT.
CREATE CONSTRAINT TRIGGER entries_balanced
  AFTER INSERT ON entries
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION assert_transaction_balanced();

REVOKE UPDATE, DELETE ON entries, journal_transactions FROM app_writer;

Key Indexes

CREATE UNIQUE INDEX uq_entries_account_version ON entries(account_id, account_version)

Gives each account a gapless posting order; the balance after any posting is one index lookup, and a duplicate version fails loudly.

CREATE INDEX idx_entries_transaction ON entries(transaction_id)

Loads every leg of a transaction for the balance check, receipts, and reversals.

CREATE INDEX idx_entries_account_effective ON entries(account_id, effective_at DESC)

Serves account statements and effective-date reporting over a date range.

CREATE UNIQUE INDEX uq_txn_reverses ON journal_transactions(reverses_id) WHERE reverses_id IS NOT NULL

A transaction can be reversed only once; the partial index skips the many rows that reverse nothing.

CREATE INDEX idx_accounts_owner ON accounts(owner_id) WHERE owner_id IS NOT NULL

Lists a customer's accounts; internal accounts such as fees and suspense have no owner and stay out of the index.

Key Decisions to Defend

Entries store a positive amount plus a direction, matching accounting convention; the account's normal_side decides whether a debit raises or lowers its balance.
Balance is enforced by a deferred constraint trigger, so an unbalanced transaction cannot commit regardless of what the application code does.
Posting locks the account_balances row (SELECT ... FOR UPDATE), writes the entry with version + 1 and its balance_after, and updates the row in the same transaction.
Corrections are reversing transactions linked by reverses_id, and UPDATE and DELETE are revoked on entries at the role level.
The unique idempotency_key turns a retried posting into a no-op that returns the original transaction.

Likely follow-ups

· The fees account takes 5,000 postings a second and its balance row lock is the bottleneck, what do you change?
· Post a transfer from a USD account to a EUR account, how many entries and which accounts?
· Finance needs month-end balances that include entries backdated after the close.
· How do you reconcile this ledger against the bank's daily statement?

Dive Deeper

Ledger design tests whether you treat money as an append-only record with invariants the database enforces. Strong candidates name double entry immediately, store integer minor units, make the balance check impossible to bypass, and correct mistakes with reversals. The hot account follow-up is the senior probe: one balance row serializes every posting, and the usual answers are splitting the account into sub-accounts or batching postings, each with its own reconciliation cost.

All Database Design Prompts