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 pinned: boolean("pinned").notNull().default(false),278 archived: boolean("archived").notNull().default(false),279 /** Last model used (`provider/modelId`). */280 modelKey: text("model_key"),281 provider: text("provider").$type<ProviderId>(),282 systemPrompt: text("system_prompt"),283 /** Unified generation settings (temperature, maxTokens, reasoningEffort, tools…). */284 settings: jsonb("settings").$type<Record<string, unknown>>().notNull().default({}),285 parentConversationId: text("parent_conversation_id"),286 branchedFromMessageId: text("branched_from_message_id"),287 messageCount: integer("message_count").notNull().default(0),288 totalCostUsd: doublePrecision("total_cost_usd").notNull().default(0),289 totalInputTokens: integer("total_input_tokens").notNull().default(0),290 totalOutputTokens: integer("total_output_tokens").notNull().default(0),291 lastMessageAt: ts("last_message_at"),292 createdAt: ts("created_at").notNull().default(now()),293 updatedAt: ts("updated_at").notNull().default(now()),294 },295 (t) => [296 index("conversations_user_updated_idx").on(t.userId, t.updatedAt),297 index("conversations_user_folder_idx").on(t.userId, t.folderId),298 index("conversations_user_model_idx").on(t.userId, t.modelKey),299 ],300);301302export const conversationTags = pgTable(303 "conversation_tags",304 {305 conversationId: text("conversation_id")306 .notNull()307 .references(() => conversations.id, { onDelete: "cascade" }),308 tagId: text("tag_id")309 .notNull()310 .references(() => tags.id, { onDelete: "cascade" }),311 },312 (t) => [primaryKey({ columns: [t.conversationId, t.tagId] })],313);314315export const messages = pgTable(316 "messages",317 {318 id: text("id").primaryKey(),319 conversationId: text("conversation_id")320 .notNull()321 .references(() => conversations.id, { onDelete: "cascade" }),322 userId: text("user_id")323 .notNull()324 .references(() => users.id, { onDelete: "cascade" }),325 role: text("role").notNull(), // user | assistant | system326 /** Plain markdown text (search + export). Derived from `parts`. */327 content: text("content").notNull().default(""),328 /** Ordered content parts: text, reasoning, tool-call, tool-result, attachment, citation, structured. */329 parts: jsonb("parts").$type<unknown[]>().notNull().default([]),330 modelKey: text("model_key"),331 provider: text("provider").$type<ProviderId>(),332 status: text("status").notNull().default("complete"), // complete | streaming | stopped | error333 finishReason: text("finish_reason"),334 error: jsonb("error").$type<{ code: string; message: string } | null>(),335 usage: jsonb("usage").$type<Record<string, number> | null>(),336 settings: jsonb("settings").$type<Record<string, unknown> | null>(),337 latencyMs: integer("latency_ms"),338 ttftMs: integer("ttft_ms"),339 costUsd: doublePrecision("cost_usd"),340 /** Edits/regenerations: siblings share `parentMessageId`; `version` orders them; `active` marks the shown one. */341 parentMessageId: text("parent_message_id"),342 version: integer("version").notNull().default(1),343 active: boolean("active").notNull().default(true),344 createdAt: ts("created_at").notNull().default(now()),345 updatedAt: ts("updated_at").notNull().default(now()),346 },347 (t) => [348 index("messages_conversation_created_idx").on(t.conversationId, t.createdAt),349 index("messages_user_idx").on(t.userId),350 ],351);352353export const messageAttachments = pgTable(354 "message_attachments",355 {356 id: text("id").primaryKey(),357 userId: text("user_id")358 .notNull()359 .references(() => users.id, { onDelete: "cascade" }),360 messageId: text("message_id").references(() => messages.id, { onDelete: "cascade" }),361 conversationId: text("conversation_id").references(() => conversations.id, { onDelete: "cascade" }),362 kind: text("kind").notNull(), // image | pdf | text | code | csv | json | document363 name: text("name").notNull(),364 mimeType: text("mime_type").notNull(),365 sizeBytes: integer("size_bytes").notNull(),366 /** Base64 payload (max ~10 MB). Kept in Postgres to avoid an object store on the cluster. */367 dataBase64: text("data_base64").notNull(),368 width: integer("width"),369 height: integer("height"),370 createdAt: ts("created_at").notNull().default(now()),371 },372 (t) => [index("attachments_message_idx").on(t.messageId), index("attachments_user_idx").on(t.userId)],373);374375export const sharedConversations = pgTable(376 "shared_conversations",377 {378 /** Public slug used in `/share/<id>`. */379 id: text("id").primaryKey(),380 conversationId: text("conversation_id")381 .notNull()382 .references(() => conversations.id, { onDelete: "cascade" }),383 userId: text("user_id")384 .notNull()385 .references(() => users.id, { onDelete: "cascade" }),386 title: text("title").notNull(),387 /** Frozen copy of the messages at share time (no live updates, no attachments data). */388 snapshot: jsonb("snapshot").$type<unknown[]>().notNull(),389 isPublic: boolean("is_public").notNull().default(true),390 viewCount: integer("view_count").notNull().default(0),391 createdAt: ts("created_at").notNull().default(now()),392 revokedAt: ts("revoked_at"),393 },394 (t) => [index("shared_conversation_idx").on(t.conversationId)],395);396397// ---------------------------------------------------------------------------398// Presets399// ---------------------------------------------------------------------------400export const promptPresets = pgTable(401 "prompt_presets",402 {403 id: text("id").primaryKey(),404 userId: text("user_id")405 .notNull()406 .references(() => users.id, { onDelete: "cascade" }),407 name: text("name").notNull(),408 description: text("description"),409 icon: text("icon"),410 systemPrompt: text("system_prompt").notNull(),411 defaultModelKey: text("default_model_key"),412 parameters: jsonb("parameters").$type<Record<string, unknown>>().notNull().default({}),413 tools: jsonb("tools").$type<Record<string, unknown>>().notNull().default({}),414 sortOrder: integer("sort_order").notNull().default(0),415 createdAt: ts("created_at").notNull().default(now()),416 updatedAt: ts("updated_at").notNull().default(now()),417 },418 (t) => [index("prompt_presets_user_idx").on(t.userId)],419);420421export const modelPresets = pgTable(422 "model_presets",423 {424 id: text("id").primaryKey(),425 userId: text("user_id")426 .notNull()427 .references(() => users.id, { onDelete: "cascade" }),428 name: text("name").notNull(),429 description: text("description"),430 icon: text("icon"),431 modelKey: text("model_key").notNull(),432 systemPrompt: text("system_prompt"),433 parameters: jsonb("parameters").$type<Record<string, unknown>>().notNull().default({}),434 tools: jsonb("tools").$type<Record<string, unknown>>().notNull().default({}),435 fileSettings: jsonb("file_settings").$type<Record<string, unknown>>().notNull().default({}),436 sortOrder: integer("sort_order").notNull().default(0),437 createdAt: ts("created_at").notNull().default(now()),438 updatedAt: ts("updated_at").notNull().default(now()),439 },440 (t) => [index("model_presets_user_idx").on(t.userId)],441);442443// ---------------------------------------------------------------------------444// Usage & Arena445// ---------------------------------------------------------------------------446export const usageRecords = pgTable(447 "usage_records",448 {449 id: text("id").primaryKey(),450 userId: text("user_id")451 .notNull()452 .references(() => users.id, { onDelete: "cascade" }),453 conversationId: text("conversation_id"),454 messageId: text("message_id"),455 arenaSessionId: text("arena_session_id"),456 provider: text("provider").$type<ProviderId>().notNull(),457 modelKey: text("model_key").notNull(),458 kind: text("kind").notNull().default("chat"), // chat | arena | validate | title | structured459 status: text("status").notNull().default("ok"), // ok | error | stopped460 errorCode: text("error_code"),461 inputTokens: integer("input_tokens").notNull().default(0),462 outputTokens: integer("output_tokens").notNull().default(0),463 cachedTokens: integer("cached_tokens").notNull().default(0),464 reasoningTokens: integer("reasoning_tokens").notNull().default(0),465 costUsd: doublePrecision("cost_usd"),466 latencyMs: integer("latency_ms"),467 ttftMs: integer("ttft_ms"),468 createdAt: ts("created_at").notNull().default(now()),469 },470 (t) => [471 index("usage_user_created_idx").on(t.userId, t.createdAt),472 index("usage_user_provider_idx").on(t.userId, t.provider),473 index("usage_user_model_idx").on(t.userId, t.modelKey),474 ],475);476477export const arenaSessions = pgTable(478 "arena_sessions",479 {480 id: text("id").primaryKey(),481 userId: text("user_id")482 .notNull()483 .references(() => users.id, { onDelete: "cascade" }),484 prompt: text("prompt").notNull(),485 systemPrompt: text("system_prompt"),486 modelKeys: jsonb("model_keys").$type<string[]>().notNull(),487 settings: jsonb("settings").$type<Record<string, unknown>>().notNull().default({}),488 createdAt: ts("created_at").notNull().default(now()),489 },490 (t) => [index("arena_sessions_user_idx").on(t.userId, t.createdAt)],491);492493export const arenaResponses = pgTable(494 "arena_responses",495 {496 id: text("id").primaryKey(),497 sessionId: text("session_id")498 .notNull()499 .references(() => arenaSessions.id, { onDelete: "cascade" }),500 modelKey: text("model_key").notNull(),501 provider: text("provider").$type<ProviderId>().notNull(),502 content: text("content").notNull().default(""),503 reasoning: text("reasoning"),504 status: text("status").notNull().default("complete"),505 error: jsonb("error").$type<{ code: string; message: string } | null>(),506 usage: jsonb("usage").$type<Record<string, number> | null>(),507 latencyMs: integer("latency_ms"),508 ttftMs: integer("ttft_ms"),509 costUsd: doublePrecision("cost_usd"),510 ratings: jsonb("ratings").$type<Record<string, boolean>>().notNull().default({}),511 createdAt: ts("created_at").notNull().default(now()),512 },513 (t) => [index("arena_responses_session_idx").on(t.sessionId)],514);515516export type User = typeof users.$inferSelect;517export type ProviderConnection = typeof providerConnections.$inferSelect;518export type ModelRow = typeof models.$inferSelect;519export type Conversation = typeof conversations.$inferSelect;520export type Message = typeof messages.$inferSelect;521export type MessageAttachment = typeof messageAttachments.$inferSelect;522export type UsageRecord = typeof usageRecords.$inferSelect;523export type PromptPreset = typeof promptPresets.$inferSelect;524export type ModelPreset = typeof modelPresets.$inferSelect;525export type Folder = typeof folders.$inferSelect;526export type UserPreferences = typeof userPreferences.$inferSelect;527export type ArenaSession = typeof arenaSessions.$inferSelect;528export type ArenaResponse = typeof arenaResponses.$inferSelect;529