A loyalty points system rewards users for purchases and engagement, allowing them to redeem points for discounts or free items. Fundamental requirements: a reliable ledger that never loses or double-counts points, expiry handling, atomic earn-and-redeem operations, and a balance that can be queried efficiently without replaying every transaction. Used by airlines, retailers, food delivery apps, and SaaS products.
Core Data Model
-- Immutable ledger — every earn and redeem is a row. NEVER update or delete.
CREATE TABLE PointTransaction (
txn_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL,
points INT NOT NULL, -- positive = earn, negative = redeem/expire
txn_type TEXT NOT NULL CHECK (txn_type IN ('earn','redeem','expire','adjust','refund')),
reference_id TEXT, -- order_id, promo_code, redemption_id
reference_type TEXT, -- 'order', 'promotion', 'redemption'
expires_at TIMESTAMPTZ, -- non-NULL for earned points that expire
notes TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_points_user ON PointTransaction (user_id, created_at DESC);
CREATE INDEX idx_points_expiry ON PointTransaction (expires_at) WHERE txn_type = 'earn' AND expires_at IS NOT NULL;
-- Materialized balance cache for fast reads (recomputed on each transaction)
CREATE TABLE PointBalance (
user_id UUID PRIMARY KEY,
available INT NOT NULL DEFAULT 0, -- spendable now (not expired)
pending INT NOT NULL DEFAULT 0, -- earned but not yet available (hold period)
lifetime INT NOT NULL DEFAULT 0, -- total ever earned (for tier calculation)
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
Earning Points
from uuid import uuid4
from datetime import datetime, timezone, timedelta
import psycopg2
POINTS_PER_DOLLAR = 10
EARN_HOLD_DAYS = 3 # points available after 3-day hold (chargeback window)
POINTS_EXPIRY_DAYS = 365
def earn_points_for_order(conn, user_id: str, order_id: str, order_total_cents: int) -> int:
"""
Award points for a completed purchase.
Points are held for EARN_HOLD_DAYS before becoming spendable (chargeback protection).
Returns points earned.
"""
points = (order_total_cents // 100) * POINTS_PER_DOLLAR
if points <= 0:
return 0
now = datetime.now(timezone.utc)
available_at = now + timedelta(days=EARN_HOLD_DAYS)
expires_at = now + timedelta(days=POINTS_EXPIRY_DAYS)
with conn.cursor() as cur:
# Idempotency: one earn per order
cur.execute(
"SELECT 1 FROM PointTransaction WHERE reference_id = %s AND txn_type = 'earn'",
(order_id,)
)
if cur.fetchone():
return 0 # already credited
# Insert earn transaction
cur.execute("""
INSERT INTO PointTransaction
(user_id, points, txn_type, reference_id, reference_type, expires_at, notes)
VALUES (%s, %s, 'earn', %s, 'order', %s, %s)
""", (user_id, points, order_id, expires_at,
f"Earned for order {order_id}"))
# Update balance cache: add to pending (available after hold period)
cur.execute("""
INSERT INTO PointBalance (user_id, pending, lifetime)
VALUES (%s, %s, %s)
ON CONFLICT (user_id) DO UPDATE
SET pending = PointBalance.pending + EXCLUDED.pending,
lifetime = PointBalance.lifetime + EXCLUDED.lifetime,
updated_at = NOW()
""", (user_id, points, points))
conn.commit()
return points
def release_held_points(conn):
"""
Cron job: move points from pending to available after hold period.
Runs every hour; processes in batches.
"""
now = datetime.now(timezone.utc)
with conn.cursor() as cur:
cur.execute("""
SELECT user_id, SUM(points) as pending_points
FROM PointTransaction
WHERE txn_type = 'earn'
AND created_at <= %s - interval '%s days'
AND reference_id NOT IN (
SELECT reference_id FROM PointTransaction
WHERE txn_type IN ('redeem', 'expire')
)
GROUP BY user_id
""", (now, EARN_HOLD_DAYS))
# Simplified: production systems track per-batch release with a 'released' flag
Redeeming Points
def redeem_points(conn, user_id: str, points_to_redeem: int, order_id: str) -> int:
"""
Atomically redeem points against an order.
Returns discount_cents (100 points = $1).
Raises ValueError if insufficient balance.
"""
CENTS_PER_100_POINTS = 100
with conn.cursor() as cur:
# Lock the user's balance row to prevent concurrent overdraft
cur.execute(
"SELECT available FROM PointBalance WHERE user_id = %s FOR UPDATE",
(user_id,)
)
row = cur.fetchone()
available = row[0] if row else 0
if available < points_to_redeem:
raise ValueError(f"Insufficient points: have {available}, need {points_to_redeem}")
# Idempotency: one redemption per order
cur.execute(
"SELECT 1 FROM PointTransaction WHERE reference_id = %s AND txn_type = 'redeem'",
(order_id,)
)
if cur.fetchone():
raise ValueError("Points already redeemed for this order")
# Insert redeem transaction (negative points)
cur.execute("""
INSERT INTO PointTransaction
(user_id, points, txn_type, reference_id, reference_type, notes)
VALUES (%s, %s, 'redeem', %s, 'order', %s)
""", (user_id, -points_to_redeem, order_id,
f"Redeemed {points_to_redeem} points on order {order_id}"))
# Decrement balance cache
cur.execute(
"UPDATE PointBalance SET available = available - %s, updated_at = NOW() WHERE user_id = %s",
(points_to_redeem, user_id)
)
conn.commit()
discount_cents = (points_to_redeem // 100) * CENTS_PER_100_POINTS
return discount_cents
Points Expiry Job
def expire_points(conn):
"""
Nightly job: expire points that have passed their expiry date.
Uses a ledger entry (negative transaction) rather than deleting rows.
"""
now = datetime.now(timezone.utc)
with conn.cursor() as cur:
# Find users with expiring points
cur.execute("""
SELECT user_id, SUM(points) as expiring_points
FROM PointTransaction
WHERE txn_type = 'earn'
AND expires_at 0
""", (now,))
rows = cur.fetchall()
for user_id, expiring_points in rows:
with conn.cursor() as cur:
# Insert expiry transaction
cur.execute("""
INSERT INTO PointTransaction
(user_id, points, txn_type, notes)
VALUES (%s, %s, 'expire', 'Annual points expiry')
""", (user_id, -expiring_points))
# Update balance
cur.execute("""
UPDATE PointBalance
SET available = GREATEST(0, available - %s), updated_at = NOW()
WHERE user_id = %s
""", (expiring_points, user_id))
conn.commit()
# Notify user of expiry (send email)
notify_points_expiry(user_id, expiring_points)
Key Interview Points
- Ledger pattern vs. running balance: Never store a single mutable balance field. Use an immutable ledger (all transactions as rows) with a materialized balance cache for fast reads. The cache is a denormalization that can be rebuilt from the ledger at any time — it’s the authoritative source. This allows auditing, debugging, and recomputing the balance from scratch if the cache is corrupted.
- FOR UPDATE prevents overdraft: Without locking, two concurrent redemptions can both read the same available balance, both pass the check, and both deduct — resulting in a negative balance. SELECT … FOR UPDATE on the PointBalance row serializes concurrent redemptions for the same user. Alternative: use an optimistic lock (version column) and retry on conflict.
- Earn idempotency: Check reference_id before inserting earn transactions. If a webhook fires twice for the same order, or a background job retries, the idempotency check prevents double-crediting. Index reference_id for fast lookup: CREATE INDEX ON PointTransaction (reference_id) WHERE txn_type = ‘earn’.
- FIFO vs. LIFO expiry: When a user redeems 500 points, which earn transactions are consumed? FIFO (oldest first) minimizes points lost to expiry — points closest to expiry are spent first. This requires tracking which earn batches have been consumed, adding complexity. Simple approach: apply redemptions against available balance without tracking batch consumption; expiry job expires all earned points past their date. Most consumer loyalty programs use the simple approach.
- Tier calculation: Use lifetime points (total ever earned, not current balance) for tier thresholds (e.g., Silver = 5,000 lifetime, Gold = 25,000). Track lifetime separately so it never decreases on redemption. Query: SELECT lifetime FROM PointBalance WHERE user_id = X.
{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”Why use the ledger pattern instead of a single balance column for loyalty points?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”A mutable balance field (UPDATE User SET points = points + 100) loses history: you cannot answer "when did this user earn these points?" or "why does this user have 5,000 points?" without a separate audit table. The ledger pattern stores every earn, redeem, and expiry as an immutable row — the balance is SUM(points) across all rows for the user. This makes the balance self-auditing: any discrepancy can be diagnosed by replaying the ledger. The materialized balance cache (PointBalance.available) is a performance optimization — always recomputable from the ledger. If the cache is corrupted, rebuild it: UPDATE PointBalance SET available = (SELECT SUM(points) FROM PointTransaction WHERE user_id = X AND (expires_at IS NULL OR expires_at > NOW())) WHERE user_id = X.”}},{“@type”:”Question”,”name”:”How does FOR UPDATE prevent simultaneous double-redemption of loyalty points?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”If two requests simultaneously try to redeem 500 points from a balance of 500, both read available=500, both pass the check, both INSERT redeem transactions, both decrement the balance. Result: balance goes to -500, double-spend. SELECT … FOR UPDATE on the PointBalance row acquires an exclusive lock before reading. The second transaction blocks at the SELECT until the first transaction commits. After the first commits (balance now 0), the second reads available=0 and fails the check — ValueError. This serializes concurrent redemptions for the same user without reducing throughput for different users. The lock is held for the duration of the transaction (< 10ms typically). An alternative is an optimistic lock: add a version column, CHECK (available – $redeemed >= 0) UPDATE WHERE version = $version_at_read.”}},{“@type”:”Question”,”name”:”How do you implement FIFO point expiry where oldest points are spent first?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”FIFO expiry minimizes points lost to expiration by ensuring the points nearest to expiry are spent before newer ones. Implementation: tag each earn transaction with its batch_id and expires_at. On redemption, instead of decrementing the global balance, consume specific earn transactions starting with those expiring soonest. Query: SELECT txn_id, points, expires_at FROM PointTransaction WHERE user_id = X AND txn_type = ‘earn’ AND NOT consumed ORDER BY expires_at ASC. Consume batches until the required points are met. Insert a redeem transaction for the consumed amount and mark earn transactions as consumed. This is more complex than the simple balance approach and most consumer loyalty programs skip it — only use FIFO if your business requires it (e.g., airline miles). For most applications, LIFO (newest first) or no tracking is simpler and acceptable.”}},{“@type”:”Question”,”name”:”How do you handle point refunds when an order is cancelled?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”When an order is cancelled, any points earned for that order should be reversed and any points redeemed in that order should be refunded. Earn reversal: INSERT INTO PointTransaction (user_id, points, txn_type, reference_id) VALUES (X, -earn_amount, ‘refund’, order_id). Check: ensure the original earn transaction exists (idempotency key: order_id + ‘_refund’). Redeem refund: INSERT with positive points (txn_type=’refund’) for the redeemed amount. Update PointBalance accordingly. The idempotency check prevents double-refunds if the refund webhook fires twice. Order of operations: process the earn refund and redeem refund in the same database transaction as the order cancellation — either both happen or neither does.”}},{“@type”:”Question”,”name”:”How do you calculate tier status using lifetime points without being affected by redemptions?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Tier thresholds (Silver = 5,000 lifetime, Gold = 25,000 lifetime) should be based on total points ever earned, not current balance. A user who earned 30,000 points and redeemed 28,000 is still Gold tier — they’ve spent their points, not lost their status. The PointBalance.lifetime column tracks this: it is incremented on every earn transaction and never decremented. Query: SELECT lifetime FROM PointBalance WHERE user_id = X. Tier calculation: if lifetime >= 25000 then Gold; elif lifetime >= 5000 then Silver; else Bronze. Reset annually: some programs reset lifetime points at year-end, requiring users to re-qualify. For rolling eligibility: tier = based on lifetime points earned in the last 12 calendar months. Track this with a WHERE created_at >= NOW() – interval ’12 months’ AND txn_type = ‘earn’ filter.”}}]}
Loyalty points and rewards program system design is discussed in Shopify system design interview questions.
Loyalty points and host rewards system design is covered in Airbnb system design interview preparation.
Loyalty points and ride rewards program design is discussed in Lyft system design interview guide.
See also: Scale AI Interview Guide 2026: Data Infrastructure, RLHF Pipelines, and ML Engineering