← back to Nationalrealestate
usre: contrarian fixes — brand/keyword/non-broker classifier (Berkadia stays commercial, lenders excluded), runner-safe migration (no BEGIN/COMMIT; indexes+backfill out-of-band), class-gated /api/firm/:id 404, deploy runbook (TK-10535)
c428bcd02124db11821f8461b72d181641a1edef · 2026-08-13 10:48:11 -0700 · steve
Files touched
M db/migrations/020_broker_firm_asset_class.sqlA docs/deploy-asset-class.mdM scripts/classify-asset-class.sqlM src/server/index.ts
Diff
commit c428bcd02124db11821f8461b72d181641a1edef
Author: steve <steve@designerwallcoverings.com>
Date: Thu Aug 13 10:48:11 2026 -0700
usre: contrarian fixes — brand/keyword/non-broker classifier (Berkadia stays commercial, lenders excluded), runner-safe migration (no BEGIN/COMMIT; indexes+backfill out-of-band), class-gated /api/firm/:id 404, deploy runbook (TK-10535)
---
db/migrations/020_broker_firm_asset_class.sql | 35 +++++++--------
docs/deploy-asset-class.md | 64 +++++++++++++++++++++++++++
scripts/classify-asset-class.sql | 38 ++++++++++------
src/server/index.ts | 5 +++
4 files changed, 110 insertions(+), 32 deletions(-)
diff --git a/db/migrations/020_broker_firm_asset_class.sql b/db/migrations/020_broker_firm_asset_class.sql
index b6d2cc9..5841919 100644
--- a/db/migrations/020_broker_firm_asset_class.sql
+++ b/db/migrations/020_broker_firm_asset_class.sql
@@ -1,34 +1,31 @@
-- 020_broker_firm_asset_class.sql
-- Adds a real commercial-vs-residential classification to firms + brokers.
--
--- WHY: usre IS the residential platform (Redfin/Zillow/Census). Its broker
--- registry (~215k firms / ~2M brokers) is the DRE licensee universe — overwhelmingly
--- RESIDENTIAL. RENTV (commercial-only) was proxying /api/brokers + /api/firms straight
--- into this, leaking residential agents/firms onto a CRE surface. This tag lets every
--- consumer filter by asset_class: RENTV -> commercial only; usreal + CRCP -> residential.
+-- WHY: usre IS the residential platform (Redfin/Zillow/Census). Its broker registry
+-- (~215k firms / ~2M brokers) is the DRE licensee universe — overwhelmingly RESIDENTIAL.
+-- RENTV (commercial-only) was proxying /api/brokers + /api/firms straight into this, leaking
+-- residential agents/firms onto a CRE surface. This tag lets every consumer filter by
+-- asset_class: RENTV -> commercial only; usreal + CRCP -> residential.
--
--- DESIGN: firm is the primary unit; brokers inherit firm.asset_class (one propagation
--- pass). Default is RESIDENTIAL (the majority); a firm is PROMOTED to 'commercial' only
--- when its name matches the CRE allowlist/keywords (see scripts/classify-asset-class.sql).
--- 'unclassified' is reserved for firm-less / ambiguous rows; every consumer treats
--- non-'commercial' as residential so nothing residential can leak into RENTV.
+-- RUNNER CONTRACT: no BEGIN/COMMIT here — db/migrate.ts wraps each file in a transaction.
+-- Only INSTANT, transaction-safe DDL lives in this file:
+-- * ADD COLUMN ... DEFAULT <const> is metadata-only in PG11+ (no table rewrite), so it is
+-- safe on the ~2M-row broker table even inside the runner's transaction.
+-- The HEAVY, lock-taking steps are DELIBERATELY NOT here — they run out-of-band from the
+-- deploy runbook (docs/deploy-asset-class.md) so they never hold an exclusive lock during
+-- the migration:
+-- * CREATE INDEX CONCURRENTLY on firm.asset_class + broker.asset_class (cannot run in a txn)
+-- * scripts/classify-asset-class.sql (the ~2M-row backfill UPDATE, run off-peak)
--
--- Reversible: DROP the two columns. No data is deleted.
-
-BEGIN;
+-- Reversible: DROP the four columns. No data is deleted.
-- asset_class: 'commercial' | 'residential' | 'unclassified'
ALTER TABLE firm ADD COLUMN IF NOT EXISTS asset_class text NOT NULL DEFAULT 'unclassified';
ALTER TABLE broker ADD COLUMN IF NOT EXISTS asset_class text NOT NULL DEFAULT 'unclassified';
-- how a row got its class, for auditability + manual-override protection
--- 'seed' = matched the classifier (name allowlist/keywords)
+-- 'seed' = matched the classifier (name brand/keyword/non-broker rules)
-- 'inherited' = broker took its firm's class
-- 'manual' = a human override; the classifier must NEVER overwrite this
ALTER TABLE firm ADD COLUMN IF NOT EXISTS asset_class_source text;
ALTER TABLE broker ADD COLUMN IF NOT EXISTS asset_class_source text;
-
-CREATE INDEX IF NOT EXISTS idx_firm_asset_class ON firm (asset_class);
-CREATE INDEX IF NOT EXISTS idx_broker_asset_class ON broker (asset_class);
-
-COMMIT;
diff --git a/docs/deploy-asset-class.md b/docs/deploy-asset-class.md
new file mode 100644
index 0000000..d5cc8d1
--- /dev/null
+++ b/docs/deploy-asset-class.md
@@ -0,0 +1,64 @@
+# Deploy runbook — commercial/residential asset_class (TK-10535)
+
+**Status: GATED — do not run on prod without Steve's approval.** Prod usre is on Kamatera.
+Everything below is verified locally (Mac2 `usre` DB, usre :9913, RENTV :9704, CRCP :9911).
+
+## What ships
+- **usre** (`nationalrealestate`): migration `020_broker_firm_asset_class.sql` (adds `asset_class` +
+ `asset_class_source` on `firm`+`broker`), `scripts/classify-asset-class.sql` (backfill), and
+ `src/server/index.ts` (`?asset_class=` filter on `/api/brokers` + `/api/firms`, class-gated
+ `/api/firm/:id`).
+- **RENTV** (`rentv`): `server.js` forces `asset_class=commercial` in BOTH usre call paths
+ (`usreProxy` + `usreFetch`/Summit Leads).
+- **CRCP** (`commercialrealestate`): `scripts/serve.js` `/api/residential-brokers` +
+ `/api/residential-firms`, `public/residential-brokers.html`.
+
+## Order matters — usre prod first (RENTV/CRCP proxy INTO it)
+
+### 1. usre (Kamatera) — DB, then code
+Prod usre is behind on migrations **014–019**. The runner (`db/migrate.ts`) applies unapplied
+files in lexical order and rolls back the whole file on any error.
+
+```sh
+# a. Back up first (per standing 3-2-1 rule)
+pg_dump usre > /root/backups/usre-pre-020-$(date +%F).sql
+
+# b. Apply 014..020 in order via the runner (020 is instant: ADD COLUMN DEFAULT const = metadata-only).
+# If 014-019 fail on drift, resolve those FIRST — do not hand-jam 020 ahead of them
+# (keeps schema_migrations honest).
+npx tsx db/migrate.ts
+
+# c. Indexes OUT OF BAND — CONCURRENTLY (cannot run in the runner's txn; avoids an exclusive
+# lock on the ~2M-row broker table that would stall the live desk).
+psql usre -c "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_firm_asset_class ON firm (asset_class);"
+psql usre -c "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_broker_asset_class ON broker (asset_class);"
+
+# d. Backfill the tag — off-peak. Single UPDATE over ~2M broker rows; ~2 min locally. It takes a
+# ROW-share lock (not a table lock) but is write-heavy — run when desk traffic is low.
+# Idempotent + re-runnable; never overwrites asset_class_source='manual'.
+psql usre -f scripts/classify-asset-class.sql # prints the firm/broker class tallies
+
+# e. Deploy usre code + restart. Expect ~4,100 commercial firms / ~18,400 commercial brokers.
+pm2 restart usrealestate
+```
+
+### 2. RENTV (claude-rentv's separate prod worktree — SOLO-OWNED by cre-agent, TK-10290)
+Hand the `server.js` diff (commit on Mac2) to cre-agent to land + deploy on the prod worktree.
+Verify after: `/api/brokers` and `/api/summit/leads?type=firms` return only `asset_class:commercial`
+(CA brokers drop ~384k → ~6.6k; first firm Eastdil/CBRE).
+
+### 3. CRCP (`commercialrealestate`)
+Deploy `scripts/serve.js` + `public/residential-brokers.html`; restart the CRCP server.
+Verify `/api/residential-brokers?state=CA` returns ~204k residential brokers.
+
+## Rollback
+- usre: `ALTER TABLE firm DROP COLUMN asset_class, DROP COLUMN asset_class_source;` (same for broker),
+ revert `src/server/index.ts`. RENTV/CRCP simply pass a param usre then ignores — no break.
+- RENTV/CRCP: revert the commits.
+
+## Known limitation (be honest in the UI)
+Classification is **name-based** (usre's DRE roster has no specialty field). It has HIGH PRECISION
+(named national CRE houses + CRE keywords, lenders/insurers excluded) but MODEST RECALL — boutique /
+single-office CRE shops whose names carry no CRE signal default to residential and won't show on
+RENTV's desk. Improve over time via `asset_class_source='manual'` overrides (the classifier never
+clobbers them). RENTV desk copy should say it shows *major/branded* CRE firms, not the whole market.
diff --git a/scripts/classify-asset-class.sql b/scripts/classify-asset-class.sql
index e875557..d59a563 100644
--- a/scripts/classify-asset-class.sql
+++ b/scripts/classify-asset-class.sql
@@ -2,28 +2,40 @@
-- Re-runnable: safe to run any number of times. NEVER overwrites a row whose
-- asset_class_source = 'manual' (human override wins).
--
--- Rule: DEFAULT residential (the ~215k-firm registry is overwhelmingly residential).
--- PROMOTE to 'commercial' only when the firm name matches the CRE allowlist/keywords
--- below AND does not carry an explicit 'residential' token. Brokers inherit their firm.
---
--- The commercial signal set (major U.S. CRE brokerages + CRE keywords). Short/ambiguous
--- tokens (cbre, jll, nai, cbc, svn, ipa, cre) use word boundaries (\y) to avoid substring
--- false-positives. Coldwell Banker / Keller Williams only match their COMMERCIAL arms.
-\set cre_rx '\\y(cbre|jll|nai|cbc|svn|ipa|cre)\\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|\\ygantry\\y|coldwell banker commercial|keller williams commercial|sperry van ness|tcn worldwide|voit real estate|daum commercial|commercial real estate|\\ycommercial\\y|net lease|capital markets|investment sales|industrial realty'
+-- Rule (evaluated top-down, first match wins):
+-- 1. BRAND match -> commercial (a named national CRE house; wins even if the name also
+-- contains a lender word, e.g. "Berkadia Commercial Mortgage")
+-- 2. "residential" in name -> residential (explicit; keeps Coldwell Banker RESIDENTIAL out)
+-- 3. NON-BROKER match -> residential (mortgage / lender / insurer / escrow / title / bank /
+-- appraiser / property-mgmt: NOT a CRE brokerage, so it must
+-- not appear on RENTV's CRE broker desk. Only reached when
+-- no CRE brand matched, so it can't demote a real CRE house.)
+-- 4. KEYWORD match -> commercial (commercial real estate / net lease / capital markets /
+-- investment sales / industrial realty / bare "commercial" / cre)
+-- 5. else -> residential (the ~215k-firm registry is overwhelmingly residential)
+-- Brokers inherit their firm's class; firm-less brokers default residential.
+
+-- 1) Named national CRE houses. Short/ambiguous tokens use word boundaries (\y).
+\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'
+-- 4) Generic commercial signals (weaker than a brand; gated by the non-broker guard above).
+\set kw_rx 'commercial real estate|\\ycommercial\\y|net lease|capital markets|investment sales|industrial realty|\\ycre\\y'
+-- 3) Not a CRE brokerage — lenders, insurers, title/escrow, banks, appraisers, property managers.
+\set nonbroker_rx 'mortgage|lending|\\yloans?\\y|insurance|escrow|\\ytitle\\y|\\ybank\\y|apprais|property management'
BEGIN;
--- 1) Firms: promote to commercial on CRE match (unless explicitly 'residential'); else residential.
UPDATE firm
SET asset_class = CASE
+ WHEN lower(coalesce(normalized_name, name)) ~ :'brand_rx' THEN 'commercial'
WHEN lower(coalesce(normalized_name, name)) ~ '\yresidential\y' THEN 'residential'
- WHEN lower(coalesce(normalized_name, name)) ~ :'cre_rx' THEN 'commercial'
+ WHEN lower(coalesce(normalized_name, name)) ~ :'nonbroker_rx' THEN 'residential'
+ WHEN lower(coalesce(normalized_name, name)) ~ :'kw_rx' THEN 'commercial'
ELSE 'residential'
END,
asset_class_source = 'seed'
WHERE asset_class_source IS DISTINCT FROM 'manual';
--- 2) Brokers with a firm: inherit the firm's class.
+-- Brokers with a firm inherit the firm's class.
UPDATE broker b
SET asset_class = f.asset_class,
asset_class_source = 'inherited'
@@ -31,8 +43,8 @@ UPDATE broker b
WHERE b.firm_id = f.id
AND b.asset_class_source IS DISTINCT FROM 'manual';
--- 3) Firm-less brokers (standalone DRE licensees): residential by default. RENTV already
--- drops these via firm_id IS NOT NULL, but tagging them keeps usreal/CRCP correct.
+-- Firm-less brokers (standalone DRE licensees): residential by default. RENTV drops these via
+-- firm_id IS NOT NULL anyway, but tagging keeps usreal/CRCP correct.
UPDATE broker
SET asset_class = 'residential',
asset_class_source = 'seed'
diff --git a/src/server/index.ts b/src/server/index.ts
index 4132bf9..9312bf3 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -494,6 +494,11 @@ app.get('/api/firm/:id', async (req, res) => {
FROM broker WHERE firm_id = $1 ORDER BY name LIMIT 50`, [id]),
]);
if (!firm.rows.length) return res.status(404).json({ error: 'firm not found: ' + id });
+ // Class-gated drill: a commercial-only consumer (RENTV) that passes ?asset_class=commercial
+ // must not be able to open a residential firm's detail/roster by a hand-crafted id (TK-10535).
+ if (req.query.asset_class && String(firm.rows[0].asset_class) !== String(req.query.asset_class).toLowerCase()) {
+ return res.status(404).json({ error: 'firm not found: ' + id });
+ }
res.json({
firm: firm.rows[0],
site: site.rows[0] || null,
← a522adb usre: add commercial/residential asset_class tag on firm+bro
·
back to Nationalrealestate
·
nav-agent: opt-out market.html (charts detail) + listings.ht e48c935 →