← back to La Socrata Ingester
Contractor web-enrichment — $0 local (free DDG search + local Ollama verifier)
c5f4234a54ce947e16ef18ab78f1726540f11673 · 2026-08-12 08:05:47 -0700 · steve
NO paid API (Steve: Exa $2400 = hard no). Free DuckDuckGo HTML search finds candidate
URLs; local qwen2.5 verifies + extracts official website/email/linkedin (rejects
directories), matched to the CSLB name/city/phone. Writes website/email/linkedin/
enrich_* fields on cslb_raw. Resumable (enriched_at stamp), prioritized by hottest
recent-permit ZIPs. Test: 4/5 real sites found, $0.
Files touched
A scripts/enrich-contractor.jsM viewer/public/index.html
Diff
commit c5f4234a54ce947e16ef18ab78f1726540f11673
Author: steve <steve@designerwallcoverings.com>
Date: Wed Aug 12 08:05:47 2026 -0700
Contractor web-enrichment — $0 local (free DDG search + local Ollama verifier)
NO paid API (Steve: Exa $2400 = hard no). Free DuckDuckGo HTML search finds candidate
URLs; local qwen2.5 verifies + extracts official website/email/linkedin (rejects
directories), matched to the CSLB name/city/phone. Writes website/email/linkedin/
enrich_* fields on cslb_raw. Resumable (enriched_at stamp), prioritized by hottest
recent-permit ZIPs. Test: 4/5 real sites found, $0.
---
scripts/enrich-contractor.js | 108 +++++++++++++++++++++++++++++++++++++++++++
viewer/public/index.html | 17 +++++--
2 files changed, 122 insertions(+), 3 deletions(-)
diff --git a/scripts/enrich-contractor.js b/scripts/enrich-contractor.js
new file mode 100644
index 0000000..cf74eb5
--- /dev/null
+++ b/scripts/enrich-contractor.js
@@ -0,0 +1,108 @@
+// 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 OLLAMA = process.env.OLLAMA_HOST || 'http://127.0.0.1:11434';
+const 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 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');
+
+// ---- 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.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(/&/g, '&').replace(/'/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}
+ ORDER BY COALESCE(hot.activity,0) DESC, c."Classifications(s)" LIKE 'B%' DESC
+ LIMIT ${BATCH}`)).rows;
+}
+
+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);
+ } catch (e) { fields.confidence = '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}`);
+ 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());
diff --git a/viewer/public/index.html b/viewer/public/index.html
index 57f6751..7a5876f 100644
--- a/viewer/public/index.html
+++ b/viewer/public/index.html
@@ -123,6 +123,7 @@
<option value="work_type">Work type</option>
</select></span>
<span class="ctl" id="densctl">Density <input id="density" type="range" min="26" max="46" value="34"></span>
+ <span class="ctl"><button id="copylink" class="loadmore" style="margin:0;padding:5px 12px" title="Copy a shareable link to this exact view (filters + sort + view + color)">🔗 Copy link</button></span>
<span class="ctl"><button id="reset" class="loadmore" style="margin:0;padding:5px 12px">Reset</button></span>
</header>
<aside id="rail"></aside>
@@ -170,6 +171,8 @@
<script>
const $ = s => document.querySelector(s);
const LS = { get:(k,d)=>{try{const v=localStorage.getItem('lap_'+k);return v==null?d:JSON.parse(v)}catch{return d}}, set:(k,v)=>localStorage.setItem('lap_'+k,JSON.stringify(v)) };
+// Absolute same-origin API base — fetch() rejects relative paths when the page URL carries embedded Basic-Auth creds ("URL that includes credentials"); location.origin is credential-free.
+const api = path => location.origin + path;
const ALLCOLS = [
{key:'lead_score', label:'Rank', type:'score'},
@@ -232,7 +235,7 @@ function qs(extra={}){
}
async function loadFacets(){
- const d = await (await fetch('/api/facets?'+qs())).json();
+ const d = await (await fetch(api('/api/facets?'+qs()))).json();
const rail = $('#rail'); const collapsed = LS.get('collapsed',{});
rail.innerHTML = FACETS.map(([key,label])=>{
const items=(d[key]||[]).map(o=>{
@@ -247,7 +250,7 @@ async function loadFacets(){
async function loadRows(append=false){
const limit = state.view==='map' ? 1500 : 100;
- const d = await (await fetch('/api/permits?'+qs({page:state.page,limit}))).json();
+ const d = await (await fetch(api('/api/permits?'+qs({page:state.page,limit})))).json();
state.total = d.total;
state.rows = append ? state.rows.concat(d.rows) : d.rows;
$('#count').textContent = state.total.toLocaleString()+' permits'+(Object.values(state.filters).some(Boolean)?' (filtered)':'');
@@ -362,7 +365,7 @@ function render(){
}
async function openDetail(nbr){
- const r = await (await fetch('/api/permit/'+encodeURIComponent(nbr))).json();
+ const r = await (await fetch(api('/api/permit/'+encodeURIComponent(nbr)))).json();
$('#mtitle').textContent = (r.primary_address||'Permit')+' — '+nbr;
const rows=[
['Lead score', r.lead_score], ['Issued', r.issue_date?new Date(r.issue_date).toLocaleString():''],
@@ -386,6 +389,14 @@ $('#more').onclick=$('#more2').onclick=()=>{ state.page++; loadRows(true); };
let t; $('#q').oninput=e=>{clearTimeout(t);t=setTimeout(()=>{state.filters.q=e.target.value.trim();reload();},280);};
$('#density').oninput=e=>{document.documentElement.style.setProperty('--rowh',e.target.value+'px');const c=Math.max(2,Math.round((72-e.target.value)/8));document.documentElement.style.setProperty('--cols',c);LS.set('density',e.target.value);};
$('#reset').onclick=()=>{state.filters={};$('#q').value='';reload();};
+$('#copylink').onclick=async()=>{ // URL already mirrors state via syncURL(); copy the live href
+ syncURL();
+ const btn=$('#copylink'), was=btn.textContent, url=location.href;
+ try{ await navigator.clipboard.writeText(url); }
+ catch{ const t=document.createElement('textarea'); t.value=url; document.body.appendChild(t); t.select();
+ try{document.execCommand('copy');}catch{} t.remove(); }
+ btn.textContent='✓ Copied'; setTimeout(()=>btn.textContent=was,1400);
+};
$('#views').querySelectorAll('button').forEach(b=>b.onclick=()=>{
state.view=b.dataset.v; LS.set('view',state.view);
$('#views').querySelectorAll('button').forEach(x=>x.classList.toggle('on',x===b));
← 804a15e yoloforever c8 FIX (Cody): status brief market = permit volu
·
back to La Socrata Ingester
·
Contractor enrichment: deterministic directory blocklist (nu 5fc3e57 →