All notes
Fintech

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 a transactions log 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 + fee because 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:

  1. 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 unique constraint lets only one commit; the other gets IntegrityError and can re-read the winner (the idempotency-keys post covers this in detail).
  2. Locking in a stable order. select_for_update() with order_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.
  3. Balance per currency. The Python check gives a readable error early; the trigger is the safety net.
  4. Overdraft. With rows locked, the cached balance is current, and nobody can spend the same money concurrently.
  5. 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:

  1. Cached balance on the account, updated in the same transaction as the postings. This is what you show in the app and use for overdraft checks.
  2. 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"]
  1. 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 TestCase wraps each test in a transaction that is never committed, so the deferred check does not fire where you expect. Force it with SET 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_entry runs inside a bigger atomic() block, the trigger fires at the outer commit, and the IntegrityError appears there, far from the cause. Keep ledger writes in short transactions.
  • TRUNCATE trigger vs. test flush. TransactionTestCase and manage.py flush empty tables with TRUNCATE, so the no-truncate trigger makes them fail with "ledger_posting is append-only". Either keep ledger tests on TestCase, or drop that one trigger and rely on REVOKE TRUNCATE for the production role.
  • Hot accounts. Every payment credits revenue:fees. If you lock it with select_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_at is set before commit (in Python by auto_now_add, and PostgreSQL's now() 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 ModelAdmin for Posting offers edit and delete buttons. Register it read-only (has_change_permission and has_delete_permission returning False), 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...

    // next step

    Let's build something together.

    Message me on Telegram
    aslbek@asldev: ~
    ❯