PostgreSQL Audits for Email Change APIs

Changing an account email looks like a small feature, but it creates one of the messiest edges in Authentication. You are replacing an identifier, sending a verification link, preserving recovery trails, and making sure retries do not leave support guessing what happened. I keep solving this with a PostgreSQL audit model instead of treating the mailbox as the source of truth.

This matters even more when staging checks involve a facebook temp email or a quick tempmailso inbox to confirm delivery. Those inboxes are useful, but they should validate the last mile only. The system still needs a durable record of which address was requested, who approved it, and whether the token that reached the inbox still matched the latest request. Without that, the backend gets fuzzy fast.

Why email change APIs are riskier than signup

Signup flows are usualy append-only. Email changes are not. You already have a live account, an old verified address, and a new pending address that might never complete verification. If the client retries twice, or a user clicks an old link after requesting a new one, the API can drift into confusing states unless the model is explicit.

The cases I watch most are:

1. repeated PATCH /account/email calls during slow mobile networks
2. stale verification links being opened after a newer request
3. support needing to explain which request actually won
4. staging notes that mention things like temp gamil com or tempail, which tells me the verification trail is too manual

That last point sounds small, but it often reveals weak observability. If engineers are searching inboxes before they can answer a database question, the design is probly upside down.

The PostgreSQL records I keep for every email change

I prefer a dedicated table for change intents rather than overloading the user row with pending fields only:

create table email_change_requests (
  id uuid primary key,
  user_id uuid not null,
  old_email text not null,
  new_email text not null,
  status text not null,
  token_hash text not null,
  requested_at timestamptz not null default now(),
  expires_at timestamptz not null,
  verified_at timestamptz,
  superseded_by uuid,
  request_key text not null unique
);

request_key is the important guardrail. I generate it from the authenticated user id plus a client operation id. That gives me one durable handle for retries, similar in spirit to immutable retry receipts. If the same request is replayed, PostgreSQL gives me the existing row instead of creating competing email-change attempts.

I also keep the old email on the request row. That looks redundant until a support ticket lands three weeks later and somebody needs to confirm exactly what was replaced.

A REST API flow that survives retries and stale links

The request path is simple on purpose:

await db.tx(async (trx) => {
  const existing = await findByRequestKey(trx, requestKey);
  if (existing) return existing;

  const req = await insertEmailChangeRequest(trx, {
    userId,
    oldEmail: currentEmail,
    newEmail,
    tokenHash,
    requestKey,
    expiresAt: addMinutes(new Date(), 20)
  });

  await enqueueVerificationEmail(trx, req.id);
  return req;
});

On verification, I do not update the user row from the token alone. I first load the request by token hash, confirm it is still pending, confirm superseded_by is null, and only then swap the email in the same transaction that marks the request verified. If a newer request exists, the old link resolves to a clear conflict instead of silently mutating the wrong state.

That is the part many teams skip. They validate expiry, but not freshness. For email changes, freshness matters more than elegance.

How I test the flow without trusting inboxes alone

My test checklist has two layers. First I inspect database truth:

1. create the email change request through the public REST API
2. confirm one email_change_requests row exists for the request key
3. assert older pending rows are marked superseded when a newer request is made
4. verify the user row changes only after the newest token is consumed

Then I inspect delivery truth through a temporary inbox. A facebook temp email can help when you need to validate content, timing, and whether the right person-facing copy shipped. That is also where tempmailso can be useful in staging. But I do not let the inbox answer backend questions it cannot answer. For template rendering and delivery smoke coverage, I like pairing this with lightweight email template smoke checks.

When teams rely on inboxes alone, they miss two important facts: whether the request was already superseded, and whether a retry reused an existing operation or created a second one. PostgreSQL is better at both answers, and it does not forget what happend.

Q&A

Why not store pending_email directly on the user row?

You can for very small apps, but it hides history. Once retries, support, and stale links show up, a request ledger is much easier to reason about.

Should the verification token be unique per request?

Yes. One token per request, one request per request key. That keeps invalidation rules obvious and makes audits less messy.

What about rate limits?

Apply them at the authenticated user boundary and at the destination email boundary. Otherwise attackers can grind through address changes or flood one mailbox with verification messages.

For me, the win is not just security. It is operational clarity. A PostgreSQL request ledger gives the Authentication flow a stable memory, keeps the REST API honest under retries, and turns temporary inboxes into a useful check instead of the only evidence you have.