-- Notifications: an actor did something (mention, assignment) that a user should
-- see. Rendered by resolving the linked actor/repo/issue at display time.
CREATE TABLE notifications (
    id         TEXT PRIMARY KEY,
    user_id    TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    actor_id   TEXT REFERENCES users(id) ON DELETE SET NULL,
    kind       TEXT NOT NULL,
    repo_id    TEXT REFERENCES repos(id) ON DELETE CASCADE,
    issue_id   TEXT REFERENCES issues(id) ON DELETE CASCADE,
    read_at    BIGINT,
    created_at BIGINT NOT NULL
);
CREATE INDEX idx_notifications_user ON notifications (user_id, read_at);