Database Schema — Journeys, Home & Content¶

Draft 2 · 2026-08-12 · Companion to journeys-and-home.md — review/optimize before implementation

Column-level schema for every table the Journeys + Home design creates or reshapes. Wagtail's own tables (revisions, images, documents) are referenced, not redefined. All content tables also get Wagtail DraftStateMixin columns (live, has_unpublished_changes, first_published_at, …) — shown once here, implied elsewhere.

Conventions: PK = bigint identity primary key (implied on every table). FKs are bigint with ON DELETE noted. ❖ = indexed beyond the automatic FK/unique indexes.

ER overview¶

erDiagram
    THEME ||--o{ PASSAGE : "primary_theme"
    THEME ||--o{ WRITING : "primary_theme"
    THEME ||--o{ JOURNEY : "primary_theme"
    BOOK ||--o{ WRITING : "source"
    BOOK ||--o{ PASSAGE : "source"
    WRITING ||--o{ PASSAGE : "source_writing"
    JOURNEY ||--o{ JOURNEY_CHAPTER : "has ordered"
    JOURNEY_CHAPTER }o--|| PASSAGE : "XOR ref"
    JOURNEY_CHAPTER }o--|| WRITING : "XOR ref"
    JOURNEY_CHAPTER }o--|| ARTICLE : "XOR ref"
    JOURNEY ||--o{ RELATED_JOURNEY : "none or 2-4 ordered"
    HOME ||--o{ HOME_JOURNEY_PIN : "≤5"
    HOME ||--o{ HOME_WHATS_NEW_PIN : ""
    HOME }o--|| THEME : "theme_of_week"
    USER ||--o{ JOURNEY_PROGRESS : ""
    USER ||--o{ CHAPTER_COMPLETION : ""
    USER ||--o{ BOOKMARK : ""
    USER ||--o{ REFLECTION : ""
    JOURNEY ||--o{ JOURNEY_PROGRESS : ""
    JOURNEY_CHAPTER ||--o{ CHAPTER_COMPLETION : ""

Taxonomy¶

data_theme¶

The API projects colour/icon/background/home_background/lottie + design_extra as one design JSON object; assets stay individual FK columns so the CMS captures and validates each one properly (choosers, hex validation, referential integrity).

data_tagcategory and data_tag are unchanged from the current build.

Content (evergreen + editorial)¶

Shared columns on all four: review_status varchar(16) (ai_draft | reviewed, default ai_draft) ❖, is_ai_generated boolean (CMS-only, permanent), DraftState/Revision columns.

How primary theme is tracked. Each content model (and Journey) has a themes M2M for the full, unordered set (powers the graph/discovery) plus a scalar primary_theme_id FK column naming the one theme that drives visuals (reading-screen colour, card tint, theme assets). Primary is a dedicated column, not a flag on the M2M rows — the editor picks it in the CMS. The intended rule is primary_theme ∈ themes (it shouldn't point outside the set), though that is not yet enforced in clean(). The API returns primary_theme as a scalar theme_id (nullable — the FE resolves it against its client-cached theme set and falls back to the active theme when null); the theme object is never re-embedded per card. (Alternative considered: an is_primary boolean on a custom M2M through-table with a partial-unique "one-per-content" — rejected as heavier given the existing ParentalManyToManyField themes.)

data_passage¶

(No is_new_eligible — Passage owns the hero and isn't part of What's New.)

M2M joins: data_passage_themes, data_passage_tags. Intended app-level rule primary_theme ∈ themes — not yet enforced (Passage.clean() validates only the source/source_writing XOR).

data_writing¶

M2M joins: data_writing_themes, data_writing_tags.

data_book¶

M2M joins: data_book_themes, data_book_tags. (Future: buy_link.)

data_article¶

Curation¶

data_journey¶

M2M join: data_journey_themes. DraftState/Revision columns (publishable) — a Journey is the single publishable unit; its chapters are captured in the Journey's revision.

The emblem is one image, not two. It is the journey-card artwork, and it is the collectible the reader earns. The FE blurs it and lifts the blur as chapters complete (the "journey becomes a little clearer" beat), revealing it fully at the end. The backend stores the finished artwork once and serves no reveal states; progressive disclosure is a front-end effect driven by the reader's completion count.

data_related_journey¶

data_journey_chapter¶

No taxonomy columns and no M2M join: a chapter is its piece, so its themes and primary theme are the piece's, read through at query time. Tagging the chapter as well would be the same answer entered twice, free to drift. The Journey carries the taxonomy for the course as a whole.

The closing reflection question is likewise the piece's own reflection_prompt — not duplicated here. Chapters have no independent publish state; they live and die with the Journey revision.

Featured lists — data_featured_journeys · data_featured_themes · data_featured_books¶

Same shape ×3:

data_home (singleton)¶

data_home_journey_pin¶

data_home_whats_new_pin¶

User data¶

All FKs to data_user are CASCADE (account deletion is soft-delete at the app layer; hard cleanup cascades).

data_user gains one column (the account model is owned by CSL-30/auth, listed here for completeness): preferred_theme_id FK → data_theme, nullable, on_delete=SET_NULL — the user's chosen active theme, null = fall back to Theme-of-the-Week. Read via GET /api/account/, set via PATCH /api/account/.

data_journey_progress¶

data_chapter_completion¶

There is no per-piece completion table: a chapter is one piece, so chapter completion is the only granularity. Position within a chapter's arc is presentation state, not a row per screen.

data_bookmark¶

data_reflection¶

Optimization notes (pre-implementation review)¶

• Home is per-section endpoints, not one aggregate query — hero + Further Reading random pools are ORDER BY random() over small tables at launch corpus size (fine now; swap to TABLESAMPLE past ~10k rows); every other section is a PK/list lookup. Splitting means each section caches independently (Cache-Control per endpoint) and no request waits on the slowest.
• What's New — the auto-fill query (filter(live=True, is_new_eligible=True).order_by( first_published_at desc) over Article + Writing) would benefit from a partial index on (is_new_eligible, live) + first_published_at DESC. Not yet created (migration 0009 adds is_new_eligible as a plain boolean) — deferred while the corpus is small; add the composite partial index when volume warrants.
• Journey landing + progress joins data_journey_chapter → completions per user: the unique (user, chapter) index doubles as the lookup index.
• XOR refs (chapters, pins, bookmarks, reflections) are nullable FKs + CHECK, chosen over GenericForeignKey so select_related/prefetch_related work and FK integrity is real.
• PROTECT on content FKs from curation (chapters, progress) — an editor cannot delete a piece that a Journey references; they must unlink it first. Deliberate friction.
• The display ordinal is computed, never stored. "Chapter N" is derived at read time over the journey's authored chapters — like read time, computed so it can't drift. sort_order is nullable and can tie, so it orders the list but never numbers it. A live journey cannot lose a chapter (the CMS blocks unpublishing a piece it uses), so the numbering is stable for a reader whose progress is saved against particular chapters.