`scripts/enrich-contractor.js`

118 lines
// Contractor web-enrichment — $0 LOCAL: free DuckDuckGo HTML search + a LOCAL Ollama
// model (verifier) to extract the official website/email/linkedin and confirm it matches
// the CSLB record (name/city/phone). NO paid API. Writes website/email/linkedin/
// enrich_confidence/enrich_source/enriched_at onto cslb_raw.
//
// Resumable: only processes rows with enriched_at IS NULL; every attempt stamps
// enriched_at (success OR not-found OR error) so the loop always makes forward progress.
// Prioritized: contractors in the hottest recent-permit ZIPs first ("recent project" proxy).
//
// Usage: node scripts/enrich-contractor.js [--loop] [--batch=N] [--all]
import { q, pool } from '../src/db.js';

const flags = new Set(process.argv.slice(2).filter(a => a.startsWith('--') && !a.includes('=')));
const kv = Object.fromEntries(process.argv.slice(2).filter(a => a.includes('=')).map(a => a.slice(2).split('=')));
const OLLAMA = kv.ollama || process.env.OLLAMA_HOST || 'http://127.0.0.1:11434';
const MODEL = kv.model || process.env.ENRICH_MODEL || 'qwen2.5:latest'; // fast local model
const DELAY = Number(process.env.ENRICH_DELAY_MS || 1200);   // polite delay between DDG hits
const sleep = ms => new Promise(r => setTimeout(r, ms));
const BATCH = Number(kv.batch || 40);
const LA_ONLY = !flags.has('--all');
// --shard=i/N : this worker only takes rows where hashtext(LicenseNo) % N == i (max-it fan-out)
const [SHARD_I, SHARD_N] = (kv.shard || '0/1').split('/').map(Number);
const SHARD = SHARD_N > 1 ? `AND (abs(hashtext(c."LicenseNo")) % ${SHARD_N}) = ${SHARD_I}` : '';

// ---- free DuckDuckGo HTML search ----
async function ddg(query) {
  const url = 'https://html.duckduckgo.com/html/?q=' + encodeURIComponent(query);
  const res = await fetch(url, { headers: { 'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36' } });
  if (res.status === 403 || res.status === 429) throw new Error('ddg-blocked'); // rate-limited — DO NOT treat as no-site
  if (!res.ok) throw new Error('ddg ' + res.status);
  const html = await res.text();
  const out = [];
  const re = /result__a"[^>]*href="([^"]+)"[^>]*>(.*?)<\/a>[\s\S]*?result__snippet"[^>]*>(.*?)<\/a>/g;
  let m;
  while ((m = re.exec(html)) && out.length < 5) {
    let href = m[1];
    const u = href.match(/uddg=([^&]+)/);
    const link = u ? decodeURIComponent(u[1]) : href;
    const strip = s => s.replace(/<[^>]+>/g, '').replace(/&amp;/g, '&').replace(/&#x27;/g, "'").trim();
    out.push({ url: link, title: strip(m[2]), snippet: strip(m[3]) });
  }
  return out;
}

// ---- local model: extract + verify ----
async function extract(c, results) {
  const prompt = `You match a licensed contractor to their OFFICIAL website from web search results.
Contractor: ${c.BusinessName}, ${c.City} CA ${c.ZIPCode}, phone ${c.BusinessPhone || 'n/a'}.
Results:
${results.map((r, i) => `${i + 1}. ${r.title} — ${r.url}\n   ${r.snippet}`).join('\n')}
Pick the contractor's OWN official website (a domain they own, e.g. companyname.com). REJECT any directory/aggregator/listing site — buildzoom, yelp, bbb, mapquest, facebook, instagram, contractorlicenseca, bizapedia, dnb, dandb, manta, chamberofcommerce, houzz, angi, thumbtack, porch, nextdoor, zoominfo, indeed, linkedin (that goes in the linkedin field, not website). Only accept a website match if the business name and city/phone plausibly correspond. Extract email and linkedin if present.
Return ONLY strict JSON: {"website": string|null, "email": string|null, "linkedin": string|null, "confidence": "high"|"low"|"none"}`;
  const res = await fetch(OLLAMA + '/api/generate', {
    method: 'POST', headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ model: MODEL, prompt, stream: false, format: 'json', options: { temperature: 0 } }),
  });
  if (!res.ok) throw new Error('ollama ' + res.status);
  const data = await res.json();
  try { return JSON.parse(data.response); } catch { return { website: null, email: null, linkedin: null, confidence: 'none' }; }
}

async function pick() {
  // prioritize by the contractor's ZIP recent-permit activity ("recent project" proxy)
  const laFilter = LA_ONLY ? `AND "County"='Los Angeles'` : '';
  return (await q(`
    WITH hot AS (
      SELECT zip_code AS zip, count(*) AS activity FROM la_building_permits_raw
      WHERE dataset_id='pi9x-tg5x' AND issue_date > now()-interval '90 days' AND zip_code IS NOT NULL
      GROUP BY 1
    )
    SELECT c."LicenseNo", c."BusinessName", c."City", c."ZIPCode", c."BusinessPhone", c."Classifications(s)" AS cls
    FROM cslb_raw c LEFT JOIN hot ON hot.zip = left(c."ZIPCode",5)
    WHERE c."PrimaryStatus"='CLEAR' AND c."BusinessName" IS NOT NULL AND c.enriched_at IS NULL ${laFilter} ${SHARD}
    ORDER BY COALESCE(hot.activity,0) DESC, c."Classifications(s)" LIKE 'B%' DESC
    LIMIT ${BATCH}`)).rows;
}

// deterministic directory/aggregator blocklist — nulls a website the LLM wrongly accepted
const DIRECTORY = /(buildzoom|yelp|bbb\.org|mapquest|facebook|instagram|contractorlicenseca|contractorlicense|licensedcontractor|bizapedia|dnb\.com|dandb|manta|chamberofcommerce|houzz|angi\.|angieslist|thumbtack|porch\.com|nextdoor|zoominfo|indeed|homeadvisor|networx|birdeye|trustpilot|opencorporates|buzzfile|corporationwiki|dexknows|superpages|yellowpages)/i;

async function enrichOne(c) {
  let fields = { website: null, email: null, linkedin: null, confidence: 'none' };
  try {
    const results = await ddg(`${c.BusinessName} ${c.City} California contractor official website`);
    if (results.length) fields = await extract(c, results);
    if (fields.website && DIRECTORY.test(fields.website)) { fields.website = null; if (fields.confidence === 'high') fields.confidence = 'none'; }
  } catch (e) { fields.confidence = e.message === 'ddg-blocked' ? 'ddg-blocked' : 'error:' + e.message.slice(0, 30); }
  await q(`UPDATE cslb_raw SET website=$2, email=$3, linkedin=$4, enrich_confidence=$5, enrich_source='ddg+local-llm', enriched_at=now() WHERE "LicenseNo"=$1`,
    [c.LicenseNo, fields.website || null, fields.email || null, fields.linkedin || null, fields.confidence || 'none']);
  return fields;
}

async function main() {
  // verify local model reachable
  const tags = await (await fetch(OLLAMA + '/api/tags')).json().catch(() => null);
  if (!tags || !tags.models?.some(m => m.name === MODEL)) { console.error(`local model ${MODEL} not available on ${OLLAMA}`); process.exit(1); }

  let done = 0, found = 0;
  for (;;) {
    const batch = await pick();
    if (!batch.length) { console.log(`\n✔ enrichment complete — no unenriched contractors left (${LA_ONLY ? 'LA County' : 'all'})`); break; }
    for (const c of batch) {
      const f = await enrichOne(c);
      done++; if (f.website) found++;
      if (done % 10 === 0 || f.website) console.log(`  [${done}] ${c.BusinessName.slice(0, 34).padEnd(34)} ${f.website ? '→ ' + f.website + ' (' + f.confidence + ')' : '· ' + f.confidence}`);
      // DDG rate-limit backoff: cool down so it recovers instead of hammering
      if (f.confidence === 'ddg-blocked') { console.log('  ⏸ DDG rate-limited — cooling 45s'); await sleep(45000); }
      else await sleep(DELAY);
    }
    const rem = Number((await q(`SELECT count(*) c FROM cslb_raw WHERE "PrimaryStatus"='CLEAR' AND enriched_at IS NULL ${LA_ONLY ? `AND "County"='Los Angeles'` : ''}`)).rows[0].c);
    console.log(`— batch done: ${done} processed, ${found} websites found, ~${rem.toLocaleString()} remaining — $0 (local)`);
    if (!flags.has('--loop')) break;
  }
  console.log(`\nTotal: ${done} processed, ${found} websites found. $0 (free search + local model).`);
}

main().catch(e => { console.error('enrich error:', e.message); process.exitCode = 1; }).finally(() => pool.end());