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.

See also: Shopify Interview Prep

See also: Airbnb Interview Prep

See also: Uber Interview Prep

See also: Scale AI Interview Guide 2026: Data Infrastructure, RLHF Pipelines, and ML Engineering