CT broker upgrade: full DCP salesperson roster (eqtn-rppv), 18k->97k w/ status+city+firm linkage

61b8b5cf6dd11b25a7151960f33738a71f9dfff2 · 2026-07-26 08:08:30 -0700 · Steve Abrams

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

Files touched

M BROKER-COVERAGE-WAVE2.md
M src/ingest/brokers/ct_dcp.ts

Diff

commit 61b8b5cf6dd11b25a7151960f33738a71f9dfff2
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Jul 26 08:08:30 2026 -0700

    CT broker upgrade: full DCP salesperson roster (eqtn-rppv), 18k->97k w/ status+city+firm linkage
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 BROKER-COVERAGE-WAVE2.md     | 11 ++++++++
 src/ingest/brokers/ct_dcp.ts | 61 ++++++++++++++++++++++++++++++++------------
 2 files changed, 56 insertions(+), 16 deletions(-)

diff --git a/BROKER-COVERAGE-WAVE2.md b/BROKER-COVERAGE-WAVE2.md
index f73d130..51a6002 100644
--- a/BROKER-COVERAGE-WAVE2.md
+++ b/BROKER-COVERAGE-WAVE2.md
@@ -56,6 +56,17 @@ implementations; IL introduced the SODA-paginated-CSV fetch pattern
 ($limit/$offset pages, header-only page = stop, 500ms inter-page delay)
 reusable for any future big Socrata slice.
 
+## Wave-3 done
+
+- **CT upgrade (2026-07-26)** — `ct_dcp` re-pointed from the affiliation-only feed
+  `6tja-6vdt` (18,367 active salespersons, no status/city) to the FULL DCP
+  salesperson credential roster `eqtn-rppv` (99,396 rows). CT salespersons
+  **18,367 → 96,989** (5.3×), now carrying `license_status` (96,981) and `city`
+  (96,956) on every row + `supervising_broker` firm linkage (20,460). Same
+  `source`/source_id (`fullcredentialcode` = "RES.#") → in-place upsert, no
+  double-count. Salespersons only; the broker-side `REB` credential roster
+  remains a follow-up. Cost $0 (SODA CSV, `$limit=200000`).
+
 ## Wave-3 candidates
 
 1. **AK CBPL CSV** — genuinely free full professional-license DB; only a WAF
diff --git a/src/ingest/brokers/ct_dcp.ts b/src/ingest/brokers/ct_dcp.ts
index a0188ff..378c2fd 100644
--- a/src/ingest/brokers/ct_dcp.ts
+++ b/src/ingest/brokers/ct_dcp.ts
@@ -1,34 +1,63 @@
 /**
- * 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.
+ * CT — DCP "Real Estate Salesperson" full credential roster on data.ct.gov
+ * (eqtn-rppv; 99,396 rows probed 2026-07-26). Wave-3 upgrade from the old
+ * affiliation-only feed 6tja-6vdt (18,281 ACTIVE salesperson↔broker pairs, no
+ * status, no city): eqtn-rppv is the FULL salesperson credential DB — every
+ * status (ACTIVE 20.7k / INACTIVE 52k / EXPIRED APPLICATION 22.8k / LAPSED /
+ * RETIRED / SUSPENDED / REVOKED …), mailing city+state, and the sponsoring
+ * broker linkage (`supervising_broker` name + `broker_license` #, populated on
+ * 22,812 rows). Same `source='ct_dcp'` + license_no = fullcredentialcode
+ * ("RES.0000000"), which matches the old feed's source_id shape, so this
+ * upserts over the existing CT rows in place (enriches status/city, adds the
+ * inactive/expired long tail) — no double-count.
+ *
+ * Salespersons ONLY (credentialtype RES). The broker-side REB credential roster
+ * is a separate DCP extract — a follow-up, not this dataset.
  */
 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';
+const URL = 'https://data.ct.gov/resource/eqtn-rppv.csv'
+  + '?$order=fullcredentialcode&$limit=200000';
+
+/** Non-individual credential holders (a few dozen) → brokerage firm rows. */
+const ENTITY = /^(BUSINESS|CORPORATION|PARTNERSHIP|LIMITED LIABILITY COMPANY)$/i;
 
 export const ctDcp: StateAdapter = {
   state: 'CT',
   source: 'ct_dcp',
   url: URL,
   async fetch() {
-    return [await download(URL, 'ct_dcp_salespersons_with_broker.csv')];
+    return [await download(URL, 'ct_dcp_salesperson_full_roster.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',
-      };
+      const lic = o.fullcredentialcode;
+      if (!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 || null, 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 || 'Salesperson', license_status: status,
+          city, state_code: st,
+          firm_name: o.supervising_broker || null,
+          firm_license_no: o.supervising_broker ? (o.broker_license || null) : null,
+        };
+      }
     }
   },
 };

← 9e65b77 Self-correct: gate silent-break detection behind stableVolum · back to Nationalrealestate · Parcel coverage: 2026-07-26 re-probe intel (Franklin OH shp- 9c0ce40 →