spb/chat-spboucher-ai

1import "server-only";2import { sql } from "../db";3import { newId } from "../ids";4import type { Part, Role } from "../ai/types";56export interface ConversationRow {7  id: string; project_id: string | null; title: string; model_id: string | null; settings: Record<string, unknown>; pinned: boolean; archived_at: Date | null;8  parent_id: string | null; branch_message_id: string | null; mode: string; message_count: number; total_cost_usd: number; last_message_at: Date | null; created_at: Date; updated_at: Date;9}10export interface MessageRow {11  id: string; conversation_id: string; parent_id: string | null; role: Role; parts: Part[]; text: string; model_id: string | null; provider: string | null;12  settings: Record<string, unknown> | null; routing: Record<string, unknown> | null; usage: Record<string, number> | null; cost_usd: number | null; latency_ms: number | null; ttft_ms: number | null;13  status: string; error: string | null; active: boolean; created_at: Date;14}1516export const partsToText = (parts: Part[]) => parts.filter(p => p.type === "text").map(p => (p as { text: string }).text).join("\n");1718export async function createConversation(opts: { projectId?: string | null; modelId?: string | null; title?: string; settings?: Record<string, unknown>; mode?: string; parentId?: string | null; branchMessageId?: string | null }): Promise<ConversationRow> {19  const id = newId("conv");20  const rows = await sql<ConversationRow[]>`INSERT INTO conversations (id, project_id, title, model_id, settings, mode, parent_id, branch_message_id)21    VALUES (${id}, ${opts.projectId ?? null}, ${opts.title ?? "Nouvelle conversation"}, ${opts.modelId ?? null}, ${sql.json((opts.settings ?? {}) as never)}, ${opts.mode ?? "chat"}, ${opts.parentId ?? null}, ${opts.branchMessageId ?? null}) RETURNING *`;22  return rows[0];23}2425export async function getConversation(id: string): Promise<ConversationRow | null> {26  const rows = await sql<ConversationRow[]>`SELECT * FROM conversations WHERE id = ${id}`;27  return rows[0] ?? null;28}2930export async function listConversations(opts: { projectId?: string | null; archived?: boolean; limit?: number; q?: string } = {}) {31  const rows = await sql<(ConversationRow & { project_name: string | null })[]>`32    SELECT c.*, p.name AS project_name FROM conversations c LEFT JOIN projects p ON p.id = c.project_id33    WHERE (${opts.archived ?? false} AND c.archived_at IS NOT NULL OR NOT ${opts.archived ?? false} AND c.archived_at IS NULL)34      AND (${opts.projectId === undefined} OR c.project_id IS NOT DISTINCT FROM ${opts.projectId ?? null})35      AND (${!opts.q} OR c.tsv @@ plainto_tsquery('simple', ${opts.q ?? ""}) OR c.title ILIKE ${"%" + (opts.q ?? "") + "%"})36    ORDER BY c.pinned DESC, coalesce(c.last_message_at, c.created_at) DESC LIMIT ${opts.limit ?? 200}`;37  return rows;38}3940export async function updateConversation(id: string, patch: Partial<Pick<ConversationRow, "title" | "project_id" | "model_id" | "pinned" | "settings">> & { archived?: boolean }) {41  await sql`UPDATE conversations SET42    title = COALESCE(${patch.title ?? null}, title),43    project_id = CASE WHEN ${patch.project_id !== undefined} THEN ${patch.project_id ?? null} ELSE project_id END,44    model_id = COALESCE(${patch.model_id ?? null}, model_id),45    pinned = COALESCE(${patch.pinned ?? null}, pinned),46    settings = COALESCE(${patch.settings ? sql.json(patch.settings as never) : null}, settings),47    archived_at = CASE WHEN ${patch.archived === undefined} THEN archived_at WHEN ${patch.archived ?? false} THEN now() ELSE NULL END48    WHERE id = ${id}`;49}5051export async function deleteConversation(id: string) { await sql`DELETE FROM conversations WHERE id = ${id}`; }5253/** Active thread (the path currently displayed). */54export async function getActiveMessages(conversationId: string): Promise<MessageRow[]> {55  return sql<MessageRow[]>`SELECT * FROM messages WHERE conversation_id = ${conversationId} AND active = true ORDER BY created_at ASC`;56}57export async function getAllMessages(conversationId: string): Promise<MessageRow[]> {58  return sql<MessageRow[]>`SELECT * FROM messages WHERE conversation_id = ${conversationId} ORDER BY created_at ASC`;59}60export async function getMessage(id: string): Promise<MessageRow | null> {61  const rows = await sql<MessageRow[]>`SELECT * FROM messages WHERE id = ${id}`; return rows[0] ?? null;62}6364export async function insertMessage(m: { conversationId: string; parentId?: string | null; role: Role; parts: Part[]; modelId?: string | null; provider?: string | null; settings?: Record<string, unknown> | null; routing?: Record<string, unknown> | null; status?: string; id?: string }): Promise<MessageRow> {65  const id = m.id ?? newId("msg");66  const rows = await sql<MessageRow[]>`INSERT INTO messages (id, conversation_id, parent_id, role, parts, text, model_id, provider, settings, routing, status)67    VALUES (${id}, ${m.conversationId}, ${m.parentId ?? null}, ${m.role}, ${sql.json(m.parts as never)}, ${partsToText(m.parts)}, ${m.modelId ?? null}, ${m.provider ?? null}, ${m.settings ? sql.json(m.settings as never) : null}, ${m.routing ? sql.json(m.routing as never) : null}, ${m.status ?? "complete"}) RETURNING *`;68  await sql`UPDATE conversations SET message_count = message_count + 1, last_message_at = now() WHERE id = ${m.conversationId}`;69  return rows[0];70}7172export async function updateMessage(id: string, patch: { parts?: Part[]; status?: string; error?: string | null; usage?: Record<string, number> | null; costUsd?: number | null; latencyMs?: number | null; ttftMs?: number | null; modelId?: string | null; routing?: Record<string, unknown> | null }) {73  await sql`UPDATE messages SET74    parts = COALESCE(${patch.parts ? sql.json(patch.parts as never) : null}, parts),75    text = COALESCE(${patch.parts ? partsToText(patch.parts) : null}, text),76    status = COALESCE(${patch.status ?? null}, status),77    error = CASE WHEN ${patch.error !== undefined} THEN ${patch.error ?? null} ELSE error END,78    usage = COALESCE(${patch.usage ? sql.json(patch.usage as never) : null}, usage),79    cost_usd = COALESCE(${patch.costUsd ?? null}, cost_usd),80    latency_ms = COALESCE(${patch.latencyMs ?? null}, latency_ms),81    ttft_ms = COALESCE(${patch.ttftMs ?? null}, ttft_ms),82    model_id = COALESCE(${patch.modelId ?? null}, model_id),83    routing = COALESCE(${patch.routing ? sql.json(patch.routing as never) : null}, routing)84    WHERE id = ${id}`;85  if (patch.costUsd) await sql`UPDATE conversations c SET total_cost_usd = (SELECT coalesce(sum(cost_usd),0) FROM messages WHERE conversation_id = c.id) WHERE c.id = (SELECT conversation_id FROM messages WHERE id = ${id})`;86}8788/** Deactivate a message and every active message after it (used by edit / regenerate). */89export async function deactivateFrom(conversationId: string, messageId: string, inclusive: boolean) {90  await sql`UPDATE messages SET active = false WHERE conversation_id = ${conversationId} AND active = true91    AND created_at ${inclusive ? sql`>=` : sql`>`} (SELECT created_at FROM messages WHERE id = ${messageId})`;92}9394/** Switch the active path to the sibling `targetId` (same parent) and its descendants chain. */95export async function activateBranch(conversationId: string, targetId: string) {96  const target = await getMessage(targetId); if (!target) return;97  await deactivateFrom(conversationId, targetId, true);98  // re-activate target then follow the latest child chain99  let cur: MessageRow | null = target;100  while (cur) {101    await sql`UPDATE messages SET active = true WHERE id = ${cur.id}`;102    const kids: MessageRow[] = await sql<MessageRow[]>`SELECT * FROM messages WHERE parent_id = ${cur.id} ORDER BY created_at DESC LIMIT 1`;103    cur = kids[0] ?? null;104  }105}106107export async function siblings(messageId: string): Promise<{ id: string; active: boolean; created_at: Date }[]> {108  const m = await getMessage(messageId); if (!m) return [];109  return sql`SELECT id, active, created_at FROM messages WHERE conversation_id = ${m.conversation_id} AND parent_id IS NOT DISTINCT FROM ${m.parent_id} AND role = ${m.role} ORDER BY created_at ASC`;110}111112/** Fork a conversation at `messageId` (inclusive) into a new conversation. */113export async function branchConversation(conversationId: string, messageId: string): Promise<ConversationRow> {114  const src = await getConversation(conversationId); if (!src) throw new Error("conversation not found");115  const msgs = await getActiveMessages(conversationId);116  const idx = msgs.findIndex(m => m.id === messageId);117  const keep = idx >= 0 ? msgs.slice(0, idx + 1) : msgs;118  const conv = await createConversation({ projectId: src.project_id, modelId: src.model_id, title: `${src.title} (branche)`, settings: src.settings, parentId: src.id, branchMessageId: messageId });119  let parent: string | null = null;120  for (const m of keep) {121    const row = await insertMessage({ conversationId: conv.id, parentId: parent, role: m.role, parts: m.parts, modelId: m.model_id, provider: m.provider, settings: m.settings, routing: m.routing });122    await sql`UPDATE messages SET usage = ${m.usage ? sql.json(m.usage as never) : null}, cost_usd = ${m.cost_usd}, latency_ms = ${m.latency_ms}, ttft_ms = ${m.ttft_ms} WHERE id = ${row.id}`;123    parent = row.id;124  }125  return conv;126}127