g1t/services/events/migrations/0005_inbox.sql
Pick any line to see why it is the way it is: the commit, the pull request and issue it came from, and what the agent was thinking.
| Inbox: the events service tells people what needs them as events arrive | 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. | |
| 4 | CREATE 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. | |
| 36 | CREATE INDEX inbox_recent ON inbox_items (username, id) WHERE done_at IS NULL; | |
| 37 | CREATE INDEX inbox_unread ON inbox_items (username, severity) WHERE read_at IS NULL AND done_at IS NULL; | |
| 38 | CREATE INDEX inbox_saved ON inbox_items (username, id) WHERE saved = 1; | |
| 39 | CREATE 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. | |
| 41 | CREATE INDEX inbox_repo ON inbox_items (repo_id) WHERE repo_id IS NOT NULL; | |
| 42 | CREATE INDEX inbox_time ON inbox_items (created_at); |