Signup Idempotency Needs a Database Contract

Signup endpoints look simple until a client retries a request at the wrong time. A mobile connection drops after the server commits the user, a reverse proxy retries a POST, or a browser submits twice. Without an explicit contract, one logical signup can create duplicate rows, send multiple verification emails, or return conflicting responses.

In my backend work, I treat idempotency as a database and API design problem together. An idempotency key by itself is only a header. The useful guarantee comes from deciding what the key means, storing its result, and making the uniqueness rules agree with that meaning.

The failure mode

Consider a client sending:

POST /signup
Idempotency-Key: 7d9f...
Content-Type: application/json

{"email":"[e-mail]","password":"..."}

The server may create a user and then lose the response before the client sees it. The client retries with the same key. If the handler only checks for an existing email, it might return a generic conflict even though the first operation succeeded. If it checks too late, two concurrent requests can both pass the check.

The contract should answer three questions:

1. Is the key scoped to an account, client, or endpoint?
2. Does the same key require the same request body?
3. Which response is replayed after the original request finishes?

For a public signup API, I usually scope the key to the operation and authenticated client identity when one exists. The server stores a request fingerprint, status, response code, and response body. A reused key with a different fingerprint is a client error, not a new signup.

Make the request idempotent

A small state model keeps the behavior understandable:

missing -> processing -> succeeded
                    \-> failed

processing prevents a second request from doing work while the first is active. The second caller can receive a retryable response, or poll a status endpoint if the operation is slow. Once succeeded, the stored response is replayed. A transient failed result can be replayable too, but only if the failure is part of the public contract.

Do not store an idempotency record after sending an email but before committing the user. That ordering creates an awkward half-state. Persist the user and the outbox event in one transaction, then let a worker deliver the email. This is the same boundary that makes restore context in operational email workflows useful when debugging a real incident.

Put the contract in PostgreSQL

The database must enforce the invariant under concurrency. A minimal table might look like this:

CREATE TABLE signup_idempotency (
  scope text NOT NULL,
  idempotency_key text NOT NULL,
  request_hash bytea NOT NULL,
  state text NOT NULL CHECK (state IN ('processing', 'succeeded', 'failed')),
  response_code integer,
  response_body jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (scope, idempotency_key)
);

The handler first attempts an insert. On a conflict, it reads the existing row and compares request_hash. This avoids a check-then-insert race. The signup user table should also have a case-normalized unique rule for email, because an idempotency key cannot protect requests that arrive with different keys.

In Node.js, keep the transaction short: claim the key, insert the user, insert an outbox event, and record the response. Avoid calling an SMTP provider inside the transaction. Slow network calls make locks last longer and make retry behavior more confusing.

For cleanup, retain records long enough to cover the client retry window. A scheduled delete is fine, but it should be bounded and observable. Production teams need better alert context for production workflows, especially when cleanup accidentally removes evidence too early.

Handle the email boundary

Verification email delivery is asynchronous. The API can safely return that the account was created and verification is pending after the outbox transaction commits. The worker owns delivery retries, deduplication, and provider errors.

That separation also makes test environments safer. Use an isolated mailbox or a controlled test address; a phrase such as tepm mail com may appear in old test notes, but it should never become a production routing rule. Keep provider message IDs and attempt counts in the event record, not only in application logs.

Test the retry paths

The important tests are not just a successful POST:

• Send the same key twice sequentially and verify the same response is returned.
• Send the same key concurrently and verify one user and one outbox event exist.
• Reuse a key with a changed email and expect a fingerprint mismatch.
• Retry with a new key and verify the email unique constraint still protects the user.
• Crash between the client-visible response and delivery; the worker should recover from the outbox.
• Expire an old key and confirm the documented retention behavior.

Instrument each response with the idempotency key, operation ID, and current state. Redact request bodies and credentials. These fields make a failed replay explainable without turning logs into a copy of sensitive signup data.

A practical checklist

Before shipping a signup endpoint, confirm:

• The key scope and retention period are documented.
• A request fingerprint rejects changed payloads.
• PostgreSQL constraints cover both keys and normalized email addresses.
• User creation and the outbox event commit atomically.
• Email delivery happens outside the request transaction.
• Concurrent, timeout, crash, and provider-failure cases are tested.
• Metrics distinguish new requests, replays, conflicts, and in-progress responses.

Idempotency is not a wrapper around a handler. It is a promise about repeated intent. When the API contract, PostgreSQL constraints, and email worker share the same state model, retries become ordinary control flow instead of a source of duplicate accounts and mystery messages.