← back to Commercialrealestate
CRE residential agents: feasibility probe (mainHouseInfo endpoint confirms agent name+brokerage+phone/email/license) + residential schema (broker.agent_type/license, broker_condo edge, extended broker_node view)
2d837a22fcfb1ecc5919ca2a9c42582022489a3f · 2026-06-28 08:00:42 -0700 · Steve
Files touched
A scripts/db/agent-residential-schema.sqlA scripts/probe-redfin-agents.js
Diff
commit 2d837a22fcfb1ecc5919ca2a9c42582022489a3f
Author: Steve <steve@designerwallcoverings.com>
Date: Sun Jun 28 08:00:42 2026 -0700
CRE residential agents: feasibility probe (mainHouseInfo endpoint confirms agent name+brokerage+phone/email/license) + residential schema (broker.agent_type/license, broker_condo edge, extended broker_node view)
---
scripts/db/agent-residential-schema.sql | 41 ++++++++++++
scripts/probe-redfin-agents.js | 109 ++++++++++++++++++++++++++++++++
2 files changed, 150 insertions(+)
diff --git a/scripts/db/agent-residential-schema.sql b/scripts/db/agent-residential-schema.sql
new file mode 100644
index 0000000..0517d9a
--- /dev/null
+++ b/scripts/db/agent-residential-schema.sql
@@ -0,0 +1,41 @@
+-- agent-residential-schema.sql — adds RESIDENTIAL real-estate agents to the unified person model.
+-- Reuses the existing broker/firm graph: a residential listing agent is a broker row with
+-- agent_type='residential', their brokerage is a firm row, and broker_condo links them to the
+-- cre.condo they listed (mirrors broker_listing for the commercial side). This keeps the mind-map,
+-- /api/broker, and the Arcstone pitch generator working unchanged for both commercial + residential.
+--
+-- CCPA posture (unchanged): business-contact fields only (agent name / brokerage / business phone /
+-- business email / license #). Per-field provenance recorded in broker_field_source (tier
+-- 'redfin-detail'). NO consumer/family data. NO send (pitches stay draft, George-gated).
+
+ALTER TABLE broker ADD COLUMN IF NOT EXISTS agent_type text DEFAULT 'commercial';
+ALTER TABLE broker ADD COLUMN IF NOT EXISTS license text; -- DRE/MLS license # when public
+
+-- Backfill existing rows explicitly (DEFAULT only applies to new rows on some PG versions).
+UPDATE broker SET agent_type = 'commercial' WHERE agent_type IS NULL;
+
+CREATE INDEX IF NOT EXISTS idx_broker_agent_type ON broker(agent_type);
+
+-- The residential edge: an agent listed a condo. Mirrors broker_listing for cre.condo.
+CREATE TABLE IF NOT EXISTS broker_condo (
+ broker_id integer NOT NULL REFERENCES broker(id) ON DELETE CASCADE,
+ condo_id text NOT NULL REFERENCES condo(id) ON DELETE CASCADE,
+ role text DEFAULT 'listing', -- listing | co | team
+ PRIMARY KEY (broker_id, condo_id)
+);
+CREATE INDEX IF NOT EXISTS idx_bc_condo ON broker_condo(condo_id);
+CREATE INDEX IF NOT EXISTS idx_bc_broker ON broker_condo(broker_id);
+
+-- broker_node powers the mind-map node weight. Extend it so a node's listing count includes BOTH
+-- commercial listings (broker_listing) AND residential condos (broker_condo), and expose agent_type.
+-- DROP+recreate (not CREATE OR REPLACE): adding agent_type changes column order, which REPLACE
+-- forbids. Keeps total_assets so scripts/export-to-gsheet.js + brokers-db topBrokers stay intact.
+DROP VIEW IF EXISTS broker_node;
+CREATE VIEW broker_node AS
+ SELECT b.id, b.name, f.name AS firm, b.phone, b.email, b.title, b.total_assets, b.agent_type,
+ (count(DISTINCT bl.listing_id) + count(DISTINCT bc.condo_id)) AS listings
+ 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;
diff --git a/scripts/probe-redfin-agents.js b/scripts/probe-redfin-agents.js
new file mode 100644
index 0000000..6933413
--- /dev/null
+++ b/scripts/probe-redfin-agents.js
@@ -0,0 +1,109 @@
+// probe-redfin-agents.js — STEP 1 FEASIBILITY PROBE (~$0.04, 1 Browserbase session).
+// Goal: find the Redfin detail endpoint that returns the RESIDENTIAL listing agent + brokerage
+// for a propertyId, so the bulk capture (fetch-redfin-agents.js) can be feed-first (in-page fetch
+// from one warmed redfin.com session) instead of 736 full page-loads.
+//
+// We try, per propertyId: aboveTheFold + belowTheFold (the two stingray detail feeds), looking for
+// the listingAgents / mlsDisclaimer / agentInfo blocks that carry agent name + brokerage (+ phone).
+//
+// Usage: NODE_PATH=$HOME/.claude/skills/browserbase/node_modules node scripts/probe-redfin-agents.js
+'use strict';
+const fs = require('fs');
+const path = require('path');
+const { chromium } = require('playwright-core');
+const Browserbase = require('@browserbasehq/sdk').default;
+
+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');
+
+// A few real propertyIds + their full URLs (the warm-up needs the listing path).
+const SAMPLES = [
+ { pid: '5265960', url: 'https://www.redfin.com/CA/Burbank/8007-Via-Verona-91504/unit-36/home/5265960' },
+ { pid: '8130466', url: 'https://www.redfin.com/CA/Burbank/4106-W-Kling-St-91505/home/8130466' },
+ { pid: '5347017', url: 'https://www.redfin.com/CA/Burbank/330-N-5th-St-91501/home/5347017' },
+ { pid: '184824835', url: 'https://www.redfin.com/CA/Burbank/9745-Via-Roma-91504/home/184824835' }
+];
+
+const strip = s => s.replace(/^[)\]}'&\s]*\{\}&&/, '').replace(/^[)\]}'\s]+/, '');
+
+// Recursively hunt an object for keys that look like agent / brokerage / mls-disclaimer fields.
+function findAgentish(obj, hits, pathStr = '') {
+ if (!obj || typeof obj !== 'object') return;
+ for (const [k, v] of Object.entries(obj)) {
+ const lk = k.toLowerCase();
+ if (/agent|broker|listingagent|mlsdisclaimer|listedby|office|salesperson|listingbrok/.test(lk)) {
+ const sample = typeof v === 'string' ? v.slice(0, 200)
+ : (v && typeof v === 'object' ? JSON.stringify(v).slice(0, 400) : v);
+ hits.push({ path: (pathStr + '.' + k).replace(/^\./, ''), value: sample });
+ }
+ if (v && typeof v === 'object') findAgentish(v, hits, pathStr + '.' + k);
+ }
+}
+
+(async () => {
+ const out = { ran_at: new Date().toISOString(), samples: [] };
+ let browser, session;
+ 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);
+
+ // Warm Redfin once (cookies/referer the stingray detail feeds expect).
+ await page.goto('https://www.redfin.com/city/11203/CA/Los-Angeles/filter/property-type=condo', { waitUntil: 'domcontentloaded' }).catch(() => {});
+ await page.waitForTimeout(2500);
+
+ for (const s of SAMPLES) {
+ // Land on the actual listing page first (some detail feeds 403 without the listing referer).
+ await page.goto(s.url, { waitUntil: 'domcontentloaded' }).catch(() => {});
+ await page.waitForTimeout(1500);
+
+ const endpoints = {
+ aboveTheFold: `https://www.redfin.com/stingray/api/home/details/aboveTheFold?propertyId=${s.pid}&accessLevel=1`,
+ belowTheFold: `https://www.redfin.com/stingray/api/home/details/belowTheFold?propertyId=${s.pid}&accessLevel=1`,
+ avm: `https://www.redfin.com/stingray/api/home/details/listing/floorplans?propertyId=${s.pid}`,
+ mainHouseInfo: `https://www.redfin.com/stingray/api/home/details/mainHouseInfoPanelInfo?propertyId=${s.pid}&accessLevel=1`
+ };
+
+ const sampleOut = { pid: s.pid, endpoints: {} };
+ for (const [name, u] of Object.entries(endpoints)) {
+ try {
+ const res = await page.evaluate(async (u) => {
+ const r = await fetch(u, { headers: { accept: 'application/json' } });
+ const t = await r.text();
+ return { status: r.status, len: t.length, head: t.slice(0, 50), body: t.slice(0, 600000) };
+ }, u);
+ let hits = [], parseErr = null;
+ if (res.body && res.status === 200) {
+ try { const j = JSON.parse(strip(res.body)); findAgentish(j.payload || j, hits); }
+ catch (e) { parseErr = e.message.slice(0, 80); }
+ }
+ sampleOut.endpoints[name] = {
+ status: res.status, len: res.len, head: res.head, parseErr,
+ agentHits: hits.slice(0, 25)
+ };
+ } catch (e) {
+ sampleOut.endpoints[name] = { err: String(e.message).slice(0, 100) };
+ }
+ await page.waitForTimeout(400);
+ }
+ out.samples.push(sampleOut);
+ }
+ } catch (e) {
+ out.err = e.message;
+ } finally { if (browser) try { await browser.close(); } catch (_) {} }
+
+ const outFile = path.join(__dirname, '..', 'data', 'raw', 'redfin-agent-probe.json');
+ fs.writeFileSync(outFile, JSON.stringify(out, null, 2));
+ // Compact console summary.
+ for (const s of out.samples) {
+ for (const [name, r] of Object.entries(s.endpoints)) {
+ const n = r.agentHits ? r.agentHits.length : 0;
+ console.log(`pid ${s.pid} ${name} status=${r.status || r.err} len=${r.len || 0} agentHits=${n}`);
+ }
+ }
+ console.log('cost: ~$0.04 (1 Browserbase session) · full dump -> ' + outFile);
+})();
← 44b6973 auto-save: 2026-06-28T07:43:27 (2 files) — public/brokers.ht
·
back to Commercialrealestate
·
CRE: closed-sales scraper (Redfin gis-csv sold feed, 10yr) → a686d47 →