When the balance column lies: a double-entry ledger in PostgreSQL and Django
A single balance column can't tell you where money came from or where it went. Accounts, entries and postings, debit = credit enforced by a PostgreSQL trigger, reversals and snapshots: an honest ledger in Django.
A support ticket arrives on a Monday morning: "Yesterday my wallet showed 350 000 so'm, today it shows 150 000. I didn't buy anything." You open the database. The wallets table has one row for this user, and the balance column says 150 000. That's it. Was it a purchase? A refund that got reverted? A bug in last week's deploy that subtracted twice? The number is there, but the story behind it is gone.
Now the opposite case, which is worse because nobody complains: a retry in a payment callback credits a merchant twice. The merchant is happy, the user is happy, and your company has quietly created 2 million so'm out of thin air. Nothing in the system knows that money must come from somewhere.
Both problems have the same root: the balance is stored as the only source of truth. Accountants solved this centuries ago with double-entry bookkeeping. In this post we'll build a small but honest double-entry ledger on PostgreSQL and Django: accounts, journal entries, postings, the debit = credit invariant enforced by the database, balances with snapshots, and corrections without ever editing history.
Why a balance column is not enough
Here is the version most of us wrote at some point:
# wallets/services.py — the "simple" version
from django.db import transaction
from django.db.models import F
from .models import Wallet
@transaction.atomic
def transfer(sender_id: int, receiver_id: int, amount: int) -> None:
Wallet.objects.filter(user_id=sender_id).update(balance=F("balance") - amount)
Wallet.objects.filter(user_id=receiver_id).update(balance=F("balance") + amount)
It is atomic, it uses F() so concurrent updates don't overwrite each other. And still:
- No history. After the
UPDATE, the previous value is gone. If you also write atransactionslog table, it lives next to the balance, not underneath it, and sooner or later the two drift apart: someone fixes a balance by hand, a migration forgets the log, a code path skips it. - No invariant. Nothing checks that what left one wallet arrived in another. If the second line credits
amount + feebecause of a bug, the database is perfectly happy. - Corrections destroy evidence. The natural way to fix a wrong balance is another
UPDATE. Now the bug and the fix are both invisible. - Money has nowhere to come from. A card top-up only increases one number. Where did that money come from? From the payment provider, who now owes it to us. A single balance column cannot express that.
A balance is a derived value. The source of truth is the list of movements, and every movement must take money from somewhere and put it somewhere.
The model: accounts, journal entries, postings
Double-entry is just three concepts.
Account — a bucket that holds value: a user's wallet, a merchant's wallet, the money the payment provider owes us, our fee revenue. Each account has a type:
| Type | Example in a wallet platform | Normal side |
|---|---|---|
| Asset | Money held at the bank / owed by the PSP | Debit |
| Liability | User and merchant wallets (we owe them) | Credit |
| Revenue | Commission fees | Credit |
| Expense | Cashback, card processing costs | Debit |
| Equity | Owner's capital | Credit |
The surprising part for developers: a user's wallet is a liability for the platform. The money is ours to hold but theirs to own, so it is a debt.
Journal entry — one business event: "top-up", "payment for order #812", "refund". It has a description, a timestamp and an idempotency key.
Posting (a line of the entry) — the amount that hits one account in one entry. An entry has two or more postings, and the rule is: the sum of debits equals the sum of credits in every entry.
In code it's convenient to store a single signed integer: positive is debit, negative is credit. Then the rule becomes "the postings of an entry sum to zero", which is trivial to check.
A user tops up 200 000 so'm by card:
| Account | Type | Amount |
|---|---|---|
| asset:psp-clearing | Asset | +200 000 |
| wallet:user:42 | Liability | −200 000 |
The PSP owes us more (asset grows by debit), we owe the user more (liability grows by credit). Then the user pays a merchant 150 000 so'm with a 1% fee:
| Account | Type | Amount |
|---|---|---|
| wallet:user:42 | Liability | +150 000 |
| wallet:merchant:7 | Liability | −148 500 |
| revenue:fees | Revenue | −1 500 |
Debits 150 000, credits 148 500 + 1 500. Sum is zero. The user's displayed balance is the negated sum of their postings: 200 000 − 150 000 = 50 000. Every so'm in the system can now be traced to the entry that moved it.
PostgreSQL schema and Django models
Amounts are integers in tiyin (see the earlier post about float and Decimal). Accounts get a cached balance; the source of truth is still the postings.
# ledger/models.py
from django.db import models
from django.db.models import Q
class Account(models.Model):
class Kind(models.TextChoices):
ASSET = "asset", "Asset"
LIABILITY = "liability", "Liability"
EQUITY = "equity", "Equity"
REVENUE = "revenue", "Revenue"
EXPENSE = "expense", "Expense"
code = models.CharField(max_length=64, unique=True) # "wallet:user:42", "revenue:fees"
kind = models.CharField(max_length=16, choices=Kind.choices)
currency = models.CharField(max_length=3, default="UZS")
allow_negative = models.BooleanField(default=False)
# Cached sum of all postings, in tiyin, debit-positive.
balance = models.BigIntegerField(default=0)
@property
def is_debit_normal(self) -> bool:
return self.kind in (self.Kind.ASSET, self.Kind.EXPENSE)
@property
def display_balance(self) -> int:
return self.balance if self.is_debit_normal else -self.balance
class JournalEntry(models.Model):
description = models.CharField(max_length=255)
idempotency_key = models.CharField(max_length=64, unique=True)
reverses = models.OneToOneField(
"self",
null=True,
blank=True,
on_delete=models.PROTECT,
related_name="reversed_by",
)
created_at = models.DateTimeField(auto_now_add=True)
class Posting(models.Model):
entry = models.ForeignKey(JournalEntry, on_delete=models.PROTECT, related_name="postings")
account = models.ForeignKey(Account, on_delete=models.PROTECT, related_name="postings")
amount = models.BigIntegerField() # tiyin: + debit, - credit
created_at = models.DateTimeField(auto_now_add=True)
class Meta:
constraints = [
models.CheckConstraint(condition=~Q(amount=0), name="posting_amount_nonzero"),
]
indexes = [
models.Index(
fields=["account", "created_at"],
include=["amount"],
name="posting_acc_time_idx",
),
]
A few deliberate choices: on_delete=PROTECT everywhere, because a ledger never cascades deletes; reverses is a OneToOneField, so the database itself refuses a second reversal of the same entry; the index (account, created_at) INCLUDE (amount) lets PostgreSQL compute "balance at moment X" with an index-only scan when the visibility map is up to date. Note that CheckConstraint takes condition= since Django 5.1; the old check= argument is gone in Django 6.0.
The database enforces debit = credit
Checking the sum in Python is good, but the guarantee should not depend on every code path going through your service. A row-level CHECK can't see other rows, so we use a constraint trigger that is deferred to commit time:
CREATE FUNCTION ledger_check_entry_balanced() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
IF EXISTS (
SELECT 1
FROM ledger_posting p
JOIN ledger_account a ON a.id = p.account_id
WHERE p.entry_id = NEW.entry_id
GROUP BY a.currency
HAVING SUM(p.amount) <> 0
) THEN
RAISE EXCEPTION 'journal entry % is not balanced', NEW.entry_id
USING ERRCODE = 'check_violation';
END IF;
RETURN NULL;
END;
$$;
CREATE CONSTRAINT TRIGGER ledger_posting_balanced
AFTER INSERT ON ledger_posting
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION ledger_check_entry_balanced();
Why DEFERRABLE INITIALLY DEFERRED? Postings are inserted one by one (or in one statement); after the first one the entry is unbalanced by definition. A deferred constraint trigger fires at COMMIT, when all postings of the entry are in place. The ERRCODE = 'check_violation' makes psycopg raise CheckViolation, which Django surfaces as IntegrityError; a plain RAISE EXCEPTION would arrive as a far less telling ProgrammingError. Grouping by currency means an entry must balance in each currency separately: you cannot "balance" 100 USD against 100 UZS.
Immutable records
Postings and journal entries are append-only. Not "by convention", but enforced:
CREATE FUNCTION ledger_forbid_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
RAISE EXCEPTION '% is append-only: % is not allowed', TG_TABLE_NAME, TG_OP;
END;
$$;
CREATE TRIGGER ledger_posting_append_only
BEFORE UPDATE OR DELETE ON ledger_posting
FOR EACH ROW EXECUTE FUNCTION ledger_forbid_change();
CREATE TRIGGER ledger_posting_no_truncate
BEFORE TRUNCATE ON ledger_posting
FOR EACH STATEMENT EXECUTE FUNCTION ledger_forbid_change();
CREATE TRIGGER ledger_entry_append_only
BEFORE UPDATE OR DELETE ON ledger_journalentry
FOR EACH ROW EXECUTE FUNCTION ledger_forbid_change();
Row-level triggers don't fire on TRUNCATE, which is why there is a separate statement-level trigger for it. On top of that, if your application connects as a role that does not own the tables, REVOKE UPDATE, DELETE, TRUNCATE ON ledger_posting, ledger_journalentry FROM app_role; closes the door at the privilege level too. Ship the SQL with a regular migration:
# ledger/migrations/0002_ledger_triggers.py
from django.db import migrations
FORWARD = """
-- paste both SQL blocks from above here:
-- ledger_check_entry_balanced + ledger_posting_balanced,
-- ledger_forbid_change + the three append-only triggers
"""
REVERSE = """
DROP TRIGGER IF EXISTS ledger_posting_balanced ON ledger_posting;
DROP TRIGGER IF EXISTS ledger_posting_append_only ON ledger_posting;
DROP TRIGGER IF EXISTS ledger_posting_no_truncate ON ledger_posting;
DROP TRIGGER IF EXISTS ledger_entry_append_only ON ledger_journalentry;
DROP FUNCTION IF EXISTS ledger_check_entry_balanced();
DROP FUNCTION IF EXISTS ledger_forbid_change();
"""
class Migration(migrations.Migration):
dependencies = [("ledger", "0001_initial")]
operations = [migrations.RunSQL(FORWARD, reverse_sql=REVERSE)]
Posting an entry from Django
The service function is the only place that writes to the ledger. It validates, locks the affected accounts, checks for overdraft, inserts the postings and updates cached balances, all inside one transaction:
# ledger/services.py
from collections import defaultdict
from dataclasses import dataclass
from django.db import transaction
from django.db.models import F
from .models import Account, JournalEntry, Posting
class LedgerError(Exception):
pass
class UnbalancedEntry(LedgerError):
pass
class InsufficientFunds(LedgerError):
pass
@dataclass(frozen=True)
class Line:
account_id: int
amount: int # tiyin: + debit, - credit
@transaction.atomic
def post_entry(
*,
description: str,
idempotency_key: str,
lines: list[Line],
reverses: JournalEntry | None = None,
) -> JournalEntry:
if any(line.amount == 0 for line in lines):
raise UnbalancedEntry("zero-amount line")
existing = JournalEntry.objects.filter(idempotency_key=idempotency_key).first()
if existing is not None:
return existing
deltas: dict[int, int] = defaultdict(int)
for line in lines:
deltas[line.account_id] += line.amount
# Stable lock order: A->B and B->A transfers cannot deadlock each other.
accounts = {
acc.id: acc
for acc in Account.objects.select_for_update()
.filter(id__in=list(deltas))
.order_by("id")
}
if len(accounts) != len(deltas):
raise LedgerError("unknown account")
per_currency: dict[str, int] = defaultdict(int)
for acc_id, delta in deltas.items():
per_currency[accounts[acc_id].currency] += delta
if any(total != 0 for total in per_currency.values()):
raise UnbalancedEntry(f"debits != credits: {dict(per_currency)}")
for acc_id, delta in deltas.items():
acc = accounts[acc_id]
new_balance = acc.balance + delta
shown = new_balance if acc.is_debit_normal else -new_balance
if shown < 0 and not acc.allow_negative:
raise InsufficientFunds(acc.code)
entry = JournalEntry.objects.create(
description=description,
idempotency_key=idempotency_key,
reverses=reverses,
)
Posting.objects.bulk_create(
[Posting(entry=entry, account_id=line.account_id, amount=line.amount) for line in lines]
)
for acc_id, delta in deltas.items():
Account.objects.filter(id=acc_id).update(balance=F("balance") + delta)
return entry
What is happening, step by step:
- Idempotency. If an entry with this key exists, we return it instead of posting again. Two concurrent requests with the same key will both pass the check, but the
uniqueconstraint lets only one commit; the other getsIntegrityErrorand can re-read the winner (the idempotency-keys post covers this in detail). - Locking in a stable order.
select_for_update()withorder_by("id")locks account rows in ascending id order. Two opposite transfers lock in the same order, so they wait for each other instead of deadlocking. - Balance per currency. The Python check gives a readable error early; the trigger is the safety net.
- Overdraft. With rows locked, the cached balance is current, and nobody can spend the same money concurrently.
- Writes. One entry, its postings via
bulk_create, and a balance update per account.
Using it for the merchant payment above:
# payments/services.py
from ledger.models import Account, JournalEntry
from ledger.services import Line, post_entry
def pay_merchant(
payer: Account, merchant: Account, fees: Account, amount: int, order_id: int
) -> JournalEntry:
fee = amount // 100 # 1%, rounded down to a whole tiyin
lines = [
Line(payer.id, amount), # debit: we owe the payer less
Line(merchant.id, -(amount - fee)), # credit: we owe the merchant more
Line(fees.id, -fee), # credit: our revenue
]
return post_entry(
description=f"order #{order_id}",
idempotency_key=f"order:{order_id}:pay",
lines=[line for line in lines if line.amount != 0],
)
Notice the fee rounding: the merchant gets amount - fee, not amount * 0.99 rounded separately. Compute one side and derive the other from it, so the lines always sum to zero exactly.
Corrections: reverse, never edit
Wrong amount? Wrong merchant? You don't touch the old entry. You post a reversing entry with the same lines negated, then, if needed, a new correct entry:
# ledger/services.py (continued)
def reverse_entry(entry: JournalEntry, *, reason: str) -> JournalEntry:
return post_entry(
description=f"reversal of #{entry.pk}: {reason}",
idempotency_key=f"reversal:{entry.pk}",
lines=[Line(p.account_id, -p.amount) for p in entry.postings.all()],
reverses=entry,
)
The history now reads exactly like what happened: original, reversal, correct entry. An auditor (or you, at 3 a.m.) can reconstruct any balance at any moment. The OneToOneField plus the idempotency key make a double reversal impossible.
Balances and snapshots
There are three ways to answer "what's the balance", and a real system uses all three:
- Cached
balanceon the account, updated in the same transaction as the postings. This is what you show in the app and use for overdraft checks. - Sum of postings — the truth. For a point in time:
# ledger/queries.py
from datetime import datetime
from django.db.models import Sum
from .models import Account, Posting
def balance_at(account: Account, moment: datetime) -> int:
"""Debit-positive balance from all postings created before `moment`."""
return Posting.objects.filter(account=account, created_at__lt=moment).aggregate(
total=Sum("amount", default=0)
)["total"]
- Snapshots. Summing millions of postings for a statement is slow, so close each day into a table and sum only the postings after the last snapshot:
CREATE TABLE ledger_daily_balance (
account_id bigint NOT NULL REFERENCES ledger_account (id),
day date NOT NULL,
balance bigint NOT NULL, -- closing balance, debit-positive
PRIMARY KEY (account_id, day)
);
-- Closing balance of 2026-11-10 (Tashkent time) = previous day + that day's postings.
INSERT INTO ledger_daily_balance (account_id, day, balance)
SELECT a.id,
DATE '2026-11-10',
COALESCE(prev.balance, 0) + COALESCE(SUM(p.amount), 0)
FROM ledger_account a
LEFT JOIN ledger_daily_balance prev
ON prev.account_id = a.id AND prev.day = DATE '2026-11-09'
LEFT JOIN ledger_posting p
ON p.account_id = a.id
AND p.created_at >= TIMESTAMPTZ '2026-11-10 00:00:00+05'
AND p.created_at < TIMESTAMPTZ '2026-11-11 00:00:00+05'
GROUP BY a.id, prev.balance
ON CONFLICT (account_id, day) DO UPDATE SET balance = EXCLUDED.balance;
The balance at any moment is then the closing balance of the previous day plus the postings since midnight. Before trusting any of this, measure: run EXPLAIN (ANALYZE, BUFFERS) on the point-in-time query for your busiest account with and without the snapshot table.
Finally, reconciliation. Run these regularly (a nightly Celery task is fine) and alert on any rows returned:
-- 1. Every account: cached balance must equal the sum of its postings.
SELECT a.id, a.code, a.balance, COALESCE(SUM(p.amount), 0) AS computed
FROM ledger_account a
LEFT JOIN ledger_posting p ON p.account_id = a.id
GROUP BY a.id
HAVING a.balance <> COALESCE(SUM(p.amount), 0);
-- 2. The whole ledger, per currency, must sum to zero.
SELECT a.currency, SUM(p.amount) AS total
FROM ledger_posting p
JOIN ledger_account a ON a.id = p.account_id
GROUP BY a.currency
HAVING SUM(p.amount) <> 0;
Pitfalls in production
- Deferred triggers and tests. Django's
TestCasewraps each test in a transaction that is never committed, so the deferred check does not fire where you expect. Force it withSET CONSTRAINTS ALL IMMEDIATE, which checks pending events retroactively:
# ledger/tests.py
from django.db import IntegrityError, connection, transaction
from django.test import TestCase
from ledger.models import Account, JournalEntry, Posting
class LedgerInvariantTests(TestCase):
def test_unbalanced_entry_is_rejected_by_db(self):
a = Account.objects.create(code="asset:psp", kind="asset")
b = Account.objects.create(code="wallet:user:1", kind="liability")
with self.assertRaises(IntegrityError):
with transaction.atomic():
entry = JournalEntry.objects.create(description="bad", idempotency_key="t1")
Posting.objects.create(entry=entry, account=a, amount=100)
Posting.objects.create(entry=entry, account=b, amount=-90)
# TestCase never commits, so force the deferred check right here.
with connection.cursor() as cur:
cur.execute("SET CONSTRAINTS ALL IMMEDIATE")
- Outer transactions. If
post_entryruns inside a biggeratomic()block, the trigger fires at the outer commit, and theIntegrityErrorappears there, far from the cause. Keep ledger writes in short transactions. - TRUNCATE trigger vs. test flush.
TransactionTestCaseandmanage.py flushempty tables withTRUNCATE, so the no-truncate trigger makes them fail with "ledger_posting is append-only". Either keep ledger tests onTestCase, or drop that one trigger and rely onREVOKE TRUNCATEfor the production role. - Hot accounts. Every payment credits
revenue:fees. If you lock it withselect_for_update(), all payments serialize on one row. Accounts that may go negative or don't need an overdraft check (revenue, PSP clearing) don't need the lock or a cached balance: insert their postings and compute their balance from postings and snapshots. - Timestamps are not commit order.
created_atis set before commit (in Python byauto_now_add, and PostgreSQL'snow()is the transaction start time). A transaction that starts at 23:59:59 and commits at 00:00:01 lands in "yesterday" after yesterday's snapshot may already be computed. Run the snapshot job with a safety lag and keep transactions short. For the same reason, never use "last posting id" as a snapshot watermark: sequence values are assigned at insert, not at commit. - Reversals can overdraft. Reversing a top-up after the user has spent the money fails with
InsufficientFunds. That's correct behavior, but you need a business answer: a receivable account for user debt, or a manual process. - The admin panel. A default
ModelAdminforPostingoffers edit and delete buttons. Register it read-only (has_change_permissionandhas_delete_permissionreturningFalse), even though the trigger would refuse anyway. - One account, one currency. Currency conversion is two entries through an FX account, not one entry with mixed currencies.
Checklist
- Money in integer minor units (
BigIntegerField, tiyin), never float. - Every movement is a journal entry with ≥ 2 postings summing to zero per currency.
- A deferred constraint trigger enforces the sum at commit.
- Postings and entries are append-only: triggers plus revoked privileges.
- Errors are fixed with reversing entries, never
UPDATE. - Accounts locked with
select_for_update()in ascending id order. - Every entry has a unique idempotency key.
- Cached balances reconciled against postings on a schedule; alerts on mismatch.
- Snapshots for closed periods only, computed with a lag.
Takeaway
A balance column tells you a number; a double-entry ledger tells you why the number is what it is. The design is small: accounts, entries, postings, and one invariant that every entry sums to zero, enforced by PostgreSQL, not by good intentions. Add immutability and reversals, and every so'm in the system has a traceable origin.
// comments
Loading comments...