An AdonisJS content platform schema — courses, lessons, threaded discussions, watch progress, and Stripe billing. Designed to keep tree reads fast: parent, root, and depth get denormalised onto every row instead of using recursive CTEs.
What this Adocasts schema covers
Lessons, screencasts, and livestreams with revisions
Series and learning paths via collections
Threaded discussions, lesson requests, and Q&A
Stripe billing, plans, and invoice history
Watch progress, history, and watchlists
Display ad slots with per-impression events
A few decisions the diagram can't show:
comments is one thread store for posts, discussions, and lesson requests. reply_to, root_parent_id, and level_index denormalize the tree so reads skip recursive CTEs.
taxonomies keeps parent_id, root_parent_id, and level_index on every row for the same reason.
post_snapshots is append-only with revision and revised_by, giving full edit history per post.
posts.body_blocks is jsonb for the block editor; body_type_id picks the renderer.
histories, progresses, and watchlists all carry a (post_id, collection_id, taxonomy_id) nullable triple, but only histories and watchlists use all three today — progresses is post-only in practice (its collection_id is deprecated).
Most *_type_id and state_id columns reference TypeScript enums, not lookup tables.
Good fork target for an AdonisJS content site with Stripe subscriptions and threaded comments. Tree columns are fast but you have to keep them in sync on every write.
AdonisJS platform for screencasts, courses, and lessons — Adocasts is the open-source AdonisJS codebase behind adocasts.com, Tom Gobich's tutorial platform for developers learning the AdonisJS framework.
