Built a loyalty wallet, leagues and badges
Built the loyalty and reputation system — 67 functions over a
31‑table ledger, with leagues, badges and a redemption shop — taking
a row lock on the balance to close the double‑spend window.
2025

Situation. Professional networking has a cold start problem: the
platform is worth using once other people are already using it, and
until then there is little reason to come back. The usual lever is a
rewards system — points for contributing, standing that reflects
reputation — which is easy to describe and treacherous to build,
because the moment points can be spent they are money, and every
mistake money makes is available to be made here.

Task. Contribution had to be measurable and rewardable, with a
balance that could not be spent twice.

Action. The loyalty schema runs to 31 tables and 67 functions, with
standing in a further 5 tables and 32 functions, and discovery and
personalization schemas alongside them to decide what a given member
sees. On top of the ledger sit leagues, badges and a redemption shop
where a balance turns into something real. The part that took the
care is the oldest bug in the book: check the balance, then spend
it, and two requests arriving together both pass the check. Every
mutation takes a row lock on the wallet before reading it, so the
second request waits for the first to finish rather than racing it.
That the wallet is in the same database as everything else is what
makes it possible at all — the balance and the thing it bought
commit together or not at all.

Result. Contribution is measured and rewarded, and a balance is
arithmetic rather than an approximation. Row locking is the slower
answer and was chosen anyway: contention on a wallet means members
wait their turn, and the alternative is a member spending the same
points twice and someone reconciling it by hand afterwards.

Categories:
1Backend Engineering	/categories/backend/	data.engineer.company	70
1Databases	/categories/databases/	data.engineer.company	70
1PostgreSQL	/categories/postgresql/	data.engineer.company	70
1SQL	/categories/sql/	data.engineer.company	70
1Performance Tuning	/categories/performance/	data.engineer.company	70
1Product & Requirements	/categories/product/	data.engineer.company	70

Services:
1Backend & API Development	/services/backend-development/	data.engineer.company	70
1Database Design & Modeling	/services/database-design/	data.engineer.company	70
1Product Strategy & Requirements	/services/product-strategy/	data.engineer.company	70

hThis page on the web	URL:https://data.engineer.company/portfolio/built-a-loyalty-wallet-leagues-and-badges/	data.engineer.company	70
