Replay-Safe Verification with PostgreSQL

Email verification looks simple until a request times out at the worst possible moment. The API may have stored a token, queued a message, and then lost the connection before returning 201 Created. The client retries because it cannot tell whether the operation failed or only the response was lost.

That retry is not a new intent; its a new delivery attempt. A useful backend design makes both attempts converge on one verification workflow. The result is fewer duplicate messages, fewer invalidated tokens, and a much clearer support trail.

This pattern is also valuable in test environments. A disposable email inbox, for example, should be tied to one logical verification run instead of being recreated every time a worker sees a transient error. The same data boundary that protects production users makes automated tests easier to reason about.

The replay problem in verification APIs

Imagine this request:

POST /v1/email-verifications
Idempotency-Key: signup-tenant-42-8f14c7
Content-Type: application/json

{"email":"[e-mail]"}

The idempotency key represents the logical signup operation, not the individual network attempt. If the server receives this request twice with the same tenant, key, and payload, it should return the same logical result. It should not mint a second token merely because the first HTTP response was hiden by a timeout.

The key needs a scope. At minimum, include the tenant or user identity in the lookup. A global key such as signup-123 can collide across customers, while a key scoped only to the email address can accidentally merge two intentional verification flows.

Put the idempotency boundary at the API

Validate the request before creating any side effect. Normalize the email for comparison, reject an invalid key, and calculate a request fingerprint from the fields that define the operation. Store the fingerprint with the idempotency record.

A repeated key with the same fingerprint can return the stored response. A repeated key with a different fingerprint should produce 409 Conflict. Quietly changing the meaning of an existing key creates an incident that is difficult to reconstruct later.

The record should preserve enough response data to replay the result:

• tenant or user scope;
• idempotency key;
• request fingerprint;
• verification ID;
• response status and a small response body;
• creation and expiration timestamps.

Do not store a raw verification token in a general-purpose idempotency table. Keep secrets in the verification record, preferably as a digest, and make the replay response contain only the fields the client originally received.

For privacy-sensitive test flows, this boundary should also identify the owner and expiry of the inbox fixture. A short note about privacy boundaries for email checks is a useful reminder that test data is still data, even when the address is not a customer mailbox.

Model verification as durable state

Treat the verification process as a small state machine instead of a boolean that workers update from memory. A practical set of states is:

created -> send_pending -> sent -> verified

with explicit failure and expiry paths such as send_failed and expired.

Each transition should record who or what caused it, when it happened, and the provider message ID if one exists. The transition table are the durable boundary between an API request and an asynchronous worker. If a worker crashes after the provider accepts a message, the next worker can inspect the state rather than blindly sending again.

Keep transitions monotonic where possible. A late send_failed event must not move an already verified address backwards. If a business rule allows a new verification after expiry, create a new attempt ID instead of reusing the old row.

Use PostgreSQL to make duplicates boring

Application checks such as SELECT ...; if no row, INSERT ... are not enough under concurrent requests. Two API instances can both observe the gap and both insert a record. Let PostgreSQL enforce the invariant:

CREATE UNIQUE INDEX verification_idempotency_scope_key
ON verification_requests (tenant_id, idempotency_key);

CREATE UNIQUE INDEX verification_active_email
ON verification_requests (tenant_id, normalized_email)
WHERE state IN ('created', 'send_pending', 'sent');

Use a transaction for the idempotency insert and the verification row creation. On a unique violation, fetch the existing row and compare its request fingerprint. Matching fingerprints take the replay path. Mismatches return 409; they should never be silently overwritten.

For the worker, claim work with a lease or FOR UPDATE SKIP LOCKED. A lease gives a crashed worker time to recover without allowing two workers to send forever at the same time. It does not make an external email provider transactional, so the provider message ID and an attempt number should be recorded whenever available.

Make the worker replay-safe

The worker should follow a narrow sequence:

1. Load the verification attempt and lock or lease it.
2. Check whether the state already proves that the send happened.
3. If it is still eligible, call the provider with a stable deduplication key when the provider supports one.
4. Persist the provider result and transition the state in one database transaction.
5. Release the lease and emit a structured event.

If the process dies between steps 3 and 4, the next run faces an ambiguity: the message may have been sent. That is why a reconciliation job matters. Search provider events by the stable attempt ID, then mark the database from evidence. Sending immediately again may create a duplicate; waiting forever may leave a user stuck.

The same approach makes test runs more useful. A run receipt for disposable email tests can record the attempt ID, expected subject, observed message ID, and cleanup status. Small evidence like this is often more usefull than a long CI log.

Observability and operational checks

Log the tenant-safe verification ID, idempotency key hash, attempt number, state transition, and correlation ID. Avoid logging the token or complete email content. Metrics should separate duplicate requests, provider failures, lease expirations, and reconciliation outcomes.

Useful alerts include:

• a growing number of send_pending rows older than the normal queue delay;
• repeated fingerprint conflicts for one client;
• verification attempts whose lease expires more than once;
• provider messages without a matching database transition.

In practise, these signals make a timeout incident much less mysterious. The question changes from “why did the user get two emails?” to “which attempt crossed the provider boundary without a committed transition?”

A practical checklist

• Scope every idempotency key to a tenant or user.
• Store and compare a request fingerprint.
• Enforce uniqueness in PostgreSQL, not only in application code.
• Keep verification attempts separate from idempotency records.
• Use explicit, monotonic states and short leases.
• Record provider IDs without storing verification secrets in logs.
• Reconcile ambiguous sends before issuing another message.
• Give test fixtures an owner, expiry, and run receipt.

The goal is not to pretend that distributed systems never retry. The goal is to make a retry ordinary: one logical verification, one durable state history, and a response that can be replayed safely.