63 lines
3.6 KiB
SQL
63 lines
3.6 KiB
SQL
-- Post comments — two-level threads, likes, pinning, reports.
|
|
--
|
|
-- Threading is deliberately ONE level deep: `parent_id` is NULL for a
|
|
-- top-level comment and otherwise points at a TOP-LEVEL comment (the thread
|
|
-- root). When a user replies to a reply, the service flattens the new comment
|
|
-- onto the same root and records `reply_to_user_id` so the UI can render the
|
|
-- "@handle" it was addressed to — so every reply lives under one thread.
|
|
-- ON DELETE CASCADE on parent_id means deleting a root removes its whole thread.
|
|
CREATE TABLE IF NOT EXISTS post_comments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
parent_id UUID REFERENCES post_comments(id) ON DELETE CASCADE,
|
|
-- The user a reply is addressed to (for the "@handle" prefix). NULL on
|
|
-- top-level comments. SET NULL if that user is later deleted.
|
|
reply_to_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
body TEXT NOT NULL,
|
|
-- Denormalized like counter (the UI shows only the count; comment_likes
|
|
-- keeps the relations). Kept in sync on like/unlike in the same txn — comment
|
|
-- likes are far lower-volume than post likes, so no Redis batching here.
|
|
likes_count INTEGER NOT NULL DEFAULT 0,
|
|
pinned_at TIMESTAMPTZ, -- non-null = pinned (top-level only)
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- Dedup: a user can't post the identical text twice in the same scope (post +
|
|
-- thread). COALESCE folds top-level (NULL parent) onto a fixed sentinel so the
|
|
-- index covers it; md5(body) keeps the key small for long comments.
|
|
CREATE UNIQUE INDEX IF NOT EXISTS post_comments_dedup
|
|
ON post_comments (post_id, user_id, COALESCE(parent_id, '00000000-0000-0000-0000-000000000000'::uuid), md5(body));
|
|
|
|
-- Listing a post's top-level comments, and a thread's replies (oldest first).
|
|
CREATE INDEX IF NOT EXISTS post_comments_post_idx ON post_comments (post_id, created_at);
|
|
CREATE INDEX IF NOT EXISTS post_comments_parent_idx ON post_comments (parent_id, created_at);
|
|
|
|
-- At most one pinned comment per post.
|
|
CREATE UNIQUE INDEX IF NOT EXISTS post_comments_one_pin
|
|
ON post_comments (post_id) WHERE pinned_at IS NOT NULL;
|
|
|
|
-- Comment likes — membership rows backing the displayed count. Idempotent via PK.
|
|
CREATE TABLE IF NOT EXISTS comment_likes (
|
|
comment_id UUID NOT NULL REFERENCES post_comments(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (comment_id, user_id)
|
|
);
|
|
-- "Which comments has this viewer liked?" — viewer-state hydration across a thread.
|
|
CREATE INDEX IF NOT EXISTS comment_likes_user_idx ON comment_likes (user_id);
|
|
|
|
-- Comment reports — mirrors post_reports, keyed to a comment.
|
|
CREATE TABLE IF NOT EXISTS comment_reports (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
reporter_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
comment_id UUID NOT NULL REFERENCES post_comments(id) ON DELETE CASCADE,
|
|
category VARCHAR(32) NOT NULL,
|
|
reason TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
resolved_at TIMESTAMPTZ
|
|
);
|
|
CREATE INDEX IF NOT EXISTS comment_reports_unresolved_idx
|
|
ON comment_reports (created_at DESC) WHERE resolved_at IS NULL;
|