Archiving Expired Idempotency Keys for Audit

A 24-hour idempotency window is right for replay and wrong for evidence. Once a key expires, the system forgets an operation ever happened — so a client retrying 30 hours later executes a second charge, and an incident investigation three days later cannot reconstruct which requests were in flight. A slim archive fixes both without inflating the hot store. This runbook sits under idempotency key storage & TTL management.

Prerequisites. A live key store with a defined TTL — see choosing TTL values for idempotency keys — and a relational or columnar store for the archive.

---

Step 1 — Design the archive row

CREATE TABLE idempotency_key_archive (
    scoped_key   text        NOT NULL,
    fingerprint  bytea       NOT NULL,      -- SHA-256, not the body
    status_code  smallint    NOT NULL,
    resource_id  text,                      -- e.g. the charge id, for reconciliation
    created_at   timestamptz NOT NULL,
    expired_at   timestamptz NOT NULL,
    PRIMARY KEY (scoped_key, expired_at)
) PARTITION BY RANGE (expired_at);

CREATE TABLE idempotency_key_archive_2026_08 PARTITION OF idempotency_key_archive
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

No response column. That single omission is what keeps the archive small, cheap, and free of personal data — which in turn keeps it out of scope for erasure requests and lets you retain it for years without a compliance conversation.

---

Step 2 — Archive on expiry, in batches

-- Move expired live rows into the archive and delete them, in one transaction,
-- bounded so the lock is never held long.
WITH expired AS (
    SELECT scoped_key, fingerprint, status_code, response->>'id' AS resource_id,
           created_at, expires_at
      FROM idempotency_keys
     WHERE expires_at < now()
     ORDER BY expires_at
     LIMIT 10000
     FOR UPDATE SKIP LOCKED             -- concurrent sweepers never block each other
), archived AS (
    INSERT INTO idempotency_key_archive
        (scoped_key, fingerprint, status_code, resource_id, created_at, expired_at)
    SELECT scoped_key, fingerprint, status_code, resource_id, created_at, expires_at
      FROM expired
    ON CONFLICT (scoped_key, expired_at) DO NOTHING
    RETURNING scoped_key
)
DELETE FROM idempotency_keys k
 USING archived a
 WHERE k.scoped_key = a.scoped_key;

def sweep(conn, batch=10_000, pause_s=0.2) -> int:
    """Run until nothing is left to archive. Bounded batches keep locks short."""
    total = 0
    while True:
        with conn.transaction():
            moved = conn.execute(ARCHIVE_SQL, (batch,)).rowcount
        total += moved
        metrics.inc("idempotency_archived_total", moved)
        if moved == 0:
            return total
        time.sleep(pause_s)             # yield to request traffic

For a Redis-backed live store the sweeper cannot see expirations directly. Subscribe to keyspace notifications, or — more reliably — write the archive row at completion time rather than at expiry, since the record is immutable from then on.

# Redis variant: archive at completion, not at expiry. Keyspace notifications are
# best-effort and are dropped when a client is slow, so they cannot be the only path.
def complete(scoped, status, body, resource_id):
    redis.hset(scoped, mapping={"state": "completed", "status": status, "body": body})
    redis.pexpire(scoped, TTL_MS)
    archive.insert(scoped_key=scoped, fingerprint=fp, status_code=status,
                   resource_id=resource_id, created_at=created, expired_at=now + TTL)

---

Step 3 — Answer a late retry correctly

The archive turns an ambiguous situation into a definite one.

def handle(scoped, fingerprint):
    live = store.register_or_get(scoped, fingerprint)
    if live is None:
        # Nothing live — but did this key ever exist?
        past = archive.lookup(scoped)
        if past is not None:
            store.release(scoped)                 # undo the speculative registration
            raise ApiError(409, "idempotency_key_expired", detail={
                "original_status": past.status_code,
                "resource_id": past.resource_id,   # enough to reconcile
                "created_at": past.created_at.isoformat(),
            })
        return "execute"
    ...

Returning the resource_id matters. Without it, the client knows only that it must not retry; with it, the client can fetch the original resource and reconcile without a support ticket.

The archive lookup only runs on the fresh-key path, which is the overwhelming majority of requests — so it must be fast. Keep it to a point lookup on the partitioned primary key, and skip it entirely for keys whose embedded timestamp (in a ULID or UUIDv7) is newer than the live TTL:

def needs_archive_lookup(key: str) -> bool:
    ts = ulid_timestamp_ms(key)
    return ts is not None and (time.time() * 1000 - ts) > LIVE_TTL_MS

---

Step 4 — Partition and drop

-- Monthly partitions: retention becomes a metadata operation, not a mass delete.
CREATE TABLE idempotency_key_archive_2026_09 PARTITION OF idempotency_key_archive
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

-- Retire the month that has aged out. Instant, no vacuum, no bloat.
DROP TABLE idempotency_key_archive_2026_05;

# Create next month's partition ahead of time; a missing partition rejects inserts.
psql -c "SELECT format(
  'CREATE TABLE IF NOT EXISTS idempotency_key_archive_%s PARTITION OF
   idempotency_key_archive FOR VALUES FROM (%L) TO (%L)',
  to_char(d, 'YYYY_MM'), d, d + interval '1 month')
  FROM generate_series(date_trunc('month', now()),
                       date_trunc('month', now()) + interval '2 months',
                       interval '1 month') d;" -tA | psql

Run partition creation on a schedule with at least two months of lead time. A missing partition does not degrade gracefully — inserts fail outright, and the sweeper stops archiving.

---

Verification and testing

Confirm the archive is written and the live row removed.

SELECT count(*) FROM idempotency_keys        WHERE expires_at < now();  -- expect 0
SELECT count(*) FROM idempotency_key_archive WHERE expired_at > now() - interval '1 day';

Confirm a late retry gets 409, not a second execution.

psql -c "INSERT INTO idempotency_key_archive
         (scoped_key, fingerprint, status_code, resource_id, created_at, expired_at)
         VALUES ('idem:v1:probe', '\\x00', 201, 'ch_probe', now() - interval '30 hours',
                 now() - interval '6 hours')"
curl -s -XPOST localhost:8080/v1/charges -H 'Idempotency-Key: probe' \
  -H 'Content-Type: application/json' -d '{"amount":4500,"currency":"gbp"}' | jq -r '.code'
# expect: idempotency_key_expired

Confirm no personal data leaked in.

SELECT column_name, data_type FROM information_schema.columns
 WHERE table_name = 'idempotency_key_archive';
-- expect: no jsonb/text column capable of holding a request or response payload

Confirm partitions exist ahead of time.

SELECT relname FROM pg_class c JOIN pg_inherits i ON c.oid = i.inhrelid
 WHERE i.inhparent = 'idempotency_key_archive'::regclass ORDER BY relname DESC LIMIT 3;
-- expect: at least the next two months

---

Failure scenarios and debugging

---

SRE / observability checklist

1. idempotency_expired_backlog — Gauge of live rows past expiry. Alert above 100,000; it is the leading indicator of vacuum trouble.
2. idempotency_archived_total — Counter of rows moved per sweep. A flat line while the backlog grows means the sweeper is wedged.
3. idempotency_archive_newest_partition_days — Gauge of days until the newest partition’s upper bound. Page below 30.
4. idempotency_expired_replay_total — Counter of 409 idempotency_key_expired responses. Non-zero on a payments route means clients are retrying past the window and the archive is earning its keep.
5. idempotency_archive_lookup_duration_ms — Histogram of the archive point lookup. Alert if p99 exceeds 5 ms; it sits on the fresh-key path.
6. idempotency_archive_bytes — Gauge from pg_total_relation_size. Growth well above the modelled 110 bytes per row means a column crept in.

---

Related

• Idempotency Key Storage & TTL Management — the parent page on storage sizing, expiry and reaping.
• Choosing TTL Values for Idempotency Keys — how the live window is chosen, and why the archive covers what it cannot.
• Redis vs PostgreSQL for Idempotency State — which store holds the live record, and how that changes the archiving trigger.
• Reconciling Duplicate Payments After an Incident — the investigation this archive exists to make possible.