shithub Public

Watch 1 Fork 0 Star 0
MySQL · 6191 bytes Raw Blame History
1 -- SPDX-License-Identifier: AGPL-3.0-or-later
2 --
3 -- S29 notifications inbox + per-thread subscription + email send log.
4 --
5 -- `domain_events` was already shipped in S26 (per the S00–S25
6 -- audit's forward-deferral). The fan-out worker this sprint lands
7 -- consumes from there.
8 --
9 -- Schema decisions:
10 --
11 -- * `notifications` is the user-facing inbox. One row per
12 -- (recipient, thread). Multiple events on the same thread coalesce
13 -- into the same row (last_event_at + last_actor_user_id update).
14 --
15 -- * `notification_threads` is the per-user explicit subscription
16 -- override on top of the per-repo `watches` rule. A row here lets
17 -- the user pin themselves to a thread (or ignore it) regardless
18 -- of the repo-level watch.
19 --
20 -- * `notification_email_log` is for de-dup, abuse triage, and the
21 -- storm dampener's per-thread cap.
22 --
23 -- * `domain_events_processed` records the fan-out worker's cursor
24 -- so a restart resumes where it stopped, and so the worker can
25 -- detect drift if a row is somehow consumed twice. Keyed
26 -- (consumer, last_event_id) so S33 webhooks shares the table
27 -- shape with its own consumer name later.
28
29 -- +goose Up
30
31 CREATE TYPE notification_thread_kind AS ENUM ('issue', 'pr');
32
33 CREATE TABLE notifications (
34 id bigserial PRIMARY KEY,
35 recipient_user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
36 kind text NOT NULL,
37 reason text NOT NULL,
38 repo_id bigint REFERENCES repos(id) ON DELETE CASCADE,
39 thread_kind notification_thread_kind,
40 thread_id bigint,
41 -- source_event_id points at the most recent domain_events row
42 -- this notification was last touched by. Useful for debugging
43 -- + for the read-state UI ("which event did I last see?").
44 source_event_id bigint,
45 unread boolean NOT NULL DEFAULT true,
46 last_event_at timestamptz NOT NULL DEFAULT now(),
47 last_actor_user_id bigint REFERENCES users(id) ON DELETE SET NULL,
48 summary jsonb NOT NULL DEFAULT '{}'::jsonb,
49 created_at timestamptz NOT NULL DEFAULT now(),
50 updated_at timestamptz NOT NULL DEFAULT now(),
51
52 CONSTRAINT notifications_kind_length CHECK (char_length(kind) BETWEEN 1 AND 64),
53 CONSTRAINT notifications_reason_length CHECK (char_length(reason) BETWEEN 1 AND 32)
54 );
55
56 -- Inbox view: "all my notifications, recent first."
57 CREATE INDEX notifications_recipient_recent_idx
58 ON notifications (recipient_user_id, last_event_at DESC);
59
60 -- Unread badge count: filtered partial index keeps the count cheap
61 -- even when the recipient has tens of thousands of read rows.
62 CREATE INDEX notifications_recipient_unread_idx
63 ON notifications (recipient_user_id)
64 WHERE unread = true;
65
66 -- Coalesce lookup: "do I already have an inbox row for this thread?"
67 CREATE UNIQUE INDEX notifications_thread_coalesce_idx
68 ON notifications (recipient_user_id, thread_kind, thread_id)
69 WHERE thread_id IS NOT NULL;
70
71 CREATE TRIGGER set_updated_at BEFORE UPDATE ON notifications
72 FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at();
73
74 -- ─── per-thread subscription ────────────────────────────────────────
75
76 CREATE TABLE notification_threads (
77 recipient_user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
78 thread_kind notification_thread_kind NOT NULL,
79 thread_id bigint NOT NULL,
80 subscribed boolean NOT NULL,
81 reason text NOT NULL,
82 updated_at timestamptz NOT NULL DEFAULT now(),
83 PRIMARY KEY (recipient_user_id, thread_kind, thread_id)
84 );
85
86 CREATE INDEX notification_threads_thread_idx
87 ON notification_threads (thread_kind, thread_id);
88
89 CREATE TRIGGER set_updated_at BEFORE UPDATE ON notification_threads
90 FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at();
91
92 -- ─── email send log (storm dampener + abuse triage) ─────────────────
93
94 CREATE TABLE notification_email_log (
95 id bigserial PRIMARY KEY,
96 recipient_user_id bigint NOT NULL REFERENCES users(id) ON DELETE CASCADE,
97 notification_id bigint REFERENCES notifications(id) ON DELETE SET NULL,
98 thread_kind notification_thread_kind,
99 thread_id bigint,
100 sent_at timestamptz NOT NULL DEFAULT now(),
101 message_id text
102 );
103
104 -- Storm dampener: "did I email this recipient about this thread in
105 -- the last N minutes?" The partial-index shape isn't great because
106 -- the predicate is time-relative; just key on the lookup columns
107 -- and let the planner narrow.
108 CREATE INDEX notification_email_log_recent_idx
109 ON notification_email_log (recipient_user_id, thread_kind, thread_id, sent_at DESC);
110
111 -- Per-recipient absolute rate cap (e.g. "≤ 100 emails per hour"):
112 -- recency-sorted by recipient.
113 CREATE INDEX notification_email_log_recipient_idx
114 ON notification_email_log (recipient_user_id, sent_at DESC);
115
116 -- ─── fan-out cursor ────────────────────────────────────────────────
117
118 CREATE TABLE domain_events_processed (
119 consumer text NOT NULL,
120 last_event_id bigint NOT NULL,
121 updated_at timestamptz NOT NULL DEFAULT now(),
122 PRIMARY KEY (consumer)
123 );
124
125 CREATE TRIGGER set_updated_at BEFORE UPDATE ON domain_events_processed
126 FOR EACH ROW EXECUTE FUNCTION tg_set_updated_at();
127
128 -- Seed the notify cursor at 0 so a fresh DB doesn't trip the
129 -- "first run" branch in the worker (which would otherwise have
130 -- to handle the absent-row case).
131 INSERT INTO domain_events_processed (consumer, last_event_id)
132 VALUES ('notify_fanout', 0);
133
134 -- +goose Down
135 DROP TABLE IF EXISTS domain_events_processed;
136 DROP TABLE IF EXISTS notification_email_log;
137 DROP TABLE IF EXISTS notification_threads;
138 DROP TABLE IF EXISTS notifications;
139 DROP TYPE IF EXISTS notification_thread_kind;
140