Idempotent Email Verification APIs with PostgreSQL

Email verification looks like a small authentication feature until clients retry requests, workers deliver the same message twice, or a user opens an old link after requesting a new one. At that point, the endpoint is no longer just a token check. It is a distributed workflow with database state, timing rules, and a public API contract.

This post describes a replay-safe design for an email verification API backed by PostgreSQL. The goal is not to make every request succeed. The goal is to make repeated requests produce predictable outcomes without activating an account twice, leaking token state, or creating a confusing support trail.

Why verification endpoints become non-idempotent

A naive implementation usually does three things in one handler:

1. Read a token from the database.
2. Mark the user as verified.
3. Delete or invalidate the token.

That sequence has several race windows. A mobile client can retry after a timeout while the first request is still committing. Two browser tabs can submit the same link at nearly the same moment. An email provider can also deliver a message more than once. If the handler performs a read followed by a separate write, both requests may see the token as valid.

The result is often not an obvious security incident. It is a messy API: one request returns 200, another returns 404, and the logs cannot explain whether the user was already verified or the token never existed. The failure is even harder to diagnose when a test address such as temp gamil com slips through input validation and becomes part of an ambiguous fixture.

Idempotency gives the endpoint a stable answer for a repeated operation. It does not mean that every response has the same HTTP status. It means the state transition is applied at most once, and the client can understand the resulting state.

Model the verification attempt in PostgreSQL

Start with an explicit record for each verification attempt. A compact schema might look like this:

CREATE TABLE email_verification_tokens (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    token_hash BYTEA NOT NULL UNIQUE,
    expires_at TIMESTAMPTZ NOT NULL,
    consumed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX email_verification_tokens_user_idx
    ON email_verification_tokens (user_id, created_at DESC);

Store a hash of the token, not the raw token. The raw value belongs only in the email link and in the short-lived request. A database read should never be able to recover a usable credential. The unique constraint also turns accidental duplicate token creation into a visible database error instead of silent state corruption.

The user record needs a durable verification state, for example unverified, verified, or suspended. Keep that state separate from the token row. A token is an authorization artifact with an expiry; it is not the source of truth for the account.

When consuming a token, use one transaction and lock the relevant rows. A conditional update is especially useful:

UPDATE email_verification_tokens
SET consumed_at = now()
WHERE token_hash = $1
  AND consumed_at IS NULL
  AND expires_at > now()
RETURNING user_id;

If this returns one row, the request won the consumption race. If it returns no rows, query the user state and the token metadata separately inside the transaction so the API can distinguish an expired token from an already completed verification. That distinction is valuable for clients and support, even when the public error body stays intentionally general.

Make the REST API replay-safe

For a link-based flow, GET /v1/email-verifications/{token} can be convenient, but it complicates prefetching by browsers, mail scanners, and security tools. A safer design is to let the link open a confirmation page and have the page submit the token to a state-changing endpoint:

POST /v1/email-verifications/consume
Idempotency-Key: 9f8b1e7c-...
Content-Type: application/json

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

The idempotency key is useful when the client does not know whether a timeout happened before or after the commit. Persist a short-lived record containing the key, request fingerprint, response status, and a redacted response body. If the same key arrives with a different token, return 409 Conflict; silently reusing a key for a different operation hides client bugs.

The response contract can be small and stable:

{
  "status": "verified",
  "message": "Email verification completed"
}

For an already verified account, return the same logical state. For an expired or unknown token, return a generic error and a correlation ID. Do not say whether a particular email address exists. That boundary reduces account enumeration risk while leaving engineers enough evidence to investigate the request.

The endpoint should also enforce a maximum request body, a bounded token length, and rate limits by IP and account. These controls belong at the API boundary, before expensive database work begins. In practise, a clear limit is easier to operate than a clever one that changes with every deployment.

Rotate tokens without creating races

Resending a verification email should not invalidate a new token accidentally. Generate the replacement token, insert its hash, and expire older unconsumed tokens for that user in one transaction. Define the ordering explicitly:

1. Lock the user or use a consistent advisory lock.
2. Mark older active tokens as consumed or superseded.
3. Insert the new token hash and expiry.
4. Commit before enqueueing the email delivery job.

The worker should receive a token identifier or an opaque delivery reference, not a raw token copied into multiple queue records. If the delivery fails, the account remains unverified and the user can retry. If the delivery succeeds twice, both messages still point to a controlled token policy.

Operationally, it helps to record event names such as verification_requested, verification_consumed, and verification_rejected. A useful recovery process starts with evidence: this guide on keeping email operations reviewable during a recovery drill shows why delivery context should survive the original request. For data handling decisions, a privacy review for email event pipelines is a useful companion.

Observe failures without leaking secrets

Never log the raw token, the full verification URL, or the complete email address. Log a request ID, user ID when it is already authenticated, token record ID, outcome category, and latency. Hashing or truncating a token fingerprint can help correlate retries, but it must not become a second credential.

Metrics should cover successful consumption, expired tokens, replayed tokens, rate-limit responses, and database conflicts. Alert on changes in those ratios rather than on one isolated request. A sudden rise in expired tokens might indicate delayed email delivery; a rise in conflicts might indicate a broken retry loop.

Questions engineers usually ask

Should a consumed token return an error?

Usually, return the account's current verified state when the caller can be safely associated with that account. For an anonymous caller, use a generic response that does not reveal account existence. The important property is consistent state, not a particular status code.

How long should an idempotency record live?

Long enough to cover normal client retries and queue delays, often minutes to a few hours. The exact window belongs in the API contract. Keep the record shorter than the useful lifetime of the verification token unless there is a strong reason to replay the original response.

Is PostgreSQL enough at high scale?

For many services, yes. A conditional update, appropriate indexes, and short transactions are a solid baseline. Add a cache only after measuring a real database bottleneck; correctness should remain anchored in PostgreSQL.

An email verification API is easier to maintain when its state transition is explicit, its retries are expected, and its logs describe outcomes without collecting secrets. That combination makes authentication behavior safer for users and far less mysterious for the engineers on call.