Your Order API Needs More Than an Idempotency Key

An order API can accept an Idempotency-Key header on every request and still create a second order for the same intended purchase. Generate a fresh key after a timeout, and a server that deduplicates by key has no way to connect the retry to the first attempt.

There is a separate server-side problem too. A lookup followed by an insert leaves a race if competing requests can both observe that the key is absent. The key needs an atomic relationship with the saved order.

That is the argument worth testing. How much does your retry guarantee actually cover?

This Python and SQLite lab commits an order, deliberately drops the response, and lets you retry it. You can inspect the database instead of guessing whether the request succeeded. It creates local order records only and never charges a wallet.

Download and start the API

Use Python 3.10 or later with sqlite3, and curl. The package has no third-party dependencies. The commands below use a macOS or Linux shell; on Windows, run the curl commands in WSL. The recorded checks used Python 3.13.3 on macOS.

Download the complete checkout retry lab, extract it and open a terminal inside the extracted directory. You should see checkout.py, test_checkout.py and README.txt.

python3 -B -m unittest -v
python3 checkout.py --db checkout-demo.sqlite --port 8765

The test suite creates its own temporary database and chooses a free local port. All seven included tests passed when rerun for this draft on Python 3.13.3. The second command leaves a server running on 127.0.0.1:8765. Keep that terminal open and use another terminal for the requests below. If port 8765 is occupied, choose another port and change the request URLs to match.

This is a local learning server. Python's documentation does not recommend http.server for production. The example also has no customer authentication or merchant isolation. Python HTTP server documentation.

Create one order, then repeat the request

The browser is allowed to choose a known SKU. The server's catalog owns the amount. In this example, field-kit costs the synthetic raw amount 100000000.

curl -i http://127.0.0.1:8765/orders \
  -H 'Content-Type: application/json' \
  -H 'Idempotency-Key: reader-attempt-0001' \
  -d '{"sku":"field-kit"}'

The first response has status 201. Its JSON contains a generated order id, the SKU, the saved amount and status set to open. Run the same command again. You should receive 200 with the same JSON body and the same order ID.

The different status codes are deliberate in this teaching API. 201 means this request created the record; 200 means it recovered an existing one. Other APIs have different replay contracts. Stripe, for example, documents response retention and parameter comparison in its own idempotency interface. Match the contract of the system you integrate. Stripe idempotent requests.

Put the key and the order in the same transaction

This is the table created by the downloaded server.

CREATE TABLE IF NOT EXISTS orders (
    id TEXT PRIMARY KEY,
    request_key TEXT NOT NULL UNIQUE,
    sku TEXT NOT NULL,
    amount TEXT NOT NULL
);

The unique constraint makes the key durable alongside the order. The function below, extracted from checkout.py, keeps one transaction around the lookup and insert. Run it through the supplied server, which provides the imports and initializes this table.

def create_order(db_path, key, body):
    if not re.fullmatch(r'[A-Za-z0-9_-]{8,128}', key or ''):
        return 400, {'error':'invalid_idempotency_key'}
    if not isinstance(body, dict) or set(body) != {'sku'} or body['sku'] not in CATALOG:
        return 400, {'error':'send_only_a_known_sku'}
    with closing(sqlite3.connect(db_path, timeout=10, isolation_level=None)) as db:
        db.execute('BEGIN IMMEDIATE')
        try:
            row = db.execute('SELECT id, sku, amount FROM orders WHERE request_key=?', (key,)).fetchone()
            if row:
                db.commit()
                if row[1] != body['sku']: return 409, {'error':'idempotency_conflict'}
                return 200, {'id':row[0], 'sku':row[1], 'amount':row[2], 'status':'open'}
            order_id, amount = str(uuid.uuid4()), CATALOG[body['sku']]
            db.execute('INSERT INTO orders VALUES (?,?,?,?)', (order_id,key,body['sku'],amount))
            db.commit()
            return 201, {'id':order_id, 'sku':body['sku'], 'amount':amount, 'status':'open'}
        except BaseException:
            db.rollback(); raise

BEGIN IMMEDIATE acquires the SQLite write transaction before the lookup. A competing writer has to wait or fail, rather than independently reading an absent key and proceeding with its own insert. The unique constraint is an additional guard; the server returns success only after commit. SQLite allows one writer at a time, so this approach trades write concurrency for a small, inspectable implementation. See SQLite transaction behavior.

If SQLite cannot obtain the lock within the configured timeout, the HTTP handler returns 503 with database_busy_retry_same_key. A caller should retry with backoff and keep the same purchase key. This example does not establish a production throughput limit.

There is a small but consequential choice in the replay branch. It returns the amount saved with the order. It does not calculate a new price from the catalog. A price change between attempts should not silently change the purchase already accepted under that key.

The saved purchase key connects the retry to the order that already exists.

Reproduce the uncertain result

Now use a fresh key and the lab's fault-injection header.

curl -i http://127.0.0.1:8765/orders \
  -H 'Content-Type: application/json' \
  -H 'Idempotency-Key: reader-attempt-0002' \
  -H 'X-Demo-Drop-Response: yes' \
  -d '{"sku":"field-kit"}'

Curl should report an empty response, typically exit code 52. The handler committed the order and then shut down the connection before sending response bytes. This is a controlled failure in the lab, not evidence of a real network incident.

Retry without the fault header, keeping the same key.

curl -i http://127.0.0.1:8765/orders \
  -H 'Content-Type: application/json' \
  -H 'Idempotency-Key: reader-attempt-0002' \
  -d '{"sku":"field-kit"}'

The response should be 200. The test suite checks that the disconnected call left one database row and the retry did not add another. If you repeat the fault-injected call after running the example before, the saved order will already exist; the database filename is intentionally persistent.

Now change only the key

After recovering reader-attempt-0002, send the same purchase under a new key.

curl -i http://127.0.0.1:8765/orders \
  -H 'Content-Type: application/json' \
  -H 'Idempotency-Key: reader-attempt-0003' \
  -d '{"sku":"field-kit"}'

On a fresh run, this returns 201 and a different order ID. Both requests have an idempotency header. The database still contains two separate purchases for those two keys.

Inspect the records from another terminal in the same directory. Stop the API with Ctrl+C when you are done.

python3 - <<'CHECK'
import sqlite3
with sqlite3.connect('checkout-demo.sqlite') as db:
    for row in db.execute(
        "SELECT request_key, id, sku, amount FROM orders ORDER BY request_key"
    ):
        print(row)
CHECK

If you followed all the commands once with a fresh database, expect three rows in total, one for each of 0001, 0002 and 0003. Repeating the commands against that same database reuses those rows.

The client needs to keep a key for the lifetime of one purchase attempt, including uncertain outcomes. If your business needs to recognize the same purchase across tabs, devices or newly generated keys, it also needs a separate business identity and uniqueness rule. Two identical baskets can be two legitimate purchases, so deduplicating by basket contents alone would make a different mistake.

Reject a changed purchase under the same key

Reuse reader-attempt-0001, but ask for the other SKU.

curl -i http://127.0.0.1:8765/orders \
  -H 'Content-Type: application/json' \
  -H 'Idempotency-Key: reader-attempt-0001' \
  -d '{"sku":"field-notes"}'

Expect 409 and idempotency_conflict. The existing purchase remains unchanged. Sending an extra amount field instead returns 400; the client cannot override the server price through this endpoint.

Where this guarantee ends

The test suite sends twelve HTTP requests from six client threads and checks that they produce one order ID. It also restarts the server against the same database. Those are useful checks of this local service, not a distributed exactly-once guarantee or a power-loss test.

For a payment checkout, the next boundary is the call to a payment provider or blockchain. A committed SQLite row cannot by itself prevent a second external charge. Define a stable identity for that external operation and a recovery path for an uncertain result before adding real payments.

This sample also lacks authentication and merchant isolation. In a multi-tenant API, derive the key's scope from the authenticated account and operation. Define which request fields must match and how long the key remains valid. Remove the demonstration disconnect header before adapting the pattern to a deployed service.

A practical review question is whether the integration can recover the original order after its response is lost. “We send an idempotency header” leaves that question unanswered.

For an API you maintain, would you return the original 201 on replay, or use 200 as this lab does? The important part is documenting the contract so the caller can handle a recovered purchase correctly.

Adapted from my original Python and SQLite tutorial. The runnable lab and illustrations are available there as well.

This draft was prepared by an AI coding agent from my existing tutorial and code. The seven included tests were rerun, and additional checks confirmed that a new key creates a second order and that replay keeps the saved price. This is a local teaching example, not evidence of a production payment deployment.