How to Add a Loyalty Points System to an AI-Built App

Two customers redeem points at the same coffee shop within the same second, one on the app, one at the register. If your loyalty system stores points as a single mutable balance column on the users table, one of those redemptions can silently overwrite the other, and a customer walks out having spent points that were never actually deducted. That is the bug most guides on how to add a loyalty points system to an AI-built app never mention, because it only shows up under real concurrent load, long after launch. The fix is not a lock, or at least not only a lock. It is a different schema: an append-only ledger, never a balance you update in place.

The bug: check the balance, then update it

A single SQL UPDATE that decrements a number is atomic at the row level, most databases will not lose that write on its own. The real danger sits one step earlier, in the check that has to happen before a redemption is allowed: does this user actually have enough points. A naive implementation reads the balance, checks it in application code, and only then issues the update.

• `balance = db.query("SELECT points_balance FROM users WHERE id = ?", user_id)`

• `if balance >= 500:`

• ` db.execute("UPDATE users SET points_balance = points_balance - 500 WHERE id = ?", user_id)`

Run that from two requests at the same instant, both read balance = 500, both pass the check, both proceed to subtract. The user now has -500 points, or worse, two rewards were granted for points that only existed once. This is a classic time-of-check-to-time-of-use race, and it gets more likely, not less, exactly when your loyalty program is working, at checkout during a rush.

How to add a loyalty points system to an AI-built app: the ledger schema

Instead of a balance column, every earn, redemption, and expiration is its own row. The balance is never stored directly, it is always computed. This is the points and rewards schema worth building from day one, because retrofitting it after a balance column is already in production means reconciling every existing user's history. Good loyalty program database design treats every point as an event that happened, not a number you overwrite.

• `CREATE TABLE points_ledger (`

• ` id bigserial primary key,`

• ` user_id bigint not null references users(id),`

• ` delta integer not null,`

• ` reason text not null,`

• ` reference_id text,`

• ` expires_at timestamptz,`

• ` created_at timestamptz not null default now()`

• `);`

• `CREATE UNIQUE INDEX ON points_ledger (user_id, reference_id) WHERE reference_id IS NOT NULL;`

delta is positive for an earn and negative for a redemption or expiration. reason is a short code like purchase, redemption, expiration, or referral_bonus. reference_id ties a row back to the order, redemption request, or referral that caused it, and the unique index on (user_id, reference_id) is what stops the same order from ever being credited twice if a webhook fires more than once, which webhooks do.

Computing a live balance

With no balance column to trust, the balance is a query, honoring per-row expiration in the same statement:

• `SELECT COALESCE(SUM(delta), 0) AS balance`

• `FROM points_ledger`

• `WHERE user_id = 42`

• ` AND (expires_at IS NULL OR expires_at > now());`

That single query replaces the balance column entirely, and it is correct by construction: it can never drift from the ledger, because it is the ledger. If you need this on every page load for thousands of users, cache the result and invalidate it on write, but never let the cache become the thing you trust for the actual redemption check.

Serializing redemptions without locking the whole table

The ledger fixes the storage problem but not the race by itself, two simultaneous redemption requests can still both compute a sufficient balance before either one's INSERT commits. The fix is a lock scoped to the one user being redeemed against, not the table:

• `BEGIN;`

• `SELECT pg_advisory_xact_lock(42);`

• `-- recompute balance with the SUM query above, inside this same transaction`

• `-- if balance >= 500, proceed`

• `INSERT INTO points_ledger (user_id, delta, reason, reference_id)`

• `VALUES (42, -500, 'redemption', 'redeem_8891');`

• `COMMIT;`

pg_advisory_xact_lock keyed on the user's id serializes only that user's redemptions against each other. Every other user's earns and redemptions proceed untouched, so a busy loyalty program does not turn into a queue behind a single lock.

Tiered expiration logic that stays auditable

Points expiration logic gets complicated once tiers enter the picture: standard members lose points 12 months after they are earned, top-tier members get 24. Store the expiry per row, computed once at insert time from the user's tier at the moment of the purchase, not recalculated later against their current tier. A downgrade should not retroactively expire points a customer earned while they still had the higher tier.

Expiring points is itself just another ledger row, an offsetting negative entry, rather than deleting or rewriting the original earn:

• `INSERT INTO points_ledger (user_id, delta, reason, reference_id)`

• `SELECT user_id, -delta, 'expiration', id::text`

• `FROM points_ledger AS earn`

• `WHERE reason = 'purchase'`

• ` AND expires_at <= now()`

• ` AND NOT EXISTS (`

• ` SELECT 1 FROM points_ledger AS exp`

• ` WHERE exp.reference_id = earn.id::text AND exp.reason = 'expiration'`

• ` );`

The NOT EXISTS guard, combined with using the original row's own id as the expiration's reference_id, makes this job idempotent. Running it twice by accident, or after a retry, never double-expires the same points, and the full history of every earn and every expiration stays queryable for support and audits.

Reason codes, tiers, and where a lookup table helps

If the reason column grows past a handful of fixed values, or you start attaching multiple labels to a single reward, a normalized tagging schema built the same careful way applies the same lookup-table discipline: a separate table of valid reasons or tags, referenced by id, instead of a widening text field with typos waiting to happen.

Abuse cases the ledger has to survive

The reference_id uniqueness constraint is not just for webhook retries, it is your main defense against a customer canceling and rebooking the same order repeatedly to farm points, or an affiliate script hammering the same referral. Tie reference_id to something the customer cannot regenerate on demand, the source order's own id, not a client-supplied string. Loyalty points and referral rewards share almost the same abuse surface, and a referral program that shares the same abuse-case thinking is worth reading before you ship either, since both live or die on stopping the same class of duplicate-claim exploit.

If you have not built out the rest of the app's payments and account layer yet, the full guide to building an app with AI is the better starting point before bolting a rewards system onto something that is not there yet.

A ledger-based loyalty system with tiered expiration is a few days of focused work for someone who has built one before, longer if this is the first time. If you are weighing building it in house against handing it to an agency, what agencies typically charge to build a feature like this gives a reasonable ballpark for something of this complexity.

Frequently asked questions

Frequently asked questions

Should points balance ever be a column on the users table?

Not as the source of truth. A cached balance column is fine as a read-optimization if it is recomputed from the ledger and never written to directly by application logic, but the ledger is what you reconcile against and what you trust during a dispute.

What's the real difference between a mutable balance and a ledger schema?

A balance column stores one number that gets overwritten. A ledger stores every event that changed the number, so the current balance is a derived sum rather than a stored fact, which makes lost updates structurally impossible instead of merely unlikely.

How do I design a loyalty program database without race conditions?

Store every change as an insert-only ledger row, compute balances with a SUM query, and serialize the check-then-redeem step with a per-user lock rather than a table-wide one. The combination removes both the lost-update bug and the table-level contention a full lock would cause.

How should points expiration logic handle tier changes?

Set the expiration date on each earned row at the moment it is earned, based on the tier the customer held then. Recomputing expiry against a customer's current tier retroactively changes the value of points they already earned, which reads as unfair and is hard to explain in a support ticket.

Can a customer's points balance go negative?

It shouldn't, if the redemption check and insert happen inside the same locked transaction described above. A negative balance in production almost always means the check and the write were not atomic with each other.