A double-entry ledger in Postgres, the boring way that works

Every fintech I have worked at started with a balance column on the users table. And every one of them later spent months moving away from it. If you are building anything where money moves between parties, wallets, marketplaces, lending, start with a ledger. It is less work than it sounds and Postgres gives you almost everything you need.

This is the design we run in production now. Nothing fancy. Boring is the goal.

The one rule

Double-entry means every movement of money is recorded as at least two postings that sum to zero. Money leaves one account and arrives in another. If a set of postings does not sum to zero, it is not allowed to exist. That single rule catches a surprising number of bugs, because most money bugs are "we added it here but forgot to remove it there".

Tables

create table accounts (
  id        bigint generated always as identity primary key,
  code      text not null unique,          -- e.g. 'user:4821:wallet'
  type      text not null check (type in ('asset','liability','equity','income','expense')),
  currency  char(3) not null
);

create table journal_entries (
  id           bigint generated always as identity primary key,
  external_ref text unique,                -- e.g. gateway txn id
  description  text not null,
  created_at   timestamptz not null default now()
);

create table postings (
  id         bigint generated always as identity primary key,
  entry_id   bigint not null references journal_entries(id),
  account_id bigint not null references accounts(id),
  amount     bigint not null,              -- minor units, debit positive, credit negative
  currency   char(3) not null,
  check (amount <> 0)
);

create index on postings (account_id, entry_id);

Few decisions in there that are worth explaining.

Amounts are bigint in minor units. Paise, cents, fils. Never float. Numeric also works, but bigint is faster, smaller and it forces you to think about the currency exponent explicitly. Keep a small table of exponents: INR and USD are 2, JPY is 0, KWD, BHD and JOD are 3. The day you add a 3 decimal currency and someone has hardcoded "divide by 100" in the UI is the day you will remember this.

One signed column, not separate debit and credit columns. Debit positive, credit negative. Then "does this entry balance" is just sum(amount) = 0, and the balance of any account is sum(amount). For display you flip the sign for accounts whose normal balance is credit (liabilities, income, equity). A user's wallet is a liability for you, because you owe them that money, so their balance shows positive when the account sum is negative. Confusing for the first week. Then it clicks.

external_ref is unique. When a gateway webhook comes twice, the second insert into journal_entries fails and you know it is a duplicate. The ledger becomes your idempotency layer for free.

Making Postgres enforce the rule

You can check the balance in application code, and you should. But application code has bugs, and one day someone will write a script that inserts postings directly. So we enforce it in the database with a deferred constraint trigger. Deferred means it runs at commit, after all postings of the entry are inserted, not after each row.

create function check_entry_balanced() returns trigger
language plpgsql as $$
begin
  if exists (
    select 1 from postings
    where entry_id = new.entry_id
    group by currency
    having sum(amount) <> 0
  ) then
    raise exception 'journal entry % does not balance', new.entry_id;
  end if;
  return null;
end $$;

create constraint trigger postings_balanced
  after insert on postings
  deferrable initially deferred
  for each row execute function check_entry_balanced();

It runs once per posting row, so an entry with 4 postings checks 4 times. At our volume (low millions of postings a day) this was not measurable. If it is for you, check once per statement with a statement level trigger and transition tables instead.

Also note the group by currency. A cross currency entry needs an FX account in the middle so that each currency balances on its own. Never let an entry balance across currencies, that is how you lose money in rounding.

Never update, never delete

Postings are append only. If something was wrong, you post a reversing entry and then the correct one. We revoke update and delete from the application role, so even a bad migration from the app cannot rewrite history:

revoke update, delete on postings, journal_entries from app_user;

Auditors love this. Support also loves it, because "why is my balance X" is answered by a list of entries instead of a shrug.

Balances without summing forever

select sum(amount) from postings where account_id = $1 is fine until an account has a few hundred thousand postings. Then you want a cached balance. We keep an account_balances table and update it in the same transaction as the postings. A withdrawal of 500 rupees from wallet 4821 looks like this:

begin;

-- lock the wallet row, then check available balance in the app
select balance from account_balances where account_id = 4821 for update;

with e as (
  insert into journal_entries (external_ref, description)
  values ('wd_9f2c', 'wallet withdrawal')
  returning id
)
insert into postings (entry_id, account_id, amount, currency)
select e.id, v.account_id, v.amount, 'INR'
from e, (values (4821, 50000), (17, -50000)) as v(account_id, amount);
-- 4821 = user wallet (liability) debited, 17 = bank account (asset) credited

update account_balances set balance = balance + 50000 where account_id = 4821;
update account_balances set balance = balance - 50000 where account_id = 17;

commit;

The for update on the wallet row is what stops two concurrent withdrawals from both seeing enough money. Without it, two requests read 500 rupees, both withdraw 400, and you have a wallet at minus 300 and one very happy customer. Ask me how I know.

The cached balance can always be rebuilt from postings, so if you ever doubt it, recompute and compare. We run that comparison every night for every account touched that day. It has caught one real bug in two years. One is enough to justify it.

Trial balance

The whole ledger must sum to zero per currency. This query is our smoke test after every deploy that touches money code:

select currency, sum(amount) from postings group by currency;

If it is not zero, stop everything. With the trigger above it should be impossible, which is exactly why it is worth checking.

Things I would do differently

• Name accounts with structured codes from day one (user:4821:wallet, platform:fees:INR). We started with free text names and regret it.
• Store the effective date separately from created_at. Back-dated corrections happen, accounting cares about the effective date and engineers care about when the row was written.
• Add a metadata jsonb on journal_entries for gateway ids, order ids etc. You will query by them in every single investigation.

That's it really. Three tables, one trigger, one lock. It is boring, and two years later it still balances, which is the only compliment a ledger needs.