This is an English rewrite of an article I originally published in Japanese. I've run the design on PostgreSQL 17 in Docker and folded the measured numbers into this post.
TL;DR
Designing a points system where each grant expires on its own date (think airline miles, not "12 months since last activity") is far harder than it looks.
I compare three relational designs and land on an object-oriented ledger with three tables: deposit, withdraw, and a deposit_withdraw allocation table.
That allocation table is the whole trick: it records which grant each spend drew from, which makes exact cancellation and accounting reconciliation fall out for free.
