Storing Push Notification Topic Preferences

Most topic-preference bugs are not code bugs — they are modelling bugs, and they all trace back to a schema that cannot tell “the user said no” apart from “we never asked”.

Quick answer: store topics in their own catalogue table, join them to subscribers through a row-per-decision table with an explicit tri-state state column (opt_in, opt_out, unset), stamp every row with who decided, when, and under which policy version, and enforce the resulting set in the WHERE clause of your send query rather than in the service worker. Presence-as-consent — a row exists, therefore they opted in — is the shortcut that makes defaults, audits and jurisdiction rules impossible to implement later.

Designing the Topic Taxonomy

A topic is a decision a subscriber would plausibly make differently from the others. That is the whole test. “Security alerts” and “weekly digest” pass it — someone can sensibly want one and not the other. “Deals” and “promotions” fail it: nobody has a coherent preference that separates them, so splitting them only adds a toggle that never gets used and a segment that never gets targeted.

Two levels are enough: a small number of categories for grouping in the UI, and topics inside them. A third level almost always encodes your internal team structure rather than the user’s mental model, and it multiplies the rows you have to keep consistent for no expressive gain. Keep slugs stable and human-readable (security-alerts, not topic_7), because they will end up in analytics events, campaign configuration and support tickets, and a renamed slug silently orphans every preference row that referenced it.

The Schema: Three Tables, Tri-State Preferences

The catalogue table makes topics first-class objects with their own defaults and metadata. The preference table records one decision per subscriber per topic. Splitting them means adding a topic is an insert, not a migration, and retiring one is a flag rather than a mass delete.

CREATE TABLE push_topics (
  id               BIGSERIAL PRIMARY KEY,
  slug             TEXT UNIQUE NOT NULL,        -- 'security-alerts'
  category         TEXT NOT NULL,               -- 'account' | 'marketing'
  label            TEXT NOT NULL,               -- shown in the preference centre
  is_transactional BOOLEAN NOT NULL DEFAULT false,
  default_state    TEXT NOT NULL DEFAULT 'opt_out',
  retired_at       TIMESTAMPTZ
);

CREATE TABLE push_topic_preferences (
  subscriber_id  BIGINT NOT NULL REFERENCES push_subscribers(id) ON DELETE CASCADE,
  topic_id       BIGINT NOT NULL REFERENCES push_topics(id),
  state          TEXT NOT NULL CHECK (state IN ('opt_in', 'opt_out', 'unset')),
  source         TEXT NOT NULL,                 -- 'subscribe-flow' | 'preference-centre' | 'import'
  policy_version TEXT NOT NULL,                 -- which disclosure copy the user saw
  decided_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (subscriber_id, topic_id)
);

CREATE INDEX idx_topic_pref_lookup
  ON push_topic_preferences (topic_id, state)
  WHERE state = 'opt_in';

The state column is the load-bearing decision. With presence-as-consent you can express only two facts, and you need three: opted in, opted out, and never decided. Without the third, a user who unticks a box is indistinguishable from a user who has not seen the preference centre — so you either keep sending to people who declined, or you stop sending to everyone who has not explicitly agreed, including subscribers whose jurisdiction and topic make a default opt-in perfectly lawful. Neither is acceptable, and both are unfixable without a schema change.

The partial index is deliberate too. Send queries only ever ask for opt-ins, so indexing only state = 'opt_in' keeps the index a fraction of the table’s size while answering the one question the send path cares about.

Defaults for New Subscribers

A new subscriber has no preference rows, so every send decision falls back to the topic’s default_state. Choosing those defaults is a legal question before it is a product one, and it splits along two axes: whether the message is transactional or marketing, and where the subscriber is.

Two rules keep this manageable. First, resolve the default at read time from push_topics.default_state combined with the subscriber’s region_code — never by writing a bulk opt_in row for a user who never made a decision, because that manufactures consent records that cannot be defended. Second, treat the notification permission grant itself as consent to the transactional topics only. A user clicking “Allow” in the browser dialog agreed to receive notifications; they did not agree to a marketing category they were never shown. The boundary between those two classes is exactly the one drawn in transactional vs marketing push notifications.

Syncing from the Preference Centre

The preference centre writes decisions; nothing else should. Every toggle produces one upsert carrying the state, the source, and the policy version that was on screen. Send the whole visible set rather than a diff — it is a handful of rows, and a full replace is immune to a lost intermediate request.

// Preference centre → server. Sends every visible topic, not a diff.
async function savePreferences(form) {
  const decisions = [...form.querySelectorAll('input[data-topic]')].map(input => ({
    slug: input.dataset.topic,
    state: input.checked ? 'opt_in' : 'opt_out'
  }));

  const res = await fetch('/api/push/preferences', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    credentials: 'same-origin',
    body: JSON.stringify({
      endpoint: await currentEndpoint(),      // identifies this device's subscription
      decisions,
      policyVersion: document.body.dataset.pushPolicyVersion,
      decidedAt: new Date().toISOString()
    })
  });
  if (!res.ok) throw new Error('preferences not saved: ' + res.status);
}

async function currentEndpoint() {
  const reg = await navigator.serviceWorker.ready;
  const sub = await reg.pushManager.getSubscription();
  return sub ? sub.endpoint : null;
}

-- One statement per submission, inside a transaction.
INSERT INTO push_topic_preferences
  (subscriber_id, topic_id, state, source, policy_version, decided_at)
SELECT $1, t.id, d.state, 'preference-centre', $3, $4
  FROM unnest($2::jsonb[]) AS raw(j)
  CROSS JOIN LATERAL (
    SELECT j->>'slug' AS slug, j->>'state' AS state
  ) d
  JOIN push_topics t ON t.slug = d.slug AND t.retired_at IS NULL
ON CONFLICT (subscriber_id, topic_id) DO UPDATE
  SET state = EXCLUDED.state,
      source = EXCLUDED.source,
      policy_version = EXCLUDED.policy_version,
      decided_at = EXCLUDED.decided_at;

Identify the subscriber by endpoint, and remember that endpoints rotate. If a rotation is handled by inserting a new row instead of migrating the existing one, the preferences stay attached to the dead subscriber and the live one looks brand new — which is why the migration must be atomic, as set out in subscription state synchronization. For accounts where the same person has several devices, decide explicitly whether preferences are per-device or per-account; per-account is almost always what users expect, which means keying preferences on user_id and treating the endpoint only as the routing address.

Enforce at Send Time, Not Display Time

The tempting shortcut is to send everything and let the service worker decide whether to call showNotification(). It is wrong on every axis. The message has already been encrypted with aes128gcm and pushed through the push service, so you paid for the send and consumed rate-limit budget for a notification nobody sees. The payload — capped at 4 KB under RFC 8291 — has already been delivered to the device, so a user who opted out still received the content. And Chromium enforces the userVisibleOnly contract: repeatedly receiving a push and showing nothing can cause the browser to display its own generic “site updated in the background” notification, which is worse than either outcome you intended.

Enforcement belongs in the query that selects recipients.

-- Recipients for a campaign on one topic, defaults resolved at read time.
SELECT s.endpoint, s.p256dh, s.auth
  FROM push_subscribers s
  JOIN push_topics t ON t.slug = $1 AND t.retired_at IS NULL
  LEFT JOIN push_topic_preferences p
         ON p.subscriber_id = s.id AND p.topic_id = t.id
 WHERE s.status = 'active'
   AND (
        p.state = 'opt_in'
     OR (COALESCE(p.state, 'unset') = 'unset'
         AND t.default_state = 'opt_in'
         AND (t.is_transactional OR s.region_code NOT IN ('EU', 'UK', 'CA')))
   );

The LEFT JOIN plus COALESCE is the tri-state model doing its job: an explicit opt_out excludes the subscriber unconditionally, an explicit opt_in includes them unconditionally, and only the absence of a decision consults the topic default and the region. One query, no application-layer post-filtering, and no path by which an opt-out becomes a send. Layer frequency limits on top of this rather than inside it — the two concerns compose cleanly, and capping is covered in setting push notification frequency caps.

Auditability for Consent

A regulator does not ask “is this user opted in”; it asks “prove what they agreed to, when, and what you showed them at the time”. The preference row answers the first two through decided_at and source. The third is what policy_version is for: it points at the exact disclosure copy that was on screen, so a versioned copy store turns “the user consented” into a defensible record rather than an assertion.

Two further constraints follow. Keep an append-only history alongside the current-state table — the preference table is mutable by design, so a push_consent_events log of every transition is the only place a withdrawal survives being overwritten. And never let an import, a bulk update or a data migration write opt_in with source = 'import' for users who never chose it; that is manufactured consent, and it is worse than having no record at all. The withdrawal side of the same ledger is detailed in GDPR-compliant push unsubscribe logging.

If you are migrating an existing presence-only join table, the safe path is short:

1. Add the state, source, policy_version and decided_at columns as nullable.
2. Backfill every existing row to state = 'opt_in', source = 'legacy-migration' — those rows genuinely represented an opt-in under the old model.
3. Add the CHECK constraint and set the columns NOT NULL once the backfill has completed.
4. Change the send query to the LEFT JOIN form so absent rows resolve to unset rather than being silently excluded.
5. Only then remove any application code that inferred consent from row presence.

Gotchas & Edge Cases

• Deleting a preference row is not an opt-out. A delete resets the subscriber to unset, which can silently re-enable a default-on topic. Withdrawals must write state = 'opt_out', never remove the row.
• Retiring a topic must not cascade. Set retired_at on the catalogue row and exclude it from queries; deleting it destroys the consent evidence for every decision anyone ever made about it.
• Per-device preferences surprise users. Someone who mutes deals on their laptop expects their phone to follow. Key on user_id where you have one, and treat the endpoint purely as the address.
• A permission grant is not a topic consent. Clicking “Allow” in the browser dialog covers the notification channel, not the categories. Marketing topics still need their own recorded decision, and the surfaces for capturing it live in opt-out and preference centres.
• Do not resolve defaults with a bulk write. Resolving unset at read time costs one LEFT JOIN; resolving it by inserting rows fabricates a consent history you will have to defend.

Related

• Back to Subscriber segmentation & targeting — the parent guide covering capture at subscribe time and the attributes that sit beside topics.
• Building notification preference segments — turning these stored decisions into named, queryable segments.
• Opt-out and preference centres — the UI surfaces that write these rows and the accessible unsubscribe path beside them.
• Subscription state synchronization — keeping preferences attached to the right subscriber when the endpoint rotates.

FAQ