Loyalty Program System Low-Level Design

By /

Requirements

  • Earn points on purchases and specific actions (sign-up, referral, review)
  • Redeem points for discounts, rewards, or free items
  • Tiered membership (Bronze, Silver, Gold, Platinum) with benefits per tier
  • Points expiry: points expire if unused for 12 months
  • 100M members, 10M transactions/day, <100ms point balance lookup

Data Model

LoyaltyAccount(account_id UUID, user_id UUID, tier ENUM(BRONZE,SILVER,GOLD,PLATINUM),
               points_balance BIGINT, lifetime_points BIGINT,
               tier_qualifying_points INT,  -- points earned in current tier year
               tier_expiry_date DATE, created_at)

PointTransaction(txn_id UUID, account_id UUID, type ENUM(EARN,REDEEM,EXPIRE,ADJUST),
                 points BIGINT, reference_id UUID, reference_type VARCHAR,
                 description, expires_at DATE NULL, created_at)
-- All point changes go through transactions — never update balance directly

PointExpiry(expiry_id UUID, account_id UUID, txn_id UUID,
            points BIGINT, expires_at DATE, status ENUM(ACTIVE,EXPIRED,REDEEMED))

Earning Points

Earning rules are configurable: 1 point per $1 spent, 3x points for a specific product category, 500 bonus points for referral, etc. Rules stored in a EarnRule table and evaluated at transaction time. The earn logic:

  1. Order completed → publish OrderCompleted event to Kafka
  2. Loyalty service consumes event, evaluates earn rules
  3. Calculate points = base_earn_rate * amount + bonus_points for applicable rules
  4. INSERT PointTransaction (type=EARN, points=calculated)
  5. UPDATE LoyaltyAccount SET points_balance += points, lifetime_points += points, tier_qualifying_points += points
  6. Evaluate tier upgrade: if tier_qualifying_points >= tier threshold, upgrade tier

Redeeming Points

def redeem_points(account_id, points_to_redeem, order_id):
    BEGIN TRANSACTION
    SELECT points_balance FROM LoyaltyAccount
    WHERE account_id = :id FOR UPDATE

    if points_balance < points_to_redeem:
        ROLLBACK; raise InsufficientPoints

    # Deduct using FIFO from expiring points first (redeem oldest first)
    deduct_from_expiry_buckets(account_id, points_to_redeem)

    UPDATE LoyaltyAccount SET points_balance -= points_to_redeem
    INSERT PointTransaction (type=REDEEM, points=-points_to_redeem, reference_id=order_id)
    COMMIT
    return calculate_discount(points_to_redeem)

Redemption rate example: 100 points = $1 discount. Deduct from expiry buckets in FIFO order — use points expiring soonest first. This reduces the number of points that expire unused.

Points Expiry

Points earned in each transaction expire after 12 months of account inactivity or on a rolling 12-month basis per earn event. Implementation: PointExpiry table tracks each earn event’s expiry date. A nightly batch job runs:

SELECT account_id, SUM(points) as expiring_points
FROM PointExpiry
WHERE expires_at = CURRENT_DATE AND status = 'ACTIVE'
GROUP BY account_id
LIMIT 10000  -- process in batches

For each account: INSERT PointTransaction (type=EXPIRE, points=-expiring_points), UPDATE LoyaltyAccount balance, mark PointExpiry records as EXPIRED. Send email notification 30 days before expiry: “You have X points expiring on DATE.”

Tier Management

Tier qualification period: typically calendar year or rolling 12 months. Tier thresholds: Bronze=0, Silver=1000 QP (qualifying points), Gold=5000 QP, Platinum=10000 QP. Tier upgrade: immediate when threshold is crossed. Tier downgrade: at tier year end, re-evaluate based on prior-year QP. Member retains previous tier for a grace period (e.g., 3 months) after year end. Cache tier status in Redis for fast benefit lookups: key=loyalty_tier:{user_id}, TTL=1h.

Key Design Decisions

  • Double-entry bookkeeping via PointTransaction — never modify balance directly, full audit trail
  • SELECT FOR UPDATE on redemption — prevents concurrent over-redemption
  • PointExpiry table for FIFO redemption — use oldest points first, minimize waste
  • Async earn via Kafka — order completion never blocks on loyalty points calculation
  • Nightly batch for expiry — avoid real-time TTL complexity, predictable processing window


{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”How do you prevent race conditions when redeeming loyalty points?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Concurrent redemptions from the same account (two browser tabs, two devices) could both read balance=500, both attempt to redeem 400 points, and both succeed — resulting in -300 balance. Prevention: SELECT … FOR UPDATE on the LoyaltyAccount row acquires an exclusive lock. Only one redemption transaction can hold the lock at a time; the second waits, then re-reads the (already reduced) balance and fails if insufficient. Alternative: optimistic locking — store a version column. On redemption: UPDATE LoyaltyAccount SET points_balance -= :pts, version = version + 1 WHERE account_id = :id AND version = :expected_version AND points_balance >= :pts. If the update affects 0 rows (version changed or balance insufficient), retry. Optimistic locking is better for low-contention accounts; SELECT FOR UPDATE is better for high-contention VIP accounts. For distributed deployments without a single DB, use a Redis lock: SET loyalty_lock:{account_id} 1 NX EX 5 before starting redemption.”}},{“@type”:”Question”,”name”:”How does points expiry work with FIFO redemption?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Points earned at different times may have different expiry dates. FIFO (first-in, first-out) redemption: when redeeming points, use the points expiring soonest first. This minimizes waste — the user spends points that would otherwise expire. Implementation: PointExpiry table has one row per earn event with (account_id, points, expires_at, status). On redemption: SELECT FROM PointExpiry WHERE account_id = :id AND status = 'ACTIVE' ORDER BY expires_at ASC FOR UPDATE. Process in order: subtract from the oldest bucket first. Mark buckets as REDEEMED when fully consumed, partially update the remaining points in a bucket. Expiry batch job: runs nightly, selects buckets with expires_at = today and status = ACTIVE. Creates EXPIRE transactions and marks buckets as EXPIRED. Notification: 30 days before expiry, send email "Your X points expire on DATE. Use them now."”}},{“@type”:”Question”,”name”:”How does tier calculation and upgrade work in a loyalty program?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Tier qualification uses qualifying points (QP) — points earned during the tier evaluation period (typically a calendar year or rolling 12 months). Thresholds: Bronze=0, Silver=1,000 QP, Gold=5,000 QP, Platinum=10,000 QP. Tier upgrade: on each earn event, check if QP has crossed a tier threshold. If yes, immediately upgrade the tier and update LoyaltyAccount.tier. Tier downgrade: at the end of the tier year, reset QP and re-evaluate. If the user earned 800 QP (below Silver threshold of 1,000), they drop back to Bronze. Grace period: the user retains their previous tier for 3 months after the tier year ends (incentivizes continued spending). Tier benefits: cached in Redis key=loyalty_tier:{user_id} (TTL=1h). On tier change: invalidate the cache. Benefits include: earning multipliers (Gold = 2x points), priority service, free shipping, exclusive offers — stored in a TierBenefit table referenced by tier enum.”}},{“@type”:”Question”,”name”:”How do you design the point earning rule engine?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Earning rules are configurable and change frequently (promotions, seasonal multipliers, partner bonuses). A hardcoded rule engine requires deployment for every change; a rule engine with DB-driven rules is more flexible. EarnRule table: (rule_id, name, condition_type ENUM(PRODUCT_CATEGORY, ORDER_TOTAL, FIRST_PURCHASE, REFERRAL), condition_value, points_type ENUM(FIXED, MULTIPLIER), points_value, valid_from, valid_until, priority). On order complete: load all active rules. Evaluate each rule against the order: check condition (is the product in the specified category? Is order total >= threshold?). Apply the matching rule with highest priority. Sum all applicable bonuses. Base rate: 1 point per $1. Multiplier rule: if category=Electronics, multiply base by 3. Fixed rule: if first_purchase, add 500 bonus. Rules are cached in Redis (TTL=5min) — rule changes take effect within 5 minutes without deployment.”}},{“@type”:”Question”,”name”:”How do you handle the audit trail for loyalty points?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”The PointTransaction table is the audit trail — every point change (earn, redeem, expire, adjustment) is recorded as an immutable transaction. Never update the points_balance directly; always go through a transaction. This is double-entry bookkeeping for loyalty points. Benefits: (1) Full history — show the member every point transaction with reason and reference. (2) Debugging — if a member disputes their balance, replay all transactions to verify. (3) Corrections — if a bug caused incorrect point allocation, insert an ADJUST transaction with a negative value to correct it without modifying history. (4) Compliance — financial-adjacent systems require audit trails for regulatory review. The PointTransaction table should never be deleted from (archive old records instead of deleting). For performance: index on (account_id, created_at DESC) for efficient history lookups per account. Partition by month for large tables.”}}]}

Shopify system design covers loyalty programs and customer rewards. See common questions for Shopify interview: loyalty program and rewards system design.

Airbnb system design covers loyalty programs and host/guest rewards. Review patterns for Airbnb interview: loyalty and rewards system design.

Lyft system design covers loyalty programs and driver/rider rewards. See design patterns for Lyft interview: loyalty program and rewards system design.

Scroll to Top