Most reconciliation failures begin in the domain model, months before anyone notices a mismatch.
When a finance team tells us the numbers don't tie out, the cause is almost never a lost row. It is that the system was modelled as balances rather than as events, and a balance cannot explain itself.
Postings, not updates
A ledger entry should be immutable and doubly-entered. Corrections are new entries, never edits. Once that is true, every balance in the system is a fold over history, and any disagreement is answerable.
create table posting (
id bigserial primary key,
entry_id uuid not null, -- groups the balanced legs
account_id uuid not null,
amount bigint not null, -- minor units, signed
currency char(3) not null,
occurred_at timestamptz not null,
idem_key text not null unique
);
-- an entry must net to zero, per currency
create or replace function assert_balanced() returns trigger as $$
begin
if (select sum(amount) from posting where entry_id = new.entry_id) <> 0 then
raise exception 'unbalanced entry %', new.entry_id;
end if;
return null;
end $$ language plpgsql;The idempotency key is doing more work than the constraint. Payment rails retry; webhooks arrive twice; a mobile client on a bad connection will send the same receipt three times. Idempotency at the posting level is what makes those harmless.
Settlement accounts make partners legible
- One account per partner, per currency, per purpose. Never a shared 'suspense' bucket.
- Money in transit gets its own account, so the gap between initiation and settlement is a number, not a mystery.
- Fees are postings, not adjustments to the principal leg.
Reconciliation should be a continuous assertion the system makes about itself, not an investigation a person performs at month end.
On Sanchay, this shape is what let reconciliation become a continuous assertion rather than a monthly investigation — not because the ledger was fast, but because nobody had to stop and check it by hand.