codex@macbookpro
·
2026-04-01
store.ts
1import {
2 existsSync,
3 mkdirSync,
4 readFileSync,
5 rmSync,
6 writeFileSync
7} from "node:fs";
8import { dirname, join } from "node:path";
9import { DatabaseSync } from "node:sqlite";
10
11import { ARTIFACT_SCHEMA_SQL, CONVERSATION_LINK_INDEX_SQL } from "./schema.js";
12import {
13 buildArtifactRelativePath,
14 buildExecutionArtifactFiles,
15 buildMessageArtifactFiles,
16 buildSessionArtifactFiles,
17 buildSessionIndexArtifactFiles
18} from "./static-gen.js";
19import {
20 DEFAULT_SESSION_INDEX_LIMIT,
21 DEFAULT_SUMMARY_LENGTH,
22 type ArtifactStoreConfig,
23 type ArtifactTextFile,
24 type BrowserRequestPolicyStateRecord,
25 type ConversationAutomationExecutionState,
26 type ConversationAutomationStatus,
27 type ConversationPauseReason,
28 type ConversationLinkRecord,
29 type ExecutionRecord,
30 type ExecutionParamsKind,
31 type RecoverAutomationRuntimeStateInput,
32 type RecoverAutomationRuntimeStateResult,
33 type InsertExecutionInput,
34 type InsertMessageInput,
35 type InsertRenewalJobInput,
36 type ListConversationLinksOptions,
37 type ListExecutionsOptions,
38 type ListLocalConversationsOptions,
39 type ListMessagesOptions,
40 type ListRenewalJobsOptions,
41 type ListSessionsOptions,
42 type LocalConversationRecord,
43 type MessageScanCursor,
44 type MessageRecord,
45 type RenewalJobPayloadKind,
46 type RenewalJobRecord,
47 type RenewalJobStatus,
48 type ScanMessagesOptions,
49 type SessionIndexEntry,
50 type SessionRecord,
51 type SessionTimelineEntry,
52 type SyncEnqueuer,
53 type UpdateRenewalJobInput,
54 type UpsertBrowserRequestPolicyStateInput,
55 type UpsertConversationLinkInput,
56 type UpsertLocalConversationInput,
57 type UpsertSessionInput
58} from "./types.js";
59
60const INSERT_MESSAGE_SQL = `
61INSERT INTO messages (
62 id,
63 platform,
64 conversation_id,
65 role,
66 raw_text,
67 summary,
68 observed_at,
69 static_path,
70 page_url,
71 page_title,
72 organization_id,
73 created_at
74) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
75`;
76
77const INSERT_EXECUTION_SQL = `
78INSERT INTO executions (
79 instruction_id,
80 message_id,
81 target,
82 tool,
83 params,
84 params_kind,
85 result_ok,
86 result_data,
87 result_summary,
88 result_error,
89 http_status,
90 executed_at,
91 static_path,
92 created_at
93) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
94`;
95
96const UPSERT_SESSION_SQL = `
97INSERT INTO sessions (
98 id,
99 platform,
100 conversation_id,
101 started_at,
102 last_activity_at,
103 message_count,
104 execution_count,
105 summary,
106 static_path,
107 created_at
108) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
109ON CONFLICT(id) DO UPDATE SET
110 platform = excluded.platform,
111 conversation_id = excluded.conversation_id,
112 started_at = excluded.started_at,
113 last_activity_at = excluded.last_activity_at,
114 message_count = excluded.message_count,
115 execution_count = excluded.execution_count,
116 summary = excluded.summary,
117 static_path = excluded.static_path,
118 created_at = COALESCE(sessions.created_at, excluded.created_at);
119`;
120
121const UPSERT_LOCAL_CONVERSATION_SQL = `
122INSERT INTO local_conversations (
123 local_conversation_id,
124 platform,
125 automation_status,
126 last_non_paused_automation_status,
127 pause_reason,
128 last_error,
129 execution_state,
130 consecutive_failure_count,
131 repeated_message_count,
132 repeated_renewal_count,
133 last_message_fingerprint,
134 last_renewal_fingerprint,
135 title,
136 summary,
137 last_message_id,
138 last_message_at,
139 cooldown_until,
140 paused_at,
141 created_at,
142 updated_at
143) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
144ON CONFLICT(local_conversation_id) DO UPDATE SET
145 platform = excluded.platform,
146 automation_status = excluded.automation_status,
147 last_non_paused_automation_status = excluded.last_non_paused_automation_status,
148 pause_reason = excluded.pause_reason,
149 last_error = excluded.last_error,
150 execution_state = excluded.execution_state,
151 consecutive_failure_count = excluded.consecutive_failure_count,
152 repeated_message_count = excluded.repeated_message_count,
153 repeated_renewal_count = excluded.repeated_renewal_count,
154 last_message_fingerprint = excluded.last_message_fingerprint,
155 last_renewal_fingerprint = excluded.last_renewal_fingerprint,
156 title = excluded.title,
157 summary = excluded.summary,
158 last_message_id = excluded.last_message_id,
159 last_message_at = excluded.last_message_at,
160 cooldown_until = excluded.cooldown_until,
161 paused_at = excluded.paused_at,
162 created_at = COALESCE(local_conversations.created_at, excluded.created_at),
163 updated_at = excluded.updated_at;
164`;
165
166const UPSERT_CONVERSATION_LINK_SQL = `
167INSERT INTO conversation_links (
168 link_id,
169 local_conversation_id,
170 platform,
171 remote_conversation_id,
172 client_id,
173 page_url,
174 page_title,
175 route_path,
176 route_pattern,
177 route_params,
178 target_kind,
179 target_id,
180 target_payload,
181 is_active,
182 observed_at,
183 created_at,
184 updated_at
185) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
186ON CONFLICT(link_id) DO UPDATE SET
187 local_conversation_id = excluded.local_conversation_id,
188 platform = excluded.platform,
189 remote_conversation_id = excluded.remote_conversation_id,
190 client_id = excluded.client_id,
191 page_url = excluded.page_url,
192 page_title = excluded.page_title,
193 route_path = excluded.route_path,
194 route_pattern = excluded.route_pattern,
195 route_params = excluded.route_params,
196 target_kind = excluded.target_kind,
197 target_id = excluded.target_id,
198 target_payload = excluded.target_payload,
199 is_active = excluded.is_active,
200 observed_at = excluded.observed_at,
201 created_at = COALESCE(conversation_links.created_at, excluded.created_at),
202 updated_at = excluded.updated_at;
203`;
204
205const UPDATE_CONVERSATION_LINK_SQL = `
206UPDATE conversation_links
207SET
208 local_conversation_id = ?,
209 platform = ?,
210 remote_conversation_id = ?,
211 client_id = ?,
212 page_url = ?,
213 page_title = ?,
214 route_path = ?,
215 route_pattern = ?,
216 route_params = ?,
217 target_kind = ?,
218 target_id = ?,
219 target_payload = ?,
220 is_active = ?,
221 observed_at = ?,
222 created_at = ?,
223 updated_at = ?
224WHERE link_id = ?;
225`;
226
227const TRY_BEGIN_LOCAL_CONVERSATION_EXECUTION_SQL = `
228UPDATE local_conversations
229SET
230 execution_state = ?,
231 updated_at = ?
232WHERE local_conversation_id = ?
233 AND execution_state = 'idle';
234`;
235
236const FINISH_LOCAL_CONVERSATION_EXECUTION_SQL = `
237UPDATE local_conversations
238SET
239 execution_state = 'idle',
240 updated_at = ?
241WHERE local_conversation_id = ?
242 AND execution_state = ?;
243`;
244
245const INSERT_RENEWAL_JOB_SQL = `
246INSERT INTO renewal_jobs (
247 job_id,
248 local_conversation_id,
249 message_id,
250 status,
251 payload,
252 payload_kind,
253 target_snapshot,
254 attempt_count,
255 max_attempts,
256 next_attempt_at,
257 last_attempt_at,
258 last_error,
259 log_path,
260 started_at,
261 finished_at,
262 created_at,
263 updated_at
264) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);
265`;
266
267const UPSERT_RENEWAL_JOB_SQL = `
268INSERT INTO renewal_jobs (
269 job_id,
270 local_conversation_id,
271 message_id,
272 status,
273 payload,
274 payload_kind,
275 target_snapshot,
276 attempt_count,
277 max_attempts,
278 next_attempt_at,
279 last_attempt_at,
280 last_error,
281 log_path,
282 started_at,
283 finished_at,
284 created_at,
285 updated_at
286) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
287ON CONFLICT(job_id) DO UPDATE SET
288 local_conversation_id = excluded.local_conversation_id,
289 message_id = excluded.message_id,
290 status = excluded.status,
291 payload = excluded.payload,
292 payload_kind = excluded.payload_kind,
293 target_snapshot = excluded.target_snapshot,
294 attempt_count = excluded.attempt_count,
295 max_attempts = excluded.max_attempts,
296 next_attempt_at = excluded.next_attempt_at,
297 last_attempt_at = excluded.last_attempt_at,
298 last_error = excluded.last_error,
299 log_path = excluded.log_path,
300 started_at = excluded.started_at,
301 finished_at = excluded.finished_at,
302 created_at = COALESCE(renewal_jobs.created_at, excluded.created_at),
303 updated_at = excluded.updated_at;
304`;
305
306const UPSERT_BROWSER_REQUEST_POLICY_STATE_SQL = `
307INSERT INTO browser_request_policy_state (
308 state_key,
309 value_json,
310 updated_at
311) VALUES (?, ?, ?)
312ON CONFLICT(state_key) DO UPDATE SET
313 value_json = excluded.value_json,
314 updated_at = excluded.updated_at;
315`;
316
317const DEFAULT_RENEWAL_RECOVERY_DELAY_MS = 60_000;
318const RECOVER_RUNNING_RENEWAL_JOBS_SQL = `
319UPDATE renewal_jobs
320SET
321 status = 'pending',
322 next_attempt_at = CASE
323 WHEN next_attempt_at IS NOT NULL AND next_attempt_at > ? THEN next_attempt_at
324 ELSE ?
325 END,
326 started_at = NULL,
327 finished_at = NULL,
328 updated_at = ?
329WHERE status = 'running';
330`;
331
332const RECOVER_LOCAL_CONVERSATION_EXECUTION_SQL = `
333UPDATE local_conversations
334SET
335 execution_state = 'idle',
336 updated_at = CASE
337 WHEN updated_at > ? THEN updated_at
338 ELSE ?
339 END
340WHERE execution_state != 'idle';
341`;
342
343interface FileMutation {
344 existed: boolean;
345 path: string;
346 previousContent: Uint8Array | null;
347}
348
349interface MessageRow {
350 conversation_id: string | null;
351 created_at: number;
352 id: string;
353 observed_at: number;
354 organization_id: string | null;
355 page_title: string | null;
356 page_url: string | null;
357 platform: string;
358 raw_text: string;
359 role: string;
360 static_path: string;
361 summary: string | null;
362}
363
364interface ExecutionRow {
365 created_at: number;
366 executed_at: number;
367 http_status: number | null;
368 instruction_id: string;
369 message_id: string;
370 params: string | null;
371 params_kind: ExecutionParamsKind;
372 result_data: string | null;
373 result_error: string | null;
374 result_ok: number;
375 result_summary: string | null;
376 static_path: string;
377 target: string;
378 tool: string;
379}
380
381interface SessionRow {
382 conversation_id: string | null;
383 created_at: number;
384 execution_count: number;
385 id: string;
386 last_activity_at: number;
387 message_count: number;
388 platform: string;
389 started_at: number;
390 static_path: string;
391 summary: string | null;
392}
393
394interface LocalConversationRow {
395 automation_status: ConversationAutomationStatus;
396 consecutive_failure_count: number;
397 cooldown_until: number | null;
398 created_at: number;
399 execution_state: ConversationAutomationExecutionState;
400 last_error: string | null;
401 last_message_fingerprint: string | null;
402 last_message_at: number | null;
403 last_message_id: string | null;
404 last_non_paused_automation_status: ConversationAutomationStatus;
405 last_renewal_fingerprint: string | null;
406 local_conversation_id: string;
407 paused_at: number | null;
408 pause_reason: ConversationPauseReason | null;
409 platform: string;
410 repeated_message_count: number;
411 repeated_renewal_count: number;
412 summary: string | null;
413 title: string | null;
414 updated_at: number;
415}
416
417interface ConversationLinkRow {
418 client_id: string | null;
419 created_at: number;
420 is_active: number;
421 link_id: string;
422 local_conversation_id: string;
423 observed_at: number;
424 page_title: string | null;
425 page_url: string | null;
426 platform: string;
427 remote_conversation_id: string | null;
428 route_params: string | null;
429 route_path: string | null;
430 route_pattern: string | null;
431 target_id: string | null;
432 target_kind: string | null;
433 target_payload: string | null;
434 updated_at: number;
435}
436
437interface RenewalJobRow {
438 attempt_count: number;
439 created_at: number;
440 finished_at: number | null;
441 job_id: string;
442 last_attempt_at: number | null;
443 last_error: string | null;
444 local_conversation_id: string;
445 log_path: string | null;
446 max_attempts: number;
447 message_id: string;
448 next_attempt_at: number | null;
449 payload: string;
450 payload_kind: RenewalJobPayloadKind;
451 started_at: number | null;
452 status: RenewalJobStatus;
453 target_snapshot: string | null;
454 updated_at: number;
455}
456
457interface BrowserRequestPolicyStateRow {
458 state_key: string;
459 value_json: string;
460 updated_at: number;
461}
462
463interface TimelineMessageRow {
464 id: string;
465 observed_at: number;
466 page_title: string | null;
467 page_url: string | null;
468 role: string;
469 static_path: string;
470 summary: string | null;
471}
472
473interface TimelineExecutionRow {
474 executed_at: number;
475 instruction_id: string;
476 message_id: string;
477 result_ok: number;
478 result_summary: string | null;
479 static_path: string;
480 target: string;
481 tool: string;
482}
483
484interface LatestMessageRow {
485 id: string;
486 static_path: string;
487}
488
489interface CountRow {
490 count: number;
491}
492
493export class ArtifactStore {
494 private readonly artifactDir: string;
495 private readonly db: DatabaseSync;
496 private readonly publicBaseUrl: string | null;
497 private readonly sessionIndexLimit: number;
498 private readonly summaryLength: number;
499 private syncQueue: SyncEnqueuer | null = null;
500
501 constructor(config: ArtifactStoreConfig) {
502 const databasePath = normalizeRequiredPath(config.databasePath, "databasePath");
503 this.artifactDir = normalizeRequiredPath(config.artifactDir, "artifactDir");
504 this.publicBaseUrl = normalizeOptionalBaseUrl(config.publicBaseUrl);
505 this.sessionIndexLimit = normalizePositiveInteger(config.sessionIndexLimit, DEFAULT_SESSION_INDEX_LIMIT);
506 this.summaryLength = normalizePositiveInteger(config.summaryLength, DEFAULT_SUMMARY_LENGTH);
507
508 mkdirSync(dirname(databasePath), {
509 recursive: true
510 });
511 mkdirSync(this.artifactDir, {
512 recursive: true
513 });
514 mkdirSync(join(this.artifactDir, "msg"), {
515 recursive: true
516 });
517 mkdirSync(join(this.artifactDir, "exec"), {
518 recursive: true
519 });
520 mkdirSync(join(this.artifactDir, "session"), {
521 recursive: true
522 });
523
524 this.db = new DatabaseSync(databasePath);
525 this.db.exec("PRAGMA foreign_keys = ON;");
526 this.db.exec(ARTIFACT_SCHEMA_SQL);
527 this.ensureLocalConversationColumns();
528 this.ensureConversationLinkIndexes();
529 }
530
531 close(): void {
532 this.db.close();
533 }
534
535 getArtifactsDir(): string {
536 return this.artifactDir;
537 }
538
539 getPublicBaseUrl(): string | null {
540 return this.publicBaseUrl;
541 }
542
543 setSyncQueue(queue: SyncEnqueuer | null): void {
544 this.syncQueue = queue;
545 }
546
547 async getExecution(instructionId: string): Promise<ExecutionRecord | null> {
548 const row = this.getRow<ExecutionRow>(
549 "SELECT * FROM executions WHERE instruction_id = ? LIMIT 1;",
550 instructionId
551 );
552 return row == null ? null : mapExecutionRow(row);
553 }
554
555 async getLatestSessions(limit: number = this.sessionIndexLimit): Promise<SessionRecord[]> {
556 const rows = this.getRows<SessionRow>(
557 `
558 SELECT *
559 FROM sessions
560 ORDER BY last_activity_at DESC, created_at DESC
561 LIMIT ?;
562 `,
563 normalizePositiveInteger(limit, this.sessionIndexLimit)
564 );
565 return rows.map(mapSessionRow);
566 }
567
568 async getMessage(id: string): Promise<MessageRecord | null> {
569 const row = this.getRow<MessageRow>("SELECT * FROM messages WHERE id = ? LIMIT 1;", id);
570 return row == null ? null : mapMessageRow(row);
571 }
572
573 async getLocalConversation(localConversationId: string): Promise<LocalConversationRecord | null> {
574 const row = this.getRow<LocalConversationRow>(
575 "SELECT * FROM local_conversations WHERE local_conversation_id = ? LIMIT 1;",
576 localConversationId
577 );
578 return row == null ? null : mapLocalConversationRow(row);
579 }
580
581 async findLocalConversationByLastMessageId(messageId: string): Promise<LocalConversationRecord | null> {
582 const row = this.getRow<LocalConversationRow>(
583 `
584 SELECT *
585 FROM local_conversations
586 WHERE last_message_id = ?
587 ORDER BY updated_at DESC, created_at DESC
588 LIMIT 1;
589 `,
590 normalizeRequiredString(messageId, "messageId")
591 );
592 return row == null ? null : mapLocalConversationRow(row);
593 }
594
595 async getConversationLink(linkId: string): Promise<ConversationLinkRecord | null> {
596 const row = this.getRow<ConversationLinkRow>(
597 "SELECT * FROM conversation_links WHERE link_id = ? LIMIT 1;",
598 linkId
599 );
600 return row == null ? null : mapConversationLinkRow(row);
601 }
602
603 async findConversationLinkByRemoteConversation(
604 platform: string,
605 remoteConversationId: string
606 ): Promise<ConversationLinkRecord | null> {
607 const row = this.getRow<ConversationLinkRow>(
608 `
609 SELECT *
610 FROM conversation_links
611 WHERE platform = ?
612 AND remote_conversation_id = ?
613 AND is_active = 1
614 LIMIT 1;
615 `,
616 normalizeRequiredString(platform, "platform"),
617 normalizeRequiredString(remoteConversationId, "remoteConversationId")
618 );
619 return row == null ? null : mapConversationLinkRow(row);
620 }
621
622 async getRenewalJob(jobId: string): Promise<RenewalJobRecord | null> {
623 const row = this.getRow<RenewalJobRow>(
624 "SELECT * FROM renewal_jobs WHERE job_id = ? LIMIT 1;",
625 jobId
626 );
627 return row == null ? null : mapRenewalJobRow(row);
628 }
629
630 async getBrowserRequestPolicyState(
631 stateKey: string
632 ): Promise<BrowserRequestPolicyStateRecord | null> {
633 const row = this.getRow<BrowserRequestPolicyStateRow>(
634 "SELECT * FROM browser_request_policy_state WHERE state_key = ? LIMIT 1;",
635 normalizeRequiredString(stateKey, "stateKey")
636 );
637 return row == null ? null : mapBrowserRequestPolicyStateRow(row);
638 }
639
640 async insertExecution(input: InsertExecutionInput): Promise<ExecutionRecord> {
641 const record = buildExecutionRecord(input, this.summaryLength);
642 const message = await this.getMessage(record.messageId);
643
644 if (message == null) {
645 throw new Error(`Execution "${record.instructionId}" references unknown message "${record.messageId}".`);
646 }
647
648 this.executeWrite((mutations) => {
649 this.run(INSERT_EXECUTION_SQL, executionParams(record));
650 this.writeArtifactFiles(
651 buildExecutionArtifactFiles(record, this.renderConfig(), message.staticPath),
652 mutations
653 );
654 this.writeSessionArtifacts(
655 this.buildDerivedSessionRecord({
656 conversationId: message.conversationId,
657 createdAtCandidate: message.createdAt,
658 lastActivityAt: record.executedAt,
659 platform: message.platform,
660 startedAtCandidate: message.observedAt,
661 summaryCandidate: record.resultSummary ?? message.summary
662 }),
663 mutations
664 );
665 this.writeArtifactFiles(
666 buildSessionIndexArtifactFiles(this.readSessionIndexEntries(this.sessionIndexLimit), Date.now(), this.renderConfig()),
667 mutations
668 );
669 });
670
671 this.enqueueSync("executions", record.instructionId, executionSyncPayload(record));
672
673 return record;
674 }
675
676 async insertMessage(input: InsertMessageInput): Promise<MessageRecord> {
677 const record = buildMessageRecord(input, this.summaryLength);
678
679 this.executeWrite((mutations) => {
680 this.run(INSERT_MESSAGE_SQL, messageParams(record));
681 this.writeArtifactFiles(buildMessageArtifactFiles(record, this.renderConfig()), mutations);
682 this.writeSessionArtifacts(
683 this.buildDerivedSessionRecord({
684 conversationId: record.conversationId,
685 createdAtCandidate: record.createdAt,
686 lastActivityAt: record.observedAt,
687 platform: record.platform,
688 startedAtCandidate: record.observedAt,
689 summaryCandidate: record.summary
690 }),
691 mutations
692 );
693 this.writeArtifactFiles(
694 buildSessionIndexArtifactFiles(this.readSessionIndexEntries(this.sessionIndexLimit), Date.now(), this.renderConfig()),
695 mutations
696 );
697 });
698
699 this.enqueueSync("messages", record.id, messageSyncPayload(record));
700
701 return record;
702 }
703
704 async upsertLocalConversation(
705 input: UpsertLocalConversationInput
706 ): Promise<LocalConversationRecord> {
707 const existing = await this.getLocalConversation(input.localConversationId);
708 const record = buildLocalConversationRecord(input, existing);
709
710 this.executeWrite(() => {
711 this.run(UPSERT_LOCAL_CONVERSATION_SQL, localConversationParams(record));
712 });
713
714 this.enqueueSync(
715 "local_conversations",
716 record.localConversationId,
717 localConversationSyncPayload(record)
718 );
719
720 return record;
721 }
722
723 async tryBeginLocalConversationExecution(input: {
724 executionState: ConversationAutomationExecutionState;
725 localConversationId: string;
726 updatedAt?: number;
727 }): Promise<LocalConversationRecord | null> {
728 const localConversationId = normalizeRequiredString(input.localConversationId, "localConversationId");
729 const executionState = normalizeExecutionState(input.executionState);
730 const updatedAt = normalizeNonNegativeInteger(input.updatedAt ?? Date.now(), 0, "updatedAt");
731 const result = this.db.prepare(TRY_BEGIN_LOCAL_CONVERSATION_EXECUTION_SQL).run(
732 executionState,
733 updatedAt,
734 localConversationId
735 );
736
737 if (result.changes === 0) {
738 return null;
739 }
740
741 const record = await this.getLocalConversation(localConversationId);
742
743 if (record != null) {
744 this.enqueueSync(
745 "local_conversations",
746 record.localConversationId,
747 localConversationSyncPayload(record),
748 "update"
749 );
750 }
751
752 return record;
753 }
754
755 async finishLocalConversationExecution(input: {
756 executionState: ConversationAutomationExecutionState;
757 localConversationId: string;
758 updatedAt?: number;
759 }): Promise<LocalConversationRecord | null> {
760 const localConversationId = normalizeRequiredString(input.localConversationId, "localConversationId");
761 const executionState = normalizeExecutionState(input.executionState);
762 const updatedAt = normalizeNonNegativeInteger(input.updatedAt ?? Date.now(), 0, "updatedAt");
763 const result = this.db.prepare(FINISH_LOCAL_CONVERSATION_EXECUTION_SQL).run(
764 updatedAt,
765 localConversationId,
766 executionState
767 );
768
769 if (result.changes === 0) {
770 return this.getLocalConversation(localConversationId);
771 }
772
773 const record = await this.getLocalConversation(localConversationId);
774
775 if (record != null) {
776 this.enqueueSync(
777 "local_conversations",
778 record.localConversationId,
779 localConversationSyncPayload(record),
780 "update"
781 );
782 }
783
784 return record;
785 }
786
787 async upsertConversationLink(
788 input: UpsertConversationLinkInput
789 ): Promise<ConversationLinkRecord> {
790 const existing = this.resolveExistingConversationLink(input);
791 const record = buildConversationLinkRecord(input, existing);
792
793 this.executeWrite(() => {
794 this.run(UPSERT_CONVERSATION_LINK_SQL, conversationLinkParams(record));
795 });
796
797 this.enqueueSync(
798 "conversation_links",
799 record.linkId,
800 conversationLinkSyncPayload(record)
801 );
802
803 return record;
804 }
805
806 async insertRenewalJob(input: InsertRenewalJobInput): Promise<RenewalJobRecord> {
807 const record = buildRenewalJobRecord(input);
808
809 this.executeWrite(() => {
810 this.run(INSERT_RENEWAL_JOB_SQL, renewalJobParams(record));
811 });
812
813 this.enqueueSync("renewal_jobs", record.jobId, renewalJobSyncPayload(record));
814
815 return record;
816 }
817
818 async upsertBrowserRequestPolicyState(
819 input: UpsertBrowserRequestPolicyStateInput
820 ): Promise<BrowserRequestPolicyStateRecord> {
821 const record = buildBrowserRequestPolicyStateRecord(input);
822
823 this.executeWrite(() => {
824 this.run(
825 UPSERT_BROWSER_REQUEST_POLICY_STATE_SQL,
826 browserRequestPolicyStateParams(record)
827 );
828 });
829
830 this.enqueueSync(
831 "browser_request_policy_state",
832 record.stateKey,
833 browserRequestPolicyStateSyncPayload(record),
834 "update"
835 );
836
837 return record;
838 }
839
840 async listExecutions(options: ListExecutionsOptions = {}): Promise<ExecutionRecord[]> {
841 const query = [
842 "SELECT * FROM executions",
843 buildWhereClause(
844 [
845 buildCondition("message_id", options.messageId),
846 buildCondition("target", options.target),
847 buildCondition("tool", options.tool)
848 ],
849 "AND"
850 ),
851 "ORDER BY executed_at DESC, created_at DESC",
852 "LIMIT ?",
853 "OFFSET ?"
854 ]
855 .filter(Boolean)
856 .join(" ");
857
858 const rows = this.getRows<ExecutionRow>(
859 query,
860 ...buildQueryParams(
861 [options.messageId ?? undefined, options.target ?? undefined, options.tool ?? undefined],
862 normalizeLimit(options.limit),
863 normalizeOffset(options.offset)
864 )
865 );
866
867 return rows.map(mapExecutionRow);
868 }
869
870 async listMessages(options: ListMessagesOptions = {}): Promise<MessageRecord[]> {
871 const { clause, params } = buildConversationFilters(options.platform, options.conversationId);
872 const rows = this.getRows<MessageRow>(
873 [
874 "SELECT * FROM messages",
875 clause ? `WHERE ${clause}` : "",
876 "ORDER BY observed_at DESC, created_at DESC",
877 "LIMIT ?",
878 "OFFSET ?"
879 ]
880 .filter(Boolean)
881 .join(" "),
882 ...params,
883 normalizeLimit(options.limit),
884 normalizeOffset(options.offset)
885 );
886
887 return rows.map(mapMessageRow);
888 }
889
890 async scanMessages(options: ScanMessagesOptions = {}): Promise<MessageRecord[]> {
891 const conditions: string[] = [];
892 const params: Array<number | string | null> = [];
893 const after = normalizeMessageScanCursor(options.after);
894
895 if (options.platform != null) {
896 conditions.push("platform = ?");
897 params.push(options.platform);
898 }
899
900 if (options.role != null) {
901 conditions.push("role = ?");
902 params.push(options.role);
903 }
904
905 if (options.observedAtLte != null) {
906 conditions.push("observed_at <= ?");
907 params.push(normalizeNonNegativeInteger(options.observedAtLte, 0, "observedAtLte"));
908 }
909
910 if (after != null) {
911 conditions.push("(observed_at > ? OR (observed_at = ? AND id > ?))");
912 params.push(after.observedAt, after.observedAt, after.id);
913 }
914
915 const rows = this.getRows<MessageRow>(
916 [
917 "SELECT * FROM messages",
918 conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "",
919 "ORDER BY observed_at ASC, id ASC",
920 "LIMIT ?"
921 ]
922 .filter(Boolean)
923 .join(" "),
924 ...params,
925 normalizePositiveInteger(options.limit, 50)
926 );
927
928 return rows.map(mapMessageRow);
929 }
930
931 async listSessions(options: ListSessionsOptions = {}): Promise<SessionRecord[]> {
932 const { clause, params } = buildConversationFilters(options.platform, options.conversationId);
933 const rows = this.getRows<SessionRow>(
934 [
935 "SELECT * FROM sessions",
936 clause ? `WHERE ${clause}` : "",
937 "ORDER BY last_activity_at DESC, created_at DESC",
938 "LIMIT ?",
939 "OFFSET ?"
940 ]
941 .filter(Boolean)
942 .join(" "),
943 ...params,
944 normalizeLimit(options.limit),
945 normalizeOffset(options.offset)
946 );
947
948 return rows.map(mapSessionRow);
949 }
950
951 async listLocalConversations(
952 options: ListLocalConversationsOptions = {}
953 ): Promise<LocalConversationRecord[]> {
954 const conditions: string[] = [];
955 const params: Array<number | string | null> = [];
956
957 if (options.platform != null) {
958 conditions.push("platform = ?");
959 params.push(options.platform);
960 }
961
962 if (options.automationStatus != null) {
963 conditions.push("automation_status = ?");
964 params.push(options.automationStatus);
965 }
966
967 const rows = this.getRows<LocalConversationRow>(
968 [
969 "SELECT * FROM local_conversations",
970 conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "",
971 "ORDER BY updated_at DESC, created_at DESC",
972 "LIMIT ?",
973 "OFFSET ?"
974 ]
975 .filter(Boolean)
976 .join(" "),
977 ...params,
978 normalizeLimit(options.limit),
979 normalizeOffset(options.offset)
980 );
981
982 return rows.map(mapLocalConversationRow);
983 }
984
985 async listConversationLinks(
986 options: ListConversationLinksOptions = {}
987 ): Promise<ConversationLinkRecord[]> {
988 const query = [
989 "SELECT * FROM conversation_links",
990 buildWhereClause(
991 [
992 buildCondition("local_conversation_id", options.localConversationId),
993 buildCondition("platform", options.platform),
994 buildCondition("remote_conversation_id", options.remoteConversationId),
995 buildCondition("client_id", options.clientId),
996 buildCondition("page_title", options.pageTitle),
997 buildCondition("page_url", options.pageUrl),
998 buildCondition("route_path", options.routePath),
999 buildCondition("target_id", options.targetId),
1000 options.isActive == null ? null : "is_active = ?"
1001 ],
1002 "AND"
1003 ),
1004 "ORDER BY observed_at DESC, updated_at DESC",
1005 "LIMIT ?",
1006 "OFFSET ?"
1007 ]
1008 .filter(Boolean)
1009 .join(" ");
1010
1011 const rows = this.getRows<ConversationLinkRow>(
1012 query,
1013 ...buildQueryParams(
1014 [
1015 options.localConversationId ?? undefined,
1016 options.platform ?? undefined,
1017 options.remoteConversationId ?? undefined,
1018 options.clientId ?? undefined,
1019 options.pageTitle ?? undefined,
1020 options.pageUrl ?? undefined,
1021 options.routePath ?? undefined,
1022 options.targetId ?? undefined,
1023 options.isActive == null ? undefined : (options.isActive ? 1 : 0)
1024 ],
1025 normalizeLimit(options.limit),
1026 normalizeOffset(options.offset)
1027 )
1028 );
1029
1030 return rows.map(mapConversationLinkRow);
1031 }
1032
1033 async listRenewalJobs(options: ListRenewalJobsOptions = {}): Promise<RenewalJobRecord[]> {
1034 const conditions: string[] = [];
1035 const params: Array<number | string | null> = [];
1036
1037 if (options.localConversationId != null) {
1038 conditions.push("local_conversation_id = ?");
1039 params.push(options.localConversationId);
1040 }
1041
1042 if (options.messageId != null) {
1043 conditions.push("message_id = ?");
1044 params.push(options.messageId);
1045 }
1046
1047 if (options.status != null) {
1048 conditions.push("status = ?");
1049 params.push(options.status);
1050 }
1051
1052 if (options.nextAttemptAtLte != null) {
1053 conditions.push("next_attempt_at IS NOT NULL");
1054 conditions.push("next_attempt_at <= ?");
1055 params.push(options.nextAttemptAtLte);
1056 }
1057
1058 const rows = this.getRows<RenewalJobRow>(
1059 [
1060 "SELECT * FROM renewal_jobs",
1061 conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "",
1062 options.nextAttemptAtLte == null
1063 ? "ORDER BY created_at DESC, updated_at DESC"
1064 : "ORDER BY next_attempt_at ASC, created_at ASC",
1065 "LIMIT ?",
1066 "OFFSET ?"
1067 ]
1068 .filter(Boolean)
1069 .join(" "),
1070 ...params,
1071 normalizeLimit(options.limit),
1072 normalizeOffset(options.offset)
1073 );
1074
1075 return rows.map(mapRenewalJobRow);
1076 }
1077
1078 async updateRenewalJob(input: UpdateRenewalJobInput): Promise<RenewalJobRecord> {
1079 const existing = await this.getRenewalJob(input.jobId);
1080
1081 if (existing == null) {
1082 throw new Error(`Renewal job "${input.jobId}" does not exist.`);
1083 }
1084
1085 const record = buildUpdatedRenewalJobRecord(input, existing);
1086
1087 this.executeWrite(() => {
1088 this.run(UPSERT_RENEWAL_JOB_SQL, renewalJobParams(record));
1089 });
1090
1091 this.enqueueSync("renewal_jobs", record.jobId, renewalJobSyncPayload(record), "update");
1092
1093 return record;
1094 }
1095
1096 async recoverAutomationRuntimeState(
1097 input: RecoverAutomationRuntimeStateInput = {}
1098 ): Promise<RecoverAutomationRuntimeStateResult> {
1099 const now = normalizeNonNegativeInteger(input.now ?? Date.now(), 0, "now");
1100 const renewalRecoveryDelayMs = normalizePositiveInteger(
1101 input.renewalRecoveryDelayMs,
1102 DEFAULT_RENEWAL_RECOVERY_DELAY_MS
1103 );
1104 const recoveryNextAttemptAt = now + renewalRecoveryDelayMs;
1105 const lockedConversationIds = this.getRows<{ local_conversation_id: string }>(
1106 `
1107 SELECT local_conversation_id
1108 FROM local_conversations
1109 WHERE execution_state != 'idle';
1110 `
1111 ).map((row) => row.local_conversation_id);
1112 const runningJobIds = this.getRows<{ job_id: string }>(
1113 `
1114 SELECT job_id
1115 FROM renewal_jobs
1116 WHERE status = 'running';
1117 `
1118 ).map((row) => row.job_id);
1119
1120 if (lockedConversationIds.length === 0 && runningJobIds.length === 0) {
1121 return {
1122 recoveredExecutionStateCount: 0,
1123 recoveryNextAttemptAt,
1124 requeuedRenewalJobCount: 0
1125 };
1126 }
1127
1128 this.db.exec("BEGIN;");
1129
1130 try {
1131 this.db.prepare(RECOVER_RUNNING_RENEWAL_JOBS_SQL).run(
1132 now,
1133 recoveryNextAttemptAt,
1134 now
1135 );
1136 this.db.prepare(RECOVER_LOCAL_CONVERSATION_EXECUTION_SQL).run(
1137 now,
1138 now
1139 );
1140 this.db.exec("COMMIT;");
1141 } catch (error) {
1142 this.rollbackQuietly();
1143 throw error;
1144 }
1145
1146 for (const localConversationId of lockedConversationIds) {
1147 const record = await this.getLocalConversation(localConversationId);
1148
1149 if (record == null) {
1150 continue;
1151 }
1152
1153 this.enqueueSync(
1154 "local_conversations",
1155 record.localConversationId,
1156 localConversationSyncPayload(record),
1157 "update"
1158 );
1159 }
1160
1161 for (const jobId of runningJobIds) {
1162 const record = await this.getRenewalJob(jobId);
1163
1164 if (record == null) {
1165 continue;
1166 }
1167
1168 this.enqueueSync(
1169 "renewal_jobs",
1170 record.jobId,
1171 renewalJobSyncPayload(record),
1172 "update"
1173 );
1174 }
1175
1176 return {
1177 recoveredExecutionStateCount: lockedConversationIds.length,
1178 recoveryNextAttemptAt,
1179 requeuedRenewalJobCount: runningJobIds.length
1180 };
1181 }
1182
1183 async upsertSession(input: UpsertSessionInput): Promise<SessionRecord> {
1184 const record = buildSessionRecord(input, this.summaryLength);
1185
1186 this.executeWrite((mutations) => {
1187 this.writeSessionArtifacts(record, mutations);
1188 this.writeArtifactFiles(
1189 buildSessionIndexArtifactFiles(this.readSessionIndexEntries(this.sessionIndexLimit), Date.now(), this.renderConfig()),
1190 mutations
1191 );
1192 });
1193
1194 this.enqueueSync("sessions", record.id, sessionSyncPayload(record));
1195
1196 return record;
1197 }
1198
1199 private executeWrite(operation: (mutations: FileMutation[]) => void): void {
1200 const mutations: FileMutation[] = [];
1201 this.db.exec("BEGIN;");
1202
1203 try {
1204 operation(mutations);
1205 this.db.exec("COMMIT;");
1206 } catch (error) {
1207 this.rollbackQuietly();
1208 this.restoreFiles(mutations);
1209 throw error;
1210 }
1211 }
1212
1213 private ensureLocalConversationColumns(): void {
1214 const existingColumns = new Set(
1215 this.getRows<{ name: string }>("PRAGMA table_info(local_conversations);").map((row) => row.name)
1216 );
1217 const requiredColumns = [
1218 "ALTER TABLE local_conversations "
1219 + "ADD COLUMN last_non_paused_automation_status TEXT NOT NULL DEFAULT 'manual';",
1220 "ALTER TABLE local_conversations ADD COLUMN pause_reason TEXT;",
1221 "ALTER TABLE local_conversations ADD COLUMN last_error TEXT;",
1222 "ALTER TABLE local_conversations "
1223 + "ADD COLUMN execution_state TEXT NOT NULL DEFAULT 'idle';",
1224 "ALTER TABLE local_conversations "
1225 + "ADD COLUMN consecutive_failure_count INTEGER NOT NULL DEFAULT 0;",
1226 "ALTER TABLE local_conversations "
1227 + "ADD COLUMN repeated_message_count INTEGER NOT NULL DEFAULT 0;",
1228 "ALTER TABLE local_conversations "
1229 + "ADD COLUMN repeated_renewal_count INTEGER NOT NULL DEFAULT 0;",
1230 "ALTER TABLE local_conversations ADD COLUMN last_message_fingerprint TEXT;",
1231 "ALTER TABLE local_conversations ADD COLUMN last_renewal_fingerprint TEXT;"
1232 ] as const;
1233 const columnNames = [
1234 "last_non_paused_automation_status",
1235 "pause_reason",
1236 "last_error",
1237 "execution_state",
1238 "consecutive_failure_count",
1239 "repeated_message_count",
1240 "repeated_renewal_count",
1241 "last_message_fingerprint",
1242 "last_renewal_fingerprint"
1243 ] as const;
1244
1245 this.db.exec("BEGIN;");
1246
1247 try {
1248 for (let index = 0; index < columnNames.length; index += 1) {
1249 const columnName = columnNames[index]!;
1250
1251 if (existingColumns.has(columnName)) {
1252 continue;
1253 }
1254
1255 this.db.exec(requiredColumns[index]!);
1256 }
1257
1258 this.db.exec(
1259 "CREATE INDEX IF NOT EXISTS idx_local_conversations_last_message_id "
1260 + "ON local_conversations(last_message_id);"
1261 );
1262 this.db.exec("COMMIT;");
1263 } catch (error) {
1264 this.rollbackQuietly();
1265 throw error;
1266 }
1267 }
1268
1269 private ensureConversationLinkIndexes(): void {
1270 this.db.exec("BEGIN;");
1271
1272 try {
1273 this.deduplicateNullRemoteConversationLinks();
1274 this.db.exec(CONVERSATION_LINK_INDEX_SQL);
1275 this.db.exec("COMMIT;");
1276 } catch (error) {
1277 this.rollbackQuietly();
1278 throw error;
1279 }
1280 }
1281
1282 private getRow<T>(query: string, ...params: Array<number | string | null>): T | null {
1283 const statement = this.db.prepare(query);
1284 return (statement.get(...params) as T | undefined) ?? null;
1285 }
1286
1287 private getRows<T>(query: string, ...params: Array<number | string | null>): T[] {
1288 const statement = this.db.prepare(query);
1289 return statement.all(...params) as T[];
1290 }
1291
1292 private deduplicateNullRemoteConversationLinks(): void {
1293 const rows = this.getRows<ConversationLinkRow>(
1294 `
1295 SELECT *
1296 FROM conversation_links
1297 WHERE remote_conversation_id IS NULL
1298 ORDER BY updated_at DESC, observed_at DESC, created_at DESC, link_id DESC;
1299 `
1300 );
1301 const canonicalByKey = new Map<string, ConversationLinkRecord>();
1302
1303 for (const row of rows) {
1304 const candidate = mapConversationLinkRow(row);
1305 const dedupeKey = buildNullRemoteConversationLinkDedupeKey(candidate);
1306
1307 if (dedupeKey == null) {
1308 continue;
1309 }
1310
1311 const canonical = canonicalByKey.get(dedupeKey);
1312
1313 if (canonical == null) {
1314 canonicalByKey.set(dedupeKey, candidate);
1315 continue;
1316 }
1317
1318 const merged = mergeConversationLinkRecords(canonical, candidate);
1319 canonicalByKey.set(dedupeKey, merged);
1320 this.run(UPDATE_CONVERSATION_LINK_SQL, [...conversationLinkParams(merged).slice(1), merged.linkId]);
1321 this.run("DELETE FROM conversation_links WHERE link_id = ?;", [candidate.linkId]);
1322 }
1323 }
1324
1325 private resolveExistingConversationLink(
1326 input: UpsertConversationLinkInput
1327 ): ConversationLinkRecord | null {
1328 const byId = this.getRow<ConversationLinkRow>(
1329 "SELECT * FROM conversation_links WHERE link_id = ? LIMIT 1;",
1330 normalizeRequiredString(input.linkId, "linkId")
1331 );
1332
1333 const remoteConversationId = normalizeOptionalString(input.remoteConversationId);
1334 if (remoteConversationId == null) {
1335 const byNullRemoteIdentity = this.findConversationLinkByNullRemoteIdentity(input);
1336 return byNullRemoteIdentity == null
1337 ? byId == null
1338 ? null
1339 : mapConversationLinkRow(byId)
1340 : byNullRemoteIdentity;
1341 }
1342
1343 const byRemote = this.getRow<ConversationLinkRow>(
1344 `
1345 SELECT *
1346 FROM conversation_links
1347 WHERE platform = ?
1348 AND remote_conversation_id = ?
1349 LIMIT 1;
1350 `,
1351 normalizeRequiredString(input.platform, "platform"),
1352 remoteConversationId
1353 );
1354
1355 return byRemote == null
1356 ? byId == null
1357 ? null
1358 : mapConversationLinkRow(byId)
1359 : mapConversationLinkRow(byRemote);
1360 }
1361
1362 private findConversationLinkByNullRemoteIdentity(
1363 input: Pick<
1364 UpsertConversationLinkInput,
1365 "clientId" | "pageUrl" | "platform" | "routePath" | "targetId"
1366 >
1367 ): ConversationLinkRecord | null {
1368 const platform = normalizeRequiredString(input.platform, "platform");
1369 const clientId = normalizeOptionalString(input.clientId);
1370 const clientScope = normalizeConversationLinkClientScope(clientId);
1371 const routePath = normalizeOptionalString(input.routePath);
1372 const pageUrl = normalizeOptionalString(input.pageUrl);
1373 const targetId = normalizeOptionalString(input.targetId);
1374
1375 if (routePath != null) {
1376 const row = this.getRow<ConversationLinkRow>(
1377 `
1378 SELECT *
1379 FROM conversation_links
1380 WHERE platform = ?
1381 AND remote_conversation_id IS NULL
1382 AND COALESCE(client_id, '') = ?
1383 AND route_path = ?
1384 ORDER BY updated_at DESC, observed_at DESC, created_at DESC, link_id DESC
1385 LIMIT 1;
1386 `,
1387 platform,
1388 clientScope,
1389 routePath
1390 );
1391
1392 if (row != null) {
1393 return mapConversationLinkRow(row);
1394 }
1395 }
1396
1397 if (pageUrl != null) {
1398 const row = this.getRow<ConversationLinkRow>(
1399 `
1400 SELECT *
1401 FROM conversation_links
1402 WHERE platform = ?
1403 AND remote_conversation_id IS NULL
1404 AND COALESCE(client_id, '') = ?
1405 AND route_path IS NULL
1406 AND page_url = ?
1407 ORDER BY updated_at DESC, observed_at DESC, created_at DESC, link_id DESC
1408 LIMIT 1;
1409 `,
1410 platform,
1411 clientScope,
1412 pageUrl
1413 );
1414
1415 if (row != null) {
1416 return mapConversationLinkRow(row);
1417 }
1418 }
1419
1420 if (targetId != null) {
1421 const row = this.getRow<ConversationLinkRow>(
1422 `
1423 SELECT *
1424 FROM conversation_links
1425 WHERE platform = ?
1426 AND remote_conversation_id IS NULL
1427 AND COALESCE(client_id, '') = ?
1428 AND route_path IS NULL
1429 AND page_url IS NULL
1430 AND target_id = ?
1431 ORDER BY updated_at DESC, observed_at DESC, created_at DESC, link_id DESC
1432 LIMIT 1;
1433 `,
1434 platform,
1435 clientScope,
1436 targetId
1437 );
1438
1439 if (row != null) {
1440 return mapConversationLinkRow(row);
1441 }
1442 }
1443
1444 if (clientId == null) {
1445 return null;
1446 }
1447
1448 const row = this.getRow<ConversationLinkRow>(
1449 `
1450 SELECT *
1451 FROM conversation_links
1452 WHERE platform = ?
1453 AND remote_conversation_id IS NULL
1454 AND COALESCE(client_id, '') = ?
1455 AND route_path IS NULL
1456 AND page_url IS NULL
1457 AND target_id IS NULL
1458 ORDER BY updated_at DESC, observed_at DESC, created_at DESC, link_id DESC
1459 LIMIT 1;
1460 `,
1461 platform,
1462 clientScope
1463 );
1464
1465 return row == null ? null : mapConversationLinkRow(row);
1466 }
1467
1468 private readLatestMessageForSession(session: SessionRecord): LatestMessageRow | null {
1469 const { clause, params } = buildConversationFilters(session.platform, session.conversationId);
1470 return this.getRow<LatestMessageRow>(
1471 [
1472 "SELECT id, static_path",
1473 "FROM messages",
1474 clause ? `WHERE ${clause}` : "",
1475 "ORDER BY observed_at DESC, created_at DESC",
1476 "LIMIT 1"
1477 ]
1478 .filter(Boolean)
1479 .join(" "),
1480 ...params
1481 );
1482 }
1483
1484 private buildDerivedSessionRecord(input: {
1485 conversationId: string | null;
1486 createdAtCandidate: number;
1487 lastActivityAt: number;
1488 platform: string;
1489 startedAtCandidate: number;
1490 summaryCandidate: string | null;
1491 }): SessionRecord {
1492 const id = buildSessionId(input.platform, input.conversationId);
1493 const existing = this.getRow<SessionRow>("SELECT * FROM sessions WHERE id = ? LIMIT 1;", id);
1494 const existingRecord = existing == null ? null : mapSessionRow(existing);
1495 const messageCount = this.countMessages(input.platform, input.conversationId);
1496 const executionCount = this.countExecutions(input.platform, input.conversationId);
1497
1498 return buildSessionRecord(
1499 {
1500 conversationId: input.conversationId,
1501 createdAt: existingRecord?.createdAt ?? input.createdAtCandidate,
1502 executionCount,
1503 id,
1504 lastActivityAt:
1505 existingRecord == null
1506 ? input.lastActivityAt
1507 : Math.max(existingRecord.lastActivityAt, input.lastActivityAt),
1508 messageCount,
1509 platform: input.platform,
1510 startedAt:
1511 existingRecord == null
1512 ? input.startedAtCandidate
1513 : Math.min(existingRecord.startedAt, input.startedAtCandidate),
1514 summary: resolveSessionSummary(existingRecord, input.summaryCandidate, input.lastActivityAt)
1515 },
1516 this.summaryLength
1517 );
1518 }
1519
1520 private countExecutions(platform: string, conversationId: string | null): number {
1521 const { clause, params } = buildConversationFilters(platform, conversationId);
1522 const executionClause =
1523 clause === ""
1524 ? ""
1525 : `WHERE ${clause
1526 .replaceAll("platform", "m.platform")
1527 .replaceAll("conversation_id", "m.conversation_id")}`;
1528 const row = this.getRow<CountRow>(
1529 [
1530 "SELECT COUNT(*) AS count",
1531 "FROM executions e",
1532 "INNER JOIN messages m ON m.id = e.message_id",
1533 executionClause
1534 ]
1535 .filter(Boolean)
1536 .join(" "),
1537 ...params
1538 );
1539
1540 return row?.count ?? 0;
1541 }
1542
1543 private countMessages(platform: string, conversationId: string | null): number {
1544 const { clause, params } = buildConversationFilters(platform, conversationId);
1545 const row = this.getRow<CountRow>(
1546 [
1547 "SELECT COUNT(*) AS count",
1548 "FROM messages",
1549 clause === "" ? "" : `WHERE ${clause}`
1550 ]
1551 .filter(Boolean)
1552 .join(" "),
1553 ...params
1554 );
1555
1556 return row?.count ?? 0;
1557 }
1558
1559 private readSessionIndexEntries(limit: number): SessionIndexEntry[] {
1560 const sessions = this.getRows<SessionRow>(
1561 `
1562 SELECT *
1563 FROM sessions
1564 ORDER BY last_activity_at DESC, created_at DESC
1565 LIMIT ?;
1566 `,
1567 limit
1568 ).map(mapSessionRow);
1569
1570 return sessions.map((session) => {
1571 const latestMessage = this.readLatestMessageForSession(session);
1572
1573 return {
1574 conversationId: session.conversationId,
1575 executionCount: session.executionCount,
1576 id: session.id,
1577 lastActivityAt: session.lastActivityAt,
1578 latestMessageId: latestMessage?.id ?? null,
1579 latestMessageStaticPath: latestMessage?.static_path ?? null,
1580 messageCount: session.messageCount,
1581 platform: session.platform,
1582 staticPath: session.staticPath,
1583 summary: session.summary
1584 };
1585 });
1586 }
1587
1588 private readSessionTimeline(session: SessionRecord): SessionTimelineEntry[] {
1589 const { clause, params } = buildConversationFilters(session.platform, session.conversationId);
1590 const messageRows = this.getRows<TimelineMessageRow>(
1591 [
1592 "SELECT id, role, summary, observed_at, static_path, page_url, page_title",
1593 "FROM messages",
1594 clause ? `WHERE ${clause}` : "",
1595 "ORDER BY observed_at ASC, created_at ASC"
1596 ]
1597 .filter(Boolean)
1598 .join(" "),
1599 ...params
1600 );
1601 const executionRows = this.getRows<TimelineExecutionRow>(
1602 [
1603 "SELECT e.instruction_id, e.message_id, e.target, e.tool, e.result_ok, e.result_summary, e.executed_at, e.static_path",
1604 "FROM executions e",
1605 "INNER JOIN messages m ON m.id = e.message_id",
1606 clause ? `WHERE ${clause.replaceAll("platform", "m.platform").replaceAll("conversation_id", "m.conversation_id")}` : "",
1607 "ORDER BY e.executed_at ASC, e.created_at ASC"
1608 ]
1609 .filter(Boolean)
1610 .join(" "),
1611 ...params
1612 );
1613
1614 const timeline: SessionTimelineEntry[] = [
1615 ...messageRows.map((row) => ({
1616 id: row.id,
1617 kind: "message" as const,
1618 observedAt: row.observed_at,
1619 pageTitle: row.page_title,
1620 pageUrl: row.page_url,
1621 role: row.role,
1622 staticPath: row.static_path,
1623 summary: row.summary
1624 })),
1625 ...executionRows.map((row) => ({
1626 executedAt: row.executed_at,
1627 instructionId: row.instruction_id,
1628 kind: "execution" as const,
1629 messageId: row.message_id,
1630 resultOk: row.result_ok === 1,
1631 resultSummary: row.result_summary,
1632 staticPath: row.static_path,
1633 target: row.target,
1634 tool: row.tool
1635 }))
1636 ];
1637
1638 return timeline.sort((left, right) => {
1639 const leftTime = left.kind === "message" ? left.observedAt : left.executedAt;
1640 const rightTime = right.kind === "message" ? right.observedAt : right.executedAt;
1641 return leftTime - rightTime;
1642 });
1643 }
1644
1645 private enqueueSync(
1646 tableName: string,
1647 recordId: string,
1648 payload: Record<string, unknown>,
1649 operation: "insert" | "update" = "insert"
1650 ): void {
1651 if (this.syncQueue == null) {
1652 return;
1653 }
1654
1655 try {
1656 this.syncQueue.enqueueSyncRecord({
1657 tableName,
1658 recordId,
1659 operation,
1660 payload
1661 });
1662 } catch {
1663 // Best-effort: enqueue failure must not affect the already-committed local write.
1664 }
1665 }
1666
1667 private renderConfig(): { publicBaseUrl: string | null } {
1668 return {
1669 publicBaseUrl: this.publicBaseUrl
1670 };
1671 }
1672
1673 private writeSessionArtifacts(record: SessionRecord, mutations: FileMutation[]): void {
1674 this.run(UPSERT_SESSION_SQL, sessionParams(record));
1675 this.writeArtifactFiles(
1676 buildSessionArtifactFiles(record, this.readSessionTimeline(record), this.renderConfig()),
1677 mutations
1678 );
1679 }
1680
1681 private restoreFiles(mutations: FileMutation[]): void {
1682 for (let index = mutations.length - 1; index >= 0; index -= 1) {
1683 const mutation = mutations[index];
1684
1685 if (!mutation) {
1686 continue;
1687 }
1688
1689 if (!mutation.existed) {
1690 rmSync(mutation.path, {
1691 force: true
1692 });
1693 continue;
1694 }
1695
1696 if (mutation.previousContent != null) {
1697 writeFileSync(mutation.path, mutation.previousContent);
1698 }
1699 }
1700 }
1701
1702 private rollbackQuietly(): void {
1703 try {
1704 this.db.exec("ROLLBACK;");
1705 } catch {
1706 // Ignore rollback failures during error handling.
1707 }
1708 }
1709
1710 private run(query: string, params: Array<number | string | null>): void {
1711 this.db.prepare(query).run(...params);
1712 }
1713
1714 private writeArtifactFiles(files: ArtifactTextFile[], mutations: FileMutation[]): void {
1715 for (const file of files) {
1716 const path = join(this.artifactDir, file.relativePath);
1717 const existed = existsSync(path);
1718 mutations.push({
1719 existed,
1720 path,
1721 previousContent: existed ? readFileSync(path) : null
1722 });
1723 writeFileSync(path, file.content);
1724 }
1725 }
1726}
1727
1728function buildCondition(field: string, value: string | undefined): string | null {
1729 return value == null ? null : `${field} = ?`;
1730}
1731
1732function buildConversationFilters(
1733 platform?: string,
1734 conversationId?: string | null
1735): {
1736 clause: string;
1737 params: Array<string | null>;
1738} {
1739 const conditions: string[] = [];
1740 const params: Array<string | null> = [];
1741
1742 if (platform != null) {
1743 conditions.push("platform = ?");
1744 params.push(platform);
1745 }
1746
1747 if (conversationId === null) {
1748 conditions.push("conversation_id IS NULL");
1749 } else if (conversationId != null) {
1750 conditions.push("conversation_id = ?");
1751 params.push(conversationId);
1752 }
1753
1754 return {
1755 clause: conditions.join(" AND "),
1756 params
1757 };
1758}
1759
1760function buildExecutionRecord(input: InsertExecutionInput, summaryLength: number): ExecutionRecord {
1761 const params = stringifyUnknown(input.params);
1762 const resultData = stringifyUnknown(input.resultData);
1763 const resultSummary =
1764 normalizeOptionalString(input.resultSummary) ?? summarizeText(resultData ?? input.resultError ?? "", summaryLength);
1765
1766 return {
1767 createdAt: input.createdAt ?? Date.now(),
1768 executedAt: input.executedAt,
1769 httpStatus: input.httpStatus ?? null,
1770 instructionId: normalizeRequiredString(input.instructionId, "instructionId"),
1771 messageId: normalizeRequiredString(input.messageId, "messageId"),
1772 params,
1773 paramsKind: input.paramsKind ?? (params == null ? "none" : "inline_json"),
1774 resultData,
1775 resultError: normalizeOptionalString(input.resultError),
1776 resultOk: input.resultOk,
1777 resultSummary,
1778 staticPath: buildArtifactRelativePath("exec", `${normalizeRequiredString(input.instructionId, "instructionId")}.txt`),
1779 target: normalizeRequiredString(input.target, "target"),
1780 tool: normalizeRequiredString(input.tool, "tool")
1781 };
1782}
1783
1784function buildMessageRecord(input: InsertMessageInput, summaryLength: number): MessageRecord {
1785 const id = normalizeRequiredString(input.id, "id");
1786
1787 return {
1788 conversationId: normalizeOptionalString(input.conversationId),
1789 createdAt: input.createdAt ?? Date.now(),
1790 id,
1791 observedAt: input.observedAt,
1792 organizationId: normalizeOptionalString(input.organizationId),
1793 pageTitle: normalizeOptionalString(input.pageTitle),
1794 pageUrl: normalizeOptionalString(input.pageUrl),
1795 platform: normalizeRequiredString(input.platform, "platform"),
1796 rawText: normalizeRequiredString(input.rawText, "rawText"),
1797 role: normalizeRequiredString(input.role, "role"),
1798 staticPath: buildArtifactRelativePath("msg", `${id}.txt`),
1799 summary:
1800 normalizeOptionalString(input.summary) ?? summarizeText(normalizeRequiredString(input.rawText, "rawText"), summaryLength)
1801 };
1802}
1803
1804function buildQueryParams(
1805 values: Array<number | string | undefined>,
1806 limit: number,
1807 offset: number
1808): Array<number | string | null> {
1809 return [...values.filter((value): value is number | string => value != null), limit, offset];
1810}
1811
1812function buildSessionRecord(input: UpsertSessionInput, summaryLength: number): SessionRecord {
1813 const id = normalizeRequiredString(input.id, "id");
1814
1815 return {
1816 conversationId: normalizeOptionalString(input.conversationId),
1817 createdAt: input.createdAt ?? Date.now(),
1818 executionCount: input.executionCount ?? 0,
1819 id,
1820 lastActivityAt: input.lastActivityAt,
1821 messageCount: input.messageCount ?? 0,
1822 platform: normalizeRequiredString(input.platform, "platform"),
1823 startedAt: input.startedAt,
1824 staticPath: buildArtifactRelativePath("session", `${id}.txt`),
1825 summary: normalizeOptionalString(input.summary) ?? null
1826 };
1827}
1828
1829function buildLocalConversationRecord(
1830 input: UpsertLocalConversationInput,
1831 existing: LocalConversationRecord | null
1832): LocalConversationRecord {
1833 const createdAt = input.createdAt ?? existing?.createdAt ?? Date.now();
1834 const automationStatus = input.automationStatus ?? existing?.automationStatus ?? "manual";
1835 const lastNonPausedAutomationStatus =
1836 input.lastNonPausedAutomationStatus
1837 ?? (automationStatus === "paused"
1838 ? (existing?.lastNonPausedAutomationStatus ?? existing?.automationStatus ?? "manual")
1839 : automationStatus);
1840
1841 return {
1842 automationStatus,
1843 consecutiveFailureCount: input.consecutiveFailureCount == null
1844 ? existing?.consecutiveFailureCount ?? 0
1845 : normalizeNonNegativeInteger(
1846 input.consecutiveFailureCount,
1847 existing?.consecutiveFailureCount ?? 0,
1848 "consecutiveFailureCount"
1849 ),
1850 cooldownUntil: mergeOptionalInteger(input.cooldownUntil, existing?.cooldownUntil ?? null, "cooldownUntil"),
1851 createdAt,
1852 executionState: input.executionState == null
1853 ? existing?.executionState ?? "idle"
1854 : normalizeExecutionState(input.executionState),
1855 lastError: mergeOptionalString(input.lastError, existing?.lastError ?? null),
1856 lastMessageFingerprint: mergeOptionalString(
1857 input.lastMessageFingerprint,
1858 existing?.lastMessageFingerprint ?? null
1859 ),
1860 lastMessageAt: mergeOptionalInteger(input.lastMessageAt, existing?.lastMessageAt ?? null, "lastMessageAt"),
1861 lastMessageId: mergeOptionalString(input.lastMessageId, existing?.lastMessageId ?? null),
1862 lastNonPausedAutomationStatus,
1863 lastRenewalFingerprint: mergeOptionalString(
1864 input.lastRenewalFingerprint,
1865 existing?.lastRenewalFingerprint ?? null
1866 ),
1867 localConversationId: normalizeRequiredString(input.localConversationId, "localConversationId"),
1868 pausedAt: mergeOptionalInteger(input.pausedAt, existing?.pausedAt ?? null, "pausedAt"),
1869 pauseReason: mergeOptionalPauseReason(input.pauseReason, existing?.pauseReason ?? null),
1870 platform: normalizeRequiredString(input.platform, "platform"),
1871 repeatedMessageCount: input.repeatedMessageCount == null
1872 ? existing?.repeatedMessageCount ?? 0
1873 : normalizeNonNegativeInteger(
1874 input.repeatedMessageCount,
1875 existing?.repeatedMessageCount ?? 0,
1876 "repeatedMessageCount"
1877 ),
1878 repeatedRenewalCount: input.repeatedRenewalCount == null
1879 ? existing?.repeatedRenewalCount ?? 0
1880 : normalizeNonNegativeInteger(
1881 input.repeatedRenewalCount,
1882 existing?.repeatedRenewalCount ?? 0,
1883 "repeatedRenewalCount"
1884 ),
1885 summary: mergeOptionalString(input.summary, existing?.summary ?? null),
1886 title: mergeOptionalString(input.title, existing?.title ?? null),
1887 updatedAt: Math.max(existing?.updatedAt ?? 0, input.updatedAt ?? Date.now())
1888 };
1889}
1890
1891function buildConversationLinkRecord(
1892 input: UpsertConversationLinkInput,
1893 existing: ConversationLinkRecord | null
1894): ConversationLinkRecord {
1895 const createdAt = existing?.createdAt ?? input.createdAt ?? Date.now();
1896
1897 return {
1898 clientId: mergeOptionalString(input.clientId, existing?.clientId ?? null),
1899 createdAt,
1900 isActive: input.isActive ?? existing?.isActive ?? true,
1901 linkId: existing?.linkId ?? normalizeRequiredString(input.linkId, "linkId"),
1902 localConversationId: normalizeRequiredString(input.localConversationId, "localConversationId"),
1903 observedAt: input.observedAt,
1904 pageTitle: mergeOptionalString(input.pageTitle, existing?.pageTitle ?? null),
1905 pageUrl: mergeOptionalString(input.pageUrl, existing?.pageUrl ?? null),
1906 platform: normalizeRequiredString(input.platform, "platform"),
1907 remoteConversationId: mergeOptionalString(
1908 input.remoteConversationId,
1909 existing?.remoteConversationId ?? null
1910 ),
1911 routeParams: mergeOptionalSerialized(input.routeParams, existing?.routeParams ?? null),
1912 routePath: mergeOptionalString(input.routePath, existing?.routePath ?? null),
1913 routePattern: mergeOptionalString(input.routePattern, existing?.routePattern ?? null),
1914 targetId: mergeOptionalString(input.targetId, existing?.targetId ?? null),
1915 targetKind: mergeOptionalString(input.targetKind, existing?.targetKind ?? null),
1916 targetPayload: mergeOptionalSerialized(input.targetPayload, existing?.targetPayload ?? null),
1917 updatedAt: input.updatedAt ?? Date.now()
1918 };
1919}
1920
1921function buildNullRemoteConversationLinkDedupeKey(
1922 record: Pick<
1923 ConversationLinkRecord,
1924 "clientId" | "pageUrl" | "platform" | "remoteConversationId" | "routePath" | "targetId"
1925 >
1926): string | null {
1927 if (record.remoteConversationId != null) {
1928 return null;
1929 }
1930
1931 const clientScope = normalizeConversationLinkClientScope(record.clientId);
1932 const routePath = normalizeOptionalString(record.routePath);
1933
1934 if (routePath != null) {
1935 return `${record.platform}\u0000${clientScope}\u0000route\u0000${routePath}`;
1936 }
1937
1938 const pageUrl = normalizeOptionalString(record.pageUrl);
1939
1940 if (pageUrl != null) {
1941 return `${record.platform}\u0000${clientScope}\u0000page\u0000${pageUrl}`;
1942 }
1943
1944 const targetId = normalizeOptionalString(record.targetId);
1945
1946 if (targetId != null) {
1947 return `${record.platform}\u0000${clientScope}\u0000target\u0000${targetId}`;
1948 }
1949
1950 if (clientScope === "") {
1951 return null;
1952 }
1953
1954 return `${record.platform}\u0000${clientScope}\u0000client`;
1955}
1956
1957function mergeConversationLinkRecords(
1958 canonical: ConversationLinkRecord,
1959 duplicate: ConversationLinkRecord
1960): ConversationLinkRecord {
1961 return {
1962 ...canonical,
1963 clientId: canonical.clientId ?? duplicate.clientId,
1964 createdAt: Math.min(canonical.createdAt, duplicate.createdAt),
1965 pageTitle: canonical.pageTitle ?? duplicate.pageTitle,
1966 pageUrl: canonical.pageUrl ?? duplicate.pageUrl,
1967 routeParams: canonical.routeParams ?? duplicate.routeParams,
1968 routePath: canonical.routePath ?? duplicate.routePath,
1969 routePattern: canonical.routePattern ?? duplicate.routePattern,
1970 targetId: canonical.targetId ?? duplicate.targetId,
1971 targetKind: canonical.targetKind ?? duplicate.targetKind,
1972 targetPayload: canonical.targetPayload ?? duplicate.targetPayload
1973 };
1974}
1975
1976function normalizeConversationLinkClientScope(value: string | null | undefined): string {
1977 return normalizeOptionalString(value) ?? "";
1978}
1979
1980function buildRenewalJobRecord(input: InsertRenewalJobInput): RenewalJobRecord {
1981 const createdAt = input.createdAt ?? Date.now();
1982
1983 return {
1984 attemptCount: normalizeNonNegativeInteger(input.attemptCount, 0, "attemptCount"),
1985 createdAt,
1986 finishedAt: normalizeOptionalInteger(input.finishedAt, "finishedAt"),
1987 jobId: normalizeRequiredString(input.jobId, "jobId"),
1988 lastAttemptAt: normalizeOptionalInteger(input.lastAttemptAt, "lastAttemptAt"),
1989 lastError: normalizeOptionalString(input.lastError),
1990 localConversationId: normalizeRequiredString(input.localConversationId, "localConversationId"),
1991 logPath: normalizeOptionalString(input.logPath),
1992 maxAttempts: normalizePositiveInteger(input.maxAttempts, 3),
1993 messageId: normalizeRequiredString(input.messageId, "messageId"),
1994 nextAttemptAt: input.nextAttemptAt === undefined
1995 ? createdAt
1996 : normalizeOptionalInteger(input.nextAttemptAt, "nextAttemptAt"),
1997 payload: normalizeRequiredString(input.payload, "payload"),
1998 payloadKind: input.payloadKind ?? "text",
1999 startedAt: normalizeOptionalInteger(input.startedAt, "startedAt"),
2000 status: input.status ?? "pending",
2001 targetSnapshot: stringifyUnknown(input.targetSnapshot),
2002 updatedAt: input.updatedAt ?? createdAt
2003 };
2004}
2005
2006function buildUpdatedRenewalJobRecord(
2007 input: UpdateRenewalJobInput,
2008 existing: RenewalJobRecord
2009): RenewalJobRecord {
2010 return {
2011 attemptCount: input.attemptCount == null
2012 ? existing.attemptCount
2013 : normalizeNonNegativeInteger(input.attemptCount, existing.attemptCount, "attemptCount"),
2014 createdAt: existing.createdAt,
2015 finishedAt: mergeOptionalInteger(input.finishedAt, existing.finishedAt, "finishedAt"),
2016 jobId: existing.jobId,
2017 lastAttemptAt: mergeOptionalInteger(input.lastAttemptAt, existing.lastAttemptAt, "lastAttemptAt"),
2018 lastError: mergeOptionalString(input.lastError, existing.lastError),
2019 localConversationId: existing.localConversationId,
2020 logPath: mergeOptionalString(input.logPath, existing.logPath),
2021 maxAttempts: input.maxAttempts == null
2022 ? existing.maxAttempts
2023 : normalizePositiveInteger(input.maxAttempts, existing.maxAttempts),
2024 messageId: existing.messageId,
2025 nextAttemptAt: mergeOptionalInteger(input.nextAttemptAt, existing.nextAttemptAt, "nextAttemptAt"),
2026 payload: input.payload == null ? existing.payload : normalizeRequiredString(input.payload, "payload"),
2027 payloadKind: input.payloadKind ?? existing.payloadKind,
2028 startedAt: mergeOptionalInteger(input.startedAt, existing.startedAt, "startedAt"),
2029 status: input.status ?? existing.status,
2030 targetSnapshot: mergeOptionalSerialized(input.targetSnapshot, existing.targetSnapshot),
2031 updatedAt: input.updatedAt ?? Date.now()
2032 };
2033}
2034
2035function buildBrowserRequestPolicyStateRecord(
2036 input: UpsertBrowserRequestPolicyStateInput
2037): BrowserRequestPolicyStateRecord {
2038 return {
2039 stateKey: normalizeRequiredString(input.stateKey, "stateKey"),
2040 valueJson: normalizeRequiredString(input.valueJson, "valueJson"),
2041 updatedAt: normalizeNonNegativeInteger(input.updatedAt ?? Date.now(), 0, "updatedAt")
2042 };
2043}
2044
2045function buildWhereClause(conditions: Array<string | null>, separator: "AND"): string {
2046 const filtered = conditions.filter((condition): condition is string => condition != null);
2047 return filtered.length === 0 ? "" : `WHERE ${filtered.join(` ${separator} `)}`;
2048}
2049
2050function executionParams(record: ExecutionRecord): Array<number | string | null> {
2051 return [
2052 record.instructionId,
2053 record.messageId,
2054 record.target,
2055 record.tool,
2056 record.params,
2057 record.paramsKind,
2058 record.resultOk ? 1 : 0,
2059 record.resultData,
2060 record.resultSummary,
2061 record.resultError,
2062 record.httpStatus,
2063 record.executedAt,
2064 record.staticPath,
2065 record.createdAt
2066 ];
2067}
2068
2069function mapExecutionRow(row: ExecutionRow): ExecutionRecord {
2070 return {
2071 createdAt: row.created_at,
2072 executedAt: row.executed_at,
2073 httpStatus: row.http_status,
2074 instructionId: row.instruction_id,
2075 messageId: row.message_id,
2076 params: row.params,
2077 paramsKind: row.params_kind,
2078 resultData: row.result_data,
2079 resultError: row.result_error,
2080 resultOk: row.result_ok === 1,
2081 resultSummary: row.result_summary,
2082 staticPath: row.static_path,
2083 target: row.target,
2084 tool: row.tool
2085 };
2086}
2087
2088function mapMessageRow(row: MessageRow): MessageRecord {
2089 return {
2090 conversationId: row.conversation_id,
2091 createdAt: row.created_at,
2092 id: row.id,
2093 observedAt: row.observed_at,
2094 organizationId: row.organization_id,
2095 pageTitle: row.page_title,
2096 pageUrl: row.page_url,
2097 platform: row.platform,
2098 rawText: row.raw_text,
2099 role: row.role,
2100 staticPath: row.static_path,
2101 summary: row.summary
2102 };
2103}
2104
2105function mapSessionRow(row: SessionRow): SessionRecord {
2106 return {
2107 conversationId: row.conversation_id,
2108 createdAt: row.created_at,
2109 executionCount: row.execution_count,
2110 id: row.id,
2111 lastActivityAt: row.last_activity_at,
2112 messageCount: row.message_count,
2113 platform: row.platform,
2114 startedAt: row.started_at,
2115 staticPath: row.static_path,
2116 summary: row.summary
2117 };
2118}
2119
2120function localConversationParams(record: LocalConversationRecord): Array<number | string | null> {
2121 return [
2122 record.localConversationId,
2123 record.platform,
2124 record.automationStatus,
2125 record.lastNonPausedAutomationStatus,
2126 record.pauseReason,
2127 record.lastError,
2128 record.executionState,
2129 record.consecutiveFailureCount,
2130 record.repeatedMessageCount,
2131 record.repeatedRenewalCount,
2132 record.lastMessageFingerprint,
2133 record.lastRenewalFingerprint,
2134 record.title,
2135 record.summary,
2136 record.lastMessageId,
2137 record.lastMessageAt,
2138 record.cooldownUntil,
2139 record.pausedAt,
2140 record.createdAt,
2141 record.updatedAt
2142 ];
2143}
2144
2145function conversationLinkParams(record: ConversationLinkRecord): Array<number | string | null> {
2146 return [
2147 record.linkId,
2148 record.localConversationId,
2149 record.platform,
2150 record.remoteConversationId,
2151 record.clientId,
2152 record.pageUrl,
2153 record.pageTitle,
2154 record.routePath,
2155 record.routePattern,
2156 record.routeParams,
2157 record.targetKind,
2158 record.targetId,
2159 record.targetPayload,
2160 record.isActive ? 1 : 0,
2161 record.observedAt,
2162 record.createdAt,
2163 record.updatedAt
2164 ];
2165}
2166
2167function renewalJobParams(record: RenewalJobRecord): Array<number | string | null> {
2168 return [
2169 record.jobId,
2170 record.localConversationId,
2171 record.messageId,
2172 record.status,
2173 record.payload,
2174 record.payloadKind,
2175 record.targetSnapshot,
2176 record.attemptCount,
2177 record.maxAttempts,
2178 record.nextAttemptAt,
2179 record.lastAttemptAt,
2180 record.lastError,
2181 record.logPath,
2182 record.startedAt,
2183 record.finishedAt,
2184 record.createdAt,
2185 record.updatedAt
2186 ];
2187}
2188
2189function browserRequestPolicyStateParams(
2190 record: BrowserRequestPolicyStateRecord
2191): Array<number | string | null> {
2192 return [
2193 record.stateKey,
2194 record.valueJson,
2195 record.updatedAt
2196 ];
2197}
2198
2199function mapLocalConversationRow(row: LocalConversationRow): LocalConversationRecord {
2200 return {
2201 automationStatus: row.automation_status,
2202 consecutiveFailureCount: row.consecutive_failure_count,
2203 cooldownUntil: row.cooldown_until,
2204 createdAt: row.created_at,
2205 executionState: row.execution_state,
2206 lastError: row.last_error,
2207 lastMessageFingerprint: row.last_message_fingerprint,
2208 lastMessageAt: row.last_message_at,
2209 lastMessageId: row.last_message_id,
2210 lastNonPausedAutomationStatus: row.last_non_paused_automation_status,
2211 lastRenewalFingerprint: row.last_renewal_fingerprint,
2212 localConversationId: row.local_conversation_id,
2213 pausedAt: row.paused_at,
2214 pauseReason: row.pause_reason,
2215 platform: row.platform,
2216 repeatedMessageCount: row.repeated_message_count,
2217 repeatedRenewalCount: row.repeated_renewal_count,
2218 summary: row.summary,
2219 title: row.title,
2220 updatedAt: row.updated_at
2221 };
2222}
2223
2224function mapConversationLinkRow(row: ConversationLinkRow): ConversationLinkRecord {
2225 return {
2226 clientId: row.client_id,
2227 createdAt: row.created_at,
2228 isActive: row.is_active === 1,
2229 linkId: row.link_id,
2230 localConversationId: row.local_conversation_id,
2231 observedAt: row.observed_at,
2232 pageTitle: row.page_title,
2233 pageUrl: row.page_url,
2234 platform: row.platform,
2235 remoteConversationId: row.remote_conversation_id,
2236 routeParams: row.route_params,
2237 routePath: row.route_path,
2238 routePattern: row.route_pattern,
2239 targetId: row.target_id,
2240 targetKind: row.target_kind,
2241 targetPayload: row.target_payload,
2242 updatedAt: row.updated_at
2243 };
2244}
2245
2246function mapRenewalJobRow(row: RenewalJobRow): RenewalJobRecord {
2247 return {
2248 attemptCount: row.attempt_count,
2249 createdAt: row.created_at,
2250 finishedAt: row.finished_at,
2251 jobId: row.job_id,
2252 lastAttemptAt: row.last_attempt_at,
2253 lastError: row.last_error,
2254 localConversationId: row.local_conversation_id,
2255 logPath: row.log_path,
2256 maxAttempts: row.max_attempts,
2257 messageId: row.message_id,
2258 nextAttemptAt: row.next_attempt_at,
2259 payload: row.payload,
2260 payloadKind: row.payload_kind,
2261 startedAt: row.started_at,
2262 status: row.status,
2263 targetSnapshot: row.target_snapshot,
2264 updatedAt: row.updated_at
2265 };
2266}
2267
2268function mapBrowserRequestPolicyStateRow(
2269 row: BrowserRequestPolicyStateRow
2270): BrowserRequestPolicyStateRecord {
2271 return {
2272 stateKey: row.state_key,
2273 valueJson: row.value_json,
2274 updatedAt: row.updated_at
2275 };
2276}
2277
2278function messageParams(record: MessageRecord): Array<number | string | null> {
2279 return [
2280 record.id,
2281 record.platform,
2282 record.conversationId,
2283 record.role,
2284 record.rawText,
2285 record.summary,
2286 record.observedAt,
2287 record.staticPath,
2288 record.pageUrl,
2289 record.pageTitle,
2290 record.organizationId,
2291 record.createdAt
2292 ];
2293}
2294
2295function normalizeLimit(value: number | undefined): number {
2296 return normalizePositiveInteger(value, 50);
2297}
2298
2299function normalizeOffset(value: number | undefined): number {
2300 if (value == null) {
2301 return 0;
2302 }
2303
2304 if (!Number.isInteger(value) || value < 0) {
2305 throw new Error("offset must be a non-negative integer.");
2306 }
2307
2308 return value;
2309}
2310
2311function normalizeMessageScanCursor(cursor: MessageScanCursor | null | undefined): MessageScanCursor | null {
2312 if (cursor == null) {
2313 return null;
2314 }
2315
2316 return {
2317 id: normalizeRequiredString(cursor.id, "cursor.id"),
2318 observedAt: normalizeNonNegativeInteger(cursor.observedAt, 0, "cursor.observedAt")
2319 };
2320}
2321
2322function normalizeOptionalBaseUrl(value: string | null | undefined): string | null {
2323 const normalized = normalizeOptionalString(value);
2324 return normalized == null ? null : normalized.replace(/\/+$/u, "");
2325}
2326
2327function normalizeOptionalString(value: string | null | undefined): string | null {
2328 if (value == null) {
2329 return null;
2330 }
2331
2332 const normalized = value.trim();
2333 return normalized === "" ? null : normalized;
2334}
2335
2336function normalizeOptionalInteger(value: number | null | undefined, name: string): number | null {
2337 if (value == null) {
2338 return null;
2339 }
2340
2341 if (!Number.isInteger(value)) {
2342 throw new Error(`${name} must be an integer when provided.`);
2343 }
2344
2345 return value;
2346}
2347
2348function normalizePositiveInteger(value: number | undefined, fallback: number): number {
2349 if (value == null) {
2350 return fallback;
2351 }
2352
2353 if (!Number.isInteger(value) || value <= 0) {
2354 throw new Error("Expected a positive integer.");
2355 }
2356
2357 return value;
2358}
2359
2360function normalizeRequiredPath(value: string, name: string): string {
2361 const normalized = value.trim();
2362
2363 if (normalized === "") {
2364 throw new Error(`${name} must be a non-empty path.`);
2365 }
2366
2367 return normalized;
2368}
2369
2370function normalizeNonNegativeInteger(
2371 value: number | undefined,
2372 fallback: number,
2373 name: string
2374): number {
2375 if (value == null) {
2376 return fallback;
2377 }
2378
2379 if (!Number.isInteger(value) || value < 0) {
2380 throw new Error(`${name} must be a non-negative integer.`);
2381 }
2382
2383 return value;
2384}
2385
2386function normalizeRequiredString(value: string, name: string): string {
2387 const normalized = value.trim();
2388
2389 if (normalized === "") {
2390 throw new Error(`${name} must be a non-empty string.`);
2391 }
2392
2393 return normalized;
2394}
2395
2396function normalizeExecutionState(value: string): ConversationAutomationExecutionState {
2397 const normalized = normalizeRequiredString(value, "executionState");
2398
2399 switch (normalized) {
2400 case "idle":
2401 case "instruction_running":
2402 case "renewal_running":
2403 return normalized as ConversationAutomationExecutionState;
2404 default:
2405 throw new Error(`Unsupported executionState "${normalized}".`);
2406 }
2407}
2408
2409function normalizePauseReason(value: string): ConversationPauseReason {
2410 const normalized = normalizeRequiredString(value, "pauseReason");
2411
2412 switch (normalized) {
2413 case "ai_pause":
2414 case "error_loop":
2415 case "execution_failure":
2416 case "repeated_message":
2417 case "repeated_renewal":
2418 case "rescue_wait":
2419 case "system_pause":
2420 case "user_pause":
2421 return normalized as ConversationPauseReason;
2422 default:
2423 throw new Error(`Unsupported pauseReason "${normalized}".`);
2424 }
2425}
2426
2427function sessionParams(record: SessionRecord): Array<number | string | null> {
2428 return [
2429 record.id,
2430 record.platform,
2431 record.conversationId,
2432 record.startedAt,
2433 record.lastActivityAt,
2434 record.messageCount,
2435 record.executionCount,
2436 record.summary,
2437 record.staticPath,
2438 record.createdAt
2439 ];
2440}
2441
2442function mergeOptionalInteger(
2443 value: number | null | undefined,
2444 existing: number | null,
2445 name: string
2446): number | null {
2447 return value === undefined ? existing : normalizeOptionalInteger(value, name);
2448}
2449
2450function mergeOptionalString(
2451 value: string | null | undefined,
2452 existing: string | null
2453): string | null {
2454 return value === undefined ? existing : normalizeOptionalString(value);
2455}
2456
2457function mergeOptionalPauseReason(
2458 value: ConversationPauseReason | null | undefined,
2459 existing: ConversationPauseReason | null
2460): ConversationPauseReason | null {
2461 return value === undefined
2462 ? existing
2463 : (value == null ? null : normalizePauseReason(value));
2464}
2465
2466function mergeOptionalSerialized(
2467 value: unknown,
2468 existing: string | null
2469): string | null {
2470 return value === undefined ? existing : stringifyUnknown(value);
2471}
2472
2473function stringifyUnknown(value: unknown): string | null {
2474 if (value == null) {
2475 return null;
2476 }
2477
2478 if (typeof value === "string") {
2479 return value;
2480 }
2481
2482 return JSON.stringify(value, null, 2);
2483}
2484
2485function summarizeText(value: string, summaryLength: number): string | null {
2486 const normalized = value.trim();
2487
2488 if (normalized === "") {
2489 return null;
2490 }
2491
2492 return normalized.slice(0, summaryLength);
2493}
2494
2495function buildSessionId(platform: string, conversationId: string | null): string {
2496 const key = `${normalizeRequiredString(platform, "platform")}\u0000${conversationId ?? ""}`;
2497 return `session_${buildStableHash(key)}${buildStableHash(`${key}\u0001session`)}`;
2498}
2499
2500function resolveSessionSummary(
2501 existing: SessionRecord | null,
2502 summaryCandidate: string | null,
2503 activityAt: number
2504): string | null {
2505 const normalizedCandidate = normalizeOptionalString(summaryCandidate);
2506
2507 if (existing == null) {
2508 return normalizedCandidate;
2509 }
2510
2511 if (activityAt >= existing.lastActivityAt) {
2512 return normalizedCandidate ?? existing.summary;
2513 }
2514
2515 return existing.summary;
2516}
2517
2518function messageSyncPayload(record: MessageRecord): Record<string, unknown> {
2519 return {
2520 id: record.id,
2521 platform: record.platform,
2522 conversation_id: record.conversationId,
2523 role: record.role,
2524 raw_text: record.rawText,
2525 summary: record.summary,
2526 observed_at: record.observedAt,
2527 static_path: record.staticPath,
2528 page_url: record.pageUrl,
2529 page_title: record.pageTitle,
2530 organization_id: record.organizationId,
2531 created_at: record.createdAt
2532 };
2533}
2534
2535function executionSyncPayload(record: ExecutionRecord): Record<string, unknown> {
2536 return {
2537 instruction_id: record.instructionId,
2538 message_id: record.messageId,
2539 target: record.target,
2540 tool: record.tool,
2541 params: record.params,
2542 params_kind: record.paramsKind,
2543 result_ok: record.resultOk ? 1 : 0,
2544 result_data: record.resultData,
2545 result_summary: record.resultSummary,
2546 result_error: record.resultError,
2547 http_status: record.httpStatus,
2548 executed_at: record.executedAt,
2549 static_path: record.staticPath,
2550 created_at: record.createdAt
2551 };
2552}
2553
2554function sessionSyncPayload(record: SessionRecord): Record<string, unknown> {
2555 return {
2556 id: record.id,
2557 platform: record.platform,
2558 conversation_id: record.conversationId,
2559 started_at: record.startedAt,
2560 last_activity_at: record.lastActivityAt,
2561 message_count: record.messageCount,
2562 execution_count: record.executionCount,
2563 summary: record.summary,
2564 static_path: record.staticPath,
2565 created_at: record.createdAt
2566 };
2567}
2568
2569function localConversationSyncPayload(
2570 record: LocalConversationRecord
2571): Record<string, unknown> {
2572 return {
2573 local_conversation_id: record.localConversationId,
2574 platform: record.platform,
2575 automation_status: record.automationStatus,
2576 last_non_paused_automation_status: record.lastNonPausedAutomationStatus,
2577 pause_reason: record.pauseReason,
2578 last_error: record.lastError,
2579 execution_state: record.executionState,
2580 consecutive_failure_count: record.consecutiveFailureCount,
2581 repeated_message_count: record.repeatedMessageCount,
2582 repeated_renewal_count: record.repeatedRenewalCount,
2583 last_message_fingerprint: record.lastMessageFingerprint,
2584 last_renewal_fingerprint: record.lastRenewalFingerprint,
2585 title: record.title,
2586 summary: record.summary,
2587 last_message_id: record.lastMessageId,
2588 last_message_at: record.lastMessageAt,
2589 cooldown_until: record.cooldownUntil,
2590 paused_at: record.pausedAt,
2591 created_at: record.createdAt,
2592 updated_at: record.updatedAt
2593 };
2594}
2595
2596function conversationLinkSyncPayload(
2597 record: ConversationLinkRecord
2598): Record<string, unknown> {
2599 return {
2600 link_id: record.linkId,
2601 local_conversation_id: record.localConversationId,
2602 platform: record.platform,
2603 remote_conversation_id: record.remoteConversationId,
2604 client_id: record.clientId,
2605 page_url: record.pageUrl,
2606 page_title: record.pageTitle,
2607 route_path: record.routePath,
2608 route_pattern: record.routePattern,
2609 route_params: record.routeParams,
2610 target_kind: record.targetKind,
2611 target_id: record.targetId,
2612 target_payload: record.targetPayload,
2613 is_active: record.isActive ? 1 : 0,
2614 observed_at: record.observedAt,
2615 created_at: record.createdAt,
2616 updated_at: record.updatedAt
2617 };
2618}
2619
2620function renewalJobSyncPayload(record: RenewalJobRecord): Record<string, unknown> {
2621 return {
2622 job_id: record.jobId,
2623 local_conversation_id: record.localConversationId,
2624 message_id: record.messageId,
2625 status: record.status,
2626 payload: record.payload,
2627 payload_kind: record.payloadKind,
2628 target_snapshot: record.targetSnapshot,
2629 attempt_count: record.attemptCount,
2630 max_attempts: record.maxAttempts,
2631 next_attempt_at: record.nextAttemptAt,
2632 last_attempt_at: record.lastAttemptAt,
2633 last_error: record.lastError,
2634 log_path: record.logPath,
2635 started_at: record.startedAt,
2636 finished_at: record.finishedAt,
2637 created_at: record.createdAt,
2638 updated_at: record.updatedAt
2639 };
2640}
2641
2642function browserRequestPolicyStateSyncPayload(
2643 record: BrowserRequestPolicyStateRecord
2644): Record<string, unknown> {
2645 return {
2646 state_key: record.stateKey,
2647 value_json: record.valueJson,
2648 updated_at: record.updatedAt
2649 };
2650}
2651
2652function buildStableHash(value: string): string {
2653 let hash = 2_166_136_261;
2654
2655 for (let index = 0; index < value.length; index += 1) {
2656 hash ^= value.charCodeAt(index);
2657 hash = Math.imul(hash, 16_777_619);
2658 }
2659
2660 return (hash >>> 0).toString(16).padStart(8, "0");
2661}