spb/vquant Public MIT

1/*2 * =============================================================================3 *  VibeQuant (vquant) — AI-Powered Financial Intelligence Platform4 * -----------------------------------------------------------------------------5 *  File:      shared/schema-sqlite.ts6 *7 *  Author:    Simon-Pierre Boucher8 *  Contact:   contact@spboucher.ai9 *  Website:   https://www.spboucher.ai10 *  Demo:      https://www.vquant.ai11 *  License:   MIT (see LICENSE)12 *13 *  Copyright © 2026 Simon-Pierre Boucher. All rights reserved.14 * =============================================================================15 */1617import { sqliteTable, text, integer, real } from "drizzle-orm/sqlite-core";18import { createInsertSchema } from "drizzle-zod";19import { z } from "zod";2021// Re-export shared API types22export type { SearchResult, ClaudeMessage, ClaudeToolCall, ClaudeToolResult, ChatRequest, ChatResponse, ConversationMessage } from "./types";2324// Users table with token-based authentication25export const users = sqliteTable("users", {26  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),27  displayName: text("display_name").notNull(),28  token: text("token").notNull().unique(),29  createdAt: integer("created_at", { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),30});3132// Crawled pages table - stores web content33export const crawledPages = sqliteTable("crawled_pages", {34  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),35  url: text("url").notNull().unique(),36  title: text("title").notNull(),37  content: text("content").notNull(),38  snippet: text("snippet"),39  favicon: text("favicon"),40  crawledAt: integer("crawled_at", { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),41});4243// Embeddings table - stores vector embeddings for semantic search44// Note: SQLite doesn't have native vector support, we'll store as JSON text45export const embeddings = sqliteTable("embeddings", {46  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),47  pageId: text("page_id").notNull().unique().references(() => crawledPages.id, { onDelete: "cascade" }),48  embedding: text("embedding"), // JSON string of vector array49  tokenCount: integer("token_count").notNull(),50  createdAt: integer("created_at", { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),51});5253// Chat messages table - stores conversation history54export const messages = sqliteTable("messages", {55  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),56  sessionId: text("session_id").notNull(),57  role: text("role").notNull(), // 'user' or 'assistant'58  content: text("content").notNull(),59  sources: text("sources"), // JSON array of URLs60  createdAt: integer("created_at", { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),61});6263// Shared reports table - stores crystallized reports with unique links64export const sharedReports = sqliteTable("shared_reports", {65  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),66  shareId: text("share_id").notNull().unique(), // Unique short ID for the share link67  question: text("question").notNull(),68  answer: text("answer").notNull(),69  toolResults: text("tool_results"),70  sources: text("sources"),71  customPythonFigures: text("custom_python_figures"),72  createdAt: integer("created_at", { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),73});7475// Conversation sessions table - stores complete conversation sessions with all data76export const conversationSessions = sqliteTable("conversation_sessions", {77  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),78  sessionId: text("session_id").notNull().unique(), // Session identifier79  userId: text("user_id").references(() => users.id, { onDelete: "cascade" }), // Owner of the session (null for anonymous)80  title: text("title").notNull(), // First question or custom title81  messages: text("messages").notNull(), // JSON array of { question, answer, toolResults, sources, searchQueries, pythonCode, monteCarloResults }82  inputTokens: integer("input_tokens").default(0), // Total input tokens used in this session83  outputTokens: integer("output_tokens").default(0), // Total output tokens used in this session84  totalCost: real("total_cost").default(0), // Total cost in USD (calculated as $3/$15 per million tokens)85  createdAt: integer("created_at", { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),86  updatedAt: integer("updated_at", { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),87});8889// Active users tracking - tracks who is currently generating content90export const activeUsers = sqliteTable("active_users", {91  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),92  sessionId: text("session_id").notNull().unique(), // Browser session ID (not conversation session)93  userId: text("user_id").references(() => users.id, { onDelete: "cascade" }), // User ID if logged in94  status: text("status").notNull().default("idle"), // idle, generating, error95  currentQuery: text("current_query"), // Current question being processed96  lastHeartbeat: integer("last_heartbeat", { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),97  userAgent: text("user_agent"),98  ipAddress: text("ip_address"),99  createdAt: integer("created_at", { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),100});101102// Analytics metrics - stores aggregated metrics over time103export const analyticsMetrics = sqliteTable("analytics_metrics", {104  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),105  timestamp: integer("timestamp", { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),106107  // Request metrics108  totalRequests: integer("total_requests").default(0),109  successfulRequests: integer("successful_requests").default(0),110  failedRequests: integer("failed_requests").default(0),111112  // User metrics113  activeUsers: integer("active_users").default(0), // Count of users actively generating114  uniqueVisitors: integer("unique_visitors").default(0), // Unique sessions in this period115116  // Performance metrics117  averageResponseTime: real("average_response_time").default(0), // In milliseconds118  peakResponseTime: real("peak_response_time").default(0),119120  // Token metrics121  tokensGenerated: integer("tokens_generated").default(0),122  estimatedCost: real("estimated_cost").default(0),123124  // Tool usage125  toolCallsCount: integer("tool_calls_count").default(0),126  pythonExecutions: integer("python_executions").default(0),127  searchQueries: integer("search_queries").default(0),128129  // Error tracking130  errorCount: integer("error_count").default(0),131  errorRate: real("error_rate").default(0), // Percentage132133  // Period type (for aggregation)134  periodType: text("period_type").notNull().default("minute"), // minute, hour, day135});136137// Request logs - detailed logs for each request138export const requestLogs = sqliteTable("request_logs", {139  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),140  sessionId: text("session_id").notNull(),141  userId: text("user_id").references(() => users.id, { onDelete: "cascade" }),142143  query: text("query").notNull(),144  responseTime: real("response_time"), // In milliseconds145146  inputTokens: integer("input_tokens").default(0),147  outputTokens: integer("output_tokens").default(0),148  totalCost: real("total_cost").default(0),149150  toolsCalled: text("tools_called"), // JSON array of tool names151152  status: text("status").notNull(), // success, error, timeout153  errorMessage: text("error_message"),154155  timestamp: integer("timestamp", { mode: 'timestamp' }).notNull().$defaultFn(() => new Date()),156});157158// Zod schemas for validation159export const insertUserSchema = createInsertSchema(users).omit({160  id: true,161  createdAt: true,162});163164export const insertCrawledPageSchema = createInsertSchema(crawledPages).omit({165  id: true,166  crawledAt: true,167});168169export const insertEmbeddingSchema = createInsertSchema(embeddings).omit({170  id: true,171  createdAt: true,172});173174export const insertMessageSchema = createInsertSchema(messages).omit({175  id: true,176  createdAt: true,177});178179export const insertSharedReportSchema = createInsertSchema(sharedReports).omit({180  id: true,181  createdAt: true,182});183184export const insertConversationSessionSchema = createInsertSchema(conversationSessions).omit({185  id: true,186  createdAt: true,187  updatedAt: true,188});189190export const insertActiveUserSchema = createInsertSchema(activeUsers).omit({191  id: true,192  createdAt: true,193});194195export const insertAnalyticsMetricSchema = createInsertSchema(analyticsMetrics).omit({196  id: true,197  timestamp: true,198});199200export const insertRequestLogSchema = createInsertSchema(requestLogs).omit({201  id: true,202  timestamp: true,203});204205// TypeScript types206export type User = typeof users.$inferSelect;207export type InsertUser = z.infer<typeof insertUserSchema>;208209export type CrawledPage = typeof crawledPages.$inferSelect;210export type InsertCrawledPage = z.infer<typeof insertCrawledPageSchema>;211212export type Embedding = typeof embeddings.$inferSelect;213export type InsertEmbedding = z.infer<typeof insertEmbeddingSchema>;214215export type Message = typeof messages.$inferSelect;216export type InsertMessage = z.infer<typeof insertMessageSchema>;217218export type SharedReport = typeof sharedReports.$inferSelect;219export type InsertSharedReport = z.infer<typeof insertSharedReportSchema>;220221export type ConversationSession = typeof conversationSessions.$inferSelect;222export type InsertConversationSession = z.infer<typeof insertConversationSessionSchema>;223224export type ActiveUser = typeof activeUsers.$inferSelect;225export type InsertActiveUser = z.infer<typeof insertActiveUserSchema>;226227export type AnalyticsMetric = typeof analyticsMetrics.$inferSelect;228export type InsertAnalyticsMetric = z.infer<typeof insertAnalyticsMetricSchema>;229230export type RequestLog = typeof requestLogs.$inferSelect;231export type InsertRequestLog = z.infer<typeof insertRequestLogSchema>;232233