spb/chat-spboucher-ai

1import "server-only";2import fs from "node:fs/promises";3import path from "node:path";4import { createHash } from "node:crypto";5import { sql } from "./db";6import { env } from "./env";7import { newId } from "./ids";89export interface AttachmentRow {10  id: string; conversation_id: string | null; project_id: string | null; message_id: string | null; kind: string; filename: string; mime: string;11  size_bytes: number; path: string; sha256: string | null; extracted_text: string | null; page_count: number | null; meta: Record<string, unknown>; created_at: Date;12}1314const SAFE_MIME = new Set(["image/png", "image/jpeg", "image/webp", "image/gif", "application/pdf", "text/plain", "text/markdown", "text/csv",15  "application/vnd.openxmlformats-officedocument.wordprocessingml.document", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "application/json", "audio/mpeg", "audio/mp4", "audio/webm", "audio/wav", "audio/ogg", "audio/x-m4a"]);1617export function allowedMime(mime: string): boolean { return SAFE_MIME.has(mime) || mime.startsWith("text/"); }1819export async function saveAttachment(opts: { data: Buffer; filename: string; mime: string; conversationId?: string | null; projectId?: string | null; messageId?: string | null; kind?: string; extractedText?: string | null; pageCount?: number | null; meta?: Record<string, unknown> }): Promise<AttachmentRow> {20  const id = newId("att");21  const ext = path.extname(opts.filename).slice(0, 10).replace(/[^.\w]/g, "") || "";22  const day = new Date().toISOString().slice(0, 10);23  const rel = path.join(day, `${id}${ext}`);24  const abs = path.join(env.UPLOAD_DIR, rel);25  await fs.mkdir(path.dirname(abs), { recursive: true });26  await fs.writeFile(abs, opts.data, { mode: 0o600 });27  const sha = createHash("sha256").update(opts.data).digest("hex");28  const rows = await sql<AttachmentRow[]>`INSERT INTO attachments (id, conversation_id, project_id, message_id, kind, filename, mime, size_bytes, path, sha256, extracted_text, page_count, meta)29    VALUES (${id}, ${opts.conversationId ?? null}, ${opts.projectId ?? null}, ${opts.messageId ?? null}, ${opts.kind ?? "upload"}, ${opts.filename.slice(0, 255)}, ${opts.mime}, ${opts.data.length}, ${rel}, ${sha}, ${opts.extractedText ?? null}, ${opts.pageCount ?? null}, ${sql.json((opts.meta ?? {}) as never)}) RETURNING *`;30  return rows[0];31}3233export async function getAttachment(id: string): Promise<AttachmentRow | null> {34  const rows = await sql<AttachmentRow[]>`SELECT * FROM attachments WHERE id = ${id}`;35  return rows[0] ?? null;36}3738export async function readAttachment(id: string): Promise<{ data: Buffer; mime: string; name: string; text: string | null } | null> {39  const a = await getAttachment(id);40  if (!a) return null;41  const data = await fs.readFile(path.join(env.UPLOAD_DIR, a.path));42  return { data, mime: a.mime, name: a.filename, text: a.extracted_text };43}4445export async function deleteAttachment(id: string) {46  const a = await getAttachment(id);47  if (!a) return;48  await fs.rm(path.join(env.UPLOAD_DIR, a.path), { force: true });49  await sql`DELETE FROM attachments WHERE id = ${id}`;50}5152/** Text extraction for documents. Images return null (handled by vision). */53export async function extractText(data: Buffer, mime: string, filename: string): Promise<{ text: string | null; pages?: number }> {54  try {55    if (mime === "application/pdf") {56      const mod = await import("pdf-parse");57      const PDFParse = (mod as unknown as { PDFParse?: new (o: { data: Buffer }) => { getText(): Promise<{ text: string; total?: number }> } }).PDFParse;58      if (PDFParse) { const p = new PDFParse({ data }); const r = await p.getText(); return { text: r.text, pages: r.total }; }59      const legacy = (mod as unknown as { default: (b: Buffer) => Promise<{ text: string; numpages: number }> }).default;60      const r = await legacy(data); return { text: r.text, pages: r.numpages };61    }62    if (mime === "application/vnd.openxmlformats-officedocument.wordprocessingml.document") {63      const mammoth = await import("mammoth");64      const r = await mammoth.extractRawText({ buffer: data }); return { text: r.value };65    }66    if (mime === "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" || /\.xlsx?$/i.test(filename)) {67      const XLSX = await import("xlsx");68      const wb = XLSX.read(data, { type: "buffer" });69      const parts = wb.SheetNames.map(n => `## ${n}\n${XLSX.utils.sheet_to_csv(wb.Sheets[n])}`);70      return { text: parts.join("\n\n"), pages: wb.SheetNames.length };71    }72    if (mime.startsWith("text/") || mime === "application/json" || /\.(md|txt|csv|json|ya?ml|tsx?|jsx?|py|sql|html?)$/i.test(filename)) return { text: data.toString("utf8") };73  } catch (e) { return { text: `[extraction failed: ${(e as Error).message}]` }; }74  return { text: null };75}7677/** Split text into ~1 200-char chunks on paragraph boundaries for RAG. */78export function chunkText(text: string, size = 1200, overlap = 150): string[] {79  const paras = text.split(/\n{2,}/).map(p => p.trim()).filter(Boolean);80  const chunks: string[] = []; let cur = "";81  for (const p of paras) {82    if ((cur + "\n\n" + p).length > size && cur) { chunks.push(cur); cur = cur.slice(-overlap) + "\n\n" + p; }83    else cur = cur ? cur + "\n\n" + p : p;84    while (cur.length > size * 1.5) { chunks.push(cur.slice(0, size)); cur = cur.slice(size - overlap); }85  }86  if (cur) chunks.push(cur);87  return chunks;88}89