spb/chat-spboucher Public

1// Author: Simon-Pierre Boucher2// Contact: contact@spboucher.ai3// Project: chat.spboucher.ai45// Model catalog: OpenRouter → sync job → SQLite cache → application.6// The catalog is never hand-maintained; removed models are marked unavailable, never deleted.78import { getDb } from "@/lib/db/database";9import { fetchModels } from "@/lib/openrouter";10import type { ModelDefinition } from "@/lib/openrouter";1112const SYNC_INTERVAL_MS = 1000 * 60 * 60 * 4; // refresh every 4 hours1314export interface CatalogModel extends ModelDefinition {15  available: boolean;16  favorite: boolean;17  pinned: boolean;18  lastUsedAt: number | null;19  useCount: number;20}2122interface ModelRow {23  openrouter_model_id: string;24  name: string;25  provider: string | null;26  description: string | null;27  context_length: number | null;28  pricing_prompt: number | null;29  pricing_completion: number | null;30  pricing_image: number | null;31  pricing_request: number | null;32  cap_text: number;33  cap_vision: number;34  cap_reasoning: number;35  cap_tools: number;36  cap_structured: number;37  architecture: string | null;38  tokenizer: string | null;39  available: number;40  or_created_at: number | null;41  favorite: number | null;42  pinned: number | null;43  last_used_at: number | null;44  use_count: number | null;45}4647function rowToModel(r: ModelRow): CatalogModel {48  return {49    id: r.openrouter_model_id,50    name: r.name,51    provider: r.provider ?? undefined,52    description: r.description ?? undefined,53    contextLength: r.context_length ?? undefined,54    pricing: {55      prompt: r.pricing_prompt ?? undefined,56      completion: r.pricing_completion ?? undefined,57      image: r.pricing_image ?? undefined,58      request: r.pricing_request ?? undefined,59    },60    capabilities: {61      text: Boolean(r.cap_text),62      vision: Boolean(r.cap_vision),63      reasoning: Boolean(r.cap_reasoning),64      tools: Boolean(r.cap_tools),65      structuredOutput: Boolean(r.cap_structured),66    },67    metadata: {68      architecture: r.architecture ?? undefined,69      tokenizer: r.tokenizer ?? undefined,70      createdAt: r.or_created_at ?? undefined,71    },72    available: Boolean(r.available),73    favorite: Boolean(r.favorite),74    pinned: Boolean(r.pinned),75    lastUsedAt: r.last_used_at,76    useCount: r.use_count ?? 0,77  };78}7980export async function syncCatalog(): Promise<{ total: number; added: number; removed: number }> {81  const models = await fetchModels();82  const db = getDb();83  const now = Date.now();8485  const upsert = db.prepare(`86    INSERT INTO models (87      openrouter_model_id, name, provider, description, context_length,88      pricing_prompt, pricing_completion, pricing_image, pricing_request,89      cap_text, cap_vision, cap_reasoning, cap_tools, cap_structured,90      architecture, tokenizer, raw_json, available, or_created_at, first_seen_at, updated_at91    ) VALUES (92      @id, @name, @provider, @description, @contextLength,93      @pPrompt, @pCompletion, @pImage, @pRequest,94      @cText, @cVision, @cReasoning, @cTools, @cStructured,95      @architecture, @tokenizer, @raw, 1, @orCreatedAt, @now, @now96    )97    ON CONFLICT(openrouter_model_id) DO UPDATE SET98      name=@name, provider=@provider, description=@description, context_length=@contextLength,99      pricing_prompt=@pPrompt, pricing_completion=@pCompletion, pricing_image=@pImage, pricing_request=@pRequest,100      cap_text=@cText, cap_vision=@cVision, cap_reasoning=@cReasoning, cap_tools=@cTools, cap_structured=@cStructured,101      architecture=@architecture, tokenizer=@tokenizer, raw_json=@raw, available=1, or_created_at=@orCreatedAt, updated_at=@now102  `);103104  const existing = new Set(105    (db.prepare("SELECT openrouter_model_id FROM models").all() as { openrouter_model_id: string }[]).map(106      (r) => r.openrouter_model_id107    )108  );109  const seen = new Set<string>();110  let added = 0;111112  const tx = db.transaction(() => {113    for (const m of models) {114      seen.add(m.id);115      if (!existing.has(m.id)) added++;116      upsert.run({117        id: m.id,118        name: m.name,119        provider: m.provider ?? null,120        description: m.description ?? null,121        contextLength: m.contextLength ?? null,122        pPrompt: m.pricing?.prompt ?? null,123        pCompletion: m.pricing?.completion ?? null,124        pImage: m.pricing?.image ?? null,125        pRequest: m.pricing?.request ?? null,126        cText: m.capabilities.text ? 1 : 0,127        cVision: m.capabilities.vision ? 1 : 0,128        cReasoning: m.capabilities.reasoning ? 1 : 0,129        cTools: m.capabilities.tools ? 1 : 0,130        cStructured: m.capabilities.structuredOutput ? 1 : 0,131        architecture: m.metadata.architecture ?? null,132        tokenizer: m.metadata.tokenizer ?? null,133        raw: JSON.stringify(m.metadata.raw ?? null),134        orCreatedAt: m.metadata.createdAt ?? null,135        now,136      });137    }138    // Models gone from the catalog stay in the DB (history integrity) but are marked unavailable.139    const markUnavailable = db.prepare(140      "UPDATE models SET available = 0, updated_at = ? WHERE openrouter_model_id = ?"141    );142    for (const id of existing) {143      if (!seen.has(id)) markUnavailable.run(now, id);144    }145    db.prepare("INSERT INTO settings (key, value) VALUES ('catalog_synced_at', ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value").run(String(now));146  });147  tx();148149  return { total: models.length, added, removed: existing.size - [...existing].filter((id) => seen.has(id)).length };150}151152export function catalogSyncedAt(): number | null {153  const row = getDb().prepare("SELECT value FROM settings WHERE key = 'catalog_synced_at'").get() as154    | { value: string }155    | undefined;156  return row ? Number(row.value) : null;157}158159/** Sync if the cache is empty or stale. Safe to call on any request path. */160export async function ensureCatalogFresh(): Promise<void> {161  const syncedAt = catalogSyncedAt();162  if (syncedAt && Date.now() - syncedAt < SYNC_INTERVAL_MS) return;163  try {164    await syncCatalog();165  } catch (e) {166    if (!syncedAt) throw e; // no cache at all — surface the failure167    // stale cache is still usable; sync will retry on a later request168    console.error("catalog refresh failed:", e);169  }170}171172export function listCatalog(): CatalogModel[] {173  const rows = getDb()174    .prepare(175      `SELECT m.*, p.favorite, p.pinned, p.last_used_at, p.use_count176       FROM models m LEFT JOIN model_prefs p ON p.model_id = m.openrouter_model_id177       ORDER BY m.name COLLATE NOCASE`178    )179    .all() as ModelRow[];180  return rows.map(rowToModel);181}182183export function getModel(id: string): CatalogModel | null {184  const row = getDb()185    .prepare(186      `SELECT m.*, p.favorite, p.pinned, p.last_used_at, p.use_count187       FROM models m LEFT JOIN model_prefs p ON p.model_id = m.openrouter_model_id188       WHERE m.openrouter_model_id = ?`189    )190    .get(id) as ModelRow | undefined;191  return row ? rowToModel(row) : null;192}193194export function touchModelUsage(id: string): void {195  getDb()196    .prepare(197      `INSERT INTO model_prefs (model_id, last_used_at, use_count) VALUES (?, ?, 1)198       ON CONFLICT(model_id) DO UPDATE SET last_used_at = excluded.last_used_at, use_count = use_count + 1`199    )200    .run(id, Date.now());201}202203export function setModelPref(id: string, pref: { favorite?: boolean; pinned?: boolean }): void {204  const db = getDb();205  db.prepare("INSERT OR IGNORE INTO model_prefs (model_id) VALUES (?)").run(id);206  if (pref.favorite !== undefined) {207    db.prepare("UPDATE model_prefs SET favorite = ? WHERE model_id = ?").run(pref.favorite ? 1 : 0, id);208  }209  if (pref.pinned !== undefined) {210    db.prepare("UPDATE model_prefs SET pinned = ? WHERE model_id = ?").run(pref.pinned ? 1 : 0, id);211  }212}213