spb/note-spboucher-ai

1/** Backlinks explorer: incoming-link counts, unlinked-mention finder (plain SQL strpos over pages.plain_text), link-it / dismiss. */2import { eq, sql as dsql } from "drizzle-orm";3import { db, schema } from "@/db";4import { getPage, updatePage } from "@/lib/pages/service";5import type { PMNode } from "@/lib/editor/text";6import { HttpError } from "@/lib/auth/guard";7import type { BacklinkRow, UnlinkedMention } from "./types";89const rows = async <T = any>(q: ReturnType<typeof dsql>): Promise<T[]> => (await db.execute(q)) as unknown as T[];10const DISMISS_KEY = "backlinks.dismissed";1112/** Pages ranked by incoming links (one query). */13export async function backlinkStats(wsId: string, opts: { limit?: number; q?: string } = {}): Promise<BacklinkRow[]> {14  const q = opts.q?.trim() ? dsql`and p.title ilike ${"%" + opts.q!.trim() + "%"}` : dsql``;15  const r = await rows<any>(dsql`16    select p.id, p.title, p.icon, p.type, p.section, p.updated_at,17      (select count(*)::int from links l join pages f on f.id = l.from_page_id and f.deleted_at is null where l.to_page_id = p.id) as incoming,18      (select count(*)::int from links l join pages t on t.id = l.to_page_id and t.deleted_at is null where l.from_page_id = p.id) as outgoing,19      (select count(*)::int from knowledge_edges k where k.kind = 'ai_suggested' and k.status = 'suggested' and (k.from_id = p.id or k.to_id = p.id) and not exists (select 1 from links l where (l.from_page_id = k.from_id and l.to_page_id = k.to_id) or (l.from_page_id = k.to_id and l.to_page_id = k.from_id))) as suggested20    from pages p where p.workspace_id = ${wsId} and p.deleted_at is null ${q}21    order by incoming desc, outgoing desc, p.updated_at desc limit ${opts.limit ?? 300}`);22  return r.map((x) => ({ id: x.id, title: x.title || "Untitled", icon: x.icon, type: x.type, section: x.section, updatedAt: new Date(x.updated_at).toISOString(), incoming: x.incoming, outgoing: x.outgoing, suggested: x.suggested }));23}2425export async function dismissedKeys(): Promise<string[]> {26  const [s] = await db.select().from(schema.settings).where(eq(schema.settings.key, DISMISS_KEY));27  return Array.isArray(s?.value) ? (s!.value as string[]) : [];28}29export async function dismissMention(fromId: string, toId: string, undo = false) {30  const key = `${fromId}:${toId}`; const cur = new Set(await dismissedKeys()); if (undo) cur.delete(key); else cur.add(key);31  const value = [...cur].slice(-5000);32  await db.insert(schema.settings).values({ key: DISMISS_KEY, value, updatedAt: new Date() }).onConflictDoUpdate({ target: schema.settings.key, set: { value, updatedAt: new Date() } });33  return value;34}3536/** Pages whose text contains another page's exact title (case-insensitive) without linking to it. */37export async function unlinkedMentions(wsId: string, opts: { limit?: number; pageId?: string | null; includeDismissed?: boolean } = {}): Promise<UnlinkedMention[]> {38  const scope = opts.pageId ? dsql`and (t.id = ${opts.pageId} or m.id = ${opts.pageId})` : dsql``;39  const r = await rows<any>(dsql`40    select m.id as from_id, m.title as from_title, m.icon as from_icon, t.id as to_id, t.title as to_title, t.icon as to_icon,41      substr(m.plain_text, greatest(strpos(lower(m.plain_text), lower(t.title)) - 70, 1), 200) as excerpt42    from pages t join pages m on m.workspace_id = t.workspace_id and m.id <> t.id and m.deleted_at is null43      and strpos(lower(m.plain_text), lower(t.title)) > 044    where t.workspace_id = ${wsId} and t.deleted_at is null and length(t.title) >= 4 and t.title <> 'Untitled' ${scope}45      and not exists (select 1 from links l where l.from_page_id = m.id and l.to_page_id = t.id)46    order by t.title, m.updated_at desc limit ${opts.limit ?? 400}`);47  const dismissed = opts.includeDismissed ? new Set<string>() : new Set(await dismissedKeys());48  return r.map((x) => ({ fromId: x.from_id, fromTitle: x.from_title || "Untitled", fromIcon: x.from_icon, toId: x.to_id, toTitle: x.to_title, toIcon: x.to_icon, excerpt: String(x.excerpt ?? "").replace(/\s+/g, " ").trim(), key: `${x.from_id}:${x.to_id}` })).filter((m) => !dismissed.has(m.key));49}5051/** "Link it": append a paragraph with a mention node to the mentioning page (links row is created by updatePage). */52export async function linkMention(wsId: string, fromId: string, toId: string) {53  if (fromId === toId) throw new HttpError(400, "A page cannot link to itself");54  const [from, to] = await Promise.all([getPage(wsId, fromId), getPage(wsId, toId)]);55  const doc = (from.content as PMNode) ?? { type: "doc", content: [] };56  const para: PMNode = { type: "paragraph", content: [{ type: "text", text: "See also " }, { type: "mention", attrs: { id: to.id, label: to.title } }] };57  const content: PMNode = { ...doc, type: "doc", content: [...(doc.content ?? []), para] };58  await updatePage(wsId, fromId, { content }, { versionReason: "backlink" });59  await dismissMention(fromId, toId, true);60  return { fromId, toId };61}62