REST API Verification State Machines in PostgreSQL

Email verification looks like a small feature, but its reliability depends on clear state transitions, database constraints, and idempotent API behavior.

Why verification needs an explicit state machine

Many implementations start with one verified boolean and a token column. That is enough for a demo, but it becomes hard to reason about when a user requests a second email, clicks an old link, or retries after a network timeout.

I prefer modelling the verification flow as a small state machine:

• pending: a verification request exists and has not been completed.
• sent: a message was scheduled for delivery.
• verified: the token was consumed successfully.
• expired: the token can no longer be used.
• revoked: a newer request invalidated this one.

The state is not only documentation. It gives the REST API something concrete to enforce. An expired token cannot move back to pending, and a verified token cannot be consumed twice. This sounds obvious, but vague states are where auth bugs usually begin.

The PostgreSQL model

A separate row for each verification attempt is more useful than overwriting a single token. It preserves an audit trail and lets the application reject an older request without guessing which email was most recent.

CREATE TABLE email_verifications (
    id              uuid PRIMARY KEY,
    user_id         uuid NOT NULL,
    token_digest     bytea NOT NULL UNIQUE,
    state            text NOT NULL CHECK (state IN (
        'pending', 'sent', 'verified', 'expired', 'revoked'
    )),
    expires_at       timestamptz NOT NULL,
    consumed_at      timestamptz,
    created_at       timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX email_verifications_user_pending_idx
    ON email_verifications (user_id, created_at DESC)
    WHERE state IN ('pending', 'sent');

Store a digest of the token, not the raw token. The link can contain a random value, while the database only receives its hash for lookup. When a user requests a new link, revoke previous pending and sent rows in the same transaction, then insert the new row.

The partial index keeps the common query small. It also makes the intended active states visible to the next engineer. A little explicitness here saves a lot of debugging later, specially when traffic grows.

For the email side, an outbox row can represent the delivery request. The queryable PostgreSQL outbox checks pattern is useful when the API must commit user state and delivery intent together.

Make the REST API transitions idempotent

The verification endpoint should be safe to retry. A client may receive a timeout after the server commits, so returning an error for the second request can make a successful flow look broken.

For example, the endpoint can follow this sequence:

1. Hash the presented token.
2. Start a transaction and select the matching row with a lock.
3. Reject it if it is missing, expired, or revoked.
4. If it is already verified, return the same success shape as a first successful request.
5. Otherwise update it to verified, set consumed_at, and mark the user as verified.
6. Commit before sending the response.

The important part is the conditional update. In a busy system, two requests can present the same token at almost the same time. A lock or a guarded UPDATE ... WHERE state IN ('pending', 'sent') ensures only one request performs the transition.

The response should not reveal whether a token was valid for a particular account. Use a stable public result such as verified, already_verified, or a generic failure, depending on the threat model. Error details in logs can be richer than details returned to an unknown caller.

If test data uses a frozen plan for repeatable automation, add the verification state and token identifier to the run manifest. That makes a flaky test easier to replay. A dummy e mail address is fine for a fixture, but it should never be treated as proof that delivery happened.

Keep email delivery outside the verification transaction

Sending an email while holding a database transaction creates an awkward failure window. The provider can be slow, the request can time out, and the database lock can remain open longer than expected.

Use a transactional outbox instead:

• The API transaction creates the verification row and an outbox event.
• A worker claims the event with a short lease.
• The worker sends the message and records success or a retryable failure.
• A cleanup job expires old verification rows and abandoned events.

This also separates authentication correctness from provider availability. A provider outage should delay a message, not make the user record half-updated. The worker needs bounded retries, because retrying a permanently rejected address forever is just noise and cost.

Be careful with expiration. Checking expires_at in application code is necessary, but the database transition should check it too. Otherwise a request that waited in a queue can consume a token after the application made its first decision. The two checks are slightly repetitive, and thats intentional.

Operational checklist

Before shipping the endpoint, I check the following:

• Every legal state transition has a test.
• Two concurrent requests cannot consume one token twice.
• A newer request revokes older active requests.
• Token values are never logged in raw form.
• Expiration is checked during the write, not only during the read.
• Outbox retries have a maximum or a dead-letter path.
• Metrics separate invalid, expired, already-used, and successful attempts.
• Cleanup queries use indexes and are safe to run more than once.

The wording in a few errors may look small, but it affects support and incident response. Keep the public API stable even if internal reasons are refined later. In practise, a predictable contract is more valuable than a clever one.

Final thoughts

An email verification flow is an authentication workflow with a delivery dependency. Treating it as a state machine makes the rules visible; PostgreSQL transactions make the transitions enforceable; and an outbox keeps delivery from controlling account correctness.

The same design works whether the message goes to a corporate inbox, a temp mail so address used for a controlled test, or another mailbox provider. The important boundary is that the system records what it knows: a token was created, a delivery was requested, and a token was consumed. Those are different facts, and keeping them seperate makes the API easier to operate.