`scripts/rentv-feed.mjs`

100 lines
// USRealEstate (usre) DATA FEED — read-only, CORS-enabled JSON API sharing the
// national CRE datasets (brokers, firms, commercial parcels, subleases, regions)
// with RENTV & partners. Zero-dep (Node http + psql). Tailnet-reachable (binds *).
// Run: node scripts/rentv-feed.mjs   (PORT env, default 9796)
import { createServer } from "node:http";
import { execFile } from "node:child_process";

const DBURL = "postgresql://macstudio3@localhost/usre?host=/tmp";
const PORT = Number(process.env.PORT || 9796);
const q = (sql) => new Promise((r) => execFile("psql", [DBURL, "-tAc", sql], { maxBuffer: 128 << 20 }, (e, o) => r(e ? "[]" : (o.trim() || "[]"))));
const esc = (s) => String(s ?? "").replace(/'/g, "''").replace(/[;\\]/g, "").slice(0, 60);
const int = (v, d, max) => Math.min(max, Math.max(0, parseInt(v ?? d, 10) || d));
const page = (p) => [int(p.get("limit"), 100, 1000), int(p.get("offset"), 0, 5e6)];

async function list(table, cols, where, order, p) {
  const [limit, offset] = page(p);
  const total = Number((await q(`select count(*) from ${table} where ${where}`)).trim() || 0);
  const rows = await q(`select coalesce(json_agg(t),'[]') from (select ${cols} from ${table} where ${where} order by ${order} limit ${limit} offset ${offset}) t`);
  return { total, count: limit, offset, results: JSON.parse(rows) };
}

async function brokers(p) {
  const w = ["1=1"];
  if (p.get("state")) w.push(`b.state_code='${esc(p.get("state")).toUpperCase()}'`);
  if (p.get("city")) w.push(`b.city ilike '${esc(p.get("city"))}%'`);
  if (p.get("status")) w.push(`b.license_status ilike '${esc(p.get("status"))}%'`);
  if (p.get("q")) w.push(`(b.name ilike '%${esc(p.get("q"))}%' or b.email ilike '%${esc(p.get("q"))}%' or b.license_no='${esc(p.get("q"))}')`);
  const [limit, offset] = page(p);
  const where = w.join(" and ");
  const total = Number((await q(`select count(*) from broker b where ${where}`)).trim() || 0);
  const rows = await q(`select coalesce(json_agg(t),'[]') from (
    select b.name, b.license_no, b.license_state, b.license_type, b.license_status, b.email, b.phone,
           b.city, b.state_code, f.name as firm
    from broker b left join firm f on f.id=b.firm_id where ${where}
    order by b.name limit ${limit} offset ${offset}) t`);
  return { total, count: limit, offset, results: JSON.parse(rows) };
}
async function firms(p) {
  const w = ["1=1"];
  if (p.get("state")) w.push(`hq_state='${esc(p.get("state")).toUpperCase()}'`);
  if (p.get("city")) w.push(`hq_city ilike '${esc(p.get("city"))}%'`);
  if (p.get("min_agents")) w.push(`agent_count >= ${int(p.get("min_agents"), 0, 1e7)}`);
  if (p.get("q")) w.push(`name ilike '%${esc(p.get("q"))}%'`);
  return list("firm", "name, website, phone, hq_city, hq_state, agent_count, license_no", w.join(" and "), "agent_count desc nulls last", p);
}
async function commercial(p) {
  const w = ["1=1"];
  if (p.get("city")) w.push(`city ilike '${esc(p.get("city"))}%'`);
  if (p.get("zip")) w.push(`zip='${esc(p.get("zip"))}'`);
  if (p.get("use")) w.push(`use_desc ilike '%${esc(p.get("use"))}%'`);
  if (p.get("min_assessed")) w.push(`assessed_total >= ${int(p.get("min_assessed"), 0, 1e12)}`);
  return list("commercial_parcel", "address, city, zip, ctype, use_desc, use_class, assessed_total, sqft, year_built, units, recording_date::text",
    w.join(" and "), "assessed_total desc nulls last", p);
}
async function subleases(p) {
  return list("sublease", "address, city, submarket, floor_suite, sqft, asking_rate, rate_period, lease_type, sublandlord, firm_name, broker_name, broker_phone, listing_url, lat, lng, status",
    "1=1", "created_at desc", p);
}
async function regions(p) {
  const w = ["1=1"];
  if (p.get("state")) w.push(`state_code='${esc(p.get("state")).toUpperCase()}'`);
  if (p.get("type")) w.push(`region_type='${esc(p.get("type"))}'`);
  return list("region", "name, region_type, state_code, cbsa_code, lat, lng, population", w.join(" and "), "population desc nulls last", p);
}
async function summary() {
  const counts = {};
  for (const t of ["broker", "firm", "commercial_parcel", "sublease", "region", "listing"])
    counts[t] = Number((await q(`select count(*) from ${t}`)).trim() || 0);
  const byState = JSON.parse(await q(`select coalesce(json_agg(t),'[]') from (select state_code, count(*) brokers from broker where state_code is not null group by state_code order by 2 desc limit 15) t`));
  return { counts, brokers_by_state: byState };
}

const DOCS = {
  service: "USRealEstate (usre) Data Feed",
  description: "Read-only national CRE datasets — brokers (~2M), firms (~214k), commercial parcels (~154k, assessed values), subleases, region metrics. Shared with RENTV & partners.",
  endpoints: {
    "GET /feed/brokers": "params: state, city, status, q, limit(<=1000), offset",
    "GET /feed/firms": "params: state, city, min_agents, q, limit, offset",
    "GET /feed/commercial": "commercial parcels — params: city, zip, use, min_assessed, limit, offset",
    "GET /feed/subleases": "sublease inventory — params: limit, offset",
    "GET /feed/regions": "params: state, type, limit, offset",
    "GET /feed/summary": "dataset counts + brokers by state",
  },
  cors: "enabled",
};

const routes = { "/feed/brokers": brokers, "/feed/firms": firms, "/feed/commercial": commercial, "/feed/subleases": subleases, "/feed/regions": regions };
const server = createServer(async (req, res) => {
  res.setHeader("Access-Control-Allow-Origin", "*");
  res.setHeader("content-type", "application/json");
  const u = new URL(req.url, "http://x");
  try {
    if (u.pathname === "/" || u.pathname === "/feed") return res.end(JSON.stringify(DOCS, null, 2));
    if (u.pathname === "/feed/summary") return res.end(JSON.stringify(await summary()));
    if (routes[u.pathname]) return res.end(JSON.stringify(await routes[u.pathname](u.searchParams)));
  } catch (e) { res.statusCode = 500; return res.end(JSON.stringify({ error: String(e) })); }
  res.statusCode = 404; res.end(JSON.stringify({ error: "not found", see: "/" }));
});
server.listen(PORT, () => console.log(`rentv-feed (usre) on http://127.0.0.1:${PORT}`));