CRCP: name-method commercial/residential classifier for cre brokers (conservative else=keep, TK-10583) + wire into nightly snapshot refresh; fixes Coldwell Banker Residential mis-tagged commercial

780b470731e590b735b042f4e3fd2e2ef881bad7 · 2026-08-14 12:38:36 -0700 · Steve Abrams

Files touched

A scripts/classify-broker-agent-type.sql
M scripts/refresh-broker-snapshot.sh

Diff

commit 780b470731e590b735b042f4e3fd2e2ef881bad7
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Fri Aug 14 12:38:36 2026 -0700

    CRCP: name-method commercial/residential classifier for cre brokers (conservative else=keep, TK-10583) + wire into nightly snapshot refresh; fixes Coldwell Banker Residential mis-tagged commercial
---
 scripts/classify-broker-agent-type.sql | 38 ++++++++++++++++++++++++++++++++++
 scripts/refresh-broker-snapshot.sh     |  5 +++++
 2 files changed, 43 insertions(+)

diff --git a/scripts/classify-broker-agent-type.sql b/scripts/classify-broker-agent-type.sql
new file mode 100644
index 0000000..da753ef
--- /dev/null
+++ b/scripts/classify-broker-agent-type.sql
@@ -0,0 +1,38 @@
+-- classify-broker-agent-type.sql — name-based commercial/residential classifier for the CRCP `cre` DB.
+-- Mirrors usre's classify-asset-class.sql rules BUT with a CONSERVATIVE default (else = KEEP current),
+-- because CRCP's broker table is a CURATED CRE set (pre-filtered to brokers with commercial listings),
+-- so the usre "else -> residential" default would wrongly gut the desk. Approved by Steve 2026-08-14
+-- (TK-10583, "Conservative"): apply only the DEFINITE name signals, leave ambiguous curated brokers as-is.
+--
+-- Rule (top-down, first match wins), evaluated on the broker's FIRM name:
+--   1. BRAND match             -> commercial   (named national CRE house)
+--   2. "residential" in name   -> residential   (e.g. Coldwell Banker Residential, Del Rey Residential)
+--   3. NON-BROKER match        -> residential   (mortgage/lender/insurer/escrow/title/bank/appraiser/prop-mgmt)
+--   4. KEYWORD match           -> commercial   (commercial real estate / net lease / CRE / ...)
+--   5. else                    -> KEEP current  (curated CRE membership itself is the commercial signal;
+--                                                firm-less brokers also keep current)
+-- Re-runnable / idempotent. The nightly Redfin/SFR importers re-assert their own residential tags,
+-- which this classifier's rule 2/3/else all leave intact.
+
+\set brand_rx '\\y(cbre|jll|nai|cbc|svn|ipa)\\y|jones lang lasalle|cushman|colliers|newmark|marcus (&|and) millichap|kidder mathews|lee (&|and) associates|avison young|savills|cresa|institutional property advisors|stream realty|matthews (real estate|reis)|northmarq|berkadia|walker (&|and) dunlop|eastdil|transwestern|srs real estate|hanley investment|coldwell banker commercial|keller williams commercial|sperry van ness|tcn worldwide|voit real estate|daum commercial'
+\set kw_rx 'commercial real estate|\\ycommercial\\y|net lease|capital markets|investment sales|industrial realty|\\ycre\\y'
+\set nonbroker_rx 'mortgage|lending|\\yloans?\\y|insurance|escrow|\\ytitle\\y|\\ybank\\y|apprais|property management'
+
+BEGIN;
+
+UPDATE broker b
+   SET agent_type = CASE
+         WHEN f.name IS NULL                       THEN b.agent_type
+         WHEN lower(f.name) ~ :'brand_rx'          THEN 'commercial'
+         WHEN lower(f.name) ~ '\yresidential\y'    THEN 'residential'
+         WHEN lower(f.name) ~ :'nonbroker_rx'      THEN 'residential'
+         WHEN lower(f.name) ~ :'kw_rx'             THEN 'commercial'
+         ELSE b.agent_type
+       END
+  FROM firm f
+ WHERE b.firm_id = f.id;
+
+COMMIT;
+
+-- report
+SELECT agent_type, count(*) FROM broker GROUP BY agent_type ORDER BY 2 DESC;
diff --git a/scripts/refresh-broker-snapshot.sh b/scripts/refresh-broker-snapshot.sh
index 90e0612..c090456 100755
--- a/scripts/refresh-broker-snapshot.sh
+++ b/scripts/refresh-broker-snapshot.sh
@@ -18,6 +18,11 @@ echo "[$(ts)] refresh start" >> "$LOG"
 # (TK-10139). Non-fatal: a recorder failure must never break the snapshot refresh below.
 node scripts/enrich-provenance.js >> "$LOG" 2>&1 || echo "[$(ts)] recorder non-fatal fail — continuing" >> "$LOG"
 
+# 0b) re-classify broker.agent_type by the name-method (TK-10583) BEFORE export, so newly-imported
+# brokers + firms get commercial/residential tagged consistently (conservative: else=keep). Idempotent.
+# Non-fatal: a classify failure must never break the snapshot refresh below.
+psql "host=${CRE_PGHOST:-/tmp} dbname=${CRE_PGDATABASE:-cre}" -v ON_ERROR_STOP=1 -f scripts/classify-broker-agent-type.sql >> "$LOG" 2>&1 || echo "[$(ts)] classify non-fatal fail — continuing" >> "$LOG"
+
 # 1) regenerate locally (needs the cre DB on this host)
 if ! node scripts/export-brokers-snapshot.js >> "$LOG" 2>&1; then
   echo "[$(ts)] EXPORT FAILED — leaving prod snapshot untouched" >> "$LOG"; exit 1

← d79654c auto-data-snapshot: 2026-08-14T12:24:03 (3 data files) — dat · back to Commercialrealestate · auto-data-snapshot: 2026-08-14T12:54:45 (1 data files) — dat b01c0e8 →