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