TypeScript 97.4%
SQL 1%
JavaScript 0.9%
CSS 0.6%
1import {2 pgTable,3 text,4 timestamp,5 boolean,6 integer,7 doublePrecision,8 jsonb,9 index,10 uniqueIndex,11 primaryKey,12} from "drizzle-orm/pg-core";13import { sql } from "drizzle-orm";14import type {15 ModelCapabilities,16 ModelLimits,17 ModelParameters,18 ModelPricing,19 ModelStatus,20 ProviderId,21} from "@/lib/ai/core/types";2223const ts = (name: string) => timestamp(name, { withTimezone: true, mode: "date" });24const now = () => sql`now()`;2526// ---------------------------------------------------------------------------27// Auth (Better Auth compatible — plural table names)28// ---------------------------------------------------------------------------29export const users = pgTable(30 "users",31 {32 id: text("id").primaryKey(),33 name: text("name").notNull().default(""),34 email: text("email").notNull(),35 emailVerified: boolean("email_verified").notNull().default(false),36 image: text("image"),37 role: text("role").notNull().default("user"), // user | admin38 onboardingCompletedAt: ts("onboarding_completed_at"),39 createdAt: ts("created_at").notNull().default(now()),40 updatedAt: ts("updated_at").notNull().default(now()),41 },42 (t) => [uniqueIndex("users_email_uq").on(t.email)],43);4445export const sessions = pgTable(46 "sessions",47 {48 id: text("id").primaryKey(),49 expiresAt: ts("expires_at").notNull(),50 token: text("token").notNull(),51 createdAt: ts("created_at").notNull().default(now()),52 updatedAt: ts("updated_at").notNull().default(now()),53 ipAddress: text("ip_address"),54 userAgent: text("user_agent"),55 userId: text("user_id")56 .notNull()57 .references(() => users.id, { onDelete: "cascade" }),58 },59 (t) => [uniqueIndex("sessions_token_uq").on(t.token), index("sessions_user_idx").on(t.userId)],60);6162export const accounts = pgTable(63 "accounts",64 {65 id: text("id").primaryKey(),66 accountId: text("account_id").notNull(),67 providerId: text("provider_id").notNull(),68 userId: text("user_id")69 .notNull()70 .references(() => users.id, { onDelete: "cascade" }),71 accessToken: text("access_token"),72 refreshToken: text("refresh_token"),73 idToken: text("id_token"),74 accessTokenExpiresAt: ts("access_token_expires_at"),75 refreshTokenExpiresAt: ts("refresh_token_expires_at"),76 scope: text("scope"),77 password: text("password"),78 createdAt: ts("created_at").notNull().default(now()),79 updatedAt: ts("updated_at").notNull().default(now()),80 },81 (t) => [index("accounts_user_idx").on(t.userId)],82);8384export const verifications = pgTable(85 "verifications",86 {87 id: text("id").primaryKey(),88 identifier: text("identifier").notNull(),89 value: text("value").notNull(),90 expiresAt: ts("expires_at").notNull(),91 createdAt: ts("created_at").notNull().default(now()),92 updatedAt: ts("updated_at").notNull().default(now()),93 },94 (t) => [index("verifications_identifier_idx").on(t.identifier)],95);9697// ---------------------------------------------------------------------------98// User preferences & audit99// ---------------------------------------------------------------------------100export const userPreferences = pgTable("user_preferences", {101 userId: text("user_id")102 .primaryKey()103 .references(() => users.id, { onDelete: "cascade" }),104 theme: text("theme").notNull().default("system"), // system | light | dark105 language: text("language").notNull().default("en"),106 defaultModelKey: text("default_model_key"),107 defaultSystemPrompt: text("default_system_prompt"),108 enterToSend: boolean("enter_to_send").notNull().default(true),109 streaming: boolean("streaming").notNull().default(true),110 codeWrap: boolean("code_wrap").notNull().default(false),111 showReasoning: boolean("show_reasoning").notNull().default(true),112 showCosts: boolean("show_costs").notNull().default(true),113 autoTitle: boolean("auto_title").notNull().default(true),114 extra: jsonb("extra").$type<Record<string, unknown>>().notNull().default({}),115 updatedAt: ts("updated_at").notNull().default(now()),116});117118export const auditEvents = pgTable(119 "audit_events",120 {121 id: text("id").primaryKey(),122 userId: text("user_id").references(() => users.id, { onDelete: "set null" }),123 action: text("action").notNull(),124 ipAddress: text("ip_address"),125 userAgent: text("user_agent"),126 meta: jsonb("meta").$type<Record<string, unknown>>().notNull().default({}),127 createdAt: ts("created_at").notNull().default(now()),128 },129 (t) => [index("audit_user_created_idx").on(t.userId, t.createdAt)],130);131132// ---------------------------------------------------------------------------133// Provider connections (encrypted BYOK keys)134// ---------------------------------------------------------------------------135export const providerConnections = pgTable(136 "provider_connections",137 {138 id: text("id").primaryKey(),139 userId: text("user_id")140 .notNull()141 .references(() => users.id, { onDelete: "cascade" }),142 provider: text("provider").$type<ProviderId>().notNull(),143 /** AES-256-GCM envelope: `v1.<iv>.<tag>.<ciphertext>` (base64url). Never leaves the server. */144 encryptedKey: text("encrypted_key").notNull(),145 /** Last 4 characters of the key for display: `sk-••••9A2K`. */146 keyHint: text("key_hint").notNull(),147 /** SHA-256 of the key — lets us detect "same key re-entered" without decrypting. */148 keyFingerprint: text("key_fingerprint").notNull(),149 status: text("status").notNull().default("unverified"), // unverified | valid | invalid | error150 lastValidatedAt: ts("last_validated_at"),151 lastValidationError: text("last_validation_error"),152 lastSuccessAt: ts("last_success_at"),153 lastErrorAt: ts("last_error_at"),154 lastErrorCode: text("last_error_code"),155 modelsAvailable: integer("models_available"),156 createdAt: ts("created_at").notNull().default(now()),157 updatedAt: ts("updated_at").notNull().default(now()),158 },159 (t) => [uniqueIndex("provider_connections_user_provider_uq").on(t.userId, t.provider)],160);161162// ---------------------------------------------------------------------------163// Model registry (normalized, provider-agnostic)164// ---------------------------------------------------------------------------165export const models = pgTable(166 "models",167 {168 /** `provider/modelId`, e.g. `openai/gpt-5.5`. */169 key: text("key").primaryKey(),170 provider: text("provider").$type<ProviderId>().notNull(),171 modelId: text("model_id").notNull(),172 displayName: text("display_name").notNull(),173 family: text("family"),174 capabilities: jsonb("capabilities").$type<ModelCapabilities>().notNull(),175 limits: jsonb("limits").$type<ModelLimits>().notNull().default({}),176 parameters: jsonb("parameters").$type<ModelParameters>().notNull().default({}),177 pricing: jsonb("pricing").$type<ModelPricing | null>(),178 status: text("status").$type<ModelStatus>().notNull().default("unknown"),179 /** discovery = seen on the provider list API; catalog = documented but not listed; merged = both. */180 source: text("source").notNull().default("catalog"),181 hidden: boolean("hidden").notNull().default(false),182 sortWeight: integer("sort_weight").notNull().default(0),183 metadata: jsonb("metadata").$type<Record<string, unknown>>().notNull().default({}),184 firstSeenAt: ts("first_seen_at").notNull().default(now()),185 lastSeenAt: ts("last_seen_at").notNull().default(now()),186 updatedAt: ts("updated_at").notNull().default(now()),187 },188 (t) => [index("models_provider_idx").on(t.provider), index("models_status_idx").on(t.status)],189);190191export const modelSyncRuns = pgTable(192 "model_sync_runs",193 {194 id: text("id").primaryKey(),195 provider: text("provider").$type<ProviderId>().notNull(),196 triggeredBy: text("triggered_by").notNull().default("schedule"), // startup | schedule | admin | user197 startedAt: ts("started_at").notNull().default(now()),198 finishedAt: ts("finished_at"),199 ok: boolean("ok"),200 modelsFound: integer("models_found"),201 modelsAdded: integer("models_added"),202 modelsRemoved: integer("models_removed"),203 latencyMs: integer("latency_ms"),204 errorCode: text("error_code"),205 errorMessage: text("error_message"),206 },207 (t) => [index("model_sync_runs_provider_started_idx").on(t.provider, t.startedAt)],208);209210export const userModelFavorites = pgTable(211 "user_model_favorites",212 {213 userId: text("user_id")214 .notNull()215 .references(() => users.id, { onDelete: "cascade" }),216 modelKey: text("model_key").notNull(),217 createdAt: ts("created_at").notNull().default(now()),218 },219 (t) => [primaryKey({ columns: [t.userId, t.modelKey] })],220);221222export const userModelRecents = pgTable(223 "user_model_recents",224 {225 userId: text("user_id")226 .notNull()227 .references(() => users.id, { onDelete: "cascade" }),228 modelKey: text("model_key").notNull(),229 usedAt: ts("used_at").notNull().default(now()),230 uses: integer("uses").notNull().default(1),231 },232 (t) => [primaryKey({ columns: [t.userId, t.modelKey] })],233);234235// ---------------------------------------------------------------------------236// Conversations237// ---------------------------------------------------------------------------238export const folders = pgTable(239 "folders",240 {241 id: text("id").primaryKey(),242 userId: text("user_id")243 .notNull()244 .references(() => users.id, { onDelete: "cascade" }),245 name: text("name").notNull(),246 color: text("color"),247 sortOrder: integer("sort_order").notNull().default(0),248 createdAt: ts("created_at").notNull().default(now()),249 },250 (t) => [index("folders_user_idx").on(t.userId)],251);252253export const tags = pgTable(254 "tags",255 {256 id: text("id").primaryKey(),257 userId: text("user_id")258 .notNull()259 .references(() => users.id, { onDelete: "cascade" }),260 name: text("name").notNull(),261 color: text("color"),262 createdAt: ts("created_at").notNull().default(now()),263 },264 (t) => [uniqueIndex("tags_user_name_uq").on(t.userId, t.name)],265);266267export const conversations = pgTable(268 "conversations",269 {270 id: text("id").primaryKey(),271 userId: text("user_id")272 .notNull()273 .references(() => users.id, { onDelete: "cascade" }),274 title: text("title").notNull().default("New chat"),275 titleSource: text("title_source").notNull().default("auto"), // auto | user276 folderId: text("folder_id").references(() => folders.id, { onDelete: "set null" }),277 /** Workspace the conversation belongs to (see schema-workspace.ts `projects`). No FK: projects may be deleted independently. */278 projectId: text("project_id"),279 pinned: boolean("pinned").notNull().default(false),280 archived: boolean("archived").notNull().default(false),281 /** Last model used (`provider/modelId`). */282 modelKey: text("model_key"),283 provider: text("provider").$type<ProviderId>(),284 systemPrompt: text("system_prompt"),285 /** Unified generation settings (temperature, maxTokens, reasoningEffort, tools…). */286 settings: jsonb("settings").$type<Record<string, unknown>>().notNull().default({}),287 parentConversationId: text("parent_conversation_id"),288 branchedFromMessageId: text("branched_from_message_id"),289 messageCount: integer("message_count").notNull().default(0),290 totalCostUsd: doublePrecision("total_cost_usd").notNull().default(0),291 totalInputTokens: integer("total_input_tokens").notNull().default(0),292 totalOutputTokens: integer("total_output_tokens").notNull().default(0),293 lastMessageAt: ts("last_message_at"),294 createdAt: ts("created_at").notNull().default(now()),295 updatedAt: ts("updated_at").notNull().default(now()),296 },297 (t) => [298 index("conversations_user_updated_idx").on(t.userId, t.updatedAt),299 index("conversations_user_folder_idx").on(t.userId, t.folderId),300 index("conversations_user_model_idx").on(t.userId, t.modelKey),301 index("conversations_user_project_idx").on(t.userId, t.projectId),302 ],303);304305export const conversationTags = pgTable(306 "conversation_tags",307 {308 conversationId: text("conversation_id")309 .notNull()310 .references(() => conversations.id, { onDelete: "cascade" }),311 tagId: text("tag_id")312 .notNull()313 .references(() => tags.id, { onDelete: "cascade" }),314 },315 (t) => [primaryKey({ columns: [t.conversationId, t.tagId] })],316);317318export const messages = pgTable(319 "messages",320 {321 id: text("id").primaryKey(),322 conversationId: text("conversation_id")323 .notNull()324 .references(() => conversations.id, { onDelete: "cascade" }),325 userId: text("user_id")326 .notNull()327 .references(() => users.id, { onDelete: "cascade" }),328 role: text("role").notNull(), // user | assistant | system329 /** Plain markdown text (search + export). Derived from `parts`. */330 content: text("content").notNull().default(""),331 /** Ordered content parts: text, reasoning, tool-call, tool-result, attachment, citation, structured. */332 parts: jsonb("parts").$type<unknown[]>().notNull().default([]),333 modelKey: text("model_key"),334 provider: text("provider").$type<ProviderId>(),335 status: text("status").notNull().default("complete"), // complete | streaming | stopped | error336 finishReason: text("finish_reason"),337 error: jsonb("error").$type<{ code: string; message: string } | null>(),338 usage: jsonb("usage").$type<Record<string, number> | null>(),339 settings: jsonb("settings").$type<Record<string, unknown> | null>(),340 latencyMs: integer("latency_ms"),341 ttftMs: integer("ttft_ms"),342 costUsd: doublePrecision("cost_usd"),343 /** Edits/regenerations: siblings share `parentMessageId`; `version` orders them; `active` marks the shown one. */344 parentMessageId: text("parent_message_id"),345 version: integer("version").notNull().default(1),346 active: boolean("active").notNull().default(true),347 createdAt: ts("created_at").notNull().default(now()),348 updatedAt: ts("updated_at").notNull().default(now()),349 },350 (t) => [351 index("messages_conversation_created_idx").on(t.conversationId, t.createdAt),352 index("messages_user_idx").on(t.userId),353 ],354);355356export const messageAttachments = pgTable(357 "message_attachments",358 {359 id: text("id").primaryKey(),360 userId: text("user_id")361 .notNull()362 .references(() => users.id, { onDelete: "cascade" }),363 messageId: text("message_id").references(() => messages.id, { onDelete: "cascade" }),364 conversationId: text("conversation_id").references(() => conversations.id, { onDelete: "cascade" }),365 kind: text("kind").notNull(), // image | pdf | text | code | csv | json | document366 name: text("name").notNull(),367 mimeType: text("mime_type").notNull(),368 sizeBytes: integer("size_bytes").notNull(),369 /** Base64 payload (max ~10 MB). Kept in Postgres to avoid an object store on the cluster. */370 dataBase64: text("data_base64").notNull(),371 width: integer("width"),372 height: integer("height"),373 createdAt: ts("created_at").notNull().default(now()),374 },375 (t) => [index("attachments_message_idx").on(t.messageId), index("attachments_user_idx").on(t.userId)],376);377378export const sharedConversations = pgTable(379 "shared_conversations",380 {381 /** Public slug used in `/share/<id>`. */382 id: text("id").primaryKey(),383 conversationId: text("conversation_id")384 .notNull()385 .references(() => conversations.id, { onDelete: "cascade" }),386 userId: text("user_id")387 .notNull()388 .references(() => users.id, { onDelete: "cascade" }),389 title: text("title").notNull(),390 /** Frozen copy of the messages at share time (no live updates, no attachments data). */391 snapshot: jsonb("snapshot").$type<unknown[]>().notNull(),392 isPublic: boolean("is_public").notNull().default(true),393 viewCount: integer("view_count").notNull().default(0),394 createdAt: ts("created_at").notNull().default(now()),395 revokedAt: ts("revoked_at"),396 },397 (t) => [index("shared_conversation_idx").on(t.conversationId)],398);399400// ---------------------------------------------------------------------------401// Presets402// ---------------------------------------------------------------------------403export const promptPresets = pgTable(404 "prompt_presets",405 {406 id: text("id").primaryKey(),407 userId: text("user_id")408 .notNull()409 .references(() => users.id, { onDelete: "cascade" }),410 name: text("name").notNull(),411 description: text("description"),412 icon: text("icon"),413 systemPrompt: text("system_prompt").notNull(),414 defaultModelKey: text("default_model_key"),415 parameters: jsonb("parameters").$type<Record<string, unknown>>().notNull().default({}),416 tools: jsonb("tools").$type<Record<string, unknown>>().notNull().default({}),417 sortOrder: integer("sort_order").notNull().default(0),418 createdAt: ts("created_at").notNull().default(now()),419 updatedAt: ts("updated_at").notNull().default(now()),420 },421 (t) => [index("prompt_presets_user_idx").on(t.userId)],422);423424export const modelPresets = pgTable(425 "model_presets",426 {427 id: text("id").primaryKey(),428 userId: text("user_id")429 .notNull()430 .references(() => users.id, { onDelete: "cascade" }),431 name: text("name").notNull(),432 description: text("description"),433 icon: text("icon"),434 modelKey: text("model_key").notNull(),435 systemPrompt: text("system_prompt"),436 parameters: jsonb("parameters").$type<Record<string, unknown>>().notNull().default({}),437 tools: jsonb("tools").$type<Record<string, unknown>>().notNull().default({}),438 fileSettings: jsonb("file_settings").$type<Record<string, unknown>>().notNull().default({}),439 sortOrder: integer("sort_order").notNull().default(0),440 createdAt: ts("created_at").notNull().default(now()),441 updatedAt: ts("updated_at").notNull().default(now()),442 },443 (t) => [index("model_presets_user_idx").on(t.userId)],444);445446// ---------------------------------------------------------------------------447// Usage & Arena448// ---------------------------------------------------------------------------449export const usageRecords = pgTable(450 "usage_records",451 {452 id: text("id").primaryKey(),453 userId: text("user_id")454 .notNull()455 .references(() => users.id, { onDelete: "cascade" }),456 conversationId: text("conversation_id"),457 messageId: text("message_id"),458 arenaSessionId: text("arena_session_id"),459 provider: text("provider").$type<ProviderId>().notNull(),460 modelKey: text("model_key").notNull(),461 kind: text("kind").notNull().default("chat"), // chat | arena | validate | title | structured462 status: text("status").notNull().default("ok"), // ok | error | stopped463 errorCode: text("error_code"),464 inputTokens: integer("input_tokens").notNull().default(0),465 outputTokens: integer("output_tokens").notNull().default(0),466 cachedTokens: integer("cached_tokens").notNull().default(0),467 reasoningTokens: integer("reasoning_tokens").notNull().default(0),468 costUsd: doublePrecision("cost_usd"),469 latencyMs: integer("latency_ms"),470 ttftMs: integer("ttft_ms"),471 createdAt: ts("created_at").notNull().default(now()),472 },473 (t) => [474 index("usage_user_created_idx").on(t.userId, t.createdAt),475 index("usage_user_provider_idx").on(t.userId, t.provider),476 index("usage_user_model_idx").on(t.userId, t.modelKey),477 ],478);479480export const arenaSessions = pgTable(481 "arena_sessions",482 {483 id: text("id").primaryKey(),484 userId: text("user_id")485 .notNull()486 .references(() => users.id, { onDelete: "cascade" }),487 prompt: text("prompt").notNull(),488 systemPrompt: text("system_prompt"),489 modelKeys: jsonb("model_keys").$type<string[]>().notNull(),490 settings: jsonb("settings").$type<Record<string, unknown>>().notNull().default({}),491 createdAt: ts("created_at").notNull().default(now()),492 },493 (t) => [index("arena_sessions_user_idx").on(t.userId, t.createdAt)],494);495496export const arenaResponses = pgTable(497 "arena_responses",498 {499 id: text("id").primaryKey(),500 sessionId: text("session_id")501 .notNull()502 .references(() => arenaSessions.id, { onDelete: "cascade" }),503 modelKey: text("model_key").notNull(),504 provider: text("provider").$type<ProviderId>().notNull(),505 content: text("content").notNull().default(""),506 reasoning: text("reasoning"),507 status: text("status").notNull().default("complete"),508 error: jsonb("error").$type<{ code: string; message: string } | null>(),509 usage: jsonb("usage").$type<Record<string, number> | null>(),510 latencyMs: integer("latency_ms"),511 ttftMs: integer("ttft_ms"),512 costUsd: doublePrecision("cost_usd"),513 ratings: jsonb("ratings").$type<Record<string, boolean>>().notNull().default({}),514 createdAt: ts("created_at").notNull().default(now()),515 },516 (t) => [index("arena_responses_session_idx").on(t.sessionId)],517);518519export type User = typeof users.$inferSelect;520export type ProviderConnection = typeof providerConnections.$inferSelect;521export type ModelRow = typeof models.$inferSelect;522export type Conversation = typeof conversations.$inferSelect;523export type Message = typeof messages.$inferSelect;524export type MessageAttachment = typeof messageAttachments.$inferSelect;525export type UsageRecord = typeof usageRecords.$inferSelect;526export type PromptPreset = typeof promptPresets.$inferSelect;527export type ModelPreset = typeof modelPresets.$inferSelect;528export type Folder = typeof folders.$inferSelect;529export type UserPreferences = typeof userPreferences.$inferSelect;530export type ArenaSession = typeof arenaSessions.$inferSelect;531export type ArenaResponse = typeof arenaResponses.$inferSelect;532