← back to Commercialrealestate
refactor(agent-profile): extract shared helpers (agentOverlayCondos/fetchCondoCards/mergeListings/sumPrice/distinctCities) — de-duplicate the condo-overlay scan that was copy-pasted across the found/not-found branches, drop the stale name-match comment; behavior-identical (verified same API output + 5x click-through)
d81c51055c3a6be501eac9d5b6c02208a9e68add · 2026-08-19 10:19:40 -0700 · Steve Abrams
Files touched
Diff
commit d81c51055c3a6be501eac9d5b6c02208a9e68add
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Aug 19 10:19:40 2026 -0700
refactor(agent-profile): extract shared helpers (agentOverlayCondos/fetchCondoCards/mergeListings/sumPrice/distinctCities) — de-duplicate the condo-overlay scan that was copy-pasted across the found/not-found branches, drop the stale name-match comment; behavior-identical (verified same API output + 5x click-through)
---
scripts/serve.js | 93 +++++++++++++++++++++++++++++---------------------------
1 file changed, 48 insertions(+), 45 deletions(-)
diff --git a/scripts/serve.js b/scripts/serve.js
index 7489d07..9b61cd6 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -340,6 +340,34 @@ app.get('/api/brokers/contact/:id', async (req, res) => {
// listings, built from OUR data, NEVER crexi"). Resolves the broker by name from our graph and
// returns their contact + the listings WE have for them (broker_listing -> listing). Deliberately
// omits crexi_id / source / any aggregator link — this is our own record, keyed only by name.
+// ── /api/agent-profile helpers (shared by the broker-graph and condo-overlay paths) ──
+// The condo↔agent linkage lives in the id-keyed condoBrokers() overlay (it overwrites
+// condo_card.broker_name), NOT a name-queryable column — so scan the overlay by name to recover EVERY
+// condo an agent is on (primary OR co-listing) plus their overlay contact. Returns { ids, contact }.
+function agentOverlayCondos(name) {
+ const nlc = String(name || '').toLowerCase(); const ids = []; let contact = null;
+ if (!nlc) return { ids, contact };
+ const ov = condoBrokers();
+ for (const cid of Object.keys(ov)) {
+ const e = ov[cid] || {};
+ const ae = Array.isArray(e.agents) ? e.agents.find(a => a && String(a.name || '').toLowerCase() === nlc) : null;
+ if ((e.broker_name && String(e.broker_name).toLowerCase() === nlc) || ae) {
+ ids.push(cid);
+ if (!contact) contact = { firm: (ae && ae.firm) || e.firm_name || null, dre: (ae && ae.dre) || e.broker_dre || null, phone: e.agent_phone || null, email: e.agent_email || null };
+ }
+ }
+ return { ids, contact };
+}
+const fetchCondoCards = async (ids) => (!brokerdb || !ids || !ids.length) ? [] : (await brokerdb.pool.query(
+ `SELECT address, city, zip, 'Condo' AS type, price, NULL::int AS units, NULL::numeric AS cap_rate
+ FROM condo_card WHERE id::text = ANY($1::text[]) ORDER BY price DESC NULLS LAST`, [ids]).catch(() => ({ rows: [] }))).rows;
+// Merge listing sources → dedupe by address+city → sort by price desc.
+const mergeListings = (...sources) => { const seen = new Set(); return [].concat(...sources)
+ .filter(l => { const k = (l.address || '') + '|' + (l.city || ''); if (seen.has(k)) return false; seen.add(k); return true; })
+ .sort((x, y) => Number(y.price || 0) - Number(x.price || 0)); };
+const sumPrice = (arr) => arr.reduce((s, l) => s + Number(l.price || 0), 0);
+const distinctCities = (arr) => [...new Set(arr.map(l => l.city).filter(Boolean))];
+
app.get('/api/agent-profile', async (req, res) => {
const id = req.query.id ? +req.query.id : null; // exact resolution — names collide across agents
const name = String((req.query.name || '')).trim();
@@ -348,6 +376,7 @@ app.get('/api/agent-profile', async (req, res) => {
try {
const sel = `SELECT b.id, b.name, f.name AS firm, b.phone, b.email, b.title, b.agent_type, b.license, b.office_addr, b.linkedin
FROM broker b LEFT JOIN firm f ON f.id = b.firm_id`;
+ // Resolve by id (exact) or by name (the same-named broker WITH the most listings wins, then contactable).
const b = (await brokerdb.pool.query(
id ? `${sel} WHERE b.id = $1 LIMIT 1`
: `${sel} WHERE lower(b.name) = lower($1)
@@ -355,74 +384,48 @@ app.get('/api/agent-profile', async (req, res) => {
+ (SELECT count(*) FROM broker_condo bc WHERE bc.broker_id=b.id)) DESC,
(b.phone IS NOT NULL OR b.email IS NOT NULL) DESC NULLS LAST LIMIT 1`,
[id || name])).rows[0];
+
+ // Not in the broker graph → recover an overlay-only profile (residential listing agents live solely
+ // in condoBrokers()), so their page still shows their condos + contact instead of an empty page.
if (!b) {
- // The agent may exist ONLY in the condo overlay (a residential listing agent not in the broker
- // graph). Recover their profile + condos from condoBrokers() so their page isn't empty.
- const ov = condoBrokers(); const nlc = name.toLowerCase(); let contact = null;
- const ids = name ? Object.keys(ov).filter(cid => { const e = ov[cid] || {};
- const ae = Array.isArray(e.agents) ? e.agents.find(a => a && String(a.name || '').toLowerCase() === nlc) : null;
- const hit = (e.broker_name && String(e.broker_name).toLowerCase() === nlc) || !!ae;
- if (hit && !contact) contact = { firm: (ae && ae.firm) || e.firm_name || null, dre: (ae && ae.dre) || e.broker_dre || null, phone: e.agent_phone || null, email: e.agent_email || null };
- return hit; }) : [];
+ const { ids, contact } = agentOverlayCondos(name);
if (!ids.length) return res.json({ name, firm: null, listings: [], found: false });
- const oc = (await brokerdb.pool.query(
- `SELECT address, city, zip, 'Condo' AS type, price, NULL::int AS units, NULL::numeric AS cap_rate
- FROM condo_card WHERE id::text = ANY($1::text[]) ORDER BY price DESC NULLS LAST`, [ids]).catch(() => ({ rows: [] }))).rows;
+ const listings = await fetchCondoCards(ids);
return res.json({ found: true, id: null, name, firm: contact && contact.firm, phone: contact && contact.phone,
email: contact && contact.email, title: null, agent_type: 'residential', license: contact && contact.dre, office: null, linkedin: null,
- listings: oc, count: oc.length, closed: [], closed_count: 0, firm_listings: [], firm_count: 0,
- total_value: oc.reduce((s, l) => s + Number(l.price || 0), 0), closed_value: 0,
- cities: [...new Set(oc.map(l => l.city).filter(Boolean))], source: 'condo-overlay' });
+ listings, count: listings.length, closed: [], closed_count: 0, firm_listings: [], firm_count: 0,
+ total_value: sumPrice(listings), closed_value: 0, cities: distinctCities(listings), source: 'condo-overlay' });
}
+
+ // Broker-graph agent: their book spans commercial (broker_listing) + condo edges (broker_condo) +
+ // condos linked only via the overlay by name/co-agent. Merge + dedupe all three.
const commercial = (await brokerdb.pool.query(
`SELECT l.address, l.city, l.zip, l.type, l.price, l.units, l.cap_rate
FROM broker_listing bl JOIN listing l ON l.id = bl.listing_id
WHERE bl.broker_id = $1 ORDER BY l.price DESC NULLS LAST`, [b.id])).rows;
- // Condo listings — a residential agent's book lives in broker_condo → condo (NOT broker_listing),
- // so an agent surfaced from condos.html would otherwise show an empty page. Mirrors /api/firm.
- const condoRows = (await brokerdb.pool.query(
+ const condoEdge = (await brokerdb.pool.query(
`SELECT c.address, c.city, NULL::text AS zip, 'Condo' AS type, c.price, NULL::int AS units, NULL::numeric AS cap_rate
FROM broker_condo bc JOIN condo c ON c.id = bc.condo_id
WHERE bc.broker_id = $1 ORDER BY c.price DESC NULLS LAST`, [b.id]).catch(() => ({ rows: [] }))).rows;
- // Name-match fallback: broker↔listing edges are sparse; a condo often stores the agent only as a
- // broker_name STRING (no broker_condo row). So also pull condos where broker_name matches this agent,
- // so a named-but-unlinked agent (seen on condos.html) still shows their book. Deduped by address+city.
- // The condo↔agent linkage lives in the id-keyed condoBrokers() overlay (condo_card.broker_name is
- // overwritten by it), so a name-column match misses co-listing agents. Scan the overlay by name to
- // recover EVERY condo this agent is on (primary or co-agent), then fetch those cards.
- let overlayCondos = [];
- try {
- const ov = condoBrokers(); const nlc = String(b.name || '').toLowerCase();
- const ids = b.name ? Object.keys(ov).filter(cid => { const e = ov[cid] || {};
- return (e.broker_name && String(e.broker_name).toLowerCase() === nlc)
- || (Array.isArray(e.agents) && e.agents.some(a => a && String(a.name || '').toLowerCase() === nlc)); }) : [];
- if (ids.length) overlayCondos = (await brokerdb.pool.query(
- `SELECT address, city, zip, 'Condo' AS type, price, NULL::int AS units, NULL::numeric AS cap_rate
- FROM condo_card WHERE id::text = ANY($1::text[]) ORDER BY price DESC NULLS LAST`, [ids]).catch(() => ({ rows: [] }))).rows;
- } catch (_) {}
- const seen = new Set();
- const listings = commercial.concat(condoRows, overlayCondos)
- .filter(l => { const k = (l.address || '') + '|' + (l.city || ''); if (seen.has(k)) return false; seen.add(k); return true; })
- .sort((x, y) => Number(y.price || 0) - Number(x.price || 0));
- // Past / closed (sold) listings — the agent's track record, mirrors /api/broker.
+ const overlayCondos = await fetchCondoCards(agentOverlayCondos(b.name).ids);
+ const listings = mergeListings(commercial, condoEdge, overlayCondos);
+
+ // Past/closed (sold) track record + firm-site inventory (labeled "Firm listings", NOT this agent's book).
const closed = (await brokerdb.pool.query(
`SELECT address, city, sold_price, sold_date, type, source
FROM broker_closed_listing WHERE broker_id = $1 ORDER BY sold_date DESC NULLS LAST`, [b.id]).catch(() => ({ rows: [] }))).rows;
- // FIRM listings — direct-from-firm-site inventory (openclaw+LLM, source=broker-site-oc), matched
- // by the agent's firm. Shown labeled "Firm listings", NOT claimed as this agent's personal book.
const firmListings = b.firm ? (await brokerdb.pool.query(
`SELECT address, city, zip, type, price, units, cap_rate
- FROM listing WHERE source='broker-site-oc' AND lower(firm_name)=lower($1)
- ORDER BY price DESC NULLS LAST`, [b.firm]).catch(() => ({ rows: [] }))).rows : [];
+ FROM listing WHERE source='broker-site-oc' AND lower(firm_name)=lower($1) ORDER BY price DESC NULLS LAST`, [b.firm]).catch(() => ({ rows: [] }))).rows : [];
+
res.json({
found: true, id: b.id, name: b.name, firm: b.firm, phone: b.phone, email: b.email,
title: b.title, agent_type: b.agent_type, license: b.license, office: b.office_addr, linkedin: b.linkedin,
listings, count: listings.length,
closed, closed_count: closed.length,
firm_listings: firmListings, firm_count: firmListings.length,
- total_value: listings.reduce((s, l) => s + Number(l.price || 0), 0),
- closed_value: closed.reduce((s, l) => s + Number(l.sold_price || 0), 0),
- cities: [...new Set(listings.map(l => l.city).concat(closed.map(l => l.city)).filter(Boolean))],
+ total_value: sumPrice(listings), closed_value: closed.reduce((s, l) => s + Number(l.sold_price || 0), 0),
+ cities: distinctCities(listings.concat(closed)),
});
} catch (e) { res.json({ name, firm: null, listings: [], found: false, error: String(e.message).split('\n')[0] }); }
});
← fb68fcb CRCP: backfill broker contacts for Lyon Stahl/WESTMAC/BuildO
·
back to Commercialrealestate
·
CRCP: broker-phone OVERLAY for Crexi-base rows — free usre s 3c7ce7f →