The Double-Tap Problem — Idempotency Keys, and the Race I Shipped Anyway

A shop owner in Dhaka taps রেকর্ড করুন — record sale. ৳500 on credit.

The spinner starts. The network is 3G, or worse, and it’s the middle of the day. One second. Two. Nothing happens.

So she taps it again. Obviously she does. You would.

Now: how many sales did that create?

If the answer is “two,” you don’t have a bug. You have a wrong ledger. Her customer now owes ৳1,000 instead of ৳500. She will trust that number. She will show it to the customer. And when the customer says “no, I only bought ৳500 of rice,” she will lose the argument, the money, and — this is the part that ends the business — she will stop believing the app.

There is no product left after that. So POST /sales had better be idempotent, and it had better be genuinely idempotent, not idempotent-if-you-squint.

This is the story of building that, getting it about 90% right, and finding the missing 10% with a test I should have written months earlier.

---

The Contract

The rule went into the project’s root contract on day one, in the same breath as the money and timezone rules:

Idempotency: every mutating write (POST /sales, POST /payments, …) takes an Idempotency-Key. Clients generate a UUID per logical write. Same key + same body → replay; same key + different body → 422. No exceptions.

Two words there do a lot of work.

“Per logical write.” The client mints the UUID when the user taps the button — not when the HTTP request goes out. Every retry of that tap, whether it’s the user tapping again, the HTTP client auto-retrying, or the app resending after a reconnect, carries the same key. The key identifies the intent, not the attempt. Get this backwards — generate a fresh UUID per request — and you’ve built an elaborate no-op.

“Same key + different body → 422.” If you send me the same key with a different payload, I will not guess. That’s always a client bug: either you reused a UUID you shouldn’t have, or you mutated the payload between retries. Both are things you want to find out about loudly, in development, not silently in production where I quietly replay a response that doesn’t match what you asked for.

---

The Implementation

The table is a composite primary key, and which composite matters:

class IdempotencyKey(Base):
    __tablename__ = "idempotency_keys"

    shop_id: Mapped[UUID] = mapped_column(..., primary_key=True)   # ← tenant
    key: Mapped[str] = mapped_column(String(80), primary_key=True)  # ← the client's UUID
    response: Mapped[dict[str, Any]] = mapped_column(JSONB)
    status_code: Mapped[int] = mapped_column(Integer)
    request_hash: Mapped[str] = mapped_column(String(64))
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), index=True)

(shop_id, key), not key alone. Keys are scoped per tenant. Two different shops can’t collide, and — more importantly — a key generated by one shop can never be used to fish a response out of another shop’s data. (The table is also under Row-Level Security like every other business table, but I don’t want to lean on one defense.)

Hashing the request

request_hash is what powers the “same key + different body” check:

def _canonical_body(body: bytes) -> str:
    """Stable hash input — JSON re-serialised in sort-key order if possible."""
    if not body:
        return ""
    try:
        parsed = json.loads(body)
    except json.JSONDecodeError:
        return body.decode("utf-8", errors="replace")
    return json.dumps(parsed, sort_keys=True, separators=(",", ":"))


def _hash_request(method: str, path: str, body: bytes) -> str:
    payload = f"{method.upper()} {path}\n{_canonical_body(body)}"
    return hashlib.sha256(payload.encode()).hexdigest()

The canonicalisation is the important part, and it’s easy to skip. If you just sha256(raw_bytes), then these two are different requests:

{"amount": "500.00", "customer_id": "abc"}
{"customer_id": "abc", "amount": "500.00"}

Same data. Different key order. Different bytes. Different hash. So a client that serialises its JSON with a map (hello, Dart; hello, Go) and happens to iterate in a different order on the retry would get a 422 idempotency_mismatch for a request that is semantically identical to the one it just sent.

Re-serialising through json.loads → json.dumps(sort_keys=True) normalises key order and whitespace, so the hash tracks meaning, not bytes.

Replay

async def replay(self) -> dict[str, Any] | None:
    existing = await self.session.get(
        IdempotencyKey, {"shop_id": self.shop_id, "key": self.key}
    )
    if existing is None:
        return None
    if existing.request_hash != self.request_hash:
        raise errors.AppError(errors.IDEMPOTENCY_MISMATCH)      # 422 — client bug
    return {"status_code": existing.status_code, "response": existing.response}

Note that the stored status_code gets replayed too. A replayed sale creation returns 201, not 200. As far as the client is concerned, its request succeeded and created a sale — which is exactly the truth, just not on this particular attempt. Anything else forces the client to special-case “did I create this, or did I create this earlier,” and it doesn’t care about the difference. Nobody should have to.

And the deltas underneath

Idempotency stops the second write. But the first write still has to be correct under concurrency, and money is where that bites. customers.total_due is denormalised — it’s the number the shop owner actually looks at — so it must move atomically with the sale:

await session.execute(
    update(Customer)
    .where(Customer.id == payload.customer_id, Customer.shop_id == user.shop_id)
    .values(total_due=Customer.total_due + payload.amount)      # ← delta, in SQL
)

total_due = total_due + :amount, evaluated by Postgres. Never read-modify-write in Python:

# ❌ Two concurrent sales both read 500, both write 600. One sale vanishes.
customer.total_due = customer.total_due + payload.amount

Two concurrent sales of ৳100 against a ৳500 balance: both read 500, both compute 600, both write 600. Final balance ৳600. One sale — real money, owed by a real person — has evaporated. The SQL-side delta makes the read and the write a single atomic operation and the answer is ৳700.

---

Everything Above Is Correct. It Also Wasn’t Enough.

Here’s what with_idempotency looked like:

async with handle.session.begin():                   # TX 1
    await apply_tenant_scope(...)
    cached = await handle.replay()
if cached is not None:
    return cached["status_code"], cached["response"]

response = await do()                                # TX 2 — creates the Sale

async with handle.session.begin():                   # TX 3
    await apply_tenant_scope(...)
    await handle.store(status_code=status_code, response=response)
return status_code, response

Read it as a story. Have I seen this key? No? Then do the work. Now record the key.

Three sentences. Three transactions. And in the gap between the first and the third, there is nothing at all stopping a second request from reading the same “no” to the same question.

Walk the double-tap through it. Two requests, same Idempotency-Key, both in flight at once, because the first one hadn’t come back yet — which is the entire reason she tapped twice:

	Request A	Request B
TX1	key not found → proceed	key not found → proceed
TX2	creates Sale #1, total_due +500	creates Sale #2, total_due +500
TX3	INSERT key → wins	INSERT key → on_conflict_do_nothing

Two sales. total_due is ৳1,000. And the on_conflict_do_nothing on that key insert — which I had put there thinking I was being defensive — quietly swallowed the only evidence that anything had gone wrong.

The feature worked perfectly against sequential retries: tap, wait, timeout, tap again five seconds later. TX3 from the first attempt has committed by then, so the second attempt hits the replay path and everything is fine. That’s the case I had in my head when I wrote it. That’s the case I tested.

It did nothing at all against concurrent retries. Which is the case that actually happens, on the actual network, to the actual user, for the actual reason the feature exists.

I had built a mutex with no mutex in it.

---

Reproducing It

The reason this survived so long is embarrassing and instructive: every idempotency test I had was a unit test, and every one of them was sequential. Call once, call again, assert replay. Green. Of course it’s green — a single-threaded test can never produce the interleaving that breaks it.

So the first thing I wrote was not a fix. It was a test that fires two writes with the same key on two separate connections, concurrently:

results = await asyncio.gather(
    with_idempotency(handles[0], status_code=201, do=_make_sale_work(s1, ...)),
    with_idempotency(handles[1], status_code=201, do=_make_sale_work(s2, ...)),
)

n_sales = (await owner_session.execute(
    select(func.count()).select_from(Sale).where(Sale.customer_id == cust_a.id)
)).scalar_one()
assert n_sales == 1, f"expected exactly 1 sale, got {n_sales} (duplicate write)"

Two separate sessions is load-bearing. Share one session and SQLAlchemy serialises both calls onto a single connection, the race can’t happen, and you get a green test that proves nothing. The concurrency has to be real or you’re just writing fiction.

It failed on the first run, exactly as advertised:

E   AssertionError: expected exactly 1 sale, got 2 (duplicate write)

Seeing that fail was the best part of the week. A bug you can reproduce on demand is a bug that’s already dead.

---

The Fix: Let the Primary Key Be the Mutex

I already had a perfectly good mutex. It was sitting right there in the schema and I was actively working around it.

(shop_id, key) is a primary key. Postgres will not permit two rows with the same one. Two transactions racing to insert it don’t both win — the second one blocks until the first commits or rolls back, and then it either fails with a unique violation (first one committed) or succeeds (first one aborted). That is a mutex. That is exactly a mutex. I’d just been carefully preventing it from doing its job.

Two changes.

One: the key insert becomes a plain INSERT. No conflict swallowing.

# ❌ Before — silently tolerates the duplicate, letting the second write commit
stmt = pg_insert(IdempotencyKey).values(...).on_conflict_do_nothing(
    index_elements=["shop_id", "key"]
)

# ✅ After — a duplicate raises IntegrityError, and that's the point
stmt = insert(IdempotencyKey).values(...)

Two — and this is the whole thing — the write and the key insert commit in the same transaction.

try:
    async with session.begin():             # ONE transaction
        await apply_tenant_scope(session, handle.shop_id)
        response = await do()               # the Sale
        await handle.store(...)             # the key
except IntegrityError as exc:
    if not _is_duplicate_idempotency_key(exc):
        raise
    # Lost the race. Our transaction — Sale included — has rolled back,
    # so nothing was double-written. Replay whatever the winner stored.
    async with session.begin():
        await apply_tenant_scope(session, handle.shop_id)
        cached = await handle.replay()
    return cached["status_code"], cached["response"]

Now trace the double-tap again. Both requests run do() and create a Sale inside their own transaction. Both try to insert the key. Postgres blocks the second until the first resolves.

The first commits. The second gets a unique violation — and its entire transaction rolls back, taking its Sale and its total_due increment with it. It didn’t half-happen. It didn’t happen. The loser then re-reads the key and replays the winner’s response, so the client gets the same 201 and the same body it would have gotten from the winner.

One sale. One delta. Both callers get an answer. The database did the arbitration, because that is what databases are for.

1 passed

The cost of this fix is a constraint on the callers: do() must no longer open its own transaction, because with_idempotency owns it now. That meant touching five routers (sales, payments, payments_out, payables, accounting) to strip an async with session.begin(): out of each. It’s a smaller diff than it sounds, and the invariant it buys is worth a great deal more than five dedents.

I wrote it into the contract so nobody — including me, in six months, including any agent working in this repo — puts it back:

with_idempotency() owns the transaction — a route’s do() must not open its own. The write and the idempotency_keys INSERT commit atomically, so the (shop_id, key) PK serialises concurrent duplicates and the loser’s write rolls back. Never put on_conflict_do_nothing on that INSERT: it swallows the conflict and double-counts total_due.

---

What I’d Tell You

1. Idempotency is not a lookup. It’s a lock. “Check if the key exists, then do the work” is a TOCTOU race wearing a helpful disguise. If the check and the write don’t commit atomically, two requests will both pass the check. The check is not the mechanism — the constraint is.

2. ON CONFLICT DO NOTHING is not error handling. It’s error suppression. I put it there to be defensive, and what it actually did was convert my loudest possible signal — a unique violation, raised by the database, telling me two writes were racing — into total silence. If a conflict on that row means something has gone wrong, then let it raise.

3. Your uniqueness constraint is already a mutex. Use it. Don’t reach for Redis, an advisory lock, or a distributed-lock library. The row is unique or it isn’t, and Postgres will happily serialise the racers for you and roll the loser’s work back for free. The best concurrency primitive in your system is usually the one you already declared in your schema.

4. Sequential tests cannot find concurrency bugs. Every idempotency test I had passed, forever, while the feature was broken. They were all single-threaded, so they could not have failed. If you have a concurrency guarantee, you need at least one test that runs actually concurrently, on separate connections, or you have a guarantee you’ve never once checked.

5. Deltas in SQL, never in Python. total_due = total_due + :amount. Read-modify-write in application code is a lost update waiting for its moment, and when money is the thing being lost, that moment will find you.

6. Test the failure your users actually have. I tested the retry I imagined — tap, wait, tap again. Users produce the retry that comes from impatience, and impatience is concurrent. The gap between those two mental models was the entire bug.

---

The Point

Idempotency keys aren’t about deduplicating rows. They’re a promise: “this operation will happen exactly once, no matter how many times you ask.”

For a few months, mine was making that promise and — under precisely the conditions it was designed for — quietly not keeping it. It took writing a test that could actually fail to find out.

She’s still going to tap the button twice. That was never in question. The only question was whether I’d built something that could survive it.

---

Tags: #Idempotency #Postgres #FastAPI #DistributedSystems #Concurrency #APIDesign