PostgreSQL Idempotency Keys for Email APIs

Email verification looks like a simple endpoint: accept an address, create a token, send a message, and return 202. In a real REST API, the difficult part starts when the client retries, two browser tabs submit together, or a worker delivers the same message twice.

The reliable approach is to make the verification attempt a first-class PostgreSQL record. An idempotency key then describes the client operation, while a database constraint decides whether that operation has already been accepted. This is useful for tests that use a tempmail disposable inbox, but the same design also protects production signup flows.

Why email verification needs an attempt ledger

An email verification request has several different events:

1. The API accepts a request.
2. PostgreSQL records an attempt and its token digest.
3. A worker claims the attempt.
4. The email provider accepts or rejects delivery.
5. The user submits the token.

These events should not be represented by one mutable users.email_verified flag. That flag tells you the final state, but not whether a retry created two tokens, whether a worker sent an old token, or why a message was delayed.

The API need a durable receipt for each logical attempt. A compact ledger can answer those questions without storing the raw token or the full email body. It also gives a test runner something concrete to assert instead of guessing from a mailbox that may still be catching up.

Search phrases such as “fake e mail com” and “dummy e mail” often appear in test-data discussions. Treat those values as untrusted input, just like any other address, and keep the audit record free of unnecessary message contents.

Define idempotency at the API boundary

The client should send an idempotency key that identifies one user intent, not one network packet:

POST /v1/email-verifications
Idempotency-Key: signup-9b2d
Content-Type: application/json

{"user_id":"u_1842","email":"dev@example.test"}

The server should bind that key to the operation scope. A key reused with a different user or email must not silently return the old result. Store a request fingerprint, such as a SHA-256 digest of the normalized payload, and return a conflict when the same key is presented with different input.

On the first request, the service inserts an attempt and returns a response such as 202 Accepted. On a retry, it reads the existing record and returns the same logical result. The response body does not need to expose whether the email provider has delivered the message; that is a separate state which can change later.

Do not make the idempotency check only an application-level SELECT. Two requests can both observe no row before either insert commits. The unique index is the final authority, and the transaction should handle its conflict deliberately.

Model the PostgreSQL state transition

A practical table keeps ownership of the operation explicit:

CREATE TABLE email_verification_attempts (
    id              bigserial PRIMARY KEY,
    user_id         bigint NOT NULL,
    idempotency_key text NOT NULL,
    request_hash    text NOT NULL,
    token_digest    text NOT NULL,
    status          text NOT NULL CHECK (status IN ('queued', 'sent', 'used', 'expired')),
    created_at      timestamptz NOT NULL DEFAULT now(),
    sent_at         timestamptz,
    used_at         timestamptz,
    UNIQUE (user_id, idempotency_key)
);

The token itself should be generated with a cryptographically secure source and stored only as a digest. When the user submits a token, compare its digest against the active attempt, check its expiry policy, and update the row in one guarded statement. A second successful verification should become a harmless no-op rather than issuing another side effect.

If the product allows only one active token per user, add a partial unique index for that rule. Keep it seperate from the idempotency constraint: one rule prevents duplicate intent, while the other controls how many current credentials exist.

Keep retries and delivery separate

The API transaction should record intent and enqueue work through a durable mechanism. It should not wait for an email provider before responding. A worker can claim queued rows, attach a provider message ID, and retry delivery with bounded backoff.

There are two useful boundaries here. The request boundary answers, “Did I accept this operation?” The delivery boundary answers, “Did the provider accept this message?” Mixing them makes a temporary provider timeout look like a duplicate signup.

When several workers can see the same queue, use a lease or row-lock strategy with a short ownership window. The lease must be renewed carefully; otherwise a slow worker and a replacement worker may both send. Patterns such as concurrency keys for email API checks are helpful when designing that boundary. For client-facing signup changes, versioned invite email contracts also show why the payload and message format deserve explicit ownership.

The result are easier to diagnose when every transition emits a correlation ID, attempt ID, and provider ID. Redact addresses and tokens in normal logs. A dashboard can show counts and latency without becoming a copy of the inbox.

A small implementation sequence

Start with a narrow implementation:

1. Normalize the email and validate the request.
2. Hash the payload and check the idempotency key inside a transaction.
3. Insert the attempt using the unique constraint as the concurrency guard.
4. Commit before the worker sends anything.
5. Let the worker claim, send, and record delivery status.
6. Verify submitted tokens with one conditional update.
7. Expire old rows and retain only the audit data the product needs.

This sequence makes failure recovery boring, which is a good outcome. If a client does not recieve the response, it can retry with the same key. If the worker dies after provider acceptance, the provider ID and lease policy guide reconciliation. If a test fails, the attempt ledger explains which transition was missing.

Operationaly, measure duplicate-key conflicts, queue age, delivery latency, token failures, and expired attempts. These signals reveal different classes of problems; one overall “email success” metric hides too much.

Questions engineers usually ask

Should the idempotency key be generated by the server?

Usually the client or calling service generates it because only that caller knows that two retries belong to one intent. The server should still enforce length, character, and retention limits.

Should a retry resend the email?

Not automatically. A retry of the HTTP request should return the recorded operation. A separate resend operation can create a new attempt under a deliberate policy, with its own rate limit and audit trail.

What happens when the database insert succeeds but the worker never runs?

The queued row needs a lease timeout and a monitor. A repair job can safely reclaim work whose lease expired. That is more predictable than trying to infer state from provider logs alone.

Idempotency is not just a convenience for flaky clients. It is the contract that connects a REST API, PostgreSQL, background workers, and authentication state. Design that contract first, and email delivery becomes a recoverable subsystem instead of a source of duplicate tokens and confusing support tickets.