`scripts/provenance-probe.mjs`

92 lines
#!/usr/bin/env node
// TK-10708  Provenance probe v2 (codex's #1 first move) -- now joins REAL broker
// domains from usre.firm (the CRCP/usre firm registry) instead of a hardcoded list.
//
// For N recent deals it emits, per deal: the deal facts, a candidate broker-domain set
// (firms in the deal's state with asset_class='commercial' + a website), a marketplace
// domain set (for GATED classification), and the discovery query a rate-limited,
// robots-respecting step (or the agent's WebSearch) runs. It does NOT fetch third-party
// pages itself. Output feeds a human/agent discovery pass that fills the null columns;
// only validated rows promote to usre.
//
// NOTE on the join: broker_of_record_history has no populated county_fips/ain yet, so we
// can't resolve the EXACT listing broker per parcel. We degrade to a STATE + commercial
// candidate set from firm. Upgrade to exact when BOR history gets county/ain keys.
//
// Usage: node scripts/provenance-probe.mjs --n 8 [--county 12086]

import { execFileSync } from 'node:child_process';
import { writeFileSync, mkdirSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
import { dirname, join } from 'node:path';

const ROOT = join(dirname(fileURLToPath(import.meta.url)), '..');
const OUT = join(ROOT, 'out');
mkdirSync(OUT, { recursive: true });

const args = process.argv.slice(2);
const getArg = (f, d) => { const i = args.indexOf(f); return i >= 0 ? args[i + 1] : d; };
const N = parseInt(getArg('--n', '8'), 10);
const COUNTY = getArg('--county', null);

const STATE_FIPS = { '01':'AL','02':'AK','04':'AZ','05':'AR','06':'CA','08':'CO','09':'CT','10':'DE','11':'DC','12':'FL','13':'GA','15':'HI','16':'ID','17':'IL','18':'IN','19':'IA','20':'KS','21':'KY','22':'LA','23':'ME','24':'MD','25':'MA','26':'MI','27':'MN','28':'MS','29':'MO','30':'MT','31':'NE','32':'NV','33':'NH','34':'NJ','35':'NM','36':'NY','37':'NC','38':'ND','39':'OH','40':'OK','41':'OR','42':'PA','44':'RI','45':'SC','46':'SD','47':'TN','48':'TX','49':'UT','50':'VT','51':'VA','53':'WA','54':'WV','55':'WI','56':'WY' };
// Marketplaces: automated access GATED by ToS (codex). Used ONLY to classify a hit as gated.
const MARKETPLACE_DOMAINS = ['crexi.com','loopnet.com','cityfeet.com','costar.com','brevitas.com','biproxi.com','commercialcafe.com','commercialsearch.com'];

const q = sql => execFileSync('psql', ['usre','-t','-A','-c',sql], { encoding:'utf8' }).trim();
const domainOf = url => { try { return new URL(url).hostname.replace(/^www\./,'').toLowerCase(); } catch { return null; } };

const where = COUNTY ? `WHERE county_fips='${COUNTY.replace(/'/g,"''")}'` : '';
const deals = q(`SELECT row_to_json(t) FROM (SELECT sale_date,sale_price,ctype,address,city,county_name,county_fips,sqft,doc_number FROM recent_commercial_deals ${where} ORDER BY sale_price DESC LIMIT ${N}) t`)
  .split('\n').filter(Boolean).map(l => JSON.parse(l));
if (!deals.length) { console.error('No deals.'); process.exit(1); }

// Candidate broker domains per state, from the real firm registry, cached per state.
const domainCache = {};
function brokerDomains(state) {
  if (!state) return [];
  if (domainCache[state]) return domainCache[state];
  const rows = q(`SELECT website FROM firm WHERE hq_state='${state}' AND asset_class='commercial' AND website IS NOT NULL AND website<>''`)
    .split('\n').filter(Boolean);
  const set = [...new Set(rows.map(domainOf).filter(Boolean))];
  domainCache[state] = set;
  return set;
}

const worklist = deals.map(d => {
  const state = STATE_FIPS[(d.county_fips||'').slice(0,2)] || null;
  const domains = brokerDomains(state);
  return {
    ...d, sale_price:+d.sale_price, state,
    candidate_broker_domain_count: domains.length,
    candidate_broker_domains: domains,          // full set for classification
    marketplace_domains_gated: MARKETPLACE_DOMAINS,
    // The agent runs THIS via WebSearch (a search-engine query, not scraping a broker):
    discovery_query: `"${d.address}" ${d.city} commercial "offering memorandum" OR flyer OR brochure`,
    // filled by the discovery pass:
    source_landing_url:null, document_url:null, hit_domain:null,
    rights_class:null,  // broker_owned | marketplace_gated | other_review | none
    match_confidence:null
  };
});

const ts = new Date().toISOString().replace(/[:.]/g,'-');
writeFileSync(join(OUT,`provenance-worklist-${ts}.json`), JSON.stringify(worklist,null,2));

const md = [
  `# Provenance probe v2 — ${deals.length} deals (${COUNTY||'all counties'})`,
  ``,
  `Generated ${new Date().toISOString()} · TK-10708 · DRY (no third-party pages fetched by this script).`,
  `Candidate broker domains are joined from usre.firm (state + commercial + website).`,
  `Discovery = agent WebSearch per row; classify each hit domain: broker_owned (in firm set) /`,
  `marketplace_gated / other_review. Only broker_owned + first_party promote; marketplace = GATED.`,
  ``,
  `| price | type | address | city | state | broker-domain candidates | query |`,
  `|--:|---|---|---|---|--:|---|`,
  ...worklist.map(r => `| $${r.sale_price.toLocaleString('en-US')} | ${r.ctype||''} | ${r.address} | ${r.city} | ${r.state||'?'} | ${r.candidate_broker_domain_count} | \`${r.discovery_query}\` |`)
].join('\n');
writeFileSync(join(OUT,'provenance-report.md'), md);

console.log(`Wrote out/provenance-report.md + out/provenance-worklist-${ts}.json (${worklist.length} deals)`);
worklist.forEach(r => console.log(`  ${r.address}, ${r.city} ${r.state} — ${r.candidate_broker_domain_count} candidate broker domains`));