Schema: unified journeys tables + retire concepts/people_journeys

[CraigBuckmaster]

Parent epic: #1379
Track: Code
Phase: 1 — Foundation

Dependency protocol

Blocked by: None (first issue in Phase 1 foundation)
Blocks: #2 (build pipeline), #3 (schema validator)

IMPORTANT: When this issue is complete, open a PR. Wait for merge to master before starting dependent issues. If you are Claude Code, do not proceed to dependent tasks in the same run.

Goal

Create the unified journeys, journey_stops, and journey_tags tables in the SQLite schema. Also retire the legacy concepts and people_journeys tables.

What to build

1. Add new schema to _tools/build_sqlite_loaders.py

Find the schema definition section (where tables like chapters, sections, scholars are defined) and add:

# Unified journeys table — covers person, concept, and thematic journeys
cur.execute('''
    CREATE TABLE IF NOT EXISTS journeys (
        id TEXT PRIMARY KEY,
        journey_type TEXT NOT NULL CHECK (journey_type IN ('person', 'concept', 'thematic')),
        title TEXT NOT NULL,
        subtitle TEXT,
        description TEXT NOT NULL,
        lens_id TEXT,
        depth TEXT CHECK (depth IN ('short', 'medium', 'long')),
        sort_order INTEGER DEFAULT 0,
        person_id TEXT,
        concept_id TEXT,
        era TEXT,
        tags TEXT,
        hero_image_url TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    )
''')
cur.execute('CREATE INDEX IF NOT EXISTS idx_journeys_type ON journeys(journey_type)')
cur.execute('CREATE INDEX IF NOT EXISTS idx_journeys_lens ON journeys(lens_id)')

cur.execute('''
    CREATE TABLE IF NOT EXISTS journey_stops (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        journey_id TEXT NOT NULL REFERENCES journeys(id) ON DELETE CASCADE,
        stop_order INTEGER NOT NULL,
        stop_type TEXT NOT NULL CHECK (stop_type IN ('regular', 'linked_journey')),
        label TEXT,
        ref TEXT,
        book_id TEXT,
        chapter_num INTEGER,
        verse_start INTEGER,
        verse_end INTEGER,
        development TEXT,
        what_changes TEXT,
        linked_journey_id TEXT,
        linked_journey_intro TEXT,
        bridge_to_next TEXT,
        UNIQUE (journey_id, stop_order)
    )
''')
cur.execute('CREATE INDEX IF NOT EXISTS idx_stops_journey ON journey_stops(journey_id)')

cur.execute('''
    CREATE TABLE IF NOT EXISTS journey_tags (
        journey_id TEXT NOT NULL REFERENCES journeys(id) ON DELETE CASCADE,
        tag_type TEXT NOT NULL CHECK (tag_type IN ('person', 'place', 'theme', 'word_study', 'prophecy_chain')),
        tag_id TEXT NOT NULL,
        PRIMARY KEY (journey_id, tag_type, tag_id)
    )
''')
cur.execute('CREATE INDEX IF NOT EXISTS idx_journey_tags_target ON journey_tags(tag_type, tag_id)')

2. Remove the legacy concepts and people_journeys table creation

Search _tools/build_sqlite_loaders.py for:

• CREATE TABLE IF NOT EXISTS concepts
• CREATE TABLE IF NOT EXISTS people_journeys

Delete these CREATE statements entirely. Audit issue #A has the full inventory of references.

Evaluate people_legacy_refs — if PersonDetailScreen still references it (check hooks usePersonJourney.ts), keep it for now; it's out of scope for this epic.

3. Create content/meta/journey-lenses.json

[
  { "id": "theological", "name": "Theological Development", "sort": 1 },
  { "id": "biographical", "name": "Biographical Arc", "sort": 2 },
  { "id": "narrative", "name": "Narrative Arc", "sort": 3 },
  { "id": "questions", "name": "Theological Questions", "sort": 4 },
  { "id": "comparative", "name": "Comparative Studies", "sort": 5 },
  { "id": "cultural", "name": "Cultural Threads", "sort": 6 },
  { "id": "reading_paths", "name": "Reading Paths & Intros", "sort": 7 },
  { "id": "christological", "name": "Christological", "sort": 8 },
  { "id": "liturgical", "name": "Liturgical & Devotional", "sort": 9 },
  { "id": "hard_sayings", "name": "Hard Sayings & Apologetics", "sort": 10 },
  { "id": "geographic", "name": "Geographic & Spatial", "sort": 11 },
  { "id": "historical", "name": "Historical Context", "sort": 12 },
  { "id": "application", "name": "Life Application", "sort": 13 },
  { "id": "ane", "name": "Comparative Religion / ANE", "sort": 14 }
]

4. Create content/meta/journeys/ directory structure

Create empty subdirectories:

• content/meta/journeys/thematic/
• content/meta/journeys/concept/
• content/meta/journeys/person/

Add a .gitkeep in each so git tracks them.

Acceptance criteria

• New journeys, journey_stops, journey_tags tables defined in _tools/build_sqlite_loaders.py
• Indexes created on all three tables
• Legacy concepts and people_journeys CREATE statements removed
• content/meta/journey-lenses.json created with all 14 lenses
• content/meta/journeys/{thematic,concept,person}/ directories exist
• python _tools/build_sqlite.py runs without errors (will build empty journey tables; that's expected until Merge pull request #1 from CraigBuckmaster/codex/review-recent-master… #2 is done)
• Feature branch created, PR opened
• Wait for PR merge before Merge pull request #1 from CraigBuckmaster/codex/review-recent-master… #2 or Merge pull request #2 from CraigBuckmaster/master #3 begins

Files to modify

• _tools/build_sqlite_loaders.py — add new tables, remove retired ones
• content/meta/journey-lenses.json — new file
• content/meta/journeys/{thematic,concept,person}/.gitkeep — new files

Workflow

git checkout -b feature/1379-schema-foundation
# make changes
python _tools/build_sqlite.py  # must succeed
git add -A
git commit -m "Schema: add unified journeys tables, retire concepts and people_journeys (#1379)"
git push
# open PR to master, wait for merge