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