CRCP: FL DBPR CSV adapter — Miami (Dade county) 67,776 licensees loaded (brokers+salespersons, active); streaming quoted-CSV parser + batched upsert

3d61200d8e1993e434cf05228dd1f83426fdadc8 · 2026-07-12 08:05:45 -0700 · Steve Abrams
Files touched

A scripts/ingest-gov-fl.js
Diff

commit 3d61200d8e1993e434cf05228dd1f83426fdadc8
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Jul 12 08:05:45 2026 -0700

    CRCP: FL DBPR CSV adapter — Miami (Dade county) 67,776 licensees loaded (brokers+salespersons, active); streaming quoted-CSV parser + batched upsert
---
 scripts/ingest-gov-fl.js | 79 ++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 79 insertions(+)

diff --git a/scripts/ingest-gov-fl.js b/scripts/ingest-gov-fl.js
new file mode 100644
index 0000000..595fdfb
--- /dev/null
+++ b/scripts/ingest-gov-fl.js
@@ -0,0 +1,79 @@
+#!/usr/bin/env node
+/*
+ * ingest-gov-fl.js — load Miami (Miami-Dade county) licensed real-estate agents/brokers from the
+ * FLORIDA DBPR public-records CSV into cre.gov_licensed_agent. $0, authoritative govt data.
+ *
+ * Source: FL Dept. of Business & Professional Regulation — Real Estate Commission public records
+ *   CSV (weekly). Downloaded to /tmp/fl_realestate.csv. Quote/comma delimited, no header.
+ * Fields: 0 category · 1 name · 2 dba · 3 rank · 4-6 addr · 7 city · 8 st · 9 zip · 10 county
+ *         · 11 license# · 12 primary · 13 status · ... · 16 expiration · ... · 19 employer
+ *
+ * Usage: node scripts/ingest-gov-fl.js   (filters county=Miami-Dade, status Active, person types)
+ */
+'use strict';
+const fs = require('fs');
+const readline = require('readline');
+const { pool } = require('./db/brokers-db');
+const FILE = '/tmp/fl_realestate.csv';
+const METRO = 'miami';
+
+// parse a fully-quoted CSV line: "a","b",... -> [a,b,...] (handles "" escapes)
+function parseLine(line) {
+  const out = []; const re = /"((?:[^"]|"")*)"/g; let m;
+  while ((m = re.exec(line))) out.push(m[1].replace(/""/g, '"'));
+  return out;
+}
+const flDate = s => { // "30-SEP-27" -> 2027-09-30
+  const M = { JAN: '01', FEB: '02', MAR: '03', APR: '04', MAY: '05', JUN: '06', JUL: '07', AUG: '08', SEP: '09', OCT: '10', NOV: '11', DEC: '12' };
+  const m = /^(\d{2})-([A-Z]{3})-(\d{2})$/.exec((s || '').trim());
+  if (!m) return null; const yr = +m[3] > 50 ? '19' + m[3] : '20' + m[3];
+  return `${yr}-${M[m[2]] || '01'}-${m[1]}`;
+};
+
+async function flush(batch) {
+  if (!batch.length) return 0;
+  const seen = new Set(); const vals = []; const ph = [];
+  for (const r of batch) {
+    const key = r.lic + '|' + r.firm; if (seen.has(key)) continue; seen.add(key);
+    const b = vals.length;
+    ph.push(`('FL',$${b+1},$${b+2},$${b+3},$${b+4},$${b+5},$${b+6},$${b+7},$${b+8},$${b+9},$${b+10},'fl-dbpr')`);
+    vals.push(METRO, r.name, r.firm, r.lic, r.type, r.city, r.addr, r.zip, r.county, r.exp);
+  }
+  if (!ph.length) return 0;
+  await pool.query(
+    `INSERT INTO gov_licensed_agent (state,metro,name,firm,license_number,license_type,city,addr,zip,county,expiration,source)
+     VALUES ${ph.join(',')}
+     ON CONFLICT (source,license_number,firm) DO UPDATE
+       SET license_type=EXCLUDED.license_type, expiration=EXCLUDED.expiration, city=EXCLUDED.city, fetched_at=now()`, vals);
+  return ph.length;
+}
+
+async function main() {
+  if (!fs.existsSync(FILE)) { console.error(`missing ${FILE} — download the DBPR CSV first`); process.exit(1); }
+  const rl = readline.createInterface({ input: fs.createReadStream(FILE), crlfDelay: Infinity });
+  let scanned = 0, matched = 0, loaded = 0, batch = [];
+  for await (const line of rl) {
+    scanned++;
+    if (!/"(dade|miami[- ]?dade)"/i.test(line)) continue;   // fast prefilter (FL county field = "Dade")
+    const f = parseLine(line);
+    if (f.length < 17) continue;
+    const county = (f[10] || '').trim();
+    if (!/^(dade|miami[- ]?dade)$/i.test(county)) continue; // exact county = Miami metro
+    const status = (f[13] || '').trim();
+    if (!/active/i.test(status)) continue;                  // active only
+    const rank = (f[3] || '').trim();
+    if (!/(broker|sales)/i.test(rank)) continue;            // brokers + salespersons
+    if (/corp|officer|school|instructor/i.test(rank)) continue;
+    matched++;
+    batch.push({ name: (f[1] || '').trim(), firm: (f[19] || '').trim(), lic: (f[11] || '').trim(),
+      type: rank, city: (f[7] || '').trim(), addr: (f[4] || '').trim(), zip: (f[9] || '').trim(),
+      county, exp: flDate(f[16]) });
+    if (batch.length >= 500) { loaded += await flush(batch); batch = []; process.stdout.write(`\r  scanned ${scanned}  matched ${matched}  loaded ${loaded}   `); }
+  }
+  loaded += await flush(batch);
+  process.stdout.write('\n');
+  const { rows: [c] } = await pool.query(`SELECT count(*) n, count(*) FILTER (WHERE license_type ILIKE '%broker%') brokers FROM gov_licensed_agent WHERE metro=$1 AND source='fl-dbpr'`, [METRO]);
+  console.log(`✔ miami: ${c.n} licensees in DB (${c.brokers} brokers). source: FL DBPR public records. cost: $0`);
+  await pool.end();
+}
+if (require.main === module) main().catch(e => { console.error(e); process.exit(1); });
← 7fe7597 CRCP: NY metros loaded from gov data — NYC 47,019 + Hamptons · back to Commercialrealestate · CRCP: fix empty prod broker-grid — snapshot fallback for /ap 235cba4 →