- commit
- 08680d9
- parent
- e4bbe2e
- author
- im_wower
- date
- 2026-03-21 19:41:28 +0800 CST
Implement D1 schema and db skeleton
4 files changed,
+1692,
-38
+17,
-6
1@@ -1,7 +1,7 @@
2 ---
3 task_id: T-002
4 title: D1 Schema 与 Migrations
5-status: todo
6+status: review
7 branch: feat/T-002-d1-schema
8 repo: /Users/george/code/baa-conductor
9 base_ref: main
10@@ -60,23 +60,34 @@ updated_at: 2026-03-21
11
12 ## files_changed
13
14-- 待填写
15+- `coordination/tasks/T-002-d1-schema.md`
16+- `ops/sql/schema.sql`
17+- `ops/sql/migrations/0001_init.sql`
18+- `packages/db/src/index.ts`
19
20 ## commands_run
21
22-- 待填写
23+- `sqlite3 ':memory:' < /Users/george/code/baa-conductor/ops/sql/schema.sql`
24+- `sqlite3 ':memory:' < /Users/george/code/baa-conductor/ops/sql/migrations/0001_init.sql`
25+- `sqlite3 ':memory:' ".read /Users/george/code/baa-conductor/ops/sql/schema.sql" "SELECT state_key || ':' || value_json FROM system_state;"`
26+- `/Users/george/code/baa-conductor/node_modules/.bin/tsc --noEmit -p /Users/george/code/baa-conductor/packages/db/tsconfig.json`
27+- `git diff --check -- /Users/george/code/baa-conductor/ops/sql/schema.sql /Users/george/code/baa-conductor/ops/sql/migrations/0001_init.sql /Users/george/code/baa-conductor/packages/db/src/index.ts /Users/george/code/baa-conductor/coordination/tasks/T-002-d1-schema.md`
28
29 ## result
30
31-- 待填写
32+- 按 `DESIGN.md` 第 10 节完成了 10 张 D1 表的可执行 schema 与首个 migration，并补上基础外键、恢复/查询需要的索引、数值约束，以及 `system_state` 中默认的 `automation=running` 初始化行。
33+- `packages/db/src/index.ts` 现提供完整表模型、D1 兼容接口、snake_case 到 camelCase 的 row mapper、常用 SQL 语句，以及面向 control plane 的低层 repository 骨架。
34
35 ## risks
36
37-- 待填写
38+- `leader_lease` 目前只提供低层 upsert 读写，真正的抢锁/CAS 续租语义仍需在 `T-004` 中基于当前 schema 实现。
39+- `controllers`、`workers`、`task_runs` 的状态值在设计文档中尚未完全收敛，因此当前模型保持 `string`，后续任务需要在业务层补齐约束或进一步收紧类型。
40+- 本次 SQL 只在本地 SQLite 语义下做了烟测；接入真实 Cloudflare D1 binding 后仍应补一次端到端迁移与读写验证。
41
42 ## next_handoff
43
44-- 给 `T-003` 和 `T-004` 提供稳定的数据模型基线
45+- `T-003` 可以直接复用 `D1ControlPlaneRepository` 的 `system_state`、controller/worker heartbeat、task/task_step/task_log 持久化骨架来接 control API。
46+- `T-004` 可以在当前 schema/index 基线上补 `leader_lease` 原子获取/续租逻辑，以及 runnable step claim 查询。
47
48 ## notes
49
+237,
-4
1@@ -1,9 +1,242 @@
2--- 初始 migration 占位文件。
3--- 该文件应在 T-002 中根据 schema.sql 完成。
4+-- Initial Cloudflare D1 migration for the durable conductor control plane.
5+-- Mirrors ops/sql/schema.sql.
6
7 BEGIN TRANSACTION;
8
9--- TODO: 初始化 D1 schema
10+CREATE TABLE IF NOT EXISTS leader_lease (
11+ lease_name TEXT PRIMARY KEY CHECK (lease_name = 'global'),
12+ holder_id TEXT NOT NULL,
13+ holder_host TEXT NOT NULL,
14+ term INTEGER NOT NULL CHECK (term >= 0),
15+ lease_expires_at INTEGER NOT NULL,
16+ renewed_at INTEGER NOT NULL,
17+ preferred_holder_id TEXT,
18+ metadata_json TEXT
19+);
20
21-COMMIT;
22+CREATE TABLE IF NOT EXISTS controllers (
23+ controller_id TEXT PRIMARY KEY,
24+ host TEXT NOT NULL,
25+ role TEXT NOT NULL,
26+ priority INTEGER NOT NULL CHECK (priority >= 0),
27+ status TEXT NOT NULL,
28+ version TEXT,
29+ last_heartbeat_at INTEGER NOT NULL,
30+ last_started_at INTEGER,
31+ metadata_json TEXT
32+);
33+
34+CREATE INDEX IF NOT EXISTS idx_controllers_status_heartbeat
35+ON controllers(status, last_heartbeat_at);
36+
37+CREATE TABLE IF NOT EXISTS workers (
38+ worker_id TEXT PRIMARY KEY,
39+ controller_id TEXT NOT NULL,
40+ host TEXT NOT NULL,
41+ worker_type TEXT NOT NULL,
42+ status TEXT NOT NULL,
43+ max_parallelism INTEGER NOT NULL DEFAULT 1 CHECK (max_parallelism >= 1),
44+ current_load INTEGER NOT NULL DEFAULT 0 CHECK (current_load >= 0),
45+ last_heartbeat_at INTEGER NOT NULL,
46+ capabilities_json TEXT,
47+ metadata_json TEXT,
48+ FOREIGN KEY (controller_id) REFERENCES controllers(controller_id) ON DELETE CASCADE
49+);
50+
51+CREATE INDEX IF NOT EXISTS idx_workers_controller_status
52+ON workers(controller_id, status, last_heartbeat_at);
53+
54+CREATE TABLE IF NOT EXISTS tasks (
55+ task_id TEXT PRIMARY KEY,
56+ repo TEXT NOT NULL,
57+ task_type TEXT NOT NULL,
58+ title TEXT NOT NULL,
59+ goal TEXT NOT NULL,
60+ source TEXT NOT NULL,
61+ priority INTEGER NOT NULL DEFAULT 100,
62+ status TEXT NOT NULL,
63+ planning_strategy TEXT,
64+ planner_provider TEXT,
65+ branch_name TEXT,
66+ base_ref TEXT,
67+ target_host TEXT,
68+ assigned_controller_id TEXT,
69+ current_step_index INTEGER NOT NULL DEFAULT -1 CHECK (current_step_index >= -1),
70+ constraints_json TEXT,
71+ acceptance_json TEXT,
72+ metadata_json TEXT,
73+ result_summary TEXT,
74+ result_json TEXT,
75+ error_text TEXT,
76+ created_at INTEGER NOT NULL,
77+ updated_at INTEGER NOT NULL,
78+ started_at INTEGER,
79+ finished_at INTEGER,
80+ FOREIGN KEY (assigned_controller_id) REFERENCES controllers(controller_id) ON DELETE SET NULL
81+);
82+
83+CREATE INDEX IF NOT EXISTS idx_tasks_status_priority
84+ON tasks(status, priority, created_at);
85+
86+CREATE INDEX IF NOT EXISTS idx_tasks_repo
87+ON tasks(repo, created_at);
88+
89+CREATE INDEX IF NOT EXISTS idx_tasks_controller_status
90+ON tasks(assigned_controller_id, status, updated_at);
91+
92+CREATE TABLE IF NOT EXISTS task_steps (
93+ step_id TEXT PRIMARY KEY,
94+ task_id TEXT NOT NULL,
95+ step_index INTEGER NOT NULL CHECK (step_index >= 0),
96+ step_name TEXT NOT NULL,
97+ step_kind TEXT NOT NULL,
98+ status TEXT NOT NULL,
99+ assigned_worker_id TEXT,
100+ assigned_controller_id TEXT,
101+ timeout_sec INTEGER NOT NULL CHECK (timeout_sec > 0),
102+ retry_limit INTEGER NOT NULL DEFAULT 0 CHECK (retry_limit >= 0),
103+ retry_count INTEGER NOT NULL DEFAULT 0 CHECK (retry_count >= 0),
104+ lease_expires_at INTEGER,
105+ input_json TEXT,
106+ output_json TEXT,
107+ summary TEXT,
108+ error_text TEXT,
109+ created_at INTEGER NOT NULL,
110+ updated_at INTEGER NOT NULL,
111+ started_at INTEGER,
112+ finished_at INTEGER,
113+ UNIQUE(task_id, step_index),
114+ FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
115+ FOREIGN KEY (assigned_worker_id) REFERENCES workers(worker_id) ON DELETE SET NULL,
116+ FOREIGN KEY (assigned_controller_id) REFERENCES controllers(controller_id) ON DELETE SET NULL
117+);
118+
119+CREATE INDEX IF NOT EXISTS idx_task_steps_task_status
120+ON task_steps(task_id, status, step_index);
121+
122+CREATE INDEX IF NOT EXISTS idx_task_steps_worker_status
123+ON task_steps(assigned_worker_id, status, lease_expires_at);
124+
125+CREATE TABLE IF NOT EXISTS task_runs (
126+ run_id TEXT PRIMARY KEY,
127+ task_id TEXT NOT NULL,
128+ step_id TEXT NOT NULL,
129+ worker_id TEXT NOT NULL,
130+ controller_id TEXT NOT NULL,
131+ host TEXT NOT NULL,
132+ pid INTEGER CHECK (pid IS NULL OR pid > 0),
133+ status TEXT NOT NULL,
134+ lease_expires_at INTEGER,
135+ heartbeat_at INTEGER,
136+ log_dir TEXT NOT NULL,
137+ stdout_path TEXT,
138+ stderr_path TEXT,
139+ worker_log_path TEXT,
140+ checkpoint_seq INTEGER NOT NULL DEFAULT 0 CHECK (checkpoint_seq >= 0),
141+ exit_code INTEGER,
142+ result_json TEXT,
143+ error_text TEXT,
144+ created_at INTEGER NOT NULL,
145+ started_at INTEGER,
146+ finished_at INTEGER,
147+ FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
148+ FOREIGN KEY (step_id) REFERENCES task_steps(step_id) ON DELETE CASCADE,
149+ FOREIGN KEY (worker_id) REFERENCES workers(worker_id) ON DELETE RESTRICT,
150+ FOREIGN KEY (controller_id) REFERENCES controllers(controller_id) ON DELETE RESTRICT
151+);
152+
153+CREATE INDEX IF NOT EXISTS idx_task_runs_task
154+ON task_runs(task_id, created_at);
155+
156+CREATE INDEX IF NOT EXISTS idx_task_runs_step
157+ON task_runs(step_id, created_at);
158
159+CREATE INDEX IF NOT EXISTS idx_task_runs_worker_status
160+ON task_runs(worker_id, status, heartbeat_at);
161+
162+CREATE TABLE IF NOT EXISTS task_checkpoints (
163+ checkpoint_id TEXT PRIMARY KEY,
164+ task_id TEXT NOT NULL,
165+ step_id TEXT NOT NULL,
166+ run_id TEXT NOT NULL,
167+ seq INTEGER NOT NULL CHECK (seq >= 0),
168+ checkpoint_type TEXT NOT NULL,
169+ summary TEXT,
170+ content_text TEXT,
171+ content_json TEXT,
172+ created_at INTEGER NOT NULL,
173+ UNIQUE(run_id, seq),
174+ FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
175+ FOREIGN KEY (step_id) REFERENCES task_steps(step_id) ON DELETE CASCADE,
176+ FOREIGN KEY (run_id) REFERENCES task_runs(run_id) ON DELETE CASCADE
177+);
178+
179+CREATE INDEX IF NOT EXISTS idx_task_checkpoints_step_seq
180+ON task_checkpoints(step_id, seq);
181+
182+CREATE INDEX IF NOT EXISTS idx_task_checkpoints_task_created
183+ON task_checkpoints(task_id, created_at);
184+
185+CREATE TABLE IF NOT EXISTS task_logs (
186+ log_id INTEGER PRIMARY KEY AUTOINCREMENT,
187+ task_id TEXT NOT NULL,
188+ step_id TEXT,
189+ run_id TEXT NOT NULL,
190+ seq INTEGER NOT NULL CHECK (seq >= 0),
191+ stream TEXT NOT NULL,
192+ level TEXT,
193+ message TEXT NOT NULL,
194+ created_at INTEGER NOT NULL,
195+ FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
196+ FOREIGN KEY (step_id) REFERENCES task_steps(step_id) ON DELETE SET NULL,
197+ FOREIGN KEY (run_id) REFERENCES task_runs(run_id) ON DELETE CASCADE
198+);
199+
200+CREATE INDEX IF NOT EXISTS idx_task_logs_run_seq
201+ON task_logs(run_id, seq);
202+
203+CREATE INDEX IF NOT EXISTS idx_task_logs_task_created
204+ON task_logs(task_id, created_at);
205+
206+CREATE TABLE IF NOT EXISTS system_state (
207+ state_key TEXT PRIMARY KEY,
208+ value_json TEXT NOT NULL,
209+ updated_at INTEGER NOT NULL
210+);
211+
212+INSERT INTO system_state (
213+ state_key,
214+ value_json,
215+ updated_at
216+)
217+VALUES (
218+ 'automation',
219+ '{"mode":"running"}',
220+ CAST(strftime('%s', 'now') AS INTEGER)
221+)
222+ON CONFLICT(state_key) DO NOTHING;
223+
224+CREATE TABLE IF NOT EXISTS task_artifacts (
225+ artifact_id TEXT PRIMARY KEY,
226+ task_id TEXT NOT NULL,
227+ step_id TEXT,
228+ run_id TEXT,
229+ artifact_type TEXT NOT NULL,
230+ path TEXT,
231+ uri TEXT,
232+ size_bytes INTEGER CHECK (size_bytes IS NULL OR size_bytes >= 0),
233+ sha256 TEXT,
234+ metadata_json TEXT,
235+ created_at INTEGER NOT NULL,
236+ FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
237+ FOREIGN KEY (step_id) REFERENCES task_steps(step_id) ON DELETE SET NULL,
238+ FOREIGN KEY (run_id) REFERENCES task_runs(run_id) ON DELETE SET NULL
239+);
240+
241+CREATE INDEX IF NOT EXISTS idx_task_artifacts_task_created
242+ON task_artifacts(task_id, created_at);
243+
244+CREATE INDEX IF NOT EXISTS idx_task_artifacts_run_type
245+ON task_artifacts(run_id, artifact_type);
246+
247+COMMIT;
+237,
-13
1@@ -1,18 +1,242 @@
2--- D1 schema 占位文件。
3--- 具体实现请按照 DESIGN.md 第 10 节补全。
4+-- Cloudflare D1 schema for the durable conductor control plane.
5+-- Based on DESIGN.md section 10.
6
7 BEGIN TRANSACTION;
8
9--- TODO: 创建 leader_lease
10--- TODO: 创建 controllers
11--- TODO: 创建 workers
12--- TODO: 创建 tasks
13--- TODO: 创建 task_steps
14--- TODO: 创建 task_runs
15--- TODO: 创建 task_checkpoints
16--- TODO: 创建 task_logs
17--- TODO: 创建 system_state
18--- TODO: 创建 task_artifacts
19+CREATE TABLE IF NOT EXISTS leader_lease (
20+ lease_name TEXT PRIMARY KEY CHECK (lease_name = 'global'),
21+ holder_id TEXT NOT NULL,
22+ holder_host TEXT NOT NULL,
23+ term INTEGER NOT NULL CHECK (term >= 0),
24+ lease_expires_at INTEGER NOT NULL,
25+ renewed_at INTEGER NOT NULL,
26+ preferred_holder_id TEXT,
27+ metadata_json TEXT
28+);
29
30-COMMIT;
31+CREATE TABLE IF NOT EXISTS controllers (
32+ controller_id TEXT PRIMARY KEY,
33+ host TEXT NOT NULL,
34+ role TEXT NOT NULL,
35+ priority INTEGER NOT NULL CHECK (priority >= 0),
36+ status TEXT NOT NULL,
37+ version TEXT,
38+ last_heartbeat_at INTEGER NOT NULL,
39+ last_started_at INTEGER,
40+ metadata_json TEXT
41+);
42+
43+CREATE INDEX IF NOT EXISTS idx_controllers_status_heartbeat
44+ON controllers(status, last_heartbeat_at);
45+
46+CREATE TABLE IF NOT EXISTS workers (
47+ worker_id TEXT PRIMARY KEY,
48+ controller_id TEXT NOT NULL,
49+ host TEXT NOT NULL,
50+ worker_type TEXT NOT NULL,
51+ status TEXT NOT NULL,
52+ max_parallelism INTEGER NOT NULL DEFAULT 1 CHECK (max_parallelism >= 1),
53+ current_load INTEGER NOT NULL DEFAULT 0 CHECK (current_load >= 0),
54+ last_heartbeat_at INTEGER NOT NULL,
55+ capabilities_json TEXT,
56+ metadata_json TEXT,
57+ FOREIGN KEY (controller_id) REFERENCES controllers(controller_id) ON DELETE CASCADE
58+);
59+
60+CREATE INDEX IF NOT EXISTS idx_workers_controller_status
61+ON workers(controller_id, status, last_heartbeat_at);
62+
63+CREATE TABLE IF NOT EXISTS tasks (
64+ task_id TEXT PRIMARY KEY,
65+ repo TEXT NOT NULL,
66+ task_type TEXT NOT NULL,
67+ title TEXT NOT NULL,
68+ goal TEXT NOT NULL,
69+ source TEXT NOT NULL,
70+ priority INTEGER NOT NULL DEFAULT 100,
71+ status TEXT NOT NULL,
72+ planning_strategy TEXT,
73+ planner_provider TEXT,
74+ branch_name TEXT,
75+ base_ref TEXT,
76+ target_host TEXT,
77+ assigned_controller_id TEXT,
78+ current_step_index INTEGER NOT NULL DEFAULT -1 CHECK (current_step_index >= -1),
79+ constraints_json TEXT,
80+ acceptance_json TEXT,
81+ metadata_json TEXT,
82+ result_summary TEXT,
83+ result_json TEXT,
84+ error_text TEXT,
85+ created_at INTEGER NOT NULL,
86+ updated_at INTEGER NOT NULL,
87+ started_at INTEGER,
88+ finished_at INTEGER,
89+ FOREIGN KEY (assigned_controller_id) REFERENCES controllers(controller_id) ON DELETE SET NULL
90+);
91+
92+CREATE INDEX IF NOT EXISTS idx_tasks_status_priority
93+ON tasks(status, priority, created_at);
94+
95+CREATE INDEX IF NOT EXISTS idx_tasks_repo
96+ON tasks(repo, created_at);
97+
98+CREATE INDEX IF NOT EXISTS idx_tasks_controller_status
99+ON tasks(assigned_controller_id, status, updated_at);
100+
101+CREATE TABLE IF NOT EXISTS task_steps (
102+ step_id TEXT PRIMARY KEY,
103+ task_id TEXT NOT NULL,
104+ step_index INTEGER NOT NULL CHECK (step_index >= 0),
105+ step_name TEXT NOT NULL,
106+ step_kind TEXT NOT NULL,
107+ status TEXT NOT NULL,
108+ assigned_worker_id TEXT,
109+ assigned_controller_id TEXT,
110+ timeout_sec INTEGER NOT NULL CHECK (timeout_sec > 0),
111+ retry_limit INTEGER NOT NULL DEFAULT 0 CHECK (retry_limit >= 0),
112+ retry_count INTEGER NOT NULL DEFAULT 0 CHECK (retry_count >= 0),
113+ lease_expires_at INTEGER,
114+ input_json TEXT,
115+ output_json TEXT,
116+ summary TEXT,
117+ error_text TEXT,
118+ created_at INTEGER NOT NULL,
119+ updated_at INTEGER NOT NULL,
120+ started_at INTEGER,
121+ finished_at INTEGER,
122+ UNIQUE(task_id, step_index),
123+ FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
124+ FOREIGN KEY (assigned_worker_id) REFERENCES workers(worker_id) ON DELETE SET NULL,
125+ FOREIGN KEY (assigned_controller_id) REFERENCES controllers(controller_id) ON DELETE SET NULL
126+);
127+
128+CREATE INDEX IF NOT EXISTS idx_task_steps_task_status
129+ON task_steps(task_id, status, step_index);
130+
131+CREATE INDEX IF NOT EXISTS idx_task_steps_worker_status
132+ON task_steps(assigned_worker_id, status, lease_expires_at);
133+
134+CREATE TABLE IF NOT EXISTS task_runs (
135+ run_id TEXT PRIMARY KEY,
136+ task_id TEXT NOT NULL,
137+ step_id TEXT NOT NULL,
138+ worker_id TEXT NOT NULL,
139+ controller_id TEXT NOT NULL,
140+ host TEXT NOT NULL,
141+ pid INTEGER CHECK (pid IS NULL OR pid > 0),
142+ status TEXT NOT NULL,
143+ lease_expires_at INTEGER,
144+ heartbeat_at INTEGER,
145+ log_dir TEXT NOT NULL,
146+ stdout_path TEXT,
147+ stderr_path TEXT,
148+ worker_log_path TEXT,
149+ checkpoint_seq INTEGER NOT NULL DEFAULT 0 CHECK (checkpoint_seq >= 0),
150+ exit_code INTEGER,
151+ result_json TEXT,
152+ error_text TEXT,
153+ created_at INTEGER NOT NULL,
154+ started_at INTEGER,
155+ finished_at INTEGER,
156+ FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
157+ FOREIGN KEY (step_id) REFERENCES task_steps(step_id) ON DELETE CASCADE,
158+ FOREIGN KEY (worker_id) REFERENCES workers(worker_id) ON DELETE RESTRICT,
159+ FOREIGN KEY (controller_id) REFERENCES controllers(controller_id) ON DELETE RESTRICT
160+);
161+
162+CREATE INDEX IF NOT EXISTS idx_task_runs_task
163+ON task_runs(task_id, created_at);
164+
165+CREATE INDEX IF NOT EXISTS idx_task_runs_step
166+ON task_runs(step_id, created_at);
167
168+CREATE INDEX IF NOT EXISTS idx_task_runs_worker_status
169+ON task_runs(worker_id, status, heartbeat_at);
170+
171+CREATE TABLE IF NOT EXISTS task_checkpoints (
172+ checkpoint_id TEXT PRIMARY KEY,
173+ task_id TEXT NOT NULL,
174+ step_id TEXT NOT NULL,
175+ run_id TEXT NOT NULL,
176+ seq INTEGER NOT NULL CHECK (seq >= 0),
177+ checkpoint_type TEXT NOT NULL,
178+ summary TEXT,
179+ content_text TEXT,
180+ content_json TEXT,
181+ created_at INTEGER NOT NULL,
182+ UNIQUE(run_id, seq),
183+ FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
184+ FOREIGN KEY (step_id) REFERENCES task_steps(step_id) ON DELETE CASCADE,
185+ FOREIGN KEY (run_id) REFERENCES task_runs(run_id) ON DELETE CASCADE
186+);
187+
188+CREATE INDEX IF NOT EXISTS idx_task_checkpoints_step_seq
189+ON task_checkpoints(step_id, seq);
190+
191+CREATE INDEX IF NOT EXISTS idx_task_checkpoints_task_created
192+ON task_checkpoints(task_id, created_at);
193+
194+CREATE TABLE IF NOT EXISTS task_logs (
195+ log_id INTEGER PRIMARY KEY AUTOINCREMENT,
196+ task_id TEXT NOT NULL,
197+ step_id TEXT,
198+ run_id TEXT NOT NULL,
199+ seq INTEGER NOT NULL CHECK (seq >= 0),
200+ stream TEXT NOT NULL,
201+ level TEXT,
202+ message TEXT NOT NULL,
203+ created_at INTEGER NOT NULL,
204+ FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
205+ FOREIGN KEY (step_id) REFERENCES task_steps(step_id) ON DELETE SET NULL,
206+ FOREIGN KEY (run_id) REFERENCES task_runs(run_id) ON DELETE CASCADE
207+);
208+
209+CREATE INDEX IF NOT EXISTS idx_task_logs_run_seq
210+ON task_logs(run_id, seq);
211+
212+CREATE INDEX IF NOT EXISTS idx_task_logs_task_created
213+ON task_logs(task_id, created_at);
214+
215+CREATE TABLE IF NOT EXISTS system_state (
216+ state_key TEXT PRIMARY KEY,
217+ value_json TEXT NOT NULL,
218+ updated_at INTEGER NOT NULL
219+);
220+
221+INSERT INTO system_state (
222+ state_key,
223+ value_json,
224+ updated_at
225+)
226+VALUES (
227+ 'automation',
228+ '{"mode":"running"}',
229+ CAST(strftime('%s', 'now') AS INTEGER)
230+)
231+ON CONFLICT(state_key) DO NOTHING;
232+
233+CREATE TABLE IF NOT EXISTS task_artifacts (
234+ artifact_id TEXT PRIMARY KEY,
235+ task_id TEXT NOT NULL,
236+ step_id TEXT,
237+ run_id TEXT,
238+ artifact_type TEXT NOT NULL,
239+ path TEXT,
240+ uri TEXT,
241+ size_bytes INTEGER CHECK (size_bytes IS NULL OR size_bytes >= 0),
242+ sha256 TEXT,
243+ metadata_json TEXT,
244+ created_at INTEGER NOT NULL,
245+ FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
246+ FOREIGN KEY (step_id) REFERENCES task_steps(step_id) ON DELETE SET NULL,
247+ FOREIGN KEY (run_id) REFERENCES task_runs(run_id) ON DELETE SET NULL
248+);
249+
250+CREATE INDEX IF NOT EXISTS idx_task_artifacts_task_created
251+ON task_artifacts(task_id, created_at);
252+
253+CREATE INDEX IF NOT EXISTS idx_task_artifacts_run_type
254+ON task_artifacts(run_id, artifact_type);
255+
256+COMMIT;
+1201,
-15
1@@ -1,32 +1,1218 @@
2+export const D1_TABLES = [
3+ "leader_lease",
4+ "controllers",
5+ "workers",
6+ "tasks",
7+ "task_steps",
8+ "task_runs",
9+ "task_checkpoints",
10+ "task_logs",
11+ "system_state",
12+ "task_artifacts"
13+] as const;
14+
15+export type D1TableName = (typeof D1_TABLES)[number];
16+
17+export const GLOBAL_LEASE_NAME = "global";
18+export const AUTOMATION_STATE_KEY = "automation";
19+export const DEFAULT_AUTOMATION_MODE = "running";
20+
21+export const AUTOMATION_MODE_VALUES = ["running", "draining", "paused"] as const;
22+export const TASK_STATUS_VALUES = [
23+ "queued",
24+ "planning",
25+ "running",
26+ "paused",
27+ "done",
28+ "failed",
29+ "canceled"
30+] as const;
31+export const STEP_STATUS_VALUES = ["pending", "running", "done", "failed", "timeout"] as const;
32+export const STEP_KIND_VALUES = ["planner", "codex", "shell", "git", "review", "finalize"] as const;
33+
34+export type AutomationMode = (typeof AUTOMATION_MODE_VALUES)[number];
35+export type TaskStatus = (typeof TASK_STATUS_VALUES)[number];
36+export type StepStatus = (typeof STEP_STATUS_VALUES)[number];
37+export type StepKind = (typeof STEP_KIND_VALUES)[number];
38+
39+export type JsonPrimitive = boolean | number | null | string;
40+export type JsonValue = JsonPrimitive | JsonObject | JsonValue[];
41+
42+export interface JsonObject {
43+ [key: string]: JsonValue;
44+}
45+
46+export type D1Bindable = number | null | string;
47+
48+export interface DatabaseRow {
49+ [column: string]: unknown;
50+}
51+
52+export interface D1ResultMeta {
53+ changed_db?: boolean;
54+ changes?: number;
55+ duration?: number;
56+ last_row_id?: number;
57+ rows_read?: number;
58+ rows_written?: number;
59+ served_by?: string;
60+ size_after?: number;
61+}
62+
63+export interface D1QueryResult<T = never> {
64+ meta: D1ResultMeta;
65+ results?: T[];
66+ success: boolean;
67+}
68+
69+export interface D1ExecResult {
70+ count?: number;
71+ duration?: number;
72+}
73+
74+export interface D1PreparedStatementLike {
75+ all<T = DatabaseRow>(): Promise<D1QueryResult<T>>;
76+ bind(...values: D1Bindable[]): D1PreparedStatementLike;
77+ first<T = DatabaseRow>(columnName?: string): Promise<T | null>;
78+ raw<T = unknown[]>(options?: { columnNames?: boolean }): Promise<T[]>;
79+ run(): Promise<D1QueryResult<never>>;
80+}
81+
82+export interface D1DatabaseLike {
83+ batch<T = never>(statements: D1PreparedStatementLike[]): Promise<Array<D1QueryResult<T>>>;
84+ exec(query: string): Promise<D1ExecResult>;
85+ prepare(query: string): D1PreparedStatementLike;
86+}
87+
88 export interface LeaderLeaseRecord {
89 leaseName: string;
90 holderId: string;
91 holderHost: string;
92 term: number;
93 leaseExpiresAt: number;
94+ renewedAt: number;
95+ preferredHolderId: string | null;
96+ metadataJson: string | null;
97+}
98+
99+export interface ControllerRecord {
100+ controllerId: string;
101+ host: string;
102+ role: string;
103+ priority: number;
104+ status: string;
105+ version: string | null;
106+ lastHeartbeatAt: number;
107+ lastStartedAt: number | null;
108+ metadataJson: string | null;
109+}
110+
111+export interface WorkerRecord {
112+ workerId: string;
113+ controllerId: string;
114+ host: string;
115+ workerType: string;
116+ status: string;
117+ maxParallelism: number;
118+ currentLoad: number;
119+ lastHeartbeatAt: number;
120+ capabilitiesJson: string | null;
121+ metadataJson: string | null;
122+}
123+
124+export interface TaskRecord {
125+ taskId: string;
126+ repo: string;
127+ taskType: string;
128+ title: string;
129+ goal: string;
130+ source: string;
131+ priority: number;
132+ status: TaskStatus;
133+ planningStrategy: string | null;
134+ plannerProvider: string | null;
135+ branchName: string | null;
136+ baseRef: string | null;
137+ targetHost: string | null;
138+ assignedControllerId: string | null;
139+ currentStepIndex: number;
140+ constraintsJson: string | null;
141+ acceptanceJson: string | null;
142+ metadataJson: string | null;
143+ resultSummary: string | null;
144+ resultJson: string | null;
145+ errorText: string | null;
146+ createdAt: number;
147+ updatedAt: number;
148+ startedAt: number | null;
149+ finishedAt: number | null;
150 }
151
152-export interface TaskClaimRequest {
153+export interface TaskStepRecord {
154+ stepId: string;
155+ taskId: string;
156+ stepIndex: number;
157+ stepName: string;
158+ stepKind: StepKind;
159+ status: StepStatus;
160+ assignedWorkerId: string | null;
161+ assignedControllerId: string | null;
162+ timeoutSec: number;
163+ retryLimit: number;
164+ retryCount: number;
165+ leaseExpiresAt: number | null;
166+ inputJson: string | null;
167+ outputJson: string | null;
168+ summary: string | null;
169+ errorText: string | null;
170+ createdAt: number;
171+ updatedAt: number;
172+ startedAt: number | null;
173+ finishedAt: number | null;
174+}
175+
176+export interface TaskRunRecord {
177+ runId: string;
178+ taskId: string;
179+ stepId: string;
180+ workerId: string;
181 controllerId: string;
182 host: string;
183+ pid: number | null;
184+ status: string;
185+ leaseExpiresAt: number | null;
186+ heartbeatAt: number | null;
187+ logDir: string;
188+ stdoutPath: string | null;
189+ stderrPath: string | null;
190+ workerLogPath: string | null;
191+ checkpointSeq: number;
192+ exitCode: number | null;
193+ resultJson: string | null;
194+ errorText: string | null;
195+ createdAt: number;
196+ startedAt: number | null;
197+ finishedAt: number | null;
198+}
199+
200+export interface TaskCheckpointRecord {
201+ checkpointId: string;
202+ taskId: string;
203+ stepId: string;
204+ runId: string;
205+ seq: number;
206+ checkpointType: string;
207+ summary: string | null;
208+ contentText: string | null;
209+ contentJson: string | null;
210+ createdAt: number;
211+}
212+
213+export interface TaskLogRecord {
214+ logId: number;
215+ taskId: string;
216+ stepId: string | null;
217+ runId: string;
218+ seq: number;
219+ stream: string;
220+ level: string | null;
221+ message: string;
222+ createdAt: number;
223+}
224+
225+export interface NewTaskLogRecord {
226+ taskId: string;
227+ stepId: string | null;
228+ runId: string;
229+ seq: number;
230+ stream: string;
231+ level: string | null;
232+ message: string;
233+ createdAt: number;
234+}
235+
236+export interface SystemStateRecord {
237+ stateKey: string;
238+ valueJson: string;
239+ updatedAt: number;
240+}
241+
242+export interface AutomationStateRecord extends SystemStateRecord {
243+ stateKey: typeof AUTOMATION_STATE_KEY;
244+ mode: AutomationMode;
245+}
246+
247+export interface TaskArtifactRecord {
248+ artifactId: string;
249+ taskId: string;
250+ stepId: string | null;
251+ runId: string | null;
252+ artifactType: string;
253+ path: string | null;
254+ uri: string | null;
255+ sizeBytes: number | null;
256+ sha256: string | null;
257+ metadataJson: string | null;
258+ createdAt: number;
259 }
260
261 export interface ControlPlaneRepository {
262+ appendTaskLog(record: NewTaskLogRecord): Promise<number | null>;
263+ ensureAutomationState(mode?: AutomationMode): Promise<void>;
264+ getAutomationState(): Promise<AutomationStateRecord | null>;
265 getCurrentLease(): Promise<LeaderLeaseRecord | null>;
266- acquireOrRenewLease(controllerId: string, host: string, ttlSec: number): Promise<LeaderLeaseRecord>;
267- claimNextRunnableStep(request: TaskClaimRequest): Promise<string | null>;
268+ getSystemState(stateKey: string): Promise<SystemStateRecord | null>;
269+ getTask(taskId: string): Promise<TaskRecord | null>;
270+ insertTask(record: TaskRecord): Promise<void>;
271+ insertTaskArtifact(record: TaskArtifactRecord): Promise<void>;
272+ insertTaskCheckpoint(record: TaskCheckpointRecord): Promise<void>;
273+ insertTaskRun(record: TaskRunRecord): Promise<void>;
274+ insertTaskStep(record: TaskStepRecord): Promise<void>;
275+ insertTaskSteps(records: TaskStepRecord[]): Promise<void>;
276+ listTaskSteps(taskId: string): Promise<TaskStepRecord[]>;
277+ putLeaderLease(record: LeaderLeaseRecord): Promise<void>;
278+ putSystemState(record: SystemStateRecord): Promise<void>;
279+ setAutomationMode(mode: AutomationMode, updatedAt?: number): Promise<void>;
280+ upsertController(record: ControllerRecord): Promise<void>;
281+ upsertWorker(record: WorkerRecord): Promise<void>;
282 }
283
284-export const D1_TABLES = [
285- "leader_lease",
286- "controllers",
287- "workers",
288- "tasks",
289- "task_steps",
290- "task_runs",
291- "task_checkpoints",
292- "task_logs",
293- "system_state",
294- "task_artifacts"
295-] as const;
296+const AUTOMATION_MODE_SET = new Set<string>(AUTOMATION_MODE_VALUES);
297+const TASK_STATUS_SET = new Set<string>(TASK_STATUS_VALUES);
298+const STEP_STATUS_SET = new Set<string>(STEP_STATUS_VALUES);
299+const STEP_KIND_SET = new Set<string>(STEP_KIND_VALUES);
300+
301+export function nowUnixSeconds(date: Date = new Date()): number {
302+ return Math.floor(date.getTime() / 1000);
303+}
304+
305+export function stringifyJson(value: JsonValue | null | undefined): string | null {
306+ if (value == null) {
307+ return null;
308+ }
309+
310+ return JSON.stringify(value);
311+}
312+
313+export function parseJsonText<T>(jsonText: string | null | undefined): T | null {
314+ if (jsonText == null || jsonText === "") {
315+ return null;
316+ }
317+
318+ return JSON.parse(jsonText) as T;
319+}
320+
321+export function isAutomationMode(value: unknown): value is AutomationMode {
322+ return typeof value === "string" && AUTOMATION_MODE_SET.has(value);
323+}
324+
325+export function isTaskStatus(value: unknown): value is TaskStatus {
326+ return typeof value === "string" && TASK_STATUS_SET.has(value);
327+}
328+
329+export function isStepStatus(value: unknown): value is StepStatus {
330+ return typeof value === "string" && STEP_STATUS_SET.has(value);
331+}
332+
333+export function isStepKind(value: unknown): value is StepKind {
334+ return typeof value === "string" && STEP_KIND_SET.has(value);
335+}
336+
337+export function buildAutomationStateValue(mode: AutomationMode): string {
338+ return JSON.stringify({ mode });
339+}
340+
341+function toD1Bindable(value: D1Bindable | undefined): D1Bindable {
342+ return value ?? null;
343+}
344+
345+function readColumn(row: DatabaseRow, column: string): unknown {
346+ if (!(column in row)) {
347+ throw new TypeError(`Expected D1 row to include column "${column}".`);
348+ }
349+
350+ return row[column];
351+}
352+
353+function readRequiredString(row: DatabaseRow, column: string): string {
354+ const value = readColumn(row, column);
355+
356+ if (typeof value !== "string") {
357+ throw new TypeError(`Expected column "${column}" to be a string.`);
358+ }
359+
360+ return value;
361+}
362+
363+function readOptionalString(row: DatabaseRow, column: string): string | null {
364+ const value = readColumn(row, column);
365+
366+ if (value == null) {
367+ return null;
368+ }
369+
370+ if (typeof value !== "string") {
371+ throw new TypeError(`Expected column "${column}" to be a string or null.`);
372+ }
373+
374+ return value;
375+}
376+
377+function readRequiredNumber(row: DatabaseRow, column: string): number {
378+ const value = readColumn(row, column);
379+
380+ if (typeof value !== "number") {
381+ throw new TypeError(`Expected column "${column}" to be a number.`);
382+ }
383+
384+ return value;
385+}
386
387+function readOptionalNumber(row: DatabaseRow, column: string): number | null {
388+ const value = readColumn(row, column);
389+
390+ if (value == null) {
391+ return null;
392+ }
393+
394+ if (typeof value !== "number") {
395+ throw new TypeError(`Expected column "${column}" to be a number or null.`);
396+ }
397+
398+ return value;
399+}
400+
401+function readTaskStatus(row: DatabaseRow, column: string): TaskStatus {
402+ const value = readRequiredString(row, column);
403+
404+ if (!isTaskStatus(value)) {
405+ throw new TypeError(`Unexpected task status "${value}".`);
406+ }
407+
408+ return value;
409+}
410+
411+function readStepStatus(row: DatabaseRow, column: string): StepStatus {
412+ const value = readRequiredString(row, column);
413+
414+ if (!isStepStatus(value)) {
415+ throw new TypeError(`Unexpected step status "${value}".`);
416+ }
417+
418+ return value;
419+}
420+
421+function readStepKind(row: DatabaseRow, column: string): StepKind {
422+ const value = readRequiredString(row, column);
423+
424+ if (!isStepKind(value)) {
425+ throw new TypeError(`Unexpected step kind "${value}".`);
426+ }
427+
428+ return value;
429+}
430+
431+export function mapLeaderLeaseRow(row: DatabaseRow): LeaderLeaseRecord {
432+ return {
433+ leaseName: readRequiredString(row, "lease_name"),
434+ holderId: readRequiredString(row, "holder_id"),
435+ holderHost: readRequiredString(row, "holder_host"),
436+ term: readRequiredNumber(row, "term"),
437+ leaseExpiresAt: readRequiredNumber(row, "lease_expires_at"),
438+ renewedAt: readRequiredNumber(row, "renewed_at"),
439+ preferredHolderId: readOptionalString(row, "preferred_holder_id"),
440+ metadataJson: readOptionalString(row, "metadata_json")
441+ };
442+}
443+
444+export function mapControllerRow(row: DatabaseRow): ControllerRecord {
445+ return {
446+ controllerId: readRequiredString(row, "controller_id"),
447+ host: readRequiredString(row, "host"),
448+ role: readRequiredString(row, "role"),
449+ priority: readRequiredNumber(row, "priority"),
450+ status: readRequiredString(row, "status"),
451+ version: readOptionalString(row, "version"),
452+ lastHeartbeatAt: readRequiredNumber(row, "last_heartbeat_at"),
453+ lastStartedAt: readOptionalNumber(row, "last_started_at"),
454+ metadataJson: readOptionalString(row, "metadata_json")
455+ };
456+}
457+
458+export function mapWorkerRow(row: DatabaseRow): WorkerRecord {
459+ return {
460+ workerId: readRequiredString(row, "worker_id"),
461+ controllerId: readRequiredString(row, "controller_id"),
462+ host: readRequiredString(row, "host"),
463+ workerType: readRequiredString(row, "worker_type"),
464+ status: readRequiredString(row, "status"),
465+ maxParallelism: readRequiredNumber(row, "max_parallelism"),
466+ currentLoad: readRequiredNumber(row, "current_load"),
467+ lastHeartbeatAt: readRequiredNumber(row, "last_heartbeat_at"),
468+ capabilitiesJson: readOptionalString(row, "capabilities_json"),
469+ metadataJson: readOptionalString(row, "metadata_json")
470+ };
471+}
472+
473+export function mapTaskRow(row: DatabaseRow): TaskRecord {
474+ return {
475+ taskId: readRequiredString(row, "task_id"),
476+ repo: readRequiredString(row, "repo"),
477+ taskType: readRequiredString(row, "task_type"),
478+ title: readRequiredString(row, "title"),
479+ goal: readRequiredString(row, "goal"),
480+ source: readRequiredString(row, "source"),
481+ priority: readRequiredNumber(row, "priority"),
482+ status: readTaskStatus(row, "status"),
483+ planningStrategy: readOptionalString(row, "planning_strategy"),
484+ plannerProvider: readOptionalString(row, "planner_provider"),
485+ branchName: readOptionalString(row, "branch_name"),
486+ baseRef: readOptionalString(row, "base_ref"),
487+ targetHost: readOptionalString(row, "target_host"),
488+ assignedControllerId: readOptionalString(row, "assigned_controller_id"),
489+ currentStepIndex: readRequiredNumber(row, "current_step_index"),
490+ constraintsJson: readOptionalString(row, "constraints_json"),
491+ acceptanceJson: readOptionalString(row, "acceptance_json"),
492+ metadataJson: readOptionalString(row, "metadata_json"),
493+ resultSummary: readOptionalString(row, "result_summary"),
494+ resultJson: readOptionalString(row, "result_json"),
495+ errorText: readOptionalString(row, "error_text"),
496+ createdAt: readRequiredNumber(row, "created_at"),
497+ updatedAt: readRequiredNumber(row, "updated_at"),
498+ startedAt: readOptionalNumber(row, "started_at"),
499+ finishedAt: readOptionalNumber(row, "finished_at")
500+ };
501+}
502+
503+export function mapTaskStepRow(row: DatabaseRow): TaskStepRecord {
504+ return {
505+ stepId: readRequiredString(row, "step_id"),
506+ taskId: readRequiredString(row, "task_id"),
507+ stepIndex: readRequiredNumber(row, "step_index"),
508+ stepName: readRequiredString(row, "step_name"),
509+ stepKind: readStepKind(row, "step_kind"),
510+ status: readStepStatus(row, "status"),
511+ assignedWorkerId: readOptionalString(row, "assigned_worker_id"),
512+ assignedControllerId: readOptionalString(row, "assigned_controller_id"),
513+ timeoutSec: readRequiredNumber(row, "timeout_sec"),
514+ retryLimit: readRequiredNumber(row, "retry_limit"),
515+ retryCount: readRequiredNumber(row, "retry_count"),
516+ leaseExpiresAt: readOptionalNumber(row, "lease_expires_at"),
517+ inputJson: readOptionalString(row, "input_json"),
518+ outputJson: readOptionalString(row, "output_json"),
519+ summary: readOptionalString(row, "summary"),
520+ errorText: readOptionalString(row, "error_text"),
521+ createdAt: readRequiredNumber(row, "created_at"),
522+ updatedAt: readRequiredNumber(row, "updated_at"),
523+ startedAt: readOptionalNumber(row, "started_at"),
524+ finishedAt: readOptionalNumber(row, "finished_at")
525+ };
526+}
527+
528+export function mapTaskRunRow(row: DatabaseRow): TaskRunRecord {
529+ return {
530+ runId: readRequiredString(row, "run_id"),
531+ taskId: readRequiredString(row, "task_id"),
532+ stepId: readRequiredString(row, "step_id"),
533+ workerId: readRequiredString(row, "worker_id"),
534+ controllerId: readRequiredString(row, "controller_id"),
535+ host: readRequiredString(row, "host"),
536+ pid: readOptionalNumber(row, "pid"),
537+ status: readRequiredString(row, "status"),
538+ leaseExpiresAt: readOptionalNumber(row, "lease_expires_at"),
539+ heartbeatAt: readOptionalNumber(row, "heartbeat_at"),
540+ logDir: readRequiredString(row, "log_dir"),
541+ stdoutPath: readOptionalString(row, "stdout_path"),
542+ stderrPath: readOptionalString(row, "stderr_path"),
543+ workerLogPath: readOptionalString(row, "worker_log_path"),
544+ checkpointSeq: readRequiredNumber(row, "checkpoint_seq"),
545+ exitCode: readOptionalNumber(row, "exit_code"),
546+ resultJson: readOptionalString(row, "result_json"),
547+ errorText: readOptionalString(row, "error_text"),
548+ createdAt: readRequiredNumber(row, "created_at"),
549+ startedAt: readOptionalNumber(row, "started_at"),
550+ finishedAt: readOptionalNumber(row, "finished_at")
551+ };
552+}
553+
554+export function mapTaskCheckpointRow(row: DatabaseRow): TaskCheckpointRecord {
555+ return {
556+ checkpointId: readRequiredString(row, "checkpoint_id"),
557+ taskId: readRequiredString(row, "task_id"),
558+ stepId: readRequiredString(row, "step_id"),
559+ runId: readRequiredString(row, "run_id"),
560+ seq: readRequiredNumber(row, "seq"),
561+ checkpointType: readRequiredString(row, "checkpoint_type"),
562+ summary: readOptionalString(row, "summary"),
563+ contentText: readOptionalString(row, "content_text"),
564+ contentJson: readOptionalString(row, "content_json"),
565+ createdAt: readRequiredNumber(row, "created_at")
566+ };
567+}
568+
569+export function mapTaskLogRow(row: DatabaseRow): TaskLogRecord {
570+ return {
571+ logId: readRequiredNumber(row, "log_id"),
572+ taskId: readRequiredString(row, "task_id"),
573+ stepId: readOptionalString(row, "step_id"),
574+ runId: readRequiredString(row, "run_id"),
575+ seq: readRequiredNumber(row, "seq"),
576+ stream: readRequiredString(row, "stream"),
577+ level: readOptionalString(row, "level"),
578+ message: readRequiredString(row, "message"),
579+ createdAt: readRequiredNumber(row, "created_at")
580+ };
581+}
582+
583+export function mapSystemStateRow(row: DatabaseRow): SystemStateRecord {
584+ return {
585+ stateKey: readRequiredString(row, "state_key"),
586+ valueJson: readRequiredString(row, "value_json"),
587+ updatedAt: readRequiredNumber(row, "updated_at")
588+ };
589+}
590+
591+export function mapAutomationStateRow(row: DatabaseRow): AutomationStateRecord {
592+ const record = mapSystemStateRow(row);
593+
594+ if (record.stateKey !== AUTOMATION_STATE_KEY) {
595+ throw new TypeError(`Expected state_key "${AUTOMATION_STATE_KEY}", received "${record.stateKey}".`);
596+ }
597+
598+ const parsed = parseJsonText<{ mode?: unknown }>(record.valueJson);
599+ const mode = parsed?.mode;
600+
601+ if (!isAutomationMode(mode)) {
602+ throw new TypeError(`Automation state is missing a valid mode in "${record.valueJson}".`);
603+ }
604+
605+ return {
606+ ...record,
607+ stateKey: AUTOMATION_STATE_KEY,
608+ mode
609+ };
610+}
611+
612+export function mapTaskArtifactRow(row: DatabaseRow): TaskArtifactRecord {
613+ return {
614+ artifactId: readRequiredString(row, "artifact_id"),
615+ taskId: readRequiredString(row, "task_id"),
616+ stepId: readOptionalString(row, "step_id"),
617+ runId: readOptionalString(row, "run_id"),
618+ artifactType: readRequiredString(row, "artifact_type"),
619+ path: readOptionalString(row, "path"),
620+ uri: readOptionalString(row, "uri"),
621+ sizeBytes: readOptionalNumber(row, "size_bytes"),
622+ sha256: readOptionalString(row, "sha256"),
623+ metadataJson: readOptionalString(row, "metadata_json"),
624+ createdAt: readRequiredNumber(row, "created_at")
625+ };
626+}
627+
628+export const SELECT_CURRENT_LEASE_SQL = `
629+ SELECT
630+ lease_name,
631+ holder_id,
632+ holder_host,
633+ term,
634+ lease_expires_at,
635+ renewed_at,
636+ preferred_holder_id,
637+ metadata_json
638+ FROM leader_lease
639+ WHERE lease_name = ?
640+`;
641+
642+export const UPSERT_LEADER_LEASE_SQL = `
643+ INSERT INTO leader_lease (
644+ lease_name,
645+ holder_id,
646+ holder_host,
647+ term,
648+ lease_expires_at,
649+ renewed_at,
650+ preferred_holder_id,
651+ metadata_json
652+ )
653+ VALUES (?, ?, ?, ?, ?, ?, ?, ?)
654+ ON CONFLICT(lease_name) DO UPDATE SET
655+ holder_id = excluded.holder_id,
656+ holder_host = excluded.holder_host,
657+ term = excluded.term,
658+ lease_expires_at = excluded.lease_expires_at,
659+ renewed_at = excluded.renewed_at,
660+ preferred_holder_id = excluded.preferred_holder_id,
661+ metadata_json = excluded.metadata_json
662+`;
663+
664+export const UPSERT_CONTROLLER_SQL = `
665+ INSERT INTO controllers (
666+ controller_id,
667+ host,
668+ role,
669+ priority,
670+ status,
671+ version,
672+ last_heartbeat_at,
673+ last_started_at,
674+ metadata_json
675+ )
676+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
677+ ON CONFLICT(controller_id) DO UPDATE SET
678+ host = excluded.host,
679+ role = excluded.role,
680+ priority = excluded.priority,
681+ status = excluded.status,
682+ version = excluded.version,
683+ last_heartbeat_at = excluded.last_heartbeat_at,
684+ last_started_at = excluded.last_started_at,
685+ metadata_json = excluded.metadata_json
686+`;
687+
688+export const UPSERT_WORKER_SQL = `
689+ INSERT INTO workers (
690+ worker_id,
691+ controller_id,
692+ host,
693+ worker_type,
694+ status,
695+ max_parallelism,
696+ current_load,
697+ last_heartbeat_at,
698+ capabilities_json,
699+ metadata_json
700+ )
701+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
702+ ON CONFLICT(worker_id) DO UPDATE SET
703+ controller_id = excluded.controller_id,
704+ host = excluded.host,
705+ worker_type = excluded.worker_type,
706+ status = excluded.status,
707+ max_parallelism = excluded.max_parallelism,
708+ current_load = excluded.current_load,
709+ last_heartbeat_at = excluded.last_heartbeat_at,
710+ capabilities_json = excluded.capabilities_json,
711+ metadata_json = excluded.metadata_json
712+`;
713+
714+export const UPSERT_SYSTEM_STATE_SQL = `
715+ INSERT INTO system_state (
716+ state_key,
717+ value_json,
718+ updated_at
719+ )
720+ VALUES (?, ?, ?)
721+ ON CONFLICT(state_key) DO UPDATE SET
722+ value_json = excluded.value_json,
723+ updated_at = excluded.updated_at
724+`;
725+
726+export const ENSURE_AUTOMATION_STATE_SQL = `
727+ INSERT INTO system_state (
728+ state_key,
729+ value_json,
730+ updated_at
731+ )
732+ VALUES (?, ?, ?)
733+ ON CONFLICT(state_key) DO NOTHING
734+`;
735+
736+export const SELECT_SYSTEM_STATE_SQL = `
737+ SELECT
738+ state_key,
739+ value_json,
740+ updated_at
741+ FROM system_state
742+ WHERE state_key = ?
743+`;
744+
745+export const INSERT_TASK_SQL = `
746+ INSERT INTO tasks (
747+ task_id,
748+ repo,
749+ task_type,
750+ title,
751+ goal,
752+ source,
753+ priority,
754+ status,
755+ planning_strategy,
756+ planner_provider,
757+ branch_name,
758+ base_ref,
759+ target_host,
760+ assigned_controller_id,
761+ current_step_index,
762+ constraints_json,
763+ acceptance_json,
764+ metadata_json,
765+ result_summary,
766+ result_json,
767+ error_text,
768+ created_at,
769+ updated_at,
770+ started_at,
771+ finished_at
772+ )
773+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
774+`;
775+
776+export const SELECT_TASK_SQL = `
777+ SELECT
778+ task_id,
779+ repo,
780+ task_type,
781+ title,
782+ goal,
783+ source,
784+ priority,
785+ status,
786+ planning_strategy,
787+ planner_provider,
788+ branch_name,
789+ base_ref,
790+ target_host,
791+ assigned_controller_id,
792+ current_step_index,
793+ constraints_json,
794+ acceptance_json,
795+ metadata_json,
796+ result_summary,
797+ result_json,
798+ error_text,
799+ created_at,
800+ updated_at,
801+ started_at,
802+ finished_at
803+ FROM tasks
804+ WHERE task_id = ?
805+`;
806+
807+export const INSERT_TASK_STEP_SQL = `
808+ INSERT INTO task_steps (
809+ step_id,
810+ task_id,
811+ step_index,
812+ step_name,
813+ step_kind,
814+ status,
815+ assigned_worker_id,
816+ assigned_controller_id,
817+ timeout_sec,
818+ retry_limit,
819+ retry_count,
820+ lease_expires_at,
821+ input_json,
822+ output_json,
823+ summary,
824+ error_text,
825+ created_at,
826+ updated_at,
827+ started_at,
828+ finished_at
829+ )
830+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
831+`;
832+
833+export const SELECT_TASK_STEPS_SQL = `
834+ SELECT
835+ step_id,
836+ task_id,
837+ step_index,
838+ step_name,
839+ step_kind,
840+ status,
841+ assigned_worker_id,
842+ assigned_controller_id,
843+ timeout_sec,
844+ retry_limit,
845+ retry_count,
846+ lease_expires_at,
847+ input_json,
848+ output_json,
849+ summary,
850+ error_text,
851+ created_at,
852+ updated_at,
853+ started_at,
854+ finished_at
855+ FROM task_steps
856+ WHERE task_id = ?
857+ ORDER BY step_index ASC
858+`;
859+
860+export const INSERT_TASK_RUN_SQL = `
861+ INSERT INTO task_runs (
862+ run_id,
863+ task_id,
864+ step_id,
865+ worker_id,
866+ controller_id,
867+ host,
868+ pid,
869+ status,
870+ lease_expires_at,
871+ heartbeat_at,
872+ log_dir,
873+ stdout_path,
874+ stderr_path,
875+ worker_log_path,
876+ checkpoint_seq,
877+ exit_code,
878+ result_json,
879+ error_text,
880+ created_at,
881+ started_at,
882+ finished_at
883+ )
884+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
885+`;
886+
887+export const INSERT_TASK_CHECKPOINT_SQL = `
888+ INSERT INTO task_checkpoints (
889+ checkpoint_id,
890+ task_id,
891+ step_id,
892+ run_id,
893+ seq,
894+ checkpoint_type,
895+ summary,
896+ content_text,
897+ content_json,
898+ created_at
899+ )
900+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
901+`;
902+
903+export const INSERT_TASK_LOG_SQL = `
904+ INSERT INTO task_logs (
905+ task_id,
906+ step_id,
907+ run_id,
908+ seq,
909+ stream,
910+ level,
911+ message,
912+ created_at
913+ )
914+ VALUES (?, ?, ?, ?, ?, ?, ?, ?)
915+`;
916+
917+export const INSERT_TASK_ARTIFACT_SQL = `
918+ INSERT INTO task_artifacts (
919+ artifact_id,
920+ task_id,
921+ step_id,
922+ run_id,
923+ artifact_type,
924+ path,
925+ uri,
926+ size_bytes,
927+ sha256,
928+ metadata_json,
929+ created_at
930+ )
931+ VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
932+`;
933+
934+function leaderLeaseParams(record: LeaderLeaseRecord): D1Bindable[] {
935+ return [
936+ record.leaseName,
937+ record.holderId,
938+ record.holderHost,
939+ record.term,
940+ record.leaseExpiresAt,
941+ record.renewedAt,
942+ record.preferredHolderId,
943+ record.metadataJson
944+ ];
945+}
946+
947+function controllerParams(record: ControllerRecord): D1Bindable[] {
948+ return [
949+ record.controllerId,
950+ record.host,
951+ record.role,
952+ record.priority,
953+ record.status,
954+ record.version,
955+ record.lastHeartbeatAt,
956+ record.lastStartedAt,
957+ record.metadataJson
958+ ];
959+}
960+
961+function workerParams(record: WorkerRecord): D1Bindable[] {
962+ return [
963+ record.workerId,
964+ record.controllerId,
965+ record.host,
966+ record.workerType,
967+ record.status,
968+ record.maxParallelism,
969+ record.currentLoad,
970+ record.lastHeartbeatAt,
971+ record.capabilitiesJson,
972+ record.metadataJson
973+ ];
974+}
975+
976+function systemStateParams(record: SystemStateRecord): D1Bindable[] {
977+ return [record.stateKey, record.valueJson, record.updatedAt];
978+}
979+
980+function taskParams(record: TaskRecord): D1Bindable[] {
981+ return [
982+ record.taskId,
983+ record.repo,
984+ record.taskType,
985+ record.title,
986+ record.goal,
987+ record.source,
988+ record.priority,
989+ record.status,
990+ record.planningStrategy,
991+ record.plannerProvider,
992+ record.branchName,
993+ record.baseRef,
994+ record.targetHost,
995+ record.assignedControllerId,
996+ record.currentStepIndex,
997+ record.constraintsJson,
998+ record.acceptanceJson,
999+ record.metadataJson,
1000+ record.resultSummary,
1001+ record.resultJson,
1002+ record.errorText,
1003+ record.createdAt,
1004+ record.updatedAt,
1005+ record.startedAt,
1006+ record.finishedAt
1007+ ];
1008+}
1009+
1010+function taskStepParams(record: TaskStepRecord): D1Bindable[] {
1011+ return [
1012+ record.stepId,
1013+ record.taskId,
1014+ record.stepIndex,
1015+ record.stepName,
1016+ record.stepKind,
1017+ record.status,
1018+ record.assignedWorkerId,
1019+ record.assignedControllerId,
1020+ record.timeoutSec,
1021+ record.retryLimit,
1022+ record.retryCount,
1023+ record.leaseExpiresAt,
1024+ record.inputJson,
1025+ record.outputJson,
1026+ record.summary,
1027+ record.errorText,
1028+ record.createdAt,
1029+ record.updatedAt,
1030+ record.startedAt,
1031+ record.finishedAt
1032+ ];
1033+}
1034+
1035+function taskRunParams(record: TaskRunRecord): D1Bindable[] {
1036+ return [
1037+ record.runId,
1038+ record.taskId,
1039+ record.stepId,
1040+ record.workerId,
1041+ record.controllerId,
1042+ record.host,
1043+ record.pid,
1044+ record.status,
1045+ record.leaseExpiresAt,
1046+ record.heartbeatAt,
1047+ record.logDir,
1048+ record.stdoutPath,
1049+ record.stderrPath,
1050+ record.workerLogPath,
1051+ record.checkpointSeq,
1052+ record.exitCode,
1053+ record.resultJson,
1054+ record.errorText,
1055+ record.createdAt,
1056+ record.startedAt,
1057+ record.finishedAt
1058+ ];
1059+}
1060+
1061+function taskCheckpointParams(record: TaskCheckpointRecord): D1Bindable[] {
1062+ return [
1063+ record.checkpointId,
1064+ record.taskId,
1065+ record.stepId,
1066+ record.runId,
1067+ record.seq,
1068+ record.checkpointType,
1069+ record.summary,
1070+ record.contentText,
1071+ record.contentJson,
1072+ record.createdAt
1073+ ];
1074+}
1075+
1076+function taskLogParams(record: NewTaskLogRecord): D1Bindable[] {
1077+ return [
1078+ record.taskId,
1079+ record.stepId,
1080+ record.runId,
1081+ record.seq,
1082+ record.stream,
1083+ record.level,
1084+ record.message,
1085+ record.createdAt
1086+ ];
1087+}
1088+
1089+function taskArtifactParams(record: TaskArtifactRecord): D1Bindable[] {
1090+ return [
1091+ record.artifactId,
1092+ record.taskId,
1093+ record.stepId,
1094+ record.runId,
1095+ record.artifactType,
1096+ record.path,
1097+ record.uri,
1098+ record.sizeBytes,
1099+ record.sha256,
1100+ record.metadataJson,
1101+ record.createdAt
1102+ ];
1103+}
1104+
1105+export class D1ControlPlaneRepository implements ControlPlaneRepository {
1106+ constructor(private readonly db: D1DatabaseLike) {}
1107+
1108+ async ensureAutomationState(mode: AutomationMode = DEFAULT_AUTOMATION_MODE): Promise<void> {
1109+ await this.run(ENSURE_AUTOMATION_STATE_SQL, [
1110+ AUTOMATION_STATE_KEY,
1111+ buildAutomationStateValue(mode),
1112+ nowUnixSeconds()
1113+ ]);
1114+ }
1115+
1116+ async getAutomationState(): Promise<AutomationStateRecord | null> {
1117+ const row = await this.fetchFirst(SELECT_SYSTEM_STATE_SQL, [AUTOMATION_STATE_KEY]);
1118+ return row == null ? null : mapAutomationStateRow(row);
1119+ }
1120+
1121+ async getCurrentLease(): Promise<LeaderLeaseRecord | null> {
1122+ const row = await this.fetchFirst(SELECT_CURRENT_LEASE_SQL, [GLOBAL_LEASE_NAME]);
1123+ return row == null ? null : mapLeaderLeaseRow(row);
1124+ }
1125+
1126+ async getSystemState(stateKey: string): Promise<SystemStateRecord | null> {
1127+ const row = await this.fetchFirst(SELECT_SYSTEM_STATE_SQL, [stateKey]);
1128+ return row == null ? null : mapSystemStateRow(row);
1129+ }
1130+
1131+ async getTask(taskId: string): Promise<TaskRecord | null> {
1132+ const row = await this.fetchFirst(SELECT_TASK_SQL, [taskId]);
1133+ return row == null ? null : mapTaskRow(row);
1134+ }
1135+
1136+ async insertTask(record: TaskRecord): Promise<void> {
1137+ await this.run(INSERT_TASK_SQL, taskParams(record));
1138+ }
1139+
1140+ async insertTaskArtifact(record: TaskArtifactRecord): Promise<void> {
1141+ await this.run(INSERT_TASK_ARTIFACT_SQL, taskArtifactParams(record));
1142+ }
1143+
1144+ async insertTaskCheckpoint(record: TaskCheckpointRecord): Promise<void> {
1145+ await this.run(INSERT_TASK_CHECKPOINT_SQL, taskCheckpointParams(record));
1146+ }
1147+
1148+ async insertTaskRun(record: TaskRunRecord): Promise<void> {
1149+ await this.run(INSERT_TASK_RUN_SQL, taskRunParams(record));
1150+ }
1151+
1152+ async insertTaskStep(record: TaskStepRecord): Promise<void> {
1153+ await this.run(INSERT_TASK_STEP_SQL, taskStepParams(record));
1154+ }
1155+
1156+ async insertTaskSteps(records: TaskStepRecord[]): Promise<void> {
1157+ if (records.length === 0) {
1158+ return;
1159+ }
1160+
1161+ await this.db.batch(records.map((record) => this.bind(INSERT_TASK_STEP_SQL, taskStepParams(record))));
1162+ }
1163+
1164+ async listTaskSteps(taskId: string): Promise<TaskStepRecord[]> {
1165+ const rows = await this.fetchAll(SELECT_TASK_STEPS_SQL, [taskId]);
1166+ return rows.map(mapTaskStepRow);
1167+ }
1168+
1169+ async putLeaderLease(record: LeaderLeaseRecord): Promise<void> {
1170+ if (record.leaseName !== GLOBAL_LEASE_NAME) {
1171+ throw new RangeError(`leader_lease only supports lease_name="${GLOBAL_LEASE_NAME}".`);
1172+ }
1173+
1174+ await this.run(UPSERT_LEADER_LEASE_SQL, leaderLeaseParams(record));
1175+ }
1176+
1177+ async putSystemState(record: SystemStateRecord): Promise<void> {
1178+ await this.run(UPSERT_SYSTEM_STATE_SQL, systemStateParams(record));
1179+ }
1180+
1181+ async setAutomationMode(
1182+ mode: AutomationMode,
1183+ updatedAt: number = nowUnixSeconds()
1184+ ): Promise<void> {
1185+ await this.run(UPSERT_SYSTEM_STATE_SQL, [
1186+ AUTOMATION_STATE_KEY,
1187+ buildAutomationStateValue(mode),
1188+ updatedAt
1189+ ]);
1190+ }
1191+
1192+ async upsertController(record: ControllerRecord): Promise<void> {
1193+ await this.run(UPSERT_CONTROLLER_SQL, controllerParams(record));
1194+ }
1195+
1196+ async upsertWorker(record: WorkerRecord): Promise<void> {
1197+ await this.run(UPSERT_WORKER_SQL, workerParams(record));
1198+ }
1199+
1200+ async appendTaskLog(record: NewTaskLogRecord): Promise<number | null> {
1201+ const result = await this.run(INSERT_TASK_LOG_SQL, taskLogParams(record));
1202+ return result.meta.last_row_id ?? null;
1203+ }
1204+
1205+ private bind(query: string, params: readonly (D1Bindable | undefined)[]): D1PreparedStatementLike {
1206+ return this.db.prepare(query).bind(...params.map(toD1Bindable));
1207+ }
1208+
1209+ private async fetchAll(
1210+ query: string,
1211+ params: readonly (D1Bindable | undefined)[] = []
1212+ ): Promise<DatabaseRow[]> {
1213+ const result = await this.bind(query, params).all<DatabaseRow>();
1214+ return result.results ?? [];
1215+ }
1216+
1217+ private async fetchFirst(
1218+ query: string,
1219+ params: readonly (D1Bindable | undefined)[] = []
1220+ ): Promise<DatabaseRow | null> {
1221+ return this.bind(query, params).first<DatabaseRow>();
1222+ }
1223+
1224+ private async run(
1225+ query: string,
1226+ params: readonly (D1Bindable | undefined)[] = []
1227+ ): Promise<D1QueryResult<never>> {
1228+ return this.bind(query, params).run();
1229+ }
1230+}
1231+
1232+export function createD1ControlPlaneRepository(db: D1DatabaseLike): ControlPlaneRepository {
1233+ return new D1ControlPlaneRepository(db);
1234+}