Blame · Line-by-line history

`0043_push_subscriptions.sql`

Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.

0043_push_subscriptions.sqlBlame32 lines · 1 contributor

534f04aClaude	1	-- Block M2 — Mobile-class PWA: Web Push subscriptions.
	2	--
	3	-- Persists a Web Push subscription per user / per device. The `endpoint`
	4	-- is unique per browser instance and acts as the identifier we POST to when
	5	-- delivering a notification. `p256dh` + `auth` are the standard W3C keys
	6	-- needed to encrypt the payload for the recipient. `user_agent` is stored
	7	-- for the "Subscribed on this device" UI string.
	8	--
	9	-- Strictly additive. No existing table touched. Per-event push preference
	10	-- columns are appended to `users` so the same `notify` call site can decide
	11	-- whether to fan out a push based on the user's choices.
	12
	13	CREATE TABLE IF NOT EXISTS "push_subscriptions" (
	14	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	15	"user_id" uuid NOT NULL REFERENCES "users"("id") ON DELETE CASCADE,
	16	"endpoint" text NOT NULL,
	17	"p256dh" text NOT NULL,
	18	"auth" text NOT NULL,
	19	"user_agent" text,
	20	"created_at" timestamptz NOT NULL DEFAULT now(),
	21	"last_used_at" timestamptz,
	22	UNIQUE ("user_id", "endpoint")
	23	);
	24
	25	CREATE INDEX IF NOT EXISTS "idx_push_subscriptions_user"
	26	ON "push_subscriptions" ("user_id");
	27
	28	ALTER TABLE "users"
	29	ADD COLUMN IF NOT EXISTS "notify_push_on_mention" boolean NOT NULL DEFAULT true,
	30	ADD COLUMN IF NOT EXISTS "notify_push_on_assign" boolean NOT NULL DEFAULT true,
	31	ADD COLUMN IF NOT EXISTS "notify_push_on_review_request" boolean NOT NULL DEFAULT true,
	32	ADD COLUMN IF NOT EXISTS "notify_push_on_deploy_failed" boolean NOT NULL DEFAULT true;