The failure mode: retries without memory

Notification systems fail in a specific, recurring way: a request to send arrives, the system tries to call a provider (Twilio, SendGrid, FCM), the network times out or the provider is slow, the caller retries - and now two sends are in flight for one event. If the system has no memory of "did I already try this exact event," it cannot tell a legitimate retry from an accidental duplicate. Both look identical from the inside.

The standard fix from distributed systems is an idempotency key: a value derived from the event itself (not randomly generated per request) that lets the receiving system recognize "I've seen this exact intent before" and short-circuit to the previous result instead of re-executing.

The write-first model

The core discipline: persist the notification record, with its idempotency key, before calling any provider - not after. If you call the provider first and write to your database second, a crash between those two steps loses the record of an SMS that may have already been sent, and a retry has no way to know that.

1

Compute the idempotency key from stable inputs - typically hash(event_type + user_id + event_id) - never a random UUID generated fresh on each attempt.

2

Write the notification row first, status pending, inside the same database transaction as the business event that triggered it.

3

Check for an existing key before calling the provider - if found, return the existing notification ID and skip sending again.

4

Call the provider, then update the row with the provider's response, final status, and timestamp - success or failure, always recorded.

Schema and dedupe check

CREATE TABLE notifications (
  id              BIGSERIAL PRIMARY KEY,
  idempotency_key VARCHAR(128) UNIQUE NOT NULL,
  channel         VARCHAR(20)  NOT NULL,     -- sms | email | push
  recipient       VARCHAR(255) NOT NULL,
  event_type      VARCHAR(80)  NOT NULL,
  status          VARCHAR(20)  NOT NULL DEFAULT 'pending',
  provider_id     VARCHAR(120),              -- Twilio SID / SendGrid message ID
  provider_response JSONB,
  attempt_count   INT NOT NULL DEFAULT 0,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
async function sendNotification(event) {
  const idempotencyKey = sha256(`${event.type}:${event.userId}:${event.id}`);

  // Insert-or-return: relies on the UNIQUE constraint above, not an
  // application-level "check then insert" that races under concurrency.
  const existing = await db.query(
    `INSERT INTO notifications (idempotency_key, channel, recipient, event_type)
     VALUES ($1, $2, $3, $4)
     ON CONFLICT (idempotency_key) DO NOTHING
     RETURNING id`,
    [idempotencyKey, event.channel, event.recipient, event.type]
  );

  if (existing.rowCount === 0) {
    // Row already existed - this is a retry of an event we've seen.
    const prior = await db.query(
      'SELECT id, status FROM notifications WHERE idempotency_key = $1',
      [idempotencyKey]
    );
    return prior.rows[0]; // return existing result, do not call the provider again
  }

  const notificationId = existing.rows[0].id;
  const result = await provider.send(event);
  await db.query(
    `UPDATE notifications SET status=$1, provider_id=$2, provider_response=$3,
     attempt_count = attempt_count + 1, updated_at = now() WHERE id=$4`,
    [result.status, result.id, result.raw, notificationId]
  );
  return { id: notificationId, status: result.status };
}

Why ON CONFLICT DO NOTHING matters: a "SELECT then INSERT if not found" pattern has a race window under concurrent retries - two requests can both pass the SELECT check before either INSERTs. The database's unique constraint is the only thing that actually guarantees exactly-once row creation under concurrency.

What the audit trail buys you

Duplicate prevention

The same event arriving twice - from a retry, a queue redelivery, or a webhook replay - resolves to the same notification ID instead of a second send.

Debuggability

When a customer says "I never got my OTP," the row shows exactly what was attempted, when, and what the provider returned - not a guess.

Compliance evidence

Regulated flows (payment confirmations, KYC alerts) often require proof that a notice was sent - the audit row is that proof, with a timestamp and provider receipt.

Common mistakes

Generating a fresh idempotency key per attempt

Cause: using uuid.v4() instead of deriving the key from the event. Fix: the key must be a deterministic function of the event's identity, so retries of the same event always compute the same key.

Writing to the database after calling the provider

Cause: "call first, log second" ordering. Fix: the pending row must exist before the provider call - a crash after sending but before logging is the exact gap that causes silent duplicates on retry.

Key takeaways

  • Idempotency keys must be derived from the event's identity, never generated fresh per attempt.
  • Write the notification record before calling any provider - the write-first order is what makes retries safe.
  • Use a database unique constraint, not an application-level check, to guarantee exactly-once row creation under concurrency.
  • Every attempt - success or failure - belongs in the audit row, not just the final outcome.
  • This same idempotency-key discipline is what makes the outbox pattern and exactly-once delivery guarantees actually hold up under retries.
If your notification or payment-confirmation pipeline doesn't have this write-first pattern yet, it's usually a half-day fix once the schema is right - and a very expensive one to discover missing after a duplicate-charge complaint.