PostgreSQL Token Cleanup for REST APIs

Verification tokens are usually designed as if the only important event is a successful click. In a real REST API, the harder work happens around that click: a user requests another email, a worker retries delivery, an old browser tab submits a token, or a cleanup job removes evidence before support can inspect it.

The database should make those cases explicit. A small PostgreSQL token table, a clear state transition, and a bounded cleanup worker can keep an authentication flow predictable without turning it into a large distributed system.

Why token cleanup is part of the API contract

An email verification token is temporary state, not just a random string. It has an owner, an intended action, an issued time, an expiration time, and usually a consumption time. If those values are left implicit, different parts of the service will invent their own rules.

For example, the REST API may consider a token valid for 15 minutes while the worker retries for an hour. A cleanup query might delete rows after 10 minutes, and the Node.js handler might still return a generic success response when the token is already gone. The user sees a confusing loop, while the logs show only "invalid token".

I treat the lifecycle as a contract with four useful states:

• issued: the token exists but has not been used
• consumed: the intended action completed successfully
• expired: the validity window ended without consumption
• revoked: a newer request or a security action invalidated it

This also fits a broader threat model for OAuth signup, where proof of email ownership is only one part of the trust boundary.

Model the token lifecycle in PostgreSQL

Store a hash of the token rather than the raw value. The email link can carry the raw secret, but a database leak should not immediately turn every unexpired link into a working credential. A minimal table can look like this:

CREATE TABLE verification_tokens (
  id uuid PRIMARY KEY,
  user_id uuid NOT NULL,
  token_hash bytea NOT NULL UNIQUE,
  purpose text NOT NULL,
  status text NOT NULL DEFAULT 'issued',
  issued_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL,
  consumed_at timestamptz,
  revoked_at timestamptz,
  created_request_id text NOT NULL
);

CREATE INDEX verification_tokens_expiry_idx
  ON verification_tokens (expires_at)
  WHERE status = 'issued';

The partial index keeps the cleanup path focused on live rows. I also like created_request_id because it connects a token to an idempotency key or request trace. That makes a duplicate-send investigation much less guessy.

Do not use a cleanup delete as the validity check. The API should validate status = 'issued' and expires_at > now() in one transaction. Cleanup is maintenance; it should never be the authority for whether a link works.

Use a bounded cleanup worker

A cleanup job should do small batches instead of scanning and deleting every expired row in one transaction. Large deletes create lock pressure, make vacuum work harder, and can delay unrelated authentication requests.

WITH expired AS (
  SELECT id
  FROM verification_tokens
  WHERE status = 'issued'
    AND expires_at <= now()
  ORDER BY expires_at
  FOR UPDATE SKIP LOCKED
  LIMIT 500
)
UPDATE verification_tokens AS token
SET status = 'expired'
FROM expired
WHERE token.id = expired.id;

Marking rows as expired preserves useful history. A later retention job can remove old terminal rows after the support and audit window. The exact retention period depends on the product, but mixing "not valid anymore" with "physically absent" is a common source of observabilty gaps.

Run this statement repeatedly with a short delay, and stop when the batch is empty. Multiple workers can safely share the queue because SKIP LOCKED avoids waiting on rows another worker already claimed. Keep the transaction short; a cleanup worker is a dependancy of the system, not the system itself.

Keep retries and consumption observable

Token consumption must be atomic. The handler should update only an issued, unexpired token and return the result of that update:

UPDATE verification_tokens
SET status = 'consumed', consumed_at = now()
WHERE token_hash = $1
  AND status = 'issued'
  AND expires_at > now()
RETURNING user_id, purpose;

If no row is returned, the API can distinguish an already-used token from an expired one by doing a safe follow-up lookup, or it can deliberately expose one generic response to reduce information leakage. The important point is that two requests cannot both consume the same token.

Record counters for issued, resent, consumed, expired, and revoked tokens. Add the token purpose and a request identifier, but never log the raw token. When a test note says temp mailid, that may be harmless shorthand, but production logs still need a stable, non-secret correlation value.

For end-to-end checks, capture the message timestamp and the token request ID rather than merely selecting the newest email. The same principle appears in fresh evidence when polling an email inbox. It prevents a retry from quietly consuming an older message.

Test the expiry boundary

Time-based tests become brittle when they use the wall clock directly. Inject a clock into the Node.js service and test at exact boundaries:

1. A token one second before expires_at is accepted.
2. A token at or after expires_at is rejected.
3. Two concurrent consumption requests produce one success.
4. A resend revokes or supersedes the previous issued token.
5. Cleanup does not change a consumed or revoked token.

Also test a delayed worker and a retry after a database timeout. The API response should not claim a new token was sent unless the outbox or delivery contract recorded that attempt. Otherwise a temporary database error can result in an email the user never receives, occured alongside a misleading success message.

A practical checklist

• Hash verification secrets at rest.
• Store explicit status, issued_at, and expires_at values.
• Enforce one-time consumption with a conditional update.
• Keep cleanup in bounded batches with SKIP LOCKED.
• Preserve terminal rows long enough for support and audit needs.
• Correlate resends, messages, and API requests without logging secrets.
• Inject time so expiry and retry tests are deterministic.

The pattern is deliberately modest. PostgreSQL can own token state, the REST API can own the contract, and a small worker can handle retention. That separation keeps authentication behavior understandable when the happy path is no longer the only path.