← back to Nationalrealestate
feat: RENTV data feed over usre DB (:9796) — brokers/firms/commercial parcels/subleases/regions
23c88a6c43313025720cd550e3167681faa624b8 · 2026-07-28 09:46:55 -0700 · Steve
Read-only CORS JSON feed sharing the national CRE datasets with RENTV: /feed/brokers (~2M),
/feed/firms (~214k), /feed/commercial (~154k parcels w/ assessed values), /feed/subleases,
/feed/regions, /feed/summary. Tailnet-reachable, pm2-managed.
Files touched
Diff
commit 23c88a6c43313025720cd550e3167681faa624b8
Author: Steve <steve@designerwallcoverings.com>
Date: Tue Jul 28 09:46:55 2026 -0700
feat: RENTV data feed over usre DB (:9796) — brokers/firms/commercial parcels/subleases/regions
Read-only CORS JSON feed sharing the national CRE datasets with RENTV: /feed/brokers (~2M),
/feed/firms (~214k), /feed/commercial (~154k parcels w/ assessed values), /feed/subleases,
/feed/regions, /feed/summary. Tailnet-reachable, pm2-managed.
---
scripts/rentv-feed.mjs | 99 ++++++++++++++++++++++++++++++++++++++++++++++++++
1 file changed, 99 insertions(+)
diff --git a/scripts/rentv-feed.mjs b/scripts/rentv-feed.mjs
new file mode 100644
index 0000000..568d961
--- /dev/null
+++ b/scripts/rentv-feed.mjs
@@ -0,0 +1,99 @@
+// 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}`));
← 6ff7a81 TK-50: field-level provenance across ALL remaining parcel in
·
back to Nationalrealestate
·
parcels: fix nyc_pluto 08P01 — provenance added 4 cols (19→2 f6fb527 →