Skip to content
Sign in

flagon-io/g1t

public
0

g1t/services/events/migrations/0006_inbox_threads.sql

136 lines5,932 bytesCodeBlame
1-- The inbox in threads (src/inbox.rs): one item per person per thing it is
2-- about, brought back to the top by new activity, with a short history;
3-- why each person was told, in a fixed set of reasons; subscriptions to
4-- issues and pull requests; how people watch repositories; and what each
5-- person chose about being told.
6
7-- What the item is about, as one key: `<repo_id>#<number>` for an issue or
8-- pull request, `<repo_id>/run/<workflow>@<branch>` for a workflow on a
9-- branch, `<repo_id>/deploy/<project_id>/<production|number>` for a
10-- project's deployments.
11ALTER TABLE inbox_items ADD COLUMN thread TEXT;
12-- The latest activity: when, as a time-sortable id lists order by, its
13-- event type, and how many things have happened on the thread.
14ALTER TABLE inbox_items ADD COLUMN updated_at TEXT;
15ALTER TABLE inbox_items ADD COLUMN bumped TEXT;
16ALTER TABLE inbox_items ADD COLUMN event TEXT;
17ALTER TABLE inbox_items ADD COLUMN activity INTEGER NOT NULL DEFAULT 1;
18-- A path on the site, for a subject with a page of its own (a deployment).
19ALTER TABLE inbox_items ADD COLUMN link TEXT;
20
21-- Reasons were named for what happened; now they say why the person was told.
22UPDATE inbox_items SET reason = CASE reason
23 WHEN 'agent_asked' THEN 'agent'
24 WHEN 'checks_failed' THEN 'ci_activity'
25 WHEN 'workflow_failed' THEN 'ci_activity'
26 WHEN 'mentioned' THEN 'mention'
27 WHEN 'merged' THEN 'state_change'
28 ELSE 'author'
29END;
30UPDATE inbox_items SET
31 thread = CASE
32 WHEN number IS NOT NULL THEN repo_id || '#' || number
33 WHEN run_id IS NOT NULL THEN repo_id || '/run/' || run_id
34 ELSE 'event/' || event_id
35 END,
36 updated_at = created_at,
37 bumped = id;
38
39-- What happened on each thread, newest first, at most 10 kept. One row per
40-- event per person, so a redelivered event is told once.
41CREATE TABLE inbox_activity (
42 id TEXT PRIMARY KEY,
43 item_id TEXT NOT NULL,
44 username TEXT NOT NULL,
45 event_id TEXT NOT NULL,
46 event TEXT,
47 reason TEXT NOT NULL,
48 severity TEXT NOT NULL,
49 title TEXT NOT NULL,
50 body TEXT NOT NULL,
51 actor TEXT,
52 created_at TEXT NOT NULL,
53 UNIQUE (event_id, username)
54);
55CREATE INDEX inbox_activity_item ON inbox_activity (item_id, id);
56
57-- Items about the same thing become one: every item is a line of its
58-- history, and the newest stays: unread with the most urgent severity of
59-- those unread if any was, and saved if any was.
60INSERT INTO inbox_activity (id, item_id, username, event_id, reason, severity, title, body, actor, created_at)
61SELECT i.id,
62 (SELECT max(j.id) FROM inbox_items j WHERE j.username = i.username AND j.thread = i.thread),
63 i.username, i.event_id, i.reason, i.severity, i.title, i.body, i.actor, i.created_at
64FROM inbox_items i;
65UPDATE inbox_items SET severity = COALESCE((
66 SELECT j.severity FROM inbox_items j
67 WHERE j.username = inbox_items.username AND j.thread = inbox_items.thread AND j.read_at IS NULL AND j.done_at IS NULL
68 ORDER BY CASE j.severity WHEN 'warning' THEN 0 WHEN 'error' THEN 1 WHEN 'success' THEN 2 ELSE 3 END
69 LIMIT 1), severity);
70UPDATE inbox_items SET
71 activity = (SELECT count(*) FROM inbox_items j WHERE j.username = inbox_items.username AND j.thread = inbox_items.thread),
72 created_at = (SELECT min(j.created_at) FROM inbox_items j WHERE j.username = inbox_items.username AND j.thread = inbox_items.thread),
73 saved = (SELECT max(j.saved) FROM inbox_items j WHERE j.username = inbox_items.username AND j.thread = inbox_items.thread),
74 read_at = CASE WHEN EXISTS (
75 SELECT 1 FROM inbox_items j
76 WHERE j.username = inbox_items.username AND j.thread = inbox_items.thread AND j.read_at IS NULL AND j.done_at IS NULL
77 ) THEN NULL ELSE read_at END,
78 done_at = CASE WHEN EXISTS (
79 SELECT 1 FROM inbox_items j
80 WHERE j.username = inbox_items.username AND j.thread = inbox_items.thread AND j.done_at IS NULL
81 ) THEN NULL ELSE done_at END;
82DELETE FROM inbox_items
83WHERE id NOT IN (SELECT max(id) FROM inbox_items GROUP BY username, thread);
84
85CREATE UNIQUE INDEX inbox_thread ON inbox_items (username, thread);
86CREATE INDEX inbox_thread_of ON inbox_items (thread);
87-- Lists go by latest activity.
88DROP INDEX inbox_recent;
89DROP INDEX inbox_saved;
90CREATE INDEX inbox_recent ON inbox_items (username, bumped) WHERE done_at IS NULL;
91CREATE INDEX inbox_saved ON inbox_items (username, bumped) WHERE saved = 1;
92
93-- Subscriptions to issues and pull requests, beyond being their author,
94-- assignee or reviewer (which subscribes a person without a row): someone
95-- who commented or was mentioned, subscribed by hand, unsubscribed, or
96-- ignores the thread.
97CREATE TABLE inbox_subscriptions (
98 username TEXT NOT NULL,
99 -- `<repo_id>#<number>`.
100 thread TEXT NOT NULL,
101 repo_id TEXT NOT NULL,
102 -- subscribed | unsubscribed | ignored
103 state TEXT NOT NULL,
104 -- Why: comment, mention, assign, review_requested, author or manual.
105 reason TEXT,
106 created_at TEXT NOT NULL,
107 -- When the person last chose (subscribed, unsubscribed or ignored by
108 -- hand); null while it only follows what they did.
109 chosen_at TEXT,
110 PRIMARY KEY (thread, username)
111);
112CREATE INDEX inbox_subscriptions_repo ON inbox_subscriptions (repo_id);
113
114-- How people watch repositories. No row: participating.
115CREATE TABLE inbox_watching (
116 username TEXT NOT NULL,
117 repo_id TEXT NOT NULL,
118 -- `owner/name`, kept for listing what a person watches.
119 repo TEXT,
120 -- participating | all | ignore | custom
121 level TEXT NOT NULL,
122 -- With custom: a JSON array of issues, pulls, deployments, security.
123 events TEXT NOT NULL DEFAULT '[]',
124 updated_at TEXT NOT NULL,
125 PRIMARY KEY (repo_id, username)
126);
127CREATE INDEX inbox_watching_user ON inbox_watching (username);
128
129-- What each person chose: the reasons they are emailed for (a JSON array),
130-- and how they watch repositories they create. No row: the defaults.
131CREATE TABLE inbox_settings (
132 username TEXT PRIMARY KEY,
133 email TEXT,
134 default_watch TEXT,
135 updated_at TEXT NOT NULL
136);