Building Notification Preference Segments

This guide turns the topic opt-ins you captured at subscribe time into named, reusable segments — and shows how to keep those segments accurate when the browser rotates a subscription endpoint via pushsubscriptionchange.

Quick answer

A preference segment is a query, not a stored list. Store each subscriber once and each topic opt-in as a tag row keyed to that subscriber; then define a segment as a WHERE/JOIN over tags plus attributes. The one hard part is endpoint rotation: when pushsubscriptionchange fires, the old endpoint dies and a new one appears, so you must migrate the tags to the new subscription or the user silently drops out of every segment. Key your data on a stable subscriber id, not on the volatile endpoint, and segments stay correct through rotation.

The reason to define segments as queries rather than precomputed audience lists is freshness. A materialised list goes stale the instant a subscriber opts out, rotates an endpoint, or hits a 410 Gone; a query evaluated at send time always reflects the current state. The cost is a join per send, which an index on the tag column makes trivial even at scale. Reserve materialisation for cases where a send must hit hundreds of thousands of rows under tight latency, and even then snapshot the query result moments before sending rather than maintaining a list that drifts.

The data model

Use two tables: one row per subscriber, many tag rows per subscriber. Critically, give each subscriber a stable surrogate id and treat the endpoint as a mutable attribute, not the primary key your tags point at. This is what makes rotation survivable. The capture side of this model — the preference UI and the POST that fills these tables — is covered in the parent subscriber segmentation and targeting guide, and the conventions for the tag values themselves — naming, versioning, and reconciling a changed topic list — are set out in storing push notification topic preferences.

CREATE TABLE subscribers (
  id          BIGSERIAL PRIMARY KEY,     -- stable; tags point here
  endpoint    TEXT UNIQUE NOT NULL,      -- mutable; changes on rotation
  p256dh      TEXT NOT NULL,
  auth        TEXT NOT NULL,
  locale      TEXT,
  platform    TEXT,
  plan        TEXT,
  active      BOOLEAN NOT NULL DEFAULT true,
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE subscription_tags (
  subscriber_id BIGINT NOT NULL REFERENCES subscribers(id) ON DELETE CASCADE,
  tag           TEXT NOT NULL,
  opted_in_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (subscriber_id, tag)
);

CREATE INDEX idx_tags_tag ON subscription_tags (tag);

Segment definitions

Store segment definitions as named, parameterised queries so marketing and engineering share one source of truth. A definition is just its filter predicate; the rows it returns change as subscribers come and go.

-- Segment: "engaged deal hunters"
-- tag = 'deals' AND active subscribers on either platform
SELECT s.id, s.endpoint, s.p256dh, s.auth
FROM subscribers s
JOIN subscription_tags t ON t.subscriber_id = s.id
WHERE t.tag = 'deals'
  AND s.active = true;

-- Segment: "security-critical desktop users"
SELECT s.id, s.endpoint, s.p256dh, s.auth
FROM subscribers s
JOIN subscription_tags t ON t.subscriber_id = s.id
WHERE t.tag = 'security'
  AND s.platform = 'desktop'
  AND s.active = true;

For composite segments (subscribers in several topics), aggregate and assert the count of matched tags:

-- Subscribers opted into BOTH 'deals' AND 'digest'
SELECT s.id, s.endpoint
FROM subscribers s
JOIN subscription_tags t ON t.subscriber_id = s.id
WHERE t.tag IN ('deals', 'digest')
  AND s.active = true
GROUP BY s.id, s.endpoint
HAVING COUNT(DISTINCT t.tag) = 2;

The HAVING clause is doing all the work here. Without it the same IN filter answers a completely different question.

Keeping segments in sync on pushsubscriptionchange

When a browser migrates push servers it fires pushsubscriptionchange in the service worker, invalidating the old endpoint. If you key tags to the endpoint, the user vanishes from every segment. Because tags are keyed to the stable subscriber_id, you only need to update the endpoint and keys on the existing row — the tags follow automatically.

The hop-by-hop order matters, because the only message that carries the old endpoint is the one your API receives — after that the browser has forgotten it.

This is the single most important reason the data model separates a stable subscriber_id from the mutable endpoint. Endpoint rotation is not a rare edge case — it happens routinely when a browser updates, when its push service rebalances, or when the user clears certain site data — and every rotation is an opportunity to silently lose a subscriber’s hard-won topic preferences. A subscriber who opted into three topics, rotates, and is re-inserted as a fresh row is now opted into nothing, will receive no targeted sends, and looks to your analytics like churn when nothing of the sort happened. Getting rotation right is what keeps your segment counts honest over months.

// service-worker.js
self.addEventListener('pushsubscriptionchange', (event) => {
  event.waitUntil((async () => {
    const newSub = await self.registration.pushManager.subscribe({
      userVisibleOnly: true,
      applicationServerKey: APPLICATION_SERVER_KEY // base64url VAPID public key bytes
    });

    await fetch('/api/push/rotate', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        oldEndpoint: event.oldSubscription?.endpoint ?? null,
        newSubscription: newSub
      })
    });
  })());
});

On the server, find the existing subscriber by the old endpoint and update it in place so the tags stay attached:

-- Migrate the subscription onto the existing subscriber row; tags are untouched
UPDATE subscribers
SET endpoint   = $1,   -- new endpoint
    p256dh     = $2,
    auth       = $3,
    updated_at = now()
WHERE endpoint = $4;   -- old endpoint

The contrast with the naive alternative is stark, and it is entirely a consequence of which row the tags reference.

Numbered build steps

1. Model on a stable id. Make subscribers.id the foreign key target for tags and treat endpoint as mutable. This single decision is what survives rotation.
2. Write tags transactionally. When a subscriber opts in, upsert the subscriber and insert tag rows in one transaction so you never have a subscriber without tags or vice versa.
3. Express segments as queries. Store each segment as a named predicate, not a materialised list, so it reflects current opt-ins automatically.
4. Handle rotation by update, not insert. On pushsubscriptionchange, look up by old endpoint and UPDATE the row. Inserting a new row orphans the tags.
5. Mark, don’t delete, on 410 Gone. When the push service returns 410, set active = false rather than deleting, so segment queries exclude dead endpoints while preserving consent history. See handling 410 Gone responses at scale.

Gotchas & edge cases

• event.oldSubscription can be null. Some browsers omit it on pushsubscriptionchange, leaving you no old endpoint to match. Fall back to matching by an app-issued client id stored at subscribe time, or you will create a duplicate subscriber and lose the tags.
• Inserting on rotation orphans tags. The most common segmentation bug: treating a rotated subscription as a brand-new subscriber. Always update the existing row by old endpoint (or client id).
• Composite segments need COUNT(DISTINCT tag). A naive tag IN (...) returns subscribers with any of the tags, not all. Use the HAVING COUNT(DISTINCT tag) = N pattern for AND semantics.
• Deleting on 410 destroys consent history. Soft-delete with an active flag so you can prove what a user opted into and when, which GDPR and CCPA audits require.
• Tags drift from the UI. If users edit preferences later through your opt-out and preference centers, make that edit the same upsert/delete on subscription_tags so the segment query and the UI never disagree.

Related

• Back to Subscriber Segmentation & Targeting — the capture side: preference UI, attribute collection, and the subscribe-time POST that fills these tables.
• Storing push notification topic preferences — how to name, version and reconcile the topic values these segments filter on.
• Push personalization and segmentation — enrich these segments with behavioural data for campaign targeting.
• Handling 410 Gone responses at scale — keep segments clean by retiring dead endpoints correctly.
• Handling pushsubscriptionchange on the client — the client-side half of the rotation flow that feeds the update shown above.

FAQ