spb/chat-spboucher Public

1// Author: Simon-Pierre Boucher2// Contact: contact@spboucher.ai3// Project: chat.spboucher.ai45import crypto from "node:crypto";6import { getDb } from "@/lib/db/database";78export interface ConversationRow {9  id: string;10  title: string;11  pinned: number;12  current_leaf_id: string | null;13  created_at: number;14  updated_at: number;15}1617export interface MessageRow {18  id: string;19  conversation_id: string;20  parent_id: string | null;21  role: "user" | "assistant" | "system";22  content: string;23  reasoning: string | null;24  model_id: string | null;25  model_name: string | null;26  provider: string | null;27  generation_id: string | null;28  status: "pending" | "streaming" | "completed" | "cancelled" | "failed";29  error_message: string | null;30  created_at: number;31}3233export function listConversations(): ConversationRow[] {34  return getDb()35    .prepare("SELECT * FROM conversations ORDER BY pinned DESC, updated_at DESC")36    .all() as ConversationRow[];37}3839export function getConversation(id: string): ConversationRow | null {40  return (getDb().prepare("SELECT * FROM conversations WHERE id = ?").get(id) as ConversationRow) ?? null;41}4243export function createConversation(title?: string): ConversationRow {44  const db = getDb();45  const id = crypto.randomUUID();46  const now = Date.now();47  db.prepare(48    "INSERT INTO conversations (id, title, created_at, updated_at) VALUES (?, ?, ?, ?)"49  ).run(id, title?.slice(0, 80) || "New conversation", now, now);50  return getConversation(id)!;51}5253export function updateConversation(54  id: string,55  patch: { title?: string; pinned?: boolean; currentLeafId?: string | null }56): void {57  const db = getDb();58  if (patch.title !== undefined) {59    db.prepare("UPDATE conversations SET title = ?, updated_at = ? WHERE id = ?").run(60      patch.title.slice(0, 120),61      Date.now(),62      id63    );64  }65  if (patch.pinned !== undefined) {66    db.prepare("UPDATE conversations SET pinned = ? WHERE id = ?").run(patch.pinned ? 1 : 0, id);67  }68  if (patch.currentLeafId !== undefined) {69    db.prepare("UPDATE conversations SET current_leaf_id = ? WHERE id = ?").run(patch.currentLeafId, id);70  }71}7273export function deleteConversation(id: string): void {74  getDb().prepare("DELETE FROM conversations WHERE id = ?").run(id);75}7677export function touchConversation(id: string): void {78  getDb().prepare("UPDATE conversations SET updated_at = ? WHERE id = ?").run(Date.now(), id);79}8081export function listMessages(conversationId: string): MessageRow[] {82  return getDb()83    .prepare("SELECT * FROM messages WHERE conversation_id = ? ORDER BY created_at, id")84    .all(conversationId) as MessageRow[];85}8687export function getMessage(id: string): MessageRow | null {88  return (getDb().prepare("SELECT * FROM messages WHERE id = ?").get(id) as MessageRow) ?? null;89}9091export function insertMessage(m: {92  conversationId: string;93  parentId: string | null;94  role: "user" | "assistant" | "system";95  content?: string;96  modelId?: string;97  modelName?: string;98  provider?: string;99  generationId?: string;100  status?: MessageRow["status"];101}): MessageRow {102  const db = getDb();103  const id = crypto.randomUUID();104  db.prepare(105    `INSERT INTO messages (id, conversation_id, parent_id, role, content, model_id, model_name, provider, generation_id, status, created_at)106     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`107  ).run(108    id,109    m.conversationId,110    m.parentId,111    m.role,112    m.content ?? "",113    m.modelId ?? null,114    m.modelName ?? null,115    m.provider ?? null,116    m.generationId ?? null,117    m.status ?? "completed",118    Date.now()119  );120  return getMessage(id)!;121}122123/** Walk parent pointers from a leaf to the root: the active thread, oldest first. */124export function threadToLeaf(conversationId: string, leafId: string | null): MessageRow[] {125  if (!leafId) return [];126  const byId = new Map(listMessages(conversationId).map((m) => [m.id, m]));127  const thread: MessageRow[] = [];128  let cursor = byId.get(leafId);129  while (cursor) {130    thread.push(cursor);131    cursor = cursor.parent_id ? byId.get(cursor.parent_id) : undefined;132  }133  return thread.reverse();134}135