← back to La Socrata Ingester
contacts: linkedin_source tier (site=authoritative vs search=candidate) + phase-2 site-less LinkedIn search
d5b07c98860dcefa8df8bcfddfeccae6b6a77dcd · 2026-08-12 09:48:16 -0700 · Steve Abrams
Files touched
M scripts/enrich-contacts.jsA scripts/enrich-linkedin-search.js
Diff
commit d5b07c98860dcefa8df8bcfddfeccae6b6a77dcd
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Aug 12 09:48:16 2026 -0700
contacts: linkedin_source tier (site=authoritative vs search=candidate) + phase-2 site-less LinkedIn search
---
scripts/enrich-contacts.js | 4 ++-
scripts/enrich-linkedin-search.js | 72 +++++++++++++++++++++++++++++++++++++++
2 files changed, 75 insertions(+), 1 deletion(-)
diff --git a/scripts/enrich-contacts.js b/scripts/enrich-contacts.js
index 4117c29..9c38674 100644
--- a/scripts/enrich-contacts.js
+++ b/scripts/enrich-contacts.js
@@ -78,7 +78,9 @@ async function enrichOne(c) {
}
}
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`,
+ await q(`UPDATE cslb_raw SET linkedin=$2, email=COALESCE($3,email), phone_web=$4,
+ linkedin_source=CASE WHEN $2 IS NOT NULL THEN 'site' ELSE linkedin_source END,
+ contacts_enriched_at=now() WHERE "LicenseNo"=$1`,
[c.LicenseNo, out.linkedin, out.email, out.phone]);
return out;
}
diff --git a/scripts/enrich-linkedin-search.js b/scripts/enrich-linkedin-search.js
new file mode 100644
index 0000000..7cceb7b
--- /dev/null
+++ b/scripts/enrich-linkedin-search.js
@@ -0,0 +1,72 @@
+// 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).
+//
+// Usage: node scripts/enrich-linkedin-search.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 BATCH = Number(kv.batch || 30);
+const LA_ONLY = !flags.has('--all');
+const DELAY = Number(process.env.LI_DELAY_MS || 2200); // polite; single worker
+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;
+
+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' } });
+ if (res.status === 403 || res.status === 429) throw new Error('ddg-blocked');
+ if (!res.ok) throw new Error('ddg ' + res.status);
+ const html = await res.text();
+ const out = []; const re = /result__a"[^>]*href="([^"]+)"/g; let m;
+ 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, ' ')
+ .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
+ const li = links.find(u => LI_RE.test(u) && !BAD_LI.test(u));
+ return (li && slugMatches(c.BusinessName, li)) ? li.replace(/\/$/, '').replace(/\?.*$/, '') : null;
+}
+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;
+}
+async function main() {
+ let done = 0, li = 0;
+ for (;;) {
+ const batch = await pick();
+ if (!batch.length) { console.log('\n✔ site-less LinkedIn search complete'); break; }
+ for (const c of batch) {
+ let link = null;
+ try { link = await enrichOne(c); }
+ 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
+ }
+ 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 sleep(DELAY);
+ }
+ console.log(`— ${done} processed · ${li} linkedin — $0`);
+ if (!flags.has('--loop')) break;
+ }
+ console.log(`\nTotal: ${done} processed, ${li} linkedin. $0 (search, local).`);
+}
+main().catch(e => { console.error('li-search error:', e.message); process.exitCode = 1; }).finally(() => pool.end());
← 27b3127 Add --testfire button to canary-alert (fires one real CNCP+G
·
back to La Socrata Ingester
·
contacts: fix $2 type inference in linkedin_source CASE (::t 7ba14e7 →