max / makenotwork

767 B · 17 lines History Blame Raw

1	CREATE TABLE IF NOT EXISTS threads (
2	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3	category_id UUID NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
4	author_id UUID NOT NULL REFERENCES users(mnw_account_id),
5	title TEXT NOT NULL,
6	pinned BOOLEAN NOT NULL DEFAULT false,
7	locked BOOLEAN NOT NULL DEFAULT false,
8	created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
9	last_activity_at TIMESTAMPTZ NOT NULL DEFAULT now()
10	);
11
12	-- Category thread listing: pinned first, then by last activity
13	CREATE INDEX IF NOT EXISTS idx_threads_category_listing
14	ON threads (category_id, pinned DESC, last_activity_at DESC);
15
16	CREATE INDEX IF NOT EXISTS idx_threads_author ON threads (author_id);
17