19 lines
939 B
SQL
19 lines
939 B
SQL
-- Web Push subscriptions. One row per browser/device push endpoint a user
|
|
-- has granted. The endpoint is globally unique (it's a per-subscription URL
|
|
-- at the push service), so re-subscribing the same browser upserts. p256dh
|
|
-- and auth are the subscription's encryption keys (base64url). Dead
|
|
-- endpoints (404/410 from the push service) are pruned on send.
|
|
CREATE TABLE IF NOT EXISTS push_subscriptions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
endpoint TEXT NOT NULL UNIQUE,
|
|
p256dh TEXT NOT NULL,
|
|
auth TEXT NOT NULL,
|
|
user_agent TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
last_used_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS push_subscriptions_user_idx
|
|
ON push_subscriptions (user_id);
|