# Idempotency-Key Guard — Implementation & Reference

## Overview

Clients that retry a mutating POST (network timeout, double-click, portal + webhook race) can end up sending the exact same request twice. Without protection, that means duplicate registrations or duplicate payment attempts.

The idempotency guard makes retries safe: a client-supplied `Idempotency-Key` header is claimed in a dedicated table *before* the underlying mutation runs. Exactly one request with a given key actually executes; every other request with the same key either gets rejected (still in progress / payload doesn't match) or gets a byte-for-byte replay of the original response.

It is **opt-in per request**, not enforced per endpoint — if the caller omits the header, behavior is unchanged from before this feature existed. This lets it roll out without breaking older/unmigrated clients.

**Wired into:**

- `POST /registration` — [registration.controller.ts:122-128](../src/registration/registration.controller.ts#L122-L128)
- `PUT /registration` (update) — [registration.controller.ts](../src/registration/registration.controller.ts), same pattern, `endpoint` key `'PUT /registration'`, payload hashed as `{ dto, isAdmin }` since the `isAdmin` query param also affects behavior
- `POST /payment/initiate/:registrationId` — [payment.controller.ts](../src/payment/payment.controller.ts)

**Not wired into:** Razorpay webhook handling (`PaymentService` webhook path) — deliberately out of scope, reverted after initial exploration.

---

## Components

| File | Responsibility |
| ---- | --------------- |
| [idempotency-key.entity.ts](../src/common/entities/idempotency-key.entity.ts) | TypeORM entity for the `idempotency_key` table |
| [idempotency-status.enum.ts](../src/common/enum/idempotency-status.enum.ts) | `pending` \| `completed` \| `failed` |
| [idempotency.repository.ts](../src/idempotency/idempotency.repository.ts) | Raw-SQL atomic claim (`INSERT ... ON CONFLICT DO NOTHING`), status transitions |
| [idempotency.service.ts](../src/idempotency/idempotency.service.ts) | `run()` — the public API controllers call; owns the claim/replay/conflict decision |
| [idempotency.module.ts](../src/idempotency/idempotency.module.ts) | Nest module wiring |
| `2026-08-11-01-add-idempotency-tables.sql` | Migration creating `idempotency_key` |

**Re-applying locally after a schema change:** the table's shape changed twice during review (`user_id` added to the unique index, then `id` switched from `UUID` to `SERIAL` and `hit_count` added). If you already ran an earlier draft of this migration against your local dev DB, drop the old table before re-running the migration file — this is dev-only, never do this against a shared/staging/prod database:

```sql
DROP TABLE IF EXISTS idempotency_key;
```

Then re-run `database/migrations/2026-08-11-01-add-idempotency-tables.sql` in full.

---

## Data model

```sql
CREATE TABLE idempotency_key (
  id SERIAL PRIMARY KEY,
  idempotency_key VARCHAR(255) NOT NULL,
  endpoint VARCHAR(255) NOT NULL,
  request_hash VARCHAR(64) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',   -- pending | completed | failed
  response_data JSONB,
  user_id INT,
  hit_count INT NOT NULL DEFAULT 1,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX uq_idempotency_key_endpoint_user ON idempotency_key (idempotency_key, endpoint, user_id);
```

- **`id` is a plain auto-increment integer (`SERIAL`)**, not a UUID — a deliberate exception to this repo's usual "UUID for primary keys" convention ([database.md](.claude/rules/backend/database.md)), chosen for this table specifically since these are short-lived, high-churn, purely internal rows (never exposed in an API response) where a compact sequential id is simpler to read in logs/queries.
- **Unique index on `(idempotency_key, endpoint, user_id)`** is what makes the claim atomic — the same key can be used independently for `POST /registration` and `POST /payment/initiate` (endpoint differs), and two different users can never collide on the same key even if one were ever accidentally reused (`user_id` differs). `tryInsertPending`'s `ON CONFLICT` target and `findByKeyAndEndpoint`/`tryReclaimFailed`'s `WHERE` clauses all include `user_id` to match. `tryReclaimFailed` compares it with `IS NOT DISTINCT FROM` (not `=`) so the null-user case still matches correctly.
- **`hit_count`** starts at `1` on the initial claim and is incremented ([idempotency.repository.ts](../src/idempotency/idempotency.repository.ts) `incrementHitCount`) every time a *later* request matches the same `(idempotency_key, endpoint, user_id)` — whether that later hit results in a replay, a conflict, or a reclaim. It's a best-effort counter (same swallow-and-log pattern as `markCompleted`/`markFailed`) purely for observability — it never participates in the claim/replay/conflict decision itself.
- **`request_hash`** is `sha256(JSON.stringify(payload))` of the *effective* request — not just the body. It must cover everything that changes what the executor does: for `PUT /registration` that's `{ dto, isAdmin }` (the `isAdmin` query param changes behavior), for `POST /payment/initiate` that's `{ registrationId, dto }` (the path param matters too). This is how a genuinely different request reusing the same key gets caught (see "hash mismatch" below) instead of silently replaying the wrong response.
- **`response_data`** caches the executor's return value so a replay can return it verbatim without re-running any business logic.

---

## Request flow

```mermaid
flowchart TD
    Start([Request arrives<br/>with Idempotency-Key?]) --> HasKey{Key present?}
    HasKey -->|No| RunDirect[Run executor directly<br/>— unchanged legacy behavior]
    RunDirect --> DoneDirect([Return result])

    HasKey -->|Yes| Hash[Compute sha256 of payload]
    Hash --> TryInsert{INSERT ... ON CONFLICT<br/>DO NOTHING<br/>RETURNING id}

    TryInsert -->|Row returned<br/>— won the claim| RunExecutor[Run executor<br/>e.g. registration.register&#40;&#41;]
    RunExecutor -->|success| MarkCompleted[markCompleted&#40;id, result&#41;]
    MarkCompleted --> ReturnResult([Return result])
    RunExecutor -->|throws| MarkFailed[markFailed&#40;id&#41;]
    MarkFailed --> Rethrow([Rethrow original error])

    TryInsert -->|No row<br/>— lost the race| Lookup[findByKeyAndEndpoint]
    Lookup --> Exists{Row found?}
    Exists -->|No — narrow race window,<br/>winner not visible yet| Conflict409a[409 IDEMPOTENCY_KEY_IN_PROGRESS]

    Exists -->|Yes| IncrementHit[incrementHitCount&#40;id&#41;<br/>best-effort, non-blocking]
    IncrementHit --> StatusFailed{status = failed?}
    StatusFailed -->|Yes| Reclaim{tryReclaimFailed<br/>CAS failed→pending,<br/>with the NEW hash}
    Reclaim -->|won| RunExecutor
    Reclaim -->|lost — another retry<br/>won first| Conflict409d[409 IDEMPOTENCY_KEY_IN_PROGRESS]

    StatusFailed -->|No| HashMatch{request_hash<br/>matches?}
    HashMatch -->|No| Conflict409b[409 IDEMPOTENCY_KEY_CONFLICT<br/>hash_mismatch]

    HashMatch -->|Yes| Status{status?}
    Status -->|completed| Replay[Return cached response_data<br/>— no re-execution]
    Status -->|pending| Conflict409c[409 IDEMPOTENCY_KEY_IN_PROGRESS]
```

### Step-by-step (`IdempotencyService.run`, [idempotency.service.ts:23-56](../src/idempotency/idempotency.service.ts#L23-L56))

1. No key on the request → run the executor immediately, no bookkeeping. This is the escape hatch for old clients.
2. Key present → hash the payload, then attempt an atomic claim (`claim()`, [idempotency.service.ts:58-94](../src/idempotency/idempotency.service.ts#L58-L94)).
3. **Won the claim** (`tryInsertPending` returned a row): run the caller's executor.
   - Success → cache the result, mark `completed`, return it.
   - Throws → mark `failed`, rethrow the original error (client sees the real failure, not an idempotency error).
4. **Lost the claim** (row already existed): look up the existing row and branch on its state —
   - Not found at all → an *extremely* narrow window where the winner's insert hasn't committed/become visible yet. Treated as `in_progress` rather than retrying in a loop.
   - `status = failed` → checked **before** comparing hashes. The previous attempt never succeeded, so nothing was actually done on the caller's behalf — the retry is allowed even if the payload changed (e.g. the client is correcting whatever caused the earlier failure). A compare-and-swap (`tryReclaimFailed`) lets **one** retry reclaim the row back to `pending` with the *new* hash and try again; if two retries race for the same failed key, only one wins the CAS and the other gets `409 in_progress`.
   - Otherwise, `request_hash` differs → **hash mismatch**: same key, different payload, but the prior attempt was `completed` or `pending` (i.e. something did or is doing real work under this key). Rejected with `409 IDEMPOTENCY_KEY_CONFLICT`. This is what protects against a key being accidentally reused for a different logical request.
   - `status = completed` (hash matches) → **replay**: return the cached `response_data` as-is. No business logic re-runs.
   - `status = pending` (hash matches) → another attempt is currently executing. `409 IDEMPOTENCY_KEY_IN_PROGRESS`.

---

## Concurrency: why the claim is actually atomic

`tryInsertPending` ([idempotency.repository.ts:21-39](../src/idempotency/idempotency.repository.ts#L21-L39)) uses raw SQL specifically because TypeORM has no "insert or tell me it already existed" primitive:

```sql
INSERT INTO idempotency_key (idempotency_key, endpoint, request_hash, status, user_id)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (idempotency_key, endpoint, user_id) DO NOTHING
RETURNING id
```

This is a single round-trip to Postgres, enforced by the unique index — not an application-level check-then-insert. Two (or ten) truly concurrent requests with the same key all race this statement; the database guarantees exactly one gets a row back, no matter how many arrive in the same millisecond.

This was verified manually: three identical concurrent requests to `POST /registration` with the same key produced **one** registration (success), one `409` (caught mid-flight), and one replay (arrived after completion, returned the same `registrationId`) — confirmed against the `idempotency_key` and `hdb_program_registration` tables directly.

---

## Resolved

- **Cross-user key collision.** The lookup/claim was originally scoped only to `(idempotency_key, endpoint)`, not the calling user — two different users reusing the same key value with a byte-identical payload could theoretically replay each other's cached response. **Fixed**: the unique index, `tryInsertPending`'s `ON CONFLICT` target, `findByKeyAndEndpoint`, and `tryReclaimFailed` are now all scoped to `(idempotency_key, endpoint, user_id)`, so two different users can never share a claim even if a key value were somehow reused.
- **`PUT /registration` hash didn't cover the full request.** The `isAdmin` query param changes what `RegistrationService.update()` does but wasn't part of the hashed payload. **Fixed**: the hash now covers `{ dto, isAdmin }`, matching the pattern already used for `POST /payment/initiate`'s `{ registrationId, dto }`.
- **Retry-after-failure with a corrected payload was rejected as a hash mismatch.** `claim()` checked `request_hash` before checking `status`, so a client that got a `400` on the first attempt (e.g. `U_BR_008` "email already used to register N seekers") and retried under the same key with a *different* payload (e.g. a different email) got `409 IDEMPOTENCY_KEY_CONFLICT` instead of having the corrected retry actually run — because nothing had succeeded yet, there was no real "different logical request" to protect against. **Fixed**: `status = failed` is now checked first; a failed row is reclaimed (and its `request_hash` updated to the new payload's hash) regardless of whether the hash changed. Hash-mismatch rejection now only applies when the prior attempt was `completed` or `pending` — i.e. when real work was or is being done under that key.

## Known gaps / things to watch

These are real risks identified while reviewing this implementation — not yet fixed, flagged for awareness:

1. **Duplicate registration on retry-after-partial-failure.** `RegistrationService.register()` commits the actual DB insert inside a transaction ([registration.service.ts:586-600](../src/registration/registration.service.ts#L586-L600)), but a substantial amount of work — profile updates, communication template lookups, attachment building — runs **after** that commit, outside any transaction, and isn't all wrapped in try/catch. If that post-commit code throws, the idempotency key gets marked `failed` even though the registration row is already committed. A client retry with the same key then reclaims the failed key and calls `register()` again, creating a **second** registration for what the client believes is one attempt. Only failures *after* the transaction commits are affected — earlier validation failures are safe to retry.
2. **Stuck-`pending` rows never expire.** If the process crashes/restarts between the transaction commit and `markCompleted` (or if `markCompleted`/`markFailed` itself fails — both just log-and-swallow in the repository, see [idempotency.repository.ts:70-84](../src/idempotency/idempotency.repository.ts#L70-L84)), the row stays `pending` forever. Every future retry with that key gets `409 in_progress` indefinitely — there's no staleness timeout or sweep job.
3. **No TTL / cleanup.** `idempotency_key` rows are never purged, so the table grows unbounded, and there's no window after which a key becomes safe to reuse for something unrelated.
4. **Webhook dedup was intentionally not built.** An earlier version of this migration also added a `payment_webhook_event` table + claim logic in `PaymentService` for deduping Razorpay webhook deliveries. That was reverted at the user's request — Razorpay webhook handling is untouched and relies solely on its pre-existing "is payment already completed" check.

## Client-side key generation (verified)

Both frontends mint the key correctly and were checked for the "same key reused across a genuinely different request" failure mode:

- **Seeker app** ([Registration/index.tsx:2305-2306](../../infinipath-web-seeker/infinipath-web/src/pages/Registration/index.tsx#L2305-L2306)): one `uuidv4()` (from the `uuid` npm package) per registration-create attempt, held in a `useRef`, reused only for retries of that same attempt.
- **Payment initiate, both seeker and admin apps**: key is re-minted whenever a fingerprint of the payment-affecting fields (`paymentMode`, `tdsAmount`) changes, so switching payment mode before retrying doesn't collide with the backend's hash-mismatch check. Seeker uses `uuid@11`'s `v4()`; admin uses the native `crypto.randomUUID()`. Both are cryptographically random — UUID v4 collision by chance is not a realistic concern (~2.7×10¹⁸ generations needed for a 50% chance of one collision).

---

## Manual test checklist

- [x] Same key, same payload, concurrent → exactly one execution; others get `409 in_progress` or a replay.
- [ ] Same key, different payload, prior attempt `completed` → `409 IDEMPOTENCY_KEY_CONFLICT`, second payload never executes.
- [ ] Same key, different (corrected) payload, prior attempt `failed` → reclaimed and executed with the new payload, no conflict.
- [ ] No key header → behavior identical to pre-idempotency (regression check).
- [ ] True pre-commit failure (e.g. bad `programId`) + retry same key → exactly one registration created on the successful retry.
- [ ] **Post-commit failure + retry same key → check for a duplicate row in `hdb_program_registration`** (see gap #1 above).
- [ ] Higher concurrency (10+ simultaneous requests, same key) to stress the insert race beyond 3 requests.
- [ ] Same tests repeated against `POST /payment/initiate/:registrationId`.

Verification queries used during testing:

```sql
-- Idempotency key state
SELECT id, idempotency_key, endpoint, status, request_hash, response_data, created_at, updated_at
FROM idempotency_key
WHERE idempotency_key = '<key>';

-- Actual registrations created for the attempt
SELECT id, program_id, user_id, owner_user_id, full_name, mobile_number, email_address, registration_date
FROM hdb_program_registration
WHERE program_id = <programId> AND email_address = '<email>'
ORDER BY registration_date DESC;
```