spb/note-spboucher-ai

1/** References service: CRUD, BibTeX import/export, Crossref DOI lookup, PDF attach. */2import { and, asc, desc, eq, inArray, isNull, sql as dsql } from "drizzle-orm";3import { db, schema } from "@/db";4import { HttpError } from "@/lib/auth/guard";5import { parseBibtex, formatBibtex, fieldsOf, makeCiteKey, normDoi, type ParsedRef } from "./bibtex";6const R = schema.researchReferences;7export type ReferenceRow = typeof R.$inferSelect;8export type Verified = "user" | "doi_verified" | "ai_suggested";910export async function listReferences(wsId: string, opts: { pageId?: string | null; ids?: string[] } = {}) {11  return db.select().from(R).where(and(eq(R.workspaceId, wsId), isNull(R.deletedAt), opts.pageId ? eq(R.pageId, opts.pageId) : undefined, opts.ids?.length ? inArray(R.id, opts.ids) : undefined)).orderBy(desc(R.year), asc(R.citeKey));12}13export async function getReference(wsId: string, id: string) {14  const [r] = await db.select().from(R).where(and(eq(R.id, id), eq(R.workspaceId, wsId), isNull(R.deletedAt))); if (!r) throw new HttpError(404, "Reference not found"); return r;15}16/** Make a cite key unique in the workspace (gorton2019 → gorton2019a …). */17async function uniqueKey(wsId: string, base: string, excludeId?: string) {18  const key = base.replace(/[^A-Za-z0-9:_\-.]/g, "") || "ref";19  const rows = await db.select({ citeKey: R.citeKey, id: R.id }).from(R).where(and(eq(R.workspaceId, wsId), dsql`${R.citeKey} like ${key + "%"}`));20  const taken = new Set(rows.filter((r) => r.id !== excludeId).map((r) => r.citeKey));21  if (!taken.has(key)) return key;22  for (let i = 0; i < 26; i++) { const k = key + String.fromCharCode(97 + i); if (!taken.has(k)) return k; }23  return `${key}-${Date.now().toString(36)}`;24}25export interface ReferenceInput { pageId?: string | null; citeKey?: string | null; entryType?: string; title: string; authors?: string[]; year?: number | null; journal?: string | null; doi?: string | null; url?: string | null; fields?: Record<string, unknown>; verified?: Verified; attachmentId?: string | null; bibtex?: string | null }26export async function createReference(wsId: string, input: ReferenceInput) {27  const doi = normDoi(input.doi); const entryType = input.entryType ?? "article"; const authors = input.authors ?? [];28  const base = input.citeKey?.trim() || makeCiteKey(authors, input.year ?? null, input.title);29  const citeKey = await uniqueKey(wsId, base);30  const fields = fieldsOf({ ...input, entryType, authors, doi, fields: input.fields });31  const [r] = await db.insert(R).values({ workspaceId: wsId, pageId: input.pageId ?? null, citeKey, entryType, title: input.title.trim(), authors, year: input.year ?? null, journal: input.journal ?? null, doi, url: input.url ?? (doi ? `https://doi.org/${doi}` : null), fields, bibtex: input.bibtex ?? formatBibtex({ citeKey, entryType, fields }), verified: input.verified ?? "user", attachmentId: input.attachmentId ?? null }).returning();32  return r;33}34export async function updateReference(wsId: string, id: string, patch: Partial<ReferenceInput>) {35  const prev = await getReference(wsId, id);36  const set: Partial<ReferenceRow> = { updatedAt: new Date() };37  if (patch.citeKey !== undefined && patch.citeKey && patch.citeKey !== prev.citeKey) set.citeKey = await uniqueKey(wsId, patch.citeKey, id);38  for (const k of ["pageId", "entryType", "title", "authors", "year", "journal", "url", "verified", "attachmentId"] as const) if (patch[k] !== undefined) (set as any)[k] = patch[k];39  if (patch.doi !== undefined) set.doi = normDoi(patch.doi);40  if (patch.fields !== undefined) set.fields = { ...((prev.fields as any) ?? {}), ...patch.fields };41  const merged = { ...prev, ...set } as ReferenceRow;42  set.fields = fieldsOf({ title: merged.title, authors: merged.authors, year: merged.year, journal: merged.journal, doi: merged.doi, url: merged.url, entryType: merged.entryType, fields: (set.fields ?? merged.fields) as Record<string, unknown> });43  set.bibtex = formatBibtex({ citeKey: set.citeKey ?? merged.citeKey, entryType: merged.entryType, fields: set.fields as Record<string, unknown> });44  const [r] = await db.update(R).set(set).where(eq(R.id, id)).returning(); return r;45}46export async function deleteReference(wsId: string, id: string) { await db.update(R).set({ deletedAt: new Date() }).where(and(eq(R.id, id), eq(R.workspaceId, wsId))); }47export async function attachPdf(wsId: string, id: string, attachmentId: string | null) {48  if (attachmentId) { const [a] = await db.select({ id: schema.attachments.id }).from(schema.attachments).where(and(eq(schema.attachments.id, attachmentId), eq(schema.attachments.workspaceId, wsId), isNull(schema.attachments.deletedAt))); if (!a) throw new HttpError(404, "Attachment not found"); }49  const [r] = await db.update(R).set({ attachmentId, updatedAt: new Date() }).where(and(eq(R.id, id), eq(R.workspaceId, wsId))).returning(); return r;50}5152/** Import BibTeX text: dedupe by DOI then by cite key (existing rows are kept, not overwritten). */53export async function importBibtex(wsId: string, text: string, pageId: string | null) {54  const parsed = parseBibtex(text);55  const existing = await db.select({ id: R.id, citeKey: R.citeKey, doi: R.doi }).from(R).where(and(eq(R.workspaceId, wsId), isNull(R.deletedAt)));56  const byDoi = new Map(existing.filter((e) => e.doi).map((e) => [e.doi!, e])); const byKey = new Map(existing.map((e) => [e.citeKey.toLowerCase(), e]));57  const created: ReferenceRow[] = []; const skipped: { citeKey: string; reason: string }[] = []; const seen = new Set<string>();58  for (const p of parsed) {59    const dup = (p.doi && byDoi.get(p.doi)) || byKey.get(p.citeKey.toLowerCase());60    if (dup) { skipped.push({ citeKey: p.citeKey, reason: p.doi && byDoi.get(p.doi) ? `DOI already stored as ${dup.citeKey}` : "cite key already exists" }); continue; }61    const sig = p.doi ?? p.citeKey.toLowerCase(); if (seen.has(sig)) { skipped.push({ citeKey: p.citeKey, reason: "duplicate inside file" }); continue; } seen.add(sig);62    const r = await createReference(wsId, { pageId, citeKey: p.citeKey, entryType: p.entryType, title: p.title, authors: p.authors, year: p.year, journal: p.journal, doi: p.doi, url: p.url, fields: p.fields, verified: "user", bibtex: p.bibtex });63    created.push(r); if (r.doi) byDoi.set(r.doi, r); byKey.set(r.citeKey.toLowerCase(), r);64  }65  return { created, skipped, parsed: parsed.length };66}67export async function exportBibtex(wsId: string, pageId?: string | null) {68  const rows = await listReferences(wsId, { pageId }); const sorted = [...rows].sort((a, b) => a.citeKey.localeCompare(b.citeKey));69  return sorted.map((r) => r.bibtex ?? formatBibtex({ citeKey: r.citeKey, entryType: r.entryType, fields: fieldsOf(r) })).join("\n\n") + (sorted.length ? "\n" : "");70}7172/** Crossref metadata (polite pool). */73export interface DoiMeta { doi: string; title: string; authors: string[]; year: number | null; journal: string | null; entryType: string; url: string; fields: Record<string, string>; citeKey: string }74const UA = "NoteSPBoucherAI/1.0 (https://note.spboucher.ai; mailto:spbou4@icloud.com)";75const TYPE_MAP: Record<string, string> = { "journal-article": "article", "book": "book", "book-chapter": "incollection", "proceedings-article": "inproceedings", "posted-content": "misc", "report": "techreport", "dissertation": "phdthesis", "monograph": "book" };76export async function lookupDoi(raw: string): Promise<DoiMeta> {77  const doi = normDoi(raw); if (!doi) throw new HttpError(400, "Invalid DOI");78  const ctl = new AbortController(); const t = setTimeout(() => ctl.abort(), 15_000);79  let res: Response;80  try { res = await fetch(`https://api.crossref.org/works/${encodeURIComponent(doi)}`, { headers: { "User-Agent": UA, Accept: "application/json" }, signal: ctl.signal }); }81  catch (e) { throw new HttpError(502, "Crossref unreachable: " + String((e as any)?.message ?? e).slice(0, 120)); } finally { clearTimeout(t); }82  if (res.status === 404) throw new HttpError(404, "DOI not found on Crossref");83  if (!res.ok) throw new HttpError(502, `Crossref error ${res.status}`);84  const m = (await res.json())?.message; if (!m) throw new HttpError(502, "Crossref: empty response");85  const authors: string[] = (m.author ?? []).map((a: any) => a.name ?? [a.given, a.family].filter(Boolean).join(" ")).filter(Boolean);86  const dateParts = m["published-print"]?.["date-parts"]?.[0] ?? m["published-online"]?.["date-parts"]?.[0] ?? m.issued?.["date-parts"]?.[0] ?? m.created?.["date-parts"]?.[0];87  const year = dateParts?.[0] ? Number(dateParts[0]) : null;88  const title = String(m.title?.[0] ?? "").replace(/\s+/g, " ").trim() || "Untitled";89  const journal = m["container-title"]?.[0] ?? m.publisher ?? null;90  const entryType = TYPE_MAP[m.type] ?? "misc";91  const fields: Record<string, string> = {};92  if (m.volume) fields.volume = String(m.volume); if (m.issue) fields.number = String(m.issue); if (m.page) fields.pages = String(m.page).replace(/-/g, "--"); if (m.publisher) fields.publisher = String(m.publisher);93  if (m.abstract) fields.abstract = String(m.abstract).replace(/<[^>]+>/g, " ").replace(/\s+/g, " ").trim().slice(0, 4000);94  if (m.ISSN?.[0]) fields.issn = m.ISSN[0]; if (m.subject?.length) fields.keywords = m.subject.slice(0, 8).join(", ");95  return { doi, title, authors, year, journal, entryType, url: `https://doi.org/${doi}`, fields, citeKey: makeCiteKey(authors, year, title) };96}97export async function addByDoi(wsId: string, raw: string, pageId: string | null) {98  const meta = await lookupDoi(raw);99  const [dup] = await db.select().from(R).where(and(eq(R.workspaceId, wsId), isNull(R.deletedAt), eq(R.doi, meta.doi)));100  if (dup) { if (pageId && !dup.pageId) return updateReference(wsId, dup.id, { pageId }); return dup; }101  return createReference(wsId, { pageId, citeKey: meta.citeKey, entryType: meta.entryType, title: meta.title, authors: meta.authors, year: meta.year, journal: meta.journal, doi: meta.doi, url: meta.url, fields: meta.fields, verified: "doi_verified" });102}103/** Re-verify an existing reference against Crossref (fills missing fields, marks doi_verified). */104export async function verifyReference(wsId: string, id: string) {105  const r = await getReference(wsId, id); if (!r.doi) throw new HttpError(400, "Reference has no DOI");106  const meta = await lookupDoi(r.doi);107  return updateReference(wsId, id, { title: r.title || meta.title, authors: r.authors?.length ? r.authors : meta.authors, year: r.year ?? meta.year, journal: r.journal ?? meta.journal, fields: { ...meta.fields, ...((r.fields as any) ?? {}) }, verified: "doi_verified", url: r.url ?? meta.url });108}109export type { ParsedRef };110