# Loyalty Points Low-Level Design: Earn, Redeem, Expiry, and Ledger Pattern

Source: https://www.techinterview.org/post/3233468294/lld-loyalty-points/
Updated: 2026-04-18 · techinterview.org

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.

Loyalty points and rewards program system design is discussed in [Shopify system design interview questions](https://www.techinterview.org/post/3233459924/shopify-interview/).

Loyalty points and host rewards system design is covered in [Airbnb system design interview preparation](https://www.techinterview.org/post/3233460279/airbnb-interview-guide-2026-search-systems-trust-and-safety-and-full-stack-engineering/).

Loyalty points and ride rewards program design is discussed in [Lyft system design interview guide](https://www.techinterview.org/post/3233460306/lyft-interview-guide-2026-rideshare-engineering-real-time-dispatch-and-safety-systems/).

See also: [Scale AI Interview Guide 2026: Data Infrastructure, RLHF Pipelines, and ML Engineering](https://www.techinterview.org/post/3233460275/scale-ai-interview-guide-2026-data-infrastructure-rlhf-pipelines-and-ml-engineering/)

See also: [Stripe Interview Guide 2026: Process, Bug Bash Round, and Payment Systems](https://www.techinterview.org/post/3233460268/stripe-interview-guide-2026-process-bug-bash-round-and-payment-systems/)