Loyalty points - a currency you print yourself

Why this design

Airlines, coffee chains, supermarkets, card issuers and marketplaces all run points programs, and almost every first version has a points integer on the user and an UPDATE on each purchase. It works until somebody asks which points expire next month, a refunded order needs its points back, or the auditor asks what the program owes. Interviewers use this question to see whether you recognise a ledger problem in disguise, whether you can model expiry without a job that mutates balances, and whether you know that unredeemed points are a liability with real accounting rules (IFRS 15 / ASC 606 treat them as deferred revenue, released on redemption or expected breakage).

What you will learn

• Why points are stored as immutable lots with vests_at and expires_at, and the balance is derived.
• How a redemption consumes lots oldest-expiry-first and records exactly which ones, so a cancellation restores the same lots.
• Why expiry is a filter at read time and the nightly sweeper only books it.
• Why redemption is reserve-then-confirm, because the reward is fulfilled by somebody else.
• How liability is computed - outstanding points x value x (1 - breakage) - and why breakage is an estimate that moves.
• The honest weakness: chargebacks after vesting, and a negative balance as the only remedy.

How to read this board

1. Diagram, left to right: EARN (points are printed on a purchase), THE LEDGER (lots, not a balance), REDEEM (reserve, then confirm), TIME (points die on schedule). Press play for the walkthrough.
2. Below it, LIABILITY AND CLAWBACK: the promise on the balance sheet, and the case it does not handle.
3. Then the DATA MODEL: six tables. There is no balance column anywhere.
4. Pages 1-3 · Requirements, Capacity and Storage: 142 M points printed a day, 59 billion outstanding.
5. Page 4 · Availability - why earning can be late and redemption cannot double-spend.
6. Page 5 · How it works, then Page 6 · Deployment, where a rules change is a financial event.

A loyalty program issues a private currency as a reward for spending, and lets members exchange it for goods, discounts or partner rewards. The system is a ledger of that currency plus the rules that create it and the clock that destroys it.

Ask these before designing

• When are points spendable? Immediately is what members want. After the return window (30 days) is what finance wants, because returning a $400 purchase after spending its points is a straight loss.
• Does expiry run per lot or per account? "24 months after earning" is per lot. "24 months after your last activity" is per account and far simpler - but then every lot shares one fate and the question "what expires this month" has a different meaning.
• Can a balance go negative? It must be allowed to, briefly, for clawback - or clawback must fail, which means the business eats every late refund.
• Who fulfils rewards? If an airline issues the ticket, redemption is a two-phase operation with an external party that can say no.
• Do partners buy points? A card issuer buying points at $0.007 each makes points cash-backed revenue. The ledger must know which lots were paid for, because their liability is accounted differently.

Functional

• Earn points from purchases and partner events, by rules (base rate, tier multiplier, campaigns), idempotently per order.
• Show a balance, a pending balance, and points expiring in the next 30 days.
• Redeem for a reward: reserve, then confirm or release.
• Expire lots at expires_at; claw back points for refunded or charged-back orders.
• Report outstanding liability daily.

Non-functional

• No point is spent twice. Concurrent redemptions by one member are serialised.
• Balance read p99 under 50 ms; redemption reserve p99 under 300 ms.
• Earning may lag by minutes, and is idempotent on the order id.
• Every point is traceable: earned by which order, under which rule version, consumed by which redemption, or expired on which date.
• Seven-year retention of entries - they support audited financial statements.

Out of scope

Tier qualification, campaign design, the reward catalogue’s merchandising, fraud scoring.