Defending Against Duplicate Delivery With Idempotency Keys

Goal

You design an idempotency key, block duplicates with a DB constraint, make it safe under concurrent execution, and implement store-and-return to build a receiver where resending is safe.

Why it matters

In a world with asynchronous integration and retries, duplicates are the default, not the exception. But if you implement duplicate defense with the application's 조회 후 없으면 삽입 (check first, insert if absent), it is breached the moment two arrive at the same time. Because there is a gap between the check and the insert. A DB constraint removes that gap and cannot be breached even if the application has a bug. And one step further, if you store the original response and return it as is to duplicate requests, the sender can resend with peace of mind after a timeout. This is why payment APIs use an Idempotency-Key.

Steps

1. In /root/i/idem.db (sqlite), create an inbox_log table. The columns are msg_id, biz_key, status, response, and created_at, and msg_id must have a PRIMARY KEY or UNIQUE constraint.
2. Write /root/i/key.md. The following three things must be in the body.
   • What to use as the idempotency key of this interface, and why
   • Why it must not be keyed on order_no alone
   • The retention period of the idempotency history and its basis All three words 멱등키 (idempotency key), 보관 (retention), and 수정 (modification) must appear.
3. Create /root/i/apply.sh. It takes two arguments (DB파일 JSON파일, that is, the DB file and the JSON file):
   • If new, load it and print applied on the first line, exit code 0
   • If it already exists, do nothing and print duplicate on the first line, exit code 0 It must use an approach that relies on the constraint, not check-then-insert.
4. Load all the JSON files in /opt/lab/fixtures/eai/idem/messages/ with apply.sh.
   • The inbox_log row count must equal the number of distinct msg_id values
   • Save the msg_id values ignored as duplicates to /root/i/dup.txt in ascending order
5. Create /root/i/race.sh. It takes two arguments (DB파일 JSON파일, that is, the DB file and the JSON file), tries to load the same message 10 times concurrently, and prints rows=<해당 msg_id 의 행 수> (the number of rows for that msg_id) on the last line. The value must be 1. Save the result of running it to /root/i/race.txt. (Set PRAGMA busy_timeout to prepare for sqlite lock contention.)
6. Create /root/i/purge.sh. It takes two arguments (DB파일 보관일수, that is, the DB file and the retention days), deletes only the rows whose created_at is older than the retention days, and prints deleted=<건수> remain=<건수> (counts) on the last line.
7. Extend apply.sh to implement store-and-return.
   • When processing something new, store the response JSON in the response column, and print that response on the line after applied (the second line)
   • If it is a duplicate, print the stored original response as is on the line after duplicate (the second line) When you apply the same message twice, the 2nd line of the second output must equal the response of the first. Save the verification result to /root/i/replay.txt.
8. Write /root/i/report.md. List the four paths by which duplicates arise, one item each, and for each path also write the defense point. All four words 재시도 (retry), 큐 (queue), 수동 (manual), and 배치 (batch) must appear.

Notes

• Insert using the constraint: INSERT OR IGNORE INTO ..., then judge whether it took effect with changes()
• Concurrent execution: for i in $(seq 10); do ... & done; wait
• Lock waiting: PRAGMA busy_timeout=5000;
• Date comparison: created_at < datetime('now', '-30 days')
• Common mistake 1: checking with SELECT and then doing INSERT. It is breached under concurrent execution.
• Common mistake 2: making the cleanup batch "the oldest N first." When inflow surges, it deletes recent entries.
• Common mistake 3: running in parallel without busy_timeout and failing with database is locked.

Idempotency history table

In /root/i/idem.db (sqlite), create an inbox_log table. The columns are msg_id, biz_key, status, response, and created_at, and msg_id must have a PRIMARY KEY or UNIQUE constraint.

The last line of defense against duplicates is not the application but the DB constraint. Decide first which column to put the constraint on.

Idempotency key design document

Write /root/i/key.md. The following three things must be in the body.

• What to use as the idempotency key of this interface, and why
• Why it must not be keyed on order_no alone
• The retention period of the idempotency history and its basis All three words 멱등키 (idempotency key), 보관 (retention), and 수정 (modification) must appear.

A business key alone is often not enough. There can be modification messages for the same order, and the message number may cycle daily.

Loading script

Create /root/i/apply.sh. It takes two arguments (DB파일 JSON파일, that is, the DB file and the JSON file):

• If new, load it and print applied on the first line, exit code 0
• If it already exists, do nothing and print duplicate on the first line, exit code 0 It must use an approach that relies on the constraint, not check-then-insert.

If it is a key already processed, do nothing and report that. Check-then-insert is breached under concurrent execution, so use an approach that relies on the constraint.

Bulk load and duplicate count

Load all the JSON files in /opt/lab/fixtures/eai/idem/messages/ with apply.sh.

• The inbox_log row count must equal the number of distinct msg_id values
• Save the msg_id values ignored as duplicates to /root/i/dup.txt in ascending order

You must count new and duplicate separately from the load result. If you keep the list of duplicated keys, it is used later for root-cause analysis.

Concurrent execution defense

Create /root/i/race.sh. It takes two arguments (DB파일 JSON파일, that is, the DB file and the JSON file), tries to load the same message 10 times concurrently, and prints rows=<해당 msg_id 의 행 수> (the number of rows for that msg_id) on the last line. The value must be 1. Save the result of running it to /root/i/race.txt. (Set PRAGMA busy_timeout to prepare for sqlite lock contention.)

Even if you insert the same message many times in parallel, it must be one row. sqlite has frequent lock contention, so you must set a wait time.

Clean up by retention period

Create /root/i/purge.sh. It takes two arguments (DB파일 보관일수, that is, the DB file and the retention days), deletes only the rows whose created_at is older than the retention days, and prints deleted=<건수> remain=<건수> (counts) on the last line.

The moment you clean up, duplicate defense for that window disappears. You must delete only by period condition, and if you delete by count you can delete recent entries.

Store and return

Extend apply.sh to implement store-and-return.

• When processing something new, store the response JSON in the response column, and print that response on the line after applied (the second line)
• If it is a duplicate, print the stored original response as is on the line after duplicate (the second line) When you apply the same message twice, the 2nd line of the second output must equal the response of the first. Save the verification result to /root/i/replay.txt.

If you return the original response as is to a duplicate request, resending becomes completely safe from the sender's point of view. It is the approach payment APIs use.

Sort out the duplicate paths

Write /root/i/report.md. List the four paths by which duplicates arise, one item each, and for each path also write the defense point. All four words 재시도 (retry), 큐 (queue), 수동 (manual), and 배치 (batch) must appear.

Duplicates come by four paths. For each path, write together which point is the line of defense.