spb/tools-spboucher-ai

1/** Deterministic content → database import. Idempotent: records are upserted by slug; relations are replaced; a revision is written only when the content hash changes. */2import { readFileSync, readdirSync, existsSync } from "node:fs";3import { join } from "node:path";4import { createHash } from "node:crypto";5import { eq, sql as dsql, inArray } from "drizzle-orm";6import { db, schema } from "@/db";7import { toolRecordSchema, collectionSchema, ownerRelevanceSchema, type ToolRecord } from "./schema";8import { normalizeUrl } from "@/lib/url";9import { qualityScore } from "@/lib/quality";10import { audit } from "@/lib/audit";11import { humanize } from "@/lib/text";1213export type ImportOptions = { root: string; publish?: boolean; actor?: string; only?: string[]; dryRun?: boolean; /** rewrite rows even when the content hash is unchanged (no revision written) */ force?: boolean };14export type ImportReport = { categories: number; vendors: number; tools: { created: string[]; updated: string[]; unchanged: string[]; published: string[]; skipped: { slug: string; reason: string }[] }; collections: number; ownerNotes: number; projects: number };1516const readJson = (p: string) => JSON.parse(readFileSync(p, "utf8"));17const listJson = (d: string) => (existsSync(d) ? readdirSync(d).filter((f) => f.endsWith(".json")).sort().map((f) => join(d, f)) : []);18export const contentHash = (r: ToolRecord) => createHash("sha256").update(JSON.stringify(r)).digest("hex");1920export async function importCategories(root: string) {21  const cats = readJson(join(root, "categories.json")) as { slug: string; name: string; description: string; children?: any[] }[];22  let n = 0;23  for (const [i, c] of cats.entries()) {24    const [parent] = await db.insert(schema.categories).values({ slug: c.slug, name: c.name, description: c.description, sortOrder: i })25      .onConflictDoUpdate({ target: schema.categories.slug, set: { name: c.name, description: c.description, sortOrder: i, updatedAt: new Date() } }).returning({ id: schema.categories.id });26    n++;27    for (const [j, ch] of (c.children ?? []).entries()) {28      await db.insert(schema.categories).values({ slug: ch.slug, name: ch.name, description: ch.description, parentId: parent.id, sortOrder: j })29        .onConflictDoUpdate({ target: schema.categories.slug, set: { name: ch.name, description: ch.description, parentId: parent.id, sortOrder: j, updatedAt: new Date() } });30      n++;31    }32  }33  return n;34}3536async function upsertVendor(v: ToolRecord["vendor"]) {37  const slug = v.slug ?? v.name.toLowerCase().normalize("NFKD").replace(/[̀-ͯ]/g, "").replace(/[^a-z0-9]+/g, "-").replace(/^-|-$/g, "");38  const [row] = await db.insert(schema.vendors).values({ slug, name: v.name, websiteUrl: v.website ?? null })39    .onConflictDoUpdate({ target: schema.vendors.slug, set: { name: v.name, websiteUrl: v.website ?? null, updatedAt: new Date() } }).returning({ id: schema.vendors.id });40  return row.id;41}42async function upsertTag(slug: string, kind: "tag" | "use_case") {43  const [row] = await db.insert(schema.tags).values({ slug, kind, name: humanize(slug) }).onConflictDoUpdate({ target: [schema.tags.slug, schema.tags.kind], set: { name: humanize(slug) } }).returning({ id: schema.tags.id });44  return row.id;45}46async function upsertSource(s: ToolRecord["sources"][number]) {47  const url = normalizeUrl(s.url);48  const [row] = await db.insert(schema.sources).values({ url, sourceType: s.type, title: s.title ?? null }).onConflictDoUpdate({ target: schema.sources.url, set: { sourceType: s.type, title: s.title ?? undefined, updatedAt: new Date() } }).returning({ id: schema.sources.id });49  return row.id;50}5152export function toToolRow(r: ToolRecord, vendorId: string | null, hash: string) {53  const n = (u?: string) => (u ? normalizeUrl(u) : null);54  return {55    slug: r.slug, name: r.name, aliases: r.aliases, vendorId, homepageUrl: normalizeUrl(r.homepage_url), docsUrl: n(r.docs_url), repoUrl: n(r.repo_url), pricingUrl: n(r.pricing_url), licenseUrl: n(r.license_url), privacyUrl: n(r.privacy_url), securityUrl: n(r.security_url),56    logoUrl: n(r.logo_url), logoAttribution: r.logo_attribution ?? null, productType: r.product_type, productStatus: r.product_status, summary: r.summary, description: r.description,57    capabilities: r.capabilities, workflows: r.workflows, advantages: r.advantages, limitations: r.limitations, prerequisites: r.prerequisites, audiences: r.audiences, comparisonNotes: r.comparison_notes ?? null,58    operatingSystems: r.operating_systems, executionModes: r.execution_modes, interfaces: r.interfaces, deploymentModels: r.deployment_models, integrations: r.integrations, dataFormats: r.data_formats, languages: r.languages,59    sourceModel: r.source_model, licenseSpdx: r.license_spdx ?? null, pricingModel: r.pricing_model, pricingCheckedAt: r.pricing_checked_at ?? null, privacyPosture: r.privacy_posture ?? null, dataResidency: r.data_residency ?? null, selfHostable: r.self_hostable,60    confidence: r.confidence, uncertaintyNote: r.uncertainty_note ?? null, discoveryMethod: r.discovery_method, depth: r.depth, visibility: r.visibility, featured: r.featured, author: r.author,61    reviewedAt: r.reviewed_at ?? null, reviewedBy: r.reviewed_by ?? null, qualityScore: qualityScore(r), contentHash: hash,62    embeddingText: [r.name, r.summary, r.description, r.capabilities.join("; "), r.tags.join(" ")].join("\n"),63    keywords: [...r.tags, ...r.use_cases, ...r.categories.map((c) => c.slug), r.vendor.name, r.product_type, ...r.integrations].map((k) => k.replace(/-/g, " ")).join(" · "),64    updatedAt: new Date(),65  };66}6768export async function importTools(opts: ImportOptions): Promise<ImportReport["tools"]> {69  const actor = opts.actor ?? "content-import";70  const files = [...listJson(join(opts.root, "tools")), ...listJson(join(opts.root, "private"))];71  const report: ImportReport["tools"] = { created: [], updated: [], unchanged: [], published: [], skipped: [] };72  const parsed: ToolRecord[] = [];73  for (const f of files) {74    const p = toolRecordSchema.safeParse(readJson(f));75    if (!p.success) { report.skipped.push({ slug: f, reason: p.error.issues.map((i) => `${i.path.join(".")}: ${i.message}`).join("; ") }); continue; }76    if (opts.only && !opts.only.includes(p.data.slug)) continue;77    parsed.push(p.data);78  }79  if (opts.dryRun) { for (const r of parsed) report.updated.push(r.slug); return report; }80  const catIds = new Map((await db.select({ id: schema.categories.id, slug: schema.categories.slug }).from(schema.categories)).map((c) => [c.slug, c.id]));81  const idBySlug = new Map<string, string>();82  // pass 1: tools + direct relations83  for (const r of parsed) {84    const hash = contentHash(r);85    const vendorId = await upsertVendor(r.vendor);86    const existing = await db.select({ id: schema.tools.id, contentHash: schema.tools.contentHash, editorialStatus: schema.tools.editorialStatus, publishedAt: schema.tools.publishedAt }).from(schema.tools).where(eq(schema.tools.slug, r.slug)).then((x) => x[0]);87    const row = toToolRow(r, vendorId, hash);88    let id: string;89    if (!existing) {90      const [ins] = await db.insert(schema.tools).values({ ...row, editorialStatus: r.editorial_status }).returning({ id: schema.tools.id });91      id = ins.id; report.created.push(r.slug);92      await db.insert(schema.toolRevisions).values({ toolId: id, actor, reason: "import:create", snapshot: r });93      await audit({ name: actor }, "tool.create", { type: "tool", id }, { slug: r.slug });94    } else {95      id = existing.id;96      if (existing.contentHash === hash && !opts.force) report.unchanged.push(r.slug);97      else if (existing.contentHash === hash) { await db.update(schema.tools).set({ ...row, editorialStatus: existing.editorialStatus, updatedAt: dsql`updated_at` }).where(eq(schema.tools.id, id)); report.unchanged.push(r.slug); }98      else {99        // Never demote a published record back to reviewed via import; keep the DB status unless the file explicitly deprecates it.100        const keepStatus = existing.editorialStatus === "published" && r.editorial_status !== "deprecated" ? "published" : r.editorial_status;101        await db.update(schema.tools).set({ ...row, editorialStatus: keepStatus }).where(eq(schema.tools.id, id));102        report.updated.push(r.slug);103        await db.insert(schema.toolRevisions).values({ toolId: id, actor, reason: "import:update", snapshot: r });104        await audit({ name: actor }, "tool.update", { type: "tool", id }, { slug: r.slug });105      }106    }107    idBySlug.set(r.slug, id);108    // categories109    await db.delete(schema.toolCategories).where(eq(schema.toolCategories.toolId, id));110    for (const c of r.categories) { const cid = catIds.get(c.slug); if (!cid) { report.skipped.push({ slug: r.slug, reason: `unknown category ${c.slug}` }); continue; } await db.insert(schema.toolCategories).values({ toolId: id, categoryId: cid, isPrimary: c.primary, label: c.label ?? null }).onConflictDoNothing(); }111    // tags / use cases112    await db.delete(schema.toolTags).where(eq(schema.toolTags.toolId, id));113    for (const t of r.tags) await db.insert(schema.toolTags).values({ toolId: id, tagId: await upsertTag(t, "tag") }).onConflictDoNothing();114    for (const t of r.use_cases) await db.insert(schema.toolTags).values({ toolId: id, tagId: await upsertTag(t, "use_case") }).onConflictDoNothing();115    // sources116    await db.delete(schema.toolSources).where(eq(schema.toolSources.toolId, id));117    for (const s of r.sources) await db.insert(schema.toolSources).values({ toolId: id, sourceId: await upsertSource(s), supports: s.supports, checkedAt: s.checked_at, note: s.note ?? null }).onConflictDoNothing();118  }119  // pass 2: alternatives (need all ids)120  const allIds = new Map((await db.select({ id: schema.tools.id, slug: schema.tools.slug }).from(schema.tools)).map((t) => [t.slug, t.id]));121  for (const r of parsed) {122    const id = idBySlug.get(r.slug)!;123    await db.delete(schema.toolAlternatives).where(eq(schema.toolAlternatives.toolId, id));124    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(); }125  }126  // publish gate: reviewed + public + validated → published127  if (opts.publish) {128    const slugs = parsed.filter((r) => r.visibility === "public" && r.editorial_status === "reviewed").map((r) => r.slug);129    if (slugs.length) {130      const rows = await db.update(schema.tools).set({ editorialStatus: "published", publishedAt: dsql`coalesce(published_at, now())`, reviewedAt: dsql`coalesce(reviewed_at, current_date)`, reviewedBy: dsql`coalesce(reviewed_by, ${actor})` })131        .where(dsql`${schema.tools.slug} IN ${slugs} AND ${schema.tools.editorialStatus} = 'reviewed'`).returning({ slug: schema.tools.slug, id: schema.tools.id });132      for (const t of rows) { report.published.push(t.slug); await audit({ name: actor }, "tool.publish", { type: "tool", id: t.id }, { slug: t.slug, via: "import --publish" }); }133    }134  }135  return report;136}137138export async function importCollections(root: string) {139  const ids = new Map((await db.select({ id: schema.tools.id, slug: schema.tools.slug }).from(schema.tools)).map((t) => [t.slug, t.id]));140  let n = 0;141  for (const [i, f] of listJson(join(root, "collections")).entries()) {142    const c = collectionSchema.parse(readJson(f));143    const [row] = await db.insert(schema.collections).values({ slug: c.slug, title: c.title, description: c.description, published: c.published, sortOrder: i })144      .onConflictDoUpdate({ target: schema.collections.slug, set: { title: c.title, description: c.description, published: c.published, sortOrder: i, updatedAt: new Date() } }).returning({ id: schema.collections.id });145    await db.delete(schema.collectionItems).where(eq(schema.collectionItems.collectionId, row.id));146    for (const [pos, it] of c.items.entries()) { const tid = ids.get(it.tool); if (tid) await db.insert(schema.collectionItems).values({ collectionId: row.id, toolId: tid, position: pos, blurb: it.blurb ?? null }); }147    n++;148  }149  return n;150}151152export async function importOwner(root: string) {153  const ids = new Map((await db.select({ id: schema.tools.id, slug: schema.tools.slug }).from(schema.tools)).map((t) => [t.slug, t.id]));154  let notes = 0, projects = 0;155  const pf = join(root, "owner", "projects.json");156  if (existsSync(pf)) for (const p of readJson(pf) as { slug: string; name: string; description?: string }[]) {157    await db.insert(schema.projects).values({ slug: p.slug, name: p.name, description: p.description ?? null }).onConflictDoUpdate({ target: schema.projects.slug, set: { name: p.name, description: p.description ?? null, updatedAt: new Date() } }); projects++;158  }159  const projIds = new Map((await db.select({ id: schema.projects.id, slug: schema.projects.slug }).from(schema.projects)).map((p) => [p.slug, p.id]));160  for (const f of listJson(join(root, "owner")).filter((f) => !f.endsWith("projects.json"))) {161    for (const raw of readJson(f)) {162      const o = ownerRelevanceSchema.parse(raw); const tid = ids.get(o.tool); if (!tid) continue;163      await db.insert(schema.ownerNotes).values({ toolId: tid, useState: o.use_state, rating: o.rating ?? null, notes: o.notes ?? null, clusterCompat: o.cluster_compat, priority: o.priority, subscriptionStatus: o.subscription_status ?? null, customTags: o.custom_tags, reviewAt: o.review_at ?? null })164        .onConflictDoUpdate({ target: schema.ownerNotes.toolId, set: { useState: o.use_state, rating: o.rating ?? null, notes: o.notes ?? null, clusterCompat: o.cluster_compat, priority: o.priority, subscriptionStatus: o.subscription_status ?? null, customTags: o.custom_tags, reviewAt: o.review_at ?? null, updatedAt: new Date() } });165      await db.delete(schema.projectTools).where(eq(schema.projectTools.toolId, tid));166      for (const ps of o.projects) { const pid = projIds.get(ps); if (pid) await db.insert(schema.projectTools).values({ projectId: pid, toolId: tid }).onConflictDoNothing(); }167      notes++;168    }169  }170  return { notes, projects };171}172173export async function importAll(opts: ImportOptions): Promise<ImportReport> {174  const categories = await importCategories(opts.root);175  const tools = await importTools(opts);176  const collections = opts.dryRun ? 0 : await importCollections(opts.root);177  const owner = opts.dryRun ? { notes: 0, projects: 0 } : await importOwner(opts.root);178  const vendors = (await db.select({ n: dsql<number>`count(*)::int` }).from(schema.vendors))[0].n;179  return { categories, vendors, tools, collections, ownerNotes: owner.notes, projects: owner.projects };180}181