Low Level Design: Threaded Discussion System

Data Model

Closure table for efficient subtree queries:

ThreadAncestor (
  ancestor_id   BIGINT,
  descendant_id BIGINT,
  depth         INT,
  PRIMARY KEY (ancestor_id, descendant_id)
)

Post (
  id         BIGINT PK,
  thread_id  BIGINT,
  parent_id  BIGINT NULL,
  user_id    BIGINT,
  body       TEXT,
  score      INT DEFAULT 0,
  created_at TIMESTAMP,
  deleted    BOOL DEFAULT FALSE
)

Each post inserts a self-referencing row (depth=0) plus one row per ancestor in ThreadAncestor. Subtree fetches are a single join — no recursive CTEs needed.

Voting

Vote (
  user_id BIGINT,
  post_id BIGINT,
  value   SMALLINT CHECK (value IN (1,-1)),
  PRIMARY KEY (user_id, post_id)
)

Score update is atomic:

UPDATE post SET score = score + :delta WHERE id = :post_id;

On vote change: subtract old value, add new value in a single UPDATE.

Sort Algorithms

New — ORDER BY created_at DESC
Top — ORDER BY score DESC
Hot — Wilson score lower confidence bound (accounts for vote count); precomputed periodically and stored as hot_score FLOAT on post row

Loading a Thread

Fetch all posts in a thread via closure table join, then build the tree client-side or server-side from the flat list using parent_id references. For large threads, limit initial fetch to depth <= N.

SELECT p.* FROM post p
JOIN thread_ancestor ta ON ta.descendant_id = p.id
WHERE ta.ancestor_id = :root_post_id AND ta.depth <= 3
ORDER BY p.score DESC;

Lazy Loading

Load top 3 replies per parent on initial render. “Load more replies” button triggers a paginated fetch for the next page of children for that specific parent_id.

Collapse / Expand

Track expandedPostIds in client state (Set). Toggle on click. Collapsed nodes skip rendering their subtree. No server round-trip needed for collapse.

Thread Lock

forum_thread.locked = TRUE — checked on write path. Locked threads return 403 on new post attempts. Display lock badge in UI.

Thread Subscription

Subscribe table: Subscription(user_id, thread_id). On post approval, notify subscribed users whose posts are direct parents of the new reply. Notifications sent via async job queue (email or in-app).

Moderator Tools

Remove subtree: use closure table to find all descendants, then set deleted=TRUE on all. Soft delete preserves structure; body replaced with [removed] on read.

UPDATE post SET deleted=TRUE
WHERE id IN (
  SELECT descendant_id FROM thread_ancestor WHERE ancestor_id=:target_id
);

{
“@context”: “https://schema.org”,
“@type”: “FAQPage”,
“mainEntity”: [
{
“@type”: “Question”,
“name”: “What data structure is best for storing threaded discussions?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “A closure table (ThreadAncestor with ancestor_id, descendant_id, depth) supports efficient subtree queries without recursive CTEs. Each post inserts one row per ancestor, enabling subtree fetches with a single join. This is preferable to adjacency lists for deeply nested threads.”
}
},
{
“@type”: “Question”,
“name”: “How do you implement upvotes and downvotes in a discussion system?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Store votes in a Vote table with a composite primary key (user_id, post_id) to enforce one vote per user. Use value +1 or -1. Update the post's score atomically with UPDATE post SET score = score + delta. On vote change, subtract the old value and add the new value in one operation.”
}
},
{
“@type”: “Question”,
“name”: “How do Hot, New, and Top sorting algorithms differ for threads?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “New sorts by created_at DESC. Top sorts by score DESC. Hot uses the Wilson score lower confidence bound, which balances total votes and vote ratio — a post with 10 upvotes from 10 votes ranks higher than one with 100 upvotes from 1000 votes. Hot scores are precomputed periodically.”
}
},
{
“@type”: “Question”,
“name”: “How do you handle lazy loading and collapse in a threaded UI?”,
“acceptedAnswer”: {
“@type”: “Answer”,
“text”: “Load the top 3 replies per parent on initial render. A 'load more' button fetches the next page of children for that parent_id. Collapse state is tracked client-side as a set of expanded post IDs — collapsing a node skips rendering its subtree without a server round-trip.”
}
}
]
}