`docs/deploy-asset-class.md`

74 lines
# 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);"
# c2. The broker-website-todo index was PULLED OUT of migration 017 (TK-10535, Cody gate) because a
#     non-concurrent build on the freshly-NULL website_status column matches all ~2M rows and would
#     ACCESS-EXCLUSIVE-lock the live desk mid-migration. Build it CONCURRENTLY here (IF NOT EXISTS =
#     no-op if the surgical-017 fix already created it on prod):
psql usre -c "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_broker_website_todo ON broker (id) WHERE website_status IS NULL;"

# 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. GATE the code restart on the column actually existing (TK-10535, Cody HOLE 3) — usre/RENTV/CRCP
#    all read firm.asset_class; if code restarts before 020 landed, /api/firm/:id 404s the desk blank.
psql usre -tAc "SELECT 1 FROM information_schema.columns WHERE table_name='firm' AND column_name='asset_class';" \
  | grep -q 1 && echo "column present -> safe to restart" || { echo "ABORT: asset_class column missing — do NOT restart"; exit 1; }
# 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.