pub const MIGRATION_025_CREATE: &str = "-- Migration 025: Create intent graph tables\n-- Maps the AgilePlus Intent Graph Ontology to SQLite schema\n\n-- ---------------------------------------------------------------------------\n-- intent_nodes: typed nodes in the intent graph\n-- ---------------------------------------------------------------------------\nCREATE TABLE IF NOT EXISTS intent_nodes (\n id TEXT PRIMARY KEY NOT NULL,\n node_type TEXT NOT NULL,\n dag_stage TEXT NOT NULL,\n title TEXT NOT NULL,\n description TEXT,\n status TEXT NOT NULL,\n tags TEXT DEFAULT \'[]\', -- JSON array of strings\n meta TEXT DEFAULT \'{}\' NOT NULL, -- JSON object: required {timestamp, source}\n properties TEXT DEFAULT \'{}\', -- JSON object: free-form key/value\n table_ref TEXT, -- foreign table name (e.g., \"features\")\n table_id TEXT, -- foreign row id within table_ref\n created_at TEXT NOT NULL, -- ISO 8601\n updated_at TEXT NOT NULL, -- ISO 8601\n\n -- referential integrity: table_ref + table_id must exist in target\n -- (enforced via application layer or deferred triggers if needed)\n\n CONSTRAINT valid_node_id CHECK (id REGEXP \'^[A-Z][a-z]+#[a-z0-9\\-]+$\'),\n CONSTRAINT valid_node_type CHECK (node_type IN (\n \'Intent\',\'Plan\',\'Feature\',\'Story\',\'Task\',\n \'Spec\',\'Commit\',\'Test\',\'PR\',\'Bug\',\'Artifact\'\n )),\n CONSTRAINT valid_dag_stage CHECK (dag_stage IN (\n \'intent\',\'plan\',\'feature\',\'story\',\'task\',\n \'spec\',\'commit\',\'test\',\'pr\',\'bug\',\'artifact\'\n )),\n CONSTRAINT valid_status CHECK (status IN (\n \'draft\',\'active\',\'completed\',\'deprecated\',\'rejected\',\n \'open\',\'in_progress\',\'blocked\',\'deferred\',\'cancelled\'\n ))\n);\n\n-- ---------------------------------------------------------------------------\n-- intent_edges: typed relationships between nodes\n-- ---------------------------------------------------------------------------\nCREATE TABLE IF NOT EXISTS intent_edges (\n id TEXT PRIMARY KEY NOT NULL, -- UUID\n source TEXT NOT NULL,\n target TEXT NOT NULL,\n relationship_type TEXT NOT NULL,\n canonical_map TEXT DEFAULT \'{}\', -- JSON object: {link_type, direction}\n meta TEXT DEFAULT \'{}\' NOT NULL, -- JSON object: required {timestamp, source}\n properties TEXT DEFAULT \'{}\', -- JSON object: free-form key/value\n created_at TEXT NOT NULL, -- ISO 8601\n\n -- soft foreign keys to intent_nodes\n CONSTRAINT fk_edge_source FOREIGN KEY (source) REFERENCES intent_nodes(id) ON DELETE CASCADE ON UPDATE CASCADE,\n CONSTRAINT fk_edge_target FOREIGN KEY (target) REFERENCES intent_nodes(id) ON DELETE CASCADE ON UPDATE CASCADE,\n\n CONSTRAINT valid_relationship CHECK (relationship_type IN (\n \'implements\',\'tests\',\'covers\',\'traces-to\',\n \'derives-from\',\'resolves\',\'blocks\',\'depends-on\'\n )),\n CONSTRAINT no_self_loop CHECK (source != target)\n);\n\n-- ---------------------------------------------------------------------------\n-- intent_graph_metadata: singleton-ish record for graph versioning\n-- ---------------------------------------------------------------------------\nCREATE TABLE IF NOT EXISTS intent_graph_metadata (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n version TEXT NOT NULL,\n schema_uri TEXT NOT NULL,\n created_at TEXT NOT NULL,\n updated_at TEXT NOT NULL,\n node_count INTEGER NOT NULL DEFAULT 0,\n edge_count INTEGER NOT NULL DEFAULT 0,\n dag_valid INTEGER NOT NULL DEFAULT 0, -- BOOLEAN (0/1)\n source_system TEXT\n);\n\n-- ---------------------------------------------------------------------------\n-- Indexes for common lookup patterns\n-- ---------------------------------------------------------------------------\nCREATE INDEX IF NOT EXISTS idx_nodes_type ON intent_nodes(node_type);\nCREATE INDEX IF NOT EXISTS idx_nodes_stage ON intent_nodes(dag_stage);\nCREATE INDEX IF NOT EXISTS idx_nodes_status ON intent_nodes(status);\nCREATE INDEX IF NOT EXISTS idx_nodes_table_ref ON intent_nodes(table_ref, table_id);\nCREATE INDEX IF NOT EXISTS idx_nodes_created_at ON intent_nodes(created_at);\nCREATE INDEX IF NOT EXISTS idx_nodes_updated_at ON intent_nodes(updated_at);\n\nCREATE INDEX IF NOT EXISTS idx_edges_source ON intent_edges(source);\nCREATE INDEX IF NOT EXISTS idx_edges_target ON intent_edges(target);\nCREATE INDEX IF NOT EXISTS idx_edges_relationship ON intent_edges(relationship_type);\nCREATE INDEX IF NOT EXISTS idx_edges_created_at ON intent_edges(created_at);\n\nCREATE UNIQUE INDEX IF NOT EXISTS idx_edges_pair ON intent_edges(source, target, relationship_type);\n\n-- ---------------------------------------------------------------------------\n-- Triggers: maintain metadata counts on insert/delete\n-- ---------------------------------------------------------------------------\nCREATE TRIGGER IF NOT EXISTS tg_nodes_insert\nAFTER INSERT ON intent_nodes\nBEGIN\n UPDATE intent_graph_metadata\n SET node_count = node_count + 1,\n updated_at = datetime(\'now\')\n WHERE id = (SELECT id FROM intent_graph_metadata ORDER BY id DESC LIMIT 1);\nEND;\n\nCREATE TRIGGER IF NOT EXISTS tg_nodes_delete\nAFTER DELETE ON intent_nodes\nBEGIN\n UPDATE intent_graph_metadata\n SET node_count = node_count - 1,\n updated_at = datetime(\'now\')\n WHERE id = (SELECT id FROM intent_graph_metadata ORDER BY id DESC LIMIT 1);\nEND;\n\nCREATE TRIGGER IF NOT EXISTS tg_edges_insert\nAFTER INSERT ON intent_edges\nBEGIN\n UPDATE intent_graph_metadata\n SET edge_count = edge_count + 1,\n updated_at = datetime(\'now\')\n WHERE id = (SELECT id FROM intent_graph_metadata ORDER BY id DESC LIMIT 1);\nEND;\n\nCREATE TRIGGER IF NOT EXISTS tg_edges_delete\nAFTER DELETE ON intent_edges\nBEGIN\n UPDATE intent_graph_metadata\n SET edge_count = edge_count - 1,\n updated_at = datetime(\'now\')\n WHERE id = (SELECT id FROM intent_graph_metadata ORDER BY id DESC LIMIT 1);\nEND;\n";