Never Store a Loyalty Balance — Points as a Ledger You Can Replay

There is a moment that every loyalty scheme eventually has, and everything about how the software was built is decided by it.

A customer is at the counter. The screen says 340 points. They say it should be 540, because of the thing they bought last month. Somebody now has to work out which of those numbers is true, in front of a queue.

If your system stores a balance, you cannot. You can only read the number back to them more slowly.

The obvious design, and where it goes

The natural way to build points is a column on the customer record. Someone earns, you add. Someone redeems, you subtract. Reading a balance is one cheap lookup, and it is fast.

The trouble is that this creates two sources of truth — the transaction history and the stored total — and they only agree as long as nothing ever goes wrong between writing one and updating the other. Every path that touches points has to update both, forever, including the ones added two years later by someone who did not know the invariant existed.

The failure is not dramatic. There is no crash and no error. The two numbers simply drift apart, and nobody finds out until a customer asks the question above. At that point the history is the honest record and the balance is a rumour, but the balance is the number the screen shows.

The alternative

Store only the entries. Every earn, every redemption, every correction is a row. The balance is not stored anywhere:

balance = SUM(points) for that customer

There is no balance column, and the schema should be tested to prove one has not quietly appeared. That single constraint has a consequence you feel at the counter: no screen, export, PDF or API response can disagree with the history behind it, because there is nothing else for them to read. The number and its explanation are the same object.

It costs an aggregate query. On the volume a loyalty scheme produces, that is not a real cost, and it buys the one property the scheme actually needs.

Entries are immutable, and mistakes are reversed rather than erased

The second rule follows from the first: nothing updates or deletes an entry. A mistake is corrected by writing a new, opposite entry with a reason attached.

This feels wasteful until the first dispute. A ledger that can be edited cannot answer "why is my balance this number" — it can only assert the current state. A ledger that can only be appended to answers the question by replaying itself: here is the purchase that earned 200, here is the reward that spent 150, here is the correction the manager made on the twelfth and the reason they typed.

That is the difference between a balance you can defend and a balance you can only insist on.

The race that decides whether any of this holds

Now the part that separates a points system that works from one that works under load.

A customer has 300 points. Two redemptions of 200 arrive at the same instant — a clerk at the till and the customer on their own device, or simply a double-tapped button.

The obvious implementation checks first and writes after:

1. read the balance          -> 300
2. is 300 >= 200?            -> yes
3. write the redemption

Run that twice concurrently and both requests read 300 before either writes. Both pass the check. Both write. The customer has now spent 400 points they never had, and the balance is negative — silently, with no error anywhere, in a system whose entire job is to be arithmetically correct.

The fix is not a bigger check. It is moving the read inside the lock:

1. open a write transaction
2. re-read the balance INSIDE the lock
3. refuse if it is short
4. write the entry and the redemption together, or neither

The second request now blocks until the first commits, re-reads a balance of 100, and is refused. One winner, one honest refusal, no negative balance.

Both database engines need this and they spell it differently — SQLite takes the write lock at the start of the transaction, and MySQL or MariaDB needs the balance read to be explicitly locking. A system that gets it right on one engine and not the other is broken on the other, and nothing in a normal test run will say so.

Proving it, rather than asserting it

A race condition cannot be tested by calling a function twice in a loop. Single-process tests interleave nothing and pass whether or not the lock is there.

The test that means something spawns real operating-system processes and points them at the same customer at the same moment: six processes racing for three redemptions' worth of points. The correct outcome is exactly three successes, three refusals, and a balance of precisely zero — never below.

And the test only proves what it claims if you have watched it fail. Move the balance read back outside the lock, run it again, and watch four processes win and the balance go negative. That is the moment the test earns trust. A green test that has never been shown to go red is a decoration.

Two rules that protect the ledger from the people using it

Points are earned, never typed. You enter what happened — the amount spent, the visit — and the programme's rate decides the points, rounding down in integer arithmetic. There is deliberately no screen, endpoint or assistant tool that takes a points figure directly, because that is the ability to mint points with nothing behind it.

Creating points is a different permission from spending them. Redeeming spends points a customer already earned against a reward with a published cost: that is ordinary counter work, and a clerk should do it. An adjustment creates or destroys points with nothing behind it but a typed reason, so it belongs a rung higher, with a manager. A register where anyone at the till can mint points is not a register anyone can defend — and a negative adjustment is held to the same floor at zero as a redemption, because a balance below zero has stopped describing anything real.

Why this is the part worth caring about

Loyalty software is sold on the things that photograph well: tiers, badges, campaigns, referral widgets. The ledger underneath is assumed to work, in the way plumbing is assumed to work.

But the ledger is the product. Everything else is presentation over a number, and if the number cannot explain itself, the presentation is decoration on a dispute you are going to lose.

Loyalora is built on the three rules above — the balance as a sum, the immutable entry, and the re-read inside the lock — with the concurrency proof run against both database engines, and the schema tested to confirm no balance column has appeared. What it costs to rent the same job from a hosted vendor is in the pricing teardown, with every figure quoted from the vendor's own page and dated.