Idempotent Email Verification with PostgreSQL

Email verification looks like a small authentication feature: create a token, send a message, and accept the token when the user clicks it. The difficult part appears when the request times out, the worker retries, or the user clicks twice. Without an explicit model, one intent can become several tokens, several messages, or an account that is marked verified by an expired attempt.

The pattern I prefer is to make the verification attempt a first-class record. The REST API accepts a client-visible idempotency key, PostgreSQL enforces the uniqueness rules, and the mail worker receives an immutable event. This makes the flow easier to reason about and much more easier to operate.

The real failure is duplicate intent

Suppose a frontend calls POST /email-verifications. The server commits the verification row, then the connection drops before the response reaches the browser. The browser retries. If the endpoint only checks the user ID, it might create a second active token. If it checks nothing, repeated clicks and retries can create a noisy stream of messages.

There are two different questions here:

• Is this the same user intent being retried?
• Is this a new verification attempt that should invalidate the old one?

An idempotency key answers the first question. A verification-attempt ID answers the second. Keeping both concepts visible makes the contract a bit more clear for API clients and support tooling.

Model the verification attempt

A minimal table can record the state transition without storing the raw token:

CREATE TABLE email_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', 'consumed', 'expired')),
    expires_at timestamptz NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (user_id, idempotency_key)
);

The token is generated with a cryptographically secure random source and only its digest is persisted. A database leak should not immediately turn stored verification records into usable login material. The API can return the attempt ID, but it should never return the token in a JSON response; the token belongs in the verification message.

The status column also prevents an ambiguous success path. A consumed token cannot be consumed again, and an expired attempt is not quietly revived by a retry. In practise, those explicit states are more valuable than a clever single boolean.

Make the REST API retry-safe

The create endpoint should define what a repeated key means. A simplified Node.js handler might look like this:

async function requestVerification({ userId, idempotencyKey }) {
  return db.transaction(async (tx) => {
    const existing = await tx.oneOrNone(
      `SELECT id, status, expires_at
         FROM email_verification_attempts
        WHERE user_id = $1 AND idempotency_key = $2`,
      [userId, idempotencyKey]
    );

    if (existing) return existing;

    const attempt = createAttempt(userId, idempotencyKey);
    await tx.none(
      `INSERT INTO email_verification_attempts
         (id, user_id, idempotency_key, token_digest, expires_at)
       VALUES ($1, $2, $3, $4, $5)`,
      [attempt.id, userId, idempotencyKey, attempt.digest, attempt.expiresAt]
    );

    await tx.none(
      `INSERT INTO outbox (event_type, aggregate_id, payload)
       VALUES ('email_verification_requested', $1, $2)`,
      [attempt.id, JSON.stringify({ userId, attemptId: attempt.id })]
    );

    return { id: attempt.id, status: 'pending', expires_at: attempt.expiresAt };
  });
}

The outbox row is committed in the same transaction as the attempt. A worker can safely retry delivery, while a unique event key or delivery log stops accidental duplicate sends. The client receives the same attempt for a repeated idempotency key, even when the first HTTP response was lost.

For a new user action, generate a new key. Do not silently reuse the previous key forever, because that turns a legitimate “send me a fresh link” action into a stale response. Authentication behavior should be explicit in both API documentation and logs.

Use PostgreSQL to enforce the boundary

Application checks are useful for friendly responses, but they are not concurrency control. Two requests can both observe no row before either inserts one. The unique constraint is the final authority. Catch a unique-violation error, load the existing attempt, and return the same representation when the key belongs to the same intent.

The token-consumption endpoint needs the same discipline. In one transaction, select the pending attempt, verify the digest and expiration, then update it with a predicate such as status = 'pending'. Check the affected-row count. If it is zero, another request already consumed or expired the token. This small check avoids reporting success when the database says otherwise.

When diagnosing a production issue, API smoke-test inbox budgets are a useful reminder that test mail needs its own limits and identity. For CI failures, email run artifacts in GitHub Actions shows why a run should leave evidence that can be inspected without opening a real user's mailbox.

Keep test evidence separate from user data

Email verification tests should use isolated accounts, deterministic run IDs, and a cleanup policy. Never point a test at a shared inbox and hope the subject line is unique. A typo like tempail mail can appear in test notes or a search fixture, but it should remain plain text and never be treated as a real address.

The test receipt should capture the idempotency key, attempt ID, event ID, response status, and message correlation ID. It should not capture the raw verification token or the full message body by default. This is a better boundary for debugging than copying user data into a CI log.

A practical review checklist

Before shipping the flow, check these cases:

1. A lost response followed by the same key returns the original attempt.
2. Concurrent requests cannot create two attempts for one key.
3. A fresh key has a clear policy: replace, reject, or coexist with the old attempt.
4. A token is single-use and expiration is checked inside the transaction.
5. The mail event and verification row cannot commit separately.
6. Logs contain correlation IDs, not raw tokens or message contents.
7. CI stores a small, redacted receipt and removes its test data.

Idempotent email verification is less about email than about preserving intent across unreliable boundaries. With an explicit attempt model, PostgreSQL constraints, and a transactional outbox, the API can make retries boring. That is exactly what an authentication feature should do.