feat: apply_exa_websites.ts — merge Exa-discovered website JSON into businesses table w/ aggregator filter (yelp/fb/yp/ig blocked)

fd03767bff4fd57ba1186ac76c61d2d80a82af91 · 2026-05-07 12:40:06 -0700 · SteveStudio2

Files touched

A src/enrich/apply_exa_websites.ts

Diff

commit fd03767bff4fd57ba1186ac76c61d2d80a82af91
Author: SteveStudio2 <stevestudio2@SteveStacStudio.lan>
Date:   Thu May 7 12:40:06 2026 -0700

    feat: apply_exa_websites.ts — merge Exa-discovered website JSON into businesses table w/ aggregator filter (yelp/fb/yp/ig blocked)
---
 src/enrich/apply_exa_websites.ts | 60 ++++++++++++++++++++++++++++++++++++++++
 1 file changed, 60 insertions(+)

diff --git a/src/enrich/apply_exa_websites.ts b/src/enrich/apply_exa_websites.ts
new file mode 100644
index 0000000..46618b8
--- /dev/null
+++ b/src/enrich/apply_exa_websites.ts
@@ -0,0 +1,60 @@
+// Apply Exa-discovered websites to the businesses table.
+//
+// Reads a JSON array (file or stdin) of `[{ id, name, website, source }, ...]`,
+// validates each URL (must be http/https, must NOT be yelp/facebook/yellowpages
+// /bbb/manta/google.com — those are aggregators not primary websites),
+// then UPDATEs businesses.website where currently null.
+//
+// Run:
+//   npx tsx src/enrich/apply_exa_websites.ts /tmp/exa_results.json
+//   curl ... | npx tsx src/enrich/apply_exa_websites.ts -
+
+import * as fs from 'node:fs';
+import { pool, query } from '../../db/pool.ts';
+
+const AGGREGATORS = /^(www\.)?(yelp|facebook|instagram|twitter|x|linkedin|yellowpages|bbb|manta|trustpilot|opentable|grubhub|doordash|ubereats|google|maps\.google|tripadvisor|nextdoor|whitepages|spokeo)\.(com|net|org)$/i;
+
+function normalize(raw: string): string | null {
+  if (!raw) return null;
+  let u = raw.trim();
+  if (!u) return null;
+  if (!/^https?:\/\//i.test(u)) u = 'https://' + u;
+  let parsed: URL;
+  try { parsed = new URL(u); } catch { return null; }
+  if (AGGREGATORS.test(parsed.host)) return null;
+  // Strip query strings and fragments — keep path so brand chain pages survive
+  return parsed.protocol + '//' + parsed.host + parsed.pathname.replace(/\/+$/, '');
+}
+
+async function main() {
+  const path = process.argv[2];
+  if (!path) {
+    console.error('usage: apply_exa_websites.ts <file.json | ->');
+    process.exit(1);
+  }
+  const raw = path === '-' ? fs.readFileSync(0, 'utf8') : fs.readFileSync(path, 'utf8');
+  // Tolerate the agent wrapping the JSON in markdown fences or commentary.
+  const m = raw.match(/\[[\s\S]*\]/);
+  if (!m) { console.error('no JSON array found in input'); process.exit(1); }
+  const rows: Array<{ id: number; name: string; website: string | null; source?: string }> = JSON.parse(m[0]);
+  console.log(`[apply_exa] ${rows.length} candidates`);
+
+  let updated = 0, skipped_null = 0, skipped_invalid = 0, skipped_existing = 0;
+  for (const r of rows) {
+    if (!r.website) { skipped_null++; continue; }
+    const norm = normalize(r.website);
+    if (!norm) { skipped_invalid++; console.log(`  ✕ ${r.id} ${r.name} → ${r.website} (rejected)`); continue; }
+
+    const existing = await query<{ website: string | null }>(`SELECT website FROM businesses WHERE id = $1`, [r.id]);
+    if (!existing.rowCount) { console.log(`  ? ${r.id} not found`); continue; }
+    if (existing.rows[0].website) { skipped_existing++; continue; }
+
+    await query(`UPDATE businesses SET website = $1, last_seen_at = NOW() WHERE id = $2`, [norm, r.id]);
+    updated++;
+    console.log(`  ✓ ${r.id} ${r.name.slice(0, 40).padEnd(40)} → ${norm} [${r.source || '?'}]`);
+  }
+  console.log(`[apply_exa] done · ${updated} updated · ${skipped_null} null · ${skipped_invalid} invalid · ${skipped_existing} already had website`);
+  await pool.end();
+}
+
+main().catch(e => { console.error(e); process.exit(1); });

← 8afd939 feat(news): per-business news rail on map detail panel + new · back to Ventura Corridor · feat(news): news_count badge on /buildings.html tenant rows 0418331 →