Push Subscription Storage & Lifecycle

A PushSubscription is a credential, not a user profile row. It is issued by a push service, it can be revoked without telling you, and it carries key material that decrypts everything you send. This guide covers how to persist those records, how to index them, how to protect the secret inside them, and how to age them through a lifecycle that never sends to an address the browser has already thrown away.

Prerequisites

• A relational store (examples here are PostgreSQL 14+; the model ports cleanly to MySQL 8 and DynamoDB)
• A dispatch path that already signs requests with process.env.VAPID_PRIVATE_KEY and encrypts payloads with aes128gcm
• A KMS or secrets manager that can issue and rotate a 256-bit data key for column encryption
• A scheduled job runner (cron, Temporal, Sidekiq, or a queue consumer) for the ageing sweeps
• Write access to run online DDL — the migration section assumes you can add nullable columns without a table rewrite

1. What a Subscription Record Actually Contains

registration.pushManager.subscribe() resolves to a PushSubscription whose toJSON() form has exactly three fields you care about: endpoint, expirationTime, and a keys object holding p256dh and auth. Everything else in your table is provenance and lifecycle metadata that you invent.

endpoint is an absolute HTTPS URL owned by the browser’s push service. The path segment is an opaque registration identifier — never parse it, never normalise it, never lowercase it. p256dh is the user agent’s ECDH public key on the P-256 curve, transmitted as a 65-byte uncompressed point in base64url (87 characters). auth is a 16-byte authentication secret in base64url (22 characters). Both feed the HKDF step of the aes128gcm content encoding described in RFC 8291, and both are useless without the other. expirationTime is almost always null; Chrome populates it only for subscriptions it intends to rotate, and no push service is obliged to honour it as a hard deadline.

Store the raw values verbatim. Base64url is not base64 — re-encoding p256dh through a padded base64 helper and back is the single most common cause of “encryption worked in staging” bugs, and the mechanics of that failure are covered in Push API Payload Encryption. Remember also that the ciphertext you eventually send is capped at 4 KB, so the record’s job is only to hold credentials, never message content.

2. The Endpoint URL Is the Natural Identity

There is exactly one stable identifier for a push subscription and the push service assigns it: the endpoint URL. It is globally unique across every browser, every profile and every device, because the registration ID inside it is minted by the service. Nothing you own has that property. A user ID identifies a person who may have six browsers. A session ID dies on logout. A device fingerprint is both privacy-hostile and wrong — two Chrome profiles on the same laptop produce two legitimately distinct endpoints, which is why deduplication must key on the endpoint and nothing else, as covered in deduplicating push subscriptions across devices.

Treat the endpoint as immutable. When the browser rotates it — after a service worker update, a profile restore, or a push service migration — that is a new subscription, not an edit of the old one. The client learns about the rotation through the pushsubscriptionchange event and should send you both the old and new endpoints so you can transfer the user association; the client-side half of that handshake is described in handling pushsubscriptionchange on the client.

Why you index a hash, not the URL

Endpoints are long and variable. FCM endpoints run around 180–230 bytes, Mozilla autopush around 130–160, Apple’s web push service around 160–200, and the RFC 8030 specification places no upper bound at all — a service could legitimately hand you a 2 KB URL tomorrow. That collides with real index limits:

• PostgreSQL: a btree entry cannot exceed roughly 2704 bytes (one third of an 8 KB page). Exceeding it fails the INSERT, not the CREATE INDEX, so the problem appears in production rather than in migration.
• MySQL/InnoDB: the index key prefix limit is 3072 bytes with DYNAMIC row format, which is only 768 characters at utf8mb4. Older COMPACT tables cap at 767 bytes total.
• SQL Server: 1700 bytes for a non-clustered index key.

Even where the URL fits, a 200-byte variable-width key wastes buffer cache and slows every lookup. Store the endpoint verbatim in a text column for sending, and put the unique constraint on endpoint_hash, a fixed 32-byte SHA-256 digest. Lookups, joins, dedupe checks and log correlation all use the digest; only the dispatcher reads the URL.

Use a plain SHA-256 for the index key, not an HMAC. The index has to be reproducible from the endpoint alone by any service in your estate, and a rotating HMAC key would invalidate every stored digest on rotation. Reserve the keyed HMAC for the logging identifier, where unlinkability across exports actually matters.

3. The SQL Schema

This is the full PostgreSQL definition. Nullable columns are deliberate: user_id is null for anonymous subscribers who have not yet signed in, and last_success_at is null until the first accepted send.

CREATE TYPE push_subscription_state AS ENUM
  ('pending', 'active', 'stale', 'expired', 'revoked');

CREATE TABLE push_subscription (
  id                   uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  endpoint             text        NOT NULL,
  endpoint_hash        bytea       NOT NULL,
  push_service         text        NOT NULL,
  p256dh               text        NOT NULL,
  auth_sealed          bytea       NOT NULL,
  auth_key_version     smallint    NOT NULL DEFAULT 1,
  content_encoding     text        NOT NULL DEFAULT 'aes128gcm',
  expiration_time      timestamptz,
  user_id              uuid        REFERENCES app_user (id) ON DELETE SET NULL,
  user_agent           text,
  locale               text,
  origin               text        NOT NULL,
  state                push_subscription_state NOT NULL DEFAULT 'pending',
  created_at           timestamptz NOT NULL DEFAULT now(),
  last_seen_at         timestamptz NOT NULL DEFAULT now(),
  last_success_at      timestamptz,
  last_failure_at      timestamptz,
  last_status_code     smallint,
  consecutive_failures smallint    NOT NULL DEFAULT 0,
  revoked_at           timestamptz,
  deleted_at           timestamptz,
  CONSTRAINT push_subscription_hash_uniq UNIQUE (endpoint_hash),
  CONSTRAINT push_subscription_hash_len  CHECK (octet_length(endpoint_hash) = 32),
  CONSTRAINT push_subscription_p256dh_len CHECK (char_length(p256dh) BETWEEN 80 AND 96)
);

-- Fan-out query: every live endpoint for one user.
CREATE INDEX push_subscription_user_live
  ON push_subscription (user_id)
  WHERE state IN ('pending', 'active') AND deleted_at IS NULL;

-- Ageing sweeps: find rows that have gone quiet.
CREATE INDEX push_subscription_sweep
  ON push_subscription (state, last_seen_at)
  WHERE deleted_at IS NULL;

-- Per-provider concurrency accounting and gateway routing.
CREATE INDEX push_subscription_service
  ON push_subscription (push_service, state);

Three notes on the shape. The unique constraint sits on endpoint_hash, never on (user_id, endpoint_hash) — a composite would let the same endpoint attach to two users, which is exactly the corruption you are trying to prevent. The partial indexes exclude soft-deleted rows so the working set stays small even when tombstones accumulate. And deleted_at is separate from state: revoked describes what the push service told you, while deleted_at describes what your retention policy did about it.

For MySQL, swap bytea for VARBINARY(32), timestamptz for TIMESTAMP(6), the enum for a TINYINT with a lookup table, and replace the partial indexes with full ones plus a generated is_live column. For DynamoDB, make the base64url endpoint hash the partition key and keep a global secondary index on user_id.

4. Encryption at Rest for the Auth Secret

p256dh is a public key and needs no protection beyond ordinary access control. auth is a shared secret. Together with the endpoint, it is one of the three inputs required to construct a valid encrypted push message for that user. A leaked database dump does not immediately let an attacker push to your subscribers — Chrome and Firefox bind each subscription to the applicationServerKey it was created with, so the push service rejects a request signed by a different VAPID key — but the row is still durable, user-linkable key material, and both GDPR Article 32 and SOC 2 treat it as such. Encrypt it.

Use envelope encryption rather than a database-level feature. Full-disk or tablespace encryption protects against a stolen drive and nothing else; a SQL injection or an over-broad read replica credential still returns plaintext. Column encryption at the application layer means the ciphertext is what leaves the database.

import crypto from 'node:crypto';

// A 32-byte data key unwrapped from KMS at boot and held only in memory.
// Never inline key material: the same rule that applies to
// process.env.VAPID_PRIVATE_KEY applies here.
const DATA_KEYS = new Map([
  [1, Buffer.from(process.env.SUBSCRIPTION_AUTH_KEY_V1, 'base64')],
  [2, Buffer.from(process.env.SUBSCRIPTION_AUTH_KEY_V2, 'base64')]
]);
const CURRENT_KEY_VERSION = Number(process.env.SUBSCRIPTION_AUTH_KEY_VERSION);

/** Seal the 16-byte auth secret as iv(12) || tag(16) || ciphertext(16). */
export function sealAuth(authBase64Url) {
  const key = DATA_KEYS.get(CURRENT_KEY_VERSION);
  const iv = crypto.randomBytes(12);
  const cipher = crypto.createCipheriv('aes-256-gcm', key, iv);
  const plaintext = Buffer.from(authBase64Url, 'base64url');
  if (plaintext.length !== 16) throw new Error('auth secret must be 16 bytes');
  const body = Buffer.concat([cipher.update(plaintext), cipher.final()]);
  return {
    sealed: Buffer.concat([iv, cipher.getAuthTag(), body]),
    keyVersion: CURRENT_KEY_VERSION
  };
}

/** Open a sealed auth secret using the version recorded on the row. */
export function openAuth(sealed, keyVersion) {
  const key = DATA_KEYS.get(keyVersion);
  if (!key) throw new Error(`no data key for version ${keyVersion}`);
  const iv = sealed.subarray(0, 12);
  const tag = sealed.subarray(12, 28);
  const decipher = crypto.createDecipheriv('aes-256-gcm', key, iv);
  decipher.setAuthTag(tag);
  const plain = Buffer.concat([decipher.update(sealed.subarray(28)), decipher.final()]);
  return plain.toString('base64url');
}

The auth_key_version column is what makes rotation survivable. Roll a new data key, bump CURRENT_KEY_VERSION, and let new writes seal under version 2 while reads still open version 1. A background job re-seals old rows in chunks. Without the version column you are forced into a single-transaction re-encryption of the entire table, which is exactly the migration nobody ever schedules.

Do not encrypt endpoint. It has to be readable by the dispatcher on every send, indexing it would become impossible, and its secrecy value is low — it is a bearer URL that only accepts requests signed by your VAPID key.

5. The Lifecycle State Machine

Subscriptions decay. Browsers get uninstalled, users clear site data, push services garbage-collect registrations that have not been contacted in months. A record has five meaningful states and every transition is driven either by a push service response or by the passage of time.

The transitions are unambiguous because the HTTP status codes are unambiguous. A 201 Created (or 200 OK from older services) sets state = 'active', stamps last_success_at, and zeroes consecutive_failures. A 404 Not Found is ambiguous — it can be a routing blip or a genuinely dead registration — so it increments the failure counter rather than deciding anything. A 410 Gone is terminal on first sight and moves the row straight to revoked; the fan-out mechanics for handling those at volume live in handling 410 Gone responses at scale. A 429 or any 5xx must not touch the lifecycle at all: those are transport problems that belong to the retry layer, and letting them increment a failure counter is how healthy subscribers get deleted during a push service incident.

last_seen_at deserves separate treatment from last_success_at. The former is written by the client — every page load where the service worker confirms an existing subscription should refresh it — and it is your only signal that a user is still around when you have not sent them anything for weeks. Throttle that write: an UPDATE on every page view of a busy site will produce more write amplification than the entire push pipeline. A one-hour throttle in Redis in front of the update is usually enough.

6. Implementing the Upsert

Registration is an upsert, not an insert. The same browser will re-post the same subscription on every service worker activation, and the write must be idempotent.

1. Validate before touching the database. Reject endpoints whose origin is not on your allow-list of known push services, reject p256dh values that do not decode to 65 bytes, and reject auth values that do not decode to 16.
2. Derive the hash. sha256(endpoint) as raw bytes, computed identically in every service that writes to this table.
3. Seal the auth secret with the current data key version.
4. Upsert on the hash. ON CONFLICT (endpoint_hash) DO UPDATE refreshes the keys, the user association and last_seen_at, and resurrects a soft-deleted row rather than colliding with it.
5. Never downgrade a state on upsert. A re-registration should move revoked back to pending, but must not move active back to pending.
6. Return the row id so the client can correlate later pushsubscriptionchange reports.

import crypto from 'node:crypto';
import { Pool } from 'pg';
import { sealAuth } from './auth-crypto.js';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const ALLOWED_HOSTS = new Set([
  'fcm.googleapis.com',
  'updates.push.services.mozilla.com',
  'web.push.apple.com',
  'wns2-by3p.notify.windows.com'
]);

const UPSERT = `
INSERT INTO push_subscription (
  endpoint, endpoint_hash, push_service, p256dh, auth_sealed, auth_key_version,
  expiration_time, user_id, user_agent, locale, origin, state
)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, 'pending')
ON CONFLICT (endpoint_hash) DO UPDATE SET
  p256dh           = EXCLUDED.p256dh,
  auth_sealed      = EXCLUDED.auth_sealed,
  auth_key_version = EXCLUDED.auth_key_version,
  expiration_time  = EXCLUDED.expiration_time,
  user_id          = COALESCE(EXCLUDED.user_id, push_subscription.user_id),
  user_agent       = EXCLUDED.user_agent,
  locale           = EXCLUDED.locale,
  last_seen_at     = now(),
  deleted_at       = NULL,
  revoked_at       = NULL,
  consecutive_failures = 0,
  state = CASE
            WHEN push_subscription.state IN ('expired', 'revoked') THEN 'pending'
            ELSE push_subscription.state
          END
RETURNING id, state, (xmax = 0) AS inserted;
`;

export async function saveSubscription(sub, ctx) {
  const url = new URL(sub.endpoint);
  if (url.protocol !== 'https:' || !ALLOWED_HOSTS.has(url.hostname)) {
    throw new Error('unrecognised push service endpoint');
  }
  if (Buffer.from(sub.keys.p256dh, 'base64url').length !== 65) {
    throw new Error('p256dh is not a 65-byte P-256 point');
  }

  const endpointHash = crypto.createHash('sha256').update(sub.endpoint, 'utf8').digest();
  const { sealed, keyVersion } = sealAuth(sub.keys.auth);

  const { rows } = await pool.query(UPSERT, [
    sub.endpoint,
    endpointHash,
    url.hostname.includes('mozilla') ? 'autopush'
      : url.hostname.includes('apple') ? 'apple'
      : url.hostname.includes('windows') ? 'wns' : 'fcm',
    sub.keys.p256dh,
    sealed,
    keyVersion,
    sub.expirationTime ? new Date(sub.expirationTime) : null,
    ctx.userId ?? null,
    (ctx.userAgent ?? '').slice(0, 400),
    ctx.locale ?? null,
    ctx.origin
  ]);

  return rows[0];
}

(xmax = 0) AS inserted is a PostgreSQL idiom that tells you whether the statement inserted or updated. Emit it as a metric: a sudden collapse in the insert ratio means your client is re-posting subscriptions it should be caching, and a sudden spike means endpoints are churning — usually the sign of a service worker deployment problem.

7. Configuration Reference

Defaults below assume a consumer product sending a few notifications a week. Halve the windows for daily senders; double them for monthly digests.

8. Retention Windows and GDPR Erasure

An endpoint plus a user_id is personal data under GDPR, and even an anonymous subscription is a persistent identifier under ePrivacy. Three obligations apply and they pull in different directions.

Storage limitation (Article 5(1)(e)) says you may not keep a subscription longer than you need it. A dead endpoint has no purpose, so the expiry sweep is a compliance control, not just housekeeping. Accountability (Article 5(2)) says you must be able to demonstrate that consent existed and when it ended, which argues for keeping a tombstone after deletion. Erasure (Article 17) says a valid request collapses every other window to a hard deadline.

Reconcile them with a tiered schedule. Live rows keep everything. On expiry or revocation, soft-delete: set deleted_at, null out user_agent and locale, and keep only endpoint_hash, user_id, created_at and revoked_at. That tombstone answers “did this person consent, and when did it end?” without retaining the credential. After the audit window, hard-delete the row and fold it into an aggregate counter. The unsubscribe-side logging obligations are covered in more depth in GDPR-compliant push unsubscribe logging, and the mechanics of running the deletion itself are in pruning expired push subscriptions from your database.

Erasure has one operational subtlety. If you delete the row and the user’s browser still holds a live subscription, the next re-registration silently recreates it. A genuine erasure therefore has to be paired with a server-side signal that tells the client to call subscription.unsubscribe(), otherwise you have deleted the record and not the relationship.

9. Schema Migrations and Backfill

Subscription tables get large — tens of millions of rows for a mid-size consumer product — and they are on the hot path of every campaign. Migrations must be online.

Add columns as nullable with no default. In PostgreSQL 11+ a constant default is also safe, but a volatile one (gen_random_uuid(), now()) forces a full rewrite. Add the constraint separately as NOT VALID, then VALIDATE CONSTRAINT in a second transaction that takes only a SHARE UPDATE EXCLUSIVE lock. Build indexes CONCURRENTLY and expect to have to clean up an INVALID index if the build is interrupted.

Backfill with keyset pagination, never OFFSET:

-- Chunked backfill of endpoint_hash for rows written before the column existed.
WITH batch AS (
  SELECT id, endpoint
  FROM push_subscription
  WHERE endpoint_hash IS NULL AND id > $1
  ORDER BY id
  LIMIT 5000
)
UPDATE push_subscription s
SET endpoint_hash = digest(b.endpoint, 'sha256')
FROM batch b
WHERE s.id = b.id
RETURNING s.id;

Drive it from a job that carries the last returned id forward as $1, sleeps 50–100 ms between chunks, and aborts if replication lag crosses a threshold. Only after the backfill reports zero remaining rows do you add the NOT NULL and the unique index — adding the unique index first will fail the moment two legacy rows hash to the same endpoint, which happens more often than you expect because old schemas frequently stored the same endpoint twice under different user IDs.

For a hash-algorithm change, add endpoint_hash_v2 alongside, dual-write both from the application, backfill, swap the index, then drop the old column in a later release. Never mutate a unique key in place.

Verification

Confirm the storage layer behaves before you trust it with a campaign.

# 1. Re-post the same subscription twice — the second must update, not insert.
curl -sX POST https://localhost:3000/api/push/subscribe \
  -H 'content-type: application/json' \
  --data-binary @fixtures/subscription.json | tee /tmp/first.json
curl -sX POST https://localhost:3000/api/push/subscribe \
  -H 'content-type: application/json' \
  --data-binary @fixtures/subscription.json | tee /tmp/second.json
# Expect identical "id", "inserted": true then false.

# 2. Confirm exactly one row and a 32-byte digest.
psql "$DATABASE_URL" -c \
  "SELECT count(*), octet_length(endpoint_hash) FROM push_subscription GROUP BY 2;"

# 3. Confirm the auth secret is not readable in the dump.
pg_dump --data-only -t push_subscription "$DATABASE_URL" | grep -c 'BQ_' || echo 'auth sealed'

In DevTools, open Application → Service Workers, click Push to fire a test message, and confirm the row’s last_success_at advanced. Then unsubscribe from the console with navigator.serviceWorker.ready.then(r => r.pushManager.getSubscription()).then(s => s.unsubscribe()) and send again — the next dispatch should return 410 and flip the row to revoked.

Error & Edge-Case Matrix

Cross-Browser Notes

Chrome, Edge and other Chromium browsers issue FCM endpoints on fcm.googleapis.com and are the only major implementation that occasionally populates expirationTime. Firefox issues Mozilla autopush endpoints on updates.push.services.mozilla.com, always with expirationTime: null, and rotates endpoints noticeably less often. Safari issues web.push.apple.com endpoints; on iOS a subscription only exists at all once the site is installed to the Home Screen, which makes iOS rows unusually short-lived when users delete the icon — see Safari & iOS web push integration for the constraints that implies.

Store the derived push_service value rather than inferring it at send time. Provider-specific concurrency limits, retry budgets and connection pools all key off it, and the string is stable in a way that hostname parsing across four vendors is not.

Related

• Pruning expired push subscriptions from your database — the batched, lock-free deletion job that drains the expired and revoked states.
• Deduplicating push subscriptions across devices — why one user legitimately owns many rows and how to fan out to them once.
• Delivery Tracking & Acknowledgment — the ledger that produces the status codes this state machine consumes.
• Retry Logic & Backoff Strategies — where 429 and 5xx responses belong instead of the lifecycle counter.
• Subscription State Synchronization — the client-side contract that keeps last_seen_at honest.

Back to Backend Delivery Architecture & Queue Management

FAQ