How a Database-Level Idempotency Key Prevents Duplicate Records

Duplicate database records can arise from network retries, browser timeouts, proxy retries, and frontend bugs — not just accidental double-clicks. Application-level checks like 'check-then-act' logic are vulnerable to race conditions under concurrency, allowing two requests to pass validation before either has inserted a row. The reliable fix is to enforce uniqueness at the database level by adding a unique constraint on an idempotency key field, since PostgreSQL treats the check and insert as a single atomic operation. On the frontend, a UUID idempotency key should be generated once per operation and reused across all retry attempts, rather than regenerated per request. This pattern applies broadly to any data-creation endpoint, including invoices, orders, payments, and registrations.