Keep orders and events consistent with a transactional outbox

Goal

You will verify SQLite transactions, fault injection, resending, and consumer deduplication through the state of the actual files.

Why it matters

A single success of a normal request does not guarantee boundary values or recovery from failure. In this lab you implement or test the contract of each function in small pieces and then connect it to a real run. It inspects results, exceptions, and stored state rather than just checking whether code exists or what the report says. Keep the code from the earlier steps as you move on to the next step.

Steps

1. In /root/work/idem-outbox-lab/service.py, make init_db(path) create orders(id TEXT PRIMARY KEY, amount INTEGER NOT NULL), outbox(id TEXT PRIMARY KEY, payload TEXT NOT NULL, sent INTEGER NOT NULL DEFAULT 0), consumed(id TEXT PRIMARY KEY), and totals(name TEXT PRIMARY KEY, amount INTEGER NOT NULL), and insert sales=0 into totals without duplicates. Do the first setup with the following commands.

mkdir -p /root/work/idem-outbox-lab
cp /opt/fixtures/practice_depth/idem-outbox-lab/* /root/work/idem-outbox-lab/
cd /root/work/idem-outbox-lab

1. In /root/work/idem-outbox-lab/service.py, make canonical(order_id, amount) accept only a non-empty string id and a positive int amount, and return {id, amount} as JSON with sort_keys=True, separators=(',', ':'), and ensure_ascii=False. A bool is not accepted as an amount, and invalid input is a ValueError.
2. In /root/work/idem-outbox-lab/service.py, make enqueue(path, order_id, amount, fault=lambda:None) commit, under BEGIN IMMEDIATE, the order insert → fault() → the outbox insert, and return True. A repeated request with the same id and amount returns False, and an amount conflict is a ValueError. If fault raises an exception, roll back both sides.
3. In /root/work/idem-outbox-lab/service.py, make pending(path, limit=10) return up to limit (id, payload) tuples with sent=0, in ascending id order. limit is an integer from 1 to 100 that is not a bool; a violation is a ValueError.
4. In /root/work/idem-outbox-lab/service.py, make acknowledge(path, event_id) return True if it changes a row with sent=0 to sent=1, and False if the row does not exist or was already sent.
5. In /root/work/idem-outbox-lab/service.py, make dispatch(path, publish, limit=10) pass each row of pending to publish(id, payload) and acknowledge after it succeeds. A publish exception is propagated and the unsent state is preserved. It returns the number processed successfully.
6. In /root/work/idem-outbox-lab/service.py, make consume(path, event_id, payload) validate the id and amount in the JSON and check that event_id matches. In the same transaction, record the id in consumed and add the amount to sales in totals. The first processing is True, a duplicate is False, and an invalid value is a ValueError.
7. In /root/work/idem-outbox-lab/service.py, make total(path) return the sales total. Make publish raise a ConnectionError right after it makes consume succeed, and then run dispatch again. Even if the event is delivered twice, the total must increase only once, and the outbox must end with sent=1.

Notes

• The packages are installed in the image. No internet connection or pip install is needed.
• Do the setup copy only once, at the very beginning. If you copy again, your working files are reset.
• Grading imports your service.py and runs it with different ids and a temporary store. Do not hard-code the results.
• Grading for each step runs within 45 seconds. Do not create infinite loops or real waiting.

Separate the order, the event, and the consumption record

In /root/work/idem-outbox-lab/service.py, make init_db(path) create orders(id TEXT PRIMARY KEY, amount INTEGER NOT NULL), outbox(id TEXT PRIMARY KEY, payload TEXT NOT NULL, sent INTEGER NOT NULL DEFAULT 0), consumed(id TEXT PRIMARY KEY), and totals(name TEXT PRIMARY KEY, amount INTEGER NOT NULL), and insert sales=0 into totals without duplicates. Do the first setup with the following commands.

mkdir -p /root/work/idem-outbox-lab
cp /opt/fixtures/practice_depth/idem-outbox-lab/* /root/work/idem-outbox-lab/
cd /root/work/idem-outbox-lab

Use CREATE IF NOT EXISTS and INSERT OR IGNORE so that the total is not reset on restart.

You can reproduce the grading directly with bash /opt/lab/checks/idem-outbox-lab/01-contract.sh. Save the file and run it again.

Build a validated event body

In /root/work/idem-outbox-lab/service.py, make canonical(order_id, amount) accept only a non-empty string id and a positive int amount, and return {id, amount} as JSON with sort_keys=True, separators=(',', ':'), and ensure_ascii=False. A bool is not accepted as an amount, and invalid input is a ValueError.

Normalize the whitespace and key order of the JSON string. Do not silently convert a float or a bool into an integer.

You can reproduce the grading directly with bash /opt/lab/checks/idem-outbox-lab/02-contract.sh. Save the file and run it again.

Roll back a failure between the two writes

In /root/work/idem-outbox-lab/service.py, make enqueue(path, order_id, amount, fault=lambda:None) commit, under BEGIN IMMEDIATE, the order insert → fault() → the outbox insert, and return True. A repeated request with the same id and amount returns False, and an amount conflict is a ValueError. If fault raises an exception, roll back both sides.

Do not swallow the exception inside the with connection block. If you commit before calling fault, the accident where only the order remains is reproduced.

You can reproduce the grading directly with bash /opt/lab/checks/idem-outbox-lab/03-contract.sh. Save the file and run it again.

Read unsent events in a limited batch

In /root/work/idem-outbox-lab/service.py, make pending(path, limit=10) return up to limit (id, payload) tuples with sent=0, in ascending id order. limit is an integer from 1 to 100 that is not a bool; a violation is a ValueError.

The batch limit keeps recovery from a failure from exhausting memory all at once. Do not omit ORDER BY.

You can reproduce the grading directly with bash /opt/lab/checks/idem-outbox-lab/04-contract.sh. Save the file and run it again.

Record the publish acknowledgment idempotently

In /root/work/idem-outbox-lab/service.py, make acknowledge(path, event_id) return True if it changes a row with sent=0 to sent=1, and False if the row does not exist or was already sent.

Distinguish the first acknowledgment from a duplicate one with the UPDATE condition and rowcount.

You can reproduce the grading directly with bash /opt/lab/checks/idem-outbox-lab/05-contract.sh. Save the file and run it again.

Mark it complete only after a successful publish

In /root/work/idem-outbox-lab/service.py, make dispatch(path, publish, limit=10) pass each row of pending to publish(id, payload) and acknowledge after it succeeds. A publish exception is propagated and the unsent state is preserved. It returns the number processed successfully.

If you put acknowledge before publish, you lose the event forever on a failure. An empty batch is 0.

You can reproduce the grading directly with bash /opt/lab/checks/idem-outbox-lab/06-contract.sh. Save the file and run it again.

Tie the consumption record and the effect together atomically

In /root/work/idem-outbox-lab/service.py, make consume(path, event_id, payload) validate the id and amount in the JSON and check that event_id matches. In the same transaction, record the id in consumed and add the amount to sales in totals. The first processing is True, a duplicate is False, and an invalid value is a ValueError.

Tie the duplicate check and the reflection of the effect together with BEGIN IMMEDIATE. If you commit only consumed first, the effect can be lost.

You can reproduce the grading directly with bash /opt/lab/checks/idem-outbox-lab/07-contract.sh. Save the file and run it again.

Reproduce two deliveries and one effect

In /root/work/idem-outbox-lab/service.py, make total(path) return the sales total. Make publish raise a ConnectionError right after it makes consume succeed, and then run dispatch again. Even if the event is delivered twice, the total must increase only once, and the outbox must end with sent=1.

The gap between a successful send and the acknowledgment record remains. Do not hide this gap; check that it is withstood by consumer deduplication.

You can reproduce the grading directly with bash /opt/lab/checks/idem-outbox-lab/08-contract.sh. Save the file and run it again.