Idempotency without an idempotency table: make the insert the lock

---

Here is the shape of the bug, and it is always the same shape.

A client posts a charge. The response is slow, a proxy gives up at 30 seconds, the client retries. Your handler runs twice. Somewhere in the middle sits a check that looks like `if not already_charged(order):`. That is a read and then a write, with nothing holding the gap between them. Both requests read "not charged". Both charge.

The usual fix copies the shape Stripe made familiar with its `Idempotency-Key` header: a table called `idempotency_keys`, and a lot of care. We did something narrower, and the narrowness is the point: no lock, no bespoke index, and no schema written specially for this. **The insert is the lock.**

Every guarded action claims a slot before it does anything. A claim is a row, and the row's *name* is a deterministic function of what makes the action unique:

digest = sha1(canonical_identity_of_this_action).hexdigest()[:24]
name   = f"claim_{digest}"

Two identical invocations compute the same digest, so they aim at the same address. What goes into `canonical_identity_of_this_action` is the interesting decision, and it should be everything that makes the action unique and nothing that changes between retries — which is where most implementations go wrong, usually by including a per-attempt request id. One wins. The other gets a conflict, and a conflict means the action does not run.

The hash is not for secrecy.

Record names on this platform are constrained to `[a-z0-9_]`, and an `action_id` or a caller-supplied key can contain anything at all. Hashing is how an arbitrary key becomes a legal name without anyone having to sanitise it by hand.

When no explicit key is supplied the gate falls back to `action_id:target_record_uuid`. The comment next to it explains why that fallback exists rather than a nullable column: *the gate never depends on NULL semantics.* Which brings us to the index.

A record's address includes an optional namespace, and the naive constraint is the obvious one:

-- the version that looks right and enforces nothing
CREATE UNIQUE INDEX ON records (account, kind, address, namespace);

In SQL, `NULL` is not equal to `NULL`. A unique constraint containing a nullable column treats every NULL row as distinct from every other one, so that index enforces nothing at all for any record without a namespace — and that is most of them.

The constraint would exist. The schema would look right in a review. Duplicate charges would sail through it.

If you are on PostgreSQL 15 or later there is a one-line answer: `UNIQUE NULLS NOT DISTINCT` makes the constraint treat nulls as equal, which is what you meant in the first place. Use it if you can.

We cannot, because the same platform runs on a document store as well, and there the equivalent of that constraint has per-collection semantics that had to be matched. So the portable version splits on nullability and uses two partial indexes:

CREATE UNIQUE INDEX ON records (account, kind, address)
  WHERE namespace IS NULL;

CREATE UNIQUE INDEX ON records (account, kind, address, namespace)
  WHERE namespace IS NOT NULL;

Splitting on `namespace IS NULL` is what makes the guarantee unconditional.

If you use none of our software and read no further, take that one thing: **check whether your idempotency constraint contains a nullable column.**

A claim has three states and the third is the interesting one.

**Claim.** Insert a `pending` row. If the insert conflicts, refuse: someone else is already doing this.

**Settle.** The action succeeded. Update the row to `success` and leave it there. It now permanently occupies that slot, which is exactly what you want, because a retry of a succeeded action must not run again in 2026 or in 2031.

**Release.** The action *failed*. Delete the row.

That deletion is the design decision. The obvious implementation writes `error` into the row and moves on, and it is wrong, because the failed row still occupies the unique slot. Your customer's payment fell over on a network blip. They press the button again, and your gate refuses the retry forever, on the grounds that it has seen this key before. The comment in our source is blunt about it: *we never leave 'error' rows occupying the unique slot.*

A succeeded action stays claimed forever. A failed one is released completely. An in-flight one holds the slot until it resolves either way.

A test that calls the endpoint twice in sequence proves nothing about concurrency. Sequential calls pass against a completely unguarded implementation, because the first one has already finished writing by the time the second one reads.

The test that matters fires both at once:

# test_37_action_log_gate.py
with ThreadPoolExecutor(max_workers=2) as pool:
    results = [pool.submit(invoke), pool.submit(invoke)]
assert wins == 1 and blocks == 1

Its failure message is better engineering than the assertion. If `wins == 2` the message says the database constraint is missing. If `wins == 0` it says the object type was never registered.

A concurrency test that fails with `assert 2 == 1` tells you something is broken and costs you the afternoon. One that names the two ways it can break hands you the answer in the CI log.

Seven tests guard this gate. Alongside the concurrent one, `test_37_action_log_gate.py` covers a different target record not being blocked, an invocation carrying no action identity being deliberately left ungated, and a key scoped to a narrower slot than the action id alone would give.

Usage metering does this too, independently. Each metering event carries its own identifier, and the row is written at that address, so a re-sent batch updates the same rows instead of adding a second set. The schema for those events carries a one-line comment that is the whole design: *no separate unique index needed.* The end-to-end test re-sends an identical batch and asserts that at least three of its events come back reported as deduplicated, an assertion that fails the moment the address stops being the constraint.

Two subsystems, written at different times, both reaching for the address as the constraint. That is usually the sign that the primitive underneath is the right shape.

**It fails closed.** If the action log itself cannot be read, the claim is refused and the side effect does not happen.

Your action is unavailable rather than possibly duplicated. For a charge that is the correct trade. For warming a cache it is merely annoying, and the gate does not currently distinguish between the two.

**The fast path is not the gate.** Before inserting, the gate does a cheap lookup and re-checks the full key in application code rather than trusting that lookup on its own. The insert is the real gate. Anything in front of it is an early-out for the common case, and writing it that way round means a bug in the fast path costs latency rather than correctness.

**The guarantee is inherited, not declared.** The claim record's own schema declares no unique index at all. Everything rests on address uniqueness one layer down. A marker left in our source admits the author was not certain that constraint held at the layer below, and the concurrency test exists precisely because that uncertainty deserved an executable answer instead of a comment.

**Release re-opens a window, and the window is real.** Delete-on-failure is correct when the side effect did not happen. If the side effect *succeeded* and the settle-update is what failed — a connection dropped between charging the card and recording that you charged it — then releasing the slot lets a retry charge again. Our claim and our settle are two writes, not one transaction, and this is the residual. A table that stores the *response* against the key, which is the design our approach is a narrower version of, is what closes it. We have not built that, and an article about idempotency that skipped this paragraph would be selling.

**It is idempotency, not delivery.** Invoked twice, applied once. The same rule shows up again in state operations, where an operation that would move a record to the state it is already in has to succeed without restamping anything — A 409 should tell you what to do next covers that half. That is a claim about what happens to your data when a request arrives more than once. It says nothing about whether a request arrives at all, and anyone promising exactly-once delivery across a network is selling something.

Open whatever enforces idempotency in your own system.

Then answer two questions.

Does the uniqueness live in a constraint the database enforces, or in a branch your application takes? If it is a branch, write the two-thread test above. It takes about fifteen minutes and it will either reassure you or ruin your afternoon, and both outcomes beat finding out from a customer.

Then, if it is a constraint, check every column in it for nullability. A `NULL` in a unique index is a hole shaped exactly like the bug you were trying to prevent.

*(Disclosure: I work on Supero. The gate described here is ours; the nullable-column problem is everyone's, and is the reason this article exists in a form you can use without us.)*