Auth Email Outbox Checks in PostgreSQL

Authentication email tests often look green for the wrong reason. A verification or reset message appears in some inbox, the suite clicks a link, and everyone moves on. Then a retry storm, stale token, or queue delay shows up in CI and the same flow starts failing in ways that are annoyingly hard to explain.

In backend systems, I trust tests more when they prove ordering, not just delivery. That means one request creates one durable token state, one outbound intent, and one message that can be tied back to the same actor. I still borrow ideas from password reset token checks and from broader signup inbox isolation, but the useful step is making PostgreSQL own the sequencing.

Why auth emails drift out of sync

The bug pattern is usualy some version of this:

1. API writes the user row.
2. Worker enqueues or sends the email.
3. Token state is updated in a second step.
4. The test reads whichever email arrived first and assumes the flow is correct.

That sequence can pass localy and still be wrong. If the email is emitted before the newest token is committed, your test may validate an old link. If your inbox is shared, the poller may grab a message from another run. If both happen at once, the build says "passed" while Authentication behavior is quietly under-specified.

I also see teams store too little context around outbound mail. They know a message was sent, but not which transaction created it, which token version it belonged to, or whether the worker retried. Thats where false confidence comes from.

The PostgreSQL outbox pattern I actually trust

My baseline is boring on purpose:

1. Start a database transaction.
2. Create or update the auth token row.
3. Insert an outbox event that references the token version.
4. Commit once.
5. Let the worker send only committed outbox rows.

That sounds simple because it is. PostgreSQL is already excellent at durability and ordering, so I try to keep the contract there instead of spreading it across app memory and queue timing. For auth emails, the message should be a consequence of committed state, not a parallel guess.

The part I care about most is token freshness. If a user requests two password resets or two verification emails in a row, the worker should render only the latest valid token version. I like keeping a monotonic token_version or issued_at on the auth record, then carrying that value into the outbox payload. When the worker sends, it can refuse outdated payloads rather than happily shipping stale links.

What I assert in CI before I trust the build

My CI checks are stricter than "one email arrived":

• the outbox row points to the expected user and token version
• the recipient alias is unique to the current run
• exactly one relevant email is accepted for the assertion window
• the token in the email maps to the latest committed row
• a replayed click is idempotent or rejected in the documented way

That last assertion matters more than many teams think. Replay behavior is where weak auth flows get weird fast. A second click should not reopen state, and it should not revive a link that was already superseded. If the suite skips that case, you can miss a very real bug while still collecting a nice green badge.

I also keep the inbox polling window short. Twenty to sixty seconds is often enough for a staging path. Longer windows hide latency regressions and make debugging much slower, which is not a trade I like.

A compact schema and worker example

Here is the shape I tend to use:

create table auth_tokens (
  user_id bigint not null,
  purpose text not null,
  token_hash text not null,
  token_version integer not null,
  expires_at timestamptz not null,
  primary key (user_id, purpose)
);

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

And the transaction:

await db.tx(async (trx) => {
  const token = crypto.randomUUID();
  const version = await nextTokenVersion(trx, userId, "verify_email");

  await upsertAuthToken(trx, {
    userId,
    purpose: "verify_email",
    token,
    tokenVersion: version,
  });

  await trx.none(
    `insert into outbox_events (topic, payload)
     values ('send_auth_email', $1::jsonb)`,
    [JSON.stringify({ userId, purpose: "verify_email", tokenVersion: version })]
  );
});

It is not flashy, but it ages well. When a test fails, I can inspect the token row, the outbox row, and the received message without guessing too much. That makes backend incidents much less noisy, even when the wording in the email or the queue delay is a bit messy.

Where a temporary inbox fits without taking over the post

The inbox service is just one component in this design. For non-production checks, a temporary email generator can be useful because it gives each run a disposable alias and keeps shared inbox noise low. If I need to get temporary email for a staging smoke test, I treat it as plumbing, not as the test strategy itself.

What matters more is the ownership model: one alias per run, exact matching on recipient and purpose, and clean cleanup after the assertion. That is also where I sometimes see stray notes like temp mailid in issue threads, which is a funny clue that the team has already started inventing workarounds for inbox confusion.

For search visibility, yes, terms like temp mail so or PostgreSQL may help people find the write-up. But if the article is not useful to engineers maintaining auth flows, the keyword work does not realy matter.

Q&A

Should I test the worker directly?

Yes for narrow unit coverage, but not as your only proof. I want at least one end-to-end path that starts at the public API and ends with the committed state change.

Is one inbox per test enough?

Per run is better than per suite. Parallel workers and retries create overlap faster than people expect, and seperating aliases per run removes a lot of ambiguity.

Do I need PostgreSQL-specific features?

Not many. Standard transactions, a sensible uniqueness rule, and clear token versioning are enough for most teams. PostgreSQL just makes this pattern comfortable to implement and inspect.