-- One row per browser subscription. `endpoint` is the push service's
-- unguessable URL and is unique by construction; `subject` is the
-- rastrillo session subject that enrolled it. No foreign key to any
-- users table: apps own their identity schema.
CREATE TABLE aviso_subscriptions (
    id                TEXT    NOT NULL PRIMARY KEY,
    endpoint          TEXT    NOT NULL UNIQUE,
    subject           TEXT    NOT NULL CHECK (length(subject) > 0),
    p256dh            TEXT    NOT NULL,
    auth              TEXT    NOT NULL,
    vapid_key_id      TEXT    NOT NULL,
    revision          INTEGER NOT NULL DEFAULT 1 CHECK (revision > 0),
    created_at        INTEGER NOT NULL,
    last_confirmed_at INTEGER NOT NULL
);
CREATE INDEX aviso_subscriptions_subject   ON aviso_subscriptions(subject);
CREATE INDEX aviso_subscriptions_confirmed ON aviso_subscriptions(last_confirmed_at);