max / makenotwork

2.7 KB · 52 lines History Blame Raw

1	-- Chat message storage for the livechat crate (wiki livechat-design).
2	--
3	-- Ephemeral, but written down. Chat expires in both consumers, and messages are
4	-- stored anyway for two reasons: a deploy is a symlink swap plus a restart with
5	-- no connection draining, so every client reconnects and needs its backlog
6	-- replayed from a cursor; and moderation has to reach a message that already
7	-- scrolled past. `expires_at` is what keeps that from becoming an archive.
8	--
9	-- One room per community, so `community_id` is the room id the crate calls
10	-- `RoomId`. There is no rooms table: a community with `chat_policy <> 'off'`
11	-- is the room.
12
13	CREATE TABLE chat_messages (
14	-- BIGINT identity, not UUID. The reconnect cursor (`?after=<id>`) needs a
15	-- total order, which a v4 UUID cannot give, and chat is log-shaped. This is
16	-- `livechat::MessageId`.
17	id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
18	community_id UUID NOT NULL REFERENCES communities(id) ON DELETE CASCADE,
19	author_id UUID NOT NULL REFERENCES users(mnw_account_id) ON DELETE CASCADE,
20
21	-- Rendered once at insert through docengine's chat preset, never
22	-- re-rendered: there is no edit path anywhere in the system. Storing the
23	-- HTML rather than the source is safe for the same reason it is elsewhere
24	-- in this schema, sanitization happens in docengine and nowhere else.
25	body_html TEXT NOT NULL,
26
27	created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
28
29	-- Computed at insert from the room's retention policy rather than joined at
30	-- sweep time, which is what lets the sweep be one indexed delete instead of
31	-- a join against every community's policy. Shortening a room's retention is
32	-- therefore a recompute UPDATE over that room, which is also what makes a
33	-- shortened window apply retroactively to messages already sent.
34	expires_at TIMESTAMPTZ NOT NULL
35	);
36
37	-- Backlog replay: `WHERE community_id = $1 AND id > $2 ORDER BY id`, and the
38	-- newest-window variant a fresh connection takes, `ORDER BY id DESC LIMIT n`.
39	-- Both are index-only scans on the hottest path chat has.
40	CREATE INDEX idx_chat_messages_room_cursor ON chat_messages (community_id, id);
41
42	-- The retention sweep, run on MT's scheduler: one `DELETE WHERE expires_at <
43	-- now()` across every room at once. Deliberately not keyed by community: the
44	-- sweep is global, and making it per-room would turn one statement into one per
45	-- community.
46	CREATE INDEX idx_chat_messages_expiry ON chat_messages (expires_at);
47
48	-- Ban purge: one statement keyed by (room, author). Without this index a ban
49	-- sequentially scans the room, and a ban is issued on a hot path at exactly the
50	-- moment the room is busiest.
51	CREATE INDEX idx_chat_messages_author ON chat_messages (community_id, author_id);
52