Replay-Safe Email Verification with PostgreSQL

Email verification is often treated as a small feature: create a token, send a link, and mark the account as verified. In a real backend, the difficult part starts when the user clicks twice, a mail client prefetches the URL, or two browser tabs submit the same token together.

The useful design goal is not merely “the link works.” It is a replay-safe authentication workflow with explicit state, predictable REST API responses, and database rules that remain correct under concurrency.

Why verification links get replayed

A verification link can be opened more than once for normal reasons. A user may refresh the result page, forward the message to another device, or click while the first request is still finishing. Some security products also inspect links before the user does.

If the endpoint only checks that a token exists, several problems appear:

• an expired token can be accepted after a race;
• a second request can change an already completed flow;
• the response gives no clear distinction between “already verified” and “invalid token”;
• cleanup jobs can delete a token while a valid request is using it.

The same edge cases show up when a team uses a use and throw email inbox for a test account. A test that passes once is not proof that the state transition is safe.

Model the token as a database state machine

Start with a small table that records the lifecycle rather than storing only a hash and an expiry timestamp.

CREATE TYPE verification_status AS ENUM ('pending', 'consumed', 'revoked');

CREATE TABLE email_verification_tokens (
  id uuid PRIMARY KEY,
  user_id uuid NOT NULL REFERENCES users(id),
  token_hash bytea NOT NULL UNIQUE,
  status verification_status NOT NULL DEFAULT 'pending',
  expires_at timestamptz NOT NULL,
  consumed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);

The raw token belongs only in the URL. Store a digest, then compare a digest of the presented value. This limits the value of a database leak and makes the storage contract clear.

Keep consumed as a real state. Deleting the row immediately makes it harder to explain a replay to a support engineer and harder to distinguish an old link from a malformed one. Retention can be handled by a separate job after the useful audit window.

Design an idempotent REST API

The endpoint can be POST /v1/email-verifications/consume, with the token in the request body. A POST makes the state-changing action explicit, while the link itself can still land on a browser page that submits the action.

{
  "token": "raw-token-from-the-link"
}

Useful response categories are:

• 204 No Content when a pending token was consumed successfully;
• 200 OK with an “already verified” result when the same token was safely replayed;
• 410 Gone when the token is expired, revoked, or consumed outside the supported replay window;
• 400 Bad Request when the token has an invalid shape;
• 429 Too Many Requests when repeated guesses exceed the request limit.

The exact status codes can vary by product, but the contract should be stable. Avoid returning a generic success for every input, because clients then cannot tell whether they should request a new message.

An idempotency key is useful for a client retry caused by a network timeout, but it should not replace token state. The token is the business identity of the verification action; the idempotency key is only a transport safeguard.

Use PostgreSQL constraints for concurrency

The important operation is a conditional update inside a transaction:

UPDATE email_verification_tokens
SET status = 'consumed', consumed_at = now()
WHERE token_hash = $1
  AND status = 'pending'
  AND expires_at > now()
RETURNING user_id;

If this returns a row, the request won the transition. If it returns no rows, fetch only enough information to decide whether the token is unknown, expired, or already consumed. Do not perform a separate “is it pending?” read and then update without a condition. That pattern has a race between the two statements.

After a successful transition, update the user in the same transaction. A foreign key and a unique digest constraint provide useful guardrails even when application code has a bug. It is a bit more easier to reason about a workflow when the database rejects impossible duplicates for you.

If the email event is sent from an outbox, create the verification token and outbox row in one transaction. The worker can then retry delivery without creating another token for every transient provider failure. This also keeps webhook email tests less noisy; the delivery attempt and its status have a durable record.

For broader test design, testing webhook emails without noisy inboxes is a useful related pattern. If the product blocks disposable domains, document appeal paths for disposable email blocks so legitimate test and support flows have a route forward.

Testing the unhappy paths

A good integration suite should run the same token through each meaningful state:

1. Consume a pending token and verify the user in one transaction.
2. Submit the same token twice concurrently and assert only one state transition occurs.
3. Submit an expired token and confirm no user update happens.
4. Revoke a token, then check that a new request cannot revive it.
5. Retry after a client timeout and check that the response is safe to interpret.
6. Send malformed, very long, and guessed token values through the rate limiter.

Use two database connections for the concurrent case. A unit test with a mocked repository will not expose a missing conditional clause. The check are most valuable when they exercise the real transaction boundary.

Test data should also be isolated from real inboxes. A dummy e mail address or a fake e mail com value may be fine as a fixture label, but it should never be allowed to become a production notification target. Make the environment obvious in logs, and redact token values in every environment.

Practical checklist

• Store a hash, not the raw verification token.
• Make pending, consumed, and revoked states explicit.
• Guard the update with status and expiry conditions.
• Keep user verification and token consumption in one transaction.
• Define stable responses for success, replay, expiry, and malformed input.
• Rate-limit token guesses and avoid logging secrets.
• Test concurrent requests against PostgreSQL itself.
• Retain enough audit data to explain failures, then clean it up deliberately.

Conclusion

Replay safety is a small database problem with a large effect on user trust. An explicit token state machine, a conditional PostgreSQL update, and a clear REST API contract make email verification easier to operate and easier to test. The result is not a complicated security framework; it is a few durable rules applied at the boundary where an external message becomes an authentication decision.

In practise, this approach also makes incident review calmer. You can see which transition happened, why a retry was accepted or rejected, and whether delivery or verification was the actual failure. That is much better than a single token row that simply vanished.