Replay-Safe Email Verification with PostgreSQL

Email verification looks like a delivery feature, but the harder backend problem is replay. A user can click the same link twice, a browser can retry a request after a timeout, and a worker can process the same message more than once.

If the application stores only a current token on the user row, these cases become difficult to reason about. The database cannot tell whether a request is a valid retry, an expired link, or an old token being replayed. A better design stores each verification attempt as a short-lived record with an owner, an expiry time, and a consumed state.

This pattern is useful for a production REST API and for authentication tests that use a disposable email address. The email provider is an input boundary; replay protection belongs to the service that issued the token.

The real problem is replay, not delivery

Consider a POST /email-verifications endpoint. It creates a token and sends a message. The client then calls POST /email-verifications/confirm with the token.

Several events can happen in an ordinary request flow:

• The client retries because the first response timed out.
• The user opens the link in two browser tabs.
• A mail scanner follows the link before the user does.
• A queue redelivers the confirmation event.
• A test runner repeats a failed step after a partial cleanup.

These are not exceptional failures. They are normal distributed-system behavior. The service should decide once whether a verification attempt can change account state, then make every later request return a predictable result.

The same thinking applies when a QA engineer checks a message in a free temporary email inbox. The inbox can help inspect delivery, but it must not become the source of truth for whether an account is verified.

Store attempts instead of only tokens

Create one row per issued verification attempt. Keep the token digest rather than the raw token, so a database read does not immediately grant access.

CREATE TABLE email_verification_attempts (
  id uuid PRIMARY KEY,
  user_id uuid NOT NULL REFERENCES users(id),
  token_digest bytea NOT NULL UNIQUE,
  status text NOT NULL CHECK (status IN ('pending', 'consumed', 'expired', 'revoked')),
  expires_at timestamptz NOT NULL,
  consumed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX email_verification_pending_idx
  ON email_verification_attempts (expires_at)
  WHERE status = 'pending';

The status is deliberately explicit. A consumed token and an expired token might both be rejected, but they have different operational meanings. The first can indicate a double click; the second may indicate a slow delivery or a client clock issue.

Do not use a long-lived verified = false flag as a substitute for the attempt record. That flag describes the account, while the attempt describes the security decision that led to a state change. Mixing those concerns makes incident review more confusing.

Make the REST API idempotent

The confirmation handler should claim the attempt and update the user in one transaction. A simplified flow is:

1. Hash the presented token with the same algorithm used at creation.
2. Select the pending attempt with a row lock.
3. Reject it if it is missing or expired.
4. Mark the attempt as consumed.
5. Mark the user as verified.
6. Commit both changes together.

The row lock is important. Two requests can find the same pending row, but only one should claim it. The other request should see a consumed attempt and return a stable response such as already_verified rather than running the account transition again.

For an API client, stable semantics are more valuable than pretending every duplicate is a server error. A 200 response for an already completed confirmation can be reasonable if the response does not reveal sensitive account information. The exact status code is a product decision; the one-time state transition is a backend invariant.

The endpoint should also have a clear configration for token lifetime, clock handling, and retry behavior. Put these in service configuration, not scattered across controller branches.

Use PostgreSQL to claim work safely

A transaction can claim an attempt with SELECT ... FOR UPDATE, then perform the state change:

BEGIN;

SELECT id, user_id, status, expires_at
FROM email_verification_attempts
WHERE token_digest = $1
FOR UPDATE;

UPDATE email_verification_attempts
SET status = 'consumed', consumed_at = now()
WHERE id = $2
  AND status = 'pending'
  AND expires_at > now();

UPDATE users
SET email_verified_at = COALESCE(email_verified_at, now())
WHERE id = $3;

COMMIT;

The application still needs to check affected-row counts and handle rollback errors. A database statement that ran without an exception does not always mean the intended state changed. This is a small detail, but missing it creates very strange support tickets.

For cleanup, a worker can expire pending rows in batches. It should use a bounded query and record how many rows it changed. A cleanup job should never delete all old verification data seperately from the account state without an audit decision.

Keep test fixtures isolated

Integration tests need the same ownership model. Give every run a unique ID and attach it to the user, attempt, and mailbox fixture. This prevents parallel CI jobs from consuming each other's messages.

For OTP tests, inbox snapshots for OTP tests are a useful example of preserving the message evidence needed to debug a failure. If those tests run in Kubernetes, run IDs for Kubernetes email jobs provide the same boundary at the job level.

Use a unique address or inbox per run where possible. A shared mailbox turns a test assertion into a search problem, and search results can be stale. Some teams call an address a temp org mail or a temp mailid during experimentation; the name is less important than recording ownership, expiry, and cleanup status.

Implementation checklist

• Store one row per verification attempt.
• Hash tokens before persisting them.
• Lock the pending row during confirmation.
• Update the attempt and user in one transaction.
• Distinguish consumed, expired, and revoked states.
• Make duplicate requests return deterministic results.
• Give every test run an ownership ID.
• Expire fixtures with bounded, observable cleanup jobs.
• Redact tokens and full email addresses from logs.

Q&A

Should a verification token be reusable?

No. The confirmation transition should be one-time. If the user retries after success, return a safe idempotent result without applying the transition again.

Should expired attempts be deleted immediately?

Usually not. Retain a limited amount of metadata for debugging and abuse analysis, then apply a retention policy. Deleting everything immediately can remove the evidence needed to understand delivery delays.

Is a temporary inbox enough for end-to-end testing?

It can verify delivery and message content, but it does not prove that the REST API handles replay correctly. Assert both: the message arrives, and the same token cannot perform the account transition twice.