spb/worthdoing Public

1/**2 * WorthDoing.ai3 * Author: Simon-Pierre Boucher4 * Contact: contact@spboucher.ai5 * File: src/lib/firecrawl/cache.ts6 * Description: Source/content cache — canonical URLs, content hashing, freshness window, dedup.7 */8import { and, desc, eq, gt } from "drizzle-orm";9import { db } from "@/lib/db/client";10import { sources, sourceContents } from "@/lib/db/schema";11import { scrapePage, type ScrapeResult } from "./client";12import { canonicalizeUrl, sha256 } from "./url";1314export { canonicalizeUrl, sha256 };1516const FRESHNESS_MS = 24 * 60 * 60 * 1000; // 24h cache window1718export type CachedPage = {19  sourceId: string;20  contentId: string;21  url: string;22  canonicalUrl: string;23  title: string | null;24  markdown: string;25  contentHash: string;26  wordCount: number;27  fromCache: boolean;28};2930/** Upsert a source row for a URL (no content). Returns the source id. */31export async function upsertSource(32  url: string,33  meta: { title?: string | null; description?: string | null; investigationId?: string },34): Promise<{ id: string; canonicalUrl: string }> {35  const canonicalUrl = canonicalizeUrl(url);36  const domain = new URL(canonicalUrl).hostname;37  const inserted = await db38    .insert(sources)39    .values({40      url,41      canonicalUrl,42      domain,43      title: meta.title ?? null,44      description: meta.description ?? null,45      firstSeenInvestigationId: meta.investigationId ?? null,46    })47    .onConflictDoUpdate({48      target: sources.canonicalUrl,49      set: { title: meta.title ?? undefined, description: meta.description ?? undefined },50    })51    .returning({ id: sources.id });52  return { id: inserted[0].id, canonicalUrl };53}5455/**56 * Scrape a page through the cache: return fresh cached content if available,57 * otherwise scrape via Firecrawl and persist.58 */59export async function scrapeWithCache(url: string, investigationId: string): Promise<CachedPage> {60  const canonicalUrl = canonicalizeUrl(url);6162  const existing = await db63    .select({64      sourceId: sources.id,65      title: sources.title,66      contentId: sourceContents.id,67      markdown: sourceContents.markdown,68      contentHash: sourceContents.contentHash,69      wordCount: sourceContents.wordCount,70    })71    .from(sources)72    .innerJoin(sourceContents, eq(sourceContents.sourceId, sources.id))73    .where(74      and(75        eq(sources.canonicalUrl, canonicalUrl),76        gt(sourceContents.retrievedAt, new Date(Date.now() - FRESHNESS_MS)),77      ),78    )79    .orderBy(desc(sourceContents.retrievedAt))80    .limit(1);8182  if (existing.length > 0) {83    const e = existing[0];84    return {85      sourceId: e.sourceId,86      contentId: e.contentId,87      url,88      canonicalUrl,89      title: e.title,90      markdown: e.markdown,91      contentHash: e.contentHash,92      wordCount: e.wordCount,93      fromCache: true,94    };95  }9697  const scraped: ScrapeResult = await scrapePage(url);98  return persistScrapedPage(url, scraped, investigationId);99}100101/** Persist an already-scraped page (used by scrape and crawl paths). */102export async function persistScrapedPage(103  url: string,104  scraped: ScrapeResult,105  investigationId: string,106): Promise<CachedPage> {107  const { id: sourceId, canonicalUrl } = await upsertSource(scraped.sourceUrl || url, {108    title: scraped.title,109    description: scraped.description,110    investigationId,111  });112  const contentHash = sha256(scraped.markdown);113  const wordCount = scraped.markdown.split(/\s+/).filter(Boolean).length;114  const content = await db115    .insert(sourceContents)116    .values({117      sourceId,118      contentHash,119      markdown: scraped.markdown,120      httpStatus: scraped.statusCode,121      wordCount,122    })123    .returning({ id: sourceContents.id });124  return {125    sourceId,126    contentId: content[0].id,127    url,128    canonicalUrl,129    title: scraped.title,130    markdown: scraped.markdown,131    contentHash,132    wordCount,133    fromCache: false,134  };135}136