gid_core/storage/
schema.rs

1/// DDL for the gid-core SQLite storage schema.
2///
3/// Contains CREATE TABLE statements for all tables, the FTS5 virtual table,
4/// content-sync triggers, and indexes.
5///
6/// Design reference: design-storage.md §2, §5, §6
7pub const SCHEMA_SQL: &str = r#"
8-- ═══════════════════════════════════════════════════════════
9-- GOAL-1.1 / GOAL-1.2: nodes table (21 dedicated columns)
10-- ═══════════════════════════════════════════════════════════
11CREATE TABLE IF NOT EXISTS nodes (
12    id            TEXT PRIMARY KEY NOT NULL,
13    title         TEXT,
14    status        TEXT,
15    description   TEXT,
16    node_type     TEXT NOT NULL,
17    file_path     TEXT,
18    lang          TEXT,
19    start_line    INTEGER,
20    end_line      INTEGER,
21    signature     TEXT,
22    visibility    TEXT,
23    doc_comment   TEXT,
24    body_hash     TEXT,
25    node_kind     TEXT,
26    owner         TEXT,
27    source        TEXT,
28    repo          TEXT,
29    priority      INTEGER,
30    assigned_to   TEXT,
31    parent_id     TEXT,
32    depth         INTEGER,
33    complexity    REAL,
34    is_public     INTEGER,                     -- 0/1 boolean
35    body          TEXT,
36    created_at    TEXT,
37    updated_at    TEXT
38) STRICT;
39
40-- ═══════════════════════════════════════════════════════════
41-- GOAL-1.5: edges table with relation, weight, confidence
42-- ═══════════════════════════════════════════════════════════
43CREATE TABLE IF NOT EXISTS edges (
44    id          INTEGER PRIMARY KEY AUTOINCREMENT,
45    from_node   TEXT NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
46    to_node     TEXT NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
47    relation    TEXT NOT NULL,
48    weight      REAL DEFAULT 1.0,
49    confidence  REAL,
50    metadata    TEXT
51) STRICT;
52
53-- ═══════════════════════════════════════════════════════════
54-- GOAL-1.3: node_metadata KV table
55-- ═══════════════════════════════════════════════════════════
56CREATE TABLE IF NOT EXISTS node_metadata (
57    node_id     TEXT NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
58    key         TEXT NOT NULL,
59    value       TEXT NOT NULL,
60    PRIMARY KEY (node_id, key)
61) STRICT;
62
63-- ═══════════════════════════════════════════════════════════
64-- GOAL-1.4: node_tags many-to-many table
65-- ═══════════════════════════════════════════════════════════
66CREATE TABLE IF NOT EXISTS node_tags (
67    node_id     TEXT NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
68    tag         TEXT NOT NULL,
69    PRIMARY KEY (node_id, tag)
70) STRICT;
71
72-- ═══════════════════════════════════════════════════════════
73-- GOAL-1.6: knowledge table (JSON-blob per node)
74-- ═══════════════════════════════════════════════════════════
75CREATE TABLE IF NOT EXISTS knowledge (
76    node_id       TEXT PRIMARY KEY NOT NULL REFERENCES nodes(id) ON DELETE CASCADE,
77    findings      TEXT,
78    file_cache    TEXT,
79    tool_history  TEXT
80) STRICT;
81
82-- ═══════════════════════════════════════════════════════════
83-- GOAL-1.16: config table (project metadata + schema version)
84-- ═══════════════════════════════════════════════════════════
85CREATE TABLE IF NOT EXISTS config (
86    key         TEXT PRIMARY KEY NOT NULL,
87    value       TEXT NOT NULL
88) STRICT;
89
90-- ═══════════════════════════════════════════════════════════
91-- GOAL-1.8: change_log audit trail
92-- ═══════════════════════════════════════════════════════════
93CREATE TABLE IF NOT EXISTS change_log (
94    id          INTEGER PRIMARY KEY AUTOINCREMENT,
95    batch_id    TEXT,
96    timestamp   TEXT NOT NULL,
97    actor       TEXT,
98    operation   TEXT NOT NULL,
99    node_id     TEXT,
100    field       TEXT,
101    old_value   TEXT,
102    new_value   TEXT,
103    context     TEXT
104) STRICT;
105
106-- ═══════════════════════════════════════════════════════════
107-- GOAL-1.7: FTS5 virtual table for full-text search
108--
109-- SECURITY NOTE: User input to MATCH queries MUST be sanitized.
110-- The search() method wraps input in double-quotes for literal
111-- matching. Advanced FTS5 syntax (AND, OR, NEAR, etc.) should
112-- only be exposed through a separate API with explicit opt-in.
113-- ═══════════════════════════════════════════════════════════
114CREATE VIRTUAL TABLE IF NOT EXISTS nodes_fts USING fts5(
115    id,
116    title,
117    description,
118    signature,
119    doc_comment,
120    content='nodes',
121    content_rowid='rowid'
122);
123
124-- ═══════════════════════════════════════════════════════════
125-- GOAL-1.7: FTS5 content-sync triggers (§6.2)
126-- ═══════════════════════════════════════════════════════════
127
128-- After INSERT: add new content to FTS
129CREATE TRIGGER IF NOT EXISTS nodes_fts_insert AFTER INSERT ON nodes BEGIN
130    INSERT INTO nodes_fts(rowid, id, title, description, signature, doc_comment)
131    VALUES (new.rowid, new.id, new.title, new.description, new.signature, new.doc_comment);
132END;
133
134-- After UPDATE: remove old content, add new content
135CREATE TRIGGER IF NOT EXISTS nodes_fts_update AFTER UPDATE ON nodes BEGIN
136    INSERT INTO nodes_fts(nodes_fts, rowid, id, title, description, signature, doc_comment)
137    VALUES ('delete', old.rowid, old.id, old.title, old.description, old.signature, old.doc_comment);
138    INSERT INTO nodes_fts(rowid, id, title, description, signature, doc_comment)
139    VALUES (new.rowid, new.id, new.title, new.description, new.signature, new.doc_comment);
140END;
141
142-- After DELETE: remove content from FTS
143CREATE TRIGGER IF NOT EXISTS nodes_fts_delete AFTER DELETE ON nodes BEGIN
144    INSERT INTO nodes_fts(nodes_fts, rowid, id, title, description, signature, doc_comment)
145    VALUES ('delete', old.rowid, old.id, old.title, old.description, old.signature, old.doc_comment);
146END;
147
148-- ═══════════════════════════════════════════════════════════
149-- GOAL-1.12: Indexes on high-frequency query columns
150-- ═══════════════════════════════════════════════════════════
151
152CREATE INDEX IF NOT EXISTS idx_nodes_node_type ON nodes(node_type);
153CREATE INDEX IF NOT EXISTS idx_nodes_status    ON nodes(status);
154CREATE INDEX IF NOT EXISTS idx_nodes_file_path ON nodes(file_path);
155
156CREATE INDEX IF NOT EXISTS idx_edges_from      ON edges(from_node);
157CREATE INDEX IF NOT EXISTS idx_edges_to        ON edges(to_node);
158CREATE INDEX IF NOT EXISTS idx_edges_relation  ON edges(relation);
159CREATE INDEX IF NOT EXISTS idx_edges_from_to   ON edges(from_node, to_node);
160
161CREATE INDEX IF NOT EXISTS idx_tags_tag        ON node_tags(tag);
162CREATE INDEX IF NOT EXISTS idx_metadata_key    ON node_metadata(key);
163
164CREATE INDEX IF NOT EXISTS idx_nodes_parent_id ON nodes(parent_id);
165CREATE INDEX IF NOT EXISTS idx_nodes_owner     ON nodes(owner);
166CREATE INDEX IF NOT EXISTS idx_nodes_file_lang ON nodes(file_path, lang);
167
168-- ═══════════════════════════════════════════════════════════
169-- Initial config: schema version
170-- ═══════════════════════════════════════════════════════════
171INSERT OR IGNORE INTO config (key, value) VALUES ('schema_version', '1');
172"#;
gid_core/storage/schema.rs

gid_core/storage/
schema.rs
