Design a Notification System

{
  "message": {
    "token": "DEVICE_TOKEN",

    "notification": { "title": "3 new messages", "body": "from Priya" },

    "android": {
      "collapse_key": "chat-42",
      "ttl": "3600s",
      "notification": { "title": "3 new messages", "body": "from Priya", "tag": "chat-42" }
    },

    "apns": {
      "headers": {
        "apns-push-type": "alert",
        "apns-priority": "10",
        "apns-collapse-id": "chat-42",
        "apns-expiration": "0"
      },
      "payload": {
        "aps": { "alert": { "title": "3 new messages", "body": "from Priya" }, "thread-id": "chat-42" }
      }
    }
  }
}

// APNs is HTTP/2: the status plus the JSON reason decide whether the token
// survives, whether we retry, and whether the provider is still healthy.
async function sendApns(token, payload, headers) {
  if (breaker.isOpen()) throw new RetryableError('breaker-open'); // fail fast, keep the queue

  const res = await apns.post(`/3/device/${token}`, payload, headers);

  if (res.status === 200) { breaker.recordSuccess(); return 'sent'; }
  const reason = res.body?.reason;

  // 1. The device is gone. Delete the token; nothing you send will ever arrive.
  if (res.status === 410 || reason === 'Unregistered' || reason === 'BadDeviceToken') {
    await tokens.delete(token);
    return 'dead-token';
  }

  // 2. Transient. Retry with backoff, and count it against the breaker so a
  //    provider outage does not turn into a million in-flight retries.
  if (res.status === 429 || res.status >= 500) {
    breaker.recordFailure();
    throw new RetryableError(reason ?? `status ${res.status}`);
  }

  // 3. Our fault. A malformed payload or wrong topic is deterministic: park it
  //    in the dead-letter store and page someone instead of looping.
  await deadLetters.put({ token, reason, status: res.status });
  return 'rejected';
}

-- Per-user settings that every channel needs, including the timezone that
-- quiet hours and digests are evaluated in.
CREATE TABLE user_notification_settings (
  user_id     BIGINT PRIMARY KEY,
  timezone    TEXT     NOT NULL,          -- IANA name, 'Europe/Lisbon' - never an offset
  quiet_start SMALLINT,                   -- local hour, NULL = no quiet hours
  quiet_end   SMALLINT,
  digest_hour SMALLINT NOT NULL DEFAULT 9
);

-- Sparse overrides: a missing row means "whatever the product default is".
-- A new category therefore ships without touching a single user row.
CREATE TABLE notification_prefs (
  user_id  BIGINT NOT NULL REFERENCES user_notification_settings (user_id),
  category TEXT   NOT NULL,               -- 'mention' | 'follow' | 'billing' | 'security'
  channel  TEXT   NOT NULL,               -- 'push' | 'email' | 'sms' | 'inapp'
  mode     TEXT   NOT NULL,               -- 'instant' | 'digest' | 'off'
  PRIMARY KEY (user_id, category, channel)
);

-- The digest sweep runs every hour and selects only the users whose local hour
-- is now their digest hour, which spreads a global send over 24 hours instead
-- of firing every email at once.
SELECT s.user_id
FROM user_notification_settings s
WHERE s.digest_hour = EXTRACT(HOUR FROM now() AT TIME ZONE s.timezone)
  AND EXISTS (SELECT 1 FROM notification_prefs p
              WHERE p.user_id = s.user_id AND p.mode = 'digest');