spb/chat-spboucher Public

1// Author: Simon-Pierre Boucher2// Contact: contact@spboucher.ai3// Project: chat.spboucher.ai45import type { Database } from "better-sqlite3";67interface Migration {8  version: number;9  up: string;10}1112const migrations: Migration[] = [13  {14    version: 1,15    up: `16      CREATE TABLE IF NOT EXISTS users (17        id TEXT PRIMARY KEY,18        username TEXT NOT NULL UNIQUE,19        password_hash TEXT NOT NULL,20        totp_secret TEXT,21        created_at INTEGER NOT NULL,22        updated_at INTEGER NOT NULL23      );2425      CREATE TABLE IF NOT EXISTS sessions (26        id TEXT PRIMARY KEY,               -- sha256 of the raw token; raw token lives only in the cookie27        user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,28        created_at INTEGER NOT NULL,29        expires_at INTEGER NOT NULL,30        last_seen_at INTEGER NOT NULL,31        user_agent TEXT,32        ip TEXT33      );34      CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id);35      CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);3637      CREATE TABLE IF NOT EXISTS login_attempts (38        id INTEGER PRIMARY KEY AUTOINCREMENT,39        ip TEXT NOT NULL,40        success INTEGER NOT NULL DEFAULT 0,41        created_at INTEGER NOT NULL42      );43      CREATE INDEX IF NOT EXISTS idx_login_attempts_ip ON login_attempts(ip, created_at);4445      CREATE TABLE IF NOT EXISTS conversations (46        id TEXT PRIMARY KEY,47        title TEXT NOT NULL DEFAULT 'New conversation',48        pinned INTEGER NOT NULL DEFAULT 0,49        current_leaf_id TEXT,50        created_at INTEGER NOT NULL,51        updated_at INTEGER NOT NULL52      );53      CREATE INDEX IF NOT EXISTS idx_conversations_updated ON conversations(updated_at DESC);5455      -- Messages form a tree: branching = multiple children of the same parent.56      CREATE TABLE IF NOT EXISTS messages (57        id TEXT PRIMARY KEY,58        conversation_id TEXT NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,59        parent_id TEXT REFERENCES messages(id) ON DELETE CASCADE,60        role TEXT NOT NULL CHECK (role IN ('user','assistant','system')),61        content TEXT NOT NULL DEFAULT '',62        reasoning TEXT,63        -- model attribution, stored denormalized so history survives catalog changes64        model_id TEXT,65        model_name TEXT,66        provider TEXT,67        generation_id TEXT,68        status TEXT NOT NULL DEFAULT 'completed' CHECK (status IN ('pending','streaming','completed','cancelled','failed')),69        error_message TEXT,70        created_at INTEGER NOT NULL71      );72      CREATE INDEX IF NOT EXISTS idx_messages_conversation ON messages(conversation_id, created_at);73      CREATE INDEX IF NOT EXISTS idx_messages_parent ON messages(parent_id);7475      -- Explicit generation state machine: queued → starting → streaming → completed | cancelled | failed76      CREATE TABLE IF NOT EXISTS generations (77        id TEXT PRIMARY KEY,78        message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,79        conversation_id TEXT NOT NULL,80        model_id TEXT NOT NULL,81        state TEXT NOT NULL DEFAULT 'queued' CHECK (state IN ('queued','starting','streaming','completed','cancelled','failed')),82        error_code TEXT,83        error_message TEXT,84        openrouter_generation_id TEXT,85        created_at INTEGER NOT NULL,86        finished_at INTEGER87      );88      CREATE INDEX IF NOT EXISTS idx_generations_message ON generations(message_id);8990      CREATE TABLE IF NOT EXISTS generation_usage (91        id INTEGER PRIMARY KEY AUTOINCREMENT,92        generation_id TEXT NOT NULL REFERENCES generations(id) ON DELETE CASCADE,93        model_id TEXT NOT NULL,94        prompt_tokens INTEGER,95        completion_tokens INTEGER,96        reasoning_tokens INTEGER,97        cached_tokens INTEGER,98        total_tokens INTEGER,99        estimated_cost_usd REAL,100        reported_cost_usd REAL,101        created_at INTEGER NOT NULL102      );103      CREATE INDEX IF NOT EXISTS idx_usage_created ON generation_usage(created_at);104      CREATE INDEX IF NOT EXISTS idx_usage_model ON generation_usage(model_id);105106      -- Dynamic model catalog cache, synced from OpenRouter. Never hand-maintained.107      CREATE TABLE IF NOT EXISTS models (108        openrouter_model_id TEXT PRIMARY KEY,   -- opaque, sent back verbatim109        name TEXT NOT NULL,110        provider TEXT,111        description TEXT,112        context_length INTEGER,113        pricing_prompt REAL,114        pricing_completion REAL,115        pricing_image REAL,116        pricing_request REAL,117        cap_text INTEGER NOT NULL DEFAULT 1,118        cap_vision INTEGER NOT NULL DEFAULT 0,119        cap_reasoning INTEGER NOT NULL DEFAULT 0,120        cap_tools INTEGER NOT NULL DEFAULT 0,121        cap_structured INTEGER NOT NULL DEFAULT 0,122        architecture TEXT,123        tokenizer TEXT,124        raw_json TEXT,125        available INTEGER NOT NULL DEFAULT 1,126        or_created_at INTEGER,127        first_seen_at INTEGER NOT NULL,128        updated_at INTEGER NOT NULL129      );130      CREATE INDEX IF NOT EXISTS idx_models_available ON models(available);131132      CREATE TABLE IF NOT EXISTS model_prefs (133        model_id TEXT PRIMARY KEY,134        favorite INTEGER NOT NULL DEFAULT 0,135        pinned INTEGER NOT NULL DEFAULT 0,136        last_used_at INTEGER,137        use_count INTEGER NOT NULL DEFAULT 0138      );139140      CREATE TABLE IF NOT EXISTS settings (141        key TEXT PRIMARY KEY,142        value TEXT NOT NULL143      );144    `,145  },146];147148export function migrate(db: Database): void {149  db.exec(`CREATE TABLE IF NOT EXISTS schema_migrations (150    version INTEGER PRIMARY KEY,151    applied_at INTEGER NOT NULL152  )`);153  const applied = new Set(154    (db.prepare("SELECT version FROM schema_migrations").all() as { version: number }[]).map(155      (r) => r.version156    )157  );158  for (const m of migrations) {159    if (applied.has(m.version)) continue;160    const tx = db.transaction(() => {161      db.exec(m.up);162      db.prepare("INSERT INTO schema_migrations (version, applied_at) VALUES (?, ?)").run(163        m.version,164        Date.now()165      );166    });167    tx();168  }169}170