← back to Nationalrealestate
Phase 5: CA DRE broker registry (FREE) + LA broker/firm intel surface
45be23a1fad34f9954bdcc47af4523859950b614 · 2026-07-22 08:08:52 -0700 · Steve
- CA DRE turned out to be FREE (secure.dre.ca.gov/datafile/CurrList.zip, refreshed
daily) — no purchase needed. New src/ingest/brokers/ca_dre.ts adapter (unzip + CSV,
Corporation->firm, Broker/Salesperson->broker w/ employing-firm linkage).
Loaded 382,944 brokers + 34,727 firms in 17s.
- index.ts: added city filter to /api/brokers + /api/firms (LA scoping).
- public/commercial-brokers.html: LA firms ranked by agent count (surfaces CBRE 1,213,
Newmark, etc. — the major commercial houses), click-to-expand roster, broker search.
Wired into commercial nav. Verified headless: 200 firms + roster + broker tab, 0 errors.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A public/commercial-brokers.htmlM public/commercial-feed.htmlM public/commercial.htmlA src/ingest/brokers/ca_dre.tsM src/ingest/brokers/engine.tsM src/server/index.ts
Diff
commit 45be23a1fad34f9954bdcc47af4523859950b614
Author: Steve <steve@designerwallcoverings.com>
Date: Wed Jul 22 08:08:52 2026 -0700
Phase 5: CA DRE broker registry (FREE) + LA broker/firm intel surface
- CA DRE turned out to be FREE (secure.dre.ca.gov/datafile/CurrList.zip, refreshed
daily) — no purchase needed. New src/ingest/brokers/ca_dre.ts adapter (unzip + CSV,
Corporation->firm, Broker/Salesperson->broker w/ employing-firm linkage).
Loaded 382,944 brokers + 34,727 firms in 17s.
- index.ts: added city filter to /api/brokers + /api/firms (LA scoping).
- public/commercial-brokers.html: LA firms ranked by agent count (surfaces CBRE 1,213,
Newmark, etc. — the major commercial houses), click-to-expand roster, broker search.
Wired into commercial nav. Verified headless: 200 firms + roster + broker tab, 0 errors.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
public/commercial-brokers.html | 100 +++++++++++++++++++++++++++++++++++++++++
public/commercial-feed.html | 2 +-
public/commercial.html | 2 +-
src/ingest/brokers/ca_dre.ts | 55 +++++++++++++++++++++++
src/ingest/brokers/engine.ts | 3 +-
src/server/index.ts | 2 +
6 files changed, 161 insertions(+), 3 deletions(-)
diff --git a/public/commercial-brokers.html b/public/commercial-brokers.html
new file mode 100644
index 0000000..2d4c379
--- /dev/null
+++ b/public/commercial-brokers.html
@@ -0,0 +1,100 @@
+<!doctype html>
+<html lang="en">
+<head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width, initial-scale=1">
+<link rel="icon" href="data:image/svg+xml,%3Csvg xmlns='http://www.w3.org/2000/svg' viewBox='0 0 16 16'%3E%3Crect width='16' height='16' rx='3' fill='%230a0d13'/%3E%3Ctext x='8' y='12' font-size='10' text-anchor='middle' fill='%23c8a24b'%3ECR%3C/text%3E%3C/svg%3E">
+<title>USRealEstate — LA Brokers & Firms</title>
+<style>
+ :root{--bg:#0a0d13;--panel:#11151d;--line:#222936;--fg:#e8ecf2;--muted:#8a93a3;--gold:#c8a24b;}
+ *{box-sizing:border-box;}
+ html,body{margin:0;background:var(--bg);color:var(--fg);font-family:-apple-system,BlinkMacSystemFont,'Segoe UI',Roboto,sans-serif;}
+ #topbar{display:flex;align-items:center;gap:14px;padding:10px 18px;background:rgba(17,21,29,.94);border-bottom:1px solid var(--line);position:sticky;top:0;z-index:20;backdrop-filter:blur(6px);}
+ .brand{font-size:16px;font-weight:700;white-space:nowrap;} .brand b{color:var(--gold);}
+ nav{display:flex;gap:14px;font-size:13px;} nav a{color:var(--muted);text-decoration:none;} nav a:hover,nav a.active{color:var(--gold);}
+ main{padding:16px 18px 60px;max-width:1200px;margin:0 auto;}
+ .lede{color:var(--muted);font-size:13px;line-height:1.5;margin:6px 0 14px;}
+ #controls{display:flex;flex-wrap:wrap;gap:10px;align-items:center;margin-bottom:14px;}
+ #tabs{display:inline-flex;border:1px solid var(--line);border-radius:8px;overflow:hidden;}
+ #tabs button{background:var(--panel);color:var(--muted);border:0;border-right:1px solid var(--line);padding:7px 14px;font-size:13px;cursor:pointer;}
+ #tabs button:last-child{border-right:0;} #tabs button.active{background:var(--gold);color:var(--bg);font-weight:700;}
+ input,select{background:var(--panel);color:var(--fg);border:1px solid var(--line);border-radius:8px;padding:7px 10px;font-size:13px;}
+ #count{font-size:12px;color:var(--muted);margin-left:auto;}
+ #grid{display:grid;grid-template-columns:repeat(auto-fill,minmax(280px,1fr));gap:10px;}
+ .card{background:var(--panel);border:1px solid var(--line);border-radius:10px;padding:13px;}
+ .card.firm{cursor:pointer;transition:border-color .12s;} .card.firm:hover{border-color:var(--gold);}
+ .card .n{font-size:15px;font-weight:600;line-height:1.25;}
+ .card .m{font-size:12px;color:var(--muted);margin-top:6px;display:flex;flex-wrap:wrap;gap:10px;}
+ .agents{color:var(--gold);font-weight:700;}
+ .lt{font-size:10px;letter-spacing:.05em;text-transform:uppercase;color:var(--gold);border:1px solid var(--line);border-radius:5px;padding:1px 6px;}
+ .roster{margin-top:9px;padding-top:9px;border-top:1px solid var(--line);font-size:12px;color:#c3ccd8;display:none;}
+ .roster div{padding:2px 0;}
+ #loading{padding:40px;text-align:center;color:var(--muted);}
+</style>
+</head>
+<body>
+<div id="topbar">
+ <div class="brand"><b>USRealEstate</b> — LA Brokers & Firms</div>
+ <nav>
+ <a href="/">Map</a><a href="/markets.html">Markets</a>
+ <a href="/commercial.html">Commercial</a>
+ <a href="/commercial-feed.html">Feed</a>
+ <a href="/commercial-brokers.html" class="active">Brokers</a>
+ </nav>
+</div>
+<main>
+ <p class="lede">Real estate firms & licensed brokers operating in the LA region, from the California DRE
+ current-licensee registry. Firms rank by licensed-agent count — the largest are the major commercial houses.</p>
+ <div id="controls">
+ <div id="tabs"><button data-t="firms" class="active">Firms</button><button data-t="brokers">Brokers</button></div>
+ <select id="city">
+ <option value="LOS ANGELES">Los Angeles</option><option value="BEVERLY HILLS">Beverly Hills</option>
+ <option value="SANTA MONICA">Santa Monica</option><option value="LONG BEACH">Long Beach</option>
+ <option value="BURBANK">Burbank</option><option value="GLENDALE">Glendale</option>
+ <option value="PASADENA">Pasadena</option><option value="">All CA</option>
+ </select>
+ <input id="q" placeholder="Search name / firm / license…" style="width:220px">
+ <span id="count"></span>
+ </div>
+ <div id="grid"></div>
+ <div id="loading">Loading…</div>
+</main>
+<script>
+const $=s=>document.querySelector(s);
+const state={tab:'firms',city:'LOS ANGELES',q:''};
+async function j(u){const r=await fetch(u);return r.json();}
+function params(){const p=new URLSearchParams({state:'CA',limit:200});if(state.city)p.set('city',state.city);if(state.q)p.set('q',state.q);return p;}
+async function loadFirms(){
+ const d=await j('/api/firms?'+params());
+ $('#count').textContent=d.total.toLocaleString()+' firms';
+ $('#grid').innerHTML=d.rows.map(f=>`<div class="card firm" data-id="${f.id}" data-name="${(f.name||'').replace(/"/g,'"')}">
+ <div class="n">${f.name||'—'}</div>
+ <div class="m"><span class="agents">${f.agent_count||0} agents</span><span>${f.hq_city||''}</span>${f.license_no?`<span>DRE ${f.license_no}</span>`:''}</div>
+ <div class="roster"></div></div>`).join('');
+ document.querySelectorAll('.card.firm').forEach(c=>c.onclick=()=>toggleRoster(c));
+}
+async function toggleRoster(c){
+ const r=c.querySelector('.roster');
+ if(r.style.display==='block'){r.style.display='none';return;}
+ r.style.display='block';
+ if(r.dataset.loaded){return;}
+ r.innerHTML='loading roster…';
+ const d=await j('/api/brokers?state=CA&limit=40&firm='+encodeURIComponent(c.dataset.name));
+ r.dataset.loaded='1';
+ r.innerHTML=d.rows.slice(0,30).map(b=>`<div>${b.name} <span style="color:var(--muted)">· ${b.license_type||''} · DRE ${b.license_no||''}</span></div>`).join('')||'<div style="color:var(--muted)">no roster rows</div>';
+}
+async function loadBrokers(){
+ const d=await j('/api/brokers?'+params());
+ $('#count').textContent=d.total.toLocaleString()+' brokers';
+ $('#grid').innerHTML=d.rows.map(b=>`<div class="card">
+ <div class="n">${b.name||'—'}</div>
+ <div class="m"><span class="lt">${b.license_type||''}</span><span>${b.firm_name||'—'}</span><span>${b.city||''}</span>${b.license_no?`<span>DRE ${b.license_no}</span>`:''}</div></div>`).join('');
+}
+async function load(){$('#loading').style.display='block';state.tab==='firms'?await loadFirms():await loadBrokers();$('#loading').style.display='none';}
+$('#tabs').querySelectorAll('button').forEach(b=>b.onclick=()=>{$('#tabs').querySelectorAll('button').forEach(x=>x.classList.remove('active'));b.classList.add('active');state.tab=b.dataset.t;load();});
+$('#city').onchange=()=>{state.city=$('#city').value;load();};
+$('#q').oninput=()=>{state.q=$('#q').value;clearTimeout(window._t);window._t=setTimeout(load,250);};
+load();
+</script>
+</body>
+</html>
diff --git a/public/commercial-feed.html b/public/commercial-feed.html
index 53b48dd..ea079d3 100644
--- a/public/commercial-feed.html
+++ b/public/commercial-feed.html
@@ -43,7 +43,7 @@
<a href="/">Map</a><a href="/markets.html">Markets</a>
<a href="/commercial.html">Commercial</a>
<a href="/commercial-feed.html" class="active">Feed</a>
- <a href="/brokers.html">Brokers</a>
+ <a href="/commercial-brokers.html">Brokers</a>
</nav>
</div>
<main>
diff --git a/public/commercial.html b/public/commercial.html
index d1b2959..22a8431 100644
--- a/public/commercial.html
+++ b/public/commercial.html
@@ -48,7 +48,7 @@
<a href="/">Map</a><a href="/markets.html">Markets</a>
<a href="/commercial.html" class="active">Commercial</a>
<a href="/commercial-feed.html">Feed</a>
- <a href="/brokers.html">Brokers</a>
+ <a href="/commercial-brokers.html">Brokers</a>
</nav>
</div>
<main>
diff --git a/src/ingest/brokers/ca_dre.ts b/src/ingest/brokers/ca_dre.ts
new file mode 100644
index 0000000..3eb5a7e
--- /dev/null
+++ b/src/ingest/brokers/ca_dre.ts
@@ -0,0 +1,55 @@
+/**
+ * CA — Department of Real Estate current licensee master file. FREE bulk download
+ * (secure.dre.ca.gov/datafile/CurrList.zip -> CurrList.csv, ~431k rows, refreshed
+ * daily). Columns are snake_case already (lic_type, lastname_primary, ...).
+ *
+ * lic_type='Corporation' -> brokerage firm (name lives in lastname_primary)
+ * lic_type='Broker'|'Salesperson' -> individual licensee; employing firm via
+ * related_lic_number + related_lastname_primary
+ * lic_type='Officer' -> a corporation's responsible-officer designation;
+ * skipped (that person also holds a Broker license
+ * listed on its own row) to avoid double-counting.
+ *
+ * Carries city (+ county_name) so the LA-region broker/firm surface can scope locally.
+ */
+import { execFileSync } from 'node:child_process';
+import { join } from 'node:path';
+import { download, DOWNLOADS } from '../run.ts';
+import { parseCsvObjects } from './csv.ts';
+import type { BrokerRow, FetchedFile, StateAdapter } from './types.ts';
+
+const URL = 'https://secure.dre.ca.gov/datafile/CurrList.zip';
+
+export const caDre: StateAdapter = {
+ state: 'CA',
+ source: 'ca_dre',
+ url: URL,
+ async fetch(): Promise<FetchedFile[]> {
+ const { path } = await download(URL, 'ca_dre_CurrList.zip');
+ execFileSync('unzip', ['-o', '-q', path, '-d', DOWNLOADS]);
+ return [{ path: join(DOWNLOADS, 'CurrList.csv'), sha256: '' }];
+ },
+ *parse(files: FetchedFile[]): Generator<BrokerRow> {
+ for (const o of parseCsvObjects(files[0].path)) {
+ if (!o.lic_number) continue;
+ const type = o.lic_type || '';
+ const city = o.city || null;
+ const st = (o.state || '').toUpperCase() || null;
+ const status = o.lic_status || null;
+ if (type === 'Corporation') {
+ const name = o.lastname_primary; // company name lives in the primary-name column
+ if (!name) continue;
+ yield { kind: 'firm', name, license_no: o.lic_number, license_type: type, license_status: status, city, state_code: st };
+ } else if (type === 'Broker' || type === 'Salesperson') {
+ const name = [o.firstname_secondary, o.lastname_primary].filter(Boolean).join(' ');
+ if (!name) continue;
+ yield {
+ kind: 'broker', name, license_no: o.lic_number, license_type: type, license_status: status,
+ firm_name: o.related_lastname_primary || null, firm_license_no: o.related_lic_number || null,
+ city, state_code: st,
+ };
+ }
+ // Officer / Corporation-affiliate / other rows skipped
+ }
+ },
+};
diff --git a/src/ingest/brokers/engine.ts b/src/ingest/brokers/engine.ts
index 0921f71..62a3c8b 100644
--- a/src/ingest/brokers/engine.ts
+++ b/src/ingest/brokers/engine.ts
@@ -19,8 +19,9 @@ import { coDre } from './co_dre.ts';
import { ctDcp } from './ct_dcp.ts';
import { ilIdfpr } from './il_idfpr.ts';
import { deDpr } from './de_dpr.ts';
+import { caDre } from './ca_dre.ts';
-const ADAPTERS: StateAdapter[] = [txTrec, flDbpr, nyDos, coDre, ctDcp, ilIdfpr, deDpr];
+const ADAPTERS: StateAdapter[] = [txTrec, flDbpr, nyDos, coDre, ctDcp, ilIdfpr, deDpr, caDre];
const BATCH = 5000;
export function normalizeName(name: string): string {
diff --git a/src/server/index.ts b/src/server/index.ts
index 6f031d9..418d207 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -198,6 +198,7 @@ app.get('/api/brokers', async (req, res) => {
const conds: string[] = [];
const params: unknown[] = [];
if (req.query.state) { params.push(String(req.query.state).toUpperCase()); conds.push(`b.license_state = $${params.length}`); }
+ if (req.query.city) { params.push(String(req.query.city).toUpperCase()); conds.push(`upper(b.city) = $${params.length}`); }
if (req.query.q) { params.push('%' + String(req.query.q) + '%'); conds.push(`(b.name ILIKE $${params.length} OR f.name ILIKE $${params.length} OR b.license_no ILIKE $${params.length})`); }
if (req.query.firm) { params.push('%' + String(req.query.firm) + '%'); conds.push(`f.name ILIKE $${params.length}`); }
const where = conds.length ? 'WHERE ' + conds.join(' AND ') : '';
@@ -227,6 +228,7 @@ app.get('/api/firms', async (req, res) => {
const conds: string[] = [];
const params: unknown[] = [];
if (req.query.state) { params.push(String(req.query.state).toUpperCase()); conds.push(`license_state = $${params.length}`); }
+ if (req.query.city) { params.push(String(req.query.city).toUpperCase()); conds.push(`upper(hq_city) = $${params.length}`); }
if (req.query.q) { params.push('%' + String(req.query.q) + '%'); conds.push(`(name ILIKE $${params.length} OR license_no ILIKE $${params.length})`); }
const where = conds.length ? 'WHERE ' + conds.join(' AND ') : '';
params.push(limit, offset);
← 04a19e4 Phase 4: generated commercial news briefs (local Ollama, $0)
·
back to Nationalrealestate
·
Deepen LA: commercial property-detail page unifying facts + 948bf60 →