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

The article introduces the need for a pickup code and order write‑off mechanism in an O2O e‑commerce platform, aiming to let users collect orders in‑store or have them delivered after payment.

For small‑scale shops a simple single‑table design can be used, sharing the order table or an extension table, with the rule that unredeemed numeric codes must be unique while redeemed ones may be reused.

Generation logic is illustrated with pseudo‑code that repeatedly obtains a random code, checks its uniqueness via SQL, and updates the order record; a note warns that the check and update are not atomic and recommends a distributed lock.

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 exists, retry
   if (count > 0) { continue; }
   // step4: write code to order
   UPDATE order_main SET code = ${code}, qr_code = ${qrCode}, write_off_status = 0 WHERE order_no = ${orderNo};
}

When scaling to a SaaS platform, the design must handle sharding across multiple databases and tables. The article discusses three key concerns: massive storage via sharding, robust API design with retries and fault tolerance, and configurable code generation for different tenants.

Two uniqueness strategies are compared: store‑unique (risky due to possible cross‑store collisions) and global‑unique (safer but requiring a global uniqueness check). To achieve global uniqueness efficiently, the 8‑digit code is split into a random segment and a shard‑location segment, allowing a single SQL query per shard.

Detailed steps for the global‑unique scheme are provided, including mapping two shard‑position digits to a 4‑database‑4‑table layout, with two possible mapping methods (direct digit mapping or linear index mapping).

The article then presents three real‑world problems and their solutions:

Random code collisions: use exponential back‑off retries, batch generate codes, filter by distributed lock and existing codes, then insert with a lock (e.g., Redis).

// step1: get shard suffix
String suffix = getCodeSuffix(userCode, tenantId);
// step2: batch generate random codes with exponential back‑off
for (int i = 1; i <= 5; i++) {
   List
tempCodes = getRandomCodes(2 << i);
   filterDistributeLock(tempCodes);
   filterExistsCodes(tempCodes);
   return tempCodes;
}
// step3: lock and insert
boolean hasLock = isLocked(code);
try { insert(object); } finally { unlock(); }
// step4: generate QR code later

Dynamic data‑source routing with ShardingSphere‑JDBC: configure hint‑based sharding and programmatically set database and table sharding values based on tenant ID and pickup code.

// ShardingSphere‑JDBC configuration (jdbc‑sharding.yaml)
shardingRule:
  tables:
    order_code:
      actualDataNodes: DS00->${0..3}.order_pick_up_0->${0..3}
      databaseStrategy:
        hint:
          algorithmClassName: com.xxx.xxxxx.xxx.service.impl.DbHintShardingAlgorithm
      tableStrategy:
        hint:
          algorithmClassName: com.xxx.xxxxx.xxx.service.impl.DbHintShardingAlgorithm

// Java usage
try (HintManager hintManager = HintManager.getInstance()) {
    hintManager.addDatabaseShardingValue("order_code", DbHintShardingAlgorithm.calDbShardingValue(tenantId, code));
    hintManager.addTableShardingValue("order_code", DbHintShardingAlgorithm.calTabShardingValue(tenantId, code));
    Object result = xxxMapper.selectOne(queryDTO);
}

Improving extensibility: treat tenantId as a configurable factor, allowing per‑tenant customization of code length, arrangement, and shard mapping via strategy patterns.

In summary, the article shares a practical, SaaS‑ready approach to generating and managing pickup codes, covering simple and sharded implementations, uniqueness strategies, concurrency handling, and extensible configuration.