spb/fetcha

1import { PLAN_LIMITS, PLANS } from "@fetcha/core";2import { db, sql } from "@fetcha/db";34/**5 * Retention: request metadata is kept per plan (3/7/30/90/365 days). Attempts cascade with6 * their request. Usage events (billing ledger) and audit logs are never pruned here.7 * Also expires stale proxy sessions.8 */9export async function pruneRetention(log: { info: (m: string) => void } = console): Promise<void> {10  let total = 0;11  for (const plan of PLANS) {12    const days = PLAN_LIMITS[plan].retention_days;13    const res = await db.execute(sql`14      delete from fetch_requests r15      using organizations o16      where r.organization_id = o.id17        and o.plan = ${plan}18        and r.created_at < now() - make_interval(days => ${days})19    `);20    total += Number((res as { rowCount?: number }).rowCount ?? 0);21  }22  for (const plan of PLANS) {23    const days = PLAN_LIMITS[plan].retention_days;24    await db.execute(sql`25      delete from crawl_jobs j26      using organizations o27      where j.organization_id = o.id28        and o.plan = ${plan}29        and j.created_at < now() - make_interval(days => ${days})30    `);31  }32  await db.execute(sql`update proxy_sessions set status = 'expired' where status = 'active' and expires_at < now()`);33  await db.execute(sql`delete from provider_health where checked_at < now() - interval '30 days'`);34  await db.execute(sql`delete from verifications where expires_at < now() - interval '1 day'`);35  log.info(`retention prune complete (${total} requests removed)`);36}37