max / makenotwork

1004 B · 20 lines History Blame Raw

1	-- Moderation action history: append-only record of all moderation events.
2	-- Provides transparency (user can see their history) and audit trail (admin attribution).
3
4	CREATE TABLE moderation_actions (
5	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
6	user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
7	admin_id UUID NOT NULL REFERENCES users(id),
8	action_type VARCHAR(20) NOT NULL
9	CHECK (action_type IN ('warning', 'content_removal', 'suspension', 'termination')),
10	reason TEXT NOT NULL,
11	-- Optional reference to specific content (item ID for content_removal)
12	content_ref VARCHAR(255),
13	-- NULL while action is active; set when resolved (warning acknowledged, suspension lifted, etc.)
14	resolved_at TIMESTAMPTZ,
15	created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
16	);
17
18	CREATE INDEX idx_moderation_actions_user ON moderation_actions(user_id, created_at DESC);
19	CREATE INDEX idx_moderation_actions_active ON moderation_actions(user_id) WHERE resolved_at IS NULL;
20