← back to Nationalrealestate
usre: add commercial/residential asset_class tag on firm+broker + ?asset_class= API filter (Node A of RENTV-commercial-only, TK-10535)
a522adb9dc8e9afe6f2a587f434f0c13901b9ac8 · 2026-08-13 10:31:04 -0700 · steve
Files touched
A db/migrations/020_broker_firm_asset_class.sqlA scripts/classify-asset-class.sqlM src/server/index.ts
Diff
commit a522adb9dc8e9afe6f2a587f434f0c13901b9ac8
Author: steve <steve@designerwallcoverings.com>
Date: Thu Aug 13 10:31:04 2026 -0700
usre: add commercial/residential asset_class tag on firm+broker + ?asset_class= API filter (Node A of RENTV-commercial-only, TK-10535)
---
db/migrations/020_broker_firm_asset_class.sql | 34 +++++++++++++++++++
scripts/classify-asset-class.sql | 48 +++++++++++++++++++++++++++
src/server/index.ts | 12 ++++---
3 files changed, 90 insertions(+), 4 deletions(-)
diff --git a/db/migrations/020_broker_firm_asset_class.sql b/db/migrations/020_broker_firm_asset_class.sql
new file mode 100644
index 0000000..b6d2cc9
--- /dev/null
+++ b/db/migrations/020_broker_firm_asset_class.sql
@@ -0,0 +1,34 @@
+-- 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.
+--
+-- 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.
+--
+-- Reversible: DROP the two columns. No data is deleted.
+
+BEGIN;
+
+-- 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)
+-- '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/scripts/classify-asset-class.sql b/scripts/classify-asset-class.sql
new file mode 100644
index 0000000..e875557
--- /dev/null
+++ b/scripts/classify-asset-class.sql
@@ -0,0 +1,48 @@
+-- classify-asset-class.sql — idempotent commercial/residential classifier for usre.
+-- 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'
+
+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)) ~ '\yresidential\y' THEN 'residential'
+ WHEN lower(coalesce(normalized_name, name)) ~ :'cre_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.
+UPDATE broker b
+ SET asset_class = f.asset_class,
+ asset_class_source = 'inherited'
+ FROM firm f
+ 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.
+UPDATE broker
+ SET asset_class = 'residential',
+ asset_class_source = 'seed'
+ WHERE firm_id IS NULL
+ AND asset_class_source IS DISTINCT FROM 'manual';
+
+COMMIT;
+
+-- report
+SELECT 'firm' AS tbl, asset_class, count(*) FROM firm GROUP BY asset_class
+UNION ALL
+SELECT 'broker' AS tbl, asset_class, count(*) FROM broker GROUP BY asset_class
+ORDER BY tbl, asset_class;
diff --git a/src/server/index.ts b/src/server/index.ts
index e9557e0..4132bf9 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -382,10 +382,12 @@ app.get('/api/brokers', async (req, res) => {
// Default ON for every consumer (desk, directory, etc.); ?unaffiliated=1 opts back
// into the full registry.
if (String(req.query.unaffiliated || '') !== '1') { conds.push(`b.firm_id IS NOT NULL`); }
+ // asset_class=commercial|residential — RENTV (CRE) requests commercial; usreal/CRCP request residential.
+ if (req.query.asset_class) { params.push(String(req.query.asset_class).toLowerCase()); conds.push(`b.asset_class = $${params.length}`); }
const where = conds.length ? 'WHERE ' + conds.join(' AND ') : '';
params.push(limit, offset);
const r = await query(
- `SELECT b.id, b.name, b.license_no, b.license_state, b.license_type, b.license_status,
+ `SELECT b.id, b.name, b.license_no, b.license_state, b.license_type, b.license_status, b.asset_class,
b.city, b.state_code, b.source, f.name AS firm_name, f.id AS firm_id,
b.phone AS broker_phone, b.email AS broker_email, b.website AS broker_website, b.website_status,
COALESCE(fs.url, f.website) AS firm_website,
@@ -419,10 +421,12 @@ app.get('/api/firms', async (req, res) => {
if (req.query.state) { params.push(String(req.query.state).toUpperCase()); conds.push(`license_state = $${params.length}`); }
if (req.query.city) { params.push(String(req.query.city).toUpperCase()); conds.push(`upper(hq_city) = $${params.length}`); }
if (req.query.q) { params.push('%' + String(req.query.q) + '%'); conds.push(`(name ILIKE $${params.length} OR license_no ILIKE $${params.length})`); }
+ // asset_class=commercial|residential — RENTV (CRE) requests commercial; usreal/CRCP request residential.
+ if (req.query.asset_class) { params.push(String(req.query.asset_class).toLowerCase()); conds.push(`asset_class = $${params.length}`); }
const where = conds.length ? 'WHERE ' + conds.join(' AND ') : '';
params.push(limit, offset);
const r = await query(
- `SELECT id, name, license_no, license_state, hq_city, hq_state, agent_count, source,
+ `SELECT id, name, license_no, license_state, hq_city, hq_state, agent_count, source, asset_class,
COUNT(*) OVER()::int AS total
FROM firm
${where}
@@ -479,14 +483,14 @@ app.get('/api/firm/:id', async (req, res) => {
if (!Number.isInteger(id) || id <= 0) return res.status(400).json({ error: 'bad firm id' });
const [firm, site, contacts, brokers] = await Promise.all([
query(`SELECT id, name, website, phone, hq_city, hq_state, license_no, license_state,
- source, agent_count, created_at
+ source, agent_count, asset_class, created_at
FROM firm WHERE id = $1`, [id]),
query(`SELECT url, discovery_method, discovered_at, http_status, title, has_idx_listings,
crawl_status, crawled_at, screenshot_path
FROM firm_site WHERE firm_id = $1`, [id]),
query(`SELECT kind, value, source_url, found_at FROM firm_contacts
WHERE firm_id = $1 ORDER BY kind, value`, [id]),
- query(`SELECT id, name, license_no, license_type, license_status, city, state_code
+ query(`SELECT id, name, license_no, license_type, license_status, city, state_code, asset_class
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 });
← 5a2f624 chore: gitignore *.bak (prevent tooling backup cruft) [TK-10
·
back to Nationalrealestate
·
usre: contrarian fixes — brand/keyword/non-broker classifier c428bcd →