The ledger is a table with the key as a primary key or a unique constraint, an action state, the stored response, and an expiry. The unique constraint is what does the real work: it lets the datastore arbitrate, so two requests that both pass a "have I seen this?" check cannot both proceed. Claiming the event with a unique-key insert such as INSERT ... ON CONFLICT DO NOTHING is what closes that check-then-act race, because a check-then-process deduplication has a window where two copies both pass the check before either records it (Prismatic).
A minimal schema looks like this:
CREATE TABLE action_ledger (
idempotency_key text PRIMARY KEY,
action_id text NOT NULL,
tenant_id text NOT NULL,
state text NOT NULL CHECK (state IN ('in_flight','completed','failed')),
request_hash text NOT NULL,
response_body jsonb,
response_status int,
claimed_at timestamptz NOT NULL DEFAULT now(),
lease_until timestamptz NOT NULL,
expires_at timestamptz NOT NULL
);
CREATE UNIQUE INDEX action_ledger_action ON action_ledger (tenant_id, action_id);
Claiming the action is one statement. If it returns a row, you own the work; if it returns nothing, someone else is already running it or already finished:
INSERT INTO action_ledger
(idempotency_key, action_id, tenant_id, state, request_hash, lease_until, expires_at)
VALUES ($1, $2, $3, 'in_flight', $4, now() + interval '60 seconds', now() + interval '7 days')
ON CONFLICT (idempotency_key) DO NOTHING
RETURNING idempotency_key;
The states matter more than people expect. Set to in_flight on claim, completed once the side effect commits and the response is stored, and failed only for outcomes you are prepared to make final. Cache carefully: replaying a 500 error from debug time is a real bug, and the fix is to treat 5xx as retryable by releasing the claim rather than storing the error as truth (Mathildaduku). The lease exists so a slow worker and a recovery worker cannot both act: reclaim stuck in-flight rows only after the lease expires, and use a fencing token so the original worker's late write is rejected.
Sizing the TTL is a decision rule, not a guess. Keep the deduplication window longer than the longest retry window you can expect, because a 1-hour TTL against a 72-hour retry period protects only the first hour (Prismatic). Published retry schedules give you concrete anchors. Svix retries eight times over roughly 24 hours, with delays of immediate, 5 seconds, 5 minutes, 30 minutes, 2 hours, 5 hours, 10 hours, and 10 hours (Svix). Stripe documents retries for up to three days. For agent actions, add the time a human might spend reviewing a stalled task before resuming it, then round up.
Two operational notes. First, an in-flight row that never completes is worse than a duplicate, because it blocks the action forever: run a sweeper that marks stale leases as failed and emits an alert. Second, put the ledger in the same transactional store as the business write wherever possible. If the side effect is a database row, commit the ledger state and the business row together. If the side effect is external, for example a payment call, you need the ledger plus a reconciliation job that asks the external system what actually happened.