Search for a command to run...
How do you show the total under a parent account when the whole system refuses to store a balance? A recursive query, a trigger that refuses to draw a circle, and a rule about what actually has to sum to zero.
WhoAmI => notes.sohag.pro/author
No comments yet. Be the first to comment.
Kicking off a 12-week build of an open-source, production-grade payment ledger in Go: double-entry accounting, Postgres, gRPC, OpenTelemetry, and a real deployment.
How do you hold a large payment for a second pair of eyes without ever letting the unapproved money touch a balance, and how do you stream that decision to the outside world without standing up a broker?
I had a solid list of reasons my life wasn't moving faster, until a grainy old lecture pointed out the one name missing from it
The finale isn't a victory lap. It's the story of the control I shipped that did nothing, the footgun still sitting in my demo, and the handful of things I'd keep exactly as they are.
How do you hold a large payment for a second pair of eyes without ever letting the unapproved money touch a balance, and how do you stream that decision to the outside world without standing up a broker?
Every time I wanted to change an FX rate I had to edit a file on the server and restart the app. So I moved rates and markup into a live admin API, and then audited it hard enough to find the bug that quietly undid the whole thing.
Open any budgeting app and look at the summary screen. It says you spent 620 on "Food" this month. But you never made a payment to something called Food. You bought groceries, you got coffee four times, you ordered dinner twice. "Food" is not a thing money moved to. It is a bucket that holds other buckets, and the number next to it is everything underneath, added up.
That is an account hierarchy, and this week I gave the ledger one. "Assets" holds "Cash" and "Bank". "Bank" holds "Checking" and "Savings". You read the balance of "Bank" and you want the total across everything below it, not just whatever happened to be posted directly to the "Bank" row.
Sounds like a small feature. It runs straight into the one rule this whole project is built on.
From day one, go-ledger never stores a balance. An account is just identity: a name, a type, a currency. Its balance is derived, computed by summing every posting that ever touched it. The history is the truth; the number is always recomputed from it. That is what makes the ledger trustworthy: there is no balance field for a bug to corrupt, no counter that can drift away from reality.
So when someone asks "what is the rolled-up balance of Bank", the tempting answer is to keep a running total on the Bank row and update it every time a child gets a posting. That answer is wrong here. It reintroduces exactly the stored, mutable number the ledger was designed to never have, and it drifts the first time a posting or a re-parenting misses the update.
The right answer is to keep the balance derived, and make the rollup a question you ask, not a value you store.
Give an account an optional parent_id pointing at another account. A chart of accounts becomes a tree:
Assets own: 0 rolled up: 800
|
+-- Cash own: 500 rolled up: 500
|
+-- Bank own: 100 rolled up: 300
|
+-- Checking own: 150 rolled up: 150
|
+-- Savings own: 50 rolled up: 50
"own" is what was posted directly to that account. "rolled up" is that account plus everything under it. Bank's own balance is 100, but rolled up it is 100 + 150 + 50 = 300. Assets was never posted to at all (own 0), but rolled up it is the whole subtree: 500 + 300 = 800.
Notice a parent can still have its own postings. Bank has 100 of its own on top of its children. There is no rule that a parent must be an empty folder; its rollup is simply its own postings plus all of its descendants'. An account you never post to is just a parent that happens to have zero of its own.
Two ways to compute the rollup, and I use both.
For one account, a recursive query walks the tree in the database. Gather the account and all of its descendants by following parent_id downward, then sum the postings of that whole set:
WITH RECURSIVE subtree AS (
SELECT id FROM accounts WHERE tenant_id = $1 AND id = $2
UNION ALL
SELECT a.id FROM accounts a
JOIN subtree s ON a.parent_id = s.id AND a.tenant_id = $1
)
SELECT COALESCE(SUM(p.amount), 0)
FROM postings p
WHERE p.tenant_id = $1 AND p.account_id IN (SELECT id FROM subtree);
Nothing is stored. The number is computed fresh from the postings every time you ask, same as an ordinary balance read, just over a subtree instead of a single account.
For the whole tree at once (the console's accounts page, the reports), running that recursive query once per account would be a query per node. Instead I pull every account's own balance in one flat query, then roll up in memory: build the parent-to-children map, and for each node compute own plus the sum of its children, recursively, memoized. That is linear in the number of accounts, one database read, and it hands back the tree in parent-before-child order with each node's depth so the console can just indent and print it.
A tree has one way to go badly wrong: a cycle. Make A the parent of B, then make B the parent of A, and "roll up A" chases its own tail forever. The recursive query would spin; the in-memory rollup would recurse without end.
So the database will not let a cycle exist in the first place. A trigger runs before any insert or any change to parent_id, walks up the ancestor chain from the proposed parent, and if it ever reaches the row itself, it rejects the change. It also rejects an account trying to be its own parent, and a child whose currency does not match its parent's (a subtree is single-currency, which is what lets a rollup be one number instead of a pile of per-currency totals). The application checks too and returns a clean error, but the database is the guarantee no code path can slip past.
Same tenant is handled without any trigger at all. The parent link is a composite foreign key on (tenant_id, parent_id), so a parent has to live in the same tenant as its child by construction. You cannot point an account at another tenant's account even if you try; the constraint simply will not allow the row.
Reports show the rolled-up number now. The trial balance lists every account with its own balance and its rolled-up balance side by side. But there is a trap.
The trial balance's real job is to prove the books balance: for each currency, every account's balance summed together must be exactly zero. That is the double-entry invariant made visible. If it is not zero, money was created or destroyed and something is very wrong.
You cannot run that proof on rolled-up balances. A rollup counts a child's money twice: once in the child's own row, once in the parent's rolled-up row. Sum all the rollups and you will get a number that is not zero, and it will look like the books are broken when they are perfectly fine.
So the rule is: rollups are for reading, own balances are for proving. The zero-proof is computed from own (leaf) balances exactly as it always was; the rolled-up column is an extra figure for a human to read the tree, and it never touches the invariant check. Two numbers on the same row, and only one of them is allowed near the proof.
An operator opens the accounts page and sees a real chart of accounts, indented, with each parent showing the total underneath it. They read a parent's balance rolled up and get the whole subtree. They move an account under a new parent, and the rollup reflects it on the next read, because there was never a stored number to update. And through all of it, no balance is stored, and the books still prove out to zero on the numbers that are supposed to.
The whole thing is written up in ADR-023, and the repo is open source at github.com/sohag-pro/go-ledger. Next: approval workflows and an outbox event stream, holding a big transaction for a second signature before it posts.