Idempotency: the concept that saves your data

The short answer: a retry is only safe if the write is keyed

Idempotency has a reputation as distributed systems theory. In practice it is one table, one unique constraint and a discipline about where the key comes from. The teams who skip it are not the ones who never heard of it. They are the ones who assumed their retry count was low enough for it not to matter, and duplicates do not care about your retry count. They care about whether the unlucky run happened at all.

The words, used precisely

Six terms that decide the design

Idempotency

The property that performing an operation two or more times produces the same result and the same system state as performing it once. It is a property of the write, not of the network transport that carried it.

Idempotency key

A stable identifier attached to one logical operation and reused by every retry of that operation. It must be created before the first attempt and stored, because a key regenerated on retry cannot match the earlier attempt.

At-least-once delivery

The guarantee that a message will arrive one or more times, with duplicates possible. Every practical queue, webhook and HTTP retry uses it, because guaranteeing a single delivery over an unreliable network is not achievable.

Exactly-once effect

The achievable version of exactly-once. Messages still arrive repeatedly, and the receiver makes duplicate arrivals harmless by keying the write. Systems marketed as exactly-once are almost always doing this internally.

Fencing token

A number that increases with every lease handover, sent with each write and rejected by the store if it is lower than the last one seen. It stops a worker that froze and resumed from overwriting the work of the worker that replaced it.

Dedupe window

How long you retain a key before forgetting it. A retry arriving after the window expires is treated as new work, which makes the window the real limit on your protection.

The distinction that matters most in that list is delivery against effect. Nobody can promise your webhook fires once. Anybody can make a second firing do nothing, and that is the whole game.

The seven moments a duplicate is born

Duplicates are not caused by bad code. They are caused by a network that loses responses, and every one of these seven moments happens in ordinary operation on a healthy system.

The Key Ladder, from a key that holds to a key that lies

Three things must never appear in a key: the current timestamp, a random value generated at call time, and a row number or array index. Each changes between attempts, and each turns the unique constraint into an expensive way of inserting every request.

The claim and commit pattern

Reserve the key before doing any work, then finish the bookkeeping inside the same transaction as the business write. Checking whether a key exists and then inserting it as two separate statements loses the race, because two workers can both read absent before either writes. Let the database decide the winner with a unique constraint.

-- One table. Every workflow that writes anything shares it.

create table processed_operations (
  idempotency_key text primary key,
  workflow        text        not null,
  status          text        not null check (status in ('in_flight','done','failed')),
  request_hash    text        not null,
  response        jsonb,
  attempt         int         not null default 1,
  claimed_at      timestamptz not null default now(),
  completed_at    timestamptz,
  expires_at      timestamptz not null
);
create index on processed_operations (expires_at);

-- STEP 1  Claim the key before doing any work.
-- Exactly one caller wins the insert. Everyone else gets zero rows.

insert into processed_operations (idempotency_key, workflow, status, request_hash, expires_at)
values ($1, $2, 'in_flight', $3, now() + interval '30 days')
on conflict (idempotency_key) do nothing
returning idempotency_key;

-- STEP 2  Zero rows means somebody already claimed it. Read the outcome, then decide.

select status, request_hash, response, now() - claimed_at as age
from processed_operations
where idempotency_key = $1;

--  done                        -> return the stored response, perform no write
--  failed                      -> safe to retry, set status back to in_flight and proceed
--  in_flight and age < lease   -> another worker owns it, back off and try later
--  in_flight and age > lease   -> the owner died mid-flight, take the lease over
--  request_hash differs        -> same key, different body: reject 422, never overwrite

-- STEP 3  Commit the bookkeeping in the SAME transaction as the business write.
-- Two transactions means a crash between them, and a crash between them is the bug
-- this whole table exists to prevent.

begin;
  update orders
     set status = 'refunded', refunded_at = now()
   where id = $4 and status = 'paid';

  update processed_operations
     set status = 'done', response = $5, completed_at = now()
   where idempotency_key = $1;
commit;

The request hash column earns its place the first time somebody reuses a key with a different body, usually a script looping over a list with a key computed outside the loop. Without the hash you silently return the first response for every subsequent item. With it you reject the second call and someone finds the bug in minutes.

If the write goes to a system you do not control, the same shape still applies. Claim locally, call the remote API with its own idempotency header if it offers one, store the response, and mark the row done. Your table is then the record of what you attempted, which is the artifact you need when the provider and your ledger disagree during systems integration work.

Owning the dedupe store or borrowing the provider's

Many APIs accept an idempotency header and deduplicate for you. That is genuinely useful and it is not a substitute for your own table, because it protects one hop of a workflow that has several.

Use both. The provider header protects the hop and your table protects the operation. The pattern to avoid is trusting the header alone for a workflow that writes to three systems, because a crash after the second write leaves a replay with no memory of the first two.

Where a key is not enough

Idempotency prevents the same operation applying twice. It says nothing about two different operations arriving in the wrong order, and it does not protect against a worker that came back from the dead.

Out of order updates

Two webhooks describing the same record arrive in the wrong sequence and the older payload lands last. Both are unique operations with valid keys, so dedupe does nothing. The fix is a version on the record: store the source version or event timestamp and refuse any write whose version is not greater than the one already stored. This is a last-write-wins rule made explicit rather than accidental.

Zombie workers

A worker takes a lease, its process freezes long enough for the lease to expire, a second worker takes over and finishes the job, then the first one wakes up and writes. Its key is already marked done, so it either overwrites or reports a false conflict. A fencing token fixes this: every lease handover increments a counter, every write carries the current value, and the store rejects any write carrying a lower one.

Operations that cannot be undone

Sending an email, posting to a public channel or dispatching a physical package cannot be rolled back by a later transaction. For these, claim the key first, perform the side effect, then mark done, and accept that a crash in the middle leaves one ambiguous case rather than a silent duplicate. Log that case loudly enough for a human to resolve it, which is exactly the dead letter path from error handling that stops silent failures.

Prove it with a drill, not with an argument

Idempotency is only real if you have watched it work. The drill takes an afternoon in staging and it finds the broken key in nearly every workflow that has never been tested this way.

1. Replay the same event twice, back to backThe baseline

   Send an identical event twice with no delay. One record should change and the second call should return the stored response without writing. If two records appear, the key is wrong or the claim happens after the work.

2. Replay with a delay longer than the leaseThe lease test

   Send it again after the in-flight lease expires. This exercises the takeover path, which is the branch most implementations get wrong, because it is the branch nobody triggers by accident during development.

3. Kill the process between the write and the bookkeepingThe crash test

   Stop the worker after the business write commits and before the dedupe row is updated, then replay. If both statements share one transaction, nothing is duplicated. If they do not, you have just reproduced your next incident on purpose.

4. Run two workers on one messageThe race test

   Deliver the same message to two workers at once. Exactly one should win the claim and the loser should read the winner's outcome rather than waiting on a lock or timing out.

5. Replay the same key with a different bodyThe misuse test

   Send a changed payload under a key already used. The correct answer is a rejection, not a silent success. This is the test that catches keys computed outside a loop, which is the most common way a real system reuses keys by accident.

Keep these five as a test file that runs in CI against a staging environment. They are cheap, they run in seconds, and they fail loudly the day someone moves the key generation one line lower.

The design checklist

One habit is worth more than the whole list: decide the key at design time and write it into the workflow contract before anyone builds. Choosing a key after the fact means retrofitting it into code that already assumed retries were harmless, which is a rewrite rather than a change. The contract template in choosing between n8n, Make and custom code has a field for exactly this, and rate limits, retries and backoff covers the retry policy that sits on top of it.