im_wower
·
2026-03-21
0001_init.sql
1-- Initial Cloudflare D1 migration for the durable conductor control plane.
2-- Mirrors ops/sql/schema.sql.
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)
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
242COMMIT;