ten31-database/backend/migrations/0002_phase1_architect.sql

-- Phase 1 — Workstream A/E: the Architect's thesis substrate.
--
-- ADDITIVE AND REVERSIBLE ONLY (guardrail #3). Reversal: 0002_phase1_architect.down.sql.
-- Applied once by backend/core_migrations.py (schema_migrations ledger).
--
-- Models the "living messaging source of truth" as: multiple THESIS LINES (a core
-- spine + per-segment narratives), each a tree of typed NODES with full revision
-- history, frozen into immutable VERSIONS that a human signs off to make canonical.
-- Dual approval + collaborative text feedback live in thesis_reviews. Segments are
-- versioned defs that tie to the Phase-0 canonical_entities.segment pointer.

-- ============================================================================
-- thesis_lines — each distinct narrative line. is_core=1 is the shared spine;
-- others are segment-specific (Grant: different segments may carry different,
-- related thesis lines). Full-length ids (not the 8-char CRM ids).
-- ============================================================================
CREATE TABLE IF NOT EXISTS thesis_lines (
    id           TEXT PRIMARY KEY,        -- 'thl_' + uuid4 hex
    line_key     TEXT NOT NULL UNIQUE,    -- slug: 'core' | 'btc_native_hnwi' | ...
    name         TEXT NOT NULL,
    segment_key  TEXT,                    -- NULL for the core spine; else the segment this line serves
    is_core      INTEGER NOT NULL DEFAULT 0,
    description  TEXT,
    status       TEXT NOT NULL DEFAULT 'active',
    created_at   TEXT DEFAULT (datetime('now')),
    updated_at   TEXT DEFAULT (datetime('now')),
    deleted_at   TEXT
);

-- ============================================================================
-- thesis_nodes — typed node tree per line (the unit of iteration).
-- ============================================================================
CREATE TABLE IF NOT EXISTS thesis_nodes (
    id            TEXT PRIMARY KEY,       -- 'thn_' + uuid4 hex
    line_id       TEXT NOT NULL REFERENCES thesis_lines(id) ON DELETE CASCADE,
    parent_id     TEXT,                   -- tree edge; NULL for thesis_root
    node_type     TEXT NOT NULL,          -- thesis_root|throughline|section|claim|proof_point|objection|rebuttal|segment_cut
    ord           REAL NOT NULL DEFAULT 0,-- REAL so insert-between never renumbers siblings
    title         TEXT,
    body          TEXT,
    status        TEXT NOT NULL DEFAULT 'draft',   -- draft|candidate|approved|retired
    variant_group TEXT,                   -- nodes sharing this are competing phrasings of one idea (A/B)
    meta          TEXT,                   -- JSON: tone tags, confidence, evidence_refs -> canonical_entities/source ids
    created_at    TEXT DEFAULT (datetime('now')),
    updated_at    TEXT DEFAULT (datetime('now')),
    deleted_at    TEXT
);
CREATE INDEX IF NOT EXISTS idx_thesis_nodes_line   ON thesis_nodes(line_id);
CREATE INDEX IF NOT EXISTS idx_thesis_nodes_parent ON thesis_nodes(parent_id);
CREATE INDEX IF NOT EXISTS idx_thesis_nodes_variant ON thesis_nodes(variant_group);

-- ============================================================================
-- thesis_node_revisions — append-only per-node history (provenance + undo).
-- ============================================================================
CREATE TABLE IF NOT EXISTS thesis_node_revisions (
    id                TEXT PRIMARY KEY,
    node_id           TEXT NOT NULL,
    line_id           TEXT,
    rev_no            INTEGER NOT NULL,
    node_type         TEXT, title TEXT, body TEXT, status TEXT, ord REAL, variant_group TEXT, meta TEXT,
    change_summary    TEXT,
    change_reason     TEXT,               -- WHY the edit
    actor_type        TEXT,               -- human | agent
    actor_id          TEXT,               -- users.id or 'architect'
    claude_session_id TEXT,               -- ties an agent edit back to its reasoning session
    created_at        TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_thesis_revs_node ON thesis_node_revisions(node_id);

-- ============================================================================
-- thesis_versions — immutable named snapshots per line; ONE canonical per line.
-- body_json freezes the published artifact (the Architect->Scribe contract).
-- ============================================================================
CREATE TABLE IF NOT EXISTS thesis_versions (
    id                TEXT PRIMARY KEY,   -- 'thv_' + uuid4 hex
    line_id           TEXT NOT NULL REFERENCES thesis_lines(id) ON DELETE CASCADE,
    version_no        INTEGER NOT NULL,
    body_json         TEXT NOT NULL,      -- {throughline,pillars,claims,proof_points,segment_angles,voice,guardrails}
    status            TEXT NOT NULL DEFAULT 'draft',   -- draft|in_review|canonical|superseded
    rationale         TEXT,
    parent_version_id TEXT,
    created_by        TEXT,               -- users.id or 'architect'
    created_at        TEXT DEFAULT (datetime('now')),
    approved_at       TEXT,
    superseded_by     TEXT
);
CREATE INDEX IF NOT EXISTS idx_thesis_versions_line ON thesis_versions(line_id, version_no);
-- Hard invariant: at most one canonical version per line.
CREATE UNIQUE INDEX IF NOT EXISTS idx_thesis_one_canonical ON thesis_versions(line_id) WHERE status = 'canonical';

-- ============================================================================
-- thesis_reviews — dual approval + collaborative text feedback. Both partners
-- can comment/approve; the Architect ingests feedback to iterate. Promotion to
-- canonical happens when distinct 'approve' reviews meet the required count
-- (app_settings 'thesis_required_approvals', default 1) via the human route.
-- ============================================================================
CREATE TABLE IF NOT EXISTS thesis_reviews (
    id               TEXT PRIMARY KEY,
    version_id       TEXT NOT NULL REFERENCES thesis_versions(id) ON DELETE CASCADE,
    reviewer_user_id TEXT NOT NULL,       -- users.id (the human partner)
    decision         TEXT NOT NULL,       -- approve | request_changes | comment
    feedback         TEXT,                -- free-text the Architect reads to iterate
    target_node_id   TEXT,               -- optional: feedback scoped to one node
    created_at       TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_thesis_reviews_version ON thesis_reviews(version_id);

-- ============================================================================
-- segments — versioned LP segment definitions; one active per segment_key.
-- canonical_entities.segment (Phase 0) stores the segment_key pointer.
-- ============================================================================
CREATE TABLE IF NOT EXISTS segments (
    id            TEXT PRIMARY KEY,
    segment_key   TEXT NOT NULL,          -- slug
    name          TEXT NOT NULL,
    definition    TEXT,
    needs_to_hear TEXT,
    avoid         TEXT,
    version_no    INTEGER NOT NULL DEFAULT 1,
    status        TEXT NOT NULL DEFAULT 'active',   -- active | retired
    created_at    TEXT DEFAULT (datetime('now')),
    updated_at    TEXT DEFAULT (datetime('now'))
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_segments_one_active ON segments(segment_key) WHERE status = 'active';