Designing a trust accounting ledger that can’t double-debit

A trust accounting ledger that can’t double-debit rests on four decisions. Every movement of money is a double-entry journal entry whose lines sum to zero. The journal is append-only: nothing is updated or deleted, and mistakes are fixed with reversing entries. Every posting carries an idempotency key chosen by the caller and enforced by a unique constraint, written in the same transaction as the journal lines. And every withdrawal takes a lock on the client’s ledger before it checks the balance. Reconciliation against the bank then proves, every period, that those rules held.

This article walks through each of those decisions as I’d design them today, with illustrative TypeScript and PostgreSQL written for this page. It comes from leading the architecture of a multi-tenant trust accounting platform for Australian property agencies, where getting this wrong is not a bug report but a compliance problem.

Why trust money changes the design

Most ledgers record the business’s own money. A trust ledger records money the business holds for someone else: rent collected for a landlord, a bond held for a tenant, a deposit held until settlement. The agency is a custodian, and the rules reflect that.

In Australia, real estate trust accounts are regulated state by state. The details vary: what records must be kept, how often the trust account has to be reconciled, what the annual audit checks. Your compliance adviser and auditor own those details. But three expectations are common enough to design around from day one:

• Every cent is traceable. For any balance, you can show the receipts, transfers and payments that produced it.
• No client ledger goes below zero. Paying a landlord more than you hold for that landlord is a breach, even if the trust bank account as a whole has enough money in it. Someone else’s money just paid for it.
• The books reconcile with the bank. The bank statement, the trust cash book and the sum of the client ledgers have to agree, and that check is done regularly and signed off.

A general-purpose “transactions” table with a balance column fails all three under load. Retries create duplicates, concurrent payments overdraw ledgers, and edits destroy the trail. So the design starts from the ledger, not from the screens.

Double-entry for trust money

Double-entry means every journal entry has at least two lines, and the lines sum to zero. I store amounts as signed integers in cents: debits positive, credits negative. No floating point, ever.

In a trust ledger the accounts fall into two groups. On one side is the trust bank account, an asset: the money actually sitting at the bank. On the other side are the liabilities: one ledger per client (owner, tenant, purchaser) recording how much of that money belongs to them, plus a few control accounts such as fees earned by the agency but not yet transferred out.

When a tenant pays $1,200 of rent for a landlord, the entry looks like this:

Account	Debit	Credit
Trust bank account	1,200.00
Owner ledger: 14 Example St		1,200.00

When the agency then takes its management fee and pays the owner, there are two more entries: one moving the fee from the owner’s ledger to a fees-payable account, and one paying the owner out of the trust bank account. Each entry balances on its own.

The useful property is that the books balance by construction. If every entry sums to zero, the trust bank account always equals the sum of all the liability accounts. When reconciliation later shows a difference, it is a difference with the bank, not a hole inside the ledger.

The ledger is an append-only journal

The core schema is small. Accounts, journal entries, and journal lines:

create table account (
  id         bigint generated always as identity primary key,
  kind       text not null check (kind in ('trust_bank', 'client', 'control')),
  name       text not null
);

create table journal_entry (
  id              bigint generated always as identity primary key,
  idempotency_key text not null unique,
  request_hash    text not null,
  entry_date      date not null,
  reverses_id     bigint unique references journal_entry (id),
  memo            text not null,
  created_at      timestamptz not null default now()
);

create table journal_line (
  entry_id   bigint not null references journal_entry (id),
  account_id bigint not null references account (id),
  amount     bigint not null check (amount <> 0),
  primary key (entry_id, account_id)
);

Three things in there do most of the work. The idempotency key is unique. An entry can be reversed at most once, because reverses_id is unique too. And entry_date is a plain date, the accounting date, separate from the timestamp of when the row was written. More on that under periods.

Append-only is a rule the database should enforce, not a convention. The application’s database role gets insert and select on the journal tables, and nothing else:

revoke update, delete, truncate on journal_entry, journal_line from app_user;
grant select, insert on journal_entry, journal_line to app_user;

The balance rule belongs in the database as well. The application checks it before posting, but a deferred constraint trigger checks it again at commit, so a bug in some other code path can’t write an unbalanced entry:

create function assert_entry_balances() returns trigger
language plpgsql as $$
begin
  if (select coalesce(sum(amount), 0) from journal_line
      where entry_id = new.entry_id) <> 0 then
    raise exception 'journal entry % does not balance', new.entry_id;
  end if;
  return null;
end $$;

create constraint trigger entry_balances
  after insert on journal_line
  deferrable initially deferred
  for each row execute function assert_entry_balances();

Balances are derived from the lines. For speed I usually keep an account_balance row per account as well, updated in the same transaction as the lines, and treat it as a cache that a nightly job checks against the sum of the lines. If they ever disagree, the lines win and someone gets paged.

Idempotency keys and unique constraints

Retries are the normal case, not the edge case. A browser resubmits a form. A mobile client times out and tries again. A queue delivers the same message twice, because at-least-once delivery is what most queues promise. If each arrival posts to the ledger, the same money moves twice.

The fix is to make every posting idempotent: sending the same request any number of times has the effect of sending it once. That takes three things:

1. A key chosen by the caller. The server can’t tell a retry from a new request; only the caller knows its intent. A form gets a key when it loads. A bank feed import uses the bank’s own transaction identifier. A scheduled disbursement uses the batch and line it belongs to.
2. A unique constraint in the database. A check in application code (“does this key exist?”) races with itself under concurrency. A unique index doesn’t.
3. One transaction. The key, the entry and its lines are written together. Either all of them exist or none do.

I also store a hash of the request body against the key. A repeat with the same key and the same body is a retry, and gets the original result back. The same key with a different body is a bug in the caller, and gets a hard error rather than a silent success.

type Line = { accountId: bigint; amount: bigint }; // cents, debit > 0

type PostRequest = {
  key: string;
  entryDate: string; // YYYY-MM-DD, the accounting date
  memo: string;
  lines: Line[];
  reverses?: bigint; // set only by reverse()
};

export async function post(db: Db, req: PostRequest) {
  const total = req.lines.reduce((sum, l) => sum + l.amount, 0n);
  if (req.lines.length < 2 || total !== 0n) throw new LedgerError('UNBALANCED');

  const hash = hashRequest(req);

  return db.transaction(async (tx) => {
    const inserted = await tx.query(
      `insert into journal_entry
         (idempotency_key, request_hash, entry_date, memo, reverses_id)
       values ($1, $2, $3, $4, $5)
       on conflict (idempotency_key) do nothing
       returning id`,
      [req.key, hash, req.entryDate, req.memo, req.reverses ?? null],
    );

    if (inserted.rows.length === 0) {
      // Already posted: return the original, or reject a reused key.
      const prior = await tx.one(
        `select id, request_hash from journal_entry where idempotency_key = $1`,
        [req.key],
      );
      if (prior.request_hash !== hash) throw new LedgerError('KEY_REUSED');
      return { entryId: prior.id, replayed: true };
    }

    const entryId = inserted.rows[0].id;
    await assertPeriodOpen(tx, req.entryDate);
    await lockAndCheckFunds(tx, req.lines);
    await insertLines(tx, entryId, req.lines); // and updates cached balances
    return { entryId, replayed: false };
  });
}

on conflict do nothing handles the race between two identical requests arriving at once. The second insert waits for the first transaction to finish. If the first commits, the second inserts nothing and falls through to read the original. If the first rolls back, the second goes ahead as if it were first. This relies on PostgreSQL’s default read committed isolation; under repeatable read or serialisable, the second transaction gets a serialisation error instead, which the caller has to retry.

I scope keys per tenant in a multi-tenant system, either with a composite unique index or by giving each tenant its own schema. A key only has to be unique within the books it belongs to.

Retries, crashes and double-submits, case by case

It’s worth walking through each failure and checking the design actually covers it:

• Double-click. Both requests carry the key the form was given when it loaded. One posts, the other gets the same entry back.
• Timeout after commit. The server committed, but the response never reached the client. The client retries with the same key and gets the original entry back. Nothing posts twice.
• Crash before commit. The process dies halfway through. The transaction never committed, so neither the key nor any lines exist. The retry posts normally.
• Queue redelivery. The consumer derives the key from the business event, such as the bank transaction ID, not from the queue’s message ID. That also covers the case where the producer itself sent the event twice, with two different message IDs.
• Two different withdrawals at once. Idempotency doesn’t help here: these are genuinely different requests. This is the double-debit that overdraws a client ledger, and it needs a lock.

The last case is the one teams most often miss. Two payments from the same owner’s ledger each read a balance of $1,000, each decide $800 is fine, and both commit. The fix is to lock the ledger rows before checking funds, in a fixed order so two transactions can’t deadlock each other:

async function lockAndCheckFunds(tx: Tx, lines: Line[]) {
  // Paying out of a client ledger debits it (a positive amount).
  const debited = lines.filter((l) => l.amount > 0n).map((l) => l.accountId);
  if (debited.length === 0) return;

  const rows = await tx.query(
    `select b.account_id, b.balance
     from account_balance b
     join account a on a.id = b.account_id
     where b.account_id = any($1) and a.kind = 'client'
     order by b.account_id
     for update of b`,
    [debited],
  );

  for (const row of rows.rows) {
    const change = lines
      .filter((l) => l.accountId === row.account_id)
      .reduce((sum, l) => sum + l.amount, 0n);
    if (row.balance + change > 0n) throw new LedgerError('INSUFFICIENT_FUNDS');
  }
}

The sign check reads oddly at first. With debits positive, a client ledger holding money has a negative (credit) balance, and paying out moves it towards zero. Going past zero means paying out money the client doesn’t have.

One more case sits outside the database: side effects. Generating a bank payment file or calling a payment gateway can’t be rolled back with the transaction. I write those as rows in an outbox table, in the same transaction as the journal entry, and a separate worker sends them with its own idempotency. The ledger entry and the instruction to pay either both exist or neither does.

Reversals instead of edits

Mistakes happen: a receipt allocated to the wrong owner, a fee keyed in twice. The temptation is to edit the line. In a trust ledger, don’t. An edit destroys the trail an auditor relies on, and it silently changes balances that have already been reported on statements.

The correction is two new entries. The first reverses the original exactly, every line with the opposite sign, and points at it through reverses_id. The second posts what should have happened. The trail now shows the mistake, the reversal and the correction, each with who did it and when.

export async function reverse(db: Db, entryId: bigint, entryDate: string) {
  const original = await db.entryWithLines(entryId);

  return post(db, {
    key: `reversal:${entryId}`,
    entryDate,
    memo: `Reversal of entry ${entryId}`,
    lines: original.lines.map((l) => ({ ...l, amount: -l.amount })),
    reverses: entryId, // reverses_id is unique: one reversal per entry
  });
}

The key is derived from the entry being reversed, so a retried reversal is idempotent too, and the unique index on reverses_id stops two different people reversing the same entry. A reversal that would take a client ledger below zero is refused by the same funds check as any other posting. That matters: if the owner has already been paid, reversing the receipt is a conversation, not a button.

Accounting periods

Trust accounts are reconciled and reported by period, usually monthly. Once a period has been reconciled and signed off, its numbers must not move. That gives the ledger a period table and one rule: nothing can be posted with an accounting date in a closed period.

create table accounting_period (
  starts_on  date primary key,
  ends_on    date not null,
  status     text not null check (status in ('open', 'closing', 'closed'))
);

Posting takes a shared lock on the period row and checks it’s open. Closing takes an exclusive lock, which waits for in-flight postings to finish and blocks new ones while the close runs. A late correction to a closed month is posted in the current open period, with a memo pointing at the entry it corrects.

The accounting date deserves care. Australia spans several time zones, and some of them observe daylight saving. A receipt at 11:30 pm in Perth on the last day of the month is already the next month in Sydney. I store entry_date as an explicit date, set from the agency’s own time zone when the entry is created, and never derive it from a UTC timestamp in a report query.

Reconciliation hooks

Reconciliation is how you prove the ledger matches reality. In Australian trust accounting it is usually done as a three-way reconciliation: the bank statement balance, adjusted for items not yet presented, equals the trust cash book balance, which equals the sum of the client ledger balances. If the ledger is double-entry and append-only, the last two agree by construction, and a query can prove it. The real work is matching the ledger to the bank.

That is much easier if the ledger is built with reconciliation in mind:

• Keep the bank’s identifiers. Every line that touches the trust bank account stores the bank reference it should match: the feed transaction ID for receipts, the payment file and line for disbursements.
• Store bank transactions raw. Bank feed and statement lines go into their own table, untouched, with their own unique constraint so a re-imported statement doesn’t create duplicates.
• Match, don’t merge. A match table links bank transactions to journal lines. Automatic matching clears the routine cases; unmatched items on either side are the exceptions a person reviews.
• Snapshot the result. A period’s reconciliation is saved as a record, with the balances and the list of unpresented items at the time, and who signed it off.

The ledger-side check is a query you can run at any time:

select
  sum(l.amount) filter (where a.kind = 'trust_bank') as cash_book,
  -sum(l.amount) filter (where a.kind in ('client', 'control')) as held_for_others
from journal_line l
join account a on a.id = l.account_id
join journal_entry e on e.id = l.entry_id
where e.entry_date <= $1;

If those two numbers ever differ, something has written to the ledger outside the rules above, and that is worth stopping for.

Testing a ledger that can’t double-debit

Ledger tests that mock the database prove very little. The guarantees in this design live in PostgreSQL: unique indexes, row locks, deferred triggers, transaction boundaries. So the important tests run against a real PostgreSQL instance, usually in a container in CI.

The tests I write first:

• Invariants after every test. Every entry balances, the sum of all lines is zero, and no client ledger has a debit balance. I run these checks automatically at the end of each test, so any test that breaks them fails.
• Parallel duplicates. Fire the same request many times concurrently and assert that exactly one entry exists.
• Parallel withdrawals. Fire different withdrawals against one ledger, together worth more than its balance, and assert the balance never goes below zero.
• Crashes. Inject a failure between inserting the entry and its lines, and assert nothing was written.
• Replays. Run a day of bank feed imports twice and assert the ledger is unchanged by the second run.
• Period close. Assert postings into a closed period fail, and that a close waits for in-flight postings.

The duplicate test is short and catches a surprising number of mistakes:

test('the same request posts once, however often it is sent', async () => {
  const req = receipt({ key: 'receipt:bank-txn-123', cents: 120_000n });

  const results = await Promise.allSettled(
    Array.from({ length: 20 }, () => post(db, req)),
  );

  const ok = results.filter((r) => r.status === 'fulfilled');
  expect(ok).toHaveLength(20);
  expect(await countEntries(db, 'receipt:bank-txn-123')).toBe(1);
  await assertLedgerInvariants(db);
});

Property-based tests are a good fit on top of these: generate random sequences of receipts, transfers, payments and reversals, apply them, and check the invariants still hold. They find the orderings nobody thought to write by hand.

Where to start if you already have a ledger

If you already run trust accounting software, you don’t need to rebuild it to get most of this. I’d check, in order: whether any code path can update or delete a posted line; whether money-moving endpoints accept an idempotency key and enforce it with a unique index; whether withdrawals lock the ledger before checking funds; and whether reconciliation matches against stored bank references or against amounts and dates. Each of those can be fixed on its own.

Card payments have the same retry problems one step earlier, at the gateway. I cover that in one payment interface, many gateways and a separate PCI vault.

If you’re building or fixing trust accounting software and want someone who has designed this from the ledger up, that’s the work I describe on my trust accounting software page.