`src/enrich/website_via_exa.ts`

171 lines
/**
 * Website discovery for BTRC rows via EXA web search.
 *
 * Picks businesses on the corridor with NO website, runs an EXA query
 * `<name> <city>` + a Ventura Blvd hint, takes the first credible non-social
 * result domain, and writes it to businesses.website.
 *
 * Sample-first by default: --limit=20. Pass --limit=N to scale.
 *
 * Requires EXA_API_KEY in the environment OR ~/Projects/secrets-manager/.env.
 *
 * No Claude / Anthropic. Pure HTTP to api.exa.ai.
 */
import { readFileSync } from 'node:fs';
import { homedir } from 'node:os';
import { join } from 'node:path';
import { query, pool } from '../../db/pool.ts';

const EXA = 'https://api.exa.ai/search';

function loadKey(): string {
  if (process.env.EXA_API_KEY) return process.env.EXA_API_KEY.trim();
  try {
    const txt = readFileSync(join(homedir(), 'Projects/secrets-manager/.env'), 'utf8');
    const m = txt.match(/^EXA_API_KEY=(.+)$/m);
    if (m) return m[1].trim();
  } catch {}
  return '';
}

const KEY = loadKey();

// Domain blocks: social, aggregators, gov, mapping. We want the business's
// own site, not a Yelp/Facebook/Mapquest mirror.
const BLOCK_DOMAINS = new Set([
  'yelp.com', 'facebook.com', 'instagram.com', 'twitter.com', 'x.com',
  'linkedin.com', 'tiktok.com', 'youtube.com', 'pinterest.com',
  'mapquest.com', 'google.com', 'maps.google.com', 'foursquare.com',
  'tripadvisor.com', 'opentable.com', 'doordash.com', 'ubereats.com',
  'zillow.com', 'realtor.com', 'redfin.com', 'apartments.com',
  'bbb.org', 'manta.com', 'bizapedia.com', 'opencorporates.com',
  'lacity.org', 'data.lacity.org', 'lavote.gov', 'lacounty.gov',
  'avvo.com', 'lawyers.com', 'martindale.com', 'findlaw.com',
  'healthgrades.com', 'zocdoc.com', 'vitals.com', 'webmd.com',
  'glassdoor.com', 'indeed.com', 'crunchbase.com',
]);

function hostOf(url: string): string | null {
  try {
    return new URL(url).hostname.replace(/^www\./, '').toLowerCase();
  } catch {
    return null;
  }
}

function isCredibleSite(url: string): boolean {
  const h = hostOf(url);
  if (!h) return false;
  if (BLOCK_DOMAINS.has(h)) return false;
  // Block all subdomains of blocked roots too.
  for (const d of BLOCK_DOMAINS) if (h.endsWith('.' + d)) return false;
  return true;
}

interface ExaResult {
  url: string;
  title?: string;
  publishedDate?: string;
}

async function exaSearch(q: string): Promise<ExaResult[]> {
  const r = await fetch(EXA, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json', 'x-api-key': KEY },
    body: JSON.stringify({ query: q, type: 'auto', numResults: 5 }),
    signal: AbortSignal.timeout(20_000),
  });
  if (!r.ok) throw new Error(`EXA ${r.status}: ${(await r.text()).slice(0, 150)}`);
  const data: any = await r.json();
  return Array.isArray(data?.results) ? data.results : [];
}

interface Row {
  id: number;
  name: string;
  city: string | null;
  address: string | null;
}

async function pickQueue(limit: number): Promise<Row[]> {
  // BTRC rows on corridor with no website, biased toward names that look like
  // real businesses (not just LLC pass-throughs of single-person filings).
  const r = await query<Row>(
    `
    SELECT id, name, city, address
    FROM businesses
    WHERE on_corridor
      AND source = 'la_btrc'
      AND website IS NULL
      AND length(name) >= 5
      AND name NOT LIKE '% LLC' -- LLC-only names rarely have a website
      AND name NOT ILIKE 'POST OFFICE BOX%'
    ORDER BY id
    LIMIT $1
  `,
    [limit]
  );
  return r.rows;
}

async function enrichOne(row: Row): Promise<{ ok: boolean; website?: string; reason?: string }> {
  // Build a focused query: name + Ventura Blvd + city.
  const q = `"${row.name}" Ventura Blvd ${row.city ?? 'Sherman Oaks'}`;
  let results: ExaResult[];
  try {
    results = await exaSearch(q);
  } catch (e: any) {
    return { ok: false, reason: 'exa-err: ' + (e.message || String(e)).slice(0, 100) };
  }
  for (const hit of results) {
    if (isCredibleSite(hit.url)) {
      const host = hostOf(hit.url);
      if (host) {
        const website = 'https://' + host;
        await query(`UPDATE businesses SET website = $1, last_seen_at = now() WHERE id = $2`, [
          website,
          row.id,
        ]);
        return { ok: true, website };
      }
    }
  }
  return { ok: false, reason: 'no credible result' };
}

async function main() {
  if (!KEY) {
    console.error('[exa] EXA_API_KEY not set. Add via /secrets, or pass EXA_API_KEY=… env.');
    process.exit(1);
  }
  const argLimit = process.argv.find((a) => a.startsWith('--limit='));
  const limit = argLimit ? parseInt(argLimit.split('=')[1], 10) : 20;

  const queue = await pickQueue(limit);
  console.log(`[exa] enriching ${queue.length} BTRC rows (no website)`);
  let ok = 0,
    miss = 0,
    err = 0;
  for (const row of queue) {
    const r = await enrichOne(row);
    if (r.ok) {
      ok++;
      console.log(`  ✓ ${row.name.padEnd(45).slice(0, 45)} → ${r.website}`);
    } else if ((r.reason || '').startsWith('exa-err')) {
      err++;
      console.log(`  ⚠ ${row.name.padEnd(45).slice(0, 45)} ${r.reason}`);
    } else {
      miss++;
    }
    // 200ms throttle — courteous to EXA + avoids rate spikes.
    await new Promise((res) => setTimeout(res, 200));
  }
  console.log(`[exa] done · ${ok} found · ${miss} no-site-found · ${err} errors`);
  await pool.end();
}

main().catch((e) => {
  console.error('[exa]', e);
  process.exit(1);
});