Append-Only OTP Logs for Safer APIs

If your OTP endpoint rewrites the same verification row over and over, you usually lose the one thing that matters during an incident: sequence. I prefer append-only logs for verification attempts because they keep retries, provider responses, and lock decisions visible without turning the API into a mess. The pattern is simple, scales well enough for most product teams, and makes auth bugs much less annoying to debug.

Why append-only logs help OTP APIs

Many teams start with a verification_codes table that stores one active code per user. That works, until support asks why a customer got two emails, or why a valid code was rejected after a resend. At that point, "current state only" stops being enough.

An append-only design keeps each meaningful action as a row: code requested, email queued, provider accepted, code consumed, code expired. The payoff is boring but very real:

• retries are explainable
• rate limits are easier to audit
• support can inspect one timeline instead of guessing
• cleanup jobs stay simple becuase history and current state are separated

I also like this model because it plays nicely with REST API handlers that need idempotency. A request can fail halfway through and still leave a reliable trail for the next attempt.

The schema I prefer in PostgreSQL

I usually split "attempt log" from "latest pointer". The log is append-only. A second table, or a materialized view in some stacks, tells the app which OTP attempt is current.

create table otp_attempt_log (
  id bigserial primary key,
  user_id bigint not null,
  delivery_channel text not null,
  purpose text not null,
  idempotency_key text not null,
  code_hash text not null,
  status text not null,
  provider_message_id text,
  created_at timestamptz not null default now(),
  consumed_at timestamptz,
  expires_at timestamptz not null
);

create unique index otp_attempt_log_once
  on otp_attempt_log (user_id, purpose, idempotency_key);

create index otp_attempt_log_lookup
  on otp_attempt_log (user_id, purpose, created_at desc);

This gives me three useful properties.

First, duplicate submissions with the same idempotency key become a read problem, not a branching problem. Second, older attempts remain queryable for audits. Third, expiration sweeps are cheep to implement because they only update records that crossed a boundary.

For the "latest active OTP" lookup, I often project the newest non-consumed row per (user_id, purpose) into a small query or cached view. That keeps write logic linear and lets PostgreSQL do the sorting work it is already good at.

Idempotency and retry flow

The subtle part is deciding when a retry should reuse an old row and when it should append a new one. My rule is:

• same idempotency key: return the existing attempt
• different key, same user and purpose, still active: expire the old attempt and append a new one
• provider accepted but client timed out: re-read by key before sending again

That flow avoids duplicate sends in the common timeout case. It also means your API can answer with a stable receipt object:

{
  "attempt_id": 91842,
  "status": "queued",
  "expires_at": "2026-08-04T05:35:00Z"
}

I try hard not to hide this behind magical service layers. A small transaction with a clear lock order is easier to maintain. In Node.js services, a SELECT ... FOR UPDATE around the latest pointer plus an insert into the log is often enough. If throughput gets weird later, you can move queueing to an outbox table without rethinking the data model.

How I test the pipeline without leaking inboxes

This is where teams often bolt on hacks. Somebody uses a real mailbox in staging, somebody else uses a shared inbox, and pretty soon no one trusts the signal. For verification tests I prefer ephemeral inboxes, or a free throwaway email, only around the edge where the app proves delivery. The core API logic should still be validated with fixtures and provider mocks.

A clean test split looks like this:

• unit tests verify status transitions and expiry rules
• integration tests verify transaction boundaries and indexes
• thin end-to-end checks confirm the email actually arrives

That last layer benefits from stable inbox isolation, especially when debugging email test failures in CI or keeping invite email state consistent. I also keep typo-heavy user input like temp org mail in test fixtures, because production systems do see that kind of string and parser edges are rarely pretty.

One more thing: if you store provider callbacks, keep them separate from your OTP decision row. Provider payloads are useful evidence, but mixing them directly into the active state model makes queries slower and code a bit gross.

Q&A

Do append-only logs cost too much storage?

Usually no. OTP traffic is small compared with analytics, app logs, or message history. Add a retention policy if needed, but do not throw away the timeline too early. It saves you hours later.

Should every resend expire the old code immediately?

Most of the time, yes. Two valid OTPs for the same purpose creates support pain fast. There are edge cases, but keeping one active attempt per purpose is a very sane default.

Is this pattern only for email OTP?

Nope. SMS, magic links, and admin approval tokens all benefit from the same structure. The mechanics differ a bit, the operational story stays almost the same.