schema.sql

-- ============================================================
-- Thought Engine Node — D1 Schema
-- Contract: C-THOUGHT-NODE-001 (Phase 1 Sovereign Substrate)
-- Vault: thought-vault (Cloudflare D1)
-- ============================================================

-- Sessions: The top-level reasoning container
CREATE TABLE IF NOT EXISTS sessions (
    session_id      TEXT PRIMARY KEY,
    title           TEXT,
    created_by      TEXT NOT NULL DEFAULT 'system',
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at      TEXT NOT NULL DEFAULT (datetime('now')),
    status          TEXT NOT NULL DEFAULT 'active'
                    CHECK (status IN ('active','archived','merged')),
    root_thought_id TEXT,
    active_thought_id TEXT,
    metadata        TEXT DEFAULT '{}'
);

-- Thought Units: Individual nodes in the reasoning graph
CREATE TABLE IF NOT EXISTS thought_units (
    thought_id      TEXT PRIMARY KEY,
    session_id      TEXT NOT NULL,
    content         TEXT NOT NULL,
    thought_class   TEXT NOT NULL DEFAULT 'observation'
                    CHECK (thought_class IN (
                        'hypothesis','observation','validation',
                        'counter_argument','synthesis','decision',
                        'question','proposal'
                    )),
    origin_agent    TEXT NOT NULL DEFAULT 'system',
    status          TEXT NOT NULL DEFAULT 'open'
                    CHECK (status IN (
                        'open','proposed','under_review',
                        'accepted','rejected','merged','superseded'
                    )),
    confidence      REAL DEFAULT NULL,
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    metadata        TEXT DEFAULT '{}',
    FOREIGN KEY (session_id) REFERENCES sessions(session_id)
);

-- Thought Edges: Relationships between thought units (the graph)
CREATE TABLE IF NOT EXISTS thought_edges (
    edge_id         TEXT PRIMARY KEY,
    session_id      TEXT NOT NULL,
    source_id       TEXT NOT NULL,
    target_id       TEXT NOT NULL,
    relation        TEXT NOT NULL DEFAULT 'derives_from'
                    CHECK (relation IN (
                        'derives_from','supports','challenges',
                        'refines','forks_from','merges_into',
                        'bubbles_from','bubbles_into'
                    )),
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (session_id) REFERENCES sessions(session_id),
    FOREIGN KEY (source_id) REFERENCES thought_units(thought_id),
    FOREIGN KEY (target_id) REFERENCES thought_units(thought_id)
);

-- Thought Reviews: Git-for-Thought PR lifecycle
CREATE TABLE IF NOT EXISTS thought_reviews (
    review_id       TEXT PRIMARY KEY,
    session_id      TEXT NOT NULL,
    thought_id      TEXT NOT NULL,
    action          TEXT NOT NULL
                    CHECK (action IN (
                        'propose','accept','reject','supersede'
                    )),
    actor           TEXT NOT NULL DEFAULT 'system',
    reason          TEXT DEFAULT NULL,
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (session_id) REFERENCES sessions(session_id),
    FOREIGN KEY (thought_id) REFERENCES thought_units(thought_id)
);

-- Witness Events: Provenance / audit trail
CREATE TABLE IF NOT EXISTS witness_events (
    event_id        TEXT PRIMARY KEY,
    session_id      TEXT NOT NULL,
    thought_id      TEXT,
    event_type      TEXT NOT NULL
                    CHECK (event_type IN (
                        'viewed','annotated','reviewed',
                        'merged','promoted','forked','created'
                    )),
    actor           TEXT NOT NULL,
    detail          TEXT DEFAULT NULL,
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (session_id) REFERENCES sessions(session_id)
);

-- Version History: Snapshot archive for thought edits (CAP-I.4)
CREATE TABLE IF NOT EXISTS version_history (
    version_id      TEXT PRIMARY KEY,
    thought_id      TEXT NOT NULL,
    version_num     INTEGER NOT NULL,
    content         TEXT NOT NULL,
    status          TEXT NOT NULL,
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (thought_id) REFERENCES thought_units(thought_id)
);

-- Pattern Registry: Promoted reusable reasoning structures (Phase 2 prep)
CREATE TABLE IF NOT EXISTS pattern_registry (
    pattern_id      TEXT PRIMARY KEY,
    name            TEXT NOT NULL,
    description     TEXT,
    source_session  TEXT,
    structure       TEXT NOT NULL DEFAULT '{}',
    times_reused    INTEGER NOT NULL DEFAULT 0,
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (source_session) REFERENCES sessions(session_id)
);