contacts: $0 own-site miner for linkedin/email/phone (NANP-validated, TOS-safe)

90cf00f031b85d64f55b833ed6f2b0052dfbc612 · 2026-08-12 09:40:12 -0700 · Steve Abrams
Files touched

A scripts/enrich-contacts.js
Diff

commit 90cf00f031b85d64f55b833ed6f2b0052dfbc612
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Aug 12 09:40:12 2026 -0700

    contacts: $0 own-site miner for linkedin/email/phone (NANP-validated, TOS-safe)
---
 scripts/enrich-contacts.js | 112 +++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 112 insertions(+)

diff --git a/scripts/enrich-contacts.js b/scripts/enrich-contacts.js
new file mode 100644
index 0000000..4117c29
--- /dev/null
+++ b/scripts/enrich-contacts.js
@@ -0,0 +1,112 @@
+// Contact deep-dive ($0 LOCAL). For every entity with a known website, MINE the site
+// (home + /contact + /about) for the REAL LinkedIn profile URL, email, and direct phone.
+// LinkedIn is captured from the entity's OWN site (authoritative + TOS-safe — we NEVER
+// request linkedin.com, which blocks bots and forbids scraping). Writes linkedin / email /
+// phone_web + contacts_enriched_at onto cslb_raw. Resumable (contacts_enriched_at stamp),
+// shardable (--shard=i/N) for max-it fan-out. Phone is mostly already on CSLB.BusinessPhone;
+// phone_web is a supplementary web-sourced direct line (never overwrites the canonical one).
+//
+// Usage: node scripts/enrich-contacts.js [--loop] [--batch=N] [--shard=i/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 BATCH = Number(kv.batch || 40);
+const LA_ONLY = !flags.has('--all');
+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}` : '';
+const sleep = ms => new Promise(r => setTimeout(r, ms));
+
+// non-global tester (safe for .test); global extractor (safe for .match)
+const HAS_LI = /linkedin\.com\/(?:company|in|pub|school)\//i;
+const LI_RE = /https?:\/\/(?:[a-z]{2,3}\.)?linkedin\.com\/(?:company|in|pub|school)\/[A-Za-z0-9._~%\-]+/ig;
+const EMAIL_RE = /[a-z0-9._%+\-]+@[a-z0-9.\-]+\.[a-z]{2,}/ig;
+const PHONE_RE = /(?:\+?1[-.\s]?)?\(?\d{3}\)?[-.\s]?\d{3}[-.\s]?\d{4}/g;
+const BAD_EMAIL = /(example|sentry|wixpress|godaddy|\.png|\.jpe?g|\.gif|@2x|domain\.com|email\.com|yourname|test@|no-?reply)/i;
+const BAD_LI = /linkedin\.com\/(?:shareArticle|sharing|cws|feed|company\/setup|sales|learning|jobs)/i;
+
+async function fetchText(url) {
+  try {
+    const res = await fetch(url, { headers: { 'User-Agent': 'Mozilla/5.0' }, signal: AbortSignal.timeout(8000), redirect: 'follow' });
+    if (!res.ok) return '';
+    if (!/html/.test(res.headers.get('content-type') || '')) return '';
+    return (await res.text()).slice(0, 120000);
+  } catch { return ''; }
+}
+
+function pickLinkedin(html) {
+  const hits = [...new Set((html.match(LI_RE) || []).map(u => u.replace(/\/$/, '')))].filter(u => !BAD_LI.test(u));
+  if (!hits.length) return null;
+  return hits.find(u => /\/company\//i.test(u)) || hits[0]; // prefer the company page
+}
+function pickEmail(html, site) {
+  const host = site.replace(/^https?:\/\//, '').replace(/^www\./, '').split('/')[0];
+  const emails = [...new Set((html.match(EMAIL_RE) || []).map(e => e.toLowerCase()))].filter(e => !BAD_EMAIL.test(e));
+  if (!emails.length) return null;
+  return emails.find(e => e.endsWith('@' + host)) || emails[0]; // prefer their own domain
+}
+// famous web-template default numbers (baked into thousands of themes) — never a real lead
+const BAD_PHONE = new Set(['2147483647', '1234567890', '8000000000', '1231231234', '9876543210']);
+function validNANP(d) {                                 // real North-American number, not a random digit run
+  if (!d || d.length !== 10) return false;
+  if (/^(\d)\1{9}$/.test(d)) return false;              // 3333333333 placeholder
+  if (BAD_PHONE.has(d) || /^\d{3}555\d{4}$/.test(d)) return false; // template default / 555 fictional
+  return /^[2-9]\d\d[2-9]\d{6}$/.test(d);               // area + exchange must start 2-9
+}
+function pickPhone(html) {
+  const tel = html.match(/tel:\+?([0-9\-.\s()]{7,})/i); // tel: link is the strongest signal
+  if (tel) { const d = tel[1].replace(/[^0-9]/g, '').slice(-10); if (validNANP(d)) return d; }
+  for (const raw of (html.match(PHONE_RE) || [])) {
+    const d = raw.replace(/[^0-9]/g, '');
+    const t = (d.length === 11 && d[0] === '1') ? d.slice(1) : d;
+    if (validNANP(t)) return t;
+  }
+  return null;
+}
+const fmtPhone = d => d && d.length === 10 ? `(${d.slice(0,3)}) ${d.slice(3,6)}-${d.slice(6)}` : (d || '');
+
+async function enrichOne(c) {
+  const out = { linkedin: null, email: null, phone: null };
+  const site = c.website;
+  let html = await fetchText(site);
+  if (html && !HAS_LI.test(html)) {              // no LI on home → check a contact/about page
+    const base = site.replace(/\/$/, '');
+    for (const p of ['/contact', '/contact-us', '/about']) {
+      const extra = await fetchText(base + p);
+      if (extra) html += ' ' + extra;
+      if (HAS_LI.test(html)) break;
+    }
+  }
+  if (html) { out.linkedin = pickLinkedin(html); out.email = pickEmail(html, site); out.phone = pickPhone(html); }
+  await q(`UPDATE cslb_raw SET linkedin=$2, email=COALESCE($3,email), phone_web=$4, contacts_enriched_at=now() WHERE "LicenseNo"=$1`,
+    [c.LicenseNo, out.linkedin, out.email, out.phone]);
+  return out;
+}
+
+async function pick() {
+  const la = LA_ONLY ? `AND c."County"='Los Angeles'` : '';
+  return (await q(`SELECT c."LicenseNo", c."BusinessName", c.website
+    FROM cslb_raw c
+    WHERE c."PrimaryStatus"='CLEAR' AND c.website IS NOT NULL AND c.contacts_enriched_at IS NULL ${la} ${SHARD}
+    ORDER BY c."LicenseNo" LIMIT ${BATCH}`)).rows;
+}
+
+async function main() {
+  let done = 0, li = 0, em = 0, ph = 0;
+  for (;;) {
+    const batch = await pick();
+    if (!batch.length) { console.log(`\n✔ site-mining pass complete (${LA_ONLY ? 'LA' : 'all'} entities with a website)`); break; }
+    for (const c of batch) {
+      const f = await enrichOne(c);
+      done++; if (f.linkedin) li++; if (f.email) em++; if (f.phone) ph++;
+      if (f.linkedin || f.email || f.phone)
+        console.log(`  [${done}] ${(c.BusinessName || '').slice(0, 30).padEnd(30)} ${f.linkedin ? 'in✓' : '   '} ${f.email ? '@✓' : '  '} ${fmtPhone(f.phone)}`);
+      await sleep(120);
+    }
+    const rem = Number((await q(`SELECT count(*) c FROM cslb_raw WHERE "PrimaryStatus"='CLEAR' AND website IS NOT NULL AND contacts_enriched_at IS NULL ${LA_ONLY ? `AND "County"='Los Angeles'` : ''}`)).rows[0].c);
+    console.log(`— ${done} processed · ${li} linkedin · ${em} email · ${ph} phone · ~${rem} with-site left — $0`);
+    if (!flags.has('--loop')) break;
+  }
+  console.log(`\nTotal: ${done} processed, ${li} linkedin, ${em} email, ${ph} phone. $0 (site-mining, local).`);
+}
+main().catch(e => { console.error('enrich-contacts error:', e.message); process.exitCode = 1; }).finally(() => pool.end());
← 15c6ec1 waf: period 10 (plan-allowed) — 20 req/10s = 120/min ceiling · back to La Socrata Ingester · Add --testfire button to canary-alert (fires one real CNCP+G 27b3127 →