Low-Level Design: Loyalty and Rewards System — Points, Tiers, and Redemption

Core Entities

Member: member_id, user_id, program_id, tier (BRONZE, SILVER, GOLD, PLATINUM), points_balance, lifetime_points (never decrements — used for tier calculation), enrolled_at, tier_expires_at. LoyaltyProgram: program_id, merchant_id, name, points_currency_name (e.g., “Miles”, “Stars”), earning_rules (JSON: earn X points per $1 spent, earn Y points for specific actions), tier_thresholds (JSON: SILVER at 1000 lifetime points, GOLD at 5000), redemption_rules (JSON: 100 points = $1 discount). Transaction: transaction_id, member_id, program_id, type (EARN, REDEEM, EXPIRE, ADJUST, BONUS), points (positive for earn, negative for redeem/expire), reference_id (order_id, promotion_id), balance_after, created_at, expires_at (for earned points), description. Reward: reward_id, program_id, name, points_cost, reward_type (DISCOUNT, FREE_ITEM, UPGRADE, EXPERIENCE), is_active, stock (NULL = unlimited), valid_until. Redemption: redemption_id, member_id, reward_id, points_spent, status (PENDING, CONFIRMED, CANCELLED), order_id, created_at.

Points Ledger (Append-Only)

class LoyaltyService:
    def earn_points(self, member_id: int, order_id: str,
                    order_amount_cents: int) -> Transaction:
        with db.transaction():
            member = db.query(
                "SELECT * FROM members WHERE member_id = %s FOR UPDATE",
                member_id
            )
            program = db.get_program(member.program_id)

            # Calculate points to earn
            points = self._calculate_earn(program, order_amount_cents)

            # Apply any active bonus multipliers
            points = self._apply_multipliers(member, program, points)

            # Calculate expiry (e.g., 12 months from earn date)
            expires_at = datetime.utcnow() + timedelta(days=365)

            # Append to ledger (never update existing rows)
            txn = db.insert("transactions", {
                "member_id": member_id,
                "program_id": member.program_id,
                "type": "EARN",
                "points": points,
                "reference_id": order_id,
                "balance_after": member.points_balance + points,
                "expires_at": expires_at,
                "description": f"Earned for order {order_id}"
            })

            # Update denormalized balance (for fast reads)
            db.execute(
                "UPDATE members SET points_balance = points_balance + %s, "
                "lifetime_points = lifetime_points + %s WHERE member_id = %s",
                points, points, member_id
            )

            # Check for tier upgrade
            self._check_tier_upgrade(member, program)
            return txn

    def redeem_points(self, member_id: int, reward_id: int,
                      order_id: str) -> Redemption:
        with db.transaction():
            member = db.query(
                "SELECT * FROM members WHERE member_id = %s FOR UPDATE",
                member_id
            )
            reward = db.query(
                "SELECT * FROM rewards WHERE reward_id = %s FOR UPDATE",
                reward_id
            )
            if reward.stock is not None and reward.stock <= 0:
                raise RewardOutOfStock()
            if member.points_balance < reward.points_cost:
                raise InsufficientPoints()

            db.insert("transactions", {
                "member_id": member_id,
                "type": "REDEEM",
                "points": -reward.points_cost,
                "reference_id": str(reward_id),
                "balance_after": member.points_balance - reward.points_cost
            })
            db.execute(
                "UPDATE members SET points_balance = points_balance - %s "
                "WHERE member_id = %s",
                reward.points_cost, member_id
            )
            if reward.stock is not None:
                db.execute(
                    "UPDATE rewards SET stock = stock - 1 WHERE reward_id = %s",
                    reward_id
                )
            return db.insert("redemptions", {
                "member_id": member_id, "reward_id": reward_id,
                "points_spent": reward.points_cost, "status": "PENDING",
                "order_id": order_id
            })

Point Expiry

Points expire to drive engagement and manage liability (unspent points are a financial liability on the balance sheet). Expiry strategies: Rolling expiry: each earn batch expires 12 months after it was earned. Implemented with an expires_at column on each Transaction row. A daily background job processes expiries: SELECT member_id, SUM(points) FROM transactions WHERE type = ‘EARN’ AND expires_at < NOW() AND NOT EXISTS (SELECT 1 FROM transactions WHERE type='EXPIRE' AND reference_id = transaction_id). For each batch to expire: insert an EXPIRE transaction (negative points), update member.points_balance. Send a pre-expiry warning email 30 days before expiry. Activity-based extension: any earning activity (a purchase) resets the expiry clock for all outstanding points — encourages engagement. Implemented by updating expires_at on all un-expired EARN transactions when a new EARN transaction is inserted.

Tier Management

Tiers are based on lifetime_points (total points ever earned, never decremented — prevents gaming by earning and spending to maintain tier). Tier calculation: run after every earn event. Compare member.lifetime_points to program tier thresholds. On tier upgrade: update member.tier, set tier_expires_at = one year from now, send upgrade notification email with benefits summary. Tier downgrade (annual review): if lifetime_points earned in the last 12 months = GOLD, apply free_shipping benefit. Store tier benefits in a TierBenefit table (program_id, tier, benefit_type, benefit_value) and check at checkout time.

{“@context”:”https://schema.org”,”@type”:”FAQPage”,”mainEntity”:[{“@type”:”Question”,”name”:”Why is an append-only ledger better than updating a single balance field for points?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”An append-only ledger (like a bank statement) has several advantages over a mutable balance field: (1) Auditability: every point change has a record with reason, timestamp, and reference (which order earned it, which reward redeemed it). Required for dispute resolution ("I should have earned points for order X"). (2) No race conditions on the balance field: with a ledger, two concurrent earn transactions both insert rows; the balance is the sum of all rows. With a single balance field, two concurrent INcrements can race (read-modify-write). Solve with SELECT FOR UPDATE, but ledger + atomic increment is cleaner. (3) Time travel: compute the balance at any historical point by summing rows up to that timestamp. (4) Auditability for financial compliance: points are a financial liability; regulators may require immutable records.”}},{“@type”:”Question”,”name”:”How do you handle point expiry without degrading query performance?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Naive approach: scan all un-expired EARN transactions daily and expire them. Scales poorly with millions of members and transactions. Optimized approach: (1) Maintain an expiry_queue table with (member_id, batch_points, expires_at) — one row per earn batch, not per transaction. (2) A daily job queries: SELECT * FROM expiry_queue WHERE expires_at < NOW() AND NOT expired. Process in batches: insert EXPIRE transaction, update member balance, mark expiry_queue row as expired. (3) Index on expires_at for efficient batch selection. (4) Pre-expiry warning: a separate job queries expiry_queue WHERE expires_at BETWEEN NOW() AND NOW() + 30 days AND NOT warned — sends email and sets warned=true. (5) Activity-based extension: on any new EARN, UPDATE expiry_queue SET expires_at = NOW() + 365 days WHERE member_id = ? AND NOT expired — bulk extend all un-expired batches.”}},{“@type”:”Question”,”name”:”How do tier thresholds and lifetime points prevent gaming the loyalty system?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”If tiers were based on current balance (which decrements on redemption), members could earn points to reach a tier, enjoy the benefits, redeem all points, and maintain the tier indefinitely. Lifetime points (total ever earned, never decremented) prevent this: spending points does not affect tier qualification. Members must genuinely accumulate spend to reach and maintain a tier. Annual requalification: tiers expire after 12 months and members must re-earn the tier threshold in the qualification year. This is tracked with a separate points_earned_this_year counter (reset annually) rather than re-using lifetime_points. Tier downgrade is communicated 90 days in advance ("You are 200 points away from maintaining Gold status") to drive engagement.”}},{“@type”:”Question”,”name”:”How do you implement bonus point multipliers and promotions?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Multipliers are stored as active Promotion records: (program_id, multiplier, valid_from, valid_until, conditions). Conditions can be: tier-based (GOLD members get 2x), category-based (3x on electronics), merchant-based (5x at partner restaurants), or time-based (double points on weekends). At earn time: fetch all active promotions for the member and transaction context. Apply all applicable multipliers multiplicatively or additively (per program rules). Record the effective multiplier in the Transaction row for audit. Cap: most programs cap the total multiplier (e.g., max 5x regardless of stacked promotions). Referral bonuses: when a referred member makes their first purchase, insert BONUS transactions for both the referrer and referee. Referral is tracked via a Referral table (referrer_id, referred_id, bonus_triggered_at).”}},{“@type”:”Question”,”name”:”How do you handle rollback of points when an order is cancelled or returned?”,”acceptedAnswer”:{“@type”:”Answer”,”text”:”Points rollback on return: when an order is cancelled or returned (within the return window), reverse the earned points. Do NOT delete the EARN transaction — instead, insert a REVERSE transaction (negative points, type=REVERSE, reference_id=original_earn_transaction_id). This maintains the full audit trail. Guard: check if the points being reversed have already been redeemed. If partially redeemed: reverse only the unredeemed portion (member cannot have their balance go negative from a reversal). If fully redeemed: issue a REVERSE for 0 points and potentially put the member's account in a points debt state (handled per program policy — some forgive, some reduce future earnings). Partial returns: calculate the pro-rated points to reverse based on the returned item's value relative to the order total.”}}]}