Forum / community, built on every verified stack.
Forum / community: slug-keyed category taxonomy, threaded discussions with open/locked status and pinning, and per-reply up/down votes.
40 verified stacks
Showing 12 across 1 app types. Every stack page links its neighbours along each axis, so any combination is a click or two from here.
What Forum / community gives you
Everything here hangs off one containment chain: a category holds threads, a thread holds replies, a reply holds votes, and every link in that chain is onDelete cascade. Dropping a category takes its threads, their replies and those replies' votes with it, which is convenient — and the reason a merge-two-categories feature has to rewrite threads.category_id rather than delete anything. idx_thread_category is what makes both that rewrite and the category front page cheap. Replies are flat. thread_replies has a thread_id and an author_id and no parent column, so a discussion is a list rather than a tree; nesting would be a schema change, not a query change. The upside is a trivial read path — idx_reply_thread returns an entire thread in one index range, in the order it was written.
Two moderation levers sit on threads and they are shaped differently on purpose. status is text guarded by threads_status_check to 'open' or 'locked', so introducing 'archived' later is a rewritten CHECK instead of an ALTER TYPE. is_pinned is a plain boolean, meaning a pin is a flag the listing sorts on rather than a stored position: idx_thread_category narrows to the category, and the pinned-first ordering happens on top of that. Neither lever is enforced against writes — nothing in the database refuses a reply on a locked thread, so the lock is a rule the handler applies. Votes are where a constraint carries real weight.
reply_votes keeps a surrogate id, but its identity is reply_votes_reply_voter_unique on (reply_id, voter_id): one voter, one vote per reply, upserted on that pair when someone flips an up-vote to a down-vote. value is an integer restricted by reply_votes_value_check to -1 or 1, so zero is not a legal value and retracting a vote is a delete rather than an update. Score is therefore always sum(value) taken over the leading reply_id column of that unique index, with no cached total on thread_replies to drift out of sync. Member-level moderation comes from the other side, through idx_thread_author and idx_reply_author.
Built for
categories.slug is unique and indexed by idx_category_slug for the URL; idx_thread_category on threads.category_id pulls that category's threads. Pinned-first ordering comes from the is_pinned boolean and is sorted after the index hit, not stored.
idx_reply_thread on thread_replies.thread_id returns a whole thread in one index range. There is no parent-reply column, so nothing recursive is involved — replies are flat by construction.
reply_votes_reply_voter_unique leads with reply_id, so sum(value) for a reply rides that unique index; reply_votes_value_check restricts value to -1 or 1, which is what makes the sum a plain ups-minus-downs score.
The (reply_id, voter_id) unique is the upsert key: turning an up-vote into a down-vote is one write against the existing row. Zero fails the CHECK, so retracting is a delete instead of a value update.
idx_thread_author and idx_reply_author cover both sides of what a member wrote, and threads.author_id, thread_replies.author_id and reply_votes.voter_id all cascade from user.id, so removing an account clears the trail without a cleanup pass.
Why Forum / community, specifically
Vote identity is enforced by a composite unique on (reply_id, voter_id): one vote per reply per voter, value constrained to (-1, 1) via CHECK rather than a separate enum.
Thread status uses text + CHECK ('open','locked') so new statuses ship without an ALTER TYPE migration; the same pattern applies to vote value.