Deleting an idempotency key ends its guarantee

Goal

You distinguish completed records from in-progress records, and implement a retention period and batch cleanup.

Why it matters

When the table grew large, all the old idempotency keys were deleted. The records of requests that were still in the middle of a payment disappeared too, and the retries came in as new requests. The retention period of a completed response and the ownership of an in-progress job are not the same expiry policy. Cleanup is not a simple DELETE; it is a state transition that changes the scope of the guarantee.

Steps

1. In /root/work/idem-retention-lab/service.py, init_db(path) idempotently creates keys(id TEXT PRIMARY KEY,fingerprint TEXT NOT NULL,status TEXT NOT NULL,response TEXT,expires REAL NOT NULL).

Prepare it once at the beginning. It does not overwrite an existing file.

mkdir -p /root/work/idem-retention-lab
test -e /root/work/idem-retention-lab/service.py || cp /opt/fixtures/ten_labs/idem-retention-lab/service.py /root/work/idem-retention-lab/service.py
cd /root/work/idem-retention-lab

1. In /root/work/idem-retention-lab/service.py, reserve(path,key,digest,expires) inserts a key that does not exist as pending,response=NULL and returns True. An existing key is not changed, regardless of expiry, and it returns False.

2. In /root/work/idem-retention-lab/service.py, finish(path,key,digest,response) stores response as JSON and changes it to done, returning True, only for a row that has a matching key and fingerprint and is pending; otherwise it returns False.

3. In /root/work/idem-retention-lab/service.py, fetch(path,key,now) parses the response of a row that is done and has expires>now as JSON and returns it. Otherwise it returns None.

4. In /root/work/idem-retention-lab/service.py, expired(path,now,limit=10) checks that limit is an int from 1 to 100 (not bool). It returns up to limit ids that are done and have expires<=now, in ascending id order.

5. In /root/work/idem-retention-lab/service.py, purge(path,now,limit=10) picks candidates with the same limit validation, condition, and ordering as expired, deletes them in one transaction, and returns the list of deleted ids. It does not delete pending.

6. In /root/work/idem-retention-lab/service.py, counts(path) is {pending: count, done: count}. Even when there is no row in a state, the key must exist with 0.

7. In /root/work/idem-retention-lab/service.py, retention_cycle(path,key) reserves with expires=10,digest='v1' and completes with {receipt:1}. After purge at now=10, it reserves the same key anew with digest='v2',expires=20 and returns the resulting bool. The function assumes an empty DB.

Notes

• Do it in the existing lab-dev environment, without the internet or package installation.
• Each step runs within a 45-second grading budget. Do not add real sleep or network calls.
• Grading freshly imports the submitted module and checks it with independent inputs and a temporary DB. Implement the contract instead of returning expected values as constants.
• FastAPI official documentation · pytest official documentation · Python sqlite3
• Limitation: it does not guarantee an exactly-once effect for an unlimited period. An external system may resend an old request, so the retention period must be matched to the provider's retry policy. The lease and compensation policy that recovers abandoned pending rows is the responsibility of a separate lab, and this cleanup function does not guess and delete them.

Separate completion from expiry

In /root/work/idem-retention-lab/service.py, init_db(path) idempotently creates keys(id TEXT PRIMARY KEY,fingerprint TEXT NOT NULL,status TEXT NOT NULL,response TEXT,expires REAL NOT NULL).

Prepare it once at the beginning. It does not overwrite an existing file.

mkdir -p /root/work/idem-retention-lab
test -e /root/work/idem-retention-lab/service.py || cp /opt/fixtures/ten_labs/idem-retention-lab/service.py /root/work/idem-retention-lab/service.py
cd /root/work/idem-retention-lab

Do not judge that the work is finished just by looking at the expiry time.

After saving, check with bash /opt/lab/checks/idem-retention-lab/01-contract.sh.

Reserve an in-progress key

In /root/work/idem-retention-lab/service.py, reserve(path,key,digest,expires) inserts a key that does not exist as pending,response=NULL and returns True. An existing key is not changed, regardless of expiry, and it returns False.

Control record deletion and key reuse separately and explicitly.

After saving, check with bash /opt/lab/checks/idem-retention-lab/02-contract.sh.

Complete only the current request

In /root/work/idem-retention-lab/service.py, finish(path,key,digest,response) stores response as JSON and changes it to done, returning True, only for a row that has a matching key and fingerprint and is pending; otherwise it returns False.

Compare even the fingerprint so that a worker with a different body cannot overwrite a completed response.

After saving, check with bash /opt/lab/checks/idem-retention-lab/03-contract.sh.

Replay only a valid completed response

In /root/work/idem-retention-lab/service.py, fetch(path,key,now) parses the response of a row that is done and has expires>now as JSON and returns it. Otherwise it returns None.

Pin down the contract of no longer replaying at the boundary now==expires.

After saving, check with bash /opt/lab/checks/idem-retention-lab/04-contract.sh.

Limit the cleanup candidates

In /root/work/idem-retention-lab/service.py, expired(path,now,limit=10) checks that limit is an int from 1 to 100 (not bool). It returns up to limit ids that are done and have expires<=now, in ascending id order.

If you delete the whole table at once, the lock time gets long and it is easy to mix in in-progress rows.

After saving, check with bash /opt/lab/checks/idem-retention-lab/05-contract.sh.

Put selection and deletion in the same transaction

In /root/work/idem-retention-lab/service.py, purge(path,now,limit=10) picks candidates with the same limit validation, condition, and ordering as expired, deletes them in one transaction, and returns the list of deleted ids. It does not delete pending.

Do not create a gap in which the state changes between looking up the candidates and deleting them.

After saving, check with bash /opt/lab/checks/idem-retention-lab/06-contract.sh.

Actually count by state

In /root/work/idem-retention-lab/service.py, counts(path) is {pending: count, done: count}. Even when there is no row in a state, the key must exist with 0.

You must be able to observe that unfinished rows did not disappear after cleanup.

After saving, check with bash /opt/lab/checks/idem-retention-lab/07-contract.sh.

Confirm that the scope of the guarantee ends after deletion

In /root/work/idem-retention-lab/service.py, retention_cycle(path,key) reserves with expires=10,digest='v1' and completes with {receipt:1}. After purge at now=10, it reserves the same key anew with digest='v2',expires=20 and returns the resulting bool. The function assumes an empty DB.

Do not hide the limitation that after cleanup the key becomes a new request; reproduce it directly.

After saving, check with bash /opt/lab/checks/idem-retention-lab/08-contract.sh.