spb/chat-spboucher-ai

1import { guarded, json } from "@/lib/api";2import { sql, vec } from "@/lib/db";3import { openaiEmbed } from "@/lib/ai/openai";45/** Full-text + semantic search across messages; filters: model, project, from, to, role. */6export const GET = guarded(async (req) => {7  const p = req.nextUrl.searchParams;8  const q = (p.get("q") ?? "").trim();9  if (q.length < 2) return json({ results: [] });10  const model = p.get("model"); const project = p.get("project"); const from = p.get("from"); const to = p.get("to"); const semantic = p.get("semantic") !== "0";11  let qv: number[] | null = null;12  if (semantic) { try { qv = (await openaiEmbed([q])).vectors[0]; } catch { qv = null; } }13  const rows = await sql`14    SELECT m.id, m.conversation_id, m.role, m.model_id, m.created_at, c.title, c.project_id,15      ts_headline('simple', left(m.text, 4000), plainto_tsquery('simple', ${q}), 'MaxFragments=2, MaxWords=25, MinWords=8') AS snippet,16      ts_rank(m.tsv, plainto_tsquery('simple', ${q})) AS fts,17      ${qv ? sql`coalesce(1 - (e.embedding <=> ${vec(qv)}::vector), 0)` : sql`0`} AS sem18    FROM messages m JOIN conversations c ON c.id = m.conversation_id19    LEFT JOIN message_embeddings e ON e.message_id = m.id20    WHERE m.active AND m.text <> ''21      AND (m.tsv @@ plainto_tsquery('simple', ${q}) OR m.text ILIKE ${"%" + q + "%"} ${qv ? sql`OR (e.embedding IS NOT NULL AND 1 - (e.embedding <=> ${vec(qv)}::vector) > 0.45)` : sql``})22      AND (${!model} OR m.model_id = ${model ?? ""}) AND (${!project} OR c.project_id = ${project ?? ""})23      AND (${!from} OR m.created_at >= ${from ?? "1970-01-01"}::timestamptz) AND (${!to} OR m.created_at <= ${to ?? "2100-01-01"}::timestamptz)24    ORDER BY (ts_rank(m.tsv, plainto_tsquery('simple', ${q})) * 0.6 + ${qv ? sql`coalesce(1 - (e.embedding <=> ${vec(qv)}::vector), 0)` : sql`0`} * 0.4) DESC, m.created_at DESC25    LIMIT 40`;26  return json({ results: rows });27});28