ct_dcp: normalize credential code to canonical RES.# source_id (contrarian gate fix)

c452e2e0fccadc225435c4fd4d46213cc13a2c8a · 2026-07-26 08:20:24 -0700 · Steve Abrams

218 rows carried a legacy Socrata suffix (RES.#.INACTIVE) + 1 digitless RES stub — malformed upsert keys that would silently duplicate if upstream cleaned the field. Strip the suffix at parse, require a digit, delete+reingest. Verified: 96,988 CT rows now 100% canonical shape, 0 collisions with distinct persons.

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

Files touched

M src/ingest/brokers/ct_dcp.ts

Diff

commit c452e2e0fccadc225435c4fd4d46213cc13a2c8a
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Jul 26 08:20:24 2026 -0700

    ct_dcp: normalize credential code to canonical RES.# source_id (contrarian gate fix)
    
    218 rows carried a legacy Socrata suffix (RES.#.INACTIVE) + 1 digitless RES stub — malformed upsert keys that would silently duplicate if upstream cleaned the field. Strip the suffix at parse, require a digit, delete+reingest. Verified: 96,988 CT rows now 100% canonical shape, 0 collisions with distinct persons.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 src/ingest/brokers/ct_dcp.ts | 10 ++++++++--
 1 file changed, 8 insertions(+), 2 deletions(-)

diff --git a/src/ingest/brokers/ct_dcp.ts b/src/ingest/brokers/ct_dcp.ts
index 378c2fd..73478c4 100644
--- a/src/ingest/brokers/ct_dcp.ts
+++ b/src/ingest/brokers/ct_dcp.ts
@@ -33,8 +33,14 @@ export const ctDcp: StateAdapter = {
   },
   *parse(files: FetchedFile[]): Generator<BrokerRow> {
     for (const o of parseCsvObjects(files[0].path)) {
-      const lic = o.fullcredentialcode;
-      if (!lic) continue;
+      // Normalize the credential code to the canonical "RES.#######" shape: a
+      // small tail of rows carry a legacy Socrata suffix ("RES.0769651.INACTIVE")
+      // — strip it so source_id stays stable if upstream ever cleans the field
+      // (else re-ingest would INSERT a duplicate instead of upserting). Require a
+      // real credential number so the handful of digitless "RES" stubs are dropped
+      // rather than collapsing to one shared key.
+      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;

← a74ceb3 brokers: license_status filter (?status=active) on /api/brok · back to Nationalrealestate · Free priced-deed adapters (Oregon RLIS tri-county, Spokane W a3e4169 →