codex@macbookpro
·
2026-03-31
schema.sql
1-- Cloudflare D1 schema for the durable conductor control plane.
2-- Based on DESIGN.md section 10.
3
4BEGIN TRANSACTION;
5
6CREATE TABLE IF NOT EXISTS leader_lease (
7 lease_name TEXT PRIMARY KEY CHECK (lease_name = 'global'),
8 holder_id TEXT NOT NULL,
9 holder_host TEXT NOT NULL,
10 term INTEGER NOT NULL CHECK (term >= 0),
11 lease_expires_at INTEGER NOT NULL,
12 renewed_at INTEGER NOT NULL,
13 preferred_holder_id TEXT,
14 metadata_json TEXT
15);
16
17CREATE TABLE IF NOT EXISTS controllers (
18 controller_id TEXT PRIMARY KEY,
19 host TEXT NOT NULL,
20 role TEXT NOT NULL,
21 priority INTEGER NOT NULL CHECK (priority >= 0),
22 status TEXT NOT NULL,
23 version TEXT,
24 last_heartbeat_at INTEGER NOT NULL,
25 last_started_at INTEGER,
26 metadata_json TEXT
27);
28
29CREATE INDEX IF NOT EXISTS idx_controllers_status_heartbeat
30ON controllers(status, last_heartbeat_at);
31
32CREATE TABLE IF NOT EXISTS workers (
33 worker_id TEXT PRIMARY KEY,
34 controller_id TEXT NOT NULL,
35 host TEXT NOT NULL,
36 worker_type TEXT NOT NULL,
37 status TEXT NOT NULL,
38 max_parallelism INTEGER NOT NULL DEFAULT 1 CHECK (max_parallelism >= 1),
39 current_load INTEGER NOT NULL DEFAULT 0 CHECK (current_load >= 0),
40 last_heartbeat_at INTEGER NOT NULL,
41 capabilities_json TEXT,
42 metadata_json TEXT,
43 FOREIGN KEY (controller_id) REFERENCES controllers(controller_id) ON DELETE CASCADE
44);
45
46CREATE INDEX IF NOT EXISTS idx_workers_controller_status
47ON workers(controller_id, status, last_heartbeat_at);
48
49CREATE TABLE IF NOT EXISTS tasks (
50 task_id TEXT PRIMARY KEY,
51 repo TEXT NOT NULL,
52 task_type TEXT NOT NULL,
53 title TEXT NOT NULL,
54 goal TEXT NOT NULL,
55 source TEXT NOT NULL,
56 priority INTEGER NOT NULL DEFAULT 100,
57 status TEXT NOT NULL,
58 planning_strategy TEXT,
59 planner_provider TEXT,
60 branch_name TEXT,
61 base_ref TEXT,
62 target_host TEXT,
63 assigned_controller_id TEXT,
64 current_step_index INTEGER NOT NULL DEFAULT -1 CHECK (current_step_index >= -1),
65 constraints_json TEXT,
66 acceptance_json TEXT,
67 metadata_json TEXT,
68 result_summary TEXT,
69 result_json TEXT,
70 error_text TEXT,
71 created_at INTEGER NOT NULL,
72 updated_at INTEGER NOT NULL,
73 started_at INTEGER,
74 finished_at INTEGER,
75 FOREIGN KEY (assigned_controller_id) REFERENCES controllers(controller_id) ON DELETE SET NULL
76);
77
78CREATE INDEX IF NOT EXISTS idx_tasks_status_priority
79ON tasks(status, priority, created_at);
80
81CREATE INDEX IF NOT EXISTS idx_tasks_repo
82ON tasks(repo, created_at);
83
84CREATE INDEX IF NOT EXISTS idx_tasks_controller_status
85ON tasks(assigned_controller_id, status, updated_at);
86
87CREATE TABLE IF NOT EXISTS task_steps (
88 step_id TEXT PRIMARY KEY,
89 task_id TEXT NOT NULL,
90 step_index INTEGER NOT NULL CHECK (step_index >= 0),
91 step_name TEXT NOT NULL,
92 step_kind TEXT NOT NULL,
93 status TEXT NOT NULL,
94 assigned_worker_id TEXT,
95 assigned_controller_id TEXT,
96 timeout_sec INTEGER NOT NULL CHECK (timeout_sec > 0),
97 retry_limit INTEGER NOT NULL DEFAULT 0 CHECK (retry_limit >= 0),
98 retry_count INTEGER NOT NULL DEFAULT 0 CHECK (retry_count >= 0),
99 lease_expires_at INTEGER,
100 input_json TEXT,
101 output_json TEXT,
102 summary TEXT,
103 error_text TEXT,
104 created_at INTEGER NOT NULL,
105 updated_at INTEGER NOT NULL,
106 started_at INTEGER,
107 finished_at INTEGER,
108 UNIQUE(task_id, step_index),
109 FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
110 FOREIGN KEY (assigned_worker_id) REFERENCES workers(worker_id) ON DELETE SET NULL,
111 FOREIGN KEY (assigned_controller_id) REFERENCES controllers(controller_id) ON DELETE SET NULL
112);
113
114CREATE INDEX IF NOT EXISTS idx_task_steps_task_status
115ON task_steps(task_id, status, step_index);
116
117CREATE INDEX IF NOT EXISTS idx_task_steps_worker_status
118ON task_steps(assigned_worker_id, status, lease_expires_at);
119
120CREATE TABLE IF NOT EXISTS task_runs (
121 run_id TEXT PRIMARY KEY,
122 task_id TEXT NOT NULL,
123 step_id TEXT NOT NULL,
124 worker_id TEXT NOT NULL,
125 controller_id TEXT NOT NULL,
126 host TEXT NOT NULL,
127 pid INTEGER CHECK (pid IS NULL OR pid > 0),
128 status TEXT NOT NULL,
129 lease_expires_at INTEGER,
130 heartbeat_at INTEGER,
131 log_dir TEXT NOT NULL,
132 stdout_path TEXT,
133 stderr_path TEXT,
134 worker_log_path TEXT,
135 checkpoint_seq INTEGER NOT NULL DEFAULT 0 CHECK (checkpoint_seq >= 0),
136 exit_code INTEGER,
137 result_json TEXT,
138 error_text TEXT,
139 created_at INTEGER NOT NULL,
140 started_at INTEGER,
141 finished_at INTEGER,
142 FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
143 FOREIGN KEY (step_id) REFERENCES task_steps(step_id) ON DELETE CASCADE,
144 FOREIGN KEY (worker_id) REFERENCES workers(worker_id) ON DELETE RESTRICT,
145 FOREIGN KEY (controller_id) REFERENCES controllers(controller_id) ON DELETE RESTRICT
146);
147
148CREATE INDEX IF NOT EXISTS idx_task_runs_task
149ON task_runs(task_id, created_at);
150
151CREATE INDEX IF NOT EXISTS idx_task_runs_step
152ON task_runs(step_id, created_at);
153
154CREATE INDEX IF NOT EXISTS idx_task_runs_worker_status
155ON task_runs(worker_id, status, heartbeat_at);
156
157CREATE TABLE IF NOT EXISTS task_checkpoints (
158 checkpoint_id TEXT PRIMARY KEY,
159 task_id TEXT NOT NULL,
160 step_id TEXT NOT NULL,
161 run_id TEXT NOT NULL,
162 seq INTEGER NOT NULL CHECK (seq >= 0),
163 checkpoint_type TEXT NOT NULL,
164 summary TEXT,
165 content_text TEXT,
166 content_json TEXT,
167 created_at INTEGER NOT NULL,
168 UNIQUE(run_id, seq),
169 FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
170 FOREIGN KEY (step_id) REFERENCES task_steps(step_id) ON DELETE CASCADE,
171 FOREIGN KEY (run_id) REFERENCES task_runs(run_id) ON DELETE CASCADE
172);
173
174CREATE INDEX IF NOT EXISTS idx_task_checkpoints_step_seq
175ON task_checkpoints(step_id, seq);
176
177CREATE INDEX IF NOT EXISTS idx_task_checkpoints_task_created
178ON task_checkpoints(task_id, created_at);
179
180CREATE TABLE IF NOT EXISTS task_logs (
181 log_id INTEGER PRIMARY KEY AUTOINCREMENT,
182 task_id TEXT NOT NULL,
183 step_id TEXT,
184 run_id TEXT NOT NULL,
185 seq INTEGER NOT NULL CHECK (seq >= 0),
186 stream TEXT NOT NULL,
187 level TEXT,
188 message TEXT NOT NULL,
189 created_at INTEGER NOT NULL,
190 FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
191 FOREIGN KEY (step_id) REFERENCES task_steps(step_id) ON DELETE SET NULL,
192 FOREIGN KEY (run_id) REFERENCES task_runs(run_id) ON DELETE CASCADE
193);
194
195CREATE INDEX IF NOT EXISTS idx_task_logs_run_seq
196ON task_logs(run_id, seq);
197
198CREATE INDEX IF NOT EXISTS idx_task_logs_task_created
199ON task_logs(task_id, created_at);
200
201CREATE TABLE IF NOT EXISTS system_state (
202 state_key TEXT PRIMARY KEY,
203 value_json TEXT NOT NULL,
204 updated_at INTEGER NOT NULL
205);
206
207INSERT INTO system_state (
208 state_key,
209 value_json,
210 updated_at
211)
212VALUES (
213 'automation',
214 '{"mode":"running"}',
215 CAST(strftime('%s', 'now') AS INTEGER) * 1000
216)
217ON CONFLICT(state_key) DO NOTHING;
218
219CREATE TABLE IF NOT EXISTS task_artifacts (
220 artifact_id TEXT PRIMARY KEY,
221 task_id TEXT NOT NULL,
222 step_id TEXT,
223 run_id TEXT,
224 artifact_type TEXT NOT NULL,
225 path TEXT,
226 uri TEXT,
227 size_bytes INTEGER CHECK (size_bytes IS NULL OR size_bytes >= 0),
228 sha256 TEXT,
229 metadata_json TEXT,
230 created_at INTEGER NOT NULL,
231 FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
232 FOREIGN KEY (step_id) REFERENCES task_steps(step_id) ON DELETE SET NULL,
233 FOREIGN KEY (run_id) REFERENCES task_runs(run_id) ON DELETE SET NULL
234);
235
236CREATE INDEX IF NOT EXISTS idx_task_artifacts_task_created
237ON task_artifacts(task_id, created_at);
238
239CREATE INDEX IF NOT EXISTS idx_task_artifacts_run_type
240ON task_artifacts(run_id, artifact_type);
241
242CREATE TABLE IF NOT EXISTS browser_login_states (
243 platform TEXT NOT NULL,
244 host TEXT NOT NULL,
245 browser TEXT NOT NULL,
246 client_id TEXT NOT NULL,
247 account TEXT NOT NULL,
248 credential_fingerprint TEXT NOT NULL,
249 captured_at INTEGER NOT NULL,
250 last_seen_at INTEGER NOT NULL,
251 status TEXT NOT NULL CHECK (status IN ('fresh', 'stale', 'lost')),
252 PRIMARY KEY (platform, client_id, account),
253 CHECK (captured_at <= last_seen_at)
254);
255
256CREATE INDEX IF NOT EXISTS idx_browser_login_states_status_seen
257ON browser_login_states(status, last_seen_at);
258
259CREATE INDEX IF NOT EXISTS idx_browser_login_states_lookup
260ON browser_login_states(platform, host, browser, account, last_seen_at);
261
262CREATE TABLE IF NOT EXISTS browser_endpoint_metadata (
263 platform TEXT NOT NULL,
264 client_id TEXT NOT NULL,
265 account TEXT NOT NULL,
266 endpoints_json TEXT NOT NULL,
267 updated_at INTEGER NOT NULL,
268 last_verified_at INTEGER,
269 PRIMARY KEY (platform, client_id, account)
270);
271
272CREATE INDEX IF NOT EXISTS idx_browser_endpoint_metadata_updated
273ON browser_endpoint_metadata(platform, account, updated_at);
274
275CREATE TABLE IF NOT EXISTS baa_message_dedupes (
276 record_id INTEGER PRIMARY KEY AUTOINCREMENT,
277 dedupe_key TEXT NOT NULL UNIQUE,
278 assistant_message_id TEXT NOT NULL,
279 conversation_id TEXT,
280 platform TEXT NOT NULL,
281 observed_at INTEGER,
282 created_at INTEGER NOT NULL
283);
284
285CREATE INDEX IF NOT EXISTS idx_baa_message_dedupes_created
286ON baa_message_dedupes(created_at, record_id);
287
288CREATE TABLE IF NOT EXISTS baa_instruction_dedupes (
289 record_id INTEGER PRIMARY KEY AUTOINCREMENT,
290 dedupe_key TEXT NOT NULL UNIQUE,
291 instruction_id TEXT NOT NULL,
292 assistant_message_id TEXT NOT NULL,
293 conversation_id TEXT,
294 platform TEXT NOT NULL,
295 target TEXT NOT NULL,
296 tool TEXT NOT NULL,
297 created_at INTEGER NOT NULL
298);
299
300CREATE INDEX IF NOT EXISTS idx_baa_instruction_dedupes_created
301ON baa_instruction_dedupes(created_at, record_id);
302
303CREATE TABLE IF NOT EXISTS baa_execution_journal (
304 journal_id INTEGER PRIMARY KEY AUTOINCREMENT,
305 summary_kind TEXT NOT NULL CHECK (summary_kind IN ('ingest', 'execute')),
306 assistant_message_id TEXT NOT NULL,
307 conversation_id TEXT,
308 message_dedupe_key TEXT NOT NULL,
309 platform TEXT NOT NULL,
310 source TEXT NOT NULL,
311 status TEXT NOT NULL,
312 ingested_at INTEGER NOT NULL,
313 observed_at INTEGER,
314 summary_json TEXT NOT NULL
315);
316
317CREATE INDEX IF NOT EXISTS idx_baa_execution_journal_kind_ingested
318ON baa_execution_journal(summary_kind, ingested_at, journal_id);
319
320COMMIT;