Blame · Line-by-line history

`0066_comment_moderation.sql`

Each line is annotated with the commit that last touched it. Click any SHA to jump to that commit and see the surrounding change.

0066_comment_moderation.sqlBlame86 lines · 1 contributor

Normal view Raw

cb5a796Claude	1	-- Comment moderation (anti-impersonation gate).
	2	--
	3	-- Public-repo abuse vector: non-contributors leaving comments to dress up
	4	-- their activity feed as "I contributed there." Per the platform owner:
	5	-- "Users will not be allowed to comment on another public repo unless
	6	-- they have the permission of the author."
	7	--
	8	-- This migration introduces:
	9	--
	10	-- * `moderation_status` on both `issue_comments` and `pr_comments`,
	11	-- with companion `moderated_at` / `moderated_by_user_id` audit
	12	-- columns. Default 'approved' so every existing row stays visible —
	13	-- only NEW comments from non-collaborators flow into the queue.
	14	--
	15	-- * `repo_commenter_trust` — per-repo allow/deny list. A 'trusted'
	16	-- row makes `shouldRequireApproval` return false (auto-approve);
	17	-- a 'banned' row makes the moderator's "mark as spam" decision
	18	-- sticky, so the next comment from that user on that repo also
	19	-- auto-routes to status='rejected' without bothering the owner.
	20	--
	21	-- * Two filtering indexes — one for the owner-facing pending queue
	22	-- (`/:owner/:repo/comments/pending`) and one for moderator-history
	23	-- queries.
	24	--
	25	-- Strictly additive: no existing rows mutate, every query that hasn't
	26	-- been taught about the new column continues to work because the
	27	-- default backfills 'approved' for the legacy population.
	28
	29	ALTER TABLE issue_comments
	30	ADD COLUMN IF NOT EXISTS moderation_status text NOT NULL DEFAULT 'approved';
	31
	32	ALTER TABLE issue_comments
	33	ADD COLUMN IF NOT EXISTS moderated_at timestamptz;
	34
	35	ALTER TABLE issue_comments
	36	ADD COLUMN IF NOT EXISTS moderated_by_user_id uuid REFERENCES users(id) ON DELETE SET NULL;
	37
	38	ALTER TABLE pr_comments
	39	ADD COLUMN IF NOT EXISTS moderation_status text NOT NULL DEFAULT 'approved';
	40
	41	ALTER TABLE pr_comments
	42	ADD COLUMN IF NOT EXISTS moderated_at timestamptz;
	43
	44	ALTER TABLE pr_comments
	45	ADD COLUMN IF NOT EXISTS moderated_by_user_id uuid REFERENCES users(id) ON DELETE SET NULL;
	46
	47	CREATE TABLE IF NOT EXISTS repo_commenter_trust (
	48	id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
	49	repository_id uuid NOT NULL REFERENCES repositories(id) ON DELETE CASCADE,
	50	commenter_user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	51	status text NOT NULL,
	52	granted_by_user_id uuid REFERENCES users(id) ON DELETE SET NULL,
	53	granted_at timestamptz NOT NULL DEFAULT now()
	54	);
	55
	56	CREATE UNIQUE INDEX IF NOT EXISTS repo_commenter_trust_unique
	57	ON repo_commenter_trust (repository_id, commenter_user_id);
	58
	59	CREATE INDEX IF NOT EXISTS repo_commenter_trust_repo_status
	60	ON repo_commenter_trust (repository_id, status);
	61
	62	-- Owner-facing queue: "list every pending comment on issues in MY repo".
	63	-- A partial index on the pending state keeps this fast even when the
	64	-- repo has tens of thousands of approved comments.
	65	CREATE INDEX IF NOT EXISTS issue_comments_pending_status
	66	ON issue_comments (moderation_status)
	67	WHERE moderation_status = 'pending';
	68
	69	CREATE INDEX IF NOT EXISTS pr_comments_pending_status
	70	ON pr_comments (moderation_status)
	71	WHERE moderation_status = 'pending';
	72
	73	-- Moderator history queries — "everything user X has actioned, newest
	74	-- first". Useful for the audit trail and any future moderator-leaderboard.
	75	CREATE INDEX IF NOT EXISTS issue_comments_moderated_by
	76	ON issue_comments (moderated_by_user_id, moderated_at DESC);
	77
	78	CREATE INDEX IF NOT EXISTS pr_comments_moderated_by
	79	ON pr_comments (moderated_by_user_id, moderated_at DESC);
	80
	81	-- /settings/notifications toggle — "Pending comment requests". Defaults
	82	-- ON so a new repo owner doesn't miss queued comments out of the gate.
	83	-- The notification is always written (so it shows up in /inbox); this
	84	-- flag gates email/push fan-out only.
	85	ALTER TABLE users
	86	ADD COLUMN IF NOT EXISTS notify_email_on_pending_comment boolean NOT NULL DEFAULT true;