Designing a Scalable Coupon System: From Distribution to Redemption

1 Scenario

Common e‑commerce promotion methods include coupons, group buying, bargaining, and referral programs.

1.1 Types of Coupons

Full‑reduction coupon

Direct‑reduction coupon

Discount coupon

1.2 Core Coupon Process

1.2.1 Issue

Coupons can be issued synchronously or asynchronously.

1.2.2 Receive

Who can receive? All users or specific users.

Receive limit per user.

Receive method: user‑initiated or automatic.

1.2.3 Use

Scope: product, merchant, or category.

Calculation: mutual exclusion, threshold checks, etc.

1.3 Requirement Breakdown

1.3.1 Merchant Side

Create coupons

Send coupons

1.3.2 User Side

Receive coupons

Place order

Use coupon

Pay

2 Service

2.1 Service Structure Design

2.2 Technical Challenges

Distributed transaction handling for coupon usage.

Preventing over‑issuance.

Bulk issuance to many users.

Enforcing usage conditions.

Preventing duplicate receipt.

3 Storage

3.1 Form Design

Coupon batch (coupon_batch) represents a template of a batch of coupons, containing most attributes.

Example: a batch of 1,000 coupons valid from 2022‑11‑11 00:00:00 to 2022‑11‑11 23:59:59, usable only for digital products, with a 100‑off‑50 rule.

Coupon is the actual entity bound to a user.

Rule defines usage conditions, e.g., threshold and amount.

Rule table (rule)

<code>{
  "threshold": 5.01, // usage threshold
  "amount": 5, // discount amount
  "use_range": 3, // 0‑all, 1‑merchant, 2‑category, 3‑product
  "commodity_id": 10,
  "receive_count": 1,
  "is_mutex": true,
  "receive_started_at": "2020-11-01 08:00:00",
  "receive_ended_at": "2020-11-06 08:00:00",
  "use_started_at": "2020-11-01 00:00:00",
  "use_ended_at": "2020-11-11 23:59:59"
}</code>

Coupon table (coupon)

<code>create table t_coupon (
  coupon_id int null comment '券ID，主键',
  user_id int null comment '用户ID',
  batch_id int null comment '批次ID',
  status int null comment '0‑未使用,1‑已使用,2‑已过期,3‑冻结',
  order_id varchar(255) null comment '对应订单ID',
  received_time datetime null comment '领取时间',
  validat_time datetime null comment '有效日期',
  used_time datetime null comment '使用时间'
);
</code>

3.2 Build Coupon

1. Create rule

<code>INSERT INTO rule (name, type, rule_content) VALUES ("满减规则", 0, '{
  threshold: 100,
  amount: 10,
  ...
}');
</code>

2. Create coupon batch

<code>INSERT INTO coupon_batch (coupon_name, rule_id, total_count) VALUES ("劳斯莱斯5元代金券", 1010, 10000);
</code>

3.3 Issue Coupons

How to issue coupons to many users?

Use asynchronous sending.

Message System

SMS, email – via third‑party APIs.

In‑site messages – insert records into a message table.

Message table (message)

<code>create table t_message (
  id int null comment '信息ID',
  send_id int null comment '发送者id',
  rec_id int null comment '接受者id',
  content varchar(255) null comment '站内信内容',
  is_read int null comment '是否已读',
  send_time datetime comment '发送时间'
);
</code>

For massive user bases, split the message table into

message

(user‑specific records) and

message_content

(shared content) to reduce insert volume.

Issue to 100k Users

Steps:

Upload a file of eligible user IDs and select the coupon batch.

Server generates messages with user_id and batch_id, pushes them to MQ.

Coupon service consumes messages and issues coupons.

<code># Remember to use a transaction!
INSERT INTO coupon (user_id, coupon_id, batch_id) VALUES (1001, 66889, 1111);
UPDATE coupon_batch SET total_count = total_count - 1, assign_count = assign_count + 1
WHERE batch_id = 1111 AND total_count > 0;
</code>

3.4 Receive Coupon

Validate remaining quantity:

<code>SELECT total_count FROM coupon_batch WHERE batch_id = 1111;</code>

Insert user‑coupon record and decrement batch count (transaction required).

<code># Note transaction!
INSERT INTO coupon (user_id, coupon_id, batch_id) VALUES (1001, 66889, 1111);
UPDATE coupon_batch SET total_count = total_count - 1, assign_count = assign_count + 1
WHERE batch_id = 1111 AND total_count > 0;
</code>

Prevent duplicate receipt using Redis sets:

<code># Check if user already received
SISMEMBER batch_id:1111:user_id 1001
# Add after successful receipt
SADD batch_id:1111:user_id 1001
</code>

3.5 Use Coupon

When to validate usage rules?

Confirm order

Submit order

Pay immediately

Validation checks:

Expiration

Applicable scope

Threshold met

Mutual exclusion

Query usable coupons:

<code>SELECT batch_id FROM coupon WHERE user_id = 1001 AND status = 0;
SELECT rule_id FROM coupon_batch WHERE batch_id = 1111;
SELECT name, type, rule_content FROM rule WHERE rule_id = 1010;
</code>

3.6 Consistency Across Services

Use TCC (Try‑Confirm‑Cancel) for distributed transactions:

Try: freeze resources (e.g., set coupon status to "frozen" when creating order).

Confirm: commit resources (set status to "used" after successful payment).

Cancel: release resources (set status back to "unused" on payment failure or timeout).

4 Scale

4.1 Expiring Coupon Reminder

Design a

notify_msg

table to store reminder tasks.

<code>create table t_notify_msg (
  id bigint auto_increment comment '自增主键',
  coupon_id bigint null comment '券id',
  user_id bigint null comment '用户id',
  notify_day varchar(255) null comment '需要执行通知的日期',
  notify_type int null comment '通知类型，1‑过期提醒',
  notif_time timestamp null comment '通知的时间',
  status int null comment '通知状态，0‑初始,1‑成功,2‑失败',
  constraint t_notify_msg_id_uindex unique (id)
);
alter table t_notify_msg add primary key (id);
</code>

Workflow:

Insert a reminder record when creating a coupon.

Use a composite unique index (user_id + batch_id + notify_day) to avoid duplicate notifications.

Index on

notif_time

for efficient daily scans.

After notification, delete the record via a scheduled job.

4.2 Database Index Optimization

4.3 Rate‑Limiting the Issue Interface

Front‑end

Disable the button briefly after a click.

Back‑end

Redirect excess requests to a busy page.