宜博 / lemongraph

"""SQLite schema for graphlemon's own structured storage.

U-001 scope: the 15 platform entities from docs/sdd/01-M-模型设计.md §1.0-1.14
(PH0-PH4). `deployment`/`deploy_health_signal` (§1.15/§1.16) are PH5-only and are
deliberately not created here -- see CLAUDE.md "PH5 is not in scope yet" and
development-plan.md U-034.

Interpretive conventions used below, where 01-M leaves the physical type implicit:
- Booleans are stored as INTEGER CHECK(x IN (0, 1)) -- sqlite3 has no native bool type.
- A `*_at` column that records "when this row was created" (or, for skill_iteration,
  when the row's one-shot event happened) is NOT NULL DEFAULT CURRENT_TIMESTAMP.
  A `*_at` column that records a later lifecycle event which may not have happened
  yet (started_at, completed_at, purged_at, ...) is nullable with no default -- the
  application sets it only when that event actually occurs.
- JSON-typed columns (graph_manifest, nodes, edges, payload, ...) are stored as TEXT;
  (de)serialization is an application-layer concern, not enforced by this schema.
- task.assigned_org_node is documented as "not null, fk" but there is no standalone
  org-node lookup table to reference (planner/coder/validator only exist as entries
  inside org_graph_definition.nodes, a JSON column) -- implemented as a CHECK enum
  instead, matching work_graph_run.org_node / skill.owner_org_node / agent_profile.org_node.
- bug_record.root_cause_category intentionally excludes 'core_engine' for now: that
  value is PH5-scoped (05 §5.8) and development-plan.md assigns adding it to U-034,
  not U-001 (confirmed with user, 2026-07-30).
- bug_record and skill have no created_at in 01-M (skill is Git-versioned, so git log
  covers "when"; bug_record has no such backing and this looks like a possible spec
  gap, but adding an undocumented column is out of scope for U-001 -- flagged, not fixed).
"""

import sqlite3

TABLE_NAMES = (
    "project",
    "org_graph_definition",
    "skill",
    "task",
    "work_graph_run",
    "work_node_execution",
    "artifact",
    "bug_record",
    "skill_iteration",
    "agent_profile",
    "budget_guardrail_config",
    "org_concurrency_config",
    "execution_event",
    "execution_log_file",
    "log_retention_config",
)

SCHEMA_SQL = """
-- 1.0 project
CREATE TABLE project (
    project_id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    target_folder_path TEXT NOT NULL UNIQUE,
    has_git_repo INTEGER NOT NULL CHECK (has_git_repo IN (0, 1)),
    graphlemon_subdir_initialized INTEGER NOT NULL DEFAULT 0
        CHECK (graphlemon_subdir_initialized IN (0, 1)),
    status TEXT NOT NULL CHECK (status IN ('active', 'archived')),
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 1.1 org_graph_definition
CREATE TABLE org_graph_definition (
    graph_id TEXT PRIMARY KEY,
    version TEXT NOT NULL,
    nodes TEXT NOT NULL,
    edges TEXT NOT NULL,
    file_path TEXT NOT NULL,
    status TEXT NOT NULL CHECK (status IN ('draft', 'active', 'deprecated'))
);

-- 1.7 skill (created before task/work_node_execution: work_node_execution.skill_ref
-- references it)
CREATE TABLE skill (
    skill_id TEXT PRIMARY KEY,
    owner_org_node TEXT NOT NULL CHECK (owner_org_node IN ('planner', 'coder', 'validator')),
    file_path TEXT NOT NULL,
    current_git_commit_sha TEXT NOT NULL,
    source TEXT NOT NULL CHECK (source IN ('migrated_from_hermes', 'new', 'auto_iterated')),
    migrated_from_profile TEXT CHECK (migrated_from_profile IN ('code', 'planning', 'qa')),
    status TEXT NOT NULL CHECK (status IN ('active', 'archived'))
);

-- 1.2 task
CREATE TABLE task (
    task_id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL REFERENCES project(project_id),
    goal_text TEXT NOT NULL,
    task_type TEXT NOT NULL CHECK (task_type IN ('goal', 'bug_fix')),
    origin_ref TEXT,
    created_via TEXT NOT NULL DEFAULT 'api' CHECK (created_via IN ('api', 'human_console')),
    status TEXT NOT NULL CHECK (status IN (
        'queued', 'planning_brd_prd_sdd', 'planning_failed',
        'coding', 'coding_failed', 'validating',
        'routed_fast_lane', 'routed_full_replan',
        'blocked_pending_human', 'done'
    )),
    route TEXT CHECK (route IN ('fast_lane', 'full_replan')),
    loop_round INTEGER NOT NULL DEFAULT 0,
    blocked_reason TEXT,
    assigned_org_node TEXT NOT NULL CHECK (assigned_org_node IN ('planner', 'coder', 'validator')),
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_task_status ON task(status);
CREATE INDEX idx_task_assigned_org_node ON task(assigned_org_node);
CREATE INDEX idx_task_project_id ON task(project_id);

-- 1.3 work_graph_run
CREATE TABLE work_graph_run (
    run_id TEXT PRIMARY KEY,
    task_id TEXT NOT NULL REFERENCES task(task_id),
    org_node TEXT NOT NULL CHECK (org_node IN ('planner', 'coder', 'validator')),
    graph_manifest TEXT NOT NULL,
    orchestration_script_ref TEXT,
    status TEXT NOT NULL CHECK (status IN (
        'planning', 'running', 'merging', 'verifying',
        'reflecting', 'blocked', 'done', 'failed'
    )),
    started_at TEXT,
    completed_at TEXT,
    result_artifact_refs TEXT
);

-- only one active (non-terminal) run per (task_id, org_node) at a time
CREATE UNIQUE INDEX ux_work_graph_run_active ON work_graph_run(task_id, org_node)
    WHERE status IN ('planning', 'running', 'merging', 'verifying');

-- 1.4 work_node_execution
CREATE TABLE work_node_execution (
    node_exec_id TEXT PRIMARY KEY,
    run_id TEXT NOT NULL REFERENCES work_graph_run(run_id),
    node_id TEXT NOT NULL,
    subagent_type TEXT NOT NULL,
    skill_ref TEXT NOT NULL REFERENCES skill(skill_id),
    input_schema_ref TEXT NOT NULL,
    output_schema_ref TEXT NOT NULL,
    input_payload TEXT NOT NULL,
    output_payload TEXT,
    model_tier TEXT NOT NULL DEFAULT 'standard' CHECK (model_tier IN ('cheap', 'standard', 'premium')),
    status TEXT NOT NULL CHECK (status IN (
        'pending', 'running', 'reflecting', 'retrying',
        'waiting_for_quota', 'blocked', 'done', 'failed'
    )),
    retry_count INTEGER NOT NULL DEFAULT 0,
    reflection_notes TEXT,
    git_worktree_ref TEXT,
    quota_wait_started_at TEXT,
    quota_limit_type TEXT CHECK (quota_limit_type IN ('session', 'weekly', 'opus'))
);

-- 1.5 artifact
CREATE TABLE artifact (
    artifact_id TEXT PRIMARY KEY,
    artifact_type TEXT NOT NULL CHECK (artifact_type IN (
        'brd', 'prd', 'sdd', 'benchmark', 'bug_record', 'code_diff', 'test_report'
    )),
    task_id TEXT NOT NULL REFERENCES task(task_id),
    produced_by_run_id TEXT NOT NULL REFERENCES work_graph_run(run_id),
    file_path TEXT NOT NULL,
    git_commit_sha TEXT NOT NULL,
    depends_on_artifact_ids TEXT,
    validation_status TEXT NOT NULL DEFAULT 'pending' CHECK (validation_status IN ('pending', 'passed', 'failed')),
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNIQUE INDEX ux_artifact_task_type_commit ON artifact(task_id, artifact_type, git_commit_sha);

-- 1.6 bug_record (skill_iteration_suggestion_id forward-references skill_iteration,
-- created further below -- SQLite does not validate FK target existence at CREATE
-- TABLE time, only at DML time, so table creation order here is fine)
CREATE TABLE bug_record (
    bug_id TEXT PRIMARY KEY,
    task_id TEXT REFERENCES task(task_id),
    source TEXT NOT NULL CHECK (source IN ('validator', 'human')),
    reported_by TEXT,
    description TEXT NOT NULL,
    repro_steps TEXT,
    root_cause_category TEXT CHECK (root_cause_category IN ('planning', 'coder', 'skill_defect')),
    root_cause_detail TEXT,
    related_artifact_ids TEXT,
    suggested_route TEXT CHECK (suggested_route IN ('fast_lane', 'full_replan')),
    skill_iteration_suggestion_id TEXT REFERENCES skill_iteration(iteration_id),
    status TEXT NOT NULL CHECK (status IN ('open', 'triaging', 'routed', 'resolved'))
);

-- 1.8 skill_iteration
CREATE TABLE skill_iteration (
    iteration_id TEXT PRIMARY KEY,
    skill_id TEXT NOT NULL REFERENCES skill(skill_id),
    triggered_by_bug_id TEXT NOT NULL REFERENCES bug_record(bug_id),
    before_git_commit_sha TEXT NOT NULL,
    after_git_commit_sha TEXT NOT NULL,
    change_summary TEXT NOT NULL,
    applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    rollback_of_iteration_id TEXT REFERENCES skill_iteration(iteration_id)
);

-- 1.9 agent_profile
CREATE TABLE agent_profile (
    org_node TEXT PRIMARY KEY CHECK (org_node IN ('planner', 'coder', 'validator')),
    soul_file_path TEXT NOT NULL,
    agents_file_path TEXT,
    migrated_from_profile TEXT NOT NULL CHECK (migrated_from_profile IN ('planning', 'code', 'qa')),
    current_git_commit_sha TEXT NOT NULL
);

-- 1.10 budget_guardrail_config
CREATE TABLE budget_guardrail_config (
    config_id TEXT PRIMARY KEY DEFAULT 'default',
    scope TEXT NOT NULL CHECK (scope IN ('global', 'per_task', 'per_day')),
    limit_type TEXT NOT NULL CHECK (limit_type IN ('token_count', 'call_count')),
    limit_value INTEGER NOT NULL,
    current_usage INTEGER NOT NULL DEFAULT 0,
    on_exceed_action TEXT NOT NULL DEFAULT 'pause_and_notify',
    updated_by TEXT NOT NULL,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 1.11 org_concurrency_config
CREATE TABLE org_concurrency_config (
    config_id TEXT PRIMARY KEY DEFAULT 'default',
    max_concurrent_goals INTEGER NOT NULL DEFAULT 1,
    updated_by TEXT NOT NULL,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 1.12 execution_event
CREATE TABLE execution_event (
    event_id TEXT PRIMARY KEY,
    project_id TEXT NOT NULL REFERENCES project(project_id),
    task_id TEXT REFERENCES task(task_id),
    run_id TEXT REFERENCES work_graph_run(run_id),
    node_exec_id TEXT REFERENCES work_node_execution(node_exec_id),
    event_type TEXT NOT NULL CHECK (event_type IN (
        'task_status_changed', 'node_status_changed', 'artifact_created',
        'bug_recorded', 'loop_routed', 'budget_updated', 'skill_iterated',
        'log_appended', 'quota_wait_started'
    )),
    payload TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_execution_event_project_created ON execution_event(project_id, created_at);
CREATE INDEX idx_execution_event_task_created ON execution_event(task_id, created_at);

-- 1.13 execution_log_file
CREATE TABLE execution_log_file (
    log_file_id TEXT PRIMARY KEY,
    node_exec_id TEXT NOT NULL UNIQUE REFERENCES work_node_execution(node_exec_id),
    project_id TEXT NOT NULL REFERENCES project(project_id),
    file_path TEXT NOT NULL,
    format TEXT NOT NULL CHECK (format IN ('jsonl', 'raw_text')),
    size_bytes INTEGER NOT NULL DEFAULT 0,
    status TEXT NOT NULL CHECK (status IN ('active', 'closed', 'purged')),
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    purged_at TEXT
);

CREATE INDEX idx_execution_log_file_project ON execution_log_file(project_id);

-- 1.14 log_retention_config
CREATE TABLE log_retention_config (
    config_id TEXT PRIMARY KEY DEFAULT 'default',
    retention_days INTEGER NOT NULL DEFAULT 14,
    max_total_size_mb INTEGER,
    updated_by TEXT NOT NULL,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
"""


def connect(db_path=":memory:"):
    """Open a sqlite3 connection with foreign-key enforcement turned on.

    SQLite disables FK enforcement by default per-connection; every caller needs
    this pragma, so it lives here rather than being repeated at every call site.
    """
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


def create_schema(conn):
    """Create all 15 U-001 tables (+ indexes) on an open connection."""
    conn.executescript(SCHEMA_SQL)
    conn.commit()