Build idempotent double opt-in signup

Submitting the same waitlist form twice should not create two subscribers.

It should not send two confirmation emails either.

But if the first email failed, the next submission should be allowed to try again.

This is an idempotency problem with a resend policy.

Enforce uniqueness in the database

Create a case-insensitive email column and a unique index for one email per list:

CREATE TABLE subscribers (
  id TEXT PRIMARY KEY,
  list_id TEXT NOT NULL,
  email TEXT NOT NULL COLLATE NOCASE,
  status TEXT NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'confirmed')),
  consent_at INTEGER NOT NULL,
  consent_version TEXT NOT NULL,
  confirmation_token_hash TEXT,
  confirmation_expires_at INTEGER,
  confirmed_at INTEGER
);

CREATE UNIQUE INDEX subscribers_list_email_idx
ON subscribers (list_id, email);

Application checks are not enough.

Two requests can both check for a missing row before either inserts it.

The database must own uniqueness.

Normalize email addresses to lowercase before inserting too. COLLATE NOCASE provides a second boundary so Person@example.com and person@example.com do not become two rows.

Use an upsert

Insert a pending subscriber:

INSERT INTO subscribers (
  id,
  list_id,
  email,
  status,
  consent_at,
  consent_version,
  confirmation_token_hash,
  confirmation_expires_at
) VALUES (?, ?, ?, 'pending', unixepoch(), ?, ?, ?)
ON CONFLICT (list_id, email) DO UPDATE SET
  consent_at = excluded.consent_at,
  consent_version = excluded.consent_version,
  confirmation_token_hash = excluded.confirmation_token_hash,
  confirmation_expires_at = excluded.confirmation_expires_at
WHERE subscribers.status = 'pending'
  AND (
    subscribers.confirmation_expires_at IS NULL
    OR subscribers.confirmation_expires_at
       <= excluded.confirmation_expires_at - 600
  )
RETURNING id, status, confirmation_token_hash;

Store confirmation_expires_at as Unix seconds. The subtraction above is then unambiguous.

The statement inserts a new subscriber or rotates a pending subscriber after the cooldown. Inside the cooldown, and for confirmed subscribers, it returns no row.

Rotate only after the cooldown

Suppose every new token expires in 24 hours.

The incoming expiry is:

now + 24 hours

Ten minutes ago is:

incoming expiry - 10 minutes

The conditional ON CONFLICT clause applies the same decision to consent, token hash, and expiry. It never updates a confirmed subscriber.

Decide whether to send

Do not send an email immediately after the upsert. A process crash could rotate the token without sending, or send without recording the result.

Create one row per delivery attempt and an outbox row for work that still has to reach the provider:

CREATE TABLE email_deliveries (
  sequence INTEGER PRIMARY KEY AUTOINCREMENT,
  id TEXT NOT NULL UNIQUE,
  subscriber_id TEXT NOT NULL,
  operation_key TEXT NOT NULL UNIQUE,
  retry_of_delivery_id TEXT,
  status TEXT NOT NULL,
  created_at INTEGER NOT NULL DEFAULT (unixepoch())
);

CREATE INDEX email_deliveries_subscriber_sequence_idx
ON email_deliveries (subscriber_id, sequence DESC);

CREATE UNIQUE INDEX email_deliveries_retry_idx
ON email_deliveries (retry_of_delivery_id)
WHERE retry_of_delivery_id IS NOT NULL;

CREATE TABLE email_outbox (
  id TEXT PRIMARY KEY,
  delivery_id TEXT NOT NULL UNIQUE,
  recipient TEXT NOT NULL,
  confirmation_url TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'queued',
  created_at INTEGER NOT NULL DEFAULT (unixepoch())
);

Run the upsert and the delivery/outbox inserts inside one write transaction:

BEGIN IMMEDIATE
run the subscriber upsert
if the upsert returned a row:
  insert one queued email_deliveries row
  insert its email_outbox row
COMMIT

BEGIN IMMEDIATE serializes competing SQLite writers. If the transaction rolls back, neither the token rotation nor the email work remains.

If the upsert returns no row, look up the existing subscriber:

SELECT id, status
FROM subscribers
WHERE list_id = ?
  AND email = ?;

A confirmed subscriber and a pending subscriber inside the cooldown both receive the same neutral HTTP response.

Recover after delivery failure

The cooldown becomes harmful when the previous email bounced or the provider rejected it.

Read the latest delivery using its database sequence, not a timestamp that several attempts can share:

SELECT id, status
FROM email_deliveries
WHERE subscriber_id = ?
ORDER BY sequence DESC
LIMIT 1;

Allow an immediate retry for:

const retryable = [
  'bounced',
  'failed',
  'rejected'
]

Inside the same BEGIN IMMEDIATE transaction, claim that failed attempt:

INSERT INTO email_deliveries (
  id,
  subscriber_id,
  operation_key,
  retry_of_delivery_id,
  status
) VALUES (?, ?, ?, ?, 'queued')
ON CONFLICT (retry_of_delivery_id)
WHERE retry_of_delivery_id IS NOT NULL
DO NOTHING
RETURNING id;

If the insert returns no row, another request already claimed this failure.

Next rotate the token only while the subscriber is still pending:

UPDATE subscribers
SET
  consent_at = unixepoch(),
  consent_version = ?,
  confirmation_token_hash = ?,
  confirmation_expires_at = ?
WHERE id = ?
  AND status = 'pending'
RETURNING id;

If this update returns no row, roll back the transaction. Otherwise insert the complete confirmation email into email_outbox and commit.

A worker sends each outbox row using delivery_id as the provider idempotency key. If the worker crashes after the provider accepts the message, retrying the outbox row uses the same key.

Without provider-side idempotency, exactly-once external delivery cannot be guaranteed.

Test the important sequences

Test these cases:

1. first submission creates a pending subscriber and sends
2. immediate duplicate does not send
3. submission after cooldown rotates the token and sends
4. confirmed subscriber stays confirmed and does not send
5. failed delivery permits immediate retry
6. concurrent first submissions still produce one subscriber
7. concurrent retries after failure send only one new email

The original implementation that inspired this post used an unconditional recovery update. A unique retry claim plus an outbox closes the race and keeps the database change tied to the email work.

Idempotency does not always mean “return the old result forever.”

Here it means one logical subscriber, controlled side effects, and an explicit recovery path.

Related posts about database: