`db/migrations/020_broker_firm_asset_class.sql`

32 lines
-- 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.
--
-- 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 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 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;