Design a Ledger: Double-Entry vs Balance Column

Design a Ledger: Double-Entry vs Balance Column in one line: a balance column is one mutable number per account, fast to read but easy to corrupt with concurrent read-modify-write and impossible to explain afterwards; a double-entry ledger is an append-only table of entries where every transaction's legs sum to zero and a balance is the sum of an account's entries, so history is the truth and corrections are new rows. A periodic snapshot keeps that sum short. The column can survive as a cache; it should never be the record.

Side by side:

Question	Balance column	Append-only entries
Read a balance	one row	snapshot plus the entries after it
Two transfers at once	a lost update unless every write locks	two appends, nothing overwritten
Why is it 212.40?	no answer	the list of entries that sum to it
Fix a mistake	overwrite the number	append a reversing transaction

The problem it solves

Three things must stay true under retries, crashes and concurrency: no transfer half happens, no money appears or disappears, and any balance can be explained line by line. The obvious schema, accounts(id, balance) with an UPDATE per transfer, fails the last by design and the second under concurrency. The lesson's rule, no row is ever updated in place, is the answer. The sibling article, design a payment system, builds the full ledger with idempotency keys; this one is the comparison.

The intuition

The balance column stores the answer and throws away the working. A transfer of 30.00 from A reads A's balance, subtracts, and writes the new number back. Two problems follow:

• Lost updates. Two transfers out of A both read 100.00, one writes 70.00, the other writes 50.00, and the 30.00 debit vanishes while B still received it. Money was created. Atomic UPDATE ... SET balance = balance - 3000 or row locks fix this particular race, but only if every code path remembers to use them.
• No history. Once 70.00 overwrites 100.00, nothing records why. A wrong balance cannot be traced, and a correction is another silent overwrite.

The entries table stores the working and derives the answer. A transfer appends two legs in one transaction, A −3000 and B +3000 in cents. Nothing is read and nothing is overwritten, so concurrent transfers cannot lose each other. A balance is SUM(cents) over the account's entries. Mistakes are fixed by appending a reversing transaction, so the wrong entry and its correction both stay visible, like a refund on a till roll.

Double entry also gives an invariant you can check at any moment: every transaction's legs sum to zero, so the whole ledger sums to zero. A buggy writer or a manual hotfix that breaks it shows up in one GROUP BY query.

The price is the read. Summing every entry an account has ever had gets slower every year, so a periodic job stores a snapshot: the balance as of a known entry id. Today's balance is the snapshot plus the short tail of entries after it. Some designs also keep a balance column, but as a cache updated in the same transaction as the entries, never as the source of truth.

Watch it run

The animation follows one transfer of 40.00 from A to B, under the rule that no row is ever updated. An idempotency key stops a retry paying twice. The transfer becomes a debit row and a credit row, a pair that does not sum to zero is refused, and both commit together. A mistake gets a reversing pair appended underneath. Then the cost of deriving balances appears, millions of rows after a few years, and a snapshot makes today's balance a short sum.

The code

Derived from the lesson's model, on SQLite so the transactions are real. accounts holds the column, entries the ledger, and A starts with 100.00 in cents. Each transfer is a generator that pauses after its read, so two requests interleave explicitly, with no threads:

import itertools
import random
import sqlite3

def fresh():
    """A in cents: 100.00 opened from 'world', so even the deposit is a balanced pair."""
    db = sqlite3.connect(":memory:", isolation_level=None)   # we issue BEGIN/COMMIT
    db.executescript("""
    CREATE TABLE accounts  (id TEXT PRIMARY KEY, balance INTEGER NOT NULL);
    CREATE TABLE entries   (id INTEGER PRIMARY KEY, txn TEXT NOT NULL,
                            account TEXT NOT NULL, cents INTEGER NOT NULL);
    CREATE TABLE snapshots (account TEXT PRIMARY KEY, cents INTEGER NOT NULL,
                            upto INTEGER NOT NULL);
    INSERT INTO accounts VALUES ('A', 10000), ('B', 0), ('C', 0);
    INSERT INTO entries (txn, account, cents) VALUES ('open', 'world', -10000), ('open', 'A', 10000);
    """)
    return db

def column_transfer(db, src, dst, cents, txn):
    """Read-modify-write on a balance column, the way application code often does it."""
    a, b = (db.execute("SELECT balance FROM accounts WHERE id = ?", (x,)).fetchone()[0]
            for x in (src, dst))
    yield                                              # the other request runs here
    db.execute("UPDATE accounts SET balance = ? WHERE id = ?", (a - cents, src))
    db.execute("UPDATE accounts SET balance = ? WHERE id = ?", (b + cents, dst))

def entry_transfer(db, src, dst, cents, txn):
    """Two legs appended in one transaction: nothing is read, nothing is overwritten."""
    legs = [(txn, src, -cents), (txn, dst, cents)]
    yield
    db.execute("BEGIN IMMEDIATE")
    db.executemany("INSERT INTO entries (txn, account, cents) VALUES (?, ?, ?)", legs)
    db.execute("COMMIT")

def run(transfer, schedule):
    db = fresh()
    steps = [transfer(db, "A", "B", 3000, "t1"), transfer(db, "A", "C", 5000, "t2")]
    for i in schedule:                                 # which request takes its next step
        next(steps[i], None)
    for s in steps:
        next(s, None)
    return db

column = lambda db: dict(db.execute("SELECT id, balance FROM accounts"))
ledger = lambda db: dict(db.execute("SELECT account, SUM(cents) FROM entries "
                                    "WHERE account != 'world' GROUP BY account"))
print(column(run(column_transfer, [0, 1, 0, 1])))     # interleaved: both read A = 100.00
# {'A': 5000, 'B': 3000, 'C': 5000}
print(ledger(run(entry_transfer, [0, 1, 0, 1])))
# {'A': 2000, 'B': 3000, 'C': 5000}
schedules = set(itertools.permutations([0, 0, 1, 1]))
print(len(schedules),
      sum(sum(column(run(column_transfer, s)).values()) != 10000 for s in schedules),
      sum(sum(ledger(run(entry_transfer, s)).values()) != 10000 for s in schedules))
# 6 4 0

The interleaved column run ends with 130.00 in the system after starting with 100.00: transfer t1's debit was overwritten. Of the six possible orders of the two requests' steps, four lose an update on the column; none do on the ledger. Next, the write path and the invariant. post refuses legs that do not sum to zero, a mistake is corrected by a reversal, and a raw insert that bypasses post is caught by the audit query:

class Unbalanced(Exception):
    pass

def post(db, txn, legs):
    """The only write path: all legs of one transaction, and they must sum to zero."""
    if sum(c for _, c in legs) != 0:
        raise Unbalanced(txn)
    db.execute("BEGIN IMMEDIATE")
    db.executemany("INSERT INTO entries (txn, account, cents) VALUES (?, ?, ?)",
                   [(txn, a, c) for a, c in legs])
    db.execute("COMMIT")

def unbalanced(db):
    """The invariant, checkable at any time: every transaction's legs sum to zero."""
    return db.execute("SELECT txn, SUM(cents) FROM entries GROUP BY txn "
                      "HAVING SUM(cents) != 0").fetchall()

db = run(entry_transfer, [0, 1, 0, 1])
post(db, "t3", [("C", -1000), ("A", 1000)])           # meant for B, sent to A by mistake
post(db, "t3-reverse", [("A", -1000), ("C", 1000)])   # a correction is one more transaction
post(db, "t3-fixed", [("C", -1000), ("B", 1000)])
try:
    post(db, "t4", [("A", -500), ("B", 400)])
except Unbalanced as e:
    print("refused", e)                               # refused t4
db.execute("INSERT INTO entries (txn, account, cents) VALUES ('hotfix', 'B', 700)")  # a bypass
print(unbalanced(db))                                 # [('hotfix', 700)]
print(db.execute("SELECT txn, cents FROM entries WHERE account = 'A' ORDER BY id").fetchall())
# [('open', 10000), ('t1', -3000), ('t2', -5000), ('t3', 1000), ('t3-reverse', -1000)]

A's history now explains its balance of 20.00 line by line, including the mistake and its reversal. Finally the snapshot: 4,000 seeded random transfers, a snapshot, 150 more. Snapshot plus tail must equal the full sum, then again over 40 rounds with snapshots at random points:

def full_balance(db, account):
    return db.execute("SELECT COALESCE(SUM(cents), 0), COUNT(*) FROM entries "
                      "WHERE account = ?", (account,)).fetchone()

def take_snapshot(db):
    """Freeze every balance at the current last entry id."""
    db.execute("BEGIN IMMEDIATE")
    upto = db.execute("SELECT MAX(id) FROM entries").fetchone()[0]
    db.execute("INSERT OR REPLACE INTO snapshots SELECT account, SUM(cents), ? FROM entries "
               "WHERE id <= ? GROUP BY account", (upto, upto))
    db.execute("COMMIT")

def balance(db, account):
    """Snapshot plus the tail after it: the same number, a much shorter sum."""
    cents, upto = db.execute("SELECT cents, upto FROM snapshots WHERE account = ?",
                             (account,)).fetchone() or (0, 0)
    tail, rows = db.execute("SELECT COALESCE(SUM(cents), 0), COUNT(*) FROM entries "
                            "WHERE account = ? AND id > ?", (account, upto)).fetchone()
    return cents + tail, rows

db, rng, ids = fresh(), random.Random(16), itertools.count()
names = ["world", "A", "B", "C", "D"]                  # no overdraft rule in this toy

def random_transfers(n):
    for _ in range(n):
        src, dst = rng.sample(names, 2)
        cents = rng.randint(1, 9999)
        post(db, "r%d" % next(ids), [(src, -cents), (dst, cents)])

random_transfers(4000)
take_snapshot(db)
random_transfers(150)
print(full_balance(db, "A"), balance(db, "A"))        # (-407718, 1676) (-407718, 53)

ok = True
for _ in range(40):                                    # snapshots at random points
    random_transfers(rng.randint(0, 300))
    if rng.random() < 0.5:
        take_snapshot(db)
    ok &= all(balance(db, a)[0] == full_balance(db, a)[0] for a in names)
print(ok, unbalanced(db), db.execute("SELECT COUNT(*) FROM entries").fetchone()[0])
# True [] 18882

Same balance for A both ways, but the snapshot read summed 53 rows instead of 1,676. The ledger grew to 18,882 entries and every transaction still sums to zero.

The complexity

• Balance column: O(1) read and write, O(1) storage per account, no history.
• Entries, no snapshot: O(1) append per leg; a balance read is O(entries for the account) and grows forever.
• Entries with snapshots: a balance read is one snapshot row plus O(entries since the snapshot), bounded by how often snapshots run.
• Invariant check: one GROUP BY over the entries, or over the entries since the last checked id.

Where it goes wrong

• Read-modify-write in application code. The interleaving above, in production. If a column exists, update it atomically inside the same transaction as the entries.
• Updating or deleting entries. One edited row breaks the explanation of every balance after it. Append a reversal instead.
• Legs written in separate transactions. A crash between them leaves half a transfer.
• Overdraft checks outside the transaction. A rule like "no negative balances" needs a read, and that read must sit inside the transaction under a lock or a strict isolation level.
• Floats for money. Store integer cents.
• Snapshots that are not tied to an entry id. A snapshot taken "at midnight" while writes continue can double-count or skip entries; pin it to a last-included id.

When it shows up in interviews

As "design a wallet", "design a ledger" or the checkout step of a payment design, usually with the follow-up "why not just store the balance?". Expect lost updates, auditability, corrections and fast balance reads. Atomic commits are covered in ACID transactions, and the lesson is design a payment ledger.

How to say it in an interview

"I would not make a mutable balance column the record. Concurrent read-modify-write loses updates, and once a number is overwritten nobody can explain it. Instead the ledger is append-only and double-entry: each transfer is legs in integer cents that sum to zero, written in one transaction, and a balance is the sum of the account's entries. Mistakes get a reversing transaction, never an edit, and I can check the zero-sum invariant at any time. To keep reads fast I store periodic snapshots pinned to an entry id and sum only the tail; a balance column can exist as a cache updated in the same transaction."

Frequently asked questions

Why not just store a balance column?

Because concurrent read-modify-write can lose an update and create or destroy money, and an overwritten number cannot be explained or audited. A balance column is fine as a cache updated in the same transaction as the entries, but the entries, not the column, are the record.

How do you calculate a balance in a double-entry ledger?

Sum the account's entries. To keep that fast, store a periodic snapshot of each balance pinned to the last entry id it includes; the current balance is the snapshot plus the sum of the entries after that id. Both ways give the same number.

What is the double-entry invariant?

Every transaction's legs sum to zero: each debit is matched by credits of the same total. So the whole ledger sums to zero, and one query grouping entries by transaction and keeping non-zero sums finds any write that broke the rule.