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
);

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 [email protected] and [email protected] 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', CURRENT_TIMESTAMP, ?, ?, ?)
ON CONFLICT (list_id, email) DO UPDATE SET
  confirmation_token_hash = excluded.confirmation_token_hash,
  confirmation_expires_at = excluded.confirmation_expires_at;

This is still too aggressive.

Every repeated click rotates the token and sends another message.

Add a cooldown.

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

Use a conditional update:

confirmation_token_hash = CASE
  WHEN subscribers.status = 'pending'
   AND (
     subscribers.confirmation_expires_at IS NULL
     OR subscribers.confirmation_expires_at
        <= excluded.confirmation_expires_at - 600
   )
  THEN excluded.confirmation_token_hash
  ELSE subscribers.confirmation_token_hash
END

Apply the same condition to:

• consent timestamp
• consent version
• token expiry

Do not update a confirmed subscriber.

Decide whether to send

Read the resulting row:

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

The newly generated hash tells us whether the upsert accepted this request:

const shouldSend =
  row.status === 'pending' &&
  row.confirmation_token_hash === newTokenHash

If the hash does not match, the request arrived inside the cooldown or the subscriber is already confirmed.

Return the same neutral response in every case.

Recover after delivery failure

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

Read the latest delivery:

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

Allow an immediate retry for:

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

Two retry requests can arrive together. Rotate the token with a compare-and-swap against the hash both requests read:

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

Only the request that receives the returned row sends an email. The other request sees that the old hash no longer matches and stops.

A failure should not strand the address for ten minutes, but recovery still needs concurrency control.

Keep delivery attempts separate

Do not store one mutable email_status on the subscriber.

Create one row per attempt:

CREATE TABLE email_deliveries (
  id TEXT PRIMARY KEY,
  subscriber_id TEXT NOT NULL,
  status TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Now the resend policy can inspect history without losing it.

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. The compare-and-swap above closes a race where two failed-delivery retries could both send different tokens.

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: