← back to Nationalrealestate
usre: shared CSLB contractor API (search/detail/match) + internal browse surface
c9daafc7ecd507a15898510c06d334c97221fa24 · 2026-08-12 10:13:33 -0700 · steve
- src/server/contractors.ts: GET /api/contractors (search), /api/contractors/match
(mode=home|deal, grouped nearest-first, capped), /api/contractors/:license_no.
Empty-table-safe; verified via isolated harness on seeded rows then torn down.
- src/server/index.ts: mount contractors (behind existing whole-site Basic Auth).
- public/contractors.html: card grid w/ sort + density slider + per-field toggles
(all localStorage-persisted) + in-page county/city/trade facets. Internal only.
- public/nav-drawer.js: add Contractors to canonical fleet nav.
TK-10488.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A public/contractors.htmlM public/nav-drawer.jsA src/server/contractors.tsM src/server/index.ts
Diff
commit c9daafc7ecd507a15898510c06d334c97221fa24
Author: steve <steve@designerwallcoverings.com>
Date: Wed Aug 12 10:13:33 2026 -0700
usre: shared CSLB contractor API (search/detail/match) + internal browse surface
- src/server/contractors.ts: GET /api/contractors (search), /api/contractors/match
(mode=home|deal, grouped nearest-first, capped), /api/contractors/:license_no.
Empty-table-safe; verified via isolated harness on seeded rows then torn down.
- src/server/index.ts: mount contractors (behind existing whole-site Basic Auth).
- public/contractors.html: card grid w/ sort + density slider + per-field toggles
(all localStorage-persisted) + in-page county/city/trade facets. Internal only.
- public/nav-drawer.js: add Contractors to canonical fleet nav.
TK-10488.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
public/contractors.html | 314 ++++++++++++++++++++++++++++++++++++++++++++++
public/nav-drawer.js | 1 +
src/server/contractors.ts | 218 ++++++++++++++++++++++++++++++++
src/server/index.ts | 2 +
4 files changed, 535 insertions(+)
diff --git a/public/contractors.html b/public/contractors.html
new file mode 100644
index 0000000..84677bb
--- /dev/null
+++ b/public/contractors.html
@@ -0,0 +1,314 @@
+<!doctype html>
+<html lang="en">
+<head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width,initial-scale=1">
+<title>CA Contractors · USRealEstate (internal)</title>
+<link rel="stylesheet" href="/nav-drawer.css">
+<style>
+ :root{--bg:#0a0d13;--panel:#11151d;--line:#222936;--fg:#e8ecf2;--muted:#8a93a3;--gold:#c8a24b;--blue:#5a9be0;--card:236px;--fs:13px;}
+ *{box-sizing:border-box;}
+ html,body{margin:0;height:100%;background:var(--bg);color:var(--fg);font:14px/1.5 -apple-system,Segoe UI,Roboto,sans-serif;overflow:hidden;}
+ a{color:var(--gold);text-decoration:none;} a:hover{text-decoration:underline;}
+ #topbar{position:fixed;inset:0 0 auto 0;z-index:1200;display:flex;align-items:center;gap:16px;
+ padding:9px 16px;background:rgba(17,21,29,.96);border-bottom:1px solid var(--line);backdrop-filter:blur(6px);}
+ .brand{font-size:15px;font-weight:700;white-space:nowrap;} .brand b{color:var(--gold);}
+ .brand .tag{font-size:10px;color:var(--muted);font-weight:600;border:1px solid var(--line);border-radius:6px;padding:1px 6px;margin-left:8px;vertical-align:middle;}
+ nav{display:flex;gap:13px;font-size:13px;} nav a{color:var(--muted);} nav a:hover,nav a.active{color:var(--gold);text-decoration:none;}
+ #controls{display:flex;gap:8px;align-items:center;margin-left:auto;flex-wrap:wrap;}
+ #controls input,#controls select{background:var(--bg);border:1px solid var(--line);color:var(--fg);border-radius:8px;padding:6px 10px;font:12px inherit;}
+ #controls input:focus,#controls select:focus{outline:none;border-color:var(--gold);}
+ #q{width:200px;}
+ .lbl{font-size:11px;color:var(--muted);}
+ #density{width:90px;accent-color:var(--gold);}
+ #wrap{position:fixed;inset:49px 0 0 0;display:flex;}
+ #side{width:245px;flex:none;border-right:1px solid var(--line);background:var(--panel);overflow-y:auto;padding:10px;}
+ #side h3{margin:12px 0 6px;font-size:11px;letter-spacing:.08em;text-transform:uppercase;color:var(--gold);}
+ #side h3:first-child{margin-top:2px;}
+ #totals{font-size:12px;color:var(--muted);margin-bottom:6px;font-variant-numeric:tabular-nums;line-height:1.7;}
+ #totals b{color:var(--fg);}
+ .facet{display:flex;justify-content:space-between;gap:6px;padding:4px 7px;border-radius:7px;cursor:pointer;font-size:12.5px;border:1px solid transparent;}
+ .facet:hover{background:var(--bg);border-color:var(--line);}
+ .facet.sel{background:var(--bg);border-color:var(--gold);color:var(--gold);}
+ .facet .ct{color:var(--gold);font-variant-numeric:tabular-nums;flex:none;}
+ .facet .nm{white-space:nowrap;overflow:hidden;text-overflow:ellipsis;}
+ .toggles{display:flex;flex-wrap:wrap;gap:6px;}
+ .toggles label{font-size:11.5px;color:var(--muted);display:inline-flex;align-items:center;gap:4px;cursor:pointer;border:1px solid var(--line);border-radius:12px;padding:2px 8px;}
+ .toggles label.on{color:var(--gold);border-color:var(--gold);}
+ .toggles input{display:none;}
+ #active{display:flex;flex-wrap:wrap;gap:5px;padding:8px 16px;background:var(--panel);border-bottom:1px solid var(--line);}
+ #active:empty{display:none;}
+ .chip{display:inline-flex;align-items:center;gap:5px;font-size:12px;padding:3px 9px;border-radius:12px;border:1px solid var(--gold);color:var(--gold);background:rgba(200,162,75,.08);}
+ .chip b{color:var(--fg);font-weight:600;} .chip .rm{cursor:pointer;opacity:.7;} .chip .rm:hover{opacity:1;}
+ #main{flex:1;display:flex;flex-direction:column;min-width:0;}
+ #gridwrap{flex:1;overflow:auto;padding:14px 16px;}
+ #grid{display:grid;grid-template-columns:repeat(auto-fill,minmax(var(--card),1fr));gap:12px;}
+ .cd{border:1px solid var(--line);border-radius:10px;padding:12px 13px;background:var(--panel);font-size:var(--fs);overflow:hidden;}
+ .cd:hover{border-color:var(--gold);}
+ .cd .nm{font-weight:700;color:var(--fg);margin-bottom:2px;line-height:1.3;}
+ .cd .lic{font-size:11px;color:var(--muted);font-variant-numeric:tabular-nums;margin-bottom:8px;}
+ .cd .lic a{color:var(--blue);}
+ .cd .st{display:inline-block;font-size:10.5px;padding:1px 7px;border-radius:10px;border:1px solid var(--line);color:var(--muted);}
+ .cd .st.active{color:#7fd18a;border-color:#2e5a37;}
+ .cd .row{display:flex;gap:6px;font-size:12px;margin:3px 0;color:var(--fg);}
+ .cd .row .k{color:var(--muted);flex:none;width:64px;}
+ .cd .cls{display:flex;flex-wrap:wrap;gap:4px;margin-top:8px;}
+ .cd .cls .c{font-size:10.5px;padding:1px 6px;border-radius:8px;border:1px solid var(--gold);color:var(--gold);background:rgba(200,162,75,.07);}
+ .cd .cls .c[title]{cursor:help;}
+ .cd .when{font-size:10.5px;color:var(--muted);margin-top:8px;border-top:1px solid var(--line);padding-top:6px;}
+ #status{padding:6px 14px;border-top:1px solid var(--line);font-size:11px;color:var(--muted);background:var(--panel);display:flex;gap:14px;align-items:center;}
+ #status button{background:var(--bg);border:1px solid var(--line);color:var(--muted);border-radius:6px;padding:3px 10px;cursor:pointer;font:11px inherit;}
+ #status button:hover:not(:disabled){color:var(--fg);border-color:var(--muted);} #status button:disabled{opacity:.4;cursor:default;}
+ .empty{color:var(--muted);font-size:13px;padding:50px;text-align:center;grid-column:1/-1;}
+</style>
+</head>
+<body>
+<div id="topbar">
+ <div class="brand">US<b>RealEstate</b> · Contractors <span class="tag">CSLB · internal</span></div>
+ <nav>
+ <a href="/index.html">Map</a><a href="/deals.html">Deals</a>
+ <a href="/parcels.html">Parcels</a><a href="/contractors.html" class="active">Contractors</a>
+ </nav>
+ <div id="controls">
+ <input id="q" type="search" placeholder="Search business name…" autocomplete="off">
+ <select id="class" title="Trade / classification"><option value="">All trades</option></select>
+ <select id="status" title="License status">
+ <option value="Active">Active</option>
+ <option value="all">Any status</option>
+ <option value="Inactive">Inactive</option>
+ <option value="Expired">Expired</option>
+ <option value="Suspended">Suspended</option>
+ </select>
+ <select id="sort" title="Sort">
+ <option value="name">Name A→Z</option>
+ <option value="newest">Newest added</option>
+ <option value="city">City A→Z</option>
+ <option value="county">County A→Z</option>
+ <option value="class">Primary class</option>
+ </select>
+ <span class="lbl">density</span><input id="density" type="range" min="180" max="360" value="236">
+ </div>
+</div>
+
+<div id="wrap">
+ <aside id="side">
+ <div id="totals">Loading…</div>
+ <h3>Show fields</h3>
+ <div class="toggles" id="fieldToggles"></div>
+ <h3>County</h3><div id="fCounty"></div>
+ <h3>City <span class="lbl">(top)</span></h3><div id="fCity"></div>
+ <h3>Trade <span class="lbl">(this page)</span></h3><div id="fClass"></div>
+ </aside>
+ <div id="main">
+ <div id="active"></div>
+ <div id="gridwrap"><div id="grid"><div class="empty">Loading contractors…</div></div></div>
+ <div id="status">
+ <span id="count">—</span>
+ <button id="prev" disabled>‹ Prev</button><button id="next" disabled>Next ›</button>
+ <span style="margin-left:auto">CSLB public-record registry · internal only (not customer-facing)</span>
+ </div>
+ </div>
+</div>
+
+<script src="/nav-drawer.js"></script>
+<script>
+const $ = s => document.querySelector(s);
+const fmt = n => n==null?'—':Number(n).toLocaleString();
+const esc = s => (s==null?'':String(s)).replace(/[&<>"]/g,c=>({'&':'&','<':'<','>':'>','"':'"'}[c]));
+
+// server search params (URL-addressable). client-only bits: sort + field toggles + density.
+const FILTERS = ['q','class','status','county','city'];
+const limit = 60;
+function readState(){
+ const p=new URLSearchParams(location.search); const s={offset:0};
+ for(const f of FILTERS){ const v=p.get(f); if(v) s[f]=v; }
+ const off=parseInt(p.get('offset')||'0',10); if(off>0) s.offset=off;
+ if(!s.status) s.status='Active';
+ return s;
+}
+let state = readState();
+let SORT = localStorage.getItem('ctr:sort') || 'name';
+let PAGE = []; // last-loaded results (client-side sort operates on this page)
+
+// per-field visibility toggles (localStorage-persisted)
+const FIELDS = [
+ ['status','Status'],['address','Address'],['city','City'],['county','County'],
+ ['zip','ZIP'],['phone','Phone'],['license','License #'],['dates','Dates'],
+ ['classes','Trades'],['bond','Bond'],['wc','Workers Comp'],
+];
+let SHOW = JSON.parse(localStorage.getItem('ctr:fields')||'null') || Object.fromEntries(FIELDS.map(f=>[f[0],true]));
+
+function drillUrl(over){
+ const p=new URLSearchParams(); const merged={...state,...over};
+ for(const f of FILTERS){ if(merged[f]) p.set(f,merged[f]); }
+ if(merged.offset>0) p.set('offset',merged.offset);
+ return '/contractors.html?'+p.toString();
+}
+function go(over,replace){
+ state={...state,...over}; if(over && !('offset' in over)) state.offset=0;
+ history[replace?'replaceState':'pushState']({},'',drillUrl({}));
+ syncControls(); renderActive(); loadFeed();
+}
+
+function syncControls(){
+ $('#q').value=state.q||''; $('#class').value=state.class||'';
+ $('#status').value=state.status||'Active'; $('#sort').value=SORT;
+}
+
+// ── field toggles ──
+function renderFieldToggles(){
+ const box=$('#fieldToggles'); box.innerHTML='';
+ for(const [k,lbl] of FIELDS){
+ const l=document.createElement('label'); l.className=SHOW[k]?'on':'';
+ l.innerHTML=`<input type="checkbox" ${SHOW[k]?'checked':''}> ${esc(lbl)}`;
+ l.querySelector('input').onchange=e=>{ SHOW[k]=e.target.checked; l.className=SHOW[k]?'on':'';
+ localStorage.setItem('ctr:fields',JSON.stringify(SHOW)); renderGrid(); };
+ box.appendChild(l);
+ }
+}
+
+// ── active chips ──
+function renderActive(){
+ const box=$('#active'); box.innerHTML='';
+ const chips=[];
+ if(state.q) chips.push(['q','Search','“'+state.q+'”']);
+ if(state.class) chips.push(['class','Trade',state.class]);
+ if(state.county) chips.push(['county','County',state.county]);
+ if(state.city) chips.push(['city','City',state.city]);
+ if(state.status && state.status!=='Active') chips.push(['status','Status',state.status]);
+ for(const [dim,lbl,val] of chips){
+ const c=document.createElement('span'); c.className='chip';
+ c.innerHTML=`${esc(lbl)}: <b>${esc(val)}</b> <span class="rm" title="Remove">✕</span>`;
+ c.querySelector('.rm').onclick=()=>go({[dim]:dim==='status'?'Active':undefined});
+ box.appendChild(c);
+ }
+ if(chips.length>1){
+ const clr=document.createElement('a'); clr.className='chip'; clr.href='/contractors.html';
+ clr.style.borderColor='var(--muted)'; clr.style.color='var(--muted)'; clr.textContent='clear all';
+ clr.onclick=e=>{ e.preventDefault(); go({q:undefined,class:undefined,county:undefined,city:undefined,status:'Active'}); };
+ box.appendChild(clr);
+ }
+}
+
+// ── in-page facets (aggregated from the loaded page, click-to-filter) ──
+function facetList(sel,dim,items){
+ const box=$(sel); box.innerHTML='';
+ for(const it of items){
+ const isSel=String(state[dim]||'')===String(it.key);
+ const a=document.createElement('a');
+ a.className='facet'+(isSel?' sel':''); a.href=drillUrl({[dim]:isSel?undefined:it.key,offset:0});
+ a.innerHTML=`<span class="nm">${esc(it.label)}</span><span class="ct">${fmt(it.n)}</span>`;
+ a.onclick=e=>{ e.preventDefault(); go({[dim]:isSel?undefined:String(it.key)}); };
+ box.appendChild(a);
+ }
+ if(!items.length) box.innerHTML='<div class="lbl" style="padding:4px 7px">—</div>';
+}
+function agg(rows,field){
+ const m=new Map();
+ for(const r of rows){ const v=r[field]; if(!v) continue; m.set(v,(m.get(v)||0)+1); }
+ return [...m.entries()].sort((a,b)=>b[1]-a[1]).map(([k,n])=>({key:k,label:k,n}));
+}
+function aggClasses(rows){
+ const m=new Map();
+ for(const r of rows) for(const c of (r.classifications||[])) m.set(c,(m.get(c)||0)+1);
+ return [...m.entries()].sort((a,b)=>b[1]-a[1]).slice(0,30).map(([k,n])=>({key:k,label:k,n}));
+}
+
+// ── sort (client-side over the loaded page) ──
+const SORTERS = {
+ name: (a,b)=>(a.business_name||'').localeCompare(b.business_name||''),
+ newest: (a,b)=>(b.issue_date||'').localeCompare(a.issue_date||''), // proxy: no created_at in shape → issue_date newest-first
+ city: (a,b)=>(a.city||'~').localeCompare(b.city||'~')||(a.business_name||'').localeCompare(b.business_name||''),
+ county: (a,b)=>(a.county||'~').localeCompare(b.county||'~')||(a.business_name||'').localeCompare(b.business_name||''),
+ class: (a,b)=>(a.primary_class||'~').localeCompare(b.primary_class||'~')||(a.business_name||'').localeCompare(b.business_name||''),
+};
+
+// ── card render ──
+function card(r){
+ const active = (r.license_status||'').toLowerCase()==='active';
+ const parts=[];
+ parts.push(`<div class="nm">${esc(r.business_name)}</div>`);
+ const licBits=[];
+ if(SHOW.license) licBits.push(`<a href="/contractors.html?q=${encodeURIComponent(r.business_name)}">#${esc(r.license_no)}</a>`);
+ if(SHOW.status) licBits.push(`<span class="st${active?' active':''}">${esc(r.license_status||'—')}</span>`);
+ if(licBits.length) parts.push(`<div class="lic">${licBits.join(' · ')}</div>`);
+ const row=(k,v)=>`<div class="row"><span class="k">${k}</span><span>${v}</span></div>`;
+ if(SHOW.address && r.address) parts.push(row('Address',esc(r.address)));
+ if(SHOW.city && r.city) parts.push(row('City',`<a href="/contractors.html?city=${encodeURIComponent(r.city)}">${esc(r.city)}</a>`));
+ if(SHOW.county && r.county) parts.push(row('County',`<a href="/contractors.html?county=${encodeURIComponent(r.county)}">${esc(r.county)}</a>`));
+ if(SHOW.zip && r.zip) parts.push(row('ZIP',esc(r.zip)));
+ if(SHOW.phone && r.phone) parts.push(row('Phone',esc(r.phone)));
+ if(SHOW.dates && (r.issue_date||r.expire_date)) parts.push(row('Dates',`${esc(r.issue_date||'—')} → ${esc(r.expire_date||'—')}`));
+ if(SHOW.bond && (r.bond&&r.bond.company)) parts.push(row('Bond',`${esc(r.bond.company)}${r.bond.amount!=null?' · $'+Number(r.bond.amount).toLocaleString():''}`));
+ if(SHOW.wc && (r.workers_comp&&r.workers_comp.status)) parts.push(row('WC',`${esc(r.workers_comp.status)}${r.workers_comp.carrier?' · '+esc(r.workers_comp.carrier):''}`));
+ if(SHOW.classes && (r.classification_titles||[]).length){
+ const cs=r.classification_titles.map(c=>`<a class="c" title="${esc(c.title||c.code)}" href="/contractors.html?class=${encodeURIComponent(c.code)}">${esc(c.code)}</a>`).join('');
+ parts.push(`<div class="cls">${cs}</div>`);
+ }
+ return `<div class="cd">${parts.join('')}</div>`;
+}
+
+function renderGrid(){
+ const grid=$('#grid');
+ if(!PAGE.length){ grid.innerHTML='<div class="empty">No contractors match. (The CSLB registry may still be loading — this page is empty-safe.)</div>'; return; }
+ const rows=[...PAGE].sort(SORTERS[SORT]||SORTERS.name);
+ grid.innerHTML=rows.map(card).join('');
+ // refresh in-page facets from what's loaded
+ facetList('#fCounty','county',agg(PAGE,'county'));
+ facetList('#fCity','city',agg(PAGE,'city').slice(0,25));
+ facetList('#fClass','class',aggClasses(PAGE));
+}
+
+async function loadFeed(){
+ const p=new URLSearchParams();
+ for(const f of FILTERS){ if(state[f]) p.set(f,state[f]); }
+ p.set('limit',limit); p.set('offset',state.offset||0);
+ $('#count').textContent='loading…';
+ let d; try{ d=await (await fetch('/api/contractors?'+p.toString())).json(); }
+ catch(e){ $('#grid').innerHTML='<div class="empty">Load error.</div>'; return; }
+ if(d.error){ $('#grid').innerHTML='<div class="empty">'+esc(d.error)+'</div>'; return; }
+ PAGE=d.results||[];
+ $('#totals').innerHTML=`<b>${fmt(d.count)}</b> contractors match`;
+ const from=(state.offset||0)+ (PAGE.length?1:0), to=(state.offset||0)+PAGE.length;
+ $('#count').textContent=`${fmt(from)}–${fmt(to)} of ${fmt(d.count)}`;
+ $('#prev').disabled=(state.offset||0)<=0;
+ $('#next').disabled=to>=d.count;
+ renderGrid();
+}
+
+// ── load the trade dropdown from the class-ref (one-time) ──
+async function loadClasses(){
+ try{
+ // no dedicated endpoint for the ref; derive the option list from the seeded codes we know
+ // via a probe search per code would be wasteful — instead offer the canonical CSLB set.
+ const CODES=[['','All trades'],['A','A · General Engineering'],['B','B · General Building'],
+ ['C-8','C-8 · Concrete'],['C-10','C-10 · Electrical'],['C-36','C-36 · Plumbing'],
+ ['C-20','C-20 · HVAC'],['C-39','C-39 · Roofing'],['C-33','C-33 · Painting'],
+ ['C-35','C-35 · Plaster'],['C-15','C-15 · Flooring'],['C-54','C-54 · Tile'],
+ ['C-5','C-5 · Framing'],['C-6','C-6 · Finish Carpentry'],['C-16','C-16 · Fire Protection'],
+ ['C-27','C-27 · Landscaping'],['C-9','C-9 · Drywall'],['C-12','C-12 · Paving'],
+ ['C-13','C-13 · Fencing'],['C-29','C-29 · Masonry'],['C-46','C-46 · Solar'],['C-53','C-53 · Pool']];
+ $('#class').innerHTML=CODES.map(([v,l])=>`<option value="${v}">${esc(l)}</option>`).join('');
+ $('#class').value=state.class||'';
+ }catch(e){}
+}
+
+// ── wire controls ──
+let qTimer;
+$('#q').oninput=e=>{ clearTimeout(qTimer); const v=e.target.value.trim(); qTimer=setTimeout(()=>go({q:v||undefined}),300); };
+$('#class').onchange=e=>go({class:e.target.value||undefined});
+$('#status').onchange=e=>go({status:e.target.value});
+$('#sort').onchange=e=>{ SORT=e.target.value; localStorage.setItem('ctr:sort',SORT); renderGrid(); };
+$('#density').oninput=e=>{ document.documentElement.style.setProperty('--card',e.target.value+'px'); localStorage.setItem('ctr:card',e.target.value); };
+$('#prev').onclick=()=>go({offset:Math.max(0,(state.offset||0)-limit)});
+$('#next').onclick=()=>go({offset:(state.offset||0)+limit});
+window.onpopstate=()=>{ state=readState(); syncControls(); renderActive(); loadFeed(); };
+
+// ── restore persisted density + init ──
+(function init(){
+ const cz=localStorage.getItem('ctr:card'); if(cz){ $('#density').value=cz; document.documentElement.style.setProperty('--card',cz+'px'); }
+ loadClasses(); renderFieldToggles(); syncControls(); renderActive(); loadFeed();
+})();
+</script>
+</body>
+</html>
diff --git a/public/nav-drawer.js b/public/nav-drawer.js
index 6cdef62..4d33d94 100644
--- a/public/nav-drawer.js
+++ b/public/nav-drawer.js
@@ -34,6 +34,7 @@
var NAV = [
['/', 'Map'], ['/markets.html', 'Markets'], ['/commercial.html', 'Commercial'],
['/deals.html', 'Deals'], ['/parcels.html', 'Parcels'],
+ ['/contractors.html', 'Contractors'],
['/compare.html', 'Compare'], ['/brokers.html', 'Brokers'], ['/sources.html', 'Sources'],
['/watchlist.html', 'Watchlist'], ['/admin.html', 'Admin']
];
diff --git a/src/server/contractors.ts b/src/server/contractors.ts
new file mode 100644
index 0000000..75cdc2e
--- /dev/null
+++ b/src/server/contractors.ts
@@ -0,0 +1,218 @@
+/**
+ * SHARED CA CSLB licensed-contractor API — the coordination layer the four RE
+ * builds (usre engine, CRCP/Frank, HomesOnSpec, RENTV) all read. TK-10488.
+ *
+ * Reads migration 018 tables:
+ * ca_contractors (registry, one row per CSLB license)
+ * ca_contractor_class_ref (code -> title/kind, 48 CSLB codes seeded)
+ * ca_contractor_classification (normalized license -> many trades)
+ *
+ * READ-ONLY. All endpoints work on an EMPTY table (data is loaded by a separate
+ * process). Sourcing is CSLB public-record; any customer-facing display is
+ * Steve-gated — this whole server sits behind Basic Auth, so these are internal.
+ *
+ * GET /api/contractors -> search (name/county/city/zip/class/status)
+ * GET /api/contractors/match -> coordination endpoint (mode=home | mode=deal)
+ * GET /api/contractors/:license_no -> one record (same shape)
+ *
+ * NOTE: :license_no route is registered AFTER /match so 'match' is never
+ * swallowed as a license number.
+ */
+import type { Express, Request, Response } from 'express';
+import { query } from '../../db/pool.ts';
+
+const clamp = (v: unknown, def: number, max: number) => {
+ const n = Math.floor(Number(v));
+ return Number.isFinite(n) && n > 0 ? Math.min(n, max) : def;
+};
+const like = (v: unknown, cap = 80) => '%' + String(v).slice(0, cap).replace(/[%_\\]/g, '\\$&') + '%';
+// CSLB classification codes: 'A', 'B', 'B-2', 'C-10', 'ASB', 'HAZ', 'D-49' …
+const CODE_RE = /^[A-Z]{1,3}(-[A-Z0-9]{1,3})?$/;
+const cleanCode = (v: unknown) => {
+ const t = String(v ?? '').trim().toUpperCase();
+ return CODE_RE.test(t) ? t : null;
+};
+const cleanCodes = (v: unknown) =>
+ String(v ?? '').split(',').map((s) => cleanCode(s)).filter((x): x is string => !!x);
+
+/** Default residential trade set for mode=home (GC + the common subs). */
+const HOME_TRADES = ['B', 'C-8', 'C-10', 'C-36', 'C-20', 'C-39', 'C-33', 'C-35', 'C-15', 'C-54', 'C-5', 'C-6'];
+/** GC classes framed for a commercial loan officer (mode=deal). */
+const GC_CLASSES = ['A', 'B'];
+/** Key trades around a CRE asset for mode=deal. */
+const DEAL_TRADES = ['C-8', 'C-10', 'C-20', 'C-36', 'C-39', 'C-16', 'C-35', 'C-12'];
+
+const PER_GROUP_CAP = 25;
+
+// Columns selected for the public contractor shape (+ optional distance alias).
+const CONTRACTOR_COLS = `
+ c.id, c.license_no, c.business_name, c.address, c.city, c.county,
+ c.state_code, c.zip, c.phone, c.license_status,
+ to_char(c.issue_date,'YYYY-MM-DD') AS issue_date,
+ to_char(c.expire_date,'YYYY-MM-DD') AS expire_date,
+ c.classifications, c.primary_class,
+ c.cb_bond_company, c.cb_bond_amount,
+ c.wc_status, c.wc_insurance_co, c.lat, c.lng`;
+
+/** Build the JSON shape one contractor row returns (shared by all endpoints). */
+function shapeContractor(r: any, titles: Map<string, string>) {
+ const codes: string[] = Array.isArray(r.classifications) ? r.classifications : [];
+ return {
+ license_no: r.license_no,
+ business_name: r.business_name,
+ address: r.address ?? null,
+ city: r.city ?? null,
+ county: r.county ?? null,
+ state: r.state_code ?? 'CA',
+ zip: r.zip ?? null,
+ phone: r.phone ?? null,
+ license_status: r.license_status ?? null,
+ issue_date: r.issue_date ?? null,
+ expire_date: r.expire_date ?? null,
+ classifications: codes,
+ primary_class: r.primary_class ?? null,
+ classification_titles: codes.map((code) => ({ code, title: titles.get(code) ?? null })),
+ bond: {
+ company: r.cb_bond_company ?? null,
+ amount: r.cb_bond_amount != null ? Number(r.cb_bond_amount) : null,
+ },
+ workers_comp: {
+ status: r.wc_status ?? null,
+ carrier: r.wc_insurance_co ?? null,
+ },
+ ...(r.distance_km != null ? { distance_km: Number(Number(r.distance_km).toFixed(1)) } : {}),
+ };
+}
+
+/** Load titles for every distinct code appearing in a batch of rows (one query). */
+async function titleMapFor(rows: any[]): Promise<Map<string, string>> {
+ const codes = new Set<string>();
+ for (const r of rows) for (const c of (r.classifications || [])) codes.add(c);
+ if (r_empty(codes)) return new Map();
+ const ref = await query<{ code: string; title: string }>(
+ `SELECT code, title FROM ca_contractor_class_ref WHERE code = ANY($1)`,
+ [[...codes]]);
+ return new Map(ref.rows.map((x) => [x.code, x.title]));
+}
+const r_empty = (s: Set<string>) => s.size === 0;
+
+/** WHERE builder shared by /api/contractors and the match modes. */
+function buildFilter(q: Request['query'], opts: { defaultStatus?: string } = {}) {
+ const params: unknown[] = [];
+ const parts: string[] = [];
+ // status: default 'Active'; status=all (or empty) drops the filter
+ const rawStatus = q.status === undefined ? (opts.defaultStatus ?? 'Active') : String(q.status);
+ if (rawStatus && rawStatus.toLowerCase() !== 'all') {
+ params.push(rawStatus);
+ parts.push(`c.license_status = $${params.length}`);
+ }
+ if (q.q) { params.push(like(q.q)); parts.push(`c.normalized_name ILIKE $${params.length} ESCAPE '\\'`); }
+ if (q.county) { params.push(String(q.county)); parts.push(`upper(c.county) = upper($${params.length})`); }
+ if (q.city) { params.push(String(q.city)); parts.push(`upper(c.city) = upper($${params.length})`); }
+ if (q.zip) { params.push(String(q.zip).slice(0, 10)); parts.push(`c.zip = $${params.length}`); }
+ const code = cleanCode(q.class);
+ if (code) { params.push([code]); parts.push(`c.classifications && $${params.length}::text[]`); }
+ return { where: parts.length ? parts.join(' AND ') : 'TRUE', params };
+}
+
+export function mountContractors(app: Express) {
+ // ── search ────────────────────────────────────────────────────────────────
+ app.get('/api/contractors', async (req: Request, res: Response) => {
+ try {
+ const { where, params } = buildFilter(req.query);
+ const limit = clamp(req.query.limit, 50, 200);
+ const offset = Math.max(0, Math.floor(Number(req.query.offset) || 0));
+ const rows = await query<any>(
+ `SELECT ${CONTRACTOR_COLS} FROM ca_contractors c
+ WHERE ${where}
+ ORDER BY c.business_name ASC
+ LIMIT $${params.length + 1} OFFSET $${params.length + 2}`,
+ [...params, limit, offset]);
+ const cnt = await query<{ n: number }>(
+ `SELECT count(*)::int n FROM ca_contractors c WHERE ${where}`, params);
+ const titles = await titleMapFor(rows.rows);
+ res.json({
+ count: cnt.rows[0]?.n ?? 0,
+ limit, offset,
+ results: rows.rows.map((r) => shapeContractor(r, titles)),
+ });
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+
+ // ── coordination endpoint (registered BEFORE :license_no) ───────────────────
+ app.get('/api/contractors/match', async (req: Request, res: Response) => {
+ try {
+ const mode = String(req.query.mode || 'home').toLowerCase();
+ if (mode !== 'home' && mode !== 'deal') return res.status(400).json({ error: "mode must be 'home' or 'deal'" });
+
+ // geo scope: zip > city > county (at least one recommended, but not required)
+ const zip = req.query.zip ? String(req.query.zip).slice(0, 10) : null;
+ const city = req.query.city ? String(req.query.city) : null;
+ const county = req.query.county ? String(req.query.county) : null;
+ const ctype = req.query.ctype ? String(req.query.ctype) : null;
+
+ // trade groups to fill, per mode
+ let groups: string[];
+ if (mode === 'home') {
+ const req_trades = cleanCodes(req.query.trades);
+ groups = req_trades.length ? req_trades : HOME_TRADES;
+ } else {
+ // deal: GCs (as one 'A/B' group) + key trades
+ groups = [...GC_CLASSES, ...DEAL_TRADES];
+ }
+ // dedupe, preserve order
+ groups = [...new Set(groups)];
+
+ // one query per group, nearest-first, capped
+ const matches: Record<string, any[]> = {};
+ for (const code of groups) {
+ // $1 = status, $2 = [code] (used by the && classifications filter)
+ const params: unknown[] = ['Active', [code]];
+ // nearest-first ranking: exact zip, then city, then county (each a 0/1 DESC key)
+ const rank: string[] = [];
+ if (zip) { params.push(zip); rank.push(`(c.zip = $${params.length})::int DESC`); }
+ if (city) { params.push(city); rank.push(`(upper(c.city) = upper($${params.length}))::int DESC`); }
+ if (county) { params.push(county); rank.push(`(upper(c.county) = upper($${params.length}))::int DESC`); }
+ rank.push('c.business_name ASC');
+ // geo scope filter: keep to the market (county OR city OR zip) when any is given
+ const scope: string[] = [];
+ if (zip) { params.push(zip); scope.push(`c.zip = $${params.length}`); }
+ if (city) { params.push(city); scope.push(`upper(c.city) = upper($${params.length})`); }
+ if (county) { params.push(county); scope.push(`upper(c.county) = upper($${params.length})`); }
+ const scopeSql = scope.length ? `AND (${scope.join(' OR ')})` : '';
+ params.push(PER_GROUP_CAP);
+ const rows = await query<any>(
+ `SELECT ${CONTRACTOR_COLS}
+ FROM ca_contractors c
+ WHERE c.license_status = $1
+ AND c.classifications && $2::text[]
+ ${scopeSql}
+ ORDER BY ${rank.join(', ')}
+ LIMIT $${params.length}`,
+ params);
+ const titles = await titleMapFor(rows.rows);
+ matches[code] = rows.rows.map((r) => shapeContractor(r, titles));
+ }
+
+ res.json({
+ mode,
+ criteria: { zip, city, county, ...(mode === 'deal' ? { ctype } : {}), trades: groups },
+ cap_per_group: PER_GROUP_CAP,
+ matches,
+ });
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+
+ // ── one record ──────────────────────────────────────────────────────────────
+ app.get('/api/contractors/:license_no', async (req: Request, res: Response) => {
+ try {
+ const lic = String(req.params.license_no).trim().slice(0, 40);
+ if (!lic) return res.status(400).json({ error: 'bad license_no' });
+ const rows = await query<any>(
+ `SELECT ${CONTRACTOR_COLS} FROM ca_contractors c WHERE c.license_no = $1 LIMIT 1`, [lic]);
+ if (!rows.rows.length) return res.status(404).json({ error: 'contractor not found' });
+ const titles = await titleMapFor(rows.rows);
+ res.json(shapeContractor(rows.rows[0], titles));
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+}
diff --git a/src/server/index.ts b/src/server/index.ts
index 1268828..73e14aa 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -14,6 +14,7 @@ import { mountCommercial } from './commercial.ts';
import { mountSublease } from './sublease.ts';
import { mountParcels } from './parcels.ts';
import { mountDeals } from './deals.ts';
+import { mountContractors } from './contractors.ts';
const __dirname = dirname(fileURLToPath(import.meta.url));
const ROOT = join(__dirname, '..', '..');
@@ -526,6 +527,7 @@ mountCommercial(app);
mountSublease(app);
mountParcels(app);
mountDeals(app);
+mountContractors(app);
app.use(express.static(join(ROOT, 'public')));
← 1bdb0d9 ca_contractors: CSLB licensed-contractor registry schema (TK
·
back to Nationalrealestate
·
ca_contractors: date-everything provenance (source_as_of, cs 0ef7a6b →