spb/tools-spboucher-ai
Public
1import "server-only";2import { sqlPublic } from "@/db";3import { embedQuery } from "@/lib/ai/provider";4import { cache } from "react";56export const PER_PAGE = 24;7export const SORTS = ["relevance", "name", "updated", "quality"] as const;8export type Sort = (typeof SORTS)[number];9export type Filters = { q?: string; category?: string[]; tag?: string[]; use?: string[]; os?: string[]; exec?: string[]; iface?: string[]; source?: string[]; pricing?: string[]; status?: string[]; depth?: string[]; selfhost?: boolean; sort?: Sort; page?: number };1011export type ToolCard = {12 id: string; slug: string; name: string; summary: string; productType: string; productStatus: string; sourceModel: string; licenseSpdx: string | null; pricingModel: string[];13 operatingSystems: string[]; executionModes: string[]; interfaces: string[]; depth: string; qualityScore: number; featured: boolean; updatedAt: Date; checkedAt: Date | null; linkState: string;14 vendorName: string | null; primaryCategory: { slug: string; name: string } | null; score?: number;15};16const CARD_SELECT = `t.id, t.slug, t.name, t.summary, t.product_type AS "productType", t.product_status AS "productStatus", t.source_model AS "sourceModel", t.license_spdx AS "licenseSpdx", t.pricing_model AS "pricingModel",17 t.operating_systems AS "operatingSystems", t.execution_modes AS "executionModes", t.interfaces, t.depth, t.quality_score AS "qualityScore", t.featured, t.updated_at AS "updatedAt", t.checked_at AS "checkedAt", t.link_state AS "linkState",18 v.name AS "vendorName", (SELECT jsonb_build_object('slug', c.slug, 'name', 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 "primaryCategory"`;1920/** Build the WHERE clause fragments for the structured filters (shared by search, facets and category pages). */21function whereFragments(f: Filters) {22 const parts: ReturnType<typeof sqlPublic>[] = [];23 const s = sqlPublic;24 if (f.category?.length) parts.push(s`t.id IN (SELECT tc.tool_id FROM tool_categories tc JOIN categories c ON c.id = tc.category_id LEFT JOIN categories p ON p.id = c.parent_id WHERE c.slug = ANY(${f.category}) OR p.slug = ANY(${f.category}))`);25 if (f.tag?.length) parts.push(s`t.id IN (SELECT tt.tool_id FROM tool_tags tt JOIN tags g ON g.id = tt.tag_id WHERE g.kind='tag' AND g.slug = ANY(${f.tag}))`);26 if (f.use?.length) parts.push(s`t.id IN (SELECT tt.tool_id FROM tool_tags tt JOIN tags g ON g.id = tt.tag_id WHERE g.kind='use_case' AND g.slug = ANY(${f.use}))`);27 if (f.os?.length) parts.push(s`t.operating_systems && ${f.os}::text[]`);28 if (f.exec?.length) parts.push(s`t.execution_modes && ${f.exec}::text[]`);29 if (f.iface?.length) parts.push(s`t.interfaces && ${f.iface}::text[]`);30 if (f.source?.length) parts.push(s`t.source_model = ANY(${f.source})`);31 if (f.pricing?.length) parts.push(s`t.pricing_model && ${f.pricing}::text[]`);32 if (f.status?.length) parts.push(s`t.product_status = ANY(${f.status})`);33 if (f.depth?.length) parts.push(s`t.depth = ANY(${f.depth})`);34 if (f.selfhost) parts.push(s`t.self_hostable = true`);35 return parts;36}37function andAll(parts: ReturnType<typeof sqlPublic>[]) {38 const s = sqlPublic; if (!parts.length) return s``;39 return parts.reduce((acc, p, i) => (i === 0 ? s`AND ${p}` : s`${acc} AND ${p}`), s``);40}41const cleanQuery = (q?: string) => (q ?? "").replace(/[\u0000-\u001f<>]/g, " ").replace(/\s+/g, " ").trim().slice(0, 200);42/** tsquery with prefix matching on the last token (so "postg" matches PostgreSQL). */43function prefixQuery(q: string) { const toks = q.toLowerCase().split(/[^\p{L}\p{N}+#.]+/u).filter(Boolean).slice(0, 8); if (!toks.length) return null; return toks.map((t, i) => (i === toks.length - 1 ? `${t.replace(/[':&|!()]/g, "")}:*` : t.replace(/[':&|!()]/g, ""))).filter((t) => t !== ":*").join(" & "); }4445export async function searchTools(f: Filters): Promise<{ items: ToolCard[]; total: number; page: number; pages: number; mode: "lexical" | "hybrid" | "browse"; tookMs: number }> {46 const t0 = Date.now(); const s = sqlPublic;47 const q = cleanQuery(f.q); const page = Math.max(1, f.page ?? 1); const off = (page - 1) * PER_PAGE;48 const where = andAll(whereFragments(f));49 const sort = f.sort ?? (q ? "relevance" : "name");50 const orderBy = sort === "name" ? s`t.name ASC` : sort === "updated" ? s`t.updated_at DESC, t.name` : sort === "quality" ? s`t.quality_score DESC, t.name` : s`t.featured DESC, t.quality_score DESC, t.name`;51 if (!q) {52 const items = await s<(ToolCard & { total: number })[]>`SELECT ${s.unsafe(CARD_SELECT)}, count(*) OVER()::int AS total FROM public_tools t LEFT JOIN vendors v ON v.id = t.vendor_id WHERE true ${where} ORDER BY ${orderBy} LIMIT ${PER_PAGE} OFFSET ${off}`;53 const total = items[0]?.total ?? 0;54 return { items: items.map(({ total: _t, ...r }) => r), total, page, pages: Math.max(1, Math.ceil(total / PER_PAGE)), mode: "browse", tookMs: Date.now() - t0 };55 }56 const pq = prefixQuery(q);57 const vec = await embedQuery(q);58 // lexical: full-text (weighted, prefix) ∪ trigram on name/aliases (typo tolerance)59 const lexical = s`60 SELECT t.id,61 (ts_rank_cd(t.search_vector, to_tsquery('english', f_unaccent(${pq})), 32) * 1.062 + greatest(similarity(f_unaccent(lower(t.name)), f_unaccent(lower(${q}))), (SELECT coalesce(max(similarity(f_unaccent(lower(a)), f_unaccent(lower(${q})))),0) FROM unnest(t.aliases) a)) * 0.963 + CASE WHEN f_unaccent(lower(t.name)) LIKE f_unaccent(lower(${q})) || '%' THEN 0.6 ELSE 0 END64 + CASE WHEN f_unaccent(lower(t.name)) = f_unaccent(lower(${q})) THEN 1.0 ELSE 0 END65 + t.quality_score / 1000.0 + CASE WHEN t.featured THEN 0.03 ELSE 0 END) AS lex66 FROM public_tools t67 WHERE (t.search_vector @@ to_tsquery('english', f_unaccent(${pq}))68 OR similarity(f_unaccent(lower(t.name)), f_unaccent(lower(${q}))) > 0.369 OR EXISTS (SELECT 1 FROM unnest(t.aliases) a WHERE similarity(f_unaccent(lower(a)), f_unaccent(lower(${q}))) > 0.3)70 OR f_unaccent(lower(t.name)) LIKE '%' || f_unaccent(lower(${q})) || '%')71 ${where}`;72 const rows = vec73 ? await s<(ToolCard & { total: number; score: number })[]>`74 WITH lex AS (${lexical}), lexr AS (SELECT id, lex, row_number() OVER (ORDER BY lex DESC) AS r FROM lex),75 vec AS (SELECT t.id, row_number() OVER (ORDER BY t.embedding <=> ${JSON.stringify(vec)}::vector) AS r FROM public_tools t WHERE t.embedding IS NOT NULL ${where} ORDER BY t.embedding <=> ${JSON.stringify(vec)}::vector LIMIT 60),76 fused AS (SELECT coalesce(l.id, v.id) AS id, coalesce(1.0/(60+l.r),0)*1.2 + coalesce(1.0/(60+v.r),0) + coalesce(l.lex,0)/50 AS score FROM lexr l FULL OUTER JOIN vec v ON v.id = l.id)77 SELECT ${s.unsafe(CARD_SELECT)}, f.score, count(*) OVER()::int AS total FROM fused f JOIN public_tools t ON t.id = f.id LEFT JOIN vendors v ON v.id = t.vendor_id78 ORDER BY ${sort === "relevance" ? s`f.score DESC, t.name` : orderBy} LIMIT ${PER_PAGE} OFFSET ${off}`79 : await s<(ToolCard & { total: number; score: number })[]>`80 WITH lex AS (${lexical})81 SELECT ${s.unsafe(CARD_SELECT)}, l.lex AS score, count(*) OVER()::int AS total FROM lex l JOIN public_tools t ON t.id = l.id LEFT JOIN vendors v ON v.id = t.vendor_id82 ORDER BY ${sort === "relevance" ? s`l.lex DESC, t.name` : orderBy} LIMIT ${PER_PAGE} OFFSET ${off}`;83 const total = rows[0]?.total ?? 0;84 const tookMs = Date.now() - t0;85 s`INSERT INTO search_events (query, result_count, mode, duration_ms) VALUES (${q.slice(0, 120)}, ${total}, ${vec ? "hybrid" : "lexical"}, ${tookMs})`.catch(() => {});86 return { items: rows.map(({ total: _t, ...r }) => r), total, page, pages: Math.max(1, Math.ceil(total / PER_PAGE)), mode: vec ? "hybrid" : "lexical", tookMs };87}8889export type Facet = { value: string; label: string; count: number };90export async function facets(f: Filters): Promise<Record<string, Facet[]>> {91 const s = sqlPublic; const where = andAll(whereFragments({ ...f, q: undefined }));92 const q = cleanQuery(f.q); const pq = prefixQuery(q);93 const match = q ? s`AND (t.search_vector @@ to_tsquery('english', f_unaccent(${pq})) OR similarity(f_unaccent(lower(t.name)), f_unaccent(lower(${q}))) > 0.3 OR f_unaccent(lower(t.name)) LIKE '%' || f_unaccent(lower(${q})) || '%')` : s``;94 const base = s`SELECT t.* FROM public_tools t WHERE true ${where} ${match}`;95 const [cats, os, exec, iface, source, pricing, status, depth] = await Promise.all([96 s<Facet[]>`WITH b AS (${base}) SELECT c.slug AS value, c.name AS label, count(DISTINCT b.id)::int AS count FROM b JOIN tool_categories tc ON tc.tool_id=b.id JOIN categories c ON c.id=tc.category_id GROUP BY c.slug, c.name ORDER BY count DESC, label LIMIT 40`,97 s<Facet[]>`WITH b AS (${base}) SELECT x AS value, x AS label, count(*)::int FROM b, unnest(b.operating_systems) x GROUP BY x ORDER BY count DESC`,98 s<Facet[]>`WITH b AS (${base}) SELECT x AS value, x AS label, count(*)::int FROM b, unnest(b.execution_modes) x GROUP BY x ORDER BY count DESC`,99 s<Facet[]>`WITH b AS (${base}) SELECT x AS value, x AS label, count(*)::int FROM b, unnest(b.interfaces) x GROUP BY x ORDER BY count DESC`,100 s<Facet[]>`WITH b AS (${base}) SELECT source_model AS value, source_model AS label, count(*)::int FROM b GROUP BY 1 ORDER BY count DESC`,101 s<Facet[]>`WITH b AS (${base}) SELECT x AS value, x AS label, count(*)::int FROM b, unnest(b.pricing_model) x GROUP BY x ORDER BY count DESC`,102 s<Facet[]>`WITH b AS (${base}) SELECT product_status AS value, product_status AS label, count(*)::int FROM b GROUP BY 1 ORDER BY count DESC`,103 s<Facet[]>`WITH b AS (${base}) SELECT depth AS value, depth AS label, count(*)::int FROM b GROUP BY 1 ORDER BY count DESC`,104 ]);105 return { category: cats, os, exec, iface, source, pricing, status, depth };106}107108export async function autocomplete(qRaw: string, limit = 8) {109 const q = cleanQuery(qRaw); if (q.length < 1) return [];110 const s = sqlPublic; const pq = prefixQuery(q);111 return s<{ slug: string; name: string; summary: string; kind: string }[]>`112 (SELECT t.slug, t.name, t.summary, 'tool' AS kind FROM public_tools t113 WHERE f_unaccent(lower(t.name)) LIKE f_unaccent(lower(${q})) || '%' OR t.search_vector @@ to_tsquery('english', f_unaccent(${pq})) OR similarity(f_unaccent(lower(t.name)), f_unaccent(lower(${q}))) > 0.35114 OR EXISTS (SELECT 1 FROM unnest(t.aliases) a WHERE f_unaccent(lower(a)) LIKE f_unaccent(lower(${q})) || '%')115 ORDER BY (f_unaccent(lower(t.name)) LIKE f_unaccent(lower(${q})) || '%') DESC, similarity(f_unaccent(lower(t.name)), f_unaccent(lower(${q}))) DESC, t.quality_score DESC LIMIT ${limit})116 UNION ALL117 (SELECT c.slug, c.name, c.description AS summary, 'category' AS kind FROM categories c WHERE f_unaccent(lower(c.name)) LIKE '%' || f_unaccent(lower(${q})) || '%' AND c.parent_id IS NOT NULL118 AND EXISTS (SELECT 1 FROM tool_categories tc JOIN public_tools t ON t.id = tc.tool_id WHERE tc.category_id = c.id) ORDER BY c.name LIMIT 3)`;119}120121export type ToolDetail = {122 id: string; slug: string; name: string; aliases: string[]; summary: string; description: string; productType: string; productStatus: string;123 homepageUrl: string; docsUrl: string | null; repoUrl: string | null; pricingUrl: string | null; licenseUrl: string | null; privacyUrl: string | null; securityUrl: string | null; logoUrl: string | null; logoAttribution: string | null;124 capabilities: string[]; workflows: string[]; advantages: string[]; limitations: string[]; prerequisites: string[]; audiences: string[]; comparisonNotes: string | null;125 operatingSystems: string[]; executionModes: string[]; interfaces: string[]; deploymentModels: string[]; integrations: string[]; dataFormats: string[]; languages: string[];126 sourceModel: string; licenseSpdx: string | null; pricingModel: string[]; pricingCheckedAt: string | null; privacyPosture: string | null; dataResidency: string | null; selfHostable: boolean | null;127 confidence: string; uncertaintyNote: string | null; depth: string; qualityScore: number; linkState: string; featured: boolean; reviewedAt: string | null; checkedAt: Date | null; publishedAt: Date | null; updatedAt: Date;128 vendor: { slug: string; name: string; websiteUrl: string | null } | null;129 categories: { slug: string; name: string; isPrimary: boolean; label: string | null; parent: { slug: string; name: string } | null }[];130 tags: { slug: string; name: string }[]; useCases: { slug: string; name: string }[];131 alternatives: { slug: string; name: string; summary: string; sourceModel: string; pricingModel: string[] }[];132 sources: { url: string; title: string | null; sourceType: string; supports: string[]; checkedAt: string; note: string | null; httpStatus: number | null }[];133 linkChecks: { field: string; url: string; ok: boolean; statusCode: number | null; redirected: boolean; checkedAt: Date }[];134 collections: { slug: string; title: string }[];135};136export const getTool = cache(async (slug: string): Promise<ToolDetail | null> => {137 const s = sqlPublic;138 const [t] = await s<any[]>`SELECT t.*, to_jsonb(v) - 'id' - 'created_at' - 'updated_at' - 'description' AS vendor FROM public_tools t LEFT JOIN vendors v ON v.id = t.vendor_id WHERE t.slug = ${slug}`;139 if (!t) return null;140 const [cats, tags, alts, srcs, links, cols] = await Promise.all([141 s<any[]>`SELECT c.slug, c.name, tc.is_primary AS "isPrimary", tc.label, CASE WHEN p.id IS NULL THEN NULL ELSE jsonb_build_object('slug', p.slug, 'name', p.name) END AS parent FROM tool_categories tc JOIN categories c ON c.id=tc.category_id LEFT JOIN categories p ON p.id=c.parent_id WHERE tc.tool_id=${t.id} ORDER BY tc.is_primary DESC, c.name`,142 s<any[]>`SELECT g.slug, g.name, g.kind FROM tool_tags tt JOIN tags g ON g.id=tt.tag_id WHERE tt.tool_id=${t.id} ORDER BY g.name`,143 s<any[]>`SELECT a.slug, a.name, a.summary, a.source_model AS "sourceModel", a.pricing_model AS "pricingModel" FROM tool_alternatives ta JOIN public_tools a ON a.id=ta.alternative_id WHERE ta.tool_id=${t.id} UNION SELECT a.slug, a.name, a.summary, a.source_model, a.pricing_model FROM tool_alternatives ta JOIN public_tools a ON a.id=ta.tool_id WHERE ta.alternative_id=${t.id} ORDER BY 2`,144 s<any[]>`SELECT so.url, so.title, so.source_type AS "sourceType", ts.supports, ts.checked_at AS "checkedAt", ts.note, so.http_status AS "httpStatus" FROM tool_sources ts JOIN sources so ON so.id=ts.source_id WHERE ts.tool_id=${t.id} ORDER BY so.source_type, so.url`,145 s<any[]>`SELECT DISTINCT ON (field) field, url, ok, status_code AS "statusCode", redirected, checked_at AS "checkedAt" FROM public_link_checks WHERE tool_id=${t.id} ORDER BY field, checked_at DESC`,146 s<any[]>`SELECT c.slug, c.title FROM collection_items ci JOIN public_collections c ON c.id=ci.collection_id WHERE ci.tool_id=${t.id} ORDER BY c.title`,147 ]);148 return {149 id: t.id, slug: t.slug, name: t.name, aliases: t.aliases, summary: t.summary, description: t.description, productType: t.product_type, productStatus: t.product_status,150 homepageUrl: t.homepage_url, docsUrl: t.docs_url, repoUrl: t.repo_url, pricingUrl: t.pricing_url, licenseUrl: t.license_url, privacyUrl: t.privacy_url, securityUrl: t.security_url, logoUrl: t.logo_url, logoAttribution: t.logo_attribution,151 capabilities: t.capabilities, workflows: t.workflows, advantages: t.advantages, limitations: t.limitations, prerequisites: t.prerequisites, audiences: t.audiences, comparisonNotes: t.comparison_notes,152 operatingSystems: t.operating_systems, executionModes: t.execution_modes, interfaces: t.interfaces, deploymentModels: t.deployment_models, integrations: t.integrations, dataFormats: t.data_formats, languages: t.languages,153 sourceModel: t.source_model, licenseSpdx: t.license_spdx, pricingModel: t.pricing_model, pricingCheckedAt: t.pricing_checked_at, privacyPosture: t.privacy_posture, dataResidency: t.data_residency, selfHostable: t.self_hostable,154 confidence: t.confidence, uncertaintyNote: t.uncertainty_note, depth: t.depth, qualityScore: t.quality_score, linkState: t.link_state, featured: t.featured, reviewedAt: t.reviewed_at, checkedAt: t.checked_at, publishedAt: t.published_at, updatedAt: t.updated_at,155 vendor: t.vendor ? { slug: t.vendor.slug, name: t.vendor.name, websiteUrl: t.vendor.website_url ?? null } : null,156 categories: cats, tags: tags.filter((g) => g.kind === "tag"), useCases: tags.filter((g) => g.kind === "use_case"), alternatives: alts, sources: srcs, linkChecks: links, collections: cols,157 };158});159160export const getToolsBySlugs = cache(async (slugs: string[]) => {161 if (!slugs.length) return [] as ToolDetail[];162 const out = await Promise.all(slugs.slice(0, 5).map((s) => getTool(s)));163 return out.filter((x): x is ToolDetail => !!x);164});165166export type CategoryNode = { id: string; slug: string; name: string; description: string; count: number; children: CategoryNode[] };167export const categoryTree = cache(async (): Promise<CategoryNode[]> => {168 const s = sqlPublic;169 const rows = await s<{ id: string; slug: string; name: string; description: string; parentId: string | null; sortOrder: number; count: number }[]>`170 SELECT c.id, c.slug, c.name, c.description, c.parent_id AS "parentId", c.sort_order AS "sortOrder",171 (SELECT count(DISTINCT tc.tool_id)::int FROM tool_categories tc JOIN public_tools t ON t.id = tc.tool_id JOIN categories cc ON cc.id = tc.category_id WHERE cc.id = c.id OR cc.parent_id = c.id) AS count172 FROM categories c ORDER BY c.sort_order, c.name`;173 const byId = new Map(rows.map((r) => [r.id, { ...r, children: [] as CategoryNode[] }]));174 const roots: CategoryNode[] = [];175 for (const r of byId.values()) { if (r.parentId && byId.has(r.parentId)) byId.get(r.parentId)!.children.push(r); else roots.push(r); }176 return roots;177});178export const getCategory = cache(async (slug: string) => {179 const s = sqlPublic;180 const [c] = await s<{ id: string; slug: string; name: string; description: string; parentId: string | null }[]>`SELECT id, slug, name, description, parent_id AS "parentId" FROM categories WHERE slug=${slug}`;181 if (!c) return null;182 const parent = c.parentId ? (await s<{ slug: string; name: string }[]>`SELECT slug, name FROM categories WHERE id=${c.parentId}`)[0] : null;183 const children = await s<{ slug: string; name: string; description: string; count: number }[]>`SELECT c.slug, c.name, c.description, (SELECT count(*)::int FROM tool_categories tc JOIN public_tools t ON t.id=tc.tool_id WHERE tc.category_id=c.id) AS count FROM categories c WHERE c.parent_id=${c.id} ORDER BY c.sort_order, c.name`;184 return { ...c, parent, children };185});186187export const listCollections = cache(async () => {188 const s = sqlPublic;189 return s<{ slug: string; title: string; description: string; count: number; updatedAt: Date; preview: { slug: string; name: string }[] }[]>`190 SELECT c.slug, c.title, c.description, c.updated_at AS "updatedAt", (SELECT count(*)::int FROM collection_items ci JOIN public_tools t ON t.id=ci.tool_id WHERE ci.collection_id=c.id) AS count,191 (SELECT coalesce(jsonb_agg(jsonb_build_object('slug', t.slug, 'name', t.name) ORDER BY ci.position), '[]') FROM (SELECT ci.* FROM collection_items ci JOIN public_tools t ON t.id=ci.tool_id WHERE ci.collection_id=c.id ORDER BY ci.position LIMIT 6) ci JOIN public_tools t ON t.id = ci.tool_id) AS preview192 FROM public_collections c ORDER BY c.sort_order, c.title`;193});194export const getCollection = cache(async (slug: string) => {195 const s = sqlPublic;196 const [c] = await s<{ id: string; slug: string; title: string; description: string; updatedAt: Date }[]>`SELECT id, slug, title, description, updated_at AS "updatedAt" FROM public_collections WHERE slug=${slug}`;197 if (!c) return null;198 const items = await s<(ToolCard & { blurb: string | null })[]>`SELECT ${s.unsafe(CARD_SELECT)}, ci.blurb FROM collection_items ci JOIN public_tools t ON t.id=ci.tool_id LEFT JOIN vendors v ON v.id=t.vendor_id WHERE ci.collection_id=${c.id} ORDER BY ci.position`;199 return { ...c, items };200});201202export const homeData = cache(async () => {203 const s = sqlPublic;204 const [stats] = await s<{ tools: number; deep: number; categories: number; vendors: number; collections: number; lastVerified: Date | null }[]>`205 SELECT (SELECT count(*)::int FROM public_tools) AS tools, (SELECT count(*)::int FROM public_tools WHERE depth='deep') AS deep,206 (SELECT count(DISTINCT tc.category_id)::int FROM tool_categories tc JOIN public_tools t ON t.id=tc.tool_id) AS categories,207 (SELECT count(DISTINCT vendor_id)::int FROM public_tools) AS vendors, (SELECT count(*)::int FROM public_collections) AS collections,208 (SELECT max(checked_at) FROM public_tools) AS "lastVerified"`;209 const featured = await s<ToolCard[]>`SELECT ${s.unsafe(CARD_SELECT)} FROM public_tools t LEFT JOIN vendors v ON v.id=t.vendor_id WHERE t.featured ORDER BY t.quality_score DESC, t.name LIMIT 8`;210 const recent = await s<ToolCard[]>`SELECT ${s.unsafe(CARD_SELECT)} FROM public_tools t LEFT JOIN vendors v ON v.id=t.vendor_id WHERE t.checked_at IS NOT NULL ORDER BY t.checked_at DESC, t.name LIMIT 8`;211 const tree = await categoryTree();212 const collections = await listCollections();213 return { stats, featured, recent, tree, collections };214});215216export const allPublicSlugs = cache(async () => sqlPublic<{ slug: string; updatedAt: Date }[]>`SELECT slug, updated_at AS "updatedAt" FROM public_tools ORDER BY slug`);217export const relatedTools = cache(async (toolId: string, limit = 6) => {218 const s = sqlPublic;219 return s<ToolCard[]>`SELECT ${s.unsafe(CARD_SELECT)} FROM public_tools t LEFT JOIN vendors v ON v.id=t.vendor_id220 WHERE t.id <> ${toolId} AND t.id IN (SELECT tc2.tool_id FROM tool_categories tc1 JOIN tool_categories tc2 ON tc2.category_id = tc1.category_id WHERE tc1.tool_id = ${toolId} AND tc1.is_primary)221 AND t.id NOT IN (SELECT alternative_id FROM tool_alternatives WHERE tool_id=${toolId})222 ORDER BY t.quality_score DESC, t.name LIMIT ${limit}`;223});224export const useCaseList = cache(async () => sqlPublic<{ slug: string; name: string; count: number }[]>`SELECT g.slug, g.name, count(*)::int FROM tags g JOIN tool_tags tt ON tt.tag_id=g.id JOIN public_tools t ON t.id=tt.tool_id WHERE g.kind='use_case' GROUP BY g.slug, g.name ORDER BY count DESC, g.name`);225export const tagList = cache(async () => sqlPublic<{ slug: string; name: string; count: number }[]>`SELECT g.slug, g.name, count(*)::int FROM tags g JOIN tool_tags tt ON tt.tag_id=g.id JOIN public_tools t ON t.id=tt.tool_id WHERE g.kind='tag' GROUP BY g.slug, g.name ORDER BY count DESC, g.name LIMIT 60`);226