Double-entry accounting inside an application database

Most application databases hold money in a column called balance, or credit, or amount_due, and change it with an update when something happens. It is the obvious design and it fails in a specific, expensive way: the number is the only record of itself.

When the figure is wrong, and eventually it will be, there is nothing to interrogate. You cannot ask why it is that number, because the history of how it got there was overwritten each time it changed. You can only ask somebody to guess, and then adjust it, which produces a number that is wrong in a new way nobody can explain either.

The alternative is to stop storing the balance and start recording the movements. The balance becomes something you calculate, and every figure in the business becomes explainable by the rows that produced it.

The question that separates the two designs: can you show, for any figure on any report, the list of events that add up to it? With a balance column the honest answer is no.

Stripped of vocabulary, double-entry bookkeeping is one idea: money never appears or disappears, it only moves between places. So every transaction is recorded as a set of movements, and that set must sum to zero.

A customer pays an invoice. Cash increases; the amount they owe you decreases. Two movements, equal and opposite, one transaction. Payroll runs: an expense increases, cash decreases, tax withheld becomes a liability. Three movements, still summing to zero.

That is the whole invariant, and it is worth appreciating what it buys. If every transaction sums to zero, then the sum of everything in the ledger is zero, permanently. A system that cannot lose money without the total going non-zero has a self-check no amount of application logic can provide.

You do not need to know what a debit is to implement this correctly. You need signed amounts and a constraint that they sum to zero.

Three tables carry the whole model. An account is a place money can be; a transaction groups movements that happened together; an entry is one movement.

• One signed amount per entry, not separate debit and credit columns. Two nullable columns where exactly one must be set is a constraint you will have to write anyway, expressed worse
• Positive means debit by convention. Which sign you choose does not matter; choosing once and writing it down does
• The transaction date and the date it was posted are different facts. Keep both, because a January invoice entered in February belongs to January in the accounts and to February in the audit trail

account
  id, code, name
  type          -- asset | liability | equity | revenue | expense

journal_txn                  -- what happened, once
  id, occurred_on, description
  source_type, source_id     -- the event that caused it (section 07)
  UNIQUE (source_type, source_id)

journal_entry                -- the movements, two or more per txn
  id, txn_id, account_id
  amount_minor  bigint       -- signed. positive = debit

-- The invariant. Enforced by the database, checked per transaction:
--   SELECT txn_id FROM journal_entry
--   GROUP BY txn_id HAVING SUM(amount_minor) <> 0;
-- must always return nothing.

The zero-sum rule is the only thing standing between a ledger and a spreadsheet, so it belongs in the database rather than in whichever service happens to be writing.

It cannot be a simple check constraint, because it is a property of a group of rows rather than one row. The practical options are a deferred constraint trigger that fires at commit, or writing entries only through a procedure that validates the set before inserting it.

• Validate at commit, not per row, because a balanced transaction is unbalanced halfway through being written
• Reject the transaction rather than correcting it. A ledger that silently adds a balancing figure is worse than one that fails
• Have a scheduled check that sums the entire ledger and alerts if it is non-zero. It should never fire, and the day it does you want to know before the accountant does

If entries can be written by more than one code path, the constraint has to be in the database. Application-level validation protects the paths you remembered.

This is the rule that developers find hardest, because every instinct built by CRUD says a wrong row should be corrected in place.

A posted entry is a historical claim: on this date, this movement was recorded. Editing it changes the past, which means yesterday's report can no longer be reproduced and the audit trail becomes a description of the present rather than a record of what happened.

Corrections are new transactions. To void something, post its exact reverse and link the two. The original stays, the reversal stays, and the net effect is zero, which is both correct and legible to anyone asking what happened.

• No UPDATE and no DELETE on journal_entry. Enforce it with grants, not with a comment
• A void posts a reversing transaction that references the original
• A correction is a reversal plus a fresh posting, not an edit
• Keep who posted it and when, separately from the transaction date

This is also what makes a voided bill safe. Deleting it makes the day's takings disagree with the till and gives nobody a way to find out why; reversing it leaves both facts visible.

Developers usually stall here, because debit and credit do not mean what everyday language suggests. They are not good and bad, or in and out. They are just the two directions, and which one increases an account depends on what kind of account it is.

Asset

Increases with a debit. Cash, bank, amounts customers owe you, stock on the shelf.

Expense

Increases with a debit. Wages, rent, cost of goods sold.

Liability

Increases with a credit. Amounts you owe suppliers, tax collected, unspent customer credit.

Revenue

Increases with a credit. Sales, fees, service income.

Equity

Increases with a credit. Owner capital, retained earnings.

With a single signed column and positive meaning debit, an account's balance is simply the sum of its entries. Assets and expenses normally come out positive, the rest normally negative, and you flip the sign for display where an accountant expects it. Do not model the flip in the data.

The ledger should not be written by hand. A bill is raised, payroll runs, stock is received: each is an application event that maps to a transaction, and the mapping should be one described thing rather than posting code scattered through the codebase.

The failure mode here is not a wrong mapping, which shows up quickly. It is posting the same event twice, which shows up as a balance that is exactly double and no obvious reason why.

• The unique key on the source event is what makes posting idempotent. Without it, a retried webhook or a re-run job silently doubles the books
• Post inside the same database transaction as the event where you can, so the two cannot disagree
• Where posting must be asynchronous, make the queue at-least-once and rely on the unique key, rather than trying to make delivery exactly-once
• Keep the account mapping as data. It changes for business reasons, by people who should not need a deployment

post(event):
    lines = template_for(event.type)(event)   # mapping is data
    assert sum(l.amount for l in lines) == 0

    INSERT INTO journal_txn (source_type, source_id, ...)
    VALUES (event.type, event.id, ...)
    ON CONFLICT (source_type, source_id) DO NOTHING
    RETURNING id

    # No row returned means this event was already posted.
    # That is success, not an error: retries must be harmless.
    if no row: return ALREADY_POSTED

    INSERT INTO journal_entry (txn_id, account_id, amount_minor) ...

# Both inserts in one transaction, so a crash between them cannot
# leave a header with no lines.

Never floating point. This is not a stylistic preference: binary floating point cannot represent most decimal fractions exactly, and a ledger accumulates the error until a report is visibly wrong.

Store integer minor units, or a decimal type with an explicit scale. Integers are simpler to reason about and immune to the whole category of problem.

• Minor units are not always hundredths. Some currencies have none and some have thousandths, so store the currency alongside the amount rather than assuming two decimal places
• Rounding needs a rule, applied in one place. Half-even is the usual choice for money because it does not drift upward over many operations
• When splitting an amount that does not divide evenly, allocate the remainder deliberately rather than letting it vanish. A dedicated rounding account is the honest home for the difference
• Do arithmetic on the ledger's units, not on formatted display values

The one-cent difference that nobody can find is almost always a rounding rule applied in two places with different conventions.

Once movements are the source of truth, the financial statements stop being artefacts somebody assembles and become queries over the same table.

-- Trial balance: every account's position, and proof of consistency.
SELECT a.code, a.name, SUM(e.amount_minor) AS balance
FROM journal_entry e JOIN account a ON a.id = e.account_id
GROUP BY a.code, a.name;
-- SUM over the whole result must be exactly 0.

-- Profit and loss: revenue and expense, over a period.
--   WHERE a.type IN ('revenue','expense')
--     AND t.occurred_on BETWEEN :from AND :to

-- Balance sheet: asset, liability and equity, at a point in time.
--   WHERE a.type IN ('asset','liability','equity')
--     AND t.occurred_on <= :as_at

The trial balance summing to zero is a free integrity test of the entire system. Run it in monitoring, not only when somebody asks for it.

The honest downside: a balance is a sum over history, and history only grows. At a few million entries the aggregate is still fast with the right index. Beyond that it needs help.

Resist optimising this early. The usual mistake is reintroducing a stored balance column for speed, which recreates precisely the problem the ledger was built to remove, and now there are two numbers that can disagree.

Index for the access pattern

Balances are read per account and per period. An index leading with account and date covers most of it, and is usually the entire answer.

Period checkpoints

Store a computed closing balance per account per period, and calculate current position as the last checkpoint plus movements since. The checkpoint is derived and rebuildable, which is what makes it safe.

Closing a period

Once a period is closed, its entries do not change, so its totals can be trusted permanently. This is an accounting practice that happens to be an excellent caching boundary.

A cached balance is acceptable when it can be recomputed from the entries and is verified against them on a schedule. A cached balance that is the only copy is a balance column with extra steps.

Retrofitting is the common case, because the balance column was there first and the need for a ledger becomes obvious later, usually during an audit or an argument.

The sequence that works is to build the ledger alongside the existing numbers, prove it agrees with them, and only then make it the source of truth.

• Create the accounts and post new events to the ledger, while the existing balance columns continue to run the application
• Backfill history by replaying the events that produced the current balances, or by posting a single dated opening balance per account where the history is genuinely unrecoverable
• Reconcile: for every account, the ledger's sum must equal the stored balance. The differences are your bug list, and they will include real defects in the old numbers
• Switch reads to the ledger once they agree, keeping the old column updated but unused for a period
• Delete the balance column. Until this step you still have two numbers that can drift apart

The opening-balance shortcut is legitimate and has a cost: everything before that date becomes unexplainable. Take it deliberately, and record the date it applies from.

Somebody updated an entry

Yesterday's report no longer reproduces and nobody can say why. Prevent it with grants rather than convention.

Posting was not idempotent

A retried job or a redelivered webhook doubles a transaction. The unique key on the source event is the fix, and it has to exist before the first retry rather than after.

The balance column survived

Kept for convenience during migration, never removed, and now drifting. Two sources of truth is none.

Rounding remainders vanished

Splitting an amount without allocating the remainder loses fractions that accumulate into a difference nobody can locate.

Dates were conflated

Using the posting timestamp as the transaction date puts January's invoice into February's accounts, and the month-end figures cannot be reproduced later.

Deletion instead of reversal

A cancelled transaction removed rather than reversed. The books balance, the history lies, and the audit finds it.

Not every application needs a general ledger. If money enters and leaves in one shape, a payments provider holds the truth, and nobody will ever ask you to explain a figure, a simpler model is the right call.

It becomes worth it the moment more than one thing can move money, because that is when balances start disagreeing and nobody can prove which one is right. Billing plus payroll plus inventory plus refunds is already past that line.

The signal to watch for is the question. When somebody asks why a number is what it is and the answer requires opening the code, the design has already stopped being adequate.