Skip to content
Sign in

flagon-io/g1t

public
0

g1t/services/events/migrations/0005_inbox.sql

42 lines1,713 bytesCodeBlame
1-- The inbox (src/inbox.rs): one row per person told of an event. Written
2-- as events arrive; a redelivered event finds its rows there already.
3-- Ids are time-sortable, so ordering by id is ordering by time.
4CREATE TABLE inbox_items (
5 id TEXT PRIMARY KEY,
6 -- Whose: a username, lowercased. Usernames never change.
7 username TEXT NOT NULL,
8 -- The event it came from.
9 event_id TEXT NOT NULL,
10 -- Why they were told, such as checks_failed or mentioned.
11 reason TEXT NOT NULL,
12 -- error | warning | success | info
13 severity TEXT NOT NULL,
14 title TEXT NOT NULL,
15 body TEXT NOT NULL,
16 -- The workspace's slug, and owner/name. Both follow renames and transfers.
17 workspace TEXT,
18 repo_id TEXT,
19 repo TEXT,
20 -- issue | pull | run, and its number (an issue or pull request) or id (a run).
21 subject TEXT,
22 number INTEGER,
23 run_id TEXT,
24 -- Who did it: a username, or g1t.
25 actor TEXT,
26 -- RFC 3339 UTC.
27 created_at TEXT NOT NULL,
28 read_at TEXT,
29 done_at TEXT,
30 saved INTEGER NOT NULL DEFAULT 0,
31 snoozed_until TEXT,
32 UNIQUE (event_id, username)
33);
34-- A person's inbox, newest first; what is unread in it, for every page's
35-- count; and what they saved.
36CREATE INDEX inbox_recent ON inbox_items (username, id) WHERE done_at IS NULL;
37CREATE INDEX inbox_unread ON inbox_items (username, severity) WHERE read_at IS NULL AND done_at IS NULL;
38CREATE INDEX inbox_saved ON inbox_items (username, id) WHERE saved = 1;
39CREATE INDEX inbox_done ON inbox_items (username, done_at) WHERE done_at IS NOT NULL;
40-- Renames, transfers and purges find a repository's rows by id.
41CREATE INDEX inbox_repo ON inbox_items (repo_id) WHERE repo_id IS NOT NULL;
42CREATE INDEX inbox_time ON inbox_items (created_at);