spb/tools-spboucher-ai

1import { z } from "zod";2import { eq, sql as dsql } from "drizzle-orm";3import { db, schema, sql } from "@/db";4import { toolRecordSchema, type ToolRecord } from "@/lib/content/schema";5import { toToolRow, contentHash } from "@/lib/content/import";6import { normalizeUrl } from "@/lib/url";7import { audit } from "@/lib/audit";8import { enqueue } from "@/lib/jobs/queue";9import { humanize } from "@/lib/text";10import type { SessionUser } from "@/lib/auth/session";1112export type AdminListFilters = { q?: string; status?: string; visibility?: string; linkState?: string; depth?: string; category?: string; page?: number; sort?: "updated" | "name" | "quality" | "checked" };13export async function listTools(f: AdminListFilters) {14  const page = Math.max(1, f.page ?? 1); const per = 50; const q = (f.q ?? "").trim();15  const where = [dsql`true`];16  if (q) where.push(dsql`(t.name ILIKE ${"%" + q + "%"} OR t.slug ILIKE ${"%" + q + "%"} OR t.homepage_url ILIKE ${"%" + q + "%"})`);17  if (f.status) where.push(dsql`t.editorial_status = ${f.status}`);18  if (f.visibility) where.push(dsql`t.visibility = ${f.visibility}`);19  if (f.linkState) where.push(dsql`t.link_state = ${f.linkState}`);20  if (f.depth) where.push(dsql`t.depth = ${f.depth}`);21  if (f.category) where.push(dsql`t.id IN (SELECT tool_id FROM tool_categories tc JOIN categories c ON c.id=tc.category_id WHERE c.slug=${f.category})`);22  const order = f.sort === "name" ? dsql`t.name` : f.sort === "quality" ? dsql`t.quality_score ASC, t.name` : f.sort === "checked" ? dsql`t.checked_at ASC NULLS FIRST` : dsql`t.updated_at DESC`;23  const rows = await db.execute<any>(dsql`SELECT t.id, t.slug, t.name, t.editorial_status AS "editorialStatus", t.visibility, t.depth, t.quality_score AS "qualityScore", t.link_state AS "linkState", t.checked_at AS "checkedAt", t.updated_at AS "updatedAt", t.product_status AS "productStatus", t.confidence, t.author, t.featured,24      (SELECT c.name FROM tool_categories tc JOIN categories c ON c.id=tc.category_id WHERE tc.tool_id=t.id AND tc.is_primary LIMIT 1) AS category, (SELECT use_state FROM owner_notes o WHERE o.tool_id=t.id) AS "useState",25      count(*) OVER()::int AS total FROM tools t WHERE ${dsql.join(where, dsql` AND `)} ORDER BY ${order} LIMIT ${per} OFFSET ${(page - 1) * per}`);26  const total = rows[0]?.total ?? 0; return { items: rows.map(({ total: _t, ...r }: any) => r), total, page, pages: Math.max(1, Math.ceil(total / per)) };27}2829/** Full editable record (content-schema shape) reconstructed from relational rows. */30export async function getToolRecord(id: string): Promise<(ToolRecord & { id: string; editorial_status: string; quality_score: number; link_state: string; checked_at: Date | null; published_at: Date | null; updated_at: Date; duplicate_of: string | null }) | null> {31  const [t] = await sql<any[]>`SELECT t.*, v.name AS vendor_name, v.slug AS vendor_slug, v.website_url AS vendor_website FROM tools t LEFT JOIN vendors v ON v.id=t.vendor_id WHERE t.id::text = ${id} OR t.slug = ${id}`;32  if (!t) return null;33  const [cats, tags, alts, srcs] = await Promise.all([34    sql<any[]>`SELECT c.slug, tc.is_primary, tc.label FROM tool_categories tc JOIN categories c ON c.id=tc.category_id WHERE tc.tool_id=${t.id} ORDER BY tc.is_primary DESC, c.name`,35    sql<any[]>`SELECT g.slug, g.kind FROM tool_tags tt JOIN tags g ON g.id=tt.tag_id WHERE tt.tool_id=${t.id}`,36    sql<any[]>`SELECT a.slug FROM tool_alternatives ta JOIN tools a ON a.id=ta.alternative_id WHERE ta.tool_id=${t.id} ORDER BY a.name`,37    sql<any[]>`SELECT so.url, so.title, so.source_type, ts.supports, ts.checked_at, ts.note FROM tool_sources ts JOIN sources so ON so.id=ts.source_id WHERE ts.tool_id=${t.id}`,38  ]);39  const u = (x: any) => x ?? undefined;40  const d = (x: any) => (x == null ? undefined : x instanceof Date ? x.toISOString().slice(0, 10) : String(x).slice(0, 10));41  return {42    id: t.id, slug: t.slug, name: t.name, aliases: t.aliases, vendor: { name: t.vendor_name ?? "Unknown", slug: u(t.vendor_slug), website: u(t.vendor_website) },43    homepage_url: t.homepage_url, docs_url: u(t.docs_url), repo_url: u(t.repo_url), pricing_url: u(t.pricing_url), license_url: u(t.license_url), privacy_url: u(t.privacy_url), security_url: u(t.security_url), logo_url: u(t.logo_url), logo_attribution: u(t.logo_attribution),44    product_type: t.product_type, product_status: t.product_status, summary: t.summary, description: t.description, capabilities: t.capabilities, workflows: t.workflows, advantages: t.advantages, limitations: t.limitations, prerequisites: t.prerequisites, audiences: t.audiences,45    alternatives: alts.map((a) => a.slug), comparison_notes: u(t.comparison_notes), categories: cats.map((c) => ({ slug: c.slug, primary: c.is_primary, label: u(c.label) })), tags: tags.filter((g) => g.kind === "tag").map((g) => g.slug), use_cases: tags.filter((g) => g.kind === "use_case").map((g) => g.slug),46    operating_systems: t.operating_systems, execution_modes: t.execution_modes, interfaces: t.interfaces, deployment_models: t.deployment_models, integrations: t.integrations, data_formats: t.data_formats, languages: t.languages,47    source_model: t.source_model, license_spdx: u(t.license_spdx), pricing_model: t.pricing_model, pricing_checked_at: d(t.pricing_checked_at), privacy_posture: u(t.privacy_posture), data_residency: u(t.data_residency), self_hostable: t.self_hostable,48    sources: srcs.map((s) => ({ url: s.url, type: s.source_type, title: u(s.title), supports: s.supports, checked_at: d(s.checked_at)!, note: u(s.note) })),49    confidence: t.confidence, uncertainty_note: u(t.uncertainty_note), discovery_method: t.discovery_method, depth: t.depth, editorial_status: t.editorial_status, visibility: t.visibility, featured: t.featured, author: t.author, reviewed_at: d(t.reviewed_at), reviewed_by: u(t.reviewed_by),50    quality_score: t.quality_score, link_state: t.link_state, checked_at: t.checked_at, published_at: t.published_at, updated_at: t.updated_at, duplicate_of: t.duplicate_of,51  };52}5354/** Validate + persist a full record edit from the admin editor (same path as import → same invariants), with revision + audit. */55export async function saveToolRecord(input: unknown, actor: SessionUser, ip?: string | null, existingId?: string) {56  const r = toolRecordSchema.parse(input);57  const hash = contentHash(r);58  const vslug = r.vendor.slug ?? r.vendor.name.toLowerCase().replace(/[^a-z0-9]+/g, "-").replace(/^-|-$/g, "");59  const [v] = await db.insert(schema.vendors).values({ slug: vslug, name: r.vendor.name, websiteUrl: r.vendor.website ?? null }).onConflictDoUpdate({ target: schema.vendors.slug, set: { name: r.vendor.name, websiteUrl: r.vendor.website ?? null, updatedAt: new Date() } }).returning({ id: schema.vendors.id });60  const row = toToolRow(r, v.id, hash);61  let id = existingId;62  if (id) {63    const [prev] = await db.select({ editorialStatus: schema.tools.editorialStatus, slug: schema.tools.slug }).from(schema.tools).where(eq(schema.tools.id, id));64    if (!prev) throw new Error("tool not found");65    if (prev.slug !== r.slug) { const [clash] = await db.select({ id: schema.tools.id }).from(schema.tools).where(eq(schema.tools.slug, r.slug)); if (clash) throw new Error(`slug ${r.slug} already used`); }66    await db.update(schema.tools).set({ ...row, editorialStatus: r.editorial_status, reviewedAt: r.reviewed_at ?? dsql`current_date`, reviewedBy: actor.email }).where(eq(schema.tools.id, id));67    await db.insert(schema.toolRevisions).values({ toolId: id, actor: actor.email, reason: "admin:edit", snapshot: r });68    await audit({ id: actor.id, name: actor.email }, "tool.update", { type: "tool", id }, { slug: r.slug, status: r.editorial_status }, ip);69  } else {70    const [ins] = await db.insert(schema.tools).values({ ...row, editorialStatus: r.editorial_status, reviewedBy: actor.email }).returning({ id: schema.tools.id });71    id = ins.id;72    await db.insert(schema.toolRevisions).values({ toolId: id, actor: actor.email, reason: "admin:create", snapshot: r });73    await audit({ id: actor.id, name: actor.email }, "tool.create", { type: "tool", id }, { slug: r.slug }, ip);74  }75  const catIds = new Map((await db.select({ id: schema.categories.id, slug: schema.categories.slug }).from(schema.categories)).map((c) => [c.slug, c.id]));76  await db.delete(schema.toolCategories).where(eq(schema.toolCategories.toolId, id));77  for (const c of r.categories) { const cid = catIds.get(c.slug); if (!cid) throw new Error(`unknown category ${c.slug}`); await db.insert(schema.toolCategories).values({ toolId: id, categoryId: cid, isPrimary: c.primary, label: c.label ?? null }).onConflictDoNothing(); }78  await db.delete(schema.toolTags).where(eq(schema.toolTags.toolId, id));79  for (const [kind, list] of [["tag", r.tags], ["use_case", r.use_cases]] as const) for (const t of list) { const [g] = await db.insert(schema.tags).values({ slug: t, kind, name: humanize(t) }).onConflictDoUpdate({ target: [schema.tags.slug, schema.tags.kind], set: { name: humanize(t) } }).returning({ id: schema.tags.id }); await db.insert(schema.toolTags).values({ toolId: id, tagId: g.id }).onConflictDoNothing(); }80  await db.delete(schema.toolSources).where(eq(schema.toolSources.toolId, id));81  for (const s of r.sources) { const [so] = await db.insert(schema.sources).values({ url: normalizeUrl(s.url), sourceType: s.type, title: s.title ?? null }).onConflictDoUpdate({ target: schema.sources.url, set: { sourceType: s.type, updatedAt: new Date() } }).returning({ id: schema.sources.id }); await db.insert(schema.toolSources).values({ toolId: id, sourceId: so.id, supports: s.supports, checkedAt: s.checked_at, note: s.note ?? null }).onConflictDoNothing(); }82  const allIds = new Map((await db.select({ id: schema.tools.id, slug: schema.tools.slug }).from(schema.tools)).map((t) => [t.slug, t.id]));83  await db.delete(schema.toolAlternatives).where(eq(schema.toolAlternatives.toolId, id));84  for (const a of r.alternatives) { const aid = allIds.get(a); if (aid && aid !== id) await db.insert(schema.toolAlternatives).values({ toolId: id, alternativeId: aid }).onConflictDoNothing(); }85  if (r.editorial_status === "published") await db.update(schema.tools).set({ publishedAt: dsql`coalesce(published_at, now())` }).where(eq(schema.tools.id, id));86  await enqueue("link_check", { toolId: id }, { dedupeKey: `link_check:${id}`, priority: 4 }).catch(() => {});87  await enqueue("embed_tool", { toolId: id }, { dedupeKey: `embed:${id}`, priority: 8 }).catch(() => {});88  return id;89}9091export const actionSchema = z.object({ action: z.enum(["publish", "unpublish", "deprecate", "recheck", "rescan", "embed", "merge", "feature", "unfeature", "set_visibility"]), into: z.string().optional(), visibility: z.enum(["public", "private"]).optional() });92export async function toolAction(id: string, a: z.infer<typeof actionSchema>, actor: SessionUser, ip?: string | null) {93  const [t] = await db.select({ id: schema.tools.id, slug: schema.tools.slug, status: schema.tools.editorialStatus, visibility: schema.tools.visibility, qualityScore: schema.tools.qualityScore, linkState: schema.tools.linkState }).from(schema.tools).where(eq(schema.tools.id, id));94  if (!t) throw new Error("tool not found");95  const log = (action: string, details: Record<string, unknown> = {}) => audit({ id: actor.id, name: actor.email }, action, { type: "tool", id }, { slug: t.slug, ...details }, ip);96  switch (a.action) {97    case "publish": {98      // Policy gate: published records must have quality ≥ 40 and no broken homepage.99      if (t.qualityScore < 40) throw new Error(`quality score ${t.qualityScore} below the publishing threshold (40)`);100      if (t.linkState === "broken") throw new Error("links are broken — fix or re-check before publishing");101      await db.update(schema.tools).set({ editorialStatus: "published", publishedAt: dsql`coalesce(published_at, now())`, reviewedAt: dsql`current_date`, reviewedBy: actor.email, updatedAt: new Date() }).where(eq(schema.tools.id, id)); await log("tool.publish"); break; }102    case "unpublish": await db.update(schema.tools).set({ editorialStatus: "reviewed", updatedAt: new Date() }).where(eq(schema.tools.id, id)); await log("tool.unpublish"); break;103    case "deprecate": await db.update(schema.tools).set({ editorialStatus: "deprecated", updatedAt: new Date() }).where(eq(schema.tools.id, id)); await log("tool.deprecate"); break;104    case "feature": await db.update(schema.tools).set({ featured: true }).where(eq(schema.tools.id, id)); await log("tool.feature"); break;105    case "unfeature": await db.update(schema.tools).set({ featured: false }).where(eq(schema.tools.id, id)); await log("tool.unfeature"); break;106    case "set_visibility": await db.update(schema.tools).set({ visibility: a.visibility ?? "private", updatedAt: new Date() }).where(eq(schema.tools.id, id)); await log("tool.visibility", { visibility: a.visibility }); break;107    case "recheck": await enqueue("link_check", { toolId: id }, { dedupeKey: `link_check:${id}`, priority: 2 }); await log("tool.recheck_queued"); break;108    case "rescan": await enqueue("source_rescan", { toolId: id }, { dedupeKey: `rescan:${id}`, priority: 2 }); await log("tool.rescan_queued"); break;109    case "embed": await enqueue("embed_tool", { toolId: id }, { dedupeKey: `embed:${id}`, priority: 2 }); await log("tool.embed_queued"); break;110    case "merge": {111      if (!a.into || a.into === id) throw new Error("target required");112      const [target] = await db.select({ id: schema.tools.id, slug: schema.tools.slug, aliases: schema.tools.aliases, name: schema.tools.name }).from(schema.tools).where(eq(schema.tools.id, a.into)); if (!target) throw new Error("target not found");113      await db.transaction(async (tx) => {114        await tx.update(schema.tools).set({ duplicateOf: target.id, editorialStatus: "deprecated", visibility: "private", updatedAt: new Date() }).where(eq(schema.tools.id, id));115        const [src] = await tx.select({ name: schema.tools.name, aliases: schema.tools.aliases }).from(schema.tools).where(eq(schema.tools.id, id));116        await tx.update(schema.tools).set({ aliases: Array.from(new Set([...target.aliases, src.name, ...src.aliases].filter((x) => x !== target.name))), updatedAt: new Date() }).where(eq(schema.tools.id, target.id));117        await tx.execute(dsql`INSERT INTO tool_alternatives (tool_id, alternative_id) SELECT ${target.id}, alternative_id FROM tool_alternatives WHERE tool_id=${id} AND alternative_id<>${target.id} ON CONFLICT DO NOTHING`);118        await tx.execute(dsql`UPDATE collection_items SET tool_id=${target.id} WHERE tool_id=${id} AND NOT EXISTS (SELECT 1 FROM collection_items ci WHERE ci.collection_id=collection_items.collection_id AND ci.tool_id=${target.id})`);119        await tx.insert(schema.toolRevisions).values({ toolId: id, actor: actor.email, reason: `merged into ${target.slug}`, snapshot: { mergedInto: target.id } });120      });121      await log("tool.merge", { into: target.slug }); break; }122  }123}124125export async function duplicateCandidates() {126  return sql<{ aId: string; aSlug: string; aName: string; bId: string; bSlug: string; bName: string; reason: string; score: number }[]>`127    SELECT a.id AS "aId", a.slug AS "aSlug", a.name AS "aName", b.id AS "bId", b.slug AS "bSlug", b.name AS "bName",128      CASE WHEN regexp_replace(lower(a.homepage_url), '^https?://(www\.)?', '') = regexp_replace(lower(b.homepage_url), '^https?://(www\.)?', '') THEN 'same homepage'129           WHEN lower(a.name) = ANY(SELECT lower(x) FROM unnest(b.aliases) x) OR lower(b.name) = ANY(SELECT lower(x) FROM unnest(a.aliases) x) THEN 'name is an alias of the other' ELSE 'similar name' END AS reason,130      similarity(lower(a.name), lower(b.name)) AS score131    FROM tools a JOIN tools b ON a.id < b.id AND a.duplicate_of IS NULL AND b.duplicate_of IS NULL132    WHERE similarity(lower(a.name), lower(b.name)) > 0.6133       OR regexp_replace(lower(a.homepage_url), '^https?://(www\.)?', '') = regexp_replace(lower(b.homepage_url), '^https?://(www\.)?', '')134       OR lower(a.name) = ANY(SELECT lower(x) FROM unnest(b.aliases) x) OR lower(b.name) = ANY(SELECT lower(x) FROM unnest(a.aliases) x)135    ORDER BY score DESC LIMIT 100`;136}137export async function adminStats() {138  const [s] = await sql<any[]>`SELECT139    (SELECT count(*)::int FROM tools) AS total, (SELECT count(*)::int FROM tools WHERE editorial_status='published' AND visibility='public') AS published,140    (SELECT count(*)::int FROM tools WHERE editorial_status='reviewed') AS reviewed, (SELECT count(*)::int FROM tools WHERE editorial_status='draft') AS draft,141    (SELECT count(*)::int FROM tools WHERE visibility='private') AS private, (SELECT count(*)::int FROM tools WHERE depth='deep' AND editorial_status='published') AS deep,142    (SELECT count(*)::int FROM tools WHERE link_state='broken') AS broken, (SELECT count(*)::int FROM tools WHERE link_state='unchecked') AS unchecked,143    (SELECT count(*)::int FROM jobs WHERE status='queued') AS queued, (SELECT count(*)::int FROM jobs WHERE status='running') AS running, (SELECT count(*)::int FROM jobs WHERE status='dead') AS dead,144    (SELECT count(*)::int FROM error_reports WHERE status='open') AS reports, (SELECT count(*)::int FROM settings WHERE key LIKE 'review:%' OR key LIKE 'stale:%') AS flags,145    (SELECT max(created_at) FROM audit_log) AS "lastAudit", (SELECT value FROM settings WHERE key = ${"ai_usage:" + new Date().toISOString().slice(0, 7)}) AS "aiUsage",146    (SELECT count(*)::int FROM owner_notes WHERE use_state='active') AS active, (SELECT count(*)::int FROM owner_notes WHERE use_state='testing') AS testing, (SELECT count(*)::int FROM owner_notes WHERE use_state='candidate') AS candidate`;147  const byCat = await sql<{ name: string; n: number; deep: number }[]>`SELECT p.name, count(DISTINCT t.id)::int AS n, count(DISTINCT t.id) FILTER (WHERE t.depth='deep')::int AS deep FROM categories p JOIN categories c ON c.parent_id=p.id JOIN tool_categories tc ON tc.category_id=c.id JOIN tools t ON t.id=tc.tool_id AND t.editorial_status='published' AND t.visibility='public' GROUP BY p.name, p.sort_order ORDER BY p.sort_order`;148  const byConf = await sql<{ confidence: string; n: number }[]>`SELECT confidence, count(*)::int AS n FROM tools WHERE editorial_status='published' GROUP BY 1 ORDER BY 1`;149  return { ...s, byCat, byConf };150}151