Never store a balance. Store the entries that produce it

The requirement was that children earn points for completing work, and spend them on rewards their parents have set up. The obvious implementation is a points column on the child record: add on earn, subtract on spend, read it to display the balance.

That design survives about a week of real use, and it fails in four specific ways that are worth enumerating because they generalise to every system that tracks a quantity of anything.

A retried request pays twice. A tablet on a poor connection submits a completed piece of work, the response is lost, the app retries. The server has no way to recognise the second request as the same event, so it adds the points again. Mobile clients retry. This is not an edge case, it is Tuesday.

Concurrent writes lose one another. Two devices, one balance column, read-modify-write. A spend and an earn arriving together produce whichever value was written last. The other one is gone with no trace that it ever existed.

You cannot answer the question people actually ask. A nine-year-old will ask where her points went. A balance column can tell you the number. It cannot tell you a single thing about how that number came to be. Neither can it tell you when a parent reports that the total looks wrong, which means every such report becomes an unresolvable argument.

You cannot correct a mistake safely. If something was awarded in error, adjusting the balance column destroys the evidence of both the original award and the correction.

So the points are stored as an append-only ledger. Every entry records a child, an amount, a reason, and a reference to the thing that caused it. The balance is the sum of the entries. There is no column holding a total, anywhere.

What the ledger buys, specifically

Idempotency becomes a database constraint rather than application logic. A unique index across child, reason, reference type and reference id means a duplicate award cannot be inserted. Not “should not” — cannot. The retry that would have paid twice hits the constraint and the handler treats a conflict as “this was already decided”, returning the same answer it returned the first time. The correctness does not depend on anyone remembering to check first, and it holds under concurrency, which application-level checks generally do not.

That is the important structural point. Idempotency implemented as check-then-insert is a race condition with good intentions. Idempotency implemented as a unique constraint is a guarantee.

Corrections are entries, not edits. If an award has to be reversed, the system writes a new entry with a negative amount and never touches the original. The history remains true: this was awarded, and then this was reversed, on this date, for this reason. Anyone auditing the balance can reconstruct exactly what happened. This is the same reason accountants do not use an eraser.

A related rule fell out of this: a rejection writes no entry at all unless it is overriding a previous approval, in which case it writes the reversal. Rejecting something that was never approved should leave the ledger untouched, because nothing happened.

Pending is a different thing from earned. Work awaiting a parent’s approval is visibly pending and explicitly excluded from the spendable balance. With a single column you would have to either credit it early — and claw it back if rejected, which is the worst possible experience — or hold it somewhere else entirely. With a ledger it is a state on an entry.

The bug the ledger did not prevent

Here is the part that matters more than the design, because it shows where the reasoning was incomplete.

The unique constraint was originally keyed on the attempt. That satisfied the requirement everyone had articulated: a retried submission does not pay twice. Same attempt, same key, constraint holds, duplicate refused. Correct.

It did nothing whatsoever about a child simply starting a new attempt. New attempt, new key, new row, more points. The exercise could be farmed indefinitely, and the ledger dutifully recorded every single payment as legitimate — which, under the rule as written, they were.

The requirement had been stated as “do not double-pay on retry” when what was actually meant was “pay once per piece of work, ever”. Those are different sentences and they produce different keys.

The fix was to re-key the constraint onto the underlying exercise rather than the attempt, with the earliest submitted attempt deciding the outcome. A retry now returns a byte-identical response, because it resolves to the same deciding attempt. A retake reports zero points earned, honestly, rather than silently paying again.

The lesson is about idempotency keys generally: the key must be the identity of the business event, not the identity of the request. A request id deduplicates network retries. Only a business key deduplicates the thing you actually care about happening twice. Most systems that get this wrong have chosen a technically correct key that answers a narrower question than the one being asked, and the gap only shows up when a user finds it — and users find it quickly when there is a reward on the other side.

A daily ceiling per child was added on top, which is a blunt instrument but a useful one. It bounds the damage from any farming route nobody has thought of yet, including the ones that will be introduced by future features. Ledger design gives you correctness for the cases you modelled; a rate limit gives you a bounded loss for the cases you did not.

The rule about automation

One further decision from the same subsystem, because it is a design principle rather than a technical one.

Some submitted work is checked automatically before a parent sees it. The verdict is one of three values — passes, unsure, fails — and only a pass above a confidence threshold results in automatic approval. Everything else goes to a human.

The rule stated plainly: the automated check may approve, and may never reject.

The asymmetry is deliberate and it comes from what the two errors cost. A wrong approval means a child was paid for work that was not quite done, which is a small and recoverable loss. A wrong rejection means taking away a reward a child genuinely earned — and she will not experience that as a software defect. She will experience it as unfairness, and no amount of subsequent correction undoes that.

Generalised: when automating a judgement, work out which direction of error is recoverable and only automate that direction. Most classification problems have this asymmetry, and most implementations ignore it because accuracy is measured as a single number. The threshold you choose is a statement about whose mistake you would rather make, and it deserves to be made explicitly.

The principle

Store facts, derive totals. A balance is a conclusion, and conclusions belong in queries, not in columns.

If you take one operational habit from this: any time you find yourself writing UPDATE ... SET quantity = quantity + n, stop and ask what happens when that statement runs twice. If you cannot answer it with a constraint rather than a promise, you have a ledger-shaped problem wearing a column.