internal/store/migrations/0034_inbox.up.sql

 1-- The in-app notification inbox. The `notifications` table is the outbound
 2-- mail queue and keeps that job; this is the per-user list a client reads.
 3-- A row is a link plus enough text to decide whether to follow it, so the
 4-- list renders without touching the issue or merge request it points at.
 5CREATE TABLE inbox (
 6    id         INTEGER PRIMARY KEY,
 7    user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
 8    repo_id    INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
 9    kind       TEXT NOT NULL,
10    actor      TEXT NOT NULL,
11    summary    TEXT NOT NULL,
12    path       TEXT NOT NULL,
13    created_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now')),
14    read_at    TEXT
15);
16-- The unread list and the badge count are the only reads, both newest
17-- first per user.
18CREATE INDEX inbox_unread ON inbox(user_id, read_at, id DESC);
19
20-- Watching widens who hears about a repository beyond its owners and a
21-- thread's participants; muting narrows it, and wins over both. Absence of
22-- a row is the default: owners and participants, nobody else.
23CREATE TABLE repo_watchers (
24    repo_id INTEGER NOT NULL REFERENCES repos(id) ON DELETE CASCADE,
25    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
26    state   TEXT NOT NULL CHECK (state IN ('watching', 'muted')),
27    PRIMARY KEY (repo_id, user_id)
28);