Idempotent Email Verification with PostgreSQL

Email verification looks like a simple two-step feature: create a token, send a message, then accept the token. In a real REST API, it is a small distributed workflow. The request can be retried, the email provider can deliver twice, and a user can click an old link after a newer verification attempt was created.

The useful boundary is not the email itself. It is the state transition recorded by the backend. PostgreSQL can make that transition explicit, while an idempotency key keeps repeated requests from creating a confusing pile of tokens.

Why email verification needs an idempotency contract

Consider POST /users/{id}/verification. A mobile client times out after the server commits, so it sends the request again. If every request creates a fresh token, the user may receive two messages and the first one may suddenly stop working. That feels random, but the API simply had no retry contract.

I prefer defining these rules before choosing a mail provider:

• A retry for the same logical request returns the same verification attempt.
• A new attempt invalidates older usable tokens for that user.
• A token is consumed once, inside a transaction.
• The API response does not claim delivery when only token creation succeeded.

Model the verification attempt in PostgreSQL

Keep the durable workflow in a table instead of encoding it in an email URL alone. A compact model might look like this:

CREATE TABLE verification_attempts (
    id              uuid PRIMARY KEY,
    user_id         bigint NOT NULL REFERENCES users(id),
    idempotency_key text NOT NULL,
    token_digest    bytea NOT NULL,
    status          text NOT NULL CHECK (status IN ('pending', 'sent', 'verified', 'expired')),
    expires_at      timestamptz NOT NULL,
    verified_at     timestamptz,
    created_at      timestamptz NOT NULL DEFAULT now(),
    UNIQUE (user_id, idempotency_key)
);

Store a digest rather than a raw token. The URL can contain the random token, but a database read should not immediately reveal a credential that is still usable. When the user submits the token, hash the presented value and compare the digest for the expected user and attempt.

The unique constraint is important, but it is not the whole idempotency implementation. Two concurrent requests can both observe no row before either inserts. Let PostgreSQL arbitrate the race with INSERT ... ON CONFLICT, then return the existing attempt when the same key is retried.

Do not use an email address as the idempotency key unless the product explicitly defines that behavior. The key should identify the client operation, not just the current profile field.

Make the REST API retry-safe

The creation endpoint should have a small, observable contract. Require an Idempotency-Key header, validate its length, and bind it to the authenticated user. A request from another user must never be able to replay an attempt by guessing the key.

The response can expose an attempt identifier and an expires_at value without exposing the token. If the mail send is asynchronous, return queued rather than pretending the message arrived.

For the confirmation endpoint, make the transaction do the ownership check and state transition together:

1. Lock the selected pending attempt.
2. Check the user, digest, status, and expiry.
3. Mark it verified and set verified_at.
4. Commit before returning success.

An already verified attempt can return a stable success response when the product wants confirmation to be safely repeatable. An expired or mismatched token should not reveal whether another valid token exists. The exact status code is a product choice, but the information boundary should stay consistant.

For test fixtures, keep setup deterministic too. A contract for reliable email test fixtures should create a known user, operation key, and expected attempt. Avoid making a dummy e mail address the source of truth for whether the API worked.

Keep delivery and token state separate

Email delivery is an external side effect. The verification attempt is backend state. Treating them as one transaction creates awkward failure modes: the database commits but the provider rejects the message, or the provider accepts it while the database transaction rolls back.

A practical design uses an outbox row created in the same PostgreSQL transaction as the attempt. A worker claims it, sends the message, and records a delivery result. On retry, it uses the attempt id and delivery id so the event has an audit trail.

That audit trail is useful when a provider reports success but a test inbox is late. A replayable run receipt is a helpful general pattern here: record the input identity, state changes, external result, and retry count seperately from the user-facing message.

If your workflow uses a service for disposable email, use it to observe delivery in tests, not to decide which token is valid. The API and database should remain correct even when the mailbox is delayed or unavailable.

A small implementation checklist

• Generate a cryptographically random token and store only its digest.
• Bind every attempt and idempotency key to one user.
• Use a database uniqueness rule for the retry race.
• Make token consumption one atomic state transition.
• Keep message delivery in an outbox or equivalent worker flow.
• Log attempt id, request id, and delivery id, but never the token.
• Test duplicate requests, concurrent requests, expired links, and a repeated confirmation.

The common failure is not a missing email template. It is an unclear contract between the REST API, PostgreSQL, and the delivery worker. Once those boundaries are explicit, retries become a normal path instead of a rare occurence that support has to investigate by hand.

Common questions

Should every new verification request invalidate the previous token?

Usually yes for one user and one purpose, because it limits the number of active credentials. If the product supports multiple devices or addresses, model that scope directly instead of silently sharing one global token state.

Is an idempotency key enough to prevent duplicate emails?

No. It deduplicates the API operation. The delivery worker also needs an event or delivery identifier so a worker retry can be understood and reconciled. The two keys solve different problems.

What should a test assert first?

Assert the database state and API response before asserting the inbox message. Then check that the message contains the expected link and that using the link causes exactly one verified transition. This keeps a slow provider from hiding a reponse or transaction bug.