1CREATE TABLE "users" (
2 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
3 "github_login" VARCHAR,
4 "admin" BOOLEAN,
5 "email_address" VARCHAR(255) DEFAULT NULL,
6 "name" TEXT,
7 "invite_code" VARCHAR(64),
8 "invite_count" INTEGER NOT NULL DEFAULT 0,
9 "inviter_id" INTEGER REFERENCES users (id),
10 "connected_once" BOOLEAN NOT NULL DEFAULT false,
11 "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
12 "metrics_id" TEXT,
13 "github_user_id" INTEGER NOT NULL,
14 "accepted_tos_at" TIMESTAMP WITHOUT TIME ZONE,
15 "github_user_created_at" TIMESTAMP WITHOUT TIME ZONE,
16 "custom_llm_monthly_allowance_in_cents" INTEGER
17);
18CREATE UNIQUE INDEX "index_users_github_login" ON "users" ("github_login");
19CREATE UNIQUE INDEX "index_invite_code_users" ON "users" ("invite_code");
20CREATE INDEX "index_users_on_email_address" ON "users" ("email_address");
21CREATE UNIQUE INDEX "index_users_on_github_user_id" ON "users" ("github_user_id");
22
23CREATE TABLE "access_tokens" (
24 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
25 "user_id" INTEGER REFERENCES users (id),
26 "impersonated_user_id" INTEGER REFERENCES users (id),
27 "hash" VARCHAR(128)
28);
29CREATE INDEX "index_access_tokens_user_id" ON "access_tokens" ("user_id");
30
31CREATE TABLE "contacts" (
32 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
33 "user_id_a" INTEGER REFERENCES users (id) NOT NULL,
34 "user_id_b" INTEGER REFERENCES users (id) NOT NULL,
35 "a_to_b" BOOLEAN NOT NULL,
36 "should_notify" BOOLEAN NOT NULL,
37 "accepted" BOOLEAN NOT NULL
38);
39CREATE UNIQUE INDEX "index_contacts_user_ids" ON "contacts" ("user_id_a", "user_id_b");
40CREATE INDEX "index_contacts_user_id_b" ON "contacts" ("user_id_b");
41
42CREATE TABLE "rooms" (
43 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
44 "live_kit_room" VARCHAR NOT NULL,
45 "environment" VARCHAR,
46 "channel_id" INTEGER REFERENCES channels (id) ON DELETE CASCADE
47);
48CREATE UNIQUE INDEX "index_rooms_on_channel_id" ON "rooms" ("channel_id");
49
50CREATE TABLE "projects" (
51 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
52 "room_id" INTEGER REFERENCES rooms (id) ON DELETE CASCADE,
53 "host_user_id" INTEGER REFERENCES users (id),
54 "host_connection_id" INTEGER,
55 "host_connection_server_id" INTEGER REFERENCES servers (id) ON DELETE CASCADE,
56 "unregistered" BOOLEAN NOT NULL DEFAULT FALSE
57);
58CREATE INDEX "index_projects_on_host_connection_server_id" ON "projects" ("host_connection_server_id");
59CREATE INDEX "index_projects_on_host_connection_id_and_host_connection_server_id" ON "projects" ("host_connection_id", "host_connection_server_id");
60
61CREATE TABLE "worktrees" (
62 "project_id" INTEGER NOT NULL REFERENCES projects (id) ON DELETE CASCADE,
63 "id" INTEGER NOT NULL,
64 "root_name" VARCHAR NOT NULL,
65 "abs_path" VARCHAR NOT NULL,
66 "visible" BOOL NOT NULL,
67 "scan_id" INTEGER NOT NULL,
68 "is_complete" BOOL NOT NULL DEFAULT FALSE,
69 "completed_scan_id" INTEGER NOT NULL,
70 PRIMARY KEY(project_id, id)
71);
72CREATE INDEX "index_worktrees_on_project_id" ON "worktrees" ("project_id");
73
74CREATE TABLE "worktree_entries" (
75 "project_id" INTEGER NOT NULL,
76 "worktree_id" INTEGER NOT NULL,
77 "scan_id" INTEGER NOT NULL,
78 "id" INTEGER NOT NULL,
79 "is_dir" BOOL NOT NULL,
80 "path" VARCHAR NOT NULL,
81 "canonical_path" TEXT,
82 "inode" INTEGER NOT NULL,
83 "mtime_seconds" INTEGER NOT NULL,
84 "mtime_nanos" INTEGER NOT NULL,
85 "is_external" BOOL NOT NULL,
86 "is_ignored" BOOL NOT NULL,
87 "is_deleted" BOOL NOT NULL,
88 "git_status" INTEGER,
89 "is_fifo" BOOL NOT NULL,
90 PRIMARY KEY(project_id, worktree_id, id),
91 FOREIGN KEY(project_id, worktree_id) REFERENCES worktrees (project_id, id) ON DELETE CASCADE
92);
93CREATE INDEX "index_worktree_entries_on_project_id" ON "worktree_entries" ("project_id");
94CREATE INDEX "index_worktree_entries_on_project_id_and_worktree_id" ON "worktree_entries" ("project_id", "worktree_id");
95
96CREATE TABLE "worktree_repositories" (
97 "project_id" INTEGER NOT NULL,
98 "worktree_id" INTEGER NOT NULL,
99 "work_directory_id" INTEGER NOT NULL,
100 "branch" VARCHAR,
101 "scan_id" INTEGER NOT NULL,
102 "is_deleted" BOOL NOT NULL,
103 PRIMARY KEY(project_id, worktree_id, work_directory_id),
104 FOREIGN KEY(project_id, worktree_id) REFERENCES worktrees (project_id, id) ON DELETE CASCADE,
105 FOREIGN KEY(project_id, worktree_id, work_directory_id) REFERENCES worktree_entries (project_id, worktree_id, id) ON DELETE CASCADE
106);
107CREATE INDEX "index_worktree_repositories_on_project_id" ON "worktree_repositories" ("project_id");
108CREATE INDEX "index_worktree_repositories_on_project_id_and_worktree_id" ON "worktree_repositories" ("project_id", "worktree_id");
109
110CREATE TABLE "worktree_repository_statuses" (
111 "project_id" INTEGER NOT NULL,
112 "worktree_id" INT8 NOT NULL,
113 "work_directory_id" INT8 NOT NULL,
114 "repo_path" VARCHAR NOT NULL,
115 "status" INT8 NOT NULL,
116 "status_kind" INT4 NOT NULL,
117 "first_status" INT4 NULL,
118 "second_status" INT4 NULL,
119 "scan_id" INT8 NOT NULL,
120 "is_deleted" BOOL NOT NULL,
121 PRIMARY KEY(project_id, worktree_id, work_directory_id, repo_path),
122 FOREIGN KEY(project_id, worktree_id) REFERENCES worktrees (project_id, id) ON DELETE CASCADE,
123 FOREIGN KEY(project_id, worktree_id, work_directory_id) REFERENCES worktree_entries (project_id, worktree_id, id) ON DELETE CASCADE
124);
125CREATE INDEX "index_wt_repos_statuses_on_project_id" ON "worktree_repository_statuses" ("project_id");
126CREATE INDEX "index_wt_repos_statuses_on_project_id_and_wt_id" ON "worktree_repository_statuses" ("project_id", "worktree_id");
127CREATE INDEX "index_wt_repos_statuses_on_project_id_and_wt_id_and_wd_id" ON "worktree_repository_statuses" ("project_id", "worktree_id", "work_directory_id");
128
129CREATE TABLE "worktree_settings_files" (
130 "project_id" INTEGER NOT NULL,
131 "worktree_id" INTEGER NOT NULL,
132 "path" VARCHAR NOT NULL,
133 "content" TEXT,
134 "kind" VARCHAR,
135 PRIMARY KEY(project_id, worktree_id, path),
136 FOREIGN KEY(project_id, worktree_id) REFERENCES worktrees (project_id, id) ON DELETE CASCADE
137);
138CREATE INDEX "index_worktree_settings_files_on_project_id" ON "worktree_settings_files" ("project_id");
139CREATE INDEX "index_worktree_settings_files_on_project_id_and_worktree_id" ON "worktree_settings_files" ("project_id", "worktree_id");
140
141CREATE TABLE "worktree_diagnostic_summaries" (
142 "project_id" INTEGER NOT NULL,
143 "worktree_id" INTEGER NOT NULL,
144 "path" VARCHAR NOT NULL,
145 "language_server_id" INTEGER NOT NULL,
146 "error_count" INTEGER NOT NULL,
147 "warning_count" INTEGER NOT NULL,
148 PRIMARY KEY(project_id, worktree_id, path),
149 FOREIGN KEY(project_id, worktree_id) REFERENCES worktrees (project_id, id) ON DELETE CASCADE
150);
151CREATE INDEX "index_worktree_diagnostic_summaries_on_project_id" ON "worktree_diagnostic_summaries" ("project_id");
152CREATE INDEX "index_worktree_diagnostic_summaries_on_project_id_and_worktree_id" ON "worktree_diagnostic_summaries" ("project_id", "worktree_id");
153
154CREATE TABLE "language_servers" (
155 "id" INTEGER NOT NULL,
156 "project_id" INTEGER NOT NULL REFERENCES projects (id) ON DELETE CASCADE,
157 "name" VARCHAR NOT NULL,
158 PRIMARY KEY(project_id, id)
159);
160CREATE INDEX "index_language_servers_on_project_id" ON "language_servers" ("project_id");
161
162CREATE TABLE "project_collaborators" (
163 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
164 "project_id" INTEGER NOT NULL REFERENCES projects (id) ON DELETE CASCADE,
165 "connection_id" INTEGER NOT NULL,
166 "connection_server_id" INTEGER NOT NULL REFERENCES servers (id) ON DELETE CASCADE,
167 "user_id" INTEGER NOT NULL,
168 "replica_id" INTEGER NOT NULL,
169 "is_host" BOOLEAN NOT NULL
170);
171CREATE INDEX "index_project_collaborators_on_project_id" ON "project_collaborators" ("project_id");
172CREATE UNIQUE INDEX "index_project_collaborators_on_project_id_and_replica_id" ON "project_collaborators" ("project_id", "replica_id");
173CREATE INDEX "index_project_collaborators_on_connection_server_id" ON "project_collaborators" ("connection_server_id");
174CREATE INDEX "index_project_collaborators_on_connection_id" ON "project_collaborators" ("connection_id");
175CREATE UNIQUE INDEX "index_project_collaborators_on_project_id_connection_id_and_server_id" ON "project_collaborators" ("project_id", "connection_id", "connection_server_id");
176
177CREATE TABLE "room_participants" (
178 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
179 "room_id" INTEGER NOT NULL REFERENCES rooms (id),
180 "user_id" INTEGER NOT NULL REFERENCES users (id),
181 "answering_connection_id" INTEGER,
182 "answering_connection_server_id" INTEGER REFERENCES servers (id) ON DELETE CASCADE,
183 "answering_connection_lost" BOOLEAN NOT NULL,
184 "location_kind" INTEGER,
185 "location_project_id" INTEGER,
186 "initial_project_id" INTEGER,
187 "calling_user_id" INTEGER NOT NULL REFERENCES users (id),
188 "calling_connection_id" INTEGER NOT NULL,
189 "calling_connection_server_id" INTEGER REFERENCES servers (id) ON DELETE SET NULL,
190 "participant_index" INTEGER,
191 "role" TEXT,
192 "in_call" BOOLEAN NOT NULL DEFAULT FALSE
193);
194CREATE UNIQUE INDEX "index_room_participants_on_user_id" ON "room_participants" ("user_id");
195CREATE INDEX "index_room_participants_on_room_id" ON "room_participants" ("room_id");
196CREATE INDEX "index_room_participants_on_answering_connection_server_id" ON "room_participants" ("answering_connection_server_id");
197CREATE INDEX "index_room_participants_on_calling_connection_server_id" ON "room_participants" ("calling_connection_server_id");
198CREATE INDEX "index_room_participants_on_answering_connection_id" ON "room_participants" ("answering_connection_id");
199CREATE UNIQUE INDEX "index_room_participants_on_answering_connection_id_and_answering_connection_server_id" ON "room_participants" ("answering_connection_id", "answering_connection_server_id");
200
201CREATE TABLE "servers" (
202 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
203 "environment" VARCHAR NOT NULL
204);
205
206CREATE TABLE "followers" (
207 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
208 "room_id" INTEGER NOT NULL REFERENCES rooms (id) ON DELETE CASCADE,
209 "project_id" INTEGER NOT NULL REFERENCES projects (id) ON DELETE CASCADE,
210 "leader_connection_server_id" INTEGER NOT NULL REFERENCES servers (id) ON DELETE CASCADE,
211 "leader_connection_id" INTEGER NOT NULL,
212 "follower_connection_server_id" INTEGER NOT NULL REFERENCES servers (id) ON DELETE CASCADE,
213 "follower_connection_id" INTEGER NOT NULL
214);
215CREATE UNIQUE INDEX
216 "index_followers_on_project_id_and_leader_connection_server_id_and_leader_connection_id_and_follower_connection_server_id_and_follower_connection_id"
217ON "followers" ("project_id", "leader_connection_server_id", "leader_connection_id", "follower_connection_server_id", "follower_connection_id");
218CREATE INDEX "index_followers_on_room_id" ON "followers" ("room_id");
219
220CREATE TABLE "channels" (
221 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
222 "name" VARCHAR NOT NULL,
223 "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
224 "visibility" VARCHAR NOT NULL,
225 "parent_path" TEXT NOT NULL,
226 "requires_zed_cla" BOOLEAN NOT NULL DEFAULT FALSE
227);
228
229CREATE INDEX "index_channels_on_parent_path" ON "channels" ("parent_path");
230
231CREATE TABLE IF NOT EXISTS "channel_chat_participants" (
232 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
233 "user_id" INTEGER NOT NULL REFERENCES users (id),
234 "channel_id" INTEGER NOT NULL REFERENCES channels (id) ON DELETE CASCADE,
235 "connection_id" INTEGER NOT NULL,
236 "connection_server_id" INTEGER NOT NULL REFERENCES servers (id) ON DELETE CASCADE
237);
238CREATE INDEX "index_channel_chat_participants_on_channel_id" ON "channel_chat_participants" ("channel_id");
239
240CREATE TABLE IF NOT EXISTS "channel_messages" (
241 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
242 "channel_id" INTEGER NOT NULL REFERENCES channels (id) ON DELETE CASCADE,
243 "sender_id" INTEGER NOT NULL REFERENCES users (id),
244 "body" TEXT NOT NULL,
245 "sent_at" TIMESTAMP,
246 "edited_at" TIMESTAMP,
247 "nonce" BLOB NOT NULL,
248 "reply_to_message_id" INTEGER DEFAULT NULL
249);
250CREATE INDEX "index_channel_messages_on_channel_id" ON "channel_messages" ("channel_id");
251CREATE UNIQUE INDEX "index_channel_messages_on_sender_id_nonce" ON "channel_messages" ("sender_id", "nonce");
252
253CREATE TABLE "channel_message_mentions" (
254 "message_id" INTEGER NOT NULL REFERENCES channel_messages (id) ON DELETE CASCADE,
255 "start_offset" INTEGER NOT NULL,
256 "end_offset" INTEGER NOT NULL,
257 "user_id" INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
258 PRIMARY KEY(message_id, start_offset)
259);
260
261CREATE TABLE "channel_members" (
262 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
263 "channel_id" INTEGER NOT NULL REFERENCES channels (id) ON DELETE CASCADE,
264 "user_id" INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
265 "role" VARCHAR NOT NULL,
266 "accepted" BOOLEAN NOT NULL DEFAULT false,
267 "updated_at" TIMESTAMP NOT NULL DEFAULT now
268);
269
270CREATE UNIQUE INDEX "index_channel_members_on_channel_id_and_user_id" ON "channel_members" ("channel_id", "user_id");
271
272CREATE TABLE "buffers" (
273 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
274 "channel_id" INTEGER NOT NULL REFERENCES channels (id) ON DELETE CASCADE,
275 "epoch" INTEGER NOT NULL DEFAULT 0,
276 "latest_operation_epoch" INTEGER,
277 "latest_operation_replica_id" INTEGER,
278 "latest_operation_lamport_timestamp" INTEGER
279);
280
281CREATE INDEX "index_buffers_on_channel_id" ON "buffers" ("channel_id");
282
283CREATE TABLE "buffer_operations" (
284 "buffer_id" INTEGER NOT NULL REFERENCES buffers (id) ON DELETE CASCADE,
285 "epoch" INTEGER NOT NULL,
286 "replica_id" INTEGER NOT NULL,
287 "lamport_timestamp" INTEGER NOT NULL,
288 "value" BLOB NOT NULL,
289 PRIMARY KEY(buffer_id, epoch, lamport_timestamp, replica_id)
290);
291
292CREATE TABLE "buffer_snapshots" (
293 "buffer_id" INTEGER NOT NULL REFERENCES buffers (id) ON DELETE CASCADE,
294 "epoch" INTEGER NOT NULL,
295 "text" TEXT NOT NULL,
296 "operation_serialization_version" INTEGER NOT NULL,
297 PRIMARY KEY(buffer_id, epoch)
298);
299
300CREATE TABLE "channel_buffer_collaborators" (
301 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
302 "channel_id" INTEGER NOT NULL REFERENCES channels (id) ON DELETE CASCADE,
303 "connection_id" INTEGER NOT NULL,
304 "connection_server_id" INTEGER NOT NULL REFERENCES servers (id) ON DELETE CASCADE,
305 "connection_lost" BOOLEAN NOT NULL DEFAULT false,
306 "user_id" INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
307 "replica_id" INTEGER NOT NULL
308);
309
310CREATE INDEX "index_channel_buffer_collaborators_on_channel_id" ON "channel_buffer_collaborators" ("channel_id");
311CREATE UNIQUE INDEX "index_channel_buffer_collaborators_on_channel_id_and_replica_id" ON "channel_buffer_collaborators" ("channel_id", "replica_id");
312CREATE INDEX "index_channel_buffer_collaborators_on_connection_server_id" ON "channel_buffer_collaborators" ("connection_server_id");
313CREATE INDEX "index_channel_buffer_collaborators_on_connection_id" ON "channel_buffer_collaborators" ("connection_id");
314CREATE UNIQUE INDEX "index_channel_buffer_collaborators_on_channel_id_connection_id_and_server_id" ON "channel_buffer_collaborators" ("channel_id", "connection_id", "connection_server_id");
315
316
317CREATE TABLE "feature_flags" (
318 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
319 "flag" TEXT NOT NULL UNIQUE,
320 "enabled_for_all" BOOLEAN NOT NULL DEFAULT false
321);
322
323CREATE INDEX "index_feature_flags" ON "feature_flags" ("id");
324
325
326CREATE TABLE "user_features" (
327 "user_id" INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
328 "feature_id" INTEGER NOT NULL REFERENCES feature_flags (id) ON DELETE CASCADE,
329 PRIMARY KEY (user_id, feature_id)
330);
331
332CREATE UNIQUE INDEX "index_user_features_user_id_and_feature_id" ON "user_features" ("user_id", "feature_id");
333CREATE INDEX "index_user_features_on_user_id" ON "user_features" ("user_id");
334CREATE INDEX "index_user_features_on_feature_id" ON "user_features" ("feature_id");
335
336
337CREATE TABLE "observed_buffer_edits" (
338 "user_id" INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
339 "buffer_id" INTEGER NOT NULL REFERENCES buffers (id) ON DELETE CASCADE,
340 "epoch" INTEGER NOT NULL,
341 "lamport_timestamp" INTEGER NOT NULL,
342 "replica_id" INTEGER NOT NULL,
343 PRIMARY KEY (user_id, buffer_id)
344);
345
346CREATE UNIQUE INDEX "index_observed_buffers_user_and_buffer_id" ON "observed_buffer_edits" ("user_id", "buffer_id");
347
348CREATE TABLE IF NOT EXISTS "observed_channel_messages" (
349 "user_id" INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
350 "channel_id" INTEGER NOT NULL REFERENCES channels (id) ON DELETE CASCADE,
351 "channel_message_id" INTEGER NOT NULL,
352 PRIMARY KEY (user_id, channel_id)
353);
354
355CREATE UNIQUE INDEX "index_observed_channel_messages_user_and_channel_id" ON "observed_channel_messages" ("user_id", "channel_id");
356
357CREATE TABLE "notification_kinds" (
358 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
359 "name" VARCHAR NOT NULL
360);
361
362CREATE UNIQUE INDEX "index_notification_kinds_on_name" ON "notification_kinds" ("name");
363
364CREATE TABLE "notifications" (
365 "id" INTEGER PRIMARY KEY AUTOINCREMENT,
366 "created_at" TIMESTAMP NOT NULL default CURRENT_TIMESTAMP,
367 "recipient_id" INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
368 "kind" INTEGER NOT NULL REFERENCES notification_kinds (id),
369 "entity_id" INTEGER,
370 "content" TEXT,
371 "is_read" BOOLEAN NOT NULL DEFAULT FALSE,
372 "response" BOOLEAN
373);
374
375CREATE INDEX
376 "index_notifications_on_recipient_id_is_read_kind_entity_id"
377 ON "notifications"
378 ("recipient_id", "is_read", "kind", "entity_id");
379
380CREATE TABLE contributors (
381 user_id INTEGER REFERENCES users(id),
382 signed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
383 PRIMARY KEY (user_id)
384);
385
386CREATE TABLE extensions (
387 id INTEGER PRIMARY KEY AUTOINCREMENT,
388 external_id TEXT NOT NULL,
389 name TEXT NOT NULL,
390 latest_version TEXT NOT NULL,
391 total_download_count INTEGER NOT NULL DEFAULT 0
392);
393
394CREATE TABLE extension_versions (
395 extension_id INTEGER REFERENCES extensions(id),
396 version TEXT NOT NULL,
397 published_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
398 authors TEXT NOT NULL,
399 repository TEXT NOT NULL,
400 description TEXT NOT NULL,
401 schema_version INTEGER NOT NULL DEFAULT 0,
402 wasm_api_version TEXT,
403 download_count INTEGER NOT NULL DEFAULT 0,
404 PRIMARY KEY (extension_id, version)
405);
406
407CREATE UNIQUE INDEX "index_extensions_external_id" ON "extensions" ("external_id");
408CREATE INDEX "index_extensions_total_download_count" ON "extensions" ("total_download_count");
409
410CREATE TABLE rate_buckets (
411 user_id INT NOT NULL,
412 rate_limit_name VARCHAR(255) NOT NULL,
413 token_count INT NOT NULL,
414 last_refill TIMESTAMP WITHOUT TIME ZONE NOT NULL,
415 PRIMARY KEY (user_id, rate_limit_name),
416 FOREIGN KEY (user_id) REFERENCES users(id)
417);
418CREATE INDEX idx_user_id_rate_limit ON rate_buckets (user_id, rate_limit_name);
419
420CREATE TABLE IF NOT EXISTS billing_preferences (
421 id INTEGER PRIMARY KEY AUTOINCREMENT,
422 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
423 user_id INTEGER NOT NULL REFERENCES users(id),
424 max_monthly_llm_usage_spending_in_cents INTEGER NOT NULL
425);
426
427CREATE UNIQUE INDEX "uix_billing_preferences_on_user_id" ON billing_preferences (user_id);
428
429CREATE TABLE IF NOT EXISTS billing_customers (
430 id INTEGER PRIMARY KEY AUTOINCREMENT,
431 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
432 user_id INTEGER NOT NULL REFERENCES users(id),
433 stripe_customer_id TEXT NOT NULL
434);
435
436CREATE UNIQUE INDEX "uix_billing_customers_on_user_id" ON billing_customers (user_id);
437CREATE UNIQUE INDEX "uix_billing_customers_on_stripe_customer_id" ON billing_customers (stripe_customer_id);
438
439CREATE TABLE IF NOT EXISTS billing_subscriptions (
440 id INTEGER PRIMARY KEY AUTOINCREMENT,
441 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
442 billing_customer_id INTEGER NOT NULL REFERENCES billing_customers(id),
443 stripe_subscription_id TEXT NOT NULL,
444 stripe_subscription_status TEXT NOT NULL,
445 stripe_cancel_at TIMESTAMP,
446 stripe_cancellation_reason TEXT
447);
448
449CREATE INDEX "ix_billing_subscriptions_on_billing_customer_id" ON billing_subscriptions (billing_customer_id);
450CREATE UNIQUE INDEX "uix_billing_subscriptions_on_stripe_subscription_id" ON billing_subscriptions (stripe_subscription_id);
451
452CREATE TABLE IF NOT EXISTS processed_stripe_events (
453 stripe_event_id TEXT PRIMARY KEY,
454 stripe_event_type TEXT NOT NULL,
455 stripe_event_created_timestamp INTEGER NOT NULL,
456 processed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
457);
458
459CREATE INDEX "ix_processed_stripe_events_on_stripe_event_created_timestamp" ON processed_stripe_events (stripe_event_created_timestamp);