spb/wp-spboucher-ai

1import { sql } from "@/db";2/**3 * Deterministic relations from curated metadata (topics, methods, datasets, co-authors other than the series' primary author)4 * plus inferred links from textual similarity (lexical hashed vectors; OpenAI embeddings when available). Curated relations are never touched.5 */6export async function rebuildRelations() {7  const papers = await sql`SELECT id, identifier FROM papers WHERE status = 'published'`;8  const ids = papers.map((p) => p.id as string);9  const rows: { from: string; to: string; kind: string; weight: number; en: string; fr: string; inferred: boolean }[] = [];10  const shared = async (table: string, col: string, nameTable: string, kind: string, labelEn: string, labelFr: string, weightEach: number, extra = "") => {11    const r = await sql.unsafe(`SELECT a.paper_id AS f, b.paper_id AS t, array_agg(n.name_en ORDER BY n.name_en) AS en, array_agg(n.name_fr ORDER BY n.name_en) AS fr12      FROM ${table} a JOIN ${table} b ON a.${col} = b.${col} AND a.paper_id <> b.paper_id JOIN ${nameTable} n ON n.id = a.${col}13      JOIN papers pa ON pa.id = a.paper_id AND pa.status = 'published' JOIN papers pb ON pb.id = b.paper_id AND pb.status = 'published' ${extra} GROUP BY a.paper_id, b.paper_id`);14    for (const x of r) rows.push({ from: x.f, to: x.t, kind, weight: Math.min(1, weightEach * (x.en as string[]).length), en: `${labelEn}: ${(x.en as string[]).join(", ")}`, fr: `${labelFr} : ${(x.fr as string[]).join(", ")}`, inferred: false });15  };16  await shared("paper_topics", "topic_id", "topics", "topic", "Shared topics", "Thèmes partagés", 0.35, "AND n.kind = 'topic'");17  await shared("paper_methods", "method_id", "methods", "method", "Shared methods", "Méthodes partagées", 0.4);18  await shared("paper_datasets", "dataset_id", "datasets", "dataset", "Shared datasets", "Données partagées", 0.5);19  // Co-authors: the observatory's primary author is on (nearly) every paper, so only non-primary shared authors create a link.20  const au = await sql`SELECT a.paper_id AS f, b.paper_id AS t, array_agg(n.display_name ORDER BY n.display_name) AS names FROM paper_authors a JOIN paper_authors b ON a.author_id = b.author_id AND a.paper_id <> b.paper_id JOIN authors n ON n.id = a.author_id AND NOT n.is_primary21    JOIN papers pa ON pa.id = a.paper_id AND pa.status = 'published' JOIN papers pb ON pb.id = b.paper_id AND pb.status = 'published' GROUP BY a.paper_id, b.paper_id`;22  for (const x of au) rows.push({ from: x.f, to: x.t, kind: "author", weight: 0.6, en: `Shared co-authors: ${(x.names as string[]).join(", ")}`, fr: `Co-auteurs partagés : ${(x.names as string[]).join(", ")}`, inferred: false });23  // Lexical similarity (always available) — top 4 neighbours above threshold.24  const lex = await sql`SELECT a.id AS f, b.id AS t, 1 - (a.lexvec <=> b.lexvec) AS sim FROM papers a JOIN papers b ON a.id <> b.id WHERE a.status = 'published' AND b.status = 'published' AND a.lexvec IS NOT NULL AND b.lexvec IS NOT NULL`;25  const topk = (list: { f: string; t: string; sim: number }[], k: number, thr: number) => {26    const by = new Map<string, { f: string; t: string; sim: number }[]>();27    for (const x of list) { if (x.sim < thr) continue; const arr = by.get(x.f) ?? []; arr.push(x); by.set(x.f, arr); }28    const out: { f: string; t: string; sim: number }[] = []; for (const arr of by.values()) out.push(...arr.sort((a, b) => b.sim - a.sim).slice(0, k)); return out;29  };30  for (const x of topk(lex.map((r) => ({ f: r.f, t: r.t, sim: Number(r.sim) })), 4, 0.22)) rows.push({ from: x.f, to: x.t, kind: "lexical", weight: Number(x.sim.toFixed(3)), en: `Textual similarity ${(x.sim * 100).toFixed(0)}% (inferred from vocabulary of title, abstract and manuscript)`, fr: `Similarité textuelle ${(x.sim * 100).toFixed(0)} % (inférée du vocabulaire du titre, du résumé et du manuscrit)`, inferred: true });31  const sem = await sql`SELECT a.id AS f, b.id AS t, 1 - (a.embedding <=> b.embedding) AS sim FROM papers a JOIN papers b ON a.id <> b.id WHERE a.status = 'published' AND b.status = 'published' AND a.embedding IS NOT NULL AND b.embedding IS NOT NULL`;32  for (const x of topk(sem.map((r) => ({ f: r.f, t: r.t, sim: Number(r.sim) })), 4, 0.45)) rows.push({ from: x.f, to: x.t, kind: "semantic", weight: Number(x.sim.toFixed(3)), en: `Semantic similarity ${(x.sim * 100).toFixed(0)}% (inferred from embeddings of title and abstract)`, fr: `Similarité sémantique ${(x.sim * 100).toFixed(0)} % (inférée des plongements du titre et du résumé)`, inferred: true });33  await sql.begin(async (tx) => {34    await tx`DELETE FROM paper_relations WHERE kind <> 'curated'`;35    for (let i = 0; i < rows.length; i += 500) {36      const batch = rows.slice(i, i + 500).filter((r) => ids.includes(r.from) && ids.includes(r.to));37      if (batch.length) await tx`INSERT INTO paper_relations ${tx(batch.map((r) => ({ from_paper_id: r.from, to_paper_id: r.to, kind: r.kind, weight: r.weight, explanation: r.en, explanation_fr: r.fr, inferred: r.inferred })))} ON CONFLICT (from_paper_id, to_paper_id, kind) DO UPDATE SET weight = EXCLUDED.weight, explanation = EXCLUDED.explanation, explanation_fr = EXCLUDED.explanation_fr`;38    }39  });40  return { relations: rows.length, papers: ids.length };41}42