66 lines
2.9 KiB
SQL
66 lines
2.9 KiB
SQL
|
-- +goose Up
|
||
|
|
-- +goose StatementBegin
|
||
|
|
-- Web Push channel for release notifications. Browser PushSubscriptions are
|
||
|
|
-- profile-scoped; payloads are end-to-end encrypted (RFC 8291) so the
|
||
|
|
-- browser vendor's push service never sees notification content. profile_id
|
||
|
|
-- has no FK: profiles may live in per-user SQLite stores; deletion cleans up
|
||
|
|
-- in code.
|
||
|
|
CREATE TABLE public.web_push_subscriptions (
|
||
|
|
id text PRIMARY KEY,
|
||
|
|
user_id integer NOT NULL,
|
||
|
|
profile_id text NOT NULL,
|
||
|
|
-- The push-service URL is unique per browser registration. A
|
||
|
|
-- resubscription from the same browser under a different profile
|
||
|
|
-- reassigns the row (one endpoint notifies exactly one profile).
|
||
|
|
endpoint text NOT NULL,
|
||
|
|
p256dh text NOT NULL,
|
||
|
|
auth text NOT NULL,
|
||
|
|
device_name varchar(128) NOT NULL DEFAULT '',
|
||
|
|
enabled boolean NOT NULL DEFAULT true,
|
||
|
|
consecutive_failures integer NOT NULL DEFAULT 0,
|
||
|
|
last_success_at timestamptz,
|
||
|
|
last_failure_at timestamptz,
|
||
|
|
last_failure_status integer,
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
updated_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
CONSTRAINT web_push_subscriptions_endpoint_key UNIQUE (endpoint)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX web_push_subscriptions_profile_idx
|
||
|
|
ON public.web_push_subscriptions (profile_id);
|
||
|
|
CREATE INDEX web_push_subscriptions_profile_enabled_idx
|
||
|
|
ON public.web_push_subscriptions (profile_id)
|
||
|
|
WHERE enabled;
|
||
|
|
|
||
|
|
-- Durable dispatch outbox + retry state, mirroring webhook_delivery_attempts:
|
||
|
|
-- `pending` rows are enqueued in the fanout transaction, claimed post-commit,
|
||
|
|
-- and swept by the retry worker after a crash.
|
||
|
|
CREATE TABLE public.web_push_delivery_attempts (
|
||
|
|
id text PRIMARY KEY,
|
||
|
|
notification_delivery_id text NOT NULL REFERENCES public.notification_deliveries(id) ON DELETE CASCADE,
|
||
|
|
subscription_id text NOT NULL REFERENCES public.web_push_subscriptions(id) ON DELETE CASCADE,
|
||
|
|
attempt_number integer NOT NULL,
|
||
|
|
attempted_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
next_retry_at timestamptz,
|
||
|
|
http_status integer,
|
||
|
|
outcome text NOT NULL,
|
||
|
|
failure_message varchar(256),
|
||
|
|
CONSTRAINT web_push_delivery_attempts_unique UNIQUE (subscription_id, notification_delivery_id, attempt_number),
|
||
|
|
CONSTRAINT web_push_delivery_attempts_outcome_check CHECK (outcome IN ('pending', 'delivered', 'retrying', 'failed'))
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX web_push_delivery_attempts_retry_idx
|
||
|
|
ON public.web_push_delivery_attempts (outcome, next_retry_at);
|
||
|
|
-- Serves the per-delivery claim (ClaimPendingForDelivery) and, critically,
|
||
|
|
-- the ON DELETE CASCADE from notification_deliveries: without it every
|
||
|
|
-- retention delete seq-scans this table once per deleted delivery row.
|
||
|
|
CREATE INDEX web_push_delivery_attempts_delivery_idx
|
||
|
|
ON public.web_push_delivery_attempts (notification_delivery_id);
|
||
|
|
-- +goose StatementEnd
|
||
|
|
|
||
|
|
-- +goose Down
|
||
|
|
-- +goose StatementBegin
|
||
|
|
DROP TABLE IF EXISTS public.web_push_delivery_attempts;
|
||
|
|
DROP TABLE IF EXISTS public.web_push_subscriptions;
|
||
|
|
-- +goose StatementEnd
|