← back to Commercialrealestate
CRE residential agents: integrate into CRCP — fetch-redfin-agents.js capture, agent_type+license through /api/crcp/stats topBrokers + /api/broker (id-safe lookup, condos as listings) + /api/broker/pitch (condo refs for residential) + mind-map graph()/contact card; CRCP residential/commercial badge + Agents tile + agent counts in /api/condos/stats
3fb9caa81947a41eb7431a8d90bbb47f8f8d995b · 2026-06-28 08:10:44 -0700 · Steve
Files touched
M public/crcp.htmlM scripts/db/brokers-db.jsA scripts/fetch-redfin-agents.jsM scripts/serve.js
Diff
commit 3fb9caa81947a41eb7431a8d90bbb47f8f8d995b
Author: Steve <steve@designerwallcoverings.com>
Date: Sun Jun 28 08:10:44 2026 -0700
CRE residential agents: integrate into CRCP — fetch-redfin-agents.js capture, agent_type+license through /api/crcp/stats topBrokers + /api/broker (id-safe lookup, condos as listings) + /api/broker/pitch (condo refs for residential) + mind-map graph()/contact card; CRCP residential/commercial badge + Agents tile + agent counts in /api/condos/stats
---
public/crcp.html | 28 +++---
scripts/db/brokers-db.js | 13 ++-
scripts/fetch-redfin-agents.js | 190 +++++++++++++++++++++++++++++++++++++++++
scripts/serve.js | 63 +++++++++++---
4 files changed, 267 insertions(+), 27 deletions(-)
diff --git a/public/crcp.html b/public/crcp.html
index a34401a..03bc95f 100644
--- a/public/crcp.html
+++ b/public/crcp.html
@@ -33,6 +33,9 @@
th{color:var(--muted);font-size:11px;text-transform:uppercase;letter-spacing:.4px;border-top:0;}
td.n{text-align:right;font-variant-numeric:tabular-nums;color:var(--active);font-weight:600;}
.firm{color:var(--muted);font-size:11px;}
+ .atype{display:inline-block;font-size:10px;font-weight:700;letter-spacing:.4px;text-transform:uppercase;padding:1px 6px;border-radius:9px;margin-left:6px;vertical-align:middle;}
+ .atype.res{background:rgba(91,155,213,.16);color:var(--draft);border:1px solid rgba(91,155,213,.4);}
+ .atype.com{background:rgba(200,162,75,.14);color:var(--gold);border:1px solid rgba(200,162,75,.4);}
.has{color:var(--active);} .miss{color:var(--archived);}
.bar{height:8px;background:#0a0d13;border-radius:5px;overflow:hidden;margin-top:4px;}
.bar i{display:block;height:100%;background:linear-gradient(90deg,var(--gold),var(--active));}
@@ -91,30 +94,32 @@
<script>
const $=s=>document.querySelector(s);
const money=n=>n?'$'+Number(n).toLocaleString():'—';
-async function openContact(name){
+const atype=t=>t==='residential'?'<span class="atype res" title="Residential listing agent (Redfin)">Residential</span>':'<span class="atype com" title="Commercial broker (Crexi)">Commercial</span>';
+async function openContact(name, id){
const ov=$('#ov'), mb=$('#mbody'); ov.classList.add('on'); mb.innerHTML='<div class="meta">loading '+name+'…</div>';
- let d; try{ d=await (await fetch('/api/broker?name='+encodeURIComponent(name))).json(); }catch(e){ mb.innerHTML='error'; return; }
+ const qs = id ? 'id='+encodeURIComponent(id) : 'name='+encodeURIComponent(name);
+ let d; try{ d=await (await fetch('/api/broker?'+qs)).json(); }catch(e){ mb.innerHTML='error'; return; }
if(d.error){ mb.innerHTML='<div class="meta">'+d.error+'</div>'; return; }
const b=d.broker, w=b.website?(/^https?:/.test(b.website)?b.website:'https://'+b.website):null;
- mb.innerHTML=`<h2>${b.name}</h2><div class="mfirm">${b.firm||'—'}${b.title?' · '+b.title:''}</div>
+ mb.innerHTML=`<h2>${b.name}${atype(b.agent_type)}</h2><div class="mfirm">${b.firm||'—'}${b.title?' · '+b.title:''}${b.license?' · DRE/MLS '+b.license:''}</div>
<div class="cinfo">
${b.phone?`<a href="tel:${b.phone}">☎ ${b.phone}</a>`:'<span class="m">☎ —</span>'}
${b.email?`<a href="mailto:${b.email}">✉ ${b.email}</a>`:'<span class="m">✉ —</span>'}
${w?`<a href="${w}" target="_blank" rel="noopener noreferrer">🌐 website</a>`:''}
- <span>📦 ${b.total_assets||'?'} total listings (Crexi)</span>
+ <span>📦 ${b.agent_type==='residential'?(d.current.length+' condo listing'+(d.current.length===1?'':'s')+' (Redfin)'):((b.total_assets||'?')+' total listings (Crexi)')}</span>
</div>
<div class="sec"><h4>Current listings (${d.current.length})</h4>${d.current.length?d.current.map(l=>`<div class="litem"><span class="a">${l.address}, ${l.city}</span><span class="m">${l.type} · ${money(l.price)}${l.cap_rate?' · '+l.cap_rate+'% cap':''}</span></div>`).join(''):'<div class="meta">none in our set</div>'}</div>
<div class="sec"><h4>Closed listings — past 10 yrs (${d.closed.length})</h4>${d.closed.length?d.closed.map(l=>`<div class="litem"><span class="a">${l.address}, ${l.city}</span><span class="m">${money(l.sold_price)} · ${l.sold_date||''}</span></div>`).join(''):'<div class="meta">'+(d.closedNote||'none')+'</div>'}</div>
<div class="sec"><h4>Arcstone pitch (non-warrantable condo financing)</h4>
- <button class="btn" onclick="genPitch('${name.replace(/'/g,"\\'")}','email')">✉ Generate email pitch</button>
- <button class="btn alt" onclick="genPitch('${name.replace(/'/g,"\\'")}','call_script')">☎ Generate call script</button>
+ <button class="btn" onclick="genPitch('${name.replace(/'/g,"\\'")}','email',${b.id})">✉ Generate email pitch</button>
+ <button class="btn alt" onclick="genPitch('${name.replace(/'/g,"\\'")}','call_script',${b.id})">☎ Generate call script</button>
<div id="pitchout">${d.pitches&&d.pitches.length?renderPitch(d.pitches[0]):''}</div>
</div>`;
}
function renderPitch(p){ return `<div class="pitch">${p.subject?`<div class="subj">${p.subject}</div>`:''}${(p.body||'').replace(/&/g,'&').replace(/</g,'<')}</div><div class="gate">⚑ DRAFT — sending is George-gated + Steve-approved; not sent from here.</div>`; }
-async function genPitch(name,channel){
+async function genPitch(name,channel,id){
const out=$('#pitchout'); out.innerHTML='<div class="meta">drafting…</div>';
- try{ const r=await (await fetch('/api/broker/pitch',{method:'POST',headers:{'Content-Type':'application/json'},body:JSON.stringify({name,channel})})).json();
+ try{ const r=await (await fetch('/api/broker/pitch',{method:'POST',headers:{'Content-Type':'application/json'},body:JSON.stringify({name,channel,id})})).json();
out.innerHTML = r.error?('<div class="meta">'+r.error+'</div>'):renderPitch(r.pitch);
}catch(e){ out.innerHTML='<div class="meta">'+e.message+'</div>'; }
}
@@ -134,6 +139,7 @@ async function poll(){
tile('Brokers','brokers',s.brokers,(s.colist!=null?s.colist+' co-list links':''),'active'),
tile('Firms','firms',s.firms,'brokerages','draft'),
tile('Condos scraped','condos',s.condos,(s.condosByStatus?Object.entries(s.condosByStatus).map(([k,v])=>v+' '+k.replace('_',' ')).join(' · '):'awaiting Job A'),'staged'),
+ tile('Residential agents','agents_residential',s.agents_residential||0,((s.condos_with_agent||0)+' condos w/ agent · '+(s.agents_res_phone||0)+'☎ '+(s.agents_res_email||0)+'✉'),'draft'),
tile('FHA-approved','fhaApproved',s.fhaApproved,'warrantable (proxy)','active'),
tile('Broker edges','edges',s.edges,'broker↔listing','gold')
].join('');
@@ -148,13 +154,13 @@ async function poll(){
// top brokers
$('#brC').textContent=(s.topBrokers||[]).length+' shown · click a broker';
const web=u=>{ if(!u) return '<span class="miss">—</span>'; const h=/^https?:/.test(u)?u:'https://'+u; return `<a href="${h}" target="_blank" rel="noopener noreferrer" style="color:var(--gold)">site↗</a>`; };
- $('#brTbl tbody').innerHTML=(s.topBrokers||[]).map(b=>`<tr class="brow" data-name="${(b.name||'').replace(/"/g,'"')}" style="cursor:pointer">
- <td>${b.name}<div class="firm">${b.firm||'—'}</div></td>
+ $('#brTbl tbody').innerHTML=(s.topBrokers||[]).map(b=>`<tr class="brow" data-id="${b.id||''}" data-name="${(b.name||'').replace(/"/g,'"')}" style="cursor:pointer">
+ <td>${b.name}${atype(b.agent_type)}<div class="firm">${b.firm||'—'}</div></td>
<td class="n">${b.listings}</td>
<td>${b.phone?`<a href="tel:${b.phone}" class="has" style="color:var(--active)">${b.phone}</a>`:'<span class="miss">—</span>'}</td>
<td>${b.email?`<a href="mailto:${b.email}" class="has" style="color:var(--draft)">${b.email}</a>`:'<span class="miss">—</span>'}</td>
<td>${web(b.website)}</td></tr>`).join('');
- document.querySelectorAll('.brow').forEach(r=>r.onclick=e=>{ if(e.target.tagName==='A') return; openContact(r.dataset.name); });
+ document.querySelectorAll('.brow').forEach(r=>r.onclick=e=>{ if(e.target.tagName==='A') return; openContact(r.dataset.name, r.dataset.id); });
// warrantability
$('#wa').innerHTML = bar('FHA-approved', s.fhaApproved||0, (s.fhaApproved||0)+(s.fhaExpired||0), 'var(--active)')
+ bar('FHA-expired (flag/review)', s.fhaExpired||0, (s.fhaApproved||0)+(s.fhaExpired||0), 'var(--staged)');
diff --git a/scripts/db/brokers-db.js b/scripts/db/brokers-db.js
index 48028e5..7f4d785 100644
--- a/scripts/db/brokers-db.js
+++ b/scripts/db/brokers-db.js
@@ -62,7 +62,7 @@ async function graph(limit = 400) {
const nodes = [];
Object.entries(firms).forEach(([name, n]) => nodes.push({ id: 'firm:' + name, kind: 'firm', label: name, weight: n }));
brokers.forEach(b => nodes.push({ id: 'broker:' + b.id, kind: 'broker', dbId: b.id, label: b.name, firm: b.firm, listings: +b.listings, total: b.total_assets,
- phone: b.phone, email: b.email, website: b.website, linkedin: b.linkedin, office_addr: b.office_addr }));
+ agent_type: b.agent_type, phone: b.phone, email: b.email, website: b.website, linkedin: b.linkedin, office_addr: b.office_addr }));
const edges = [];
brokers.forEach(b => { if (b.firm) edges.push({ source: 'broker:' + b.id, target: 'firm:' + b.firm, kind: 'employs' }); });
co.forEach(e => edges.push({ source: 'broker:' + e.a, target: 'broker:' + e.b, kind: 'colist', weight: e.shared_listings }));
@@ -76,21 +76,26 @@ async function graph(limit = 400) {
}
async function topBrokers(limit = 50) {
- return (await pool.query(`SELECT name, firm, listings, phone, email, title FROM broker_node ORDER BY listings DESC NULLS LAST LIMIT $1`, [limit])).rows;
+ return (await pool.query(`SELECT name, firm, listings, phone, email, title, agent_type FROM broker_node ORDER BY listings DESC NULLS LAST LIMIT $1`, [limit])).rows;
}
// Full enriched contact card for one broker (id), with per-field provenance + listing book.
async function brokerContact(id) {
const b = (await pool.query(
`SELECT b.id, b.name, b.title, b.phone, b.email, b.website, b.linkedin, b.office_addr,
- b.total_assets, b.enriched_at, f.name AS firm
+ b.total_assets, b.enriched_at, b.agent_type, b.license, f.name AS firm
FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE b.id=$1`, [id])).rows[0];
if (!b) return null;
b.provenance = (await pool.query(
`SELECT field, source_url, tier FROM broker_field_source WHERE broker_id=$1`, [id])).rows;
+ // our_listings spans commercial listings + residential condos (so a residential agent shows their book).
b.our_listings = (await pool.query(
`SELECT l.id, l.address, l.city, l.type, l.price 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 LIMIT 50`, [id])).rows;
+ JOIN listing l ON l.id=bl.listing_id WHERE bl.broker_id=$1
+ UNION ALL
+ SELECT c.id, c.address, c.city, 'Condo' type, c.price FROM broker_condo bc
+ JOIN condo c ON c.id=bc.condo_id WHERE bc.broker_id=$1
+ ORDER BY price DESC NULLS LAST LIMIT 50`, [id])).rows;
b.other_listings = (await pool.query(
`SELECT title, address, city, state, price, asset_type, url FROM broker_other_listing
WHERE broker_id=$1 ORDER BY price DESC NULLS LAST LIMIT 50`, [id])).rows;
diff --git a/scripts/fetch-redfin-agents.js b/scripts/fetch-redfin-agents.js
new file mode 100644
index 0000000..99dfe3c
--- /dev/null
+++ b/scripts/fetch-redfin-agents.js
@@ -0,0 +1,190 @@
+// fetch-redfin-agents.js — FEED-FIRST capture of the RESIDENTIAL listing agent + brokerage for every
+// cre.condo (the 736 Redfin condos). Confirmed by scripts/probe-redfin-agents.js:
+//
+// GET /stingray/api/home/details/mainHouseInfoPanelInfo?propertyId=<id>&accessLevel=1
+// -> payload.mainHouseInfo.listingAgents[0]: agentInfo.agentName, brokerName, license,
+// agentPhoneNumber.phoneNumber, brokerPhoneNumber.phoneNumber, agentEmailAddress, brokerEmailAddress
+// (Redfin prefixes JSON with `{}&&`; email/phone present only when MLS exposes them publicly.)
+// listingAgents:[] => Redfin-listed, agent suppressed -> honest "no public agent", NOT fabricated.
+//
+// Feed-first: ONE warmed Browserbase session fetches the detail endpoint in-page for MANY propertyIds
+// (land on the listing URL first so the detail feed returns populated agent data). NO 736 page-loads.
+//
+// HARD CAP (Steve-approved): stop at MAX_SESSIONS (30) sessions OR ~$1.50 spend, whichever first.
+// Per-session cost + RUNNING BATCH TOTAL surfaced live to stderr. Business contact ONLY. NO send.
+//
+// Usage: NODE_PATH=$HOME/.claude/skills/browserbase/node_modules node scripts/fetch-redfin-agents.js
+// CC_MAX_SESSIONS=30 CC_MAX_COST=1.50 CC_PER_SESSION=40 (propertyIds per warmed session)
+'use strict';
+const fs = require('fs');
+const path = require('path');
+const { chromium } = require('playwright-core');
+const Browserbase = require('@browserbasehq/sdk').default;
+const brokerdb = require('./db/brokers-db');
+
+const env = fs.readFileSync(process.env.HOME + '/.claude/skills/browserbase/.env', 'utf8');
+const get = (t, k) => (t.match(new RegExp('^' + k + '=(.*)$', 'm')) || [])[1]?.replace(/['"]/g, '').trim();
+const KEY = get(env, 'BROWSERBASE_API_KEY'), PROJECT = get(env, 'BROWSERBASE_PROJECT_ID');
+
+const SESSION_COST = 0.04;
+const MAX_SESSIONS = +(process.env.CC_MAX_SESSIONS || 30);
+const MAX_COST = +(process.env.CC_MAX_COST || 1.50);
+const PER_SESSION = +(process.env.CC_PER_SESSION || 40); // propertyIds per warmed session
+const ROOT = path.join(__dirname, '..');
+const OUT = path.join(ROOT, 'data', 'redfin-agents.json');
+
+const strip = s => s.replace(/^[)\]}'&\s]*\{\}&&/, '').replace(/^[)\]}'\s]+/, '');
+const pn = v => (v && typeof v === 'object' ? v.phoneNumber : v) || null; // phone object -> string
+
+// Pull the residential listing agent out of a mainHouseInfo payload. Returns null when suppressed.
+function extractAgent(payloadText) {
+ let j; try { j = JSON.parse(strip(payloadText)); } catch { return { parseErr: true }; }
+ const mh = (j.payload && j.payload.mainHouseInfo) || (j.mainHouseInfo) || null;
+ if (!mh) return { noPayload: true };
+ const la = Array.isArray(mh.listingAgents) ? mh.listingAgents[0] : null;
+ if (!la) return { suppressed: true };
+ const ai = la.agentInfo || {};
+ const name = (ai.agentName || '').trim();
+ if (!name || ai.isAgentNameBlank) return { suppressed: true };
+ return {
+ name,
+ brokerage: (la.brokerName || '').trim() || null,
+ license: (la.license || '').trim() || null,
+ phone: pn(la.agentPhoneNumber) || pn(la.brokerPhoneNumber) || null,
+ email: (la.agentEmailAddress || la.brokerEmailAddress || '').trim() || null,
+ isRedfinAgent: !!ai.isRedfinAgent
+ };
+}
+
+async function loadTargets() {
+ // Every condo with a parseable Redfin propertyId. broker_condo dedup happens at upsert time, but
+ // we skip condos already linked so reruns don't re-spend sessions on already-captured agents.
+ const r = await brokerdb.pool.query(`
+ SELECT c.id, c.source,
+ regexp_replace(c.source, '.*/home/([0-9]+).*', '\\1') AS pid,
+ c.address, c.city
+ FROM condo c
+ WHERE c.source ~ '/home/[0-9]+'
+ AND NOT EXISTS (SELECT 1 FROM broker_condo bc WHERE bc.condo_id = c.id)
+ ORDER BY c.city, c.id`);
+ return r.rows.filter(x => x.pid && x.pid !== x.source);
+}
+
+async function persistAgent(condo, a, sourceUrl) {
+ const firmId = a.brokerage ? await brokerdb.upsertFirm(a.brokerage) : null;
+ // Insert/refresh the broker as a RESIDENTIAL agent. upsertBroker keys on (name, firm_id); we set
+ // agent_type/license/website directly so commercial rows are untouched.
+ const r = await brokerdb.pool.query(
+ `INSERT INTO broker(name, firm_id, phone, email, source, agent_type, license)
+ VALUES($1,$2,$3,$4,'redfin','residential',$5)
+ ON CONFLICT(name, firm_id) DO UPDATE SET
+ phone=COALESCE(broker.phone, EXCLUDED.phone),
+ email=COALESCE(broker.email, EXCLUDED.email),
+ license=COALESCE(broker.license, EXCLUDED.license),
+ agent_type='residential'
+ RETURNING id`,
+ [a.name, firmId, a.phone, a.email, a.license]);
+ const brokerId = r.rows[0].id;
+
+ await brokerdb.pool.query(
+ `INSERT INTO broker_condo(broker_id, condo_id, role) VALUES($1,$2,'listing')
+ ON CONFLICT DO NOTHING`, [brokerId, condo.id]);
+
+ await brokerdb.pool.query(
+ `UPDATE condo SET broker_name=$2, firm_name=$3, firm_id=$4 WHERE id=$1`,
+ [condo.id, a.name, a.brokerage, firmId]);
+
+ // Per-field provenance (CCPA audit trail), tier 'redfin-detail'.
+ const prov = [];
+ if (a.phone) prov.push(['phone', a.phone]);
+ if (a.email) prov.push(['email', a.email]);
+ if (a.license) prov.push(['license', a.license]);
+ for (const [field, value] of prov) {
+ await brokerdb.pool.query(
+ `INSERT INTO broker_field_source(broker_id, field, value, source_url, tier)
+ VALUES($1,$2,$3,$4,'redfin-detail')
+ ON CONFLICT(broker_id, field) DO UPDATE SET value=EXCLUDED.value, source_url=EXCLUDED.source_url, tier=EXCLUDED.tier`,
+ [brokerId, field, value, sourceUrl]).catch(() => {});
+ }
+ return brokerId;
+}
+
+(async () => {
+ const targets = await loadTargets();
+ process.stderr.write(`Condos needing an agent: ${targets.length} (PER_SESSION=${PER_SESSION}, cap ${MAX_SESSIONS} sessions / $${MAX_COST.toFixed(2)})\n`);
+ if (!targets.length) { console.log(JSON.stringify({ note: 'all condos already have an agent linked', captured: 0 })); await brokerdb.pool.end(); return; }
+
+ const results = [];
+ const summary = { captured: 0, withPhone: 0, withEmail: 0, withLicense: 0, suppressed: 0, errors: 0 };
+ let sessions = 0;
+
+ for (let g = 0; g < targets.length; g += PER_SESSION) {
+ if (sessions >= MAX_SESSIONS) { process.stderr.write(`\n[CAP] ${MAX_SESSIONS}-session cap reached; stopping.\n`); break; }
+ if (sessions * SESSION_COST >= MAX_COST) { process.stderr.write(`\n[CAP] $${MAX_COST.toFixed(2)} cost cap reached; stopping.\n`); break; }
+ const group = targets.slice(g, g + PER_SESSION);
+ let browser, session;
+ sessions++;
+ const running = (sessions * SESSION_COST).toFixed(2);
+ process.stderr.write(`\n[session ${sessions}/${MAX_SESSIONS}] ${group.length} condos (per-session $${SESSION_COST.toFixed(2)} · running batch total $${running} / cap $${MAX_COST.toFixed(2)})\n`);
+
+ try {
+ const bb = new Browserbase({ apiKey: KEY });
+ session = await bb.sessions.create({ projectId: PROJECT, browserSettings: { solveCaptchas: true, viewport: { width: 1440, height: 1000 } } });
+ browser = await chromium.connectOverCDP(session.connectUrl);
+ const ctx = browser.contexts()[0];
+ const page = ctx.pages()[0] || await ctx.newPage();
+ page.setDefaultTimeout(45000);
+ await page.goto('https://www.redfin.com/city/11203/CA/Los-Angeles/filter/property-type=condo', { waitUntil: 'domcontentloaded' }).catch(() => {});
+ await page.waitForTimeout(2000);
+
+ for (const t of group) {
+ try {
+ // Warm-up: land on the listing page so the detail feed returns populated agent data.
+ await page.goto(t.source, { waitUntil: 'domcontentloaded' }).catch(() => {});
+ await page.waitForTimeout(900);
+ const u = `https://www.redfin.com/stingray/api/home/details/mainHouseInfoPanelInfo?propertyId=${t.pid}&accessLevel=1`;
+ const res = await page.evaluate(async (u) => {
+ const r = await fetch(u, { headers: { accept: 'application/json' } });
+ return { status: r.status, text: await r.text() };
+ }, u);
+ if (res.status !== 200 || !res.text) { summary.errors++; results.push({ condo: t.id, pid: t.pid, status: res.status, error: 'non-200' }); continue; }
+ const a = extractAgent(res.text);
+ if (a.suppressed || a.noPayload || a.parseErr) {
+ summary.suppressed++;
+ results.push({ condo: t.id, pid: t.pid, agent: null, label: 'no public agent (Redfin-listed / suppressed)' });
+ continue;
+ }
+ await persistAgent(t, a, t.source);
+ summary.captured++;
+ if (a.phone) summary.withPhone++;
+ if (a.email) summary.withEmail++;
+ if (a.license) summary.withLicense++;
+ results.push({ condo: t.id, pid: t.pid, agent: a.name, brokerage: a.brokerage, phone: !!a.phone, email: !!a.email });
+ process.stderr.write(` ${t.address}, ${t.city}: ${a.name} / ${a.brokerage || '?'}${a.phone ? ' ☎' : ''}${a.email ? ' ✉' : ''}\n`);
+ await page.waitForTimeout(500);
+ } catch (e) { summary.errors++; results.push({ condo: t.id, pid: t.pid, error: String(e.message).slice(0, 80) }); }
+ }
+ } catch (e) {
+ process.stderr.write(' session err: ' + e.message.split('\n')[0] + '\n');
+ } finally { if (browser) try { await browser.close(); } catch (_) {} }
+ }
+
+ const actualCost = (sessions * SESSION_COST).toFixed(2);
+ fs.writeFileSync(OUT, JSON.stringify({
+ meta: {
+ source: 'Redfin per-property detail feed (mainHouseInfoPanelInfo), via warmed Browserbase session',
+ endpoint: '/stingray/api/home/details/mainHouseInfoPanelInfo?propertyId=<id>&accessLevel=1',
+ sessions_used: sessions,
+ cost: `~$${actualCost} (${sessions} Browserbase sessions @ $${SESSION_COST.toFixed(2)})`,
+ cap: `HARD CAP ${MAX_SESSIONS} sessions / $${MAX_COST.toFixed(2)}`,
+ label: 'Business-contact only (agent name / brokerage / business phone / business email / license). Suppressed agents honestly labeled, not fabricated.',
+ fetched_at: new Date().toISOString(),
+ summary
+ },
+ results
+ }, null, 2));
+
+ process.stderr.write(`\nSessions ${sessions} · ACTUAL COST ~$${actualCost}\n`);
+ console.log(JSON.stringify({ ...summary, sessions, cost: '$' + actualCost, endpoint: 'mainHouseInfoPanelInfo' }));
+ await brokerdb.pool.end();
+})();
diff --git a/scripts/serve.js b/scripts/serve.js
index 9161e01..cec60dd 100644
--- a/scripts/serve.js
+++ b/scripts/serve.js
@@ -168,10 +168,16 @@ app.get('/api/condos/stats', async (req, res) => {
`SELECT warrantable_status s, round(avg(price))::bigint avg_price, count(*)::int n
FROM condo WHERE price>0 GROUP BY 1`)).rows;
const total = (await brokerdb.pool.query(`SELECT count(*)::int n FROM condo`)).rows[0].n;
+ const [agents] = (await brokerdb.pool.query(`SELECT
+ (SELECT count(DISTINCT condo_id) FROM broker_condo)::int condos_with_agent,
+ (SELECT count(*) FROM broker WHERE agent_type='residential')::int residential_agents,
+ (SELECT count(*) FROM broker WHERE agent_type='residential' AND phone IS NOT NULL)::int agents_phone,
+ (SELECT count(*) FROM broker WHERE agent_type='residential' AND email IS NOT NULL)::int agents_email`).catch(() => ({ rows: [{}] }))).rows;
res.json({
- total, byStatus, byCity, price,
+ total, byStatus, byCity, price, agents,
label: 'FHA/VA-approval-based proxy, NOT lender-verified Fannie/Freddie warrantability.',
- source: 'Redfin LA County condo listings (feed-first gis-csv) classified against the HUD FHA list'
+ source: 'Redfin LA County condo listings (feed-first gis-csv) classified against the HUD FHA list',
+ agentSource: 'Residential listing agents captured per-condo from Redfin detail feed (mainHouseInfoPanelInfo) — business contact only'
});
} catch (e) { res.status(502).json({ error: String(e.message).split('\n')[0] }); }
});
@@ -217,15 +223,23 @@ app.get('/api/crcp/stats', async (req, res) => {
(SELECT count(*) FROM broker WHERE phone IS NOT NULL)::int brokers_phone,
(SELECT count(*) FROM broker WHERE email IS NOT NULL)::int brokers_email,
(SELECT count(*) FROM broker_listing)::int edges,
- (SELECT count(*) FROM broker_cobroker)::int colist`);
+ (SELECT count(*) FROM broker_cobroker)::int colist,
+ (SELECT count(*) FROM broker WHERE agent_type='residential')::int agents_residential,
+ (SELECT count(*) FROM broker WHERE agent_type='commercial')::int agents_commercial,
+ (SELECT count(*) FROM broker WHERE agent_type='residential' AND phone IS NOT NULL)::int agents_res_phone,
+ (SELECT count(*) FROM broker WHERE agent_type='residential' AND email IS NOT NULL)::int agents_res_email,
+ (SELECT count(DISTINCT condo_id) FROM broker_condo)::int condos_with_agent`).catch(() => [{}]);
Object.assign(out, c1);
try { out.brokers_web = (await q(`SELECT count(*)::int n FROM broker WHERE website IS NOT NULL`))[0].n; } catch (_) { out.brokers_web = null; }
try { out.condos = (await q(`SELECT count(*)::int n FROM condo`))[0].n;
out.condosByStatus = {}; (await q(`SELECT warrantable_status s,count(*)::int n FROM condo GROUP BY 1`)).forEach(r => out.condosByStatus[r.s] = r.n); } catch (_) { out.condos = 0; }
- const brokerCols = 'b.name, f.name firm, count(DISTINCT bl.listing_id)::int listings, b.total_assets, b.phone, b.email' +
+ const brokerCols = 'b.id, b.name, f.name firm, b.agent_type, ' +
+ '(count(DISTINCT bl.listing_id) + count(DISTINCT bc.condo_id))::int listings, b.total_assets, b.phone, b.email' +
(out.brokers_web != null ? ', b.website' : '');
out.topBrokers = await q(`SELECT ${brokerCols}
- FROM broker b LEFT JOIN firm f ON f.id=b.firm_id LEFT JOIN broker_listing bl ON bl.broker_id=b.id
+ FROM broker b LEFT JOIN firm f ON f.id=b.firm_id
+ LEFT JOIN broker_listing bl ON bl.broker_id=b.id
+ LEFT JOIN broker_condo bc ON bc.broker_id=b.id
GROUP BY b.id, f.name ORDER BY listings DESC NULLS LAST LIMIT 20`);
out.topFirms = await q(`SELECT f.name firm, count(DISTINCT bl.listing_id)::int listings, count(DISTINCT b.id)::int brokers FROM firm f JOIN broker b ON b.firm_id=f.id JOIN broker_listing bl ON bl.broker_id=b.id GROUP BY 1 ORDER BY 2 DESC LIMIT 12`);
}
@@ -237,17 +251,30 @@ app.get('/api/crcp/stats', async (req, res) => {
// Legitimate B2B prep only (no personal/family data). Click-through target for the CRCP + mind-map.
app.get('/api/broker', async (req, res) => {
if (!brokerdb) return res.status(503).json({ error: 'broker DB unavailable' });
+ const id = req.query.id ? +req.query.id : null;
const name = String(req.query.name || '').trim();
- if (!name) return res.status(400).json({ error: 'name required' });
+ if (!id && !name) return res.status(400).json({ error: 'id or name required' });
try {
const q = (s, a) => brokerdb.pool.query(s, a).then(r => r.rows);
- const brokers = await q(`SELECT b.id, b.name, f.name firm, b.phone, b.email, b.title, b.total_assets, b.website
- FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE b.name ILIKE $1 ORDER BY b.id LIMIT 1`, [name]).catch(() =>
- q(`SELECT b.id, b.name, f.name firm, b.phone, b.email, b.title, b.total_assets FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE b.name ILIKE $1 LIMIT 1`, [name]));
+ // Prefer id (unambiguous — names collide across commercial/residential). For a name lookup,
+ // resolve deterministically: the row with the most listings (commercial edges + condos) wins.
+ const sel = `SELECT b.id, b.name, f.name firm, b.phone, b.email, b.title, b.total_assets, b.website, b.agent_type, b.license`;
+ const brokers = id
+ ? await q(`${sel} FROM broker b LEFT JOIN firm f ON f.id=b.firm_id WHERE b.id=$1`, [id])
+ : await q(`${sel},
+ (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 _rank
+ FROM broker b LEFT JOIN firm f ON f.id=b.firm_id
+ WHERE b.name ILIKE $1 ORDER BY _rank DESC, b.id LIMIT 1`, [name]);
const broker = brokers[0];
if (!broker) return res.status(404).json({ error: 'broker not found' });
+ // Current listings = commercial listings (broker_listing) UNION residential condos (broker_condo).
const current = await q(`SELECT l.id, l.address, l.city, l.type, l.price, l.cap_rate, bl.role
- 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`, [broker.id]);
+ FROM broker_listing bl JOIN listing l ON l.id=bl.listing_id WHERE bl.broker_id=$1
+ UNION ALL
+ SELECT c.id, c.address, c.city, 'Condo' type, c.price, NULL cap_rate, bc.role
+ FROM broker_condo bc JOIN condo c ON c.id=bc.condo_id WHERE bc.broker_id=$1
+ ORDER BY price DESC NULLS LAST`, [broker.id]);
const closed = await q(`SELECT address, city, sold_price, sold_date, type, source FROM broker_closed_listing WHERE broker_id=$1 ORDER BY sold_date DESC NULLS LAST`, [broker.id]).catch(() => []);
const pitches = await q(`SELECT id, channel, subject, body, status, created_at FROM broker_pitch WHERE broker_id=$1 ORDER BY created_at DESC`, [broker.id]).catch(() => []);
res.json({ broker, current, closed, pitches, closedNote: closed.length ? null : 'Public 10-yr closed-sales enrichment not yet run (gated public-records pull).' });
@@ -259,14 +286,26 @@ app.get('/api/broker', async (req, res) => {
// outreach is George-gated + Steve-approved (deliberately).
app.post('/api/broker/pitch', async (req, res) => {
if (!brokerdb) return res.status(503).json({ error: 'broker DB unavailable' });
+ const bid = (req.body || {}).id ? +(req.body || {}).id : null;
const name = String((req.body || {}).name || '').trim();
const channel = (req.body || {}).channel === 'call_script' ? 'call_script' : 'email';
try {
const q = (s, a) => brokerdb.pool.query(s, a).then(r => r.rows);
- const broker = (await q(`SELECT id, name, firm_id FROM broker WHERE name ILIKE $1 LIMIT 1`, [name]))[0];
+ // Resolve the SAME row /api/broker shows (id wins; else most-listings row for a colliding name).
+ const broker = bid
+ ? (await q(`SELECT id, name, firm_id, agent_type FROM broker WHERE id=$1`, [bid]))[0]
+ : (await q(`SELECT id, name, firm_id, agent_type FROM broker b WHERE name ILIKE $1
+ ORDER BY ((SELECT count(*) FROM broker_listing bl WHERE bl.broker_id=b.id)
+ +(SELECT count(*) FROM broker_condo bc WHERE bc.broker_id=b.id)) DESC, b.id LIMIT 1`, [name]))[0];
if (!broker) return res.status(404).json({ error: 'broker not found' });
const firm = (await q(`SELECT name FROM firm WHERE id=$1`, [broker.firm_id]))[0]?.name || 'your brokerage';
- const listings = await q(`SELECT l.address, l.city, l.type 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 LIMIT 3`, [broker.id]);
+ // refs pull from BOTH commercial listings + residential condos so the pitch references real work.
+ // The Arcstone non-warrantable-condo pitch is MOST relevant to residential condo agents.
+ const listings = await q(`SELECT address, city, type FROM (
+ SELECT l.address, l.city, l.type, l.price FROM broker_listing bl JOIN listing l ON l.id=bl.listing_id WHERE bl.broker_id=$1
+ UNION ALL
+ SELECT c.address, c.city, 'condo' type, c.price FROM broker_condo bc JOIN condo c ON c.id=bc.condo_id WHERE bc.broker_id=$1
+ ) x ORDER BY price DESC NULLS LAST LIMIT 3`, [broker.id]);
const progs = await q(`SELECT title, detail FROM arcstone_resource WHERE kind='program' ORDER BY id`);
const contact = (await q(`SELECT detail FROM arcstone_resource WHERE kind='contact' LIMIT 1`))[0]?.detail || '';
const first = broker.name.split(/\s+/)[0];
← a686d47 CRE: closed-sales scraper (Redfin gis-csv sold feed, 10yr) →
·
back to Commercialrealestate
·
auto-save: 2026-06-28T08:13:35 (2 files) — scripts/fetch-clo 8e18085 →