Cosmic Module

D

Qubits of DPK

April 11, 2026

Core DBMS

Domain Understanding

A Reward Points System (like Zomato Gold, Flipkart SuperCoins, or Amazon Pay Rewards) allows customers to earn points through purchases or actions and redeem them against future transactions. The schema challenge is fundamentally one of financial ledger design. Points are a currency. Every earn and burn event must be recorded with the same discipline as a bank transaction: append-only, immutable, with a running balance that can be recomputed from the ledger at any time.
The secondary challenge is that points often have expiry — points earned today may expire in 12 months if not redeemed. This means the system must track not just how many points a customer has in total, but when each batch was earned, when each batch expires, and how many of each batch have already been consumed. This is the point batch problem and it completely shapes the schema.
The third challenge is redemption ordering. When a customer redeems 500 points, which batch do you deduct from? FIFO (use the oldest points first, closest to expiry) is the fair approach and the industry standard, but it requires the schema to track per-batch balances.
The system is both write-heavy (every order generates an earn transaction) and read-heavy (every checkout page must show the customer's current redeemable balance).

Entity Map

javascript
QUBITS OF DPK
1customers ──< point_accounts             ← the customer's points wallet
2point_accounts ──< point_transactions       ← append-only ledger: every earn and burn
3point_transactions ──< point_batches         ← each earn creates a batch with expiry
4point_batches ──< batch_redemptions         ← how each batch has been consumed
5orders ──< point_transactions               ← order that triggered the earn/redeem
6reward_rules ──< point_transactions           ← which rule triggered this earn

Full Schema with Comments

sql
QUBITS OF DPK
1CREATE TABLE customers (
2  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
3  name       VARCHAR(255) NOT NULL,
4  email      VARCHAR(255) NOT NULL UNIQUE,
5  phone      CHAR(10) UNIQUE,
6  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
7  deleted_at DATETIME NULL
8);
9
10-- The customer's points wallet
11-- balance_points is a denormalized running total
12-- It is updated on every earn/redeem transaction
13-- The SOURCE OF TRUTH is the ledger (point_transactions)
14-- balance_points is a read optimisation only
15CREATE TABLE point_accounts (
16  id               BIGINT AUTO_INCREMENT PRIMARY KEY,
17  customer_id      BIGINT NOT NULL UNIQUE,
18  -- One account per customer: UNIQUE enforces this at DB level
19  balance_points   INT NOT NULL DEFAULT 0,
20  -- Current redeemable balance. Must be >= 0 at all times.
21  -- Denormalized: recomputable by summing point_transactions
22  lifetime_earned  INT NOT NULL DEFAULT 0,
23  -- Total points ever earned (never decremented)
24  -- Useful for tier calculations (Gold/Silver/Platinum)
25  lifetime_redeemed INT NOT NULL DEFAULT 0,
26  -- Total points ever spent
27  updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
28  FOREIGN KEY (customer_id) REFERENCES customers(id)
29);
30
31-- Rules that define how points are earned
32-- Makes the earn logic configurable without code changes
33CREATE TABLE reward_rules (
34  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
35  name            VARCHAR(255) NOT NULL,
36  -- 'Standard Order Earn', 'First Order Bonus', 'Birthday Bonus', 'Referral'
37  trigger_type    ENUM('ORDER_PLACED','SIGNUP','BIRTHDAY','REFERRAL','MANUAL') NOT NULL,
38  points_per_unit DECIMAL(8,4) NOT NULL,
39  -- For ORDER_PLACED: points earned per rupee spent (e.g. 1.0000 = 1 point per ₹1)
40  -- For SIGNUP: fixed bonus (e.g. 100.0000 = 100 points flat)
41  unit_type       ENUM('PER_RUPEE','FLAT','PERCENTAGE') NOT NULL,
42  min_order_value DECIMAL(10,2) NULL,
43  -- NULL = no minimum. NOT NULL = rule only applies above this order value.
44  max_points_per_event INT NULL,
45  -- Cap on points per transaction. NULL = no cap.
46  valid_from      DATE NOT NULL,
47  valid_to        DATE NULL,
48  -- NULL = rule is permanently active
49  is_active       BOOLEAN NOT NULL DEFAULT TRUE,
50  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
51);
52
53-- The immutable financial ledger of all point movements
54-- NEVER update or delete rows. Every adjustment is a new row.
55CREATE TABLE point_transactions (
56  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
57  account_id      BIGINT NOT NULL,
58  transaction_type ENUM('EARN','REDEEM','EXPIRE','ADJUST','REFUND_EARN','REFUND_REDEEM') NOT NULL,
59  -- EARN: points added (purchase, signup, referral)
60  -- REDEEM: points spent (used at checkout)
61  -- EXPIRE: points removed because they passed expiry date
62  -- ADJUST: manual correction by support team
63  -- REFUND_EARN: points removed when order is returned (reversal of EARN)
64  -- REFUND_REDEEM: points restored when redeemed order is returned
65  points          INT NOT NULL,
66  -- Positive for EARN/REFUND_REDEEM/ADJUST-credit
67  -- Negative for REDEEM/EXPIRE/REFUND_EARN/ADJUST-debit
68  -- The sign encodes the direction: this is ledger accounting
69  reference_type  ENUM('ORDER','SIGNUP','BIRTHDAY','REFERRAL','MANUAL','SYSTEM') NOT NULL,
70  reference_id    BIGINT NULL,
71  -- The ID of the triggering entity (order_id for ORDER, null for BIRTHDAY)
72  rule_id         BIGINT NULL,
73  -- Which reward_rule triggered this earn. NULL for REDEEM/EXPIRE/ADJUST.
74  description     VARCHAR(500),
75  -- Human-readable: 'Earned for order #1234', 'Redeemed at checkout'
76  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
77  -- No updated_at: this row is immutable once written
78  FOREIGN KEY (account_id) REFERENCES point_accounts(id),
79  FOREIGN KEY (rule_id)    REFERENCES reward_rules(id)
80  -- No FK on reference_id: it can point to different tables based on reference_type
81  -- Polymorphic reference handled at application level
82);
83
84-- Each EARN transaction creates a point batch with its own expiry
85-- Points are consumed FIFO: oldest batch (nearest expiry) is used first
86CREATE TABLE point_batches (
87  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
88  transaction_id  BIGINT NOT NULL UNIQUE,
89  -- Each EARN transaction generates exactly one batch
90  account_id      BIGINT NOT NULL,
91  original_points INT NOT NULL,
92  -- How many points were in this batch when created
93  remaining_points INT NOT NULL,
94  -- How many are left (decremented on each redemption against this batch)
95  expires_at      DATETIME NOT NULL,
96  -- When these points expire
97  status          ENUM('ACTIVE','PARTIALLY_USED','EXHAUSTED','EXPIRED') NOT NULL DEFAULT 'ACTIVE',
98  FOREIGN KEY (transaction_id) REFERENCES point_transactions(id),
99  FOREIGN KEY (account_id)     REFERENCES point_accounts(id)
100);
101
102-- Records exactly which batch(es) were drawn from for each REDEEM transaction
103-- A single redemption of 500 points might consume 200 from batch A + 300 from batch B
104CREATE TABLE batch_redemptions (
105  id               BIGINT AUTO_INCREMENT PRIMARY KEY,
106  redeem_txn_id    BIGINT NOT NULL,
107  -- The REDEEM transaction this draw-down belongs to
108  batch_id         BIGINT NOT NULL,
109  -- The batch that was drawn from
110  points_consumed  INT NOT NULL,
111  -- How many points from this specific batch were consumed
112  consumed_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
113  FOREIGN KEY (redeem_txn_id) REFERENCES point_transactions(id),
114  FOREIGN KEY (batch_id)      REFERENCES point_batches(id)
115);
116
117-- Indexes for the critical read paths
118CREATE INDEX idx_point_txns_account     ON point_transactions(account_id);
119-- Transaction history for a customer's account
120CREATE INDEX idx_point_txns_type        ON point_transactions(transaction_type, created_at);
121-- Analytics: total earns or redeems in a date range
122CREATE INDEX idx_batches_account_status ON point_batches(account_id, status, expires_at);
123-- FIFO redemption query: find ACTIVE batches ordered by expires_at ASC
124CREATE INDEX idx_batches_expires        ON point_batches(expires_at, status);
125-- Expiry job: find all ACTIVE batches that have crossed their expiry date

Key Design Decisions

The FIFO Redemption Algorithm

When a customer redeems 500 points, the system must consume from the oldest batches first (FIFO) to be fair — use the points closest to expiry before using newer ones. Here is how the schema supports this:
sql
QUBITS OF DPK
1-- Step 1: Find ACTIVE batches for this account, oldest expiry first
2SELECT id, remaining_points, expires_at
3FROM point_batches
4WHERE account_id = :account_id
5  AND status IN ('ACTIVE', 'PARTIALLY_USED')
6  AND expires_at > NOW()
7ORDER BY expires_at ASC;  -- FIFO: oldest expiry consumed first
8
9-- Step 2: Consume from batches until 500 points are covered
10-- e.g. Batch A has 200 remaining, Batch B has 400 remaining
11-- Take all 200 from Batch A, then 300 from Batch B = 500 total
12
13-- Step 3: For each batch consumed, INSERT a batch_redemptions row:
14INSERT INTO batch_redemptions (redeem_txn_id, batch_id, points_consumed)
15VALUES (:redeem_txn_id, :batch_a_id, 200);
16
17INSERT INTO batch_redemptions (redeem_txn_id, batch_id, points_consumed)
18VALUES (:redeem_txn_id, :batch_b_id, 300);
19
20-- Step 4: Update remaining_points on each batch:
21UPDATE point_batches SET remaining_points = 0, status = 'EXHAUSTED' WHERE id = :batch_a_id;
22UPDATE point_batches SET remaining_points = 100, status = 'PARTIALLY_USED' WHERE id = :batch_b_id;
23
24-- All of the above must be inside a single TRANSACTION
25-- If any step fails, the entire redemption rolls back
The idx_batches_account_status composite index on (account_id, status, expires_at) is what makes this query fast. Without it, finding redeemable batches would full-scan the entire point_batches table.

Points Expiry Background Job

A scheduled job runs daily (or hourly for precision) to expire points that have passed their expires_at:
sql
QUBITS OF DPK
1-- Find all batches that have expired but are not yet marked
2SELECT id, account_id, remaining_points
3FROM point_batches
4WHERE expires_at <= NOW()
5  AND status IN ('ACTIVE', 'PARTIALLY_USED');
6
7-- For each expired batch:
8-- 1. INSERT an EXPIRE transaction into point_transactions
9-- 2. UPDATE batch status to 'EXPIRED'
10-- 3. Decrement point_accounts.balance_points
11-- All inside a single TRANSACTION per batch
The idx_batches_expires index on (expires_at, status) is specifically for this job. Without it, the job scans the entire table every run.

Indexing Strategy

Common Traps to Avoid

  • Storing balance as the only truth — If balance_points is the only record, you cannot audit how the balance was reached, you cannot reverse individual transactions, and you cannot answer "how many points did this customer earn in Q3?". The ledger is the truth; balance_points is just a cached total.
  • No batch tracking for expiry — Without batches, you know total balance but not which points expire when. You cannot implement FIFO, you cannot warn customers "200 points expire next week", and you cannot run accurate expiry jobs.
  • Updating existing transaction rows — Transactions are financial records. Updating them destroys the audit trail. Every correction is a new compensating transaction (ADJUST type).
  • No reward_rules table — Hardcoding earn rates in application code means every promotional rate change requires a code deployment. A configurable rules table gives marketing teams control without engineering involvement.
  • Not wrapping redemption in a transaction — Decrementing batch remainders and updating balance_points must be atomic. A failure between these steps leaves the system in an inconsistent state where points were drawn from batches but the account balance is wrong.

Interview Q&A

Q: Why model points as a ledger instead of just a balance column?
Because points are a currency. A balance column answers "how many points do you have?" but cannot answer "how did you get them?", "which can I redeem?", "which expire next week?", or "what happened on this disputed transaction?". The append-only ledger provides a complete, auditable history from which the balance can always be recomputed. balance_points is just a cached sum for performance.
Q: How do you handle point expiry?
Earn transactions create point batches with individual expiry timestamps. A scheduled job runs periodically, queries batches where expires_at <= NOW() AND status IN ('ACTIVE', 'PARTIALLY_USED'), inserts EXPIRE transactions into the ledger for the remaining points, and updates the batch status and account balance. All inside individual transactions per batch for atomicity.
Q: What is FIFO redemption and why is it important?
FIFO means consuming points from the oldest batches first — using points closest to expiry before newer ones. This is fairest to the customer because it minimises unintentional expiry loss. The schema supports FIFO by ordering the batch query by expires_at ASC during redemption.
Q: How do you handle the case where a customer returns an order and wants their redeemed points back?
Insert a REFUND_REDEEM transaction with +N points (restoring the redeemed amount) and a REFUND_EARN transaction with -N points (reversing the earned points from the original purchase). Recreate the batch that was exhausted by the redemption, or add back to the partially-consumed batch. All of this is new ledger entries — no existing rows are modified.
Q: How do you prevent a race condition where a customer redeems points twice simultaneously?
Use SELECT FOR UPDATE on the point_accounts row at the start of the redemption transaction. This acquires an exclusive row lock, blocking any concurrent redemption for the same account until the first transaction commits. Combined with a CHECK (balance_points >= 0) constraint or an application-level guard, double-spend is prevented.

ANKI Cards

Q: Why is an append-only ledger the correct model for a reward points system instead of a single balance column?
A: A balance column only tells you the current state. A ledger records every movement, enabling full auditability, dispute resolution, per-transaction reversal, expiry tracking per earn batch, and balance recomputation from history. balance_points is a performance optimisation on top of the ledger, not a replacement for it.
Q: What is the FIFO redemption problem in reward points, and how does the schema solve it?
A: When a customer redeems points, the fairest approach is to consume from the batch nearest to expiry first (FIFO), minimising accidental expiry loss. The schema solves this with point_batches, each carrying expires_at and remaining_points. The redemption algorithm queries batches ORDER BY expires_at ASC and draws them down in order, recording each draw in batch_redemptions.
Q: In a reward points schema, what does a signed points column on point_transactions enable?
A: A single column with positive values for credits (EARN, REFUND_REDEEM) and negative values for debits (REDEEM, EXPIRE, REFUND_EARN) makes the ledger self-balancing. SELECT SUM(points) FROM point_transactions WHERE account_id = X gives the exact current balance. It mirrors the debit/credit model in financial accounting.