codex@macbookpro
·
2026-03-31
index.ts
1import { mkdirSync } from "node:fs";
2import { dirname } from "node:path";
3import { DatabaseSync } from "node:sqlite";
4
5export const D1_TABLES = [
6 "leader_lease",
7 "controllers",
8 "workers",
9 "tasks",
10 "task_steps",
11 "task_runs",
12 "task_checkpoints",
13 "task_logs",
14 "system_state",
15 "task_artifacts",
16 "browser_login_states",
17 "browser_endpoint_metadata",
18 "baa_message_dedupes",
19 "baa_instruction_dedupes",
20 "baa_execution_journal"
21] as const;
22
23export type D1TableName = (typeof D1_TABLES)[number];
24
25export const GLOBAL_LEASE_NAME = "global";
26export const AUTOMATION_STATE_KEY = "automation";
27export const DEFAULT_AUTOMATION_MODE = "running";
28export const DEFAULT_LEASE_TTL_SEC = 30;
29export const DEFAULT_LEASE_RENEW_INTERVAL_SEC = 5;
30export const DEFAULT_LEASE_RENEW_FAILURE_THRESHOLD = 2;
31export const DEFAULT_BAA_MESSAGE_DEDUPE_LIMIT = 2_048;
32export const DEFAULT_BAA_INSTRUCTION_DEDUPE_LIMIT = 8_192;
33export const DEFAULT_BAA_EXECUTION_JOURNAL_LIMIT = 50;
34
35export const AUTOMATION_MODE_VALUES = ["running", "draining", "paused"] as const;
36export const TASK_STATUS_VALUES = [
37 "queued",
38 "planning",
39 "running",
40 "paused",
41 "done",
42 "failed",
43 "canceled"
44] as const;
45export const STEP_STATUS_VALUES = ["pending", "running", "done", "failed", "timeout"] as const;
46export const STEP_KIND_VALUES = ["planner", "codex", "shell", "git", "review", "finalize"] as const;
47export const BROWSER_LOGIN_STATE_STATUS_VALUES = ["fresh", "stale", "lost"] as const;
48export const BAA_EXECUTION_JOURNAL_KIND_VALUES = ["ingest", "execute"] as const;
49
50export type AutomationMode = (typeof AUTOMATION_MODE_VALUES)[number];
51export type TaskStatus = (typeof TASK_STATUS_VALUES)[number];
52export type StepStatus = (typeof STEP_STATUS_VALUES)[number];
53export type StepKind = (typeof STEP_KIND_VALUES)[number];
54export type BrowserLoginStateStatus = (typeof BROWSER_LOGIN_STATE_STATUS_VALUES)[number];
55export type BaaExecutionJournalKind = (typeof BAA_EXECUTION_JOURNAL_KIND_VALUES)[number];
56
57export type JsonPrimitive = boolean | number | null | string;
58export type JsonValue = JsonPrimitive | JsonObject | JsonValue[];
59
60export interface JsonObject {
61 [key: string]: JsonValue;
62}
63
64export type D1Bindable = number | null | string;
65
66export interface DatabaseRow {
67 [column: string]: unknown;
68}
69
70export interface D1ResultMeta {
71 changed_db?: boolean;
72 changes?: number;
73 duration?: number;
74 last_row_id?: number;
75 rows_read?: number;
76 rows_written?: number;
77 served_by?: string;
78 size_after?: number;
79}
80
81export interface D1QueryResult<T = never> {
82 meta: D1ResultMeta;
83 results?: T[];
84 success: boolean;
85}
86
87export interface D1ExecResult {
88 count?: number;
89 duration?: number;
90}
91
92export interface D1PreparedStatementLike {
93 all<T = DatabaseRow>(): Promise<D1QueryResult<T>>;
94 bind(...values: D1Bindable[]): D1PreparedStatementLike;
95 first<T = DatabaseRow>(columnName?: string): Promise<T | null>;
96 raw<T = unknown[]>(options?: { columnNames?: boolean }): Promise<T[]>;
97 run(): Promise<D1QueryResult<never>>;
98}
99
100export interface D1DatabaseLike {
101 batch<T = never>(statements: D1PreparedStatementLike[]): Promise<Array<D1QueryResult<T>>>;
102 exec(query: string): Promise<D1ExecResult>;
103 prepare(query: string): D1PreparedStatementLike;
104}
105
106export interface LeaderLeaseRecord {
107 leaseName: string;
108 holderId: string;
109 holderHost: string;
110 term: number;
111 leaseExpiresAt: number;
112 renewedAt: number;
113 preferredHolderId: string | null;
114 metadataJson: string | null;
115}
116
117export interface ControllerRecord {
118 controllerId: string;
119 host: string;
120 role: string;
121 priority: number;
122 status: string;
123 version: string | null;
124 lastHeartbeatAt: number;
125 lastStartedAt: number | null;
126 metadataJson: string | null;
127}
128
129export type LeaderLeaseOperation = "acquire" | "renew";
130
131export interface ControllerHeartbeatInput {
132 controllerId: string;
133 host: string;
134 role: string;
135 priority: number;
136 status: string;
137 version?: string | null;
138 heartbeatAt?: number;
139 startedAt?: number | null;
140 metadataJson?: string | null;
141}
142
143export interface LeaderLeaseAcquireInput {
144 controllerId: string;
145 host: string;
146 ttlSec: number;
147 preferred?: boolean;
148 metadataJson?: string | null;
149 now?: number;
150}
151
152export interface LeaderLeaseAcquireResult {
153 holderId: string;
154 holderHost: string;
155 term: number;
156 leaseExpiresAt: number;
157 renewedAt: number;
158 isLeader: boolean;
159 operation: LeaderLeaseOperation;
160 lease: LeaderLeaseRecord;
161}
162
163export interface WorkerRecord {
164 workerId: string;
165 controllerId: string;
166 host: string;
167 workerType: string;
168 status: string;
169 maxParallelism: number;
170 currentLoad: number;
171 lastHeartbeatAt: number;
172 capabilitiesJson: string | null;
173 metadataJson: string | null;
174}
175
176export interface TaskRecord {
177 taskId: string;
178 repo: string;
179 taskType: string;
180 title: string;
181 goal: string;
182 source: string;
183 priority: number;
184 status: TaskStatus;
185 planningStrategy: string | null;
186 plannerProvider: string | null;
187 branchName: string | null;
188 baseRef: string | null;
189 targetHost: string | null;
190 assignedControllerId: string | null;
191 currentStepIndex: number;
192 constraintsJson: string | null;
193 acceptanceJson: string | null;
194 metadataJson: string | null;
195 resultSummary: string | null;
196 resultJson: string | null;
197 errorText: string | null;
198 createdAt: number;
199 updatedAt: number;
200 startedAt: number | null;
201 finishedAt: number | null;
202}
203
204export interface TaskStepRecord {
205 stepId: string;
206 taskId: string;
207 stepIndex: number;
208 stepName: string;
209 stepKind: StepKind;
210 status: StepStatus;
211 assignedWorkerId: string | null;
212 assignedControllerId: string | null;
213 timeoutSec: number;
214 retryLimit: number;
215 retryCount: number;
216 leaseExpiresAt: number | null;
217 inputJson: string | null;
218 outputJson: string | null;
219 summary: string | null;
220 errorText: string | null;
221 createdAt: number;
222 updatedAt: number;
223 startedAt: number | null;
224 finishedAt: number | null;
225}
226
227export interface TaskRunRecord {
228 runId: string;
229 taskId: string;
230 stepId: string;
231 workerId: string;
232 controllerId: string;
233 host: string;
234 pid: number | null;
235 status: string;
236 leaseExpiresAt: number | null;
237 heartbeatAt: number | null;
238 logDir: string;
239 stdoutPath: string | null;
240 stderrPath: string | null;
241 workerLogPath: string | null;
242 checkpointSeq: number;
243 exitCode: number | null;
244 resultJson: string | null;
245 errorText: string | null;
246 createdAt: number;
247 startedAt: number | null;
248 finishedAt: number | null;
249}
250
251export interface TaskCheckpointRecord {
252 checkpointId: string;
253 taskId: string;
254 stepId: string;
255 runId: string;
256 seq: number;
257 checkpointType: string;
258 summary: string | null;
259 contentText: string | null;
260 contentJson: string | null;
261 createdAt: number;
262}
263
264export interface TaskLogRecord {
265 logId: number;
266 taskId: string;
267 stepId: string | null;
268 runId: string;
269 seq: number;
270 stream: string;
271 level: string | null;
272 message: string;
273 createdAt: number;
274}
275
276export interface NewTaskLogRecord {
277 taskId: string;
278 stepId: string | null;
279 runId: string;
280 seq: number;
281 stream: string;
282 level: string | null;
283 message: string;
284 createdAt: number;
285}
286
287export interface SystemStateRecord {
288 stateKey: string;
289 valueJson: string;
290 updatedAt: number;
291}
292
293export interface AutomationStateRecord extends SystemStateRecord {
294 stateKey: typeof AUTOMATION_STATE_KEY;
295 mode: AutomationMode;
296}
297
298export interface TaskArtifactRecord {
299 artifactId: string;
300 taskId: string;
301 stepId: string | null;
302 runId: string | null;
303 artifactType: string;
304 path: string | null;
305 uri: string | null;
306 sizeBytes: number | null;
307 sha256: string | null;
308 metadataJson: string | null;
309 createdAt: number;
310}
311
312export interface BrowserSessionKey {
313 platform: string;
314 clientId: string;
315 account: string;
316}
317
318export interface BrowserLoginStateRecord extends BrowserSessionKey {
319 host: string;
320 browser: string;
321 credentialFingerprint: string;
322 capturedAt: number;
323 lastSeenAt: number;
324 status: BrowserLoginStateStatus;
325}
326
327export interface BrowserEndpointMetadataRecord extends BrowserSessionKey {
328 endpoints: string[];
329 updatedAt: number;
330 lastVerifiedAt: number | null;
331}
332
333export interface NewBaaMessageDedupeRecord {
334 assistantMessageId: string;
335 conversationId: string | null;
336 createdAt: number;
337 dedupeKey: string;
338 observedAt: number | null;
339 platform: string;
340}
341
342export interface NewBaaInstructionDedupeRecord {
343 assistantMessageId: string;
344 conversationId: string | null;
345 createdAt: number;
346 dedupeKey: string;
347 instructionId: string;
348 platform: string;
349 target: string;
350 tool: string;
351}
352
353export interface NewBaaExecutionJournalRecord {
354 assistantMessageId: string;
355 conversationId: string | null;
356 ingestedAt: number;
357 kind: BaaExecutionJournalKind;
358 messageDedupeKey: string;
359 observedAt: number | null;
360 platform: string;
361 source: string;
362 status: string;
363 summaryJson: string;
364}
365
366export interface BaaExecutionJournalRecord extends NewBaaExecutionJournalRecord {
367 journalId: number;
368}
369
370export interface ListControllersOptions {
371 limit?: number;
372}
373
374export interface ListTasksOptions {
375 limit?: number;
376 status?: TaskStatus;
377}
378
379export interface ListRunsOptions {
380 limit?: number;
381}
382
383export interface ListTaskLogsOptions {
384 limit?: number;
385 runId?: string;
386}
387
388export interface ListBrowserLoginStatesOptions {
389 account?: string;
390 browser?: string;
391 clientId?: string;
392 host?: string;
393 limit?: number;
394 platform?: string;
395 status?: BrowserLoginStateStatus;
396}
397
398export interface ListBrowserEndpointMetadataOptions {
399 account?: string;
400 clientId?: string;
401 limit?: number;
402 platform?: string;
403}
404
405export interface ListBaaExecutionJournalOptions {
406 kind?: BaaExecutionJournalKind;
407 limit?: number;
408}
409
410export interface D1ControlPlaneRepositoryOptions {
411 baaExecutionJournalLimit?: number;
412 baaInstructionDedupeLimit?: number;
413 baaMessageDedupeLimit?: number;
414}
415
416export interface ControlPlaneRepository {
417 appendBaaExecutionJournal(record: NewBaaExecutionJournalRecord): Promise<number | null>;
418 appendTaskLog(record: NewTaskLogRecord): Promise<number | null>;
419 countActiveRuns(): Promise<number>;
420 countQueuedTasks(): Promise<number>;
421 heartbeatController(input: ControllerHeartbeatInput): Promise<ControllerRecord>;
422 ensureAutomationState(mode?: AutomationMode): Promise<void>;
423 acquireLeaderLease(input: LeaderLeaseAcquireInput): Promise<LeaderLeaseAcquireResult>;
424 getAutomationState(): Promise<AutomationStateRecord | null>;
425 getBrowserEndpointMetadata(key: BrowserSessionKey): Promise<BrowserEndpointMetadataRecord | null>;
426 getBrowserLoginState(key: BrowserSessionKey): Promise<BrowserLoginStateRecord | null>;
427 getController(controllerId: string): Promise<ControllerRecord | null>;
428 getCurrentLease(): Promise<LeaderLeaseRecord | null>;
429 getRun(runId: string): Promise<TaskRunRecord | null>;
430 getSystemState(stateKey: string): Promise<SystemStateRecord | null>;
431 getTask(taskId: string): Promise<TaskRecord | null>;
432 hasBaaInstructionDedupe(dedupeKey: string): Promise<boolean>;
433 hasBaaMessageDedupe(dedupeKey: string): Promise<boolean>;
434 insertTask(record: TaskRecord): Promise<void>;
435 insertTaskArtifact(record: TaskArtifactRecord): Promise<void>;
436 insertTaskCheckpoint(record: TaskCheckpointRecord): Promise<void>;
437 insertTaskRun(record: TaskRunRecord): Promise<void>;
438 insertTaskStep(record: TaskStepRecord): Promise<void>;
439 insertTaskSteps(records: TaskStepRecord[]): Promise<void>;
440 listBaaExecutionJournal(options?: ListBaaExecutionJournalOptions): Promise<BaaExecutionJournalRecord[]>;
441 listBrowserEndpointMetadata(
442 options?: ListBrowserEndpointMetadataOptions
443 ): Promise<BrowserEndpointMetadataRecord[]>;
444 listBrowserLoginStates(options?: ListBrowserLoginStatesOptions): Promise<BrowserLoginStateRecord[]>;
445 listControllers(options?: ListControllersOptions): Promise<ControllerRecord[]>;
446 listTaskLogs(taskId: string, options?: ListTaskLogsOptions): Promise<TaskLogRecord[]>;
447 listRuns(options?: ListRunsOptions): Promise<TaskRunRecord[]>;
448 listTasks(options?: ListTasksOptions): Promise<TaskRecord[]>;
449 listTaskSteps(taskId: string): Promise<TaskStepRecord[]>;
450 markBrowserLoginStatesLost(lastSeenBeforeOrAt: number): Promise<number>;
451 markBrowserLoginStatesStale(lastSeenBeforeOrAt: number): Promise<number>;
452 putLeaderLease(record: LeaderLeaseRecord): Promise<void>;
453 putBaaInstructionDedupe(record: NewBaaInstructionDedupeRecord): Promise<void>;
454 putBaaMessageDedupe(record: NewBaaMessageDedupeRecord): Promise<void>;
455 putSystemState(record: SystemStateRecord): Promise<void>;
456 setAutomationMode(mode: AutomationMode, updatedAt?: number): Promise<void>;
457 upsertBrowserEndpointMetadata(record: BrowserEndpointMetadataRecord): Promise<void>;
458 upsertBrowserLoginState(record: BrowserLoginStateRecord): Promise<void>;
459 upsertController(record: ControllerRecord): Promise<void>;
460 upsertWorker(record: WorkerRecord): Promise<void>;
461}
462
463const AUTOMATION_MODE_SET = new Set<string>(AUTOMATION_MODE_VALUES);
464const TASK_STATUS_SET = new Set<string>(TASK_STATUS_VALUES);
465const STEP_STATUS_SET = new Set<string>(STEP_STATUS_VALUES);
466const STEP_KIND_SET = new Set<string>(STEP_KIND_VALUES);
467const BROWSER_LOGIN_STATE_STATUS_SET = new Set<string>(BROWSER_LOGIN_STATE_STATUS_VALUES);
468const BAA_EXECUTION_JOURNAL_KIND_SET = new Set<string>(BAA_EXECUTION_JOURNAL_KIND_VALUES);
469
470export function nowUnixSeconds(date: Date = new Date()): number {
471 return Math.floor(date.getTime() / 1000);
472}
473
474export function nowUnixMilliseconds(date: Date = new Date()): number {
475 return date.getTime();
476}
477
478export function stringifyJson(value: JsonValue | null | undefined): string | null {
479 if (value == null) {
480 return null;
481 }
482
483 return JSON.stringify(value);
484}
485
486export function parseJsonText<T>(jsonText: string | null | undefined): T | null {
487 if (jsonText == null || jsonText === "") {
488 return null;
489 }
490
491 return JSON.parse(jsonText) as T;
492}
493
494export function isAutomationMode(value: unknown): value is AutomationMode {
495 return typeof value === "string" && AUTOMATION_MODE_SET.has(value);
496}
497
498export function isTaskStatus(value: unknown): value is TaskStatus {
499 return typeof value === "string" && TASK_STATUS_SET.has(value);
500}
501
502export function isStepStatus(value: unknown): value is StepStatus {
503 return typeof value === "string" && STEP_STATUS_SET.has(value);
504}
505
506export function isStepKind(value: unknown): value is StepKind {
507 return typeof value === "string" && STEP_KIND_SET.has(value);
508}
509
510export function isBrowserLoginStateStatus(value: unknown): value is BrowserLoginStateStatus {
511 return typeof value === "string" && BROWSER_LOGIN_STATE_STATUS_SET.has(value);
512}
513
514export function isBaaExecutionJournalKind(value: unknown): value is BaaExecutionJournalKind {
515 return typeof value === "string" && BAA_EXECUTION_JOURNAL_KIND_SET.has(value);
516}
517
518export function buildAutomationStateValue(mode: AutomationMode): string {
519 return JSON.stringify({ mode });
520}
521
522export function isLeaderLeaseExpired(
523 lease: Pick<LeaderLeaseRecord, "leaseExpiresAt">,
524 now: number = nowUnixSeconds()
525): boolean {
526 return lease.leaseExpiresAt <= now;
527}
528
529export function canAcquireLeaderLease(
530 lease: LeaderLeaseRecord | null,
531 controllerId: string,
532 now: number = nowUnixSeconds()
533): boolean {
534 return lease == null || lease.holderId === controllerId || isLeaderLeaseExpired(lease, now);
535}
536
537export function getLeaderLeaseOperation(
538 lease: LeaderLeaseRecord | null,
539 controllerId: string,
540 now: number = nowUnixSeconds()
541): LeaderLeaseOperation {
542 return lease != null && lease.holderId === controllerId && !isLeaderLeaseExpired(lease, now)
543 ? "renew"
544 : "acquire";
545}
546
547export function buildControllerHeartbeatRecord(input: ControllerHeartbeatInput): ControllerRecord {
548 const heartbeatAt = input.heartbeatAt ?? nowUnixSeconds();
549
550 return {
551 controllerId: input.controllerId,
552 host: input.host,
553 role: input.role,
554 priority: input.priority,
555 status: input.status,
556 version: input.version ?? null,
557 lastHeartbeatAt: heartbeatAt,
558 lastStartedAt: input.startedAt ?? heartbeatAt,
559 metadataJson: input.metadataJson ?? null
560 };
561}
562
563export function buildLeaderLeaseRecord(
564 currentLease: LeaderLeaseRecord | null,
565 input: LeaderLeaseAcquireInput
566): LeaderLeaseRecord {
567 const now = input.now ?? nowUnixSeconds();
568 const operation = getLeaderLeaseOperation(currentLease, input.controllerId, now);
569
570 return {
571 leaseName: GLOBAL_LEASE_NAME,
572 holderId: input.controllerId,
573 holderHost: input.host,
574 term: operation === "renew" ? currentLease!.term : (currentLease?.term ?? 0) + 1,
575 leaseExpiresAt: now + input.ttlSec,
576 renewedAt: now,
577 preferredHolderId: input.preferred ? input.controllerId : currentLease?.preferredHolderId ?? null,
578 metadataJson: input.metadataJson ?? currentLease?.metadataJson ?? null
579 };
580}
581
582export function buildLeaderLeaseAcquireResult(
583 previousLease: LeaderLeaseRecord | null,
584 currentLease: LeaderLeaseRecord,
585 input: LeaderLeaseAcquireInput
586): LeaderLeaseAcquireResult {
587 const now = input.now ?? currentLease.renewedAt;
588
589 return {
590 holderId: currentLease.holderId,
591 holderHost: currentLease.holderHost,
592 term: currentLease.term,
593 leaseExpiresAt: currentLease.leaseExpiresAt,
594 renewedAt: currentLease.renewedAt,
595 isLeader: currentLease.holderId === input.controllerId,
596 operation:
597 currentLease.holderId === input.controllerId
598 ? getLeaderLeaseOperation(previousLease, input.controllerId, now)
599 : "acquire",
600 lease: currentLease
601 };
602}
603
604function toD1Bindable(value: D1Bindable | undefined): D1Bindable {
605 return value ?? null;
606}
607
608function readColumn(row: DatabaseRow, column: string): unknown {
609 if (!(column in row)) {
610 throw new TypeError(`Expected D1 row to include column "${column}".`);
611 }
612
613 return row[column];
614}
615
616function readRequiredString(row: DatabaseRow, column: string): string {
617 const value = readColumn(row, column);
618
619 if (typeof value !== "string") {
620 throw new TypeError(`Expected column "${column}" to be a string.`);
621 }
622
623 return value;
624}
625
626function readOptionalString(row: DatabaseRow, column: string): string | null {
627 const value = readColumn(row, column);
628
629 if (value == null) {
630 return null;
631 }
632
633 if (typeof value !== "string") {
634 throw new TypeError(`Expected column "${column}" to be a string or null.`);
635 }
636
637 return value;
638}
639
640function readRequiredNumber(row: DatabaseRow, column: string): number {
641 const value = readColumn(row, column);
642
643 if (typeof value !== "number") {
644 throw new TypeError(`Expected column "${column}" to be a number.`);
645 }
646
647 return value;
648}
649
650function readOptionalNumber(row: DatabaseRow, column: string): number | null {
651 const value = readColumn(row, column);
652
653 if (value == null) {
654 return null;
655 }
656
657 if (typeof value !== "number") {
658 throw new TypeError(`Expected column "${column}" to be a number or null.`);
659 }
660
661 return value;
662}
663
664function readTaskStatus(row: DatabaseRow, column: string): TaskStatus {
665 const value = readRequiredString(row, column);
666
667 if (!isTaskStatus(value)) {
668 throw new TypeError(`Unexpected task status "${value}".`);
669 }
670
671 return value;
672}
673
674function readStepStatus(row: DatabaseRow, column: string): StepStatus {
675 const value = readRequiredString(row, column);
676
677 if (!isStepStatus(value)) {
678 throw new TypeError(`Unexpected step status "${value}".`);
679 }
680
681 return value;
682}
683
684function readStepKind(row: DatabaseRow, column: string): StepKind {
685 const value = readRequiredString(row, column);
686
687 if (!isStepKind(value)) {
688 throw new TypeError(`Unexpected step kind "${value}".`);
689 }
690
691 return value;
692}
693
694function readBrowserLoginStateStatus(row: DatabaseRow, column: string): BrowserLoginStateStatus {
695 const value = readRequiredString(row, column);
696
697 if (!isBrowserLoginStateStatus(value)) {
698 throw new TypeError(`Unexpected browser login state status "${value}".`);
699 }
700
701 return value;
702}
703
704function readBaaExecutionJournalKind(row: DatabaseRow, column: string): BaaExecutionJournalKind {
705 const value = readRequiredString(row, column);
706
707 if (!isBaaExecutionJournalKind(value)) {
708 throw new TypeError(`Unexpected BAA execution journal kind "${value}".`);
709 }
710
711 return value;
712}
713
714function normalizeStringArray(values: readonly string[]): string[] {
715 const uniqueValues = new Set<string>();
716
717 for (const value of values) {
718 const normalized = value.trim();
719
720 if (normalized !== "") {
721 uniqueValues.add(normalized);
722 }
723 }
724
725 return [...uniqueValues].sort((left, right) => left.localeCompare(right));
726}
727
728function parseStringArrayJson(jsonText: string | null | undefined, column: string): string[] {
729 if (jsonText == null || jsonText === "") {
730 return [];
731 }
732
733 const parsed = JSON.parse(jsonText) as unknown;
734
735 if (!Array.isArray(parsed)) {
736 throw new TypeError(`Expected column "${column}" to contain a JSON string array.`);
737 }
738
739 const values: string[] = [];
740
741 for (const entry of parsed) {
742 if (typeof entry !== "string") {
743 throw new TypeError(`Expected column "${column}" to contain only string values.`);
744 }
745
746 values.push(entry);
747 }
748
749 return normalizeStringArray(values);
750}
751
752function normalizePositiveInteger(value: number | null | undefined, fallback: number): number {
753 if (typeof value !== "number" || !Number.isFinite(value)) {
754 return fallback;
755 }
756
757 const normalized = Math.trunc(value);
758 return normalized > 0 ? normalized : fallback;
759}
760
761export function mapLeaderLeaseRow(row: DatabaseRow): LeaderLeaseRecord {
762 return {
763 leaseName: readRequiredString(row, "lease_name"),
764 holderId: readRequiredString(row, "holder_id"),
765 holderHost: readRequiredString(row, "holder_host"),
766 term: readRequiredNumber(row, "term"),
767 leaseExpiresAt: readRequiredNumber(row, "lease_expires_at"),
768 renewedAt: readRequiredNumber(row, "renewed_at"),
769 preferredHolderId: readOptionalString(row, "preferred_holder_id"),
770 metadataJson: readOptionalString(row, "metadata_json")
771 };
772}
773
774export function mapControllerRow(row: DatabaseRow): ControllerRecord {
775 return {
776 controllerId: readRequiredString(row, "controller_id"),
777 host: readRequiredString(row, "host"),
778 role: readRequiredString(row, "role"),
779 priority: readRequiredNumber(row, "priority"),
780 status: readRequiredString(row, "status"),
781 version: readOptionalString(row, "version"),
782 lastHeartbeatAt: readRequiredNumber(row, "last_heartbeat_at"),
783 lastStartedAt: readOptionalNumber(row, "last_started_at"),
784 metadataJson: readOptionalString(row, "metadata_json")
785 };
786}
787
788export function mapWorkerRow(row: DatabaseRow): WorkerRecord {
789 return {
790 workerId: readRequiredString(row, "worker_id"),
791 controllerId: readRequiredString(row, "controller_id"),
792 host: readRequiredString(row, "host"),
793 workerType: readRequiredString(row, "worker_type"),
794 status: readRequiredString(row, "status"),
795 maxParallelism: readRequiredNumber(row, "max_parallelism"),
796 currentLoad: readRequiredNumber(row, "current_load"),
797 lastHeartbeatAt: readRequiredNumber(row, "last_heartbeat_at"),
798 capabilitiesJson: readOptionalString(row, "capabilities_json"),
799 metadataJson: readOptionalString(row, "metadata_json")
800 };
801}
802
803export function mapTaskRow(row: DatabaseRow): TaskRecord {
804 return {
805 taskId: readRequiredString(row, "task_id"),
806 repo: readRequiredString(row, "repo"),
807 taskType: readRequiredString(row, "task_type"),
808 title: readRequiredString(row, "title"),
809 goal: readRequiredString(row, "goal"),
810 source: readRequiredString(row, "source"),
811 priority: readRequiredNumber(row, "priority"),
812 status: readTaskStatus(row, "status"),
813 planningStrategy: readOptionalString(row, "planning_strategy"),
814 plannerProvider: readOptionalString(row, "planner_provider"),
815 branchName: readOptionalString(row, "branch_name"),
816 baseRef: readOptionalString(row, "base_ref"),
817 targetHost: readOptionalString(row, "target_host"),
818 assignedControllerId: readOptionalString(row, "assigned_controller_id"),
819 currentStepIndex: readRequiredNumber(row, "current_step_index"),
820 constraintsJson: readOptionalString(row, "constraints_json"),
821 acceptanceJson: readOptionalString(row, "acceptance_json"),
822 metadataJson: readOptionalString(row, "metadata_json"),
823 resultSummary: readOptionalString(row, "result_summary"),
824 resultJson: readOptionalString(row, "result_json"),
825 errorText: readOptionalString(row, "error_text"),
826 createdAt: readRequiredNumber(row, "created_at"),
827 updatedAt: readRequiredNumber(row, "updated_at"),
828 startedAt: readOptionalNumber(row, "started_at"),
829 finishedAt: readOptionalNumber(row, "finished_at")
830 };
831}
832
833export function mapTaskStepRow(row: DatabaseRow): TaskStepRecord {
834 return {
835 stepId: readRequiredString(row, "step_id"),
836 taskId: readRequiredString(row, "task_id"),
837 stepIndex: readRequiredNumber(row, "step_index"),
838 stepName: readRequiredString(row, "step_name"),
839 stepKind: readStepKind(row, "step_kind"),
840 status: readStepStatus(row, "status"),
841 assignedWorkerId: readOptionalString(row, "assigned_worker_id"),
842 assignedControllerId: readOptionalString(row, "assigned_controller_id"),
843 timeoutSec: readRequiredNumber(row, "timeout_sec"),
844 retryLimit: readRequiredNumber(row, "retry_limit"),
845 retryCount: readRequiredNumber(row, "retry_count"),
846 leaseExpiresAt: readOptionalNumber(row, "lease_expires_at"),
847 inputJson: readOptionalString(row, "input_json"),
848 outputJson: readOptionalString(row, "output_json"),
849 summary: readOptionalString(row, "summary"),
850 errorText: readOptionalString(row, "error_text"),
851 createdAt: readRequiredNumber(row, "created_at"),
852 updatedAt: readRequiredNumber(row, "updated_at"),
853 startedAt: readOptionalNumber(row, "started_at"),
854 finishedAt: readOptionalNumber(row, "finished_at")
855 };
856}
857
858export function mapTaskRunRow(row: DatabaseRow): TaskRunRecord {
859 return {
860 runId: readRequiredString(row, "run_id"),
861 taskId: readRequiredString(row, "task_id"),
862 stepId: readRequiredString(row, "step_id"),
863 workerId: readRequiredString(row, "worker_id"),
864 controllerId: readRequiredString(row, "controller_id"),
865 host: readRequiredString(row, "host"),
866 pid: readOptionalNumber(row, "pid"),
867 status: readRequiredString(row, "status"),
868 leaseExpiresAt: readOptionalNumber(row, "lease_expires_at"),
869 heartbeatAt: readOptionalNumber(row, "heartbeat_at"),
870 logDir: readRequiredString(row, "log_dir"),
871 stdoutPath: readOptionalString(row, "stdout_path"),
872 stderrPath: readOptionalString(row, "stderr_path"),
873 workerLogPath: readOptionalString(row, "worker_log_path"),
874 checkpointSeq: readRequiredNumber(row, "checkpoint_seq"),
875 exitCode: readOptionalNumber(row, "exit_code"),
876 resultJson: readOptionalString(row, "result_json"),
877 errorText: readOptionalString(row, "error_text"),
878 createdAt: readRequiredNumber(row, "created_at"),
879 startedAt: readOptionalNumber(row, "started_at"),
880 finishedAt: readOptionalNumber(row, "finished_at")
881 };
882}
883
884export function mapTaskCheckpointRow(row: DatabaseRow): TaskCheckpointRecord {
885 return {
886 checkpointId: readRequiredString(row, "checkpoint_id"),
887 taskId: readRequiredString(row, "task_id"),
888 stepId: readRequiredString(row, "step_id"),
889 runId: readRequiredString(row, "run_id"),
890 seq: readRequiredNumber(row, "seq"),
891 checkpointType: readRequiredString(row, "checkpoint_type"),
892 summary: readOptionalString(row, "summary"),
893 contentText: readOptionalString(row, "content_text"),
894 contentJson: readOptionalString(row, "content_json"),
895 createdAt: readRequiredNumber(row, "created_at")
896 };
897}
898
899export function mapTaskLogRow(row: DatabaseRow): TaskLogRecord {
900 return {
901 logId: readRequiredNumber(row, "log_id"),
902 taskId: readRequiredString(row, "task_id"),
903 stepId: readOptionalString(row, "step_id"),
904 runId: readRequiredString(row, "run_id"),
905 seq: readRequiredNumber(row, "seq"),
906 stream: readRequiredString(row, "stream"),
907 level: readOptionalString(row, "level"),
908 message: readRequiredString(row, "message"),
909 createdAt: readRequiredNumber(row, "created_at")
910 };
911}
912
913export function mapSystemStateRow(row: DatabaseRow): SystemStateRecord {
914 return {
915 stateKey: readRequiredString(row, "state_key"),
916 valueJson: readRequiredString(row, "value_json"),
917 updatedAt: readRequiredNumber(row, "updated_at")
918 };
919}
920
921export function mapAutomationStateRow(row: DatabaseRow): AutomationStateRecord {
922 const record = mapSystemStateRow(row);
923
924 if (record.stateKey !== AUTOMATION_STATE_KEY) {
925 throw new TypeError(`Expected state_key "${AUTOMATION_STATE_KEY}", received "${record.stateKey}".`);
926 }
927
928 const parsed = parseJsonText<{ mode?: unknown }>(record.valueJson);
929 const mode = parsed?.mode;
930
931 if (!isAutomationMode(mode)) {
932 throw new TypeError(`Automation state is missing a valid mode in "${record.valueJson}".`);
933 }
934
935 return {
936 ...record,
937 stateKey: AUTOMATION_STATE_KEY,
938 mode
939 };
940}
941
942export function mapTaskArtifactRow(row: DatabaseRow): TaskArtifactRecord {
943 return {
944 artifactId: readRequiredString(row, "artifact_id"),
945 taskId: readRequiredString(row, "task_id"),
946 stepId: readOptionalString(row, "step_id"),
947 runId: readOptionalString(row, "run_id"),
948 artifactType: readRequiredString(row, "artifact_type"),
949 path: readOptionalString(row, "path"),
950 uri: readOptionalString(row, "uri"),
951 sizeBytes: readOptionalNumber(row, "size_bytes"),
952 sha256: readOptionalString(row, "sha256"),
953 metadataJson: readOptionalString(row, "metadata_json"),
954 createdAt: readRequiredNumber(row, "created_at")
955 };
956}
957
958export function mapBrowserLoginStateRow(row: DatabaseRow): BrowserLoginStateRecord {
959 return {
960 platform: readRequiredString(row, "platform"),
961 host: readRequiredString(row, "host"),
962 browser: readRequiredString(row, "browser"),
963 clientId: readRequiredString(row, "client_id"),
964 account: readRequiredString(row, "account"),
965 credentialFingerprint: readRequiredString(row, "credential_fingerprint"),
966 capturedAt: readRequiredNumber(row, "captured_at"),
967 lastSeenAt: readRequiredNumber(row, "last_seen_at"),
968 status: readBrowserLoginStateStatus(row, "status")
969 };
970}
971
972export function mapBrowserEndpointMetadataRow(row: DatabaseRow): BrowserEndpointMetadataRecord {
973 return {
974 platform: readRequiredString(row, "platform"),
975 clientId: readRequiredString(row, "client_id"),
976 account: readRequiredString(row, "account"),
977 endpoints: parseStringArrayJson(readRequiredString(row, "endpoints_json"), "endpoints_json"),
978 updatedAt: readRequiredNumber(row, "updated_at"),
979 lastVerifiedAt: readOptionalNumber(row, "last_verified_at")
980 };
981}
982
983export function mapBaaExecutionJournalRow(row: DatabaseRow): BaaExecutionJournalRecord {
984 return {
985 journalId: readRequiredNumber(row, "journal_id"),
986 kind: readBaaExecutionJournalKind(row, "summary_kind"),
987 assistantMessageId: readRequiredString(row, "assistant_message_id"),
988 conversationId: readOptionalString(row, "conversation_id"),
989 ingestedAt: readRequiredNumber(row, "ingested_at"),
990 messageDedupeKey: readRequiredString(row, "message_dedupe_key"),
991 observedAt: readOptionalNumber(row, "observed_at"),
992 platform: readRequiredString(row, "platform"),
993 source: readRequiredString(row, "source"),
994 status: readRequiredString(row, "status"),
995 summaryJson: readRequiredString(row, "summary_json")
996 };
997}
998
999export const SELECT_CURRENT_LEASE_SQL = `
1000 SELECT
1001 lease_name,
1002 holder_id,
1003 holder_host,
1004 term,
1005 lease_expires_at,
1006 renewed_at,
1007 preferred_holder_id,
1008 metadata_json
1009 FROM leader_lease
1010 WHERE lease_name = ?
1011`;
1012
1013export const SELECT_CONTROLLER_SQL = `
1014 SELECT
1015 controller_id,
1016 host,
1017 role,
1018 priority,
1019 status,
1020 version,
1021 last_heartbeat_at,
1022 last_started_at,
1023 metadata_json
1024 FROM controllers
1025 WHERE controller_id = ?
1026`;
1027
1028export const SELECT_CONTROLLERS_SQL = `
1029 SELECT
1030 controller_id,
1031 host,
1032 role,
1033 priority,
1034 status,
1035 version,
1036 last_heartbeat_at,
1037 last_started_at,
1038 metadata_json
1039 FROM controllers
1040 ORDER BY last_heartbeat_at DESC, priority ASC, controller_id ASC
1041 LIMIT ?
1042`;
1043
1044export const UPSERT_LEADER_LEASE_SQL = `
1045 INSERT INTO leader_lease (
1046 lease_name,
1047 holder_id,
1048 holder_host,
1049 term,
1050 lease_expires_at,
1051 renewed_at,
1052 preferred_holder_id,
1053 metadata_json
1054 )
1055 VALUES (?, ?, ?, ?, ?, ?, ?, ?)
1056 ON CONFLICT(lease_name) DO UPDATE SET
1057 holder_id = excluded.holder_id,
1058 holder_host = excluded.holder_host,
1059 term = excluded.term,
1060 lease_expires_at = excluded.lease_expires_at,
1061 renewed_at = excluded.renewed_at,
1062 preferred_holder_id = excluded.preferred_holder_id,
1063 metadata_json = excluded.metadata_json
1064`;
1065
1066export const ACQUIRE_LEADER_LEASE_SQL = `
1067 INSERT INTO leader_lease (
1068 lease_name,
1069 holder_id,
1070 holder_host,
1071 term,
1072 lease_expires_at,
1073 renewed_at,
1074 preferred_holder_id,
1075 metadata_json
1076 )
1077 VALUES (?, ?, ?, ?, ?, ?, ?, ?)
1078 ON CONFLICT(lease_name) DO UPDATE SET
1079 holder_id = CASE
1080 WHEN leader_lease.holder_id = excluded.holder_id OR leader_lease.lease_expires_at <= ? THEN excluded.holder_id
1081 ELSE leader_lease.holder_id
1082 END,
1083 holder_host = CASE
1084 WHEN leader_lease.holder_id = excluded.holder_id OR leader_lease.lease_expires_at <= ? THEN excluded.holder_host
1085 ELSE leader_lease.holder_host
1086 END,
1087 term = CASE
1088 WHEN leader_lease.holder_id = excluded.holder_id THEN leader_lease.term
1089 WHEN leader_lease.lease_expires_at <= ? THEN leader_lease.term + 1
1090 ELSE leader_lease.term
1091 END,
1092 lease_expires_at = CASE
1093 WHEN leader_lease.holder_id = excluded.holder_id OR leader_lease.lease_expires_at <= ? THEN excluded.lease_expires_at
1094 ELSE leader_lease.lease_expires_at
1095 END,
1096 renewed_at = CASE
1097 WHEN leader_lease.holder_id = excluded.holder_id OR leader_lease.lease_expires_at <= ? THEN excluded.renewed_at
1098 ELSE leader_lease.renewed_at
1099 END,
1100 preferred_holder_id = CASE
1101 WHEN leader_lease.holder_id = excluded.holder_id OR leader_lease.lease_expires_at <= ? THEN excluded.preferred_holder_id
1102 ELSE leader_lease.preferred_holder_id
1103 END,
1104 metadata_json = CASE
1105 WHEN leader_lease.holder_id = excluded.holder_id OR leader_lease.lease_expires_at <= ? THEN excluded.metadata_json
1106 ELSE leader_lease.metadata_json
1107 END
1108`;
1109
1110export const UPSERT_CONTROLLER_SQL = `
1111 INSERT INTO controllers (
1112 controller_id,
1113 host,
1114 role,
1115 priority,
1116 status,
1117 version,
1118 last_heartbeat_at,
1119 last_started_at,
1120 metadata_json
1121 )
1122 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
1123 ON CONFLICT(controller_id) DO UPDATE SET
1124 host = excluded.host,
1125 role = excluded.role,
1126 priority = excluded.priority,
1127 status = excluded.status,
1128 version = excluded.version,
1129 last_heartbeat_at = excluded.last_heartbeat_at,
1130 last_started_at = excluded.last_started_at,
1131 metadata_json = excluded.metadata_json
1132`;
1133
1134export const UPSERT_WORKER_SQL = `
1135 INSERT INTO workers (
1136 worker_id,
1137 controller_id,
1138 host,
1139 worker_type,
1140 status,
1141 max_parallelism,
1142 current_load,
1143 last_heartbeat_at,
1144 capabilities_json,
1145 metadata_json
1146 )
1147 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
1148 ON CONFLICT(worker_id) DO UPDATE SET
1149 controller_id = excluded.controller_id,
1150 host = excluded.host,
1151 worker_type = excluded.worker_type,
1152 status = excluded.status,
1153 max_parallelism = excluded.max_parallelism,
1154 current_load = excluded.current_load,
1155 last_heartbeat_at = excluded.last_heartbeat_at,
1156 capabilities_json = excluded.capabilities_json,
1157 metadata_json = excluded.metadata_json
1158`;
1159
1160export const UPSERT_SYSTEM_STATE_SQL = `
1161 INSERT INTO system_state (
1162 state_key,
1163 value_json,
1164 updated_at
1165 )
1166 VALUES (?, ?, ?)
1167 ON CONFLICT(state_key) DO UPDATE SET
1168 value_json = excluded.value_json,
1169 updated_at = excluded.updated_at
1170`;
1171
1172export const ENSURE_AUTOMATION_STATE_SQL = `
1173 INSERT INTO system_state (
1174 state_key,
1175 value_json,
1176 updated_at
1177 )
1178 VALUES (?, ?, ?)
1179 ON CONFLICT(state_key) DO NOTHING
1180`;
1181
1182export const SELECT_SYSTEM_STATE_SQL = `
1183 SELECT
1184 state_key,
1185 value_json,
1186 updated_at
1187 FROM system_state
1188 WHERE state_key = ?
1189`;
1190
1191export const SELECT_QUEUED_TASK_COUNT_SQL = `
1192 SELECT COUNT(*) AS value
1193 FROM tasks
1194 WHERE status = 'queued'
1195`;
1196
1197export const INSERT_TASK_SQL = `
1198 INSERT INTO tasks (
1199 task_id,
1200 repo,
1201 task_type,
1202 title,
1203 goal,
1204 source,
1205 priority,
1206 status,
1207 planning_strategy,
1208 planner_provider,
1209 branch_name,
1210 base_ref,
1211 target_host,
1212 assigned_controller_id,
1213 current_step_index,
1214 constraints_json,
1215 acceptance_json,
1216 metadata_json,
1217 result_summary,
1218 result_json,
1219 error_text,
1220 created_at,
1221 updated_at,
1222 started_at,
1223 finished_at
1224 )
1225 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
1226`;
1227
1228export const SELECT_TASK_SQL = `
1229 SELECT
1230 task_id,
1231 repo,
1232 task_type,
1233 title,
1234 goal,
1235 source,
1236 priority,
1237 status,
1238 planning_strategy,
1239 planner_provider,
1240 branch_name,
1241 base_ref,
1242 target_host,
1243 assigned_controller_id,
1244 current_step_index,
1245 constraints_json,
1246 acceptance_json,
1247 metadata_json,
1248 result_summary,
1249 result_json,
1250 error_text,
1251 created_at,
1252 updated_at,
1253 started_at,
1254 finished_at
1255 FROM tasks
1256 WHERE task_id = ?
1257`;
1258
1259export const SELECT_TASKS_SQL = `
1260 SELECT
1261 task_id,
1262 repo,
1263 task_type,
1264 title,
1265 goal,
1266 source,
1267 priority,
1268 status,
1269 planning_strategy,
1270 planner_provider,
1271 branch_name,
1272 base_ref,
1273 target_host,
1274 assigned_controller_id,
1275 current_step_index,
1276 constraints_json,
1277 acceptance_json,
1278 metadata_json,
1279 result_summary,
1280 result_json,
1281 error_text,
1282 created_at,
1283 updated_at,
1284 started_at,
1285 finished_at
1286 FROM tasks
1287 ORDER BY updated_at DESC, created_at DESC
1288 LIMIT ?
1289`;
1290
1291export const SELECT_TASKS_BY_STATUS_SQL = `
1292 SELECT
1293 task_id,
1294 repo,
1295 task_type,
1296 title,
1297 goal,
1298 source,
1299 priority,
1300 status,
1301 planning_strategy,
1302 planner_provider,
1303 branch_name,
1304 base_ref,
1305 target_host,
1306 assigned_controller_id,
1307 current_step_index,
1308 constraints_json,
1309 acceptance_json,
1310 metadata_json,
1311 result_summary,
1312 result_json,
1313 error_text,
1314 created_at,
1315 updated_at,
1316 started_at,
1317 finished_at
1318 FROM tasks
1319 WHERE status = ?
1320 ORDER BY updated_at DESC, created_at DESC
1321 LIMIT ?
1322`;
1323
1324export const INSERT_TASK_STEP_SQL = `
1325 INSERT INTO task_steps (
1326 step_id,
1327 task_id,
1328 step_index,
1329 step_name,
1330 step_kind,
1331 status,
1332 assigned_worker_id,
1333 assigned_controller_id,
1334 timeout_sec,
1335 retry_limit,
1336 retry_count,
1337 lease_expires_at,
1338 input_json,
1339 output_json,
1340 summary,
1341 error_text,
1342 created_at,
1343 updated_at,
1344 started_at,
1345 finished_at
1346 )
1347 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
1348`;
1349
1350export const SELECT_TASK_STEPS_SQL = `
1351 SELECT
1352 step_id,
1353 task_id,
1354 step_index,
1355 step_name,
1356 step_kind,
1357 status,
1358 assigned_worker_id,
1359 assigned_controller_id,
1360 timeout_sec,
1361 retry_limit,
1362 retry_count,
1363 lease_expires_at,
1364 input_json,
1365 output_json,
1366 summary,
1367 error_text,
1368 created_at,
1369 updated_at,
1370 started_at,
1371 finished_at
1372 FROM task_steps
1373 WHERE task_id = ?
1374 ORDER BY step_index ASC
1375`;
1376
1377export const INSERT_TASK_RUN_SQL = `
1378 INSERT INTO task_runs (
1379 run_id,
1380 task_id,
1381 step_id,
1382 worker_id,
1383 controller_id,
1384 host,
1385 pid,
1386 status,
1387 lease_expires_at,
1388 heartbeat_at,
1389 log_dir,
1390 stdout_path,
1391 stderr_path,
1392 worker_log_path,
1393 checkpoint_seq,
1394 exit_code,
1395 result_json,
1396 error_text,
1397 created_at,
1398 started_at,
1399 finished_at
1400 )
1401 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
1402`;
1403
1404export const SELECT_ACTIVE_RUN_COUNT_SQL = `
1405 SELECT COUNT(*) AS value
1406 FROM task_runs
1407 WHERE started_at IS NOT NULL
1408 AND finished_at IS NULL
1409`;
1410
1411export const SELECT_RUN_SQL = `
1412 SELECT
1413 run_id,
1414 task_id,
1415 step_id,
1416 worker_id,
1417 controller_id,
1418 host,
1419 pid,
1420 status,
1421 lease_expires_at,
1422 heartbeat_at,
1423 log_dir,
1424 stdout_path,
1425 stderr_path,
1426 worker_log_path,
1427 checkpoint_seq,
1428 exit_code,
1429 result_json,
1430 error_text,
1431 created_at,
1432 started_at,
1433 finished_at
1434 FROM task_runs
1435 WHERE run_id = ?
1436`;
1437
1438export const SELECT_RUNS_SQL = `
1439 SELECT
1440 run_id,
1441 task_id,
1442 step_id,
1443 worker_id,
1444 controller_id,
1445 host,
1446 pid,
1447 status,
1448 lease_expires_at,
1449 heartbeat_at,
1450 log_dir,
1451 stdout_path,
1452 stderr_path,
1453 worker_log_path,
1454 checkpoint_seq,
1455 exit_code,
1456 result_json,
1457 error_text,
1458 created_at,
1459 started_at,
1460 finished_at
1461 FROM task_runs
1462 ORDER BY created_at DESC, run_id DESC
1463 LIMIT ?
1464`;
1465
1466export const SELECT_TASK_LOGS_SQL = `
1467 SELECT
1468 log_id,
1469 task_id,
1470 step_id,
1471 run_id,
1472 seq,
1473 stream,
1474 level,
1475 message,
1476 created_at
1477 FROM (
1478 SELECT
1479 log_id,
1480 task_id,
1481 step_id,
1482 run_id,
1483 seq,
1484 stream,
1485 level,
1486 message,
1487 created_at
1488 FROM task_logs
1489 WHERE task_id = ?
1490 ORDER BY created_at DESC, log_id DESC
1491 LIMIT ?
1492 ) AS recent_logs
1493 ORDER BY created_at ASC, log_id ASC
1494`;
1495
1496export const SELECT_TASK_LOGS_BY_RUN_SQL = `
1497 SELECT
1498 log_id,
1499 task_id,
1500 step_id,
1501 run_id,
1502 seq,
1503 stream,
1504 level,
1505 message,
1506 created_at
1507 FROM (
1508 SELECT
1509 log_id,
1510 task_id,
1511 step_id,
1512 run_id,
1513 seq,
1514 stream,
1515 level,
1516 message,
1517 created_at
1518 FROM task_logs
1519 WHERE task_id = ?
1520 AND run_id = ?
1521 ORDER BY created_at DESC, log_id DESC
1522 LIMIT ?
1523 ) AS recent_logs
1524 ORDER BY created_at ASC, log_id ASC
1525`;
1526
1527export const INSERT_TASK_CHECKPOINT_SQL = `
1528 INSERT INTO task_checkpoints (
1529 checkpoint_id,
1530 task_id,
1531 step_id,
1532 run_id,
1533 seq,
1534 checkpoint_type,
1535 summary,
1536 content_text,
1537 content_json,
1538 created_at
1539 )
1540 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
1541`;
1542
1543export const INSERT_TASK_LOG_SQL = `
1544 INSERT INTO task_logs (
1545 task_id,
1546 step_id,
1547 run_id,
1548 seq,
1549 stream,
1550 level,
1551 message,
1552 created_at
1553 )
1554 VALUES (?, ?, ?, ?, ?, ?, ?, ?)
1555`;
1556
1557export const INSERT_TASK_ARTIFACT_SQL = `
1558 INSERT INTO task_artifacts (
1559 artifact_id,
1560 task_id,
1561 step_id,
1562 run_id,
1563 artifact_type,
1564 path,
1565 uri,
1566 size_bytes,
1567 sha256,
1568 metadata_json,
1569 created_at
1570 )
1571 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
1572`;
1573
1574export const UPSERT_BROWSER_LOGIN_STATE_SQL = `
1575 INSERT INTO browser_login_states (
1576 platform,
1577 host,
1578 browser,
1579 client_id,
1580 account,
1581 credential_fingerprint,
1582 captured_at,
1583 last_seen_at,
1584 status
1585 )
1586 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
1587 ON CONFLICT(platform, client_id, account) DO UPDATE SET
1588 host = excluded.host,
1589 browser = excluded.browser,
1590 credential_fingerprint = excluded.credential_fingerprint,
1591 captured_at = CASE
1592 WHEN browser_login_states.credential_fingerprint = excluded.credential_fingerprint
1593 THEN browser_login_states.captured_at
1594 ELSE excluded.captured_at
1595 END,
1596 last_seen_at = excluded.last_seen_at,
1597 status = excluded.status
1598`;
1599
1600export const SELECT_BROWSER_LOGIN_STATE_SQL = `
1601 SELECT
1602 platform,
1603 host,
1604 browser,
1605 client_id,
1606 account,
1607 credential_fingerprint,
1608 captured_at,
1609 last_seen_at,
1610 status
1611 FROM browser_login_states
1612 WHERE platform = ?
1613 AND client_id = ?
1614 AND account = ?
1615`;
1616
1617export const SELECT_BROWSER_LOGIN_STATES_PREFIX_SQL = `
1618 SELECT
1619 platform,
1620 host,
1621 browser,
1622 client_id,
1623 account,
1624 credential_fingerprint,
1625 captured_at,
1626 last_seen_at,
1627 status
1628 FROM browser_login_states
1629`;
1630
1631export const UPDATE_BROWSER_LOGIN_STATES_STALE_SQL = `
1632 UPDATE browser_login_states
1633 SET status = 'stale'
1634 WHERE status = 'fresh'
1635 AND last_seen_at <= ?
1636`;
1637
1638export const UPDATE_BROWSER_LOGIN_STATES_LOST_SQL = `
1639 UPDATE browser_login_states
1640 SET status = 'lost'
1641 WHERE status != 'lost'
1642 AND last_seen_at <= ?
1643`;
1644
1645export const UPSERT_BROWSER_ENDPOINT_METADATA_SQL = `
1646 INSERT INTO browser_endpoint_metadata (
1647 platform,
1648 client_id,
1649 account,
1650 endpoints_json,
1651 updated_at,
1652 last_verified_at
1653 )
1654 VALUES (?, ?, ?, ?, ?, ?)
1655 ON CONFLICT(platform, client_id, account) DO UPDATE SET
1656 endpoints_json = excluded.endpoints_json,
1657 updated_at = excluded.updated_at,
1658 last_verified_at = excluded.last_verified_at
1659`;
1660
1661export const SELECT_BROWSER_ENDPOINT_METADATA_SQL = `
1662 SELECT
1663 platform,
1664 client_id,
1665 account,
1666 endpoints_json,
1667 updated_at,
1668 last_verified_at
1669 FROM browser_endpoint_metadata
1670 WHERE platform = ?
1671 AND client_id = ?
1672 AND account = ?
1673`;
1674
1675export const SELECT_BROWSER_ENDPOINT_METADATA_PREFIX_SQL = `
1676 SELECT
1677 platform,
1678 client_id,
1679 account,
1680 endpoints_json,
1681 updated_at,
1682 last_verified_at
1683 FROM browser_endpoint_metadata
1684`;
1685
1686export const INSERT_BAA_MESSAGE_DEDUPE_SQL = `
1687 INSERT INTO baa_message_dedupes (
1688 dedupe_key,
1689 assistant_message_id,
1690 conversation_id,
1691 platform,
1692 observed_at,
1693 created_at
1694 )
1695 VALUES (?, ?, ?, ?, ?, ?)
1696 ON CONFLICT(dedupe_key) DO NOTHING
1697`;
1698
1699export const SELECT_BAA_MESSAGE_DEDUPE_EXISTS_SQL = `
1700 SELECT 1 AS value
1701 FROM baa_message_dedupes
1702 WHERE dedupe_key = ?
1703 LIMIT 1
1704`;
1705
1706export const PRUNE_BAA_MESSAGE_DEDUPE_SQL = `
1707 DELETE FROM baa_message_dedupes
1708 WHERE record_id IN (
1709 SELECT record_id
1710 FROM baa_message_dedupes
1711 ORDER BY created_at DESC, record_id DESC
1712 LIMIT -1 OFFSET ?
1713 )
1714`;
1715
1716export const INSERT_BAA_INSTRUCTION_DEDUPE_SQL = `
1717 INSERT INTO baa_instruction_dedupes (
1718 dedupe_key,
1719 instruction_id,
1720 assistant_message_id,
1721 conversation_id,
1722 platform,
1723 target,
1724 tool,
1725 created_at
1726 )
1727 VALUES (?, ?, ?, ?, ?, ?, ?, ?)
1728 ON CONFLICT(dedupe_key) DO NOTHING
1729`;
1730
1731export const SELECT_BAA_INSTRUCTION_DEDUPE_EXISTS_SQL = `
1732 SELECT 1 AS value
1733 FROM baa_instruction_dedupes
1734 WHERE dedupe_key = ?
1735 LIMIT 1
1736`;
1737
1738export const PRUNE_BAA_INSTRUCTION_DEDUPE_SQL = `
1739 DELETE FROM baa_instruction_dedupes
1740 WHERE record_id IN (
1741 SELECT record_id
1742 FROM baa_instruction_dedupes
1743 ORDER BY created_at DESC, record_id DESC
1744 LIMIT -1 OFFSET ?
1745 )
1746`;
1747
1748export const INSERT_BAA_EXECUTION_JOURNAL_SQL = `
1749 INSERT INTO baa_execution_journal (
1750 summary_kind,
1751 assistant_message_id,
1752 conversation_id,
1753 message_dedupe_key,
1754 platform,
1755 source,
1756 status,
1757 ingested_at,
1758 observed_at,
1759 summary_json
1760 )
1761 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
1762`;
1763
1764export const SELECT_BAA_EXECUTION_JOURNAL_PREFIX_SQL = `
1765 SELECT
1766 journal_id,
1767 summary_kind,
1768 assistant_message_id,
1769 conversation_id,
1770 message_dedupe_key,
1771 platform,
1772 source,
1773 status,
1774 ingested_at,
1775 observed_at,
1776 summary_json
1777 FROM baa_execution_journal
1778`;
1779
1780export const PRUNE_BAA_EXECUTION_JOURNAL_SQL = `
1781 DELETE FROM baa_execution_journal
1782 WHERE journal_id IN (
1783 SELECT journal_id
1784 FROM baa_execution_journal
1785 ORDER BY ingested_at DESC, journal_id DESC
1786 LIMIT -1 OFFSET ?
1787 )
1788`;
1789
1790function leaderLeaseParams(record: LeaderLeaseRecord): D1Bindable[] {
1791 return [
1792 record.leaseName,
1793 record.holderId,
1794 record.holderHost,
1795 record.term,
1796 record.leaseExpiresAt,
1797 record.renewedAt,
1798 record.preferredHolderId,
1799 record.metadataJson
1800 ];
1801}
1802
1803function acquireLeaderLeaseParams(record: LeaderLeaseRecord, now: number): D1Bindable[] {
1804 return [...leaderLeaseParams(record), now, now, now, now, now, now, now];
1805}
1806
1807function controllerParams(record: ControllerRecord): D1Bindable[] {
1808 return [
1809 record.controllerId,
1810 record.host,
1811 record.role,
1812 record.priority,
1813 record.status,
1814 record.version,
1815 record.lastHeartbeatAt,
1816 record.lastStartedAt,
1817 record.metadataJson
1818 ];
1819}
1820
1821function workerParams(record: WorkerRecord): D1Bindable[] {
1822 return [
1823 record.workerId,
1824 record.controllerId,
1825 record.host,
1826 record.workerType,
1827 record.status,
1828 record.maxParallelism,
1829 record.currentLoad,
1830 record.lastHeartbeatAt,
1831 record.capabilitiesJson,
1832 record.metadataJson
1833 ];
1834}
1835
1836function systemStateParams(record: SystemStateRecord): D1Bindable[] {
1837 return [record.stateKey, record.valueJson, record.updatedAt];
1838}
1839
1840function taskParams(record: TaskRecord): D1Bindable[] {
1841 return [
1842 record.taskId,
1843 record.repo,
1844 record.taskType,
1845 record.title,
1846 record.goal,
1847 record.source,
1848 record.priority,
1849 record.status,
1850 record.planningStrategy,
1851 record.plannerProvider,
1852 record.branchName,
1853 record.baseRef,
1854 record.targetHost,
1855 record.assignedControllerId,
1856 record.currentStepIndex,
1857 record.constraintsJson,
1858 record.acceptanceJson,
1859 record.metadataJson,
1860 record.resultSummary,
1861 record.resultJson,
1862 record.errorText,
1863 record.createdAt,
1864 record.updatedAt,
1865 record.startedAt,
1866 record.finishedAt
1867 ];
1868}
1869
1870function taskStepParams(record: TaskStepRecord): D1Bindable[] {
1871 return [
1872 record.stepId,
1873 record.taskId,
1874 record.stepIndex,
1875 record.stepName,
1876 record.stepKind,
1877 record.status,
1878 record.assignedWorkerId,
1879 record.assignedControllerId,
1880 record.timeoutSec,
1881 record.retryLimit,
1882 record.retryCount,
1883 record.leaseExpiresAt,
1884 record.inputJson,
1885 record.outputJson,
1886 record.summary,
1887 record.errorText,
1888 record.createdAt,
1889 record.updatedAt,
1890 record.startedAt,
1891 record.finishedAt
1892 ];
1893}
1894
1895function taskRunParams(record: TaskRunRecord): D1Bindable[] {
1896 return [
1897 record.runId,
1898 record.taskId,
1899 record.stepId,
1900 record.workerId,
1901 record.controllerId,
1902 record.host,
1903 record.pid,
1904 record.status,
1905 record.leaseExpiresAt,
1906 record.heartbeatAt,
1907 record.logDir,
1908 record.stdoutPath,
1909 record.stderrPath,
1910 record.workerLogPath,
1911 record.checkpointSeq,
1912 record.exitCode,
1913 record.resultJson,
1914 record.errorText,
1915 record.createdAt,
1916 record.startedAt,
1917 record.finishedAt
1918 ];
1919}
1920
1921function taskCheckpointParams(record: TaskCheckpointRecord): D1Bindable[] {
1922 return [
1923 record.checkpointId,
1924 record.taskId,
1925 record.stepId,
1926 record.runId,
1927 record.seq,
1928 record.checkpointType,
1929 record.summary,
1930 record.contentText,
1931 record.contentJson,
1932 record.createdAt
1933 ];
1934}
1935
1936function taskLogParams(record: NewTaskLogRecord): D1Bindable[] {
1937 return [
1938 record.taskId,
1939 record.stepId,
1940 record.runId,
1941 record.seq,
1942 record.stream,
1943 record.level,
1944 record.message,
1945 record.createdAt
1946 ];
1947}
1948
1949function taskArtifactParams(record: TaskArtifactRecord): D1Bindable[] {
1950 return [
1951 record.artifactId,
1952 record.taskId,
1953 record.stepId,
1954 record.runId,
1955 record.artifactType,
1956 record.path,
1957 record.uri,
1958 record.sizeBytes,
1959 record.sha256,
1960 record.metadataJson,
1961 record.createdAt
1962 ];
1963}
1964
1965function browserSessionKeyParams(key: BrowserSessionKey): D1Bindable[] {
1966 return [key.platform, key.clientId, key.account];
1967}
1968
1969function browserLoginStateParams(record: BrowserLoginStateRecord): D1Bindable[] {
1970 return [
1971 record.platform,
1972 record.host,
1973 record.browser,
1974 record.clientId,
1975 record.account,
1976 record.credentialFingerprint,
1977 record.capturedAt,
1978 record.lastSeenAt,
1979 record.status
1980 ];
1981}
1982
1983function browserEndpointMetadataParams(record: BrowserEndpointMetadataRecord): D1Bindable[] {
1984 return [
1985 record.platform,
1986 record.clientId,
1987 record.account,
1988 JSON.stringify(normalizeStringArray(record.endpoints)),
1989 record.updatedAt,
1990 record.lastVerifiedAt
1991 ];
1992}
1993
1994function baaMessageDedupeParams(record: NewBaaMessageDedupeRecord): D1Bindable[] {
1995 return [
1996 record.dedupeKey,
1997 record.assistantMessageId,
1998 record.conversationId,
1999 record.platform,
2000 record.observedAt,
2001 record.createdAt
2002 ];
2003}
2004
2005function baaInstructionDedupeParams(record: NewBaaInstructionDedupeRecord): D1Bindable[] {
2006 return [
2007 record.dedupeKey,
2008 record.instructionId,
2009 record.assistantMessageId,
2010 record.conversationId,
2011 record.platform,
2012 record.target,
2013 record.tool,
2014 record.createdAt
2015 ];
2016}
2017
2018function baaExecutionJournalParams(record: NewBaaExecutionJournalRecord): D1Bindable[] {
2019 return [
2020 record.kind,
2021 record.assistantMessageId,
2022 record.conversationId,
2023 record.messageDedupeKey,
2024 record.platform,
2025 record.source,
2026 record.status,
2027 record.ingestedAt,
2028 record.observedAt,
2029 record.summaryJson
2030 ];
2031}
2032
2033function buildBrowserLoginStatesListQuery(
2034 options: ListBrowserLoginStatesOptions
2035): { query: string; params: D1Bindable[] } {
2036 const clauses: string[] = [];
2037 const params: D1Bindable[] = [];
2038
2039 if (options.platform != null) {
2040 clauses.push("platform = ?");
2041 params.push(options.platform);
2042 }
2043
2044 if (options.host != null) {
2045 clauses.push("host = ?");
2046 params.push(options.host);
2047 }
2048
2049 if (options.browser != null) {
2050 clauses.push("browser = ?");
2051 params.push(options.browser);
2052 }
2053
2054 if (options.clientId != null) {
2055 clauses.push("client_id = ?");
2056 params.push(options.clientId);
2057 }
2058
2059 if (options.account != null) {
2060 clauses.push("account = ?");
2061 params.push(options.account);
2062 }
2063
2064 if (options.status != null) {
2065 clauses.push("status = ?");
2066 params.push(options.status);
2067 }
2068
2069 const whereClause = clauses.length === 0 ? "" : `\nWHERE ${clauses.join("\n AND ")}`;
2070 const query = `${SELECT_BROWSER_LOGIN_STATES_PREFIX_SQL}${whereClause}
2071 ORDER BY last_seen_at DESC, platform ASC, client_id ASC, account ASC
2072 LIMIT ?`;
2073
2074 params.push(options.limit ?? 50);
2075 return { query, params };
2076}
2077
2078function buildBrowserEndpointMetadataListQuery(
2079 options: ListBrowserEndpointMetadataOptions
2080): { query: string; params: D1Bindable[] } {
2081 const clauses: string[] = [];
2082 const params: D1Bindable[] = [];
2083
2084 if (options.platform != null) {
2085 clauses.push("platform = ?");
2086 params.push(options.platform);
2087 }
2088
2089 if (options.clientId != null) {
2090 clauses.push("client_id = ?");
2091 params.push(options.clientId);
2092 }
2093
2094 if (options.account != null) {
2095 clauses.push("account = ?");
2096 params.push(options.account);
2097 }
2098
2099 const whereClause = clauses.length === 0 ? "" : `\nWHERE ${clauses.join("\n AND ")}`;
2100 const query = `${SELECT_BROWSER_ENDPOINT_METADATA_PREFIX_SQL}${whereClause}
2101 ORDER BY updated_at DESC, platform ASC, client_id ASC, account ASC
2102 LIMIT ?`;
2103
2104 params.push(options.limit ?? 50);
2105 return { query, params };
2106}
2107
2108function buildBaaExecutionJournalListQuery(
2109 options: ListBaaExecutionJournalOptions
2110): { query: string; params: D1Bindable[] } {
2111 const clauses: string[] = [];
2112 const params: D1Bindable[] = [];
2113
2114 if (options.kind != null) {
2115 clauses.push("summary_kind = ?");
2116 params.push(options.kind);
2117 }
2118
2119 const whereClause = clauses.length === 0 ? "" : `\nWHERE ${clauses.join("\n AND ")}`;
2120 const query = `${SELECT_BAA_EXECUTION_JOURNAL_PREFIX_SQL}${whereClause}
2121 ORDER BY ingested_at DESC, journal_id DESC
2122 LIMIT ?`;
2123
2124 params.push(options.limit ?? DEFAULT_BAA_EXECUTION_JOURNAL_LIMIT);
2125 return { query, params };
2126}
2127
2128function sqliteQueryMeta(extra: D1ResultMeta = {}): D1ResultMeta {
2129 return {
2130 changes: 0,
2131 changed_db: false,
2132 ...extra
2133 };
2134}
2135
2136export interface SqliteD1DatabaseOptions {
2137 schemaSql: string;
2138}
2139
2140class SqliteD1PreparedStatement implements D1PreparedStatementLike {
2141 constructor(
2142 private readonly db: DatabaseSync,
2143 private readonly query: string,
2144 private readonly params: D1Bindable[] = []
2145 ) {}
2146
2147 bind(...values: D1Bindable[]): D1PreparedStatementLike {
2148 return new SqliteD1PreparedStatement(this.db, this.query, values);
2149 }
2150
2151 async all<T = DatabaseRow>(): Promise<D1QueryResult<T>> {
2152 const statement = this.db.prepare(this.query);
2153 const results = statement.all(...this.params) as T[];
2154
2155 return {
2156 success: true,
2157 results,
2158 meta: sqliteQueryMeta({
2159 rows_read: results.length
2160 })
2161 };
2162 }
2163
2164 async first<T = DatabaseRow>(columnName?: string): Promise<T | null> {
2165 const statement = this.db.prepare(this.query);
2166 const row = statement.get(...this.params) as T | null;
2167
2168 if (row == null) {
2169 return null;
2170 }
2171
2172 if (!columnName) {
2173 return row;
2174 }
2175
2176 return ((row as DatabaseRow)[columnName] ?? null) as T | null;
2177 }
2178
2179 async raw<T = unknown[]>(options: { columnNames?: boolean } = {}): Promise<T[]> {
2180 const statement = this.db.prepare(this.query);
2181 const columns = statement.columns().map((column) => column.name) as unknown as T;
2182 statement.setReturnArrays(true);
2183
2184 const rows = statement.all(...this.params) as T[];
2185
2186 if (options.columnNames) {
2187 return [columns, ...rows];
2188 }
2189
2190 return rows;
2191 }
2192
2193 async run(): Promise<D1QueryResult<never>> {
2194 const statement = this.db.prepare(this.query);
2195 const result = statement.run(...this.params);
2196 const lastRowId =
2197 result.lastInsertRowid == null ? undefined : Number(result.lastInsertRowid);
2198
2199 return {
2200 success: true,
2201 meta: sqliteQueryMeta({
2202 changes: result.changes ?? 0,
2203 changed_db: (result.changes ?? 0) > 0,
2204 last_row_id: lastRowId,
2205 rows_written: result.changes ?? 0
2206 })
2207 };
2208 }
2209}
2210
2211export class SqliteD1Database implements D1DatabaseLike {
2212 private readonly db: DatabaseSync;
2213
2214 constructor(
2215 private readonly databasePath: string,
2216 options: SqliteD1DatabaseOptions
2217 ) {
2218 if (databasePath !== ":memory:") {
2219 mkdirSync(dirname(databasePath), {
2220 recursive: true
2221 });
2222 }
2223
2224 this.db = new DatabaseSync(databasePath);
2225 this.db.exec("PRAGMA foreign_keys = ON;");
2226 this.db.exec(options.schemaSql);
2227 }
2228
2229 prepare(query: string): D1PreparedStatementLike {
2230 return new SqliteD1PreparedStatement(this.db, query);
2231 }
2232
2233 async exec(query: string): Promise<D1ExecResult> {
2234 this.db.exec(query);
2235 return {
2236 count: 0,
2237 duration: 0
2238 };
2239 }
2240
2241 async batch<T = never>(statements: D1PreparedStatementLike[]): Promise<Array<D1QueryResult<T>>> {
2242 const results: Array<D1QueryResult<T>> = [];
2243 this.db.exec("BEGIN;");
2244
2245 try {
2246 for (const statement of statements) {
2247 results.push((await statement.run()) as D1QueryResult<T>);
2248 }
2249
2250 this.db.exec("COMMIT;");
2251 return results;
2252 } catch (error) {
2253 this.db.exec("ROLLBACK;");
2254 throw error;
2255 }
2256 }
2257
2258 close(): void {
2259 this.db.close();
2260 }
2261
2262 getDatabasePath(): string {
2263 return this.databasePath;
2264 }
2265}
2266
2267export class D1ControlPlaneRepository implements ControlPlaneRepository {
2268 private readonly baaExecutionJournalLimit: number;
2269 private readonly baaInstructionDedupeLimit: number;
2270 private readonly baaMessageDedupeLimit: number;
2271 private readonly db: D1DatabaseLike;
2272
2273 constructor(db: D1DatabaseLike, options: D1ControlPlaneRepositoryOptions = {}) {
2274 this.db = db;
2275 this.baaExecutionJournalLimit = normalizePositiveInteger(
2276 options.baaExecutionJournalLimit,
2277 DEFAULT_BAA_EXECUTION_JOURNAL_LIMIT
2278 );
2279 this.baaInstructionDedupeLimit = normalizePositiveInteger(
2280 options.baaInstructionDedupeLimit,
2281 DEFAULT_BAA_INSTRUCTION_DEDUPE_LIMIT
2282 );
2283 this.baaMessageDedupeLimit = normalizePositiveInteger(
2284 options.baaMessageDedupeLimit,
2285 DEFAULT_BAA_MESSAGE_DEDUPE_LIMIT
2286 );
2287 }
2288
2289 async ensureAutomationState(mode: AutomationMode = DEFAULT_AUTOMATION_MODE): Promise<void> {
2290 await this.run(ENSURE_AUTOMATION_STATE_SQL, [
2291 AUTOMATION_STATE_KEY,
2292 buildAutomationStateValue(mode),
2293 nowUnixMilliseconds()
2294 ]);
2295 }
2296
2297 async countActiveRuns(): Promise<number> {
2298 return this.count(SELECT_ACTIVE_RUN_COUNT_SQL);
2299 }
2300
2301 async countQueuedTasks(): Promise<number> {
2302 return this.count(SELECT_QUEUED_TASK_COUNT_SQL);
2303 }
2304
2305 async heartbeatController(input: ControllerHeartbeatInput): Promise<ControllerRecord> {
2306 const record = buildControllerHeartbeatRecord(input);
2307 await this.upsertController(record);
2308 return record;
2309 }
2310
2311 async acquireLeaderLease(input: LeaderLeaseAcquireInput): Promise<LeaderLeaseAcquireResult> {
2312 const now = input.now ?? nowUnixSeconds();
2313 const normalizedInput = { ...input, now };
2314 const currentLease = await this.getCurrentLease();
2315 const desiredLease = buildLeaderLeaseRecord(currentLease, normalizedInput);
2316
2317 await this.run(ACQUIRE_LEADER_LEASE_SQL, acquireLeaderLeaseParams(desiredLease, now));
2318
2319 const updatedLease = await this.getCurrentLease();
2320
2321 if (updatedLease == null) {
2322 throw new Error("leader_lease row was not available after acquire attempt.");
2323 }
2324
2325 return buildLeaderLeaseAcquireResult(currentLease, updatedLease, normalizedInput);
2326 }
2327
2328 async getAutomationState(): Promise<AutomationStateRecord | null> {
2329 const row = await this.fetchFirst(SELECT_SYSTEM_STATE_SQL, [AUTOMATION_STATE_KEY]);
2330 return row == null ? null : mapAutomationStateRow(row);
2331 }
2332
2333 async getBrowserEndpointMetadata(key: BrowserSessionKey): Promise<BrowserEndpointMetadataRecord | null> {
2334 const row = await this.fetchFirst(SELECT_BROWSER_ENDPOINT_METADATA_SQL, browserSessionKeyParams(key));
2335 return row == null ? null : mapBrowserEndpointMetadataRow(row);
2336 }
2337
2338 async getBrowserLoginState(key: BrowserSessionKey): Promise<BrowserLoginStateRecord | null> {
2339 const row = await this.fetchFirst(SELECT_BROWSER_LOGIN_STATE_SQL, browserSessionKeyParams(key));
2340 return row == null ? null : mapBrowserLoginStateRow(row);
2341 }
2342
2343 async getController(controllerId: string): Promise<ControllerRecord | null> {
2344 const row = await this.fetchFirst(SELECT_CONTROLLER_SQL, [controllerId]);
2345 return row == null ? null : mapControllerRow(row);
2346 }
2347
2348 async getCurrentLease(): Promise<LeaderLeaseRecord | null> {
2349 const row = await this.fetchFirst(SELECT_CURRENT_LEASE_SQL, [GLOBAL_LEASE_NAME]);
2350 return row == null ? null : mapLeaderLeaseRow(row);
2351 }
2352
2353 async getRun(runId: string): Promise<TaskRunRecord | null> {
2354 const row = await this.fetchFirst(SELECT_RUN_SQL, [runId]);
2355 return row == null ? null : mapTaskRunRow(row);
2356 }
2357
2358 async getSystemState(stateKey: string): Promise<SystemStateRecord | null> {
2359 const row = await this.fetchFirst(SELECT_SYSTEM_STATE_SQL, [stateKey]);
2360 return row == null ? null : mapSystemStateRow(row);
2361 }
2362
2363 async getTask(taskId: string): Promise<TaskRecord | null> {
2364 const row = await this.fetchFirst(SELECT_TASK_SQL, [taskId]);
2365 return row == null ? null : mapTaskRow(row);
2366 }
2367
2368 async hasBaaInstructionDedupe(dedupeKey: string): Promise<boolean> {
2369 return (await this.fetchFirst(SELECT_BAA_INSTRUCTION_DEDUPE_EXISTS_SQL, [dedupeKey])) != null;
2370 }
2371
2372 async hasBaaMessageDedupe(dedupeKey: string): Promise<boolean> {
2373 return (await this.fetchFirst(SELECT_BAA_MESSAGE_DEDUPE_EXISTS_SQL, [dedupeKey])) != null;
2374 }
2375
2376 async upsertBrowserEndpointMetadata(record: BrowserEndpointMetadataRecord): Promise<void> {
2377 await this.run(UPSERT_BROWSER_ENDPOINT_METADATA_SQL, browserEndpointMetadataParams(record));
2378 }
2379
2380 async upsertBrowserLoginState(record: BrowserLoginStateRecord): Promise<void> {
2381 await this.run(UPSERT_BROWSER_LOGIN_STATE_SQL, browserLoginStateParams(record));
2382 }
2383
2384 async putBaaInstructionDedupe(record: NewBaaInstructionDedupeRecord): Promise<void> {
2385 await this.run(INSERT_BAA_INSTRUCTION_DEDUPE_SQL, baaInstructionDedupeParams(record));
2386 await this.run(PRUNE_BAA_INSTRUCTION_DEDUPE_SQL, [this.baaInstructionDedupeLimit]);
2387 }
2388
2389 async putBaaMessageDedupe(record: NewBaaMessageDedupeRecord): Promise<void> {
2390 await this.run(INSERT_BAA_MESSAGE_DEDUPE_SQL, baaMessageDedupeParams(record));
2391 await this.run(PRUNE_BAA_MESSAGE_DEDUPE_SQL, [this.baaMessageDedupeLimit]);
2392 }
2393
2394 async insertTask(record: TaskRecord): Promise<void> {
2395 await this.run(INSERT_TASK_SQL, taskParams(record));
2396 }
2397
2398 async insertTaskArtifact(record: TaskArtifactRecord): Promise<void> {
2399 await this.run(INSERT_TASK_ARTIFACT_SQL, taskArtifactParams(record));
2400 }
2401
2402 async insertTaskCheckpoint(record: TaskCheckpointRecord): Promise<void> {
2403 await this.run(INSERT_TASK_CHECKPOINT_SQL, taskCheckpointParams(record));
2404 }
2405
2406 async insertTaskRun(record: TaskRunRecord): Promise<void> {
2407 await this.run(INSERT_TASK_RUN_SQL, taskRunParams(record));
2408 }
2409
2410 async insertTaskStep(record: TaskStepRecord): Promise<void> {
2411 await this.run(INSERT_TASK_STEP_SQL, taskStepParams(record));
2412 }
2413
2414 async insertTaskSteps(records: TaskStepRecord[]): Promise<void> {
2415 if (records.length === 0) {
2416 return;
2417 }
2418
2419 await this.db.batch(records.map((record) => this.bind(INSERT_TASK_STEP_SQL, taskStepParams(record))));
2420 }
2421
2422 async listControllers(options: ListControllersOptions = {}): Promise<ControllerRecord[]> {
2423 const rows = await this.fetchAll(SELECT_CONTROLLERS_SQL, [options.limit ?? 20]);
2424 return rows.map(mapControllerRow);
2425 }
2426
2427 async listBrowserEndpointMetadata(
2428 options: ListBrowserEndpointMetadataOptions = {}
2429 ): Promise<BrowserEndpointMetadataRecord[]> {
2430 const { query, params } = buildBrowserEndpointMetadataListQuery(options);
2431 const rows = await this.fetchAll(query, params);
2432 return rows.map(mapBrowserEndpointMetadataRow);
2433 }
2434
2435 async listBaaExecutionJournal(
2436 options: ListBaaExecutionJournalOptions = {}
2437 ): Promise<BaaExecutionJournalRecord[]> {
2438 const { query, params } = buildBaaExecutionJournalListQuery(options);
2439 const rows = await this.fetchAll(query, params);
2440 return rows.map(mapBaaExecutionJournalRow);
2441 }
2442
2443 async listBrowserLoginStates(
2444 options: ListBrowserLoginStatesOptions = {}
2445 ): Promise<BrowserLoginStateRecord[]> {
2446 const { query, params } = buildBrowserLoginStatesListQuery(options);
2447 const rows = await this.fetchAll(query, params);
2448 return rows.map(mapBrowserLoginStateRow);
2449 }
2450
2451 async listTaskLogs(taskId: string, options: ListTaskLogsOptions = {}): Promise<TaskLogRecord[]> {
2452 const limit = options.limit ?? 200;
2453 const query = options.runId == null ? SELECT_TASK_LOGS_SQL : SELECT_TASK_LOGS_BY_RUN_SQL;
2454 const params =
2455 options.runId == null ? [taskId, limit] : [taskId, options.runId, limit];
2456 const rows = await this.fetchAll(query, params);
2457 return rows.map(mapTaskLogRow);
2458 }
2459
2460 async listRuns(options: ListRunsOptions = {}): Promise<TaskRunRecord[]> {
2461 const rows = await this.fetchAll(SELECT_RUNS_SQL, [options.limit ?? 20]);
2462 return rows.map(mapTaskRunRow);
2463 }
2464
2465 async listTasks(options: ListTasksOptions = {}): Promise<TaskRecord[]> {
2466 const query = options.status == null ? SELECT_TASKS_SQL : SELECT_TASKS_BY_STATUS_SQL;
2467 const params = options.status == null ? [options.limit ?? 20] : [options.status, options.limit ?? 20];
2468 const rows = await this.fetchAll(query, params);
2469 return rows.map(mapTaskRow);
2470 }
2471
2472 async listTaskSteps(taskId: string): Promise<TaskStepRecord[]> {
2473 const rows = await this.fetchAll(SELECT_TASK_STEPS_SQL, [taskId]);
2474 return rows.map(mapTaskStepRow);
2475 }
2476
2477 async markBrowserLoginStatesLost(lastSeenBeforeOrAt: number): Promise<number> {
2478 const result = await this.run(UPDATE_BROWSER_LOGIN_STATES_LOST_SQL, [lastSeenBeforeOrAt]);
2479 return result.meta.changes ?? 0;
2480 }
2481
2482 async markBrowserLoginStatesStale(lastSeenBeforeOrAt: number): Promise<number> {
2483 const result = await this.run(UPDATE_BROWSER_LOGIN_STATES_STALE_SQL, [lastSeenBeforeOrAt]);
2484 return result.meta.changes ?? 0;
2485 }
2486
2487 async putLeaderLease(record: LeaderLeaseRecord): Promise<void> {
2488 if (record.leaseName !== GLOBAL_LEASE_NAME) {
2489 throw new RangeError(`leader_lease only supports lease_name="${GLOBAL_LEASE_NAME}".`);
2490 }
2491
2492 await this.run(UPSERT_LEADER_LEASE_SQL, leaderLeaseParams(record));
2493 }
2494
2495 async putSystemState(record: SystemStateRecord): Promise<void> {
2496 await this.run(UPSERT_SYSTEM_STATE_SQL, systemStateParams(record));
2497 }
2498
2499 async setAutomationMode(
2500 mode: AutomationMode,
2501 updatedAt: number = nowUnixMilliseconds()
2502 ): Promise<void> {
2503 await this.run(UPSERT_SYSTEM_STATE_SQL, [
2504 AUTOMATION_STATE_KEY,
2505 buildAutomationStateValue(mode),
2506 updatedAt
2507 ]);
2508 }
2509
2510 async upsertController(record: ControllerRecord): Promise<void> {
2511 await this.run(UPSERT_CONTROLLER_SQL, controllerParams(record));
2512 }
2513
2514 async upsertWorker(record: WorkerRecord): Promise<void> {
2515 await this.run(UPSERT_WORKER_SQL, workerParams(record));
2516 }
2517
2518 async appendTaskLog(record: NewTaskLogRecord): Promise<number | null> {
2519 const result = await this.run(INSERT_TASK_LOG_SQL, taskLogParams(record));
2520 return result.meta.last_row_id ?? null;
2521 }
2522
2523 async appendBaaExecutionJournal(record: NewBaaExecutionJournalRecord): Promise<number | null> {
2524 const result = await this.run(INSERT_BAA_EXECUTION_JOURNAL_SQL, baaExecutionJournalParams(record));
2525 await this.run(PRUNE_BAA_EXECUTION_JOURNAL_SQL, [this.baaExecutionJournalLimit]);
2526 return result.meta.last_row_id ?? null;
2527 }
2528
2529 private bind(query: string, params: readonly (D1Bindable | undefined)[]): D1PreparedStatementLike {
2530 return this.db.prepare(query).bind(...params.map(toD1Bindable));
2531 }
2532
2533 private async fetchAll(
2534 query: string,
2535 params: readonly (D1Bindable | undefined)[] = []
2536 ): Promise<DatabaseRow[]> {
2537 const result = await this.bind(query, params).all<DatabaseRow>();
2538 return result.results ?? [];
2539 }
2540
2541 private async fetchFirst(
2542 query: string,
2543 params: readonly (D1Bindable | undefined)[] = []
2544 ): Promise<DatabaseRow | null> {
2545 return this.bind(query, params).first<DatabaseRow>();
2546 }
2547
2548 private async count(
2549 query: string,
2550 params: readonly (D1Bindable | undefined)[] = []
2551 ): Promise<number> {
2552 const row = await this.fetchFirst(query, params);
2553 return row == null ? 0 : readRequiredNumber(row, "value");
2554 }
2555
2556 private async run(
2557 query: string,
2558 params: readonly (D1Bindable | undefined)[] = []
2559 ): Promise<D1QueryResult<never>> {
2560 return this.bind(query, params).run();
2561 }
2562}
2563
2564export function createD1ControlPlaneRepository(
2565 db: D1DatabaseLike,
2566 options: D1ControlPlaneRepositoryOptions = {}
2567): ControlPlaneRepository {
2568 return new D1ControlPlaneRepository(db, options);
2569}