codex@macbookpro
·
2026-04-01
schema.ts
1export const CONVERSATION_LINK_INDEX_SQL = `
2DROP INDEX IF EXISTS idx_conversation_links_remote;
3
4CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_remote
5 ON conversation_links(platform, remote_conversation_id)
6 WHERE remote_conversation_id IS NOT NULL;
7CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_null_route
8 ON conversation_links(platform, COALESCE(client_id, ''), route_path)
9 WHERE remote_conversation_id IS NULL
10 AND route_path IS NOT NULL;
11CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_null_page
12 ON conversation_links(platform, COALESCE(client_id, ''), page_url)
13 WHERE remote_conversation_id IS NULL
14 AND route_path IS NULL
15 AND page_url IS NOT NULL;
16CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_null_target
17 ON conversation_links(platform, COALESCE(client_id, ''), target_id)
18 WHERE remote_conversation_id IS NULL
19 AND route_path IS NULL
20 AND page_url IS NULL
21 AND target_id IS NOT NULL;
22CREATE UNIQUE INDEX IF NOT EXISTS idx_conversation_links_null_client
23 ON conversation_links(platform, COALESCE(client_id, ''))
24 WHERE remote_conversation_id IS NULL
25 AND route_path IS NULL
26 AND page_url IS NULL
27 AND target_id IS NULL
28 AND client_id IS NOT NULL;
29CREATE INDEX IF NOT EXISTS idx_conversation_links_local
30 ON conversation_links(local_conversation_id, is_active, updated_at DESC);
31CREATE INDEX IF NOT EXISTS idx_conversation_links_client
32 ON conversation_links(client_id, updated_at DESC);
33`;
34
35export const ARTIFACT_SCHEMA_SQL = `
36CREATE TABLE IF NOT EXISTS messages (
37 id TEXT PRIMARY KEY,
38 platform TEXT NOT NULL,
39 conversation_id TEXT,
40 role TEXT NOT NULL,
41 raw_text TEXT NOT NULL,
42 summary TEXT,
43 observed_at INTEGER NOT NULL,
44 static_path TEXT NOT NULL,
45 page_url TEXT,
46 page_title TEXT,
47 organization_id TEXT,
48 created_at INTEGER NOT NULL
49);
50
51CREATE INDEX IF NOT EXISTS idx_messages_conversation
52 ON messages(conversation_id);
53CREATE INDEX IF NOT EXISTS idx_messages_platform
54 ON messages(platform, observed_at DESC);
55CREATE INDEX IF NOT EXISTS idx_messages_role_observed
56 ON messages(role, observed_at ASC, id ASC);
57
58CREATE TABLE IF NOT EXISTS executions (
59 instruction_id TEXT PRIMARY KEY,
60 message_id TEXT NOT NULL,
61 target TEXT NOT NULL,
62 tool TEXT NOT NULL,
63 params TEXT,
64 params_kind TEXT NOT NULL,
65 result_ok INTEGER NOT NULL,
66 result_data TEXT,
67 result_summary TEXT,
68 result_error TEXT,
69 http_status INTEGER,
70 executed_at INTEGER NOT NULL,
71 static_path TEXT NOT NULL,
72 created_at INTEGER NOT NULL,
73 FOREIGN KEY (message_id) REFERENCES messages(id)
74);
75
76CREATE INDEX IF NOT EXISTS idx_executions_message
77 ON executions(message_id);
78CREATE INDEX IF NOT EXISTS idx_executions_target_tool
79 ON executions(target, tool);
80
81CREATE TABLE IF NOT EXISTS sessions (
82 id TEXT PRIMARY KEY,
83 platform TEXT NOT NULL,
84 conversation_id TEXT,
85 started_at INTEGER NOT NULL,
86 last_activity_at INTEGER NOT NULL,
87 message_count INTEGER NOT NULL DEFAULT 0,
88 execution_count INTEGER NOT NULL DEFAULT 0,
89 summary TEXT,
90 static_path TEXT NOT NULL,
91 created_at INTEGER NOT NULL
92);
93
94CREATE INDEX IF NOT EXISTS idx_sessions_platform
95 ON sessions(platform, last_activity_at DESC);
96CREATE INDEX IF NOT EXISTS idx_sessions_conversation
97 ON sessions(conversation_id);
98
99CREATE TABLE IF NOT EXISTS local_conversations (
100 local_conversation_id TEXT PRIMARY KEY,
101 platform TEXT NOT NULL,
102 automation_status TEXT NOT NULL DEFAULT 'manual',
103 last_non_paused_automation_status TEXT NOT NULL DEFAULT 'manual',
104 pause_reason TEXT,
105 last_error TEXT,
106 execution_state TEXT NOT NULL DEFAULT 'idle',
107 consecutive_failure_count INTEGER NOT NULL DEFAULT 0,
108 repeated_message_count INTEGER NOT NULL DEFAULT 0,
109 repeated_renewal_count INTEGER NOT NULL DEFAULT 0,
110 last_message_fingerprint TEXT,
111 last_renewal_fingerprint TEXT,
112 title TEXT,
113 summary TEXT,
114 last_message_id TEXT,
115 last_message_at INTEGER,
116 cooldown_until INTEGER,
117 paused_at INTEGER,
118 created_at INTEGER NOT NULL,
119 updated_at INTEGER NOT NULL
120);
121
122CREATE INDEX IF NOT EXISTS idx_local_conversations_status
123 ON local_conversations(automation_status, updated_at DESC);
124CREATE INDEX IF NOT EXISTS idx_local_conversations_platform
125 ON local_conversations(platform, updated_at DESC);
126CREATE INDEX IF NOT EXISTS idx_local_conversations_last_message
127 ON local_conversations(last_message_at DESC);
128CREATE INDEX IF NOT EXISTS idx_local_conversations_last_message_id
129 ON local_conversations(last_message_id);
130
131CREATE TABLE IF NOT EXISTS conversation_links (
132 link_id TEXT PRIMARY KEY,
133 local_conversation_id TEXT NOT NULL,
134 platform TEXT NOT NULL,
135 remote_conversation_id TEXT,
136 client_id TEXT,
137 page_url TEXT,
138 page_title TEXT,
139 route_path TEXT,
140 route_pattern TEXT,
141 route_params TEXT,
142 target_kind TEXT,
143 target_id TEXT,
144 target_payload TEXT,
145 is_active INTEGER NOT NULL DEFAULT 1,
146 observed_at INTEGER NOT NULL,
147 created_at INTEGER NOT NULL,
148 updated_at INTEGER NOT NULL,
149 FOREIGN KEY (local_conversation_id) REFERENCES local_conversations(local_conversation_id)
150);
151
152CREATE TABLE IF NOT EXISTS renewal_jobs (
153 job_id TEXT PRIMARY KEY,
154 local_conversation_id TEXT NOT NULL,
155 message_id TEXT NOT NULL,
156 status TEXT NOT NULL DEFAULT 'pending',
157 payload TEXT NOT NULL,
158 payload_kind TEXT NOT NULL DEFAULT 'text',
159 target_snapshot TEXT,
160 attempt_count INTEGER NOT NULL DEFAULT 0,
161 max_attempts INTEGER NOT NULL DEFAULT 3,
162 next_attempt_at INTEGER,
163 last_attempt_at INTEGER,
164 last_error TEXT,
165 log_path TEXT,
166 started_at INTEGER,
167 finished_at INTEGER,
168 created_at INTEGER NOT NULL,
169 updated_at INTEGER NOT NULL,
170 FOREIGN KEY (local_conversation_id) REFERENCES local_conversations(local_conversation_id),
171 FOREIGN KEY (message_id) REFERENCES messages(id)
172);
173
174CREATE UNIQUE INDEX IF NOT EXISTS idx_renewal_jobs_message
175 ON renewal_jobs(message_id);
176CREATE INDEX IF NOT EXISTS idx_renewal_jobs_status_due
177 ON renewal_jobs(status, next_attempt_at ASC, created_at ASC);
178CREATE INDEX IF NOT EXISTS idx_renewal_jobs_local_conversation
179 ON renewal_jobs(local_conversation_id, status, created_at DESC);
180
181CREATE TABLE IF NOT EXISTS browser_request_policy_state (
182 state_key TEXT PRIMARY KEY,
183 value_json TEXT NOT NULL,
184 updated_at INTEGER NOT NULL
185);
186
187CREATE INDEX IF NOT EXISTS idx_browser_request_policy_state_updated
188 ON browser_request_policy_state(updated_at DESC);
189`;