`scripts/fetch-brokers.js`

93 lines
// fetch-brokers.js — capture each Crexi listing's broker team and ingest into the broker graph DB.
// Strategy: ONE Browserbase session captures Crexi's anonymous Bearer token, then fetches
// api.crexi.com/assets/<id>/brokers for our listing ids FROM INSIDE the page (same browser, авторized),
// in batches. Each broker → firm + agent name + book size → cre DB (firm/broker/listing + edges).
//
//   CC_LIMIT=80 node scripts/fetch-brokers.js     # sample run (default 80)
//   CC_LIMIT=0  node scripts/fetch-brokers.js     # all listings
// COST: 1 Browserbase session (~$0.05-0.10 depending on batch count). NODE_PATH → browserbase skill.
'use strict';
const fs = require('fs');
const path = require('path');
const { chromium } = require('playwright-core');
const Browserbase = require('@browserbasehq/sdk').default;
const db = require('./db/brokers-db');
const bbEnv = 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 ROOT = path.join(__dirname, '..');

const LIMIT = process.env.CC_LIMIT != null ? +process.env.CC_LIMIT : 80;

(async () => {
  let all = JSON.parse(fs.readFileSync(path.join(ROOT, 'data', 'listings.json'), 'utf8')).listings
    .filter(l => /^crx\d+/.test(l.id))
    .map(l => ({ ...l, assetId: l.id.replace(/^crx/, '') }));
  // Skip listings already captured (idempotent + fast incremental runs) unless CC_REDO=1.
  if (!process.env.CC_REDO) {
    const done = new Set((await db.pool.query('SELECT DISTINCT listing_id FROM broker_listing')).rows.map(r => r.listing_id));
    const before = all.length; all = all.filter(l => !done.has(l.id));
    console.log(`skipping ${before - all.length} already-captured; ${all.length} new`);
  }
  const work = LIMIT ? all.slice(0, LIMIT) : all;
  console.log(`brokers for ${work.length}/${all.length} Crexi listings…`);

  let browser, results = [];
  try {
    const bb = new Browserbase({ apiKey: get(bbEnv, 'BROWSERBASE_API_KEY') });
    const session = await bb.sessions.create({ projectId: get(bbEnv, 'BROWSERBASE_PROJECT_ID'), browserSettings: { solveCaptchas: true, viewport: { width: 1440, height: 1000 } } });
    console.log('bb session', session.id);
    browser = await chromium.connectOverCDP(session.connectUrl);
    const ctx = browser.contexts()[0];
    const page = ctx.pages()[0] || await ctx.newPage();
    page.setDefaultTimeout(45000);

    // capture the Authorization bearer token Crexi's UI uses
    let auth = null;
    page.on('request', req => { const h = req.headers(); if (!auth && h.authorization && /bearer/i.test(h.authorization) && req.url().includes('api.crexi.com')) auth = h.authorization; });
    await page.goto('https://www.crexi.com/properties/CA/Los-Angeles/multifamily', { waitUntil: 'domcontentloaded' });
    await page.waitForTimeout(6000); await page.mouse.wheel(0, 2500).catch(() => {}); await page.waitForTimeout(2500);
    if (!auth) { console.log('no bearer captured; will try cookie-auth in-page fetch'); }

    // fetch brokers in-page (same browser context → authorized), batched
    const ids = work.map(w => w.assetId);
    const BATCH = 25;
    for (let i = 0; i < ids.length; i += BATCH) {
      const batch = ids.slice(i, i + BATCH);
      const out = await page.evaluate(async ({ batch, auth }) => {
        const r = [];
        for (const id of batch) {
          try {
            const resp = await fetch(`https://api.crexi.com/assets/${id}/brokers`, { headers: auth ? { authorization: auth } : {} });
            if (resp.ok) r.push({ id, brokers: await resp.json() }); else r.push({ id, err: resp.status });
          } catch (e) { r.push({ id, err: String(e).slice(0, 60) }); }
        }
        return r;
      }, { batch, auth });
      results.push(...out);
      process.stdout.write(`  ${Math.min(i + BATCH, ids.length)}/${ids.length}\r`);
    }
  } catch (e) { console.error('FATAL', e.message); } finally { if (browser) await browser.close().catch(() => {}); }

  // ingest
  const byId = Object.fromEntries(work.map(w => [w.assetId, w]));
  let linked = 0, brokersSeen = 0, withBrokers = 0;
  for (const row of results) {
    const L = byId[row.id]; if (!L) continue;
    await db.upsertListing({ id: L.id, address: L.address, city: L.city, zip: L.zip, type: L.type, price: L.price, cap_rate: L.cap_rate, units: L.units, source: L.source });
    if (!Array.isArray(row.brokers) || !row.brokers.length) continue;
    withBrokers++;
    for (const b of row.brokers) {
      const name = [b.firstName, b.lastName].filter(Boolean).join(' ').trim(); if (!name) continue;
      const firm = b.brokerage && b.brokerage.name;
      const brokerId = await db.upsertBroker({ name, firm, total_assets: b.numberOfAssets || null, crexi_id: String(b.id || b.globalId || ''), source: 'crexi' });
      await db.link(brokerId, L.id, row.brokers.length > 1 ? 'co' : 'lead');
      linked++; brokersSeen++;
    }
  }
  console.log(`\ningested: ${withBrokers}/${results.length} listings had brokers · ${brokersSeen} broker links · ${linked} edges`);
  const top = await db.topBrokers(12);
  console.log('top brokers by listings in our set:');
  top.forEach(t => console.log(`  ${t.name} (${t.firm || 'n/a'}) — ${t.listings} here / ${t.total_assets || '?'} total`));
  await db.pool.end();
})();