internal/store/migrations/0059_push.up.sql

 1-- Apple devices an account has registered, and the queue of pushes bound
 2-- for them. The mail queue's table is named `notifications`, so this one
 3-- cannot be; the columns mirror it so the drainer is the mailer's loop.
 4CREATE TABLE push_devices (
 5    id           INTEGER PRIMARY KEY,
 6    user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 7    token        TEXT NOT NULL UNIQUE,
 8    label        TEXT NOT NULL DEFAULT '',
 9    created_at   TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
10    last_seen_at TEXT
11);
12CREATE INDEX push_devices_user ON push_devices(user_id);
13
14CREATE TABLE push_queue (
15    id              INTEGER PRIMARY KEY,
16    device_id       INTEGER NOT NULL REFERENCES push_devices(id) ON DELETE CASCADE,
17    title           TEXT NOT NULL,
18    body            TEXT NOT NULL,
19    path            TEXT NOT NULL,
20    attempts        INTEGER NOT NULL DEFAULT 0,
21    next_attempt_at TEXT,
22    sent_at         TEXT,
23    failed_at       TEXT,
24    last_error      TEXT,
25    created_at      TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))
26);
27CREATE INDEX push_queue_due ON push_queue(next_attempt_at)
28    WHERE sent_at IS NULL AND failed_at IS NULL;
29
30-- Whether activity reaches the account's registered devices. Defaults on
31-- and costs nothing for an account with no devices; it exists so a user
32-- with a phone and an iPad silences both without deregistering each.
33ALTER TABLE users ADD COLUMN notify_push INTEGER NOT NULL DEFAULT 1;