Design and Implementation of Pickup Code Generation and Write‑off Logic for SaaS E‑commerce Platforms

With the rapid expansion of O2O e‑commerce services, the need for a reliable pickup code generation and write‑off mechanism becomes critical for improving user experience after payment.

In small‑scale scenarios, a single table shared with orders or an extension table can store the code, ensuring that unredeemed numeric codes remain unique while allowing reuse of redeemed ones.

Simple implementation pseudocode:

for (;;) {
   // step1: get random code
   String code = this.getRandomCode();
   // step2: check uniqueness
   SELECT COUNT(1) FROM order_main WHERE code = ${code} AND write_off_status = 0;
   // step3: if duplicate, continue
   if (count > 0) { continue; }
   // step4: insert code
   UPDATE order_main SET code = ${code}, qr_code = ${qrCode}, write_off_status = 0 WHERE order_no = ${orderNo};
}

Note: Steps 2 and 4 are not atomic; a distributed lock (e.g., Redis) should be used in production.

When scaling to a SaaS platform with massive order volumes, the design must adopt sharding. The pickup‑code table should follow the same sharding strategy as the order table, using factors such as open_id or member_id to route queries.

Two global‑uniqueness approaches are discussed:

Store the 8‑digit code as random part + sharding hint , mapping the last two digits to a specific database and table among 4 databases and 4 tables (16 shards).

Encode the sharding hint as a one‑dimensional index (00‑15) to support up to 99 shards.

By ensuring the 6‑digit random segment is unique within a single shard, the system can guarantee overall uniqueness while keeping SQL queries simple.

Typical problems and solutions:

Problem 1: Random code collisions. Solution: exponential back‑off retries and batch generation of candidate codes.

Problem 2: Dynamic data‑source routing with ShardingSphere‑JDBC. Solution: use hint‑based sharding and programmatic hint manager to set database/table based on tenant ID and code.

Problem 3: Extensibility for different tenants. Solution: expose configurable parameters (code length, mapping strategy) and apply strategy pattern.

In summary, the article shares a practical, SaaS‑ready design for pickup code generation that balances simplicity, scalability, and reliability, and encourages further experimentation and adaptation to various business scenarios.