Blog / CMS, built on every verified stack.
Blog / CMS: authored posts with a draft→published→archived workflow, slug-keyed taxonomy (categories + tags via join), and moderated reader comments.
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 Blog / CMS gives you
The publishing workflow is not a side field on this schema — it is the column the read path turns on. posts.status is text constrained by posts_status_check to 'draft', 'published' or 'archived', and idx_post_status_published indexes (status, published_at) in that order: equality on the status, then an ordered walk of the timestamp. The public index page therefore arrives newest-first with no sort step, the one read here where ordering comes free from the index, and the reason those two columns share a composite instead of sitting in separate indexes. published_at is deliberately not created_at. A draft has a creation time and a null published_at; the timestamp is written when the post goes live, which also lets an editor back-date or schedule by setting it directly.
Nothing at the database level ties the pair together — a row can be 'published' with a null published_at and the CHECK will accept it — so that invariant belongs to whatever handles the transition. Identity is doubled on purpose. slug is unique and is what the URL carries, but comments.post_id and post_tags.post_id both reference posts.id, the generated surrogate key. Renaming a slug rewrites one column and breaks no reference. Taxonomy comes in two shapes and only one of them is wired to posts. post_tags is the real join: a composite primary key on (post_id, tag_id), which makes re-saving a post's tag set an idempotent insert-on-conflict, with idx_post_tags_tag inverting it for a tag archive.
categories is the single-valued half: posts.category_id is a nullable FK onto it with idx_post_category behind it, and ON DELETE set null, so retiring a category unfiles its posts instead of deleting them. Comments land at 'pending' by default, with 'approved' and 'spam' as the other two states comments_status_check allows, so a comment stays invisible until someone promotes it. idx_comment_post keys on post_id alone, so a thread fetch is an index range and the approved-only filter is applied over the rows it returns.
Built for
idx_post_status_published is a composite on (status, published_at): equality on 'published' then an ordered walk of the timestamp, so the listing needs no sort step.
posts.slug carries a unique constraint, making the lookup a single-row index hit; because comments.post_id and post_tags.post_id reference posts.id instead, editing a slug touches no foreign key.
tags.slug is unique with idx_tag_slug behind the URL, and idx_post_tags_tag on post_tags.tag_id collects every post carrying that tag — the reverse direction of the join's (post_id, tag_id) primary key.
post_tags has no surrogate key: (post_id, tag_id) is the primary key, so writing the tag set is an idempotent insert-on-conflict, and the leading post_id lists a post's tags without a second index.
comments.status defaults to 'pending' under comments_status_check ('pending', 'approved', 'spam'), and idx_comment_post fetches a post's thread; the approved-only filter is applied over that index, which is keyed on post_id alone.
Why Blog / CMS, specifically
post_tags is keyed by a composite primary key on (post_id, tag_id) — the tagging identity used by application-level upserts. (Postgres also carries a redundant explicit unique on the same columns; MySQL relies on the composite PK alone.)
Comments default to 'pending' and require explicit promotion to 'approved'; the CHECK on both posts and comments uses text + CHECK rather than pgEnum so new statuses ship without an ALTER TYPE migration.