li-search: multi-source (cslb + rentv_licensed_targets) through one rate budget

71dd4abbc785ccf633593cafe6a4a69dcce776f2 · 2026-08-12 09:56:01 -0700 · Steve Abrams
Files touched

M scripts/enrich-linkedin-search.js
Diff

commit 71dd4abbc785ccf633593cafe6a4a69dcce776f2
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Aug 12 09:56:01 2026 -0700

    li-search: multi-source (cslb + rentv_licensed_targets) through one rate budget
---
 scripts/enrich-linkedin-search.js | 73 +++++++++++++++++++++++----------------
 1 file changed, 44 insertions(+), 29 deletions(-)

diff --git a/scripts/enrich-linkedin-search.js b/scripts/enrich-linkedin-search.js
index 7cceb7b..4340441 100644
--- a/scripts/enrich-linkedin-search.js
+++ b/scripts/enrich-linkedin-search.js
@@ -1,9 +1,10 @@
-// Phase-2 contact deep-dive ($0 LOCAL): LinkedIn-by-SEARCH for entities with NO website.
-// Polite LOW-RATE single worker over free DuckDuckGo HTML. Looks up "<name> <city> CA
-// linkedin", pulls the first real linkedin.com/(company|in|pub) URL, and accepts it ONLY
-// if a significant name token also appears in the profile slug (precision guard). We NEVER
-// request linkedin.com itself (TOS + bot-block). DDG-block => cool down and DO NOT stamp,
-// so a rate-limit is never mistaken for "no profile" (the data-loss lesson).
+// Multi-source LinkedIn-by-SEARCH ($0 LOCAL) for every RE entity with NO website.
+// ONE polite low-rate DuckDuckGo stream drains each source pool in order (CSLB site-less →
+// rentv_licensed_targets → …). A 2nd concurrent DDG worker would just throttle both, so this
+// is deliberately single-worker + multi-source. Looks up "<name> <city> CA linkedin", takes
+// the first real linkedin.com/(company|in|pub) URL, and accepts it ONLY if a name token
+// (>=4 chars) appears in the slug. NEVER requests linkedin.com. DDG-block => cool down and
+// DO NOT stamp (retry later), so a rate-limit is never mistaken for "no profile".
 //
 // Usage: node scripts/enrich-linkedin-search.js [--loop] [--batch=N] [--all]
 import { q, pool } from '../src/db.js';
@@ -12,11 +13,19 @@ const flags = new Set(process.argv.slice(2).filter(a => a.startsWith('--') && !a
 const kv = Object.fromEntries(process.argv.slice(2).filter(a => a.includes('=')).map(a => a.slice(2).split('=')));
 const BATCH = Number(kv.batch || 30);
 const LA_ONLY = !flags.has('--all');
-const DELAY = Number(process.env.LI_DELAY_MS || 2200);      // polite; single worker
+const DELAY = Number(process.env.LI_DELAY_MS || 2200);
 const sleep = ms => new Promise(r => setTimeout(r, ms));
 const LI_RE = /https?:\/\/(?:[a-z]{2,3}\.)?linkedin\.com\/(?:company|in|pub|school)\/[A-Za-z0-9._~%\-]+/i;
 const BAD_LI = /linkedin\.com\/(?:shareArticle|sharing|cws|feed|company\/setup|sales|learning|jobs|pulse|posts|directory)/i;
 
+// each source: how to read a pool of un-enriched rows and where to write the result back
+const SOURCES = [
+  { key: 'cslb', table: 'cslb_raw', id: '"LicenseNo"', name: '"BusinessName"', city: '"City"',
+    where: `"PrimaryStatus"='CLEAR' AND website IS NULL${LA_ONLY ? ` AND "County"='Los Angeles'` : ''}` },
+  { key: 'rentv', table: 'rentv_licensed_targets', id: 'id', name: 'entity_name', city: 'city',
+    where: `(website IS NULL OR website='')` },
+];
+
 async function ddgLinks(query) {
   const res = await fetch('https://html.duckduckgo.com/html/?q=' + encodeURIComponent(query),
     { headers: { 'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36' } });
@@ -27,44 +36,50 @@ async function ddgLinks(query) {
   while ((m = re.exec(html)) && out.length < 8) { const u = m[1].match(/uddg=([^&]+)/); out.push(u ? decodeURIComponent(u[1]) : m[1]); }
   return out;
 }
-// accept a LinkedIn URL only if a real name token (>=4 chars) shows up in its slug
 function slugMatches(name, url) {
   const slug = url.toLowerCase().replace(/[^a-z0-9]/g, '');
-  const toks = String(name).toLowerCase()
-    .replace(/\b(inc|llc|corp|co|ltd|the|construction|builders|building|group|company|dev|development|and|of|services|enterprises)\b/g, ' ')
+  const toks = String(name || '').toLowerCase()
+    .replace(/\b(inc|llc|corp|co|ltd|the|construction|builders|building|group|company|dev|development|and|of|services|enterprises|realty|real|estate|properties|associates)\b/g, ' ')
     .replace(/[^a-z0-9 ]/g, ' ').split(/\s+/).filter(t => t.length >= 4);
   return toks.some(t => slug.includes(t));
 }
-async function enrichOne(c) {
-  const links = await ddgLinks(`${c.BusinessName} ${c.City || ''} CA linkedin`);   // throws 'ddg-blocked' up
+async function findLinkedin(name, city) {
+  const links = await ddgLinks(`${name} ${city || ''} CA linkedin`);   // throws 'ddg-blocked' up
   const li = links.find(u => LI_RE.test(u) && !BAD_LI.test(u));
-  return (li && slugMatches(c.BusinessName, li)) ? li.replace(/\/$/, '').replace(/\?.*$/, '') : null;
+  return (li && slugMatches(name, li)) ? li.replace(/\/$/, '').replace(/\?.*$/, '') : null;
 }
+
+// pick a batch from the first non-empty source (drains cslb, then rentv, …)
 async function pick() {
-  const la = LA_ONLY ? `AND "County"='Los Angeles'` : '';
-  return (await q(`SELECT "LicenseNo","BusinessName","City" FROM cslb_raw
-    WHERE "PrimaryStatus"='CLEAR' AND website IS NULL AND contacts_enriched_at IS NULL ${la}
-    ORDER BY "LicenseNo" LIMIT ${BATCH}`)).rows;
+  for (const s of SOURCES) {
+    const rows = (await q(`SELECT ${s.id} AS id, ${s.name} AS name, ${s.city} AS city
+      FROM ${s.table} WHERE ${s.where} AND contacts_enriched_at IS NULL
+      ORDER BY ${s.id} LIMIT ${BATCH}`)).rows;
+    if (rows.length) return { src: s, rows };
+  }
+  return { src: null, rows: [] };
 }
+
 async function main() {
-  let done = 0, li = 0;
+  let done = 0, li = 0, curKey = '';
   for (;;) {
-    const batch = await pick();
-    if (!batch.length) { console.log('\n✔ site-less LinkedIn search complete'); break; }
-    for (const c of batch) {
+    const { src, rows } = await pick();
+    if (!rows.length) { console.log('\n✔ multi-source LinkedIn search complete (all pools drained)'); break; }
+    if (src.key !== curKey) { curKey = src.key; console.log(`\n▶ source: ${src.key} (${src.table})`); }
+    for (const c of rows) {
       let link = null;
-      try { link = await enrichOne(c); }
+      try { link = await findLinkedin(c.name, c.city); }
       catch (e) {
-        if (e.message === 'ddg-blocked') { console.log('  ⏸ DDG blocked — cooling 60s (no stamp; will retry)'); await sleep(60000); continue; }
-        // other error: treat as attempted (no profile), stamp so we move on
+        if (e.message === 'ddg-blocked') { console.log('  ⏸ DDG blocked — cooling 60s (no stamp; retry)'); await sleep(60000); continue; }
+        // other error → treat as attempted, stamp and move on
       }
-      await q(`UPDATE cslb_raw SET linkedin=COALESCE($2,linkedin),
-                 linkedin_source=CASE WHEN $2 IS NOT NULL THEN 'search' ELSE linkedin_source END,
-                 contacts_enriched_at=now() WHERE "LicenseNo"=$1`, [c.LicenseNo, link]);
-      done++; if (link) { li++; console.log(`  [${done}] ${c.BusinessName.slice(0, 32).padEnd(32)} → ${link}`); }
+      await q(`UPDATE ${src.table} SET linkedin=COALESCE($2::text,linkedin),
+                 linkedin_source=CASE WHEN $2::text IS NOT NULL THEN 'search' ELSE linkedin_source END,
+                 contacts_enriched_at=now() WHERE ${src.id}=$1`, [c.id, link]);
+      done++; if (link) { li++; console.log(`  [${done}] ${String(c.name).slice(0, 34).padEnd(34)} → ${link}`); }
       await sleep(DELAY);
     }
-    console.log(`— ${done} processed · ${li} linkedin — $0`);
+    console.log(`— ${curKey}: ${done} processed · ${li} linkedin — $0`);
     if (!flags.has('--loop')) break;
   }
   console.log(`\nTotal: ${done} processed, ${li} linkedin. $0 (search, local).`);
← 7ba14e7 contacts: fix $2 type inference in linkedin_source CASE (::t · back to La Socrata Ingester · contacts: per-row try/catch so one bad URL can't crash a sha cefbc8e →