AkurAI-Build / migrations/011_notifications.sql

Latest change b3f9a218e1ff51ec87a0dd7c07ff6b98fdb47905 - OLI-275: notifications feed, page and mail for reviews, merges and failed runs by Ólafur Búi Ólafsson

CREATE TABLE notifications (
    id INTEGER PRIMARY KEY,
    subject TEXT NOT NULL CHECK (length(subject) BETWEEN 1 AND 256),
    kind TEXT NOT NULL CHECK (kind IN ('review_requested', 'review_submitted', 'run_failed', 'pull_merged')),
    repository TEXT NOT NULL CHECK (length(repository) BETWEEN 1 AND 64),
    title TEXT NOT NULL CHECK (length(title) BETWEEN 1 AND 300),
    body TEXT NOT NULL DEFAULT '',
    link TEXT NOT NULL DEFAULT '',
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    read_at INTEGER,
    mailed_at INTEGER
) STRICT;

CREATE INDEX notifications_feed ON notifications(subject, read_at, id);
CREATE INDEX notifications_mail ON notifications(mailed_at, id);

CREATE TABLE pull_request_reviewers (
    pull_request_id INTEGER NOT NULL REFERENCES pull_requests(id) ON DELETE CASCADE,
    subject TEXT NOT NULL CHECK (length(subject) BETWEEN 1 AND 256),
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    PRIMARY KEY (pull_request_id, subject)
) STRICT;