1import * as SqlClient from "effect/sql/SqlClient";
2import * as Effect from "effect/Effect";
3
4export default Effect.gen(function* () {
5 const sql = yield* SqlClient.SqlClient;
6
7 yield* sql`
8 CREATE TABLE IF NOT EXISTS projection_projects (
9 project_id TEXT PRIMARY KEY,
10 title TEXT NOT NULL,
11 workspace_root TEXT NOT NULL,
12 default_model TEXT,
13 scripts_json TEXT NOT NULL,
14 created_at TEXT NOT NULL,
15 updated_at TEXT NOT NULL,
16 deleted_at TEXT
17 )
18 `;
19
20 yield* sql`
21 CREATE TABLE IF NOT EXISTS projection_threads (
22 thread_id TEXT PRIMARY KEY,
23 project_id TEXT NOT NULL,
24 title TEXT NOT NULL,
25 model TEXT NOT NULL,
26 branch TEXT,
27 worktree_path TEXT,
28 latest_turn_id TEXT,
29 created_at TEXT NOT NULL,
30 updated_at TEXT NOT NULL,
31 deleted_at TEXT
32 )
33 `;
34
35 yield* sql`
36 CREATE TABLE IF NOT EXISTS projection_thread_messages (
37 message_id TEXT PRIMARY KEY,
38 thread_id TEXT NOT NULL,
39 turn_id TEXT,
40 role TEXT NOT NULL,
41 text TEXT NOT NULL,
42 is_streaming INTEGER NOT NULL,
43 created_at TEXT NOT NULL,
44 updated_at TEXT NOT NULL
45 )
46 `;
47
48 yield* sql`
49 CREATE TABLE IF NOT EXISTS projection_thread_activities (
50 activity_id TEXT PRIMARY KEY,
51 thread_id TEXT NOT NULL,
52 turn_id TEXT,
53 tone TEXT NOT NULL,
54 kind TEXT NOT NULL,
55 summary TEXT NOT NULL,
56 payload_json TEXT NOT NULL,
57 created_at TEXT NOT NULL
58 )
59 `;
60
61 yield* sql`
62 CREATE TABLE IF NOT EXISTS projection_thread_sessions (
63 thread_id TEXT PRIMARY KEY,
64 status TEXT NOT NULL,
65 provider_name TEXT,
66 provider_session_id TEXT,
67 provider_thread_id TEXT,
68 active_turn_id TEXT,
69 last_error TEXT,
70 updated_at TEXT NOT NULL
71 )
72 `;
73
74 yield* sql`
75 CREATE TABLE IF NOT EXISTS projection_turns (
76 row_id INTEGER PRIMARY KEY AUTOINCREMENT,
77 thread_id TEXT NOT NULL,
78 turn_id TEXT,
79 pending_message_id TEXT,
80 assistant_message_id TEXT,
81 state TEXT NOT NULL,
82 requested_at TEXT NOT NULL,
83 started_at TEXT,
84 completed_at TEXT,
85 checkpoint_turn_count INTEGER,
86 checkpoint_ref TEXT,
87 checkpoint_status TEXT,
88 checkpoint_files_json TEXT NOT NULL,
89 UNIQUE (thread_id, turn_id),
90 UNIQUE (thread_id, checkpoint_turn_count)
91 )
92 `;
93
94 yield* sql`
95 CREATE TABLE IF NOT EXISTS projection_pending_approvals (
96 request_id TEXT PRIMARY KEY,
97 thread_id TEXT NOT NULL,
98 turn_id TEXT,
99 status TEXT NOT NULL,
100 decision TEXT,
101 created_at TEXT NOT NULL,
102 resolved_at TEXT
103 )
104 `;
105
106 yield* sql`
107 CREATE TABLE IF NOT EXISTS projection_state (
108 projector TEXT PRIMARY KEY,
109 last_applied_sequence INTEGER NOT NULL,
110 updated_at TEXT NOT NULL
111 )
112 `;
113
114 yield* sql`
115 CREATE INDEX IF NOT EXISTS idx_projection_projects_updated_at
116 ON projection_projects(updated_at)
117 `;
118
119 yield* sql`
120 CREATE INDEX IF NOT EXISTS idx_projection_threads_project_id
121 ON projection_threads(project_id)
122 `;
123
124 yield* sql`
125 CREATE INDEX IF NOT EXISTS idx_projection_thread_messages_thread_created
126 ON projection_thread_messages(thread_id, created_at)
127 `;
128
129 yield* sql`
130 CREATE INDEX IF NOT EXISTS idx_projection_thread_activities_thread_created
131 ON projection_thread_activities(thread_id, created_at)
132 `;
133
134 yield* sql`
135 CREATE INDEX IF NOT EXISTS idx_projection_thread_sessions_provider_session
136 ON projection_thread_sessions(provider_session_id)
137 `;
138
139 yield* sql`
140 CREATE INDEX IF NOT EXISTS idx_projection_turns_thread_requested
141 ON projection_turns(thread_id, requested_at)
142 `;
143
144 yield* sql`
145 CREATE INDEX IF NOT EXISTS idx_projection_turns_thread_checkpoint_completed
146 ON projection_turns(thread_id, checkpoint_turn_count, completed_at)
147 `;
148
149 yield* sql`
150 CREATE INDEX IF NOT EXISTS idx_projection_pending_approvals_thread_status
151 ON projection_pending_approvals(thread_id, status)
152 `;
153});