Versioned Reset Tokens in PostgreSQL

Password reset flows look routine until the first real retry storm lands. A mobile client resubmits, a worker drains late, or a user clicks the older link after a newer one was issued. If the backend does not make token ownership explicit, support ends up chasing inbox screenshots instead of database facts. I have seen this enough times that I now treat reset email delivery as a state-management problem first and a mail problem second.

The pattern here is close to the same lessons behind PostgreSQL outbox checks and isolated inbox validation: one user intent should map to one current backend truth. For Authentication systems that already lean on PostgreSQL, versioned reset tokens are a simple way to keep that truth inspectable.

Why password reset flows drift under retries

The common failure shape is boring, which is why it slips through reviews:

1. POST /password-reset creates a token row.
2. An email job is queued in a separate step.
3. The client retries before the first response is observed.
4. A second token or second job is created with just enough timing difference to confuse everyone.

Nothing looks dramatic at first. The API still returns success. The worker still sends. The inbox still shows a reset email. But later you discover the link the user clicked was already obsolete, or that two links were valid longer than intended. That is not just messy, its a contract problem.

I prefer deciding one rule clearly: every reset attempt gets a token version, and only the newest committed version is allowed to survive as current. Older versions can exist for audit, but they should stop being actionable immediately.

The versioned token contract I prefer

My default contract is pretty simple:

1. Lock the user reset state in one transaction.
2. Increment a version whenever a new reset intent is accepted.
3. Store the token hash and expiry beside that version.
4. Emit one outbox event keyed by user_id plus version.
5. Let the worker send only if the referenced version is still current.

This does two useful things. First, retries become inspectable because you can answer "which token version won?" with a query instead of a guess. Second, worker lag becomes tolerable. Even if an older job wakes up late, it can re-check state and skip itself.

For backend teams, that skip step is where a lot of reliability comes from. People sometimes think on conflict do nothing is the whole story. It helps, but it does not answer whether the event still matches the latest reset truth. You need that second check or you eventualy trust queue timing more than data.

A PostgreSQL schema that makes stale mail obvious

This is the smallest shape I tend to like:

create table password_reset_state (
  user_id bigint primary key,
  token_hash text not null,
  version integer not null,
  expires_at timestamptz not null,
  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 is straightforward:

await db.tx(async (trx) => {
  const state = await trx.oneOrNone(
    `select version
       from password_reset_state
      where user_id = $1
      for update`,
    [userId]
  );

  const nextVersion = (state?.version ?? 0) + 1;

  await trx.none(
    `insert into password_reset_state (user_id, token_hash, version, expires_at)
     values ($1, $2, $3, $4)
     on conflict (user_id) do update
       set token_hash = excluded.token_hash,
           version = excluded.version,
           expires_at = excluded.expires_at,
           updated_at = now()`,
    [userId, tokenHash, nextVersion, expiresAt]
  );

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

The worker should read password_reset_state before delivery and compare versions. If the payload says version 4 but the table is now version 5, skip send and mark the event stale. It is a tiny read, but it makes debugging much less anoying later.

What I validate in staging before calling it done

I still use inbox checks in staging, but only as delivery evidence. The authoritative assertions live in backend state:

• a retried reset request should leave one current token version
• an older outbox event should not send after supersession
• the clicked link should map to the latest committed version
• logs, worker events, and audit rows should agree on the same version

This is where temporary inbox tooling can help without becoming the source of truth. A temporary email or a disposable email address generator is useful when I need to prove the message rendered and arrived. But I do not treat the inbox as the contract itself. If a team starts saying "the tepm mail com check looked okay" while the database story is fuzzy, that is usualy a sign the test setup is compensating for a backend gap.

One more practical rule: keep reset TTL short and version checks explicit in logs. When support gets a stale-link report, you want to know whether the user clicked version 2 after version 3 had already replaced it. That answer should be cheap to get, not buried in a worker trace from six systems ago.

Q&A

Should older reset tokens be deleted immediately?

Not necessarily. I prefer keeping them for audit for a short period, but marking them unusable as soon as a newer version becomes current.

Is one table enough for this flow?

For many services, yes. A single current-state table plus an outbox table keeps the flow simple and debuggable. If volume grows, the contract still holds.

Does this only matter for password resets?

No. The same pattern works for magic links, verify-email flows, and invite acceptance. Password reset just makes the failure mode show up faster because users retry when they are already frustrated.