brokers: add CT broker-side REB roster (fwpc-pgqj) — 16.8k brokers + 4.9k firms

ffad4297f7aa6f8d191b74b7c781cb9d0a6d286e · 2026-07-26 10:46:23 -0700 · Steve Abrams

Completes the CT broker thread: ct_dcp had salespersons only (RES). New ct_dcp_reb adapter ingests the Real Estate Broker Licenses roster (21,754 REB credentials) — 16,833 individual brokers + 4,920 brokerage firms (type INDIVIDUAL vs LLC/CORP/PARTNERSHIP split), all with status+city. Separate source so REB.# never collides with RES.#; same canonical-code normalization. $0 SODA CSV.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

A src/ingest/brokers/ct_dcp_reb.ts
M src/ingest/brokers/engine.ts

Diff

commit ffad4297f7aa6f8d191b74b7c781cb9d0a6d286e
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Jul 26 10:46:23 2026 -0700

    brokers: add CT broker-side REB roster (fwpc-pgqj) — 16.8k brokers + 4.9k firms
    
    Completes the CT broker thread: ct_dcp had salespersons only (RES). New ct_dcp_reb adapter ingests the Real Estate Broker Licenses roster (21,754 REB credentials) — 16,833 individual brokers + 4,920 brokerage firms (type INDIVIDUAL vs LLC/CORP/PARTNERSHIP split), all with status+city. Separate source so REB.# never collides with RES.#; same canonical-code normalization. $0 SODA CSV.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 src/ingest/brokers/ct_dcp_reb.ts | 54 ++++++++++++++++++++++++++++++++++++++++
 src/ingest/brokers/engine.ts     |  3 ++-
 2 files changed, 56 insertions(+), 1 deletion(-)

diff --git a/src/ingest/brokers/ct_dcp_reb.ts b/src/ingest/brokers/ct_dcp_reb.ts
new file mode 100644
index 0000000..3c67c6c
--- /dev/null
+++ b/src/ingest/brokers/ct_dcp_reb.ts
@@ -0,0 +1,54 @@
+/**
+ * CT — DCP "Real Estate Broker Licenses" on data.ct.gov (fwpc-pgqj; 21,754 rows
+ * probed 2026-07-26). The broker-side (REB) complement to the salesperson roster
+ * ingested by ct_dcp.ts (eqtn-rppv, RES credentials). Separate `source='ct_dcp_reb'`
+ * so REB.# credentials never collide with the salespersons' RES.# source_ids.
+ *
+ * `type` splits the roster: INDIVIDUAL → an individual broker licensee; the entity
+ * types (LIMITED LIABILITY COMPANY / CORPORATION / PARTNERSHIP / BUSINESS) are the
+ * brokerage FIRMS. All statuses kept (ACTIVE / INACTIVE / EXPIRED APPLICATION …),
+ * mailing city+state preserved. Cost $0 (SODA CSV).
+ */
+import { download } from '../run.ts';
+import { parseCsvObjects } from './csv.ts';
+import type { BrokerRow, FetchedFile, StateAdapter } from './types.ts';
+
+const URL = 'https://data.ct.gov/resource/fwpc-pgqj.csv?$order=fullcredentialcode&$limit=100000';
+
+const ENTITY = /^(BUSINESS|CORPORATION|PARTNERSHIP|LIMITED LIABILITY COMPANY)$/i;
+
+export const ctDcpReb: StateAdapter = {
+  state: 'CT',
+  source: 'ct_dcp_reb',
+  url: URL,
+  async fetch() {
+    return [await download(URL, 'ct_dcp_broker_roster.csv')];
+  },
+  *parse(files: FetchedFile[]): Generator<BrokerRow> {
+    for (const o of parseCsvObjects(files[0].path)) {
+      const lic = (o.fullcredentialcode || '').replace(/\.[A-Za-z]+$/, '').trim();
+      if (!lic || !/\d/.test(lic)) continue;
+      const status = o.status || null;
+      const city = o.city || null;
+      const st = (o.state || '').toUpperCase() || null;
+      const type = (o.type || '').toUpperCase();
+      if (ENTITY.test(type)) {
+        const name = o.businessname || o.name;
+        if (!name) continue;
+        yield {
+          kind: 'firm', name, license_no: lic,
+          license_type: o.credential || 'Real Estate Broker', license_status: status,
+          city, state_code: st,
+        };
+      } else {
+        const name = o.name;
+        if (!name) continue;
+        yield {
+          kind: 'broker', name, license_no: lic,
+          license_type: o.credential || 'Real Estate Broker', license_status: status,
+          city, state_code: st,
+        };
+      }
+    }
+  },
+};
diff --git a/src/ingest/brokers/engine.ts b/src/ingest/brokers/engine.ts
index 62a3c8b..fbdde40 100644
--- a/src/ingest/brokers/engine.ts
+++ b/src/ingest/brokers/engine.ts
@@ -17,11 +17,12 @@ import { flDbpr } from './fl_dbpr.ts';
 import { nyDos } from './ny_dos.ts';
 import { coDre } from './co_dre.ts';
 import { ctDcp } from './ct_dcp.ts';
+import { ctDcpReb } from './ct_dcp_reb.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, caDre];
+const ADAPTERS: StateAdapter[] = [txTrec, flDbpr, nyDos, coDre, ctDcp, ctDcpReb, ilIdfpr, deDpr, caDre];
 const BATCH = 5000;
 
 export function normalizeName(name: string): string {

← d409f98 miami: contrarian gate fixes — placeholder-deed bulk false-p · back to Nationalrealestate · parcel coverage: record Broward (WAF) + Palm Beach (cached-o 4118d04 →