← back to Commercialrealestate
scripts/export-brokers-snapshot.js
114 lines
#!/usr/bin/env node
/*
* export-brokers-snapshot.js — dump the broker grid's data to data/brokers-snapshot.json so
* PROD (crcp.agentabrams.com, which has NO Postgres) can serve /api/brokers/all from the
* snapshot, exactly like /api/residential does. Same columns the live route selects.
* Run locally (where the `cre` DB lives), then deploy the snapshot + serve.js.
*/
'use strict';
const fs = require('fs');
const path = require('path');
const { pool, graph, graphHistory, topBrokers, enrichStats } = require('./db/brokers-db');
async function main() {
const brokers = (await pool.query(
`SELECT b.id, b.name, f.name firm, b.agent_type, b.phone, b.email, b.website, b.linkedin,
b.license, b.title, b.total_assets, b.specialties, b.office_addr, b.crexi_id, b.source, b.created_at,
b.state, b.dre_match, b.dre_license,
(SELECT count(*) FROM broker_listing bl WHERE bl.broker_id=b.id)
+ (SELECT count(*) FROM broker_condo bc WHERE bc.broker_id=b.id) AS listings
FROM broker b LEFT JOIN firm f ON f.id=b.firm_id
ORDER BY listings DESC NULLS LAST, b.name`)).rows;
// Also snapshot the mind-map page's endpoints (brokers.html): /api/graph, /api/brokers/top,
// /api/brokers/enrich-stats. Reuse the live functions so the shapes are byte-identical to the
// DB path — prod (no Postgres) serves these from the snapshot exactly like /api/brokers/all.
// Use the history variant so the PROD snapshot carries colist (+ future worked_at)
// edges — otherwise ?history=1 on prod silently degrades to a base-only graph.
const graphData = await graphHistory(400);
const top = await topBrokers(50);
const enrich = await enrichStats();
// ── Per-broker + per-firm property buckets (Steve 2026-07-31) ─────────────
// PROD has no Postgres, so the broker/firm DETAIL modals (which need broker_listing
// etc.) can't run there — they error. We bake each broker's Active/Closed/Expired
// property lists into the snapshot so serve.js can serve the detail modals DB-less,
// exactly like /api/brokers/all. Attribution is only as good as the free data:
// Active = Crexi/Redfin listing edges; Closed = the enriched pilot brokers only;
// Expired = best-effort sold-cross-ref (no MLS off-market feed). No fabrication —
// per-broker CLOSED beyond the pilots + real WITHDRAWN status + event TIMES need a
// paid records source (ATTOM/CoStar/LoopNet) — that's the gated paid track.
const CAP_B = 100, CAP_BX = 12, CAP_F = 300, CAP_FX = 40;
const rows = (s) => pool.query(s).then(r => r.rows);
const [curCom, curCon, closedAll, expiredAll] = await Promise.all([
// Correctness gate (DTD 2026-08-19, A-with-rider): the commercial `listing` table has NO
// status column, so we can't filter sold/off-market by status. It IS actively refreshed
// (nothing older than ~90d today), so a created_at recency window keeps a future stale row
// from ever surfacing as an ACTIVE listing on a public broker profile. 120d = headroom over
// the current ~90d max; widen only if genuinely-active long-listed deals start dropping off.
// (A real fix needs a last_seen column on the commercial sweep — tracked as a follow-up.)
rows(`SELECT bl.broker_id, l.address, l.city, l.zip, l.type, l.price, l.units, l.cap_rate, l.created_at AS listed_at
FROM broker_listing bl JOIN listing l ON l.id=bl.listing_id
WHERE l.created_at > now() - interval '120 days'`),
rows(`SELECT bc.broker_id, c.address, c.city, NULL::text zip, 'Condo' type, c.price,
NULL::int units, NULL::numeric cap_rate, NULL::timestamptz listed_at
FROM broker_condo bc JOIN condo c ON c.id=bc.condo_id`),
rows(`SELECT broker_id, address, city, sold_price, sold_date, type, source FROM broker_closed_listing`),
rows(`SELECT bl.broker_id, l.address, l.city, l.type, l.price AS list_price,
cs.sold_price, cs.sold_date, cs.mls, cs.url
FROM broker_listing bl JOIN listing l ON l.id=bl.listing_id
JOIN closed_sale cs ON cs.city = l.city
AND split_part(cs.address,' ',1) = split_part(l.address,' ',1)
AND lower(cs.address) LIKE '%'||lower(coalesce((regexp_match(l.address,'[A-Za-z]{3,}'))[1],'~'))||'%'
WHERE cs.sold_price > 0`),
]);
const groupBy = (arr) => { const m = {}; for (const r of arr) { (m[r.broker_id] = m[r.broker_id] || []).push(r); } return m; };
const cur = groupBy([...curCom, ...curCon]), clo = groupBy(closedAll), exp = groupBy(expiredAll);
const byPrice = (a, b) => (Number(b.price ?? b.sold_price ?? 0) - Number(a.price ?? a.sold_price ?? 0));
const byDate = (a, b) => String(b.sold_date || '').localeCompare(String(a.sold_date || ''));
const strip = (r) => { const { broker_id, ...rest } = r; return rest; };
const brokerDetail = {};
for (const b of brokers) {
const c = (cur[b.id] || []).sort(byPrice).slice(0, CAP_B).map(strip);
const cl = (clo[b.id] || []).sort(byDate).slice(0, CAP_B).map(strip);
const ex = (exp[b.id] || []).sort(byDate).slice(0, CAP_BX).map(strip);
if (c.length || cl.length || ex.length) brokerDetail[b.id] = { current: c, closed: cl, expired: ex };
}
// Firm rollup — concat the firm's brokers' buckets (dedupe by address+city), cap, sort.
const firmOf = {}; brokers.forEach(b => { if (b.firm) firmOf[b.id] = b.firm; });
const firmAcc = {}; // firmLower -> {name, current, closed, expired}
for (const b of brokers) {
const f = b.firm; if (!f) continue;
const key = f.toLowerCase();
const acc = firmAcc[key] || (firmAcc[key] = { name: f, current: [], closed: [], expired: [] });
const d = brokerDetail[b.id]; if (!d) continue;
acc.current.push(...d.current); acc.closed.push(...d.closed); acc.expired.push(...d.expired);
}
const dedupe = (arr) => { const seen = new Set(); return arr.filter(r => { const k = (r.address || '') + '|' + (r.city || ''); if (seen.has(k)) return false; seen.add(k); return true; }); };
const firmDetail = {};
for (const [key, acc] of Object.entries(firmAcc)) {
const cur2 = dedupe(acc.current).sort(byPrice).slice(0, CAP_F);
const clo2 = dedupe(acc.closed).sort(byDate).slice(0, CAP_F);
const exp2 = dedupe(acc.expired).sort(byDate).slice(0, CAP_FX);
if (cur2.length || clo2.length || exp2.length) firmDetail[key] = { name: acc.name, current: cur2, closed: clo2, expired: exp2 };
}
console.log(` detail: ${Object.keys(brokerDetail).length} brokers, ${Object.keys(firmDetail).length} firms with property buckets`);
const out = { brokers, total: brokers.length, top, graph: graphData, enrichStats: enrich,
brokerDetail, firmDetail,
source: 'snapshot', exported_at: new Date().toISOString() };
const dest = path.join(__dirname, '..', 'data', 'brokers-snapshot.json');
fs.writeFileSync(dest, JSON.stringify(out));
const mb = fs.statSync(dest).size / 1e6;
console.log(`✔ wrote ${dest} — ${brokers.length} brokers (${mb.toFixed(1)}MB)`);
// Size-ceiling guard (DTD 2026-08-19, A-with-rider item 4): readBrokerSnap() loads this ENTIRE file
// into the prod Node heap, so runaway growth (more brokers × per-broker books) degrades every CRCP
// endpoint. Emit a PASS/WARN/FAIL verdict (fleet-health vocabulary): WARN past 30MB, FAIL past 60MB.
// The forward fix past the ceiling is to slice brokerDetail into a lazy-loaded sidecar, not the
// always-loaded snapshot.
const verdict = mb > 60 ? 'FAIL' : mb > 30 ? 'WARN' : 'PASS';
if (verdict !== 'PASS') console.warn(`⚠️ ${verdict}: broker snapshot is ${mb.toFixed(1)}MB (>${mb > 60 ? 60 : 30}MB) — it loads into the prod Node heap; move brokerDetail to a lazy sidecar before it grows further.`);
console.log(`snapshot-health: ${verdict} size=${mb.toFixed(1)}MB brokers=${brokers.length}`);
await pool.end();
}
if (require.main === module) main().catch(e => { console.error(e); process.exit(1); });