ADR-0011: Shows Data Model — Hybrid SQLite + Filesystem¶

Status: Accepted Date: 2026-05-20 Extends: ADR-0009 (SQLite state layer), ADR-0010 (plugin-aware Studio)

Related update: The shows/plays schema defined here stores structural and operational state. ADR-0033 adds NormalizedState columns (health, delivery, reason_codes) additively to these tables. The lifecycle vocabularies for Shows and Plays in ADR-0033 §"Per-entity lifecycle vocabularies" are derived from this ADR's status enums. This ADR remains authoritative for the table structure and cardinality; ADR-0033 governs the meaning of state values written to those tables.

Context¶

Shows (multi-play DAGs orchestrated by the show skill) currently exist only as filesystem trees under ~/khive-work/shows/{topic}/. Each play writes _meta.json, _verdict.json, _intent.md, _prompt.md, and agent artifacts. Studio reads these files on every request.

This creates three problems:

1. No cross-reference to sessions. Each play IS a li play invocation that creates a session in SQLite (via ADR-0009 hooks). But nothing links the show DAG to those sessions. You can't drill from show → play → session → messages.

2. No query performance. Listing shows requires readdir + parsing N JSON files per show. With 2 shows × 12 plays average, this is ~24 file reads per list request. Scales poorly.

3. No structural queries. "Which plays are blocked?" "What's the critical path?" "Show me all escalated plays across all shows" — impossible without loading everything into memory.

Meanwhile, the long-form content (_show.md, _intent.md, _prompt.md) is authored markdown that belongs on disk — it's edited by the director mid-show, versioned by git, and doesn't benefit from being in a database column.

Decision¶

Add two tables to ~/.lionagi/state.db for show-level orchestration state. Filesystem remains source of truth for markdown content; SQLite stores structural/status data and the critical session_id foreign key.

Schema¶

-- ── Shows ────────────────────────────────────────────────────────────────
-- One row per show (multi-play DAG).

CREATE TABLE IF NOT EXISTS shows (
  id                  TEXT    PRIMARY KEY,
  topic               TEXT    NOT NULL UNIQUE,
  goal                TEXT,                       -- one-line summary from _show.md
  repo                TEXT,                       -- absolute path to repo
  base_branch         TEXT,                       -- e.g. 'main'
  integration_branch  TEXT,                       -- e.g. 'show/topic/integration'
  status              TEXT    NOT NULL DEFAULT 'active',  -- active|completed|aborted|imported
  status_source       TEXT,                       -- sqlite|filesystem (confidence indicator)
  show_dir            TEXT    NOT NULL,           -- absolute path to show directory
  created_at          REAL    NOT NULL,
  updated_at          REAL    NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_shows_topic ON shows(topic);
CREATE INDEX IF NOT EXISTS idx_shows_status ON shows(status);
CREATE INDEX IF NOT EXISTS idx_shows_updated ON shows(updated_at DESC);

-- ── Plays ────────────────────────────────────────────────────────────────
-- One row per play within a show. Links to sessions table via session_id.

CREATE TABLE IF NOT EXISTS plays (
  id              TEXT    PRIMARY KEY,
  show_id         TEXT    NOT NULL REFERENCES shows(id) ON DELETE CASCADE,
  name            TEXT    NOT NULL,
  playbook        TEXT,                           -- which playbook was used
  effort          TEXT,                           -- low|medium|high|xhigh
  status          TEXT    NOT NULL DEFAULT 'pending',
                  -- pending|prepared|running|running_complete|
                  -- gated|gate_failed|redoing|merged|escalated|
                  -- blocked|aborted_after_finish
  attempt         INTEGER NOT NULL DEFAULT 1,
  session_id      TEXT    REFERENCES sessions(id), -- THE KEY LINK
  started_at      REAL,
  ended_at        REAL,
  exit_code       INTEGER,
  worktree        TEXT,                           -- absolute path
  branch          TEXT,                           -- git branch name
  merge_sha       TEXT,
  merged_at       REAL,
  gate_passed     INTEGER,                        -- 0|1|NULL (not yet gated)
  gate_feedback   TEXT,                           -- from _verdict.json
  depends_on      JSON    DEFAULT '[]',           -- array of play names
  sort_order      INTEGER NOT NULL DEFAULT 0,
  created_at      REAL    NOT NULL,
  updated_at      REAL    NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_plays_show ON plays(show_id);
CREATE INDEX IF NOT EXISTS idx_plays_status ON plays(status);
CREATE INDEX IF NOT EXISTS idx_plays_session ON plays(session_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_plays_show_name ON plays(show_id, name);

What lives where¶

Write path¶

The show skill's existing Steps already write _meta.json at lifecycle transitions (Step 0, 3, 4, 5). Each write point also upserts the SQLite row:

session_id resolution¶

When a play fires via li play ... --team-mode show_topic_play, the session created by li play gets a deterministic name: show_{topic}_{play}. After the play subprocess exits, the director queries:

SELECT id FROM sessions WHERE name = ? ORDER BY created_at DESC LIMIT 1

This links the play row to the session. From there, the full message tree is accessible: plays.session_id → sessions → branches → progressions → messages.

Read path (Studio API)¶

GET /api/shows/           → SELECT from shows table (fast, no filesystem)
GET /api/shows/{topic}    → shows row + plays rows + _show.md from disk
GET /api/shows/{topic}/plays/{name}
                          → play row + _intent.md + _verdict.json from disk
                          → play.session_id for drill-down link to /runs/{session_id}

Response schema — GET /api/shows/{topic}¶

The endpoint returns a JSON object with the following canonical fields:

{
  "topic":         "<string>  — show directory name (URL-safe)",
  "path":          "<string>  — absolute filesystem path to show directory",
  "show_md":       "<string | null>  — raw content of _show.md; null if file absent",
  "goal":          "<string | null>  — one-line summary from shows.goal column, or
                                       extracted from _show.md first paragraph if not in DB",
  "status":        "<string>  — shows.status value (active|completed|aborted|imported)
                                or 'unknown' for shows not yet imported to SQLite",
  "status_source": "<string>  — 'sqlite' when row found in DB; 'filesystem' when derived
                                from filesystem only",
  "plays":         "<array>   — play objects (see play shape below)"
}

Each element in plays has the following shape (SQLite-backed path):

{
  "name":       "<string>",
  "meta": {
    "worktree":    "<string | null>",
    "branch":      "<string | null>",
    "attempt":     "<integer>",
    "started_at":  "<real | null>  — unix timestamp",
    "ended_at":    "<real | null>  — unix timestamp",
    "exit_code":   "<integer | null>",
    "merged_at":   "<real | null>",
    "merge_sha":   "<string | null>",
    "status":      "<string>  — ADR-0011 play status vocabulary"
  },
  "verdict": {
    "gate_passed": "<boolean | null>",
    "feedback":    "<string | null>"
  },
  "session_id":   "<string | null>  — FK to sessions.id",
  "session_name": "<string | null>",
  "intent":       "<string | null>  — raw content of _intent.md",
  "updated_at":   "<real>  — unix timestamp",
  "depends_on":   "<array of string>  — play names this play depends on"
}

For shows not imported to SQLite (filesystem-only fallback), the play shape is minimal: {name, meta: <raw _meta.json object>, verdict: <_verdict.json object | null>, updated_at}.

Pending migration — status_source column¶

status_source is returned by the API ("sqlite" or "filesystem") but is not yet a column in schema.sql — it is derived in code. The shows table DDL in this ADR includes the column (see Schema section above), but the actual schema.sql and _MIGRATION_COLUMNS in db.py do not yet carry it. An ALTER TABLE shows ADD COLUMN status_source TEXT migration is required before status_source can be persisted or queried from the database. Until that migration lands, the field is always computed at response time from the code path taken (DB row found vs. filesystem fallback).

Migration: li state import-shows¶

One-time import of existing filesystem shows into SQLite:

for show_dir in SHOWS_ROOT.iterdir():
    # Parse _show.md for goal (first paragraph under ## Goal)
    # Insert shows row
    for play_dir in show_dir subdirs:
        # Parse _meta.json → plays row
        # Parse _verdict.json → gate_passed, gate_feedback
        # Resolve session_id by name lookup
        # Insert plays row

Play detail accordion and session drill-down¶

The plays.session_id FK enables the most important UI connection. The shows detail page surfaces this via inline accordion (not a drawer — a drawer competes with the DAG for horizontal space):

1. Play row click expands the row vertically to show:
2. Session link: Open Session → routes to /runs/{session_id} (first element)
3. Intent (_intent.md)
4. Duration, exit code, attempt count
5. Gate verdict and feedback

6. Raw data (meta JSON, verdict JSON) collapsed by default

7. Play DAG node click scrolls to and expands the corresponding play row.

8. Reverse lookup: run detail page queries SELECT show_id, name FROM plays WHERE session_id = ? to show a "Source: Show {topic} / Play {name}" backlink in the Overview section.

Note: the UI route is /runs/{session_id} (user-facing "Runs" label per ADR-0012), not /sessions/{session_id}.

Play status display: structured multi-badge State cell¶

The plays table uses a single State column with a primary lifecycle pill plus optional secondary badges for gate and integration:

[completed] [passed] [merged]      ← full provenance
[completed]                        ← no gate, no merge
[awaiting gate]                    ← running_complete
[pending]                          ← no activity yet
[failed] [gate failed]             ← gate rejection

This avoids three separate columns (too wide for the dense table) and avoids a single compound pill (hard to scan because dimensions aren't visually separable).

Badge colors:

• Lifecycle: pending (amber), running (blue), awaiting_gate (amber), completed (green), failed (red), aborted (gray)
• Gate: passed (green outline), failed (red outline), skipped (gray outline)
• Integration: merged (green outline), local (gray outline)

Raw status appears in expanded accordion details and tooltips.

The raw plays.status values (running_complete, gated, gate_failed, redoing, escalated, blocked, aborted_after_finish) map to normalized lifecycle + gate + review badges per ADR-0012's status vocabulary.

PlayDag placement: compact strip above plays table¶

The PlayDag renders as a compact dependency graph strip above the plays table, not a competing primary view:

Dependency graph                                        [Hide graph]
┌────────────────────────────────────────────────────────────────────┐
│  compact PlayDag, fitView, 150–170px tall                          │
│  node hover → highlight table row                                  │
│  row hover → highlight graph node                                  │
│  node click → scroll to and expand play row                        │
└────────────────────────────────────────────────────────────────────┘

Height scales with play count:

• ≤4 plays: 112px
• 5–20 plays: 160px
• 21+ plays: 220px with scroll/pan

The graph is visible by default (not behind a toggle) because it is the only visual representation of dependency structure. Hiding it collapses shows into a plain status table and loses the orchestration identity of the page.

Show status provenance (added post-review)¶

Show status includes provenance to address trust concerns:

• active — at least one play running or pending
• completed — all plays merged and final gate passed (or inferred from filesystem)
• aborted — _ABORT file exists

Note: The shows DDL reserves imported as a valid status value but the current import_shows implementation (services/shows.py) resolves to aborted, completed, or active based on filesystem evidence — it does not write imported. If a future import path cannot determine status from filesystem signals, imported would be the appropriate fallback. Until then, callers should not expect to see imported in shows.status.

The API includes status_source: "sqlite" | "filesystem" to indicate confidence level.

Consequences¶

Positive

• Show → play → session → message drill-down via foreign keys.
• Fast list/filter queries without filesystem scanning.
• Structural queries: blocked plays, critical path, cross-show stats.
• Filesystem content unchanged — git workflow, director editing, resume protocol all work identically.
• Play inline accordion provides full context without leaving the shows page.
• Status normalization eliminates contradictory displays across pages.

Negative

• Dual write: every _meta.json update must also update SQLite. If the show skill crashes between writes, they can drift. Mitigation: the li state import-shows command can re-sync from filesystem at any time.
• Schema migration: existing state.db instances need ALTER TABLE or recreation. Use schema_meta version bump (1 → 2) with migration.
• Status mapping adds a translation layer between raw play statuses and normalized display vocabulary.

Alternatives Considered¶