1pub const SCHEMA: &str = r#"
2-- Configuration
3CREATE TABLE IF NOT EXISTS config (
4 key TEXT PRIMARY KEY,
5 value TEXT NOT NULL,
6 updated_at TEXT DEFAULT CURRENT_TIMESTAMP
7);
8
9-- Spec sections (indexed from markdown)
10CREATE TABLE IF NOT EXISTS spec_sections (
11 id INTEGER PRIMARY KEY,
12 file_path TEXT NOT NULL,
13 heading_path TEXT NOT NULL,
14 level INTEGER NOT NULL,
15 content TEXT NOT NULL,
16 indexed_at TEXT DEFAULT CURRENT_TIMESTAMP
17);
18
19CREATE VIRTUAL TABLE IF NOT EXISTS spec_sections_fts USING fts5(
20 heading_path, content,
21 content='spec_sections', content_rowid='id',
22 tokenize='porter'
23);
24
25CREATE TRIGGER IF NOT EXISTS spec_sections_ai AFTER INSERT ON spec_sections BEGIN
26 INSERT INTO spec_sections_fts(rowid, heading_path, content)
27 VALUES (NEW.id, NEW.heading_path, NEW.content);
28END;
29
30CREATE TRIGGER IF NOT EXISTS spec_sections_ad AFTER DELETE ON spec_sections BEGIN
31 INSERT INTO spec_sections_fts(spec_sections_fts, rowid, heading_path, content)
32 VALUES('delete', OLD.id, OLD.heading_path, OLD.content);
33END;
34
35CREATE TRIGGER IF NOT EXISTS spec_sections_au AFTER UPDATE ON spec_sections BEGIN
36 INSERT INTO spec_sections_fts(spec_sections_fts, rowid, heading_path, content)
37 VALUES('delete', OLD.id, OLD.heading_path, OLD.content);
38 INSERT INTO spec_sections_fts(rowid, heading_path, content)
39 VALUES (NEW.id, NEW.heading_path, NEW.content);
40END;
41
42-- Tasks (replaces fix_plan.md)
43CREATE TABLE IF NOT EXISTS tasks (
44 id INTEGER PRIMARY KEY,
45 description TEXT NOT NULL,
46 status TEXT DEFAULT 'pending'
47 CHECK(status IN ('pending', 'in_progress', 'completed', 'blocked', 'cancelled')),
48 priority INTEGER DEFAULT 5,
49 blocked_by TEXT,
50 spec_section_id INTEGER,
51 created_at TEXT DEFAULT CURRENT_TIMESTAMP,
52 started_at TEXT,
53 completed_at TEXT,
54 FOREIGN KEY (spec_section_id) REFERENCES spec_sections(id)
55);
56
57CREATE VIRTUAL TABLE IF NOT EXISTS tasks_fts USING fts5(
58 description,
59 content='tasks', content_rowid='id',
60 tokenize='porter'
61);
62
63CREATE TRIGGER IF NOT EXISTS tasks_ai AFTER INSERT ON tasks BEGIN
64 INSERT INTO tasks_fts(rowid, description) VALUES (NEW.id, NEW.description);
65END;
66
67CREATE TRIGGER IF NOT EXISTS tasks_ad AFTER DELETE ON tasks BEGIN
68 INSERT INTO tasks_fts(tasks_fts, rowid, description)
69 VALUES('delete', OLD.id, OLD.description);
70END;
71
72CREATE TRIGGER IF NOT EXISTS tasks_au AFTER UPDATE ON tasks BEGIN
73 INSERT INTO tasks_fts(tasks_fts, rowid, description)
74 VALUES('delete', OLD.id, OLD.description);
75 INSERT INTO tasks_fts(rowid, description) VALUES (NEW.id, NEW.description);
76END;
77
78-- Iterations (each loop cycle)
79CREATE TABLE IF NOT EXISTS iterations (
80 id INTEGER PRIMARY KEY,
81 task_id INTEGER NOT NULL,
82 status TEXT DEFAULT 'in_progress'
83 CHECK(status IN ('in_progress', 'completed', 'failed', 'reverted')),
84 attempt_number INTEGER DEFAULT 1,
85 started_at TEXT DEFAULT CURRENT_TIMESTAMP,
86 ended_at TEXT,
87 duration_seconds REAL,
88 commit_hash TEXT,
89 notes TEXT,
90 FOREIGN KEY (task_id) REFERENCES tasks(id)
91);
92
93-- Actions (what was attempted)
94CREATE TABLE IF NOT EXISTS actions (
95 id INTEGER PRIMARY KEY,
96 iteration_id INTEGER NOT NULL,
97 action_type TEXT NOT NULL,
98 description TEXT NOT NULL,
99 file_path TEXT,
100 created_at TEXT DEFAULT CURRENT_TIMESTAMP,
101 FOREIGN KEY (iteration_id) REFERENCES iterations(id)
102);
103
104-- Outcomes (what happened)
105CREATE TABLE IF NOT EXISTS outcomes (
106 id INTEGER PRIMARY KEY,
107 action_id INTEGER NOT NULL,
108 success INTEGER NOT NULL,
109 output_summary TEXT,
110 error_message TEXT,
111 duration_seconds REAL,
112 created_at TEXT DEFAULT CURRENT_TIMESTAMP,
113 FOREIGN KEY (action_id) REFERENCES actions(id)
114);
115
116-- Failure patterns (categorized failure types)
117CREATE TABLE IF NOT EXISTS failure_patterns (
118 id INTEGER PRIMARY KEY,
119 pattern_key TEXT UNIQUE NOT NULL,
120 description TEXT NOT NULL,
121 category TEXT,
122 occurrence_count INTEGER DEFAULT 0,
123 first_seen_at TEXT DEFAULT CURRENT_TIMESTAMP,
124 last_seen_at TEXT
125);
126
127-- Failures (specific failure instances)
128CREATE TABLE IF NOT EXISTS failures (
129 id INTEGER PRIMARY KEY,
130 iteration_id INTEGER NOT NULL,
131 pattern_id INTEGER,
132 error_text TEXT NOT NULL,
133 file_path TEXT,
134 line_number INTEGER,
135 resolved INTEGER DEFAULT 0,
136 created_at TEXT DEFAULT CURRENT_TIMESTAMP,
137 resolved_at TEXT,
138 resolved_by_solution_id INTEGER,
139 FOREIGN KEY (iteration_id) REFERENCES iterations(id),
140 FOREIGN KEY (pattern_id) REFERENCES failure_patterns(id),
141 FOREIGN KEY (resolved_by_solution_id) REFERENCES solutions(id)
142);
143
144CREATE VIRTUAL TABLE IF NOT EXISTS failures_fts USING fts5(
145 error_text,
146 content='failures', content_rowid='id',
147 tokenize='porter'
148);
149
150CREATE TRIGGER IF NOT EXISTS failures_ai AFTER INSERT ON failures BEGIN
151 INSERT INTO failures_fts(rowid, error_text) VALUES (NEW.id, NEW.error_text);
152END;
153
154CREATE TRIGGER IF NOT EXISTS failures_ad AFTER DELETE ON failures BEGIN
155 INSERT INTO failures_fts(failures_fts, rowid, error_text)
156 VALUES('delete', OLD.id, OLD.error_text);
157END;
158
159CREATE TRIGGER IF NOT EXISTS failures_au AFTER UPDATE ON failures BEGIN
160 INSERT INTO failures_fts(failures_fts, rowid, error_text)
161 VALUES('delete', OLD.id, OLD.error_text);
162 INSERT INTO failures_fts(rowid, error_text) VALUES (NEW.id, NEW.error_text);
163END;
164
165-- Solutions (fixes with earned trust)
166CREATE TABLE IF NOT EXISTS solutions (
167 id INTEGER PRIMARY KEY,
168 pattern_id INTEGER NOT NULL,
169 description TEXT NOT NULL,
170 code_example TEXT,
171 confidence REAL DEFAULT 0.3,
172 success_count INTEGER DEFAULT 0,
173 failure_count INTEGER DEFAULT 0,
174 created_at TEXT DEFAULT CURRENT_TIMESTAMP,
175 last_used_at TEXT,
176 FOREIGN KEY (pattern_id) REFERENCES failure_patterns(id)
177);
178
179CREATE VIRTUAL TABLE IF NOT EXISTS solutions_fts USING fts5(
180 description, code_example,
181 content='solutions', content_rowid='id',
182 tokenize='porter'
183);
184
185CREATE TRIGGER IF NOT EXISTS solutions_ai AFTER INSERT ON solutions BEGIN
186 INSERT INTO solutions_fts(rowid, description, code_example)
187 VALUES (NEW.id, NEW.description, COALESCE(NEW.code_example, ''));
188END;
189
190CREATE TRIGGER IF NOT EXISTS solutions_ad AFTER DELETE ON solutions BEGIN
191 INSERT INTO solutions_fts(solutions_fts, rowid, description, code_example)
192 VALUES('delete', OLD.id, OLD.description, COALESCE(OLD.code_example, ''));
193END;
194
195CREATE TRIGGER IF NOT EXISTS solutions_au AFTER UPDATE ON solutions BEGIN
196 INSERT INTO solutions_fts(solutions_fts, rowid, description, code_example)
197 VALUES('delete', OLD.id, OLD.description, COALESCE(OLD.code_example, ''));
198 INSERT INTO solutions_fts(rowid, description, code_example)
199 VALUES (NEW.id, NEW.description, COALESCE(NEW.code_example, ''));
200END;
201
202-- Solution applications (tracking when solutions were used)
203CREATE TABLE IF NOT EXISTS solution_applications (
204 id INTEGER PRIMARY KEY,
205 solution_id INTEGER NOT NULL,
206 failure_id INTEGER NOT NULL,
207 iteration_id INTEGER NOT NULL,
208 success INTEGER,
209 applied_at TEXT DEFAULT CURRENT_TIMESTAMP,
210 FOREIGN KEY (solution_id) REFERENCES solutions(id),
211 FOREIGN KEY (failure_id) REFERENCES failures(id),
212 FOREIGN KEY (iteration_id) REFERENCES iterations(id)
213);
214
215-- Learnings (project-specific operational knowledge - AGENT.md equivalent)
216CREATE TABLE IF NOT EXISTS learnings (
217 id INTEGER PRIMARY KEY,
218 category TEXT,
219 description TEXT NOT NULL,
220 discovered_at TEXT DEFAULT CURRENT_TIMESTAMP,
221 times_referenced INTEGER DEFAULT 0
222);
223
224CREATE VIRTUAL TABLE IF NOT EXISTS learnings_fts USING fts5(
225 category, description,
226 content='learnings', content_rowid='id',
227 tokenize='porter'
228);
229
230CREATE TRIGGER IF NOT EXISTS learnings_ai AFTER INSERT ON learnings BEGIN
231 INSERT INTO learnings_fts(rowid, category, description)
232 VALUES (NEW.id, COALESCE(NEW.category, ''), NEW.description);
233END;
234
235CREATE TRIGGER IF NOT EXISTS learnings_ad AFTER DELETE ON learnings BEGIN
236 INSERT INTO learnings_fts(learnings_fts, rowid, category, description)
237 VALUES('delete', OLD.id, COALESCE(OLD.category, ''), OLD.description);
238END;
239
240CREATE TRIGGER IF NOT EXISTS learnings_au AFTER UPDATE ON learnings BEGIN
241 INSERT INTO learnings_fts(learnings_fts, rowid, category, description)
242 VALUES('delete', OLD.id, COALESCE(OLD.category, ''), OLD.description);
243 INSERT INTO learnings_fts(rowid, category, description)
244 VALUES (NEW.id, COALESCE(NEW.category, ''), NEW.description);
245END;
246"#;