← back to Nationalrealestate
M-B1 national broker registry: 5 open-state adapters (TX/FL/NY/CO/CT), 926,973 brokers + 141,591 firms, /api/brokers|firms|broker-stats, brokers.html directory, BROKER-COVERAGE.md matrix
1af53fac629a2e2ff0ded2276a98734cab1a61ad · 2026-07-21 13:21:47 -0700 · Steve Abrams
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A BROKER-COVERAGE.mdM SOURCES.mdM package.jsonA public/brokers.htmlA src/ingest/brokers/co_dre.tsA src/ingest/brokers/csv.tsA src/ingest/brokers/ct_dcp.tsA src/ingest/brokers/engine.tsA src/ingest/brokers/fl_dbpr.tsA src/ingest/brokers/ny_dos.tsA src/ingest/brokers/tx_trec.tsA src/ingest/brokers/types.tsM src/server/index.ts
Diff
commit 1af53fac629a2e2ff0ded2276a98734cab1a61ad
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 21 13:21:47 2026 -0700
M-B1 national broker registry: 5 open-state adapters (TX/FL/NY/CO/CT), 926,973 brokers + 141,591 firms, /api/brokers|firms|broker-stats, brokers.html directory, BROKER-COVERAGE.md matrix
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
BROKER-COVERAGE.md | 35 ++++++++
SOURCES.md | 10 +++
package.json | 1 +
public/brokers.html | 150 +++++++++++++++++++++++++++++++++
src/ingest/brokers/co_dre.ts | 47 +++++++++++
src/ingest/brokers/csv.ts | 51 +++++++++++
src/ingest/brokers/ct_dcp.ts | 34 ++++++++
src/ingest/brokers/engine.ts | 191 ++++++++++++++++++++++++++++++++++++++++++
src/ingest/brokers/fl_dbpr.ts | 63 ++++++++++++++
src/ingest/brokers/ny_dos.ts | 48 +++++++++++
src/ingest/brokers/tx_trec.ts | 44 ++++++++++
src/ingest/brokers/types.ts | 36 ++++++++
src/server/index.ts | 78 +++++++++++++++++
13 files changed, 788 insertions(+)
diff --git a/BROKER-COVERAGE.md b/BROKER-COVERAGE.md
new file mode 100644
index 0000000..a6e162b
--- /dev/null
+++ b/BROKER-COVERAGE.md
@@ -0,0 +1,35 @@
+# Broker registry — state coverage matrix (M-B1)
+
+Probed 2026-07-21, curl-only (DTD verdict: OPEN-FILES-ONLY this pass — no headless
+scraping of gated lookup portals, no paid subscriptions). Row estimates are from the
+live probe (Socrata `$select=count(*)` or file-size/row sampling). Statuses:
+
+- **OPEN-INGESTED** — free bulk download, adapter shipped + loaded this pass
+- **OPEN-TODO** — free bulk download exists, adapter deferred to a later pass
+- **GATED-portal** — per-name lookup portal / form-post / session only, no bulk file
+- **GATED-paid** — bulk roster exists but costs money
+- **NOT-FOUND** — no bulk mechanism located this pass
+
+| State | Source | URL | Status | Rows (est) | Notes |
+|---|---|---|---|---|---|
+| TX | TREC via data.texas.gov Socrata `s7ft-44qi` | https://data.texas.gov/api/views/s7ft-44qi/rows.csv?accessType=DOWNLOAD | OPEN-INGESTED | 321,788 | Refreshed daily. 18,945 Broker Company / 42,417 Broker Individual / 260,426 Sales Agent. Firm linkage via `related_license_*`. No city column (county only). trec.texas.gov "High Value Data Sets" page itself no longer links raw files — the Socrata dataset IS the bulk feed. |
+| FL | DBPR free extracts | https://www2.myfloridalicense.com/sto/file_download/extracts/RE_rgn1.csv (…rgn1–14) | OPEN-INGESTED | ~380k lines across 14 regional files | Refreshed daily (Last-Modified 07-20/07-21). Headerless DBPR standard layout, latin-1. Directory listing disabled — filenames recovered via Wayback CDX, all 14 live-verified. Employer name + numeric license in cols 21/22 → firm linkage. `RealEstateCorpLicense.csv` (9MB) + `re_salesperson.csv` (180MB, history) also live but redundant with the rgn files. |
+| NY | DOS via data.ny.gov Socrata `yg7h-zjbf` | https://data.ny.gov/api/views/yg7h-zjbf/rows.csv?accessType=DOWNLOAD | OPEN-INGESTED | 147,941 | "Active Real Estate Salespersons and Brokers", refreshed daily. Active-only. Firm = `business_name` (no firm license # in-row → firms dedupe by normalized name). 77,632 salespersons, 16,108 assoc brokers, 9,234 individual brokers + entity types. |
+| CO | DRE via data.colorado.gov Socrata `4zse-6bnw` | https://data.colorado.gov/api/views/4zse-6bnw/rows.csv?accessType=DOWNLOAD | OPEN-INGESTED | 108,811 total; ~49k RE brokers + 16.7k RE companies kept | "Licensed Real Estate Professionals" mixes in MLOs (21,341), appraisers, HOAs — adapter filters to Real Estate Broker/Company types, license_type preserved. No employer linkage columns. |
+| CT | DCP via data.ct.gov Socrata `6tja-6vdt` | https://data.ct.gov/api/views/6tja-6vdt/rows.csv?accessType=DOWNLOAD | OPEN-INGESTED | 18,281 | "Real Estate Salespersons with Associated Broker" — active affiliation pairs (RES.# ↔ REB.#), broker credential feeds firm table. Sister datasets (`eqtn-rppv` status, team members `5vfu-8yxm`) available for later passes. |
+| VA | DPOR | https://www.dpor.virginia.gov/LicenseLookup/ | GATED-portal | — | Lookup portal only; no bulk file linked anywhere on the rebuilt Drupal site (grepped nav + guessed download paths → 404). DPOR bulk data goes through a records-request/order process. |
+| AZ | ADRE public database | https://services.azre.gov/PdbWeb/ | GATED-portal | — | "Public Database" exists but is a JS SPA (PdbWeb) — exports are generated client-side (jszip), no direct bulk CSV URL. Candidate for a later pass via its underlying JSON API (needs endpoint mapping, out of scope under open-files-only). |
+| NC | NCREC | https://www.ncrec.gov/Home/Databases | GATED-portal | — | 403 bot-block on direct fetch; NCREC licensee data is by data-request form. |
+| GA | GREC | https://grec.state.ga.us | GATED-paid | — | GREC sells licensee rosters (paid data request); no free bulk file. Probe of licensee page → 404/portal. |
+| WA | DOL professions | https://professions.dol.wa.gov/s/license-lookup | GATED-portal | — | Lookup portal only. data.wa.gov has license COUNTS (`a2n7-rij5`) but no licensee-level RE roster. Bulk = public-records request. |
+| NJ | Division of Consumer Affairs | https://newjersey.mylicense.com/verification/ | GATED-portal | — | mylicense.com verification portal (form-post + session), no bulk export. |
+| PA | PALS | https://www.pals.pa.gov/ | GATED-portal | — | PALS lookup portal; bulk licensee lists are paid data requests through the Bureau. |
+| OH | eLicense / data.ohio.gov | https://elicense.ohio.gov | NOT-FOUND | — | No RE licensee dataset on any Socrata domain (catalog search empty); eLicense is a Salesforce portal. Ohio DOC offers roster downloads somewhere behind eLicense login — recheck next pass. |
+| GA/others via NMLS | — | — | — | NMLS covers mortgage (MLO), not real-estate brokers — not applicable. |
+
+## Next-pass candidates
+
+1. **AZ PdbWeb JSON API** — map the SPA's fetch endpoints (likely open JSON, just undocumented).
+2. **OH eLicense "download data" export** — reported to exist behind a public form.
+3. **CT broker-side roster** (`eqtn-rppv` + DCP full credential extract) to add CT firm HQ cities.
+4. Remaining Socrata domains sweep (`api.us.socrata.com/api/catalog/v1?q=salesperson`, `q=licensee`) for smaller states.
diff --git a/SOURCES.md b/SOURCES.md
index b8e02bf..aa68dbb 100644
--- a/SOURCES.md
+++ b/SOURCES.md
@@ -76,3 +76,13 @@ Metrics ingested per row: `median_sale_price`, `median_sale_price_yoy`, `homes_s
## Derived metrics — source `derived` (M3)
- `affordability` = latest zhvi / ACS median_hh_income (annual ACS joins to any zhvi month); `rent_yield` = (zori×12)/zhvi at latest common period; `zhvi_yoy` = zhvi latest vs 12 months prior (fraction). period = the zhvi period used. Counts on first run: affordability 3,063 · rent_yield 2,047 · zhvi_yoy 3,918.
+
+## Broker registry — state licensing rosters (M-B1, probed 2026-07-21)
+
+5 states genuinely-open + ingested this pass: **TX** (data.texas.gov Socrata `s7ft-44qi`,
+321,788 rows), **FL** (DBPR free extracts `RE_rgn1..14.csv`, filenames recovered via
+Wayback CDX since directory listing is off), **NY** (data.ny.gov `yg7h-zjbf`, 147,941
+active), **CO** (data.colorado.gov `4zse-6bnw`, RE-filtered from 108,811), **CT**
+(data.ct.gov `6tja-6vdt`, 18,281 affiliation pairs). Socrata bulk endpoint pattern:
+`https://<domain>/api/views/<id>/rows.csv?accessType=DOWNLOAD`. Full per-state matrix
+incl. 8 gated/blocked states → `BROKER-COVERAGE.md`. Adapters: `src/ingest/brokers/`.
diff --git a/package.json b/package.json
index a5dbe7a..e43c84d 100644
--- a/package.json
+++ b/package.json
@@ -12,6 +12,7 @@
"ingest:redfin": "tsx src/ingest/redfin_tracker.ts",
"ingest:acs": "tsx src/ingest/census_acs.ts",
"ingest:fhfa": "tsx src/ingest/fhfa_hpi.ts",
+ "ingest:brokers": "tsx src/ingest/brokers/engine.ts",
"derive": "tsx src/ingest/derive_metrics.ts",
"score": "tsx src/score/opportunity.ts",
"refresh": "tsx src/jobs/refresh_all.ts",
diff --git a/public/brokers.html b/public/brokers.html
new file mode 100644
index 0000000..f8aedde
--- /dev/null
+++ b/public/brokers.html
@@ -0,0 +1,150 @@
+<!doctype html>
+<html lang="en">
+<head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width, initial-scale=1">
+<title>USRealEstate — Broker Registry</title>
+<link rel="stylesheet" href="/crcp-grid.css">
+<style>
+ :root{--bg:#0a0d13;--panel:#11151d;--line:#222936;--fg:#e8ecf2;--muted:#8a93a3;--gold:#c8a24b;
+ --cg-card:#11151d;--cg-line:#222936;--cg-ink:#e8ecf2;--cg-mut:#8a93a3;
+ --cg-acc:#c8a24b;--cg-accbg:rgba(200,162,75,.12);--cg-railbg:#0a0d13;--cg-topbg:rgba(17,21,29,.94);}
+ *{box-sizing:border-box;}
+ html,body{margin:0;background:var(--bg);color:var(--fg);
+ font:14px/1.5 -apple-system,Segoe UI,Roboto,sans-serif;}
+ #topbar{position:sticky;top:0;z-index:1000;display:flex;align-items:center;gap:18px;flex-wrap:wrap;
+ padding:10px 18px;background:rgba(17,21,29,.94);border-bottom:1px solid var(--line);backdrop-filter:blur(6px);}
+ #topbar .brand{font-size:16px;font-weight:700;white-space:nowrap;}
+ #topbar .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);}
+ #coverage{display:flex;gap:8px;align-items:center;flex-wrap:wrap;margin-left:auto;font-size:12px;}
+ #coverage .cov-lbl{color:var(--muted);}
+ .st-chip{border:1px solid var(--line);border-radius:8px;background:var(--panel);color:var(--muted);
+ padding:4px 10px;cursor:pointer;white-space:nowrap;font-size:12px;}
+ .st-chip b{color:var(--fg);} .st-chip .n{color:var(--gold);font-weight:700;margin-left:5px;}
+ .st-chip.active{background:var(--gold);color:var(--bg);} .st-chip.active b,.st-chip.active .n{color:var(--bg);}
+ #srvbar{display:flex;gap:10px;align-items:center;flex-wrap:wrap;padding:10px 18px;border-bottom:1px solid var(--line);
+ background:var(--panel);font-size:13px;}
+ #srvbar input{background:var(--bg);border:1px solid var(--line);color:var(--fg);border-radius:8px;
+ padding:7px 10px;font-size:13px;min-width:220px;}
+ #srvbar input:focus{outline:none;border-color:var(--gold);}
+ #pager{margin-left:auto;display:flex;gap:8px;align-items:center;color:var(--muted);}
+ #pager button{background:var(--panel);border:1px solid var(--line);color:var(--fg);border-radius:8px;
+ padding:6px 12px;cursor:pointer;font-size:12px;}
+ #pager button:disabled{opacity:.35;cursor:default;}
+ #pager button:not(:disabled):hover{border-color:var(--gold);color:var(--gold);}
+ #loading{padding:60px;text-align:center;color:var(--muted);}
+ .lic{font-family:ui-monospace,Menlo,monospace;font-size:12px;}
+</style>
+</head>
+<body>
+<div id="topbar">
+ <div class="brand"><b>USRealEstate</b> — Broker Registry</div>
+ <nav>
+ <a href="/">Map</a>
+ <a href="/markets.html">Markets</a>
+ <a href="/brokers.html" class="active">Brokers</a>
+ <a href="/watchlist.html">Watchlist</a>
+ <a href="/admin.html">Admin</a>
+ </nav>
+ <span id="coverage"><span class="cov-lbl">coverage:</span><span style="color:var(--muted)">loading…</span></span>
+</div>
+<div id="srvbar">
+ <input type="text" id="srvQ" placeholder="search name / firm / license # (server-side)">
+ <input type="text" id="srvFirm" placeholder="firm filter" style="min-width:160px">
+ <span id="pager">
+ <button id="pgPrev">‹ Prev</button>
+ <span id="pgInfo">—</span>
+ <button id="pgNext">Next ›</button>
+ </span>
+</div>
+<div class="cg-shell">
+ <div class="cg-rail" id="cgRail"></div>
+ <div class="cg-main" id="cgMain"><div id="loading">loading brokers…</div></div>
+</div>
+
+<script src="/crcp-grid.js"></script>
+<script src="/col-resize.js"></script>
+<script>
+const PAGE = 500;
+const S = { state: localStorage.getItem('usreBrk.state') || '', q: '', firm: '', offset: 0, total: 0 };
+
+const FIELDS = [
+ { k: 'license_no', l: 'License #', def: 1, col: 1 },
+ { k: 'license_type', l: 'Type', def: 1, col: 1 },
+ { k: 'license_status', l: 'Status', def: 1, col: 1 },
+ { k: 'firm_name', l: 'Firm', def: 1, col: 1 },
+ { k: 'city', l: 'City', def: 1, col: 1 },
+ { k: 'license_state', l: 'State', def: 1, col: 1 },
+ { k: 'source', l: 'Source', def: 0, col: 1 },
+ { k: 'name', l: 'Name', def: 0, col: 0 }, // title-column sort target only
+];
+
+let grid = null;
+
+async function loadStats() {
+ const d = await fetch('/api/broker-stats').then(r => r.json());
+ const box = document.getElementById('coverage');
+ box.innerHTML = '<span class="cov-lbl">coverage:</span>' +
+ `<span class="st-chip${S.state === '' ? ' active' : ''}" data-st=""><b>ALL</b><span class="n">${(d.total_brokers || 0).toLocaleString()}</span></span>` +
+ (d.states || []).map(s =>
+ `<span class="st-chip${S.state === s.state ? ' active' : ''}" data-st="${s.state}" title="${s.firms.toLocaleString()} firms"><b>${s.state}</b><span class="n">${s.brokers.toLocaleString()}</span></span>`
+ ).join('');
+ box.addEventListener('click', e => {
+ const c = e.target.closest('.st-chip'); if (!c) return;
+ S.state = c.dataset.st; S.offset = 0;
+ localStorage.setItem('usreBrk.state', S.state);
+ box.querySelectorAll('.st-chip').forEach(x => x.classList.toggle('active', x.dataset.st === S.state));
+ loadPage();
+ }, { once: false });
+}
+
+async function loadPage() {
+ const p = new URLSearchParams();
+ if (S.state) p.set('state', S.state);
+ if (S.q) p.set('q', S.q);
+ if (S.firm) p.set('firm', S.firm);
+ p.set('limit', PAGE); p.set('offset', S.offset);
+ const d = await fetch('/api/brokers?' + p).then(r => r.json());
+ S.total = d.total || 0;
+ const rows = d.rows || [];
+ const loading = document.getElementById('loading');
+ if (loading) loading.remove();
+ const pg = Math.floor(S.offset / PAGE) + 1, pgs = Math.max(Math.ceil(S.total / PAGE), 1);
+ document.getElementById('pgInfo').textContent = `${S.total.toLocaleString()} brokers · page ${pg} / ${pgs.toLocaleString()}`;
+ document.getElementById('pgPrev').disabled = S.offset <= 0;
+ document.getElementById('pgNext').disabled = S.offset + PAGE >= S.total;
+ if (grid) { grid.setData(rows); return; }
+ grid = CrcpGrid.mount({
+ data: rows,
+ fields: FIELDS,
+ mount: '#cgMain',
+ rail: '#cgRail',
+ storageKey: 'usreBrk',
+ cols: 4,
+ view: 'list',
+ title: r => ({ name: r.name, sub: (r.firm_name ? r.firm_name + ' · ' : '') + r.license_state }),
+ titleCol: 'Broker',
+ titleSortK: 'name',
+ search: r => `${r.name} ${r.firm_name || ''} ${r.license_no || ''} ${r.city || ''}`,
+ sort: { k: 'name', dir: 1 },
+ facets: [
+ { k: 'license_type', l: 'License Type', type: 'chip', max: 20 },
+ { k: 'license_status', l: 'Status', type: 'chip', max: 12 },
+ { k: 'license_state', l: 'State', type: 'chip', max: 10 },
+ ],
+ });
+}
+
+const deb = (fn, ms) => { let t; return (...a) => { clearTimeout(t); t = setTimeout(() => fn(...a), ms); }; };
+document.getElementById('srvQ').addEventListener('input', deb(e => { S.q = e.target.value.trim(); S.offset = 0; loadPage(); }, 350));
+document.getElementById('srvFirm').addEventListener('input', deb(e => { S.firm = e.target.value.trim(); S.offset = 0; loadPage(); }, 350));
+document.getElementById('pgPrev').addEventListener('click', () => { S.offset = Math.max(S.offset - PAGE, 0); loadPage(); });
+document.getElementById('pgNext').addEventListener('click', () => { S.offset += PAGE; loadPage(); });
+
+loadStats();
+loadPage();
+</script>
+</body>
+</html>
diff --git a/src/ingest/brokers/co_dre.ts b/src/ingest/brokers/co_dre.ts
new file mode 100644
index 0000000..1ae9424
--- /dev/null
+++ b/src/ingest/brokers/co_dre.ts
@@ -0,0 +1,47 @@
+/**
+ * CO — DORA/DRE "Licensed Real Estate Professionals in Colorado" on
+ * data.colorado.gov (4zse-6bnw, refreshed daily; 108,811 rows probed 2026-07-21
+ * — but that includes MLOs, appraisers and HOAs). We keep only the real-estate
+ * broker license types (~49k individuals) + Real Estate Company rows (~16.7k firms).
+ * No employer linkage columns in this dataset → individual rows carry no firm.
+ */
+import { download } from '../run.ts';
+import { parseCsvObjects } from './csv.ts';
+import type { BrokerRow, FetchedFile, StateAdapter } from './types.ts';
+
+const URL = 'https://data.colorado.gov/api/views/4zse-6bnw/rows.csv?accessType=DOWNLOAD';
+
+export const coDre: StateAdapter = {
+ state: 'CO',
+ source: 'co_dre',
+ url: URL,
+ async fetch() {
+ return [await download(URL, 'co_dre_licensed_re_professionals.csv')];
+ },
+ *parse(files: FetchedFile[]): Generator<BrokerRow> {
+ for (const o of parseCsvObjects(files[0].path)) {
+ const type = o.licensetype || '';
+ if (!o.licensenumber) continue;
+ const city = o.city || null;
+ const st = (o.state || '').toUpperCase() || null;
+ const status = o.licensestatus || null;
+ if (type.startsWith('Real Estate Company')) {
+ // company rows carry the name in `entityname`, not the person-name columns
+ const name = o.entityname || o.lastname || [o.firstname, o.lastname].filter(Boolean).join(' ');
+ if (!name) continue;
+ yield {
+ kind: 'firm', name, license_no: o.licensenumber,
+ license_type: type, license_status: status, city, state_code: st,
+ };
+ } else if (/Real Estate Broker/.test(type)) {
+ const name = [o.firstname, o.middlename, o.lastname].filter(Boolean).join(' ');
+ if (!name) continue;
+ yield {
+ kind: 'broker', name, license_no: o.licensenumber,
+ license_type: type, license_status: status, city, state_code: st,
+ };
+ }
+ // MLO / appraiser / HOA / subdivision rows skipped — not the broker registry
+ }
+ },
+};
diff --git a/src/ingest/brokers/csv.ts b/src/ingest/brokers/csv.ts
new file mode 100644
index 0000000..ae2a07c
--- /dev/null
+++ b/src/ingest/brokers/csv.ts
@@ -0,0 +1,51 @@
+/**
+ * Minimal RFC-4180 CSV parser (quoted fields, embedded commas/newlines/"" escapes).
+ * Whole-file in memory — largest M-B1 file is ~65MB, fine on a 32GB workstation.
+ */
+import { readFileSync } from 'node:fs';
+
+export function parseCsv(text: string): string[][] {
+ const rows: string[][] = [];
+ let row: string[] = [];
+ let field = '';
+ let inQ = false;
+ for (let i = 0; i < text.length; i++) {
+ const c = text[i];
+ if (inQ) {
+ if (c === '"') {
+ if (text[i + 1] === '"') { field += '"'; i++; }
+ else inQ = false;
+ } else field += c;
+ } else if (c === '"') inQ = true;
+ else if (c === ',') { row.push(field); field = ''; }
+ else if (c === '\n') { row.push(field); field = ''; rows.push(row); row = []; }
+ else if (c !== '\r') field += c;
+ }
+ if (field !== '' || row.length) { row.push(field); rows.push(row); }
+ return rows;
+}
+
+export function parseCsvFile(path: string, encoding: BufferEncoding = 'utf8'): string[][] {
+ return parseCsv(readFileSync(path, { encoding }));
+}
+
+/** Header display names -> snake_case keys ("License Type" -> license_type). */
+export function headerKeys(header: string[]): string[] {
+ return header.map(h => h.toLowerCase().replace(/[^a-z0-9]+/g, '_').replace(/^_+|_+$/g, ''));
+}
+
+/** Parse a headered CSV file into objects keyed by snake_cased header names. */
+export function parseCsvObjects(path: string, encoding: BufferEncoding = 'utf8'): Record<string, string>[] {
+ const rows = parseCsvFile(path, encoding);
+ if (!rows.length) return [];
+ const keys = headerKeys(rows[0]);
+ const out: Record<string, string>[] = [];
+ for (let i = 1; i < rows.length; i++) {
+ const r = rows[i];
+ if (r.length === 1 && r[0] === '') continue;
+ const o: Record<string, string> = {};
+ for (let j = 0; j < keys.length; j++) o[keys[j]] = (r[j] ?? '').trim();
+ out.push(o);
+ }
+ return out;
+}
diff --git a/src/ingest/brokers/ct_dcp.ts b/src/ingest/brokers/ct_dcp.ts
new file mode 100644
index 0000000..a0188ff
--- /dev/null
+++ b/src/ingest/brokers/ct_dcp.ts
@@ -0,0 +1,34 @@
+/**
+ * CT — DCP "Real Estate Salespersons with Associated Broker" on data.ct.gov
+ * (6tja-6vdt, refreshed daily; 18,281 rows probed 2026-07-21). Pairs each active
+ * salesperson credential (RES.#) with the sponsoring broker credential (REB.#) —
+ * the broker side is usually the brokerage entity, so it feeds the firm table.
+ * No status/city columns; dataset is the active-affiliation roster.
+ */
+import { download } from '../run.ts';
+import { parseCsvObjects } from './csv.ts';
+import type { BrokerRow, FetchedFile, StateAdapter } from './types.ts';
+
+const URL = 'https://data.ct.gov/api/views/6tja-6vdt/rows.csv?accessType=DOWNLOAD';
+
+export const ctDcp: StateAdapter = {
+ state: 'CT',
+ source: 'ct_dcp',
+ url: URL,
+ async fetch() {
+ return [await download(URL, 'ct_dcp_salespersons_with_broker.csv')];
+ },
+ *parse(files: FetchedFile[]): Generator<BrokerRow> {
+ for (const o of parseCsvObjects(files[0].path)) {
+ const name = o.real_estate_salesperson;
+ if (!name || !o.salespersoncredential) continue;
+ yield {
+ kind: 'broker', name, license_no: o.salespersoncredential,
+ license_type: 'Salesperson', license_status: null,
+ firm_name: o.real_estate_broker || null,
+ firm_license_no: o.real_estate_broker ? o.broker_credential || null : null,
+ state_code: 'CT',
+ };
+ }
+ },
+};
diff --git a/src/ingest/brokers/engine.ts b/src/ingest/brokers/engine.ts
new file mode 100644
index 0000000..38fff8d
--- /dev/null
+++ b/src/ingest/brokers/engine.ts
@@ -0,0 +1,191 @@
+/**
+ * M-B1 broker-registry ingest engine. Runs one StateAdapter (or all) through the
+ * shared harness: openRun → fetch files → parse → upsert firms first (keyed on
+ * (source, source_id) where source_id = the firm's license number when the roster
+ * prints one, else 'n:'+normalized name) → upsert brokers with firm_id linkage →
+ * refresh firm.agent_count → closeRun. region_id = the state region ('state:XX');
+ * county-level geocoding is a later pass. Fail-loud: 0 parsed brokers = failed run.
+ *
+ * npm run ingest:brokers -- tx # one state
+ * npm run ingest:brokers -- all # every registered adapter
+ */
+import { pool, query } from '../../../db/pool.ts';
+import { openRun, closeRun } from '../run.ts';
+import type { BrokerRow, StateAdapter } from './types.ts';
+import { txTrec } from './tx_trec.ts';
+import { flDbpr } from './fl_dbpr.ts';
+import { nyDos } from './ny_dos.ts';
+import { coDre } from './co_dre.ts';
+import { ctDcp } from './ct_dcp.ts';
+
+const ADAPTERS: StateAdapter[] = [txTrec, flDbpr, nyDos, coDre, ctDcp];
+const BATCH = 5000;
+
+export function normalizeName(name: string): string {
+ return name.toUpperCase().replace(/[^A-Z0-9]+/g, ' ').trim()
+ .replace(/\s+(LLC|L L C|INC|CORP|CO|LTD|LP|LLP|PLLC|PC|PA)$/,'').trim();
+}
+
+interface FirmEntity {
+ sourceId: string;
+ name: string;
+ license_no: string | null;
+ license_status: string | null;
+ city: string | null;
+ state_code: string | null;
+ fromRow: boolean; // true = real firm row from the roster, not just an employer reference
+}
+
+async function stateRegionId(state: string): Promise<number | null> {
+ const r = await query<{ id: number }>(`SELECT id FROM region WHERE canonical_key = $1`, ['state:' + state]);
+ return r.rows[0]?.id ?? null;
+}
+
+function firmKey(licenseNo: string | null | undefined, name: string): string {
+ return licenseNo && licenseNo.trim() ? licenseNo.trim() : 'n:' + normalizeName(name);
+}
+
+async function runAdapter(a: StateAdapter): Promise<void> {
+ const t0 = Date.now();
+ console.log(`[${a.source}] fetching ${a.url}`);
+ const runId = await openRun(a.source, a.url);
+ try {
+ const files = await a.fetch();
+ console.log(`[${a.source}] ${files.length} file(s) downloaded`);
+ const regionId = await stateRegionId(a.state);
+
+ // ---- pass 1: collect firm entities + broker rows (deduped by source_id) ----
+ const firms = new Map<string, FirmEntity>();
+ const brokers = new Map<string, BrokerRow>();
+ let skipped = 0;
+ for (const row of a.parse(files)) {
+ if (row.kind === 'firm') {
+ const key = firmKey(row.firm_license_no ?? row.license_no, row.name);
+ const prev = firms.get(key);
+ if (!prev || !prev.fromRow) {
+ firms.set(key, {
+ sourceId: key, name: row.name, license_no: row.license_no,
+ license_status: row.license_status ?? null,
+ city: row.city ?? null, state_code: row.state_code ?? null, fromRow: true,
+ });
+ }
+ } else {
+ if (!row.license_no) { skipped++; continue; }
+ if (brokers.has(row.license_no)) skipped++;
+ brokers.set(row.license_no, row);
+ if (row.firm_name) {
+ const key = firmKey(row.firm_license_no, row.firm_name);
+ if (!firms.has(key)) {
+ firms.set(key, {
+ sourceId: key, name: row.firm_name, license_no: row.firm_license_no ?? null,
+ license_status: null, city: null, state_code: a.state, fromRow: false,
+ });
+ }
+ }
+ }
+ }
+ if (!brokers.size) throw new Error(`0 broker rows parsed for ${a.source} — layout drift?`);
+ console.log(`[${a.source}] parsed ${brokers.size.toLocaleString()} brokers, ${firms.size.toLocaleString()} firms (${skipped} skipped)`);
+
+ // ---- pass 2: upsert firms, build source_id -> id + normalized name -> id maps ----
+ const firmIdByKey = new Map<string, number>();
+ const firmIdByName = new Map<string, number>();
+ const firmList = [...firms.values()];
+ for (let i = 0; i < firmList.length; i += BATCH) {
+ const b = firmList.slice(i, i + BATCH);
+ const r = await query<{ id: number; source_id: string; normalized_name: string }>(
+ `INSERT INTO firm (name, normalized_name, license_no, license_state, hq_city, hq_state, source, source_id, region_id)
+ SELECT * FROM UNNEST($1::text[],$2::text[],$3::text[],$4::text[],$5::text[],$6::text[],$7::text[],$8::text[],$9::int[])
+ ON CONFLICT (source, source_id) DO UPDATE SET
+ name = EXCLUDED.name, normalized_name = EXCLUDED.normalized_name,
+ license_no = COALESCE(EXCLUDED.license_no, firm.license_no),
+ hq_city = COALESCE(EXCLUDED.hq_city, firm.hq_city),
+ hq_state = COALESCE(EXCLUDED.hq_state, firm.hq_state),
+ region_id = COALESCE(EXCLUDED.region_id, firm.region_id)
+ RETURNING id, source_id, normalized_name`,
+ [
+ b.map(f => f.name), b.map(f => normalizeName(f.name)), b.map(f => f.license_no),
+ b.map(() => a.state), b.map(f => f.city), b.map(f => f.state_code),
+ b.map(() => a.source), b.map(f => f.sourceId), b.map(() => regionId),
+ ],
+ );
+ for (const row of r.rows) {
+ firmIdByKey.set(row.source_id, row.id);
+ if (!firmIdByName.has(row.normalized_name)) firmIdByName.set(row.normalized_name, row.id);
+ }
+ }
+
+ // ---- pass 3: upsert brokers with firm linkage ----
+ const brokerList = [...brokers.values()];
+ let upserted = 0;
+ for (let i = 0; i < brokerList.length; i += BATCH) {
+ const b = brokerList.slice(i, i + BATCH);
+ const firmIds = b.map(row => {
+ if (!row.firm_name) return null;
+ return firmIdByKey.get(firmKey(row.firm_license_no, row.firm_name))
+ ?? firmIdByName.get(normalizeName(row.firm_name)) ?? null;
+ });
+ const r = await query(
+ `INSERT INTO broker (firm_id, name, license_no, license_state, license_type, license_status,
+ email, phone, city, state_code, source, source_id, region_id)
+ SELECT * FROM UNNEST($1::int[],$2::text[],$3::text[],$4::text[],$5::text[],$6::text[],
+ $7::text[],$8::text[],$9::text[],$10::text[],$11::text[],$12::text[],$13::int[])
+ ON CONFLICT (source, source_id) DO UPDATE SET
+ firm_id = EXCLUDED.firm_id, name = EXCLUDED.name,
+ license_type = EXCLUDED.license_type, license_status = EXCLUDED.license_status,
+ email = COALESCE(EXCLUDED.email, broker.email),
+ phone = COALESCE(EXCLUDED.phone, broker.phone),
+ city = COALESCE(EXCLUDED.city, broker.city),
+ state_code = COALESCE(EXCLUDED.state_code, broker.state_code),
+ region_id = COALESCE(EXCLUDED.region_id, broker.region_id)`,
+ [
+ firmIds, b.map(r2 => r2.name), b.map(r2 => r2.license_no),
+ b.map(() => a.state), b.map(r2 => r2.license_type), b.map(r2 => r2.license_status),
+ b.map(r2 => r2.email ?? null), b.map(r2 => r2.phone ?? null),
+ b.map(r2 => r2.city ?? null), b.map(r2 => r2.state_code ?? null),
+ b.map(() => a.source), b.map(r2 => r2.license_no), b.map(() => regionId),
+ ],
+ );
+ upserted += r.rowCount ?? 0;
+ if ((i / BATCH) % 10 === 0) process.stdout.write(`\r[${a.source}] brokers ${Math.min(i + BATCH, brokerList.length).toLocaleString()}/${brokerList.length.toLocaleString()}`);
+ }
+ process.stdout.write('\n');
+
+ await query(
+ `UPDATE firm f SET agent_count = c.n
+ FROM (SELECT firm_id, COUNT(*)::int AS n FROM broker WHERE source = $1 AND firm_id IS NOT NULL GROUP BY firm_id) c
+ WHERE f.id = c.firm_id`,
+ [a.source],
+ );
+
+ await closeRun(runId, 'ok', {
+ upserted, skipped,
+ fileHash: files[0]?.sha256,
+ notes: `${a.state}: ${brokers.size} brokers, ${firms.size} firms, ${files.length} file(s), ${((Date.now() - t0) / 1000).toFixed(0)}s`,
+ });
+ console.log(`[${a.source}] OK — ${upserted.toLocaleString()} brokers upserted, ${firms.size.toLocaleString()} firms, ${((Date.now() - t0) / 1000).toFixed(0)}s`);
+ } catch (e: any) {
+ await closeRun(runId, 'failed', { notes: String(e.message || e).slice(0, 500) });
+ throw e;
+ }
+}
+
+async function main() {
+ const arg = (process.argv[2] || 'all').toLowerCase();
+ const picked = arg === 'all'
+ ? ADAPTERS
+ : ADAPTERS.filter(x => x.state.toLowerCase() === arg || x.source === arg);
+ if (!picked.length) {
+ console.error(`unknown state '${arg}' — have: ${ADAPTERS.map(x => x.state).join(', ')} (or 'all')`);
+ process.exit(2);
+ }
+ let failed = 0;
+ for (const a of picked) {
+ try { await runAdapter(a); }
+ catch (e) { failed++; console.error(`[${a.source}] FAILED:`, e); }
+ }
+ await pool.end();
+ if (failed) process.exit(1);
+}
+
+main();
diff --git a/src/ingest/brokers/fl_dbpr.ts b/src/ingest/brokers/fl_dbpr.ts
new file mode 100644
index 0000000..7648062
--- /dev/null
+++ b/src/ingest/brokers/fl_dbpr.ts
@@ -0,0 +1,63 @@
+/**
+ * FL — DBPR free licensee extracts (www2.myfloridalicense.com/sto/file_download/extracts/,
+ * refreshed ~daily, probed 2026-07-21). 14 regional files RE_rgn1..14.csv, headerless
+ * DBPR standard layout (latin-1). Column map (0-based, verified against live rows):
+ * 1 license-type group · 2 name · 3 dba · 4 rank ("SL Sales Associate", "BK Broker",
+ * "BL Broker Sales", "CQ RE Corp.", "PR RE Partnership", branch/instructor ranks)
+ * 8 city · 9 state · 13 numeric license # · 14 primary status ("Current"/"Invol Inactive"…)
+ * 15 secondary status ("Active"/"Inactive") · 19 prefixed license # (SL1234567)
+ * 21 employer firm name · 22 employer numeric license #.
+ * Firm source_id uses the NUMERIC license number so CQ firm rows and employer refs join.
+ */
+import { download } from '../run.ts';
+import { parseCsvFile } from './csv.ts';
+import type { BrokerRow, FetchedFile, StateAdapter } from './types.ts';
+
+const BASE = 'https://www2.myfloridalicense.com/sto/file_download/extracts/';
+const REGIONS = Array.from({ length: 14 }, (_, i) => `RE_rgn${i + 1}.csv`);
+
+export const flDbpr: StateAdapter = {
+ state: 'FL',
+ source: 'fl_dbpr',
+ url: BASE + 'RE_rgn1.csv',
+ async fetch() {
+ const out = [];
+ for (const f of REGIONS) out.push(await download(BASE + f, `fl_dbpr_${f}`));
+ return out;
+ },
+ *parse(files: FetchedFile[]): Generator<BrokerRow> {
+ for (const file of files) {
+ for (const r of parseCsvFile(file.path, 'latin1')) {
+ if (r.length < 20) continue;
+ const rank = (r[4] || '').trim();
+ const prefix = rank.slice(0, 2).toUpperCase();
+ const name = (r[2] || '').trim();
+ const licNo = (r[19] || '').trim() || (r[13] || '').trim();
+ if (!name || !licNo) continue;
+ const status = [r[14], r[15]].map(s => (s || '').trim()).filter(Boolean).join(' / ') || null;
+ const city = (r[8] || '').trim() || null;
+ const st = (r[9] || '').trim().toUpperCase() || null;
+ if (prefix === 'CQ' || prefix === 'PR') {
+ yield {
+ kind: 'firm', name, license_no: licNo,
+ license_type: prefix === 'CQ' ? 'Real Estate Corporation' : 'Real Estate Partnership',
+ license_status: status, city, state_code: st,
+ // numeric license joins employer refs — engine keys firms on firm_license_no
+ firm_license_no: (r[13] || '').trim() || null,
+ };
+ } else if (prefix === 'SL' || prefix === 'BK' || prefix === 'BL') {
+ const empName = (r[21] || '').trim() || null;
+ const empLic = (r[22] || '').trim() || null;
+ yield {
+ kind: 'broker', name, license_no: licNo,
+ license_type: rank.slice(3).trim() || rank,
+ license_status: status,
+ firm_name: empName, firm_license_no: empName ? empLic : null,
+ city, state_code: st,
+ };
+ }
+ // branch offices / instructors / additional locations skipped by design
+ }
+ }
+ },
+};
diff --git a/src/ingest/brokers/ny_dos.ts b/src/ingest/brokers/ny_dos.ts
new file mode 100644
index 0000000..a290bce
--- /dev/null
+++ b/src/ingest/brokers/ny_dos.ts
@@ -0,0 +1,48 @@
+/**
+ * NY — DOS "Active Real Estate Salespersons and Brokers" on data.ny.gov
+ * (yg7h-zjbf, refreshed daily; 147,941 rows probed 2026-07-21). Active licenses
+ * only, so license_status = 'Active'. Individuals carry their firm as
+ * business_name (no firm license number in-row → firms dedupe by normalized name).
+ * Entity types (CORPORATE/LLC/PARTNERSHIP/TRADENAME BROKER, PRINCIPAL OFFICE)
+ * become firm rows; BRANCH OFFICE rows are skipped (locations, not entities).
+ */
+import { download } from '../run.ts';
+import { parseCsvObjects } from './csv.ts';
+import type { BrokerRow, FetchedFile, StateAdapter } from './types.ts';
+
+const URL = 'https://data.ny.gov/api/views/yg7h-zjbf/rows.csv?accessType=DOWNLOAD';
+const INDIVIDUAL = new Set(['SALESPERSON', 'ASSOCIATE BROKER', 'INDIVIDUAL BROKER', 'REAL ESTATE SALESPERSON']);
+const FIRM = new Set(['CORPORATE BROKER', 'LIMITED LIABILITY BROKER', 'PARTNERSHIP BROKER', 'TRADENAME BROKER', 'REAL ESTATE PRINCIPAL OFFICE']);
+
+export const nyDos: StateAdapter = {
+ state: 'NY',
+ source: 'ny_dos',
+ url: URL,
+ async fetch() {
+ return [await download(URL, 'ny_dos_active_re_licensees.csv')];
+ },
+ *parse(files: FetchedFile[]): Generator<BrokerRow> {
+ for (const o of parseCsvObjects(files[0].path)) {
+ const type = (o.license_type || '').toUpperCase();
+ const city = o.business_city || null;
+ const st = (o.business_state || '').toUpperCase() || null;
+ if (!o.license_number) continue;
+ if (FIRM.has(type)) {
+ const name = o.business_name || o.license_holder_name;
+ if (!name) continue;
+ yield {
+ kind: 'firm', name, license_no: o.license_number,
+ license_type: type, license_status: 'Active', city, state_code: st,
+ };
+ } else if (INDIVIDUAL.has(type)) {
+ if (!o.license_holder_name) continue;
+ yield {
+ kind: 'broker', name: o.license_holder_name, license_no: o.license_number,
+ license_type: type, license_status: 'Active',
+ firm_name: o.business_name || null, firm_license_no: null,
+ city, state_code: st,
+ };
+ }
+ }
+ },
+};
diff --git a/src/ingest/brokers/tx_trec.ts b/src/ingest/brokers/tx_trec.ts
new file mode 100644
index 0000000..59275d2
--- /dev/null
+++ b/src/ingest/brokers/tx_trec.ts
@@ -0,0 +1,44 @@
+/**
+ * TX — TREC "Broker and Sales Agent License Holder Information" on the state
+ * Socrata portal (data.texas.gov s7ft-44qi, refreshed daily; ~322k rows probed
+ * 2026-07-21: 18,945 Broker Company / 42,417 Broker Individual / 260,426 Sales Agent).
+ * Firm linkage: related_license_* on each sponsored licensee = the sponsoring
+ * broker/company. No city column in this dataset (county only) — city stays null.
+ */
+import { download } from '../run.ts';
+import { parseCsvObjects } from './csv.ts';
+import type { BrokerRow, FetchedFile, StateAdapter } from './types.ts';
+
+const URL = 'https://data.texas.gov/api/views/s7ft-44qi/rows.csv?accessType=DOWNLOAD';
+
+export const txTrec: StateAdapter = {
+ state: 'TX',
+ source: 'tx_trec',
+ url: URL,
+ async fetch() {
+ return [await download(URL, 'tx_trec_license_holders.csv')];
+ },
+ *parse(files: FetchedFile[]): Generator<BrokerRow> {
+ for (const o of parseCsvObjects(files[0].path)) {
+ const type = o.license_type;
+ const name = o.full_name || o.key_name;
+ if (!name || !o.license_number) continue;
+ if (type === 'Broker Company') {
+ yield {
+ kind: 'firm', name, license_no: o.license_number,
+ license_type: type, license_status: o.status || null, state_code: 'TX',
+ };
+ } else {
+ const rel = o.related_license_type || '';
+ const sponsored = rel.startsWith('Broker') && !!o.related_license_full_name;
+ yield {
+ kind: 'broker', name, license_no: o.license_number,
+ license_type: type, license_status: o.status || null,
+ firm_name: sponsored ? o.related_license_full_name : null,
+ firm_license_no: sponsored ? o.related_license_number || null : null,
+ state_code: 'TX',
+ };
+ }
+ }
+ },
+};
diff --git a/src/ingest/brokers/types.ts b/src/ingest/brokers/types.ts
new file mode 100644
index 0000000..48afff8
--- /dev/null
+++ b/src/ingest/brokers/types.ts
@@ -0,0 +1,36 @@
+/**
+ * State licensing-roster adapter contract (M-B1). Each adapter wraps ONE state's
+ * genuinely-open bulk download (no portals, no sessions) and yields normalized rows.
+ * `kind:'firm'` rows are brokerage entities (companies/partnerships); `kind:'broker'`
+ * rows are individual licensees (brokers AND salespersons — license_type records which).
+ */
+export interface BrokerRow {
+ kind: 'firm' | 'broker';
+ name: string;
+ license_no: string | null;
+ license_type: string | null;
+ license_status: string | null;
+ /** broker rows only: sponsoring/employing firm as printed in the roster */
+ firm_name?: string | null;
+ firm_license_no?: string | null;
+ email?: string | null;
+ phone?: string | null;
+ city?: string | null;
+ state_code?: string | null;
+}
+
+export interface FetchedFile {
+ path: string;
+ sha256: string;
+}
+
+export interface StateAdapter {
+ /** 2-letter licensing state, e.g. 'TX' — becomes license_state + region 'state:XX' */
+ state: string;
+ /** provenance source key, e.g. 'tx_trec' — becomes broker.source / firm.source */
+ source: string;
+ /** primary URL recorded on the ingest_runs row */
+ url: string;
+ fetch(): Promise<FetchedFile[]>;
+ parse(files: FetchedFile[]): Generator<BrokerRow>;
+}
diff --git a/src/server/index.ts b/src/server/index.ts
index 5e2ef77..efd869e 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -173,6 +173,84 @@ app.get('/api/markets', async (req, res) => {
}
});
+// ---- M-B1 broker registry ----
+
+// paginated broker directory: ?state=&q=&firm=&limit=&offset=
+app.get('/api/brokers', async (req, res) => {
+ try {
+ const limit = Math.min(Math.max(Number(req.query.limit) || 500, 1), 1000);
+ const offset = Math.max(Number(req.query.offset) || 0, 0);
+ 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.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 ') : '';
+ params.push(limit, offset);
+ const r = await query(
+ `SELECT b.id, b.name, b.license_no, b.license_state, b.license_type, b.license_status,
+ b.city, b.state_code, b.source, f.name AS firm_name, f.id AS firm_id,
+ COUNT(*) OVER()::int AS total
+ FROM broker b LEFT JOIN firm f ON f.id = b.firm_id
+ ${where}
+ ORDER BY b.name, b.id
+ LIMIT $${params.length - 1} OFFSET $${params.length}`,
+ params,
+ );
+ const total = r.rows.length ? Number(r.rows[0].total) : 0;
+ res.json({ total, limit, offset, rows: r.rows.map(({ total: _t, ...row }: any) => row) });
+ } catch (e: any) {
+ res.status(500).json({ error: String(e.message || e) });
+ }
+});
+
+// firm directory: ?state=&q=&limit=&offset=
+app.get('/api/firms', async (req, res) => {
+ try {
+ const limit = Math.min(Math.max(Number(req.query.limit) || 500, 1), 1000);
+ const offset = Math.max(Number(req.query.offset) || 0, 0);
+ 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.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);
+ const r = await query(
+ `SELECT id, name, license_no, license_state, hq_city, hq_state, agent_count, source,
+ COUNT(*) OVER()::int AS total
+ FROM firm
+ ${where}
+ ORDER BY agent_count DESC NULLS LAST, name
+ LIMIT $${params.length - 1} OFFSET $${params.length}`,
+ params,
+ );
+ const total = r.rows.length ? Number(r.rows[0].total) : 0;
+ res.json({ total, limit, offset, rows: r.rows.map(({ total: _t, ...row }: any) => row) });
+ } catch (e: any) {
+ res.status(500).json({ error: String(e.message || e) });
+ }
+});
+
+// per-state coverage counts for the brokers.html header bar
+app.get('/api/broker-stats', async (_req, res) => {
+ try {
+ const [b, f] = await Promise.all([
+ query<{ license_state: string; n: string }>(`SELECT license_state, COUNT(*)::text AS n FROM broker GROUP BY 1 ORDER BY 1`),
+ query<{ license_state: string; n: string }>(`SELECT license_state, COUNT(*)::text AS n FROM firm GROUP BY 1 ORDER BY 1`),
+ ]);
+ const firms: Record<string, number> = {};
+ for (const row of f.rows) firms[row.license_state] = Number(row.n);
+ const states = b.rows.map(row => ({ state: row.license_state, brokers: Number(row.n), firms: firms[row.license_state] || 0 }));
+ res.json({
+ states,
+ total_brokers: states.reduce((s, x) => s + x.brokers, 0),
+ total_firms: Object.values(firms).reduce((s, x) => s + x, 0),
+ });
+ } catch (e: any) {
+ res.status(500).json({ error: String(e.message || e) });
+ }
+});
+
app.use(express.static(join(ROOT, 'public')));
app.listen(PORT, () => {
← 439b0e1 M5: watchlist/alerts/admin routes as mountable module (2-lin
·
back to Nationalrealestate
·
M5 complete: watchlist/alerts/admin routes mounted and verif a7a865e →