spb/vquant Public MIT

1<!--2  =============================================================================3   VibeQuant (vquant) — AI-Powered Financial Intelligence Platform4  -----------------------------------------------------------------------------5   File:      docs/DATABASE.md67   Author:    Simon-Pierre Boucher8   Contact:   contact@spboucher.ai9   Website:   https://www.spboucher.ai10   Demo:      https://www.vquant.ai11   License:   MIT (see LICENSE)1213   Copyright © 2026 Simon-Pierre Boucher. All rights reserved.14  =============================================================================15-->1617# Database Schema1819VibeQuant supports both SQLite (development) and PostgreSQL (production) via Drizzle ORM. Both schemas are kept in parity.2021## Configuration2223```bash24# SQLite (default for development)25DATABASE_URL=sqlite://local.db2627# PostgreSQL (production)28DATABASE_URL=postgresql://user:pass@host:5432/dbname29```3031## Tables3233### `users`3435User accounts with token-based authentication.3637| Column | Type | Constraints | Description |38|--------|------|-------------|-------------|39| `id` | text | PK, UUID | Unique user ID |40| `displayName` | text | NOT NULL | Display name |41| `token` | text | NOT NULL, UNIQUE | Authentication token (vquant-XXXXXXX) |42| `createdAt` | timestamp | NOT NULL | Account creation date |4344### `messages`4546Individual chat messages (user and assistant).4748| Column | Type | Constraints | Description |49|--------|------|-------------|-------------|50| `id` | text | PK, UUID | Message ID |51| `sessionId` | text | NOT NULL | Session this message belongs to |52| `role` | text | NOT NULL | "user" or "assistant" |53| `content` | text | NOT NULL | Message content |54| `sources` | text | | JSON string of source URLs |55| `createdAt` | timestamp | NOT NULL | Message timestamp |5657### `conversationSessions`5859Complete conversation sessions with token tracking.6061| Column | Type | Constraints | Description |62|--------|------|-------------|-------------|63| `id` | text | PK, UUID | Session record ID |64| `sessionId` | text | NOT NULL, UNIQUE | Session identifier |65| `userId` | text | FK -> users.id | Owner (null for anonymous) |66| `title` | text | NOT NULL | First question or custom title |67| `messages` | text | NOT NULL | JSON array of {question, answer, toolResults, sources} |68| `inputTokens` | integer | DEFAULT 0 | Total input tokens consumed |69| `outputTokens` | integer | DEFAULT 0 | Total output tokens consumed |70| `totalCost` | real | DEFAULT 0 | Estimated cost in USD |71| `createdAt` | timestamp | NOT NULL | Session creation |72| `updatedAt` | timestamp | NOT NULL | Last activity |7374### `sharedReports`7576Shareable analysis reports with unique URLs.7778| Column | Type | Constraints | Description |79|--------|------|-------------|-------------|80| `id` | text | PK, UUID | Report record ID |81| `shareId` | text | NOT NULL, UNIQUE | Short URL ID (8 chars) |82| `question` | text | NOT NULL | Original question |83| `answer` | text | NOT NULL | Full answer |84| `toolResults` | text | | JSON string of tool results |85| `sources` | text | | JSON string of search results |86| `customPythonFigures` | text | | JSON string of figure data |87| `createdAt` | timestamp | NOT NULL | Report creation |8889### `activeUsers`9091Real-time user activity tracking.9293| Column | Type | Constraints | Description |94|--------|------|-------------|-------------|95| `id` | text | PK, UUID | Record ID |96| `sessionId` | text | NOT NULL, UNIQUE | Browser session ID |97| `userId` | text | FK -> users.id | Logged-in user (or null) |98| `status` | text | NOT NULL, DEFAULT "idle" | idle / generating / error |99| `currentQuery` | text | | Query being processed |100| `lastHeartbeat` | timestamp | NOT NULL | Last activity ping |101| `userAgent` | text | | Browser user agent |102| `ipAddress` | text | | Client IP address |103| `createdAt` | timestamp | NOT NULL | First seen |104105### `analyticsMetrics`106107Aggregated platform metrics over time.108109| Column | Type | Default | Description |110|--------|------|---------|-------------|111| `id` | text | PK | Record ID |112| `timestamp` | timestamp | | Metric timestamp |113| `totalRequests` | integer | 0 | Total API requests |114| `successfulRequests` | integer | 0 | Successful requests |115| `failedRequests` | integer | 0 | Failed requests |116| `activeUsers` | integer | 0 | Active user count |117| `uniqueVisitors` | integer | 0 | Unique visitors |118| `averageResponseTime` | real | 0 | Avg response time (ms) |119| `peakResponseTime` | real | 0 | Peak response time (ms) |120| `tokensGenerated` | integer | 0 | Tokens generated |121| `estimatedCost` | real | 0 | Estimated cost (USD) |122| `toolCallsCount` | integer | 0 | Tool calls made |123| `pythonExecutions` | integer | 0 | Python scripts run |124| `searchQueries` | integer | 0 | Web searches performed |125| `errorCount` | integer | 0 | Errors occurred |126| `errorRate` | real | 0 | Error rate (%) |127| `periodType` | text | "minute" | minute / hour / day |128129### `requestLogs`130131Detailed per-request logging.132133| Column | Type | Description |134|--------|------|-------------|135| `id` | text | Record ID |136| `sessionId` | text | Browser session |137| `userId` | text | User (FK) |138| `query` | text | User query |139| `responseTime` | real | Response time (ms) |140| `inputTokens` | integer | Input tokens |141| `outputTokens` | integer | Output tokens |142| `totalCost` | real | Request cost (USD) |143| `toolsCalled` | text | JSON array of tool names |144| `status` | text | success / error / timeout |145| `errorMessage` | text | Error details |146| `timestamp` | timestamp | Request timestamp |147148### `crawledPages`149150Cached web content from Firecrawl/Tavily.151152| Column | Type | Description |153|--------|------|-------------|154| `id` | text | Record ID |155| `url` | text | Page URL (UNIQUE) |156| `title` | text | Page title |157| `content` | text | Full page content |158| `snippet` | text | Short excerpt |159| `favicon` | text | Favicon URL |160| `crawledAt` | timestamp | Crawl timestamp |161162### `embeddings`163164Vector embeddings for semantic search (PostgreSQL only).165166| Column | Type | Description |167|--------|------|-------------|168| `id` | text | Record ID |169| `pageId` | text | FK -> crawledPages.id |170| `embedding` | vector(3072) / text | Embedding vector |171| `tokenCount` | integer | Token count |172| `createdAt` | timestamp | Creation timestamp |173