[Security] Idempotency keys missing across XP-award and webhook-triggered mutations — same root cause as #547 and #795

[mohi2006august]

What happened?

MergeShip already has two open issues pointing at the same underlying gap from different angles: #547 (duplicate webhook deliveries grant double XP) and #795 (concurrent mentor verify can award XP twice without an atomic lock). Both are symptoms of the same missing primitive: no idempotency-key layer between event ingestion (webhooks, Inngest jobs, client actions) and state-mutating writes (XP, badges, claims).

Patching each call site individually (a lock here, a dedup check there) will keep missing new cases — #857 (claim limit bypass) and #858 hint at the same class of bug recurring in different subsystems. This proposes a shared, reusable fix instead of N one-off fixes.

Proposed Approach

Add an idempotency_keys table: (key TEXT PRIMARY KEY, operation TEXT, actor_id TEXT, result JSONB, created_at TIMESTAMPTZ).
Every state-mutating operation that can be triggered more than once for the same logical event (webhook delivery ID, Inngest event ID, XP-award action, badge claim, issue claim) must supply a deterministic key — e.g. webhook:{delivery_id}, xp-award:{user_id}:{pr_id}:{stage}, claim:{user_id}:{issue_id}.
Wrap the write in a single DB transaction: INSERT ... ON CONFLICT (key) DO NOTHING RETURNING * — if no row is returned, the operation already ran; return the cached result instead of re-executing side effects.
Build this as one small shared helper in src/lib/idempotency/ so every future XP/webhook/claim code path uses the same primitive instead of reinventing locking.
Retrofit #547, #795, and #857's call sites onto this helper as the first consumers (closes all three with one mechanism).

Steps to Reproduce

1. Trigger the same webhook delivery twice (GitHub retries on non-2xx, or manually replay a delivery ID from the webhook logs).
2. Observe that the XP-award handler processes both deliveries independently — there is no idempotency key check against the delivery ID before the XP mutation runs.
3. Separately: have two requests hit the mentor-verify endpoint for the same PR at nearly the same time (e.g. two browser tabs, or a quick double-click). Both can read the "not yet verified" state before either write commits, so both award XP.
4. Same pattern applies to the issue-claim endpoint (see claimIssue bypasses the 3-active-claim limit — unlimited claims via Browse Issues page #857) — rapid repeated calls aren't blocked by a DB-level uniqueness constraint, only (at best) an app-level check that races.

Expected Behavior

Each logical event (a specific webhook delivery, a specific mentor-verify action, a specific issue claim) should produce its side effects exactly once, enforced at the database level via an idempotency key — not just an application-level check that can race under concurrent requests.

Concretely:

• A duplicated webhook delivery should be detected and skipped (return the cached result), not reprocessed.
• Two concurrent mentor-verify calls for the same PR should result in exactly one XP award, not two.
• Rapid repeated claim requests for the same issue should be rejected after the first, atomically.

Where does this occur?

API

Environment

• src/lib/xp/ — XP award logic (issue Bug: XP award function is not idempotent, duplicate webhook deliveries grant double XP #547, Concurrent mentor verify can award XP twice without atomic lock #795)
• inngest/ — webhook-triggered background jobs that call XP-award paths
• Issue-claim endpoint referenced in claimIssue bypasses the 3-active-claim limit — unlimited claims via Browse Issues page #857
• Applies in any environment (dev/prod) since it's a logic/schema gap, not environment-specific — but most visible under concurrent load or when GitHub retries webhook deliveries in production.

Screenshots / Logs

No response