baa-conductor

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)
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;