Idempotent Verify Email APIs in PostgreSQL

Verify-email endpoints look simple until retries show up. A user taps resend twice, the client retries on a slow network, and suddenly your service has two valid links in flight or two emails racing through the queue. That is why I treat verification mail as an API contract problem first, not just a delivery problem.

I like starting from the same lessons behind duplicate signup email prevention and preview inbox isolation: one user action should map to one clear backend state transition. For Authentication flows, PostgreSQL is usualy the easiest place to enforce that contract because it already owns consistency, ordering, and conflict handling.

Why verify-email endpoints are easy to duplicate

The common failure mode is split ownership:

1. The API updates users.email_verification_token.
2. A queue job is emitted in a separate step.
3. A retry repeats the API call before the first job finishes.
4. Two messages now describe slightly different truth.

Everything can still look healthy in logs. The API returns 202, the worker sends, and the UI gets a nice success toast. But later, support sees links that fail, or a stale email is clicked after a newer one was issued. Thats not a mail provider problem. It is a backend contract leak.

For resend flows, I prefer deciding one thing explicitly: is a repeated request supposed to reuse the same active verification intent, or supersede it with a newer one? Either choice is fine, but the system should say so in data, not in vague worker timing.

The PostgreSQL contract that keeps retries safe

The pattern I trust is:

1. Resolve the current verification state inside one transaction.
2. Either reuse the active token row or replace it with a higher version.
3. Insert exactly one outbox event keyed to that chosen version.
4. Commit once.
5. Let workers send only committed, non-superseded events.

That gives the API an idempotent surface. If the client repeats the same resend request with the same idempotency key, the database can point back to the same verification version instead of minting fresh chaos. If you intentionally issue a new token, the previous version becomes clearly outdated and workers can skip it.

In practice, I store both a stable verification purpose and a monotonicaly increasing version. The outbox payload includes user_id, purpose, and version. When the worker renders the email, it rechecks that the referenced version is still current before sending. This extra read is cheap, and it avoids a lot of weird edge cases.

A concrete schema for resend and dedupe

Here is a compact shape that works well:

create table email_verifications (
  user_id bigint primary key,
  token_hash text not null,
  version integer not null,
  expires_at timestamptz not null,
  last_idempotency_key text,
  updated_at timestamptz not null default now()
);

create table email_outbox (
  id bigserial primary key,
  topic text not null,
  dedupe_key text not null unique,
  payload jsonb not null,
  created_at timestamptz not null default now()
);

And the transactional write:

await db.tx(async (trx) => {
  const row = await loadVerificationForUpdate(trx, userId);
  const nextVersion = shouldReuse(row, idemKey) ? row.version : row.version + 1;

  await saveVerification(trx, {
    userId,
    tokenHash,
    version: nextVersion,
    expiresAt,
    lastIdempotencyKey: idemKey,
  });

  await trx.none(
    `insert into email_outbox (topic, dedupe_key, payload)
     values ('verify_email', $1, $2::jsonb)
     on conflict (dedupe_key) do nothing`,
    [
      `verify_email:${userId}:${nextVersion}`,
      JSON.stringify({ userId, version: nextVersion })
    ]
  );
});

The important part is not the exact schema. It is that your dedupe rule is visible and inspectable. When a bug report arrives, I want to answer "which version did we intend to send?" in one query, not after an hour of log archaeology.

What I assert in tests before I trust the flow

My verification tests are narrow but strict:

• one resend path produces one current version
• stale outbox events are ignored after supersession
• the clicked token maps to the latest committed version
• duplicate requests with the same idempotency key do not create extra sends
• retries with a new intent clearly invalidate the older link

For inbox checks, I still use a temporary inbox sometimes, but only as transport plumbing. If I need temporary disposable mail during staging checks, the real assertion is still about committed backend state. The inbox proves delivery; PostgreSQL proves correctness.

This is also where plain-text oddities like temp org mail tend to appear in team notes. People start inventing side channels when the underlying resend contract is fuzzy, which is a decent signal the API needs cleanup.

One practical rule: keep the polling window short and pin the recipient alias to the run ID. If a test can pass by grabbing any verification message from the last few minutes, it is too loose. That setup feels okay untill parallel CI jobs overlap and your suite starts "passing" for the wrong message.

Q&A

Should resend always create a new token?

Not always. Reusing an active token for a short TTL can reduce noise and user confusion. Just make sure the API behavior is documented and visible in data.

Is on conflict do nothing enough for idempotency?

No. It is useful for outbox dedupe, but you still need a clear rule for how the verification row itself is reused or replaced.

Do I need a message broker for this?

Not at first. A PostgreSQL outbox plus a worker is enough for many teams, and it is often easier to debug than a more fragmented setup.