JunliYigou's points module launched in 2019 with an idempotency key: order_id plus change_type. That held up for seven years, because points only flowed two ways — added on purchase, deducted on returns — both tied to an order.
This year three new sources arrived: daily sign-ins, product reviews, and manual compensation from support staff. Problems followed. Sign-ins have no order ID, and compensation sometimes means two separate entries for one order with different reasons. The order_id slot in the old key had nothing to hold — and when someone lazily stuffed a fixed value in, records from different business types started colliding.
The New Three-Part Key
We restructured it as source + biz_id + change_type:
- source marks where the points came from: order, signin, review, or manual.
- biz_id is the unique identifier within each business: order ID for orders, date plus user for sign-ins, review ID for reviews, and the compensation ticket number for manual entries.
- change_type remains add or deduct.
The three segments together form a unique index. Now a double-tapped sign-in and an order's points plus its compensation each have their own identity — no false collisions, no missed duplicates.
The 2-Hour Migration
More than 400,000 historical records needed backfilling under the new key. We did it without downtime: a script running in batches of 2,000 rows sliced by id ranges, with a count check after each batch.
The messiest part was the dirty data — 300-plus rows where someone had stuffed in that fixed value. Those needed their sources determined manually from timestamps and remarks. The whole run took 2 hours, 40 minutes of which went to those 300 rows.
Before the new key went live, we kept the old key's index for 7 days in case of rollback. Only after 7 days with no old-format writes did we drop it.
Fixed Along the Way
- The compensation form now requires an operator and a reason. It used to be just an amount field — when something went wrong, there was no way to find out who had issued the points.
- The admin panel gained a "look up records by biz_id" box. When support asks "where did these points come from," we paste in the number instead of digging through three tables.
- Duplicate requests still return the first result, not an error. That principle hasn't changed since 2019.
July 10
After the switchover we watched the error logs for a while: 11 unique index conflicts, all from one user tapping the sign-in button repeatedly — all correctly rejected. Not a single duplicate write.
Later, operations asked to see "which source awards the most points." That needed no code change — just a GROUP BY on source. Impossible under the old structure, because a source column never existed.
