· 4 min read · ← All posts
Go PostgreSQL FinTech Lending Accounting

Double-entry ledger invariants

A P2P lending platform processed 5K+ loans/month. The most important property of the platform was that the ledger always balanced — every debit had a matching credit, every payment was attributed correctly, every borrower’s balance was reconcilable against the source accounts. Here is the set of invariants we baked into Postgres, and the ones that stayed in application code with very heavy testing.

What “double-entry” means in code

The accounting principle: every transaction has at least two entries. A loan disbursement is a credit to the borrower’s account and a debit to the lender’s account (or pool). A repayment is the reverse.

The code shape:

CREATE TABLE journal_entries (
  entry_id UUID PRIMARY KEY,
  transaction_id UUID NOT NULL,
  account_id UUID NOT NULL,
  amount_minor INTEGER NOT NULL,   -- positive = credit, negative = debit
  currency CHAR(3) NOT NULL,
  posted_at TIMESTAMP NOT NULL DEFAULT NOW(),
  reference TEXT
);

CREATE TABLE transactions (
  transaction_id UUID PRIMARY KEY,
  type TEXT NOT NULL,              -- 'disburse' | 'repay' | 'fee' | ...
  description TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Every business action creates one transactions row and two-or-more journal_entries rows. The sum of amount_minor across the entries for one transaction must be zero.

Invariants the database enforces

We pushed three invariants into Postgres directly:

1. A transaction’s entries sum to zero

CREATE OR REPLACE FUNCTION check_transaction_balanced()
RETURNS TRIGGER AS $$
BEGIN
  IF (SELECT COALESCE(SUM(amount_minor), 0)
        FROM journal_entries
       WHERE transaction_id = NEW.transaction_id) != 0 THEN
    RAISE EXCEPTION 'transaction % does not balance', NEW.transaction_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER tr_transaction_balanced
  AFTER INSERT OR UPDATE OR DELETE ON journal_entries
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE PROCEDURE check_transaction_balanced();

DEFERRABLE INITIALLY DEFERRED is the key. The check runs at commit, not on each insert. That means you can do INSERT INTO journal_entries twice (debit, credit) inside one transaction; the check fires at COMMIT and ensures the pair balanced.

2. Entries are immutable after posting

CREATE OR REPLACE FUNCTION prevent_entry_mutation()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.entry_id IS NOT NULL THEN
    RAISE EXCEPTION 'journal entries are immutable; reverse with a new transaction';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER tr_entry_immutable
  BEFORE UPDATE OR DELETE ON journal_entries
  FOR EACH ROW EXECUTE PROCEDURE prevent_entry_mutation();

You can’t fix a journal entry. If a transaction was wrong, you create a reversing transaction. The audit trail stays intact.

3. Currency consistency within a transaction

CREATE OR REPLACE FUNCTION check_transaction_single_currency()
RETURNS TRIGGER AS $$
BEGIN
  IF (SELECT COUNT(DISTINCT currency)
        FROM journal_entries
       WHERE transaction_id = NEW.transaction_id) > 1 THEN
    RAISE EXCEPTION 'transaction % mixes currencies', NEW.transaction_id;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER tr_transaction_single_currency
  AFTER INSERT OR UPDATE ON journal_entries
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE PROCEDURE check_transaction_single_currency();

Cross-currency transactions exist (FX conversions). They get a different transaction type that uses a designated FX-suspense account; the trigger excludes them by transaction type. The rule for the common case stays simple.

What stayed in application code

Some invariants couldn’t reasonably live in the database:

Each of these had:

Reconciliation as the daily check

Every night, a reconciliation job ran:

  1. Sum all journal_entries per account.
  2. Compare against external sources of truth (bank statements, payment gateway settlement files).
  3. Flag any account where the internal balance and the external balance disagreed by more than 1 paisa.

In the first six months the job caught 14 issues. Twelve were incorrect application-side logic (a repayment fee getting double- counted, an FX conversion using the wrong rate). Two were partner issues (the payment gateway’s settlement file was wrong; the partner credited the account the next day).

The job was the operational backbone of the system. Without it, the application bugs would have accumulated invisibly.

What I’d carry forward

Three patterns that earned their keep:

  1. DEFERRABLE constraints for multi-row invariants. The “transaction must balance” rule is impossible without deferred checks; with them, it’s three lines of SQL.

  2. Immutable journal entries. The audit trail stays intact; reversals are first-class; debugging six months later is straightforward.

  3. Nightly reconciliation against external truth. The internal database can be perfectly self-consistent and still wrong. The external check is the only thing that catches integration bugs.

The P2P platform never lost money. That’s the simplest summary and the hardest property. The patterns above are what bought it.

What to avoid

Two anti-patterns we discovered the slow way:

  1. A “fix it in the app” mindset for ledger bugs. Every ledger bug should produce a reversing transaction and a fix to the application code that caused the bug. The temptation to “just update the row” exists because Postgres allows it; the immutable-entries trigger removes the temptation.

  2. Cross-currency in a single transaction. Even when it “seems convenient” — an FX conversion that pays one account in USD and another in INR — the audit story becomes hard. Use a designated FX-suspense account so the transaction is single-currency on both sides.

Both came from the same instinct (do the small expedient thing now), and both cost us in different ways.

← Back to all posts