← back to Sublease Agentabrams
auto-save: 2026-07-20T13:32:24 (4 files) — scripts/export-snapshot.js server.js scripts/import-crcp.js scripts/migrate-crcp.sql
88c13a4a2268d1fc453ff9500adfd3d8b2f30088 · 2026-07-20 13:32:25 -0700 · Steve Abrams
Files touched
M scripts/export-snapshot.jsA scripts/import-crcp.jsA scripts/migrate-crcp.sqlM server.js
Diff
commit 88c13a4a2268d1fc453ff9500adfd3d8b2f30088
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Mon Jul 20 13:32:25 2026 -0700
auto-save: 2026-07-20T13:32:24 (4 files) — scripts/export-snapshot.js server.js scripts/import-crcp.js scripts/migrate-crcp.sql
---
scripts/export-snapshot.js | 38 ++++++++------
scripts/import-crcp.js | 56 ++++++++++++++++++++
scripts/migrate-crcp.sql | 30 +++++++++++
server.js | 128 +++++++++++++++++++++++++++------------------
4 files changed, 186 insertions(+), 66 deletions(-)
diff --git a/scripts/export-snapshot.js b/scripts/export-snapshot.js
index d9d165c..2caa29d 100644
--- a/scripts/export-snapshot.js
+++ b/scripts/export-snapshot.js
@@ -1,7 +1,6 @@
'use strict';
-// Export CRUnifiedDB → data/snapshot.json (the deploy artifact). The live site serves
-// this snapshot (USE_SNAPSHOT=1) so it never touches the shared prod Postgres. Re-run
-// after every crawl to refresh what production shows (the dw_unified mirror pattern).
+// Export CRUnifiedDB → data/snapshot.json (deploy artifact). Serves the live site with zero
+// load on the shared prod Postgres. Includes the CRCP full-CRE base + sublease inventory + brokers.
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');
@@ -14,24 +13,31 @@ const OUT = path.join(__dirname, '..', 'data', 'snapshot.json');
SELECT s.*, COUNT(l.id) FILTER (WHERE l.status='active')::int AS listing_count
FROM sponsors s LEFT JOIN listings l ON l.sponsor_id=s.id GROUP BY s.id ORDER BY s.role, s.name`);
const listings = await q(`
- SELECT l.*, s.slug AS sponsor_slug, s.name AS sponsor_name, s.logo_path
- FROM listings l JOIN sponsors s ON s.id=l.sponsor_id WHERE l.status='active' ORDER BY l.id`);
- const agents = await q(`SELECT * FROM agents`);
- const runs = await q(`SELECT s.slug, cr.status, cr.finished_at, cr.listings_found
- FROM crawl_runs cr JOIN sponsors s ON s.id=cr.sponsor_id ORDER BY cr.started_at DESC LIMIT 20`);
+ SELECT l.id, l.listing_kind, l.is_sublease, l.title, l.address, l.city, l.state, l.zip, l.lat, l.lng,
+ l.space_type, l.asset_class, l.size_sf, l.units, l.cap_rate, l.year_built, l.price_amount, l.price_unit,
+ l.term, l.source_url, l.image_url, l.source_label,
+ s.slug AS sponsor_slug, s.name AS sponsor_name, s.logo_path
+ FROM listings l LEFT JOIN sponsors s ON s.id=l.sponsor_id WHERE l.status IN ('active','Active') OR l.status IS NULL
+ ORDER BY l.is_sublease DESC, l.id`);
+ const brokers = await q(`SELECT id, name, firm, agent_type, phone, email, website, linkedin, office_addr, total_assets, specialties, created_at FROM brokers ORDER BY name`);
+ const runs = await q(`SELECT s.slug, cr.status, cr.finished_at, cr.listings_found FROM crawl_runs cr JOIN sponsors s ON s.id=cr.sponsor_id ORDER BY cr.started_at DESC LIMIT 20`);
- const byType = {}; listings.forEach(l => { if (l.space_type) byType[l.space_type] = (byType[l.space_type] || 0) + 1; });
+ const cnt = (arr, key) => arr.reduce((m, x) => { const k = x[key] || '—'; m[k] = (m[k] || 0) + 1; return m; }, {});
const snapshot = {
built_at: new Date().toISOString(),
- sponsors, listings, agents, recent_runs: runs,
+ sponsors, brokers, listings, recent_runs: runs,
map_points: listings.filter(l => l.lat != null && l.lng != null).map(l => ({
- id: l.id, title: l.title, address: l.address, city: l.city, lat: l.lat, lng: l.lng,
- space_type: l.space_type, size_sf: l.size_sf, price_amount: l.price_amount, price_unit: l.price_unit,
- source_url: l.source_url, sponsor_slug: l.sponsor_slug, sponsor_name: l.sponsor_name })),
- stats: { listings: listings.length, sponsors: sponsors.length,
- by_type: Object.entries(byType).map(([space_type, n]) => ({ space_type, n })).sort((a, b) => b.n - a.n) },
+ id: l.id, title: l.title, address: l.address, city: l.city, lat: l.lat, lng: l.lng, listing_kind: l.listing_kind,
+ space_type: l.space_type, asset_class: l.asset_class, size_sf: l.size_sf, price_amount: l.price_amount,
+ price_unit: l.price_unit, cap_rate: l.cap_rate, source_url: l.source_url, sponsor_slug: l.sponsor_slug, sponsor_name: l.sponsor_name })),
+ stats: {
+ listings: listings.length, sponsors: sponsors.length, brokers: brokers.length,
+ subleases: listings.filter(l => l.is_sublease).length,
+ by_kind: Object.entries(cnt(listings, 'listing_kind')).map(([k, n]) => ({ kind: k, n })).sort((a, b) => b.n - a.n),
+ by_type: Object.entries(cnt(listings.filter(l => l.space_type), 'space_type')).map(([space_type, n]) => ({ space_type, n })).sort((a, b) => b.n - a.n),
+ },
};
fs.writeFileSync(OUT, JSON.stringify(snapshot));
- console.log(`snapshot → ${OUT} (${sponsors.length} sponsors, ${listings.length} listings, ${snapshot.map_points.length} map points)`);
+ console.log(`snapshot → ${(fs.statSync(OUT).size / 1e6).toFixed(1)}MB · ${sponsors.length} sponsors, ${brokers.length} brokers, ${listings.length} listings, ${snapshot.map_points.length} map points`);
await pool.end();
})();
diff --git a/scripts/import-crcp.js b/scripts/import-crcp.js
new file mode 100644
index 0000000..841290c
--- /dev/null
+++ b/scripts/import-crcp.js
@@ -0,0 +1,56 @@
+'use strict';
+// Import CRCP real data → CRUnifiedDB: 2,537 brokers + 3,105 for-sale listings (honestly typed
+// as listing_kind='sale', is_sublease=false), geo-matched from map-points by address. $0.
+const fs = require('fs');
+const { Pool } = require('pg');
+const pool = new Pool({ host: '/tmp', database: 'crunified' });
+const CR = process.env.HOME + '/Projects/commercialrealestate/data/';
+const j = (f) => JSON.parse(fs.readFileSync(CR + f, 'utf8'));
+const norm = (s) => (s || '').toLowerCase().replace(/[^a-z0-9]/g, '');
+const SPACE = { 'Office': 'office', 'Retail': 'retail', 'Retail/NNN': 'retail', 'Mixed-use': 'flex', 'Multifamily': 'multifamily' };
+
+(async () => {
+ const brokers = j('brokers-snapshot.json').brokers;
+ const listings = j('listings.json').listings;
+ const points = j('map-points.json').points;
+
+ // address+city → {lat,lng} from map-points (any kind) for geo matching
+ const geo = new Map();
+ for (const p of points) if (p.lat && p.lng) { const k = norm(p.address) + '|' + norm(p.city); if (!geo.has(k)) geo.set(k, { lat: p.lat, lng: p.lng }); }
+
+ // --- brokers ---
+ let b = 0;
+ for (const r of brokers) {
+ await pool.query(`INSERT INTO brokers (id,name,firm,agent_type,phone,email,website,linkedin,office_addr,total_assets,specialties)
+ VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11) ON CONFLICT (id) DO UPDATE SET
+ name=EXCLUDED.name, firm=EXCLUDED.firm, phone=EXCLUDED.phone, email=EXCLUDED.email, website=EXCLUDED.website`,
+ [r.id, r.name, r.firm, r.agent_type, r.phone, r.email, r.website, r.linkedin, r.office_addr, r.total_assets,
+ Array.isArray(r.specialties) ? r.specialties.join(', ') : r.specialties]);
+ b++;
+ }
+
+ // --- listings (for-sale base) ---
+ let li = 0, withGeo = 0;
+ for (const r of listings) {
+ const g = geo.get(norm(r.address) + '|' + norm(r.city));
+ if (g) withGeo++;
+ await pool.query(`INSERT INTO listings
+ (source_label, external_id, listing_kind, is_sublease, title, address, city, state, zip, lat, lng,
+ space_type, asset_class, size_sf, units, cap_rate, year_built, price_amount, price_unit, status, source_url, raw, last_seen)
+ VALUES ('crcp',$1,'sale',false,$2,$3,$4,'CA',$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,'sale price',$15,$16,$17, now())
+ ON CONFLICT (source_label, external_id) WHERE source_label IS NOT NULL DO UPDATE SET price_amount=EXCLUDED.price_amount, cap_rate=EXCLUDED.cap_rate,
+ lat=COALESCE(EXCLUDED.lat, listings.lat), lng=COALESCE(EXCLUDED.lng, listings.lng), last_seen=now()`,
+ [String(r.id), (r.type ? r.type + ' — ' : '') + r.address, r.address, r.city, r.zip,
+ g ? g.lat : null, g ? g.lng : null, SPACE[r.type] || 'other', r.type, r.sqft || null, r.units || null,
+ r.cap_rate || null, r.year_built || null, r.price || null,
+ (r.status || 'Active'), r.source, JSON.stringify({ cap_rate: r.cap_rate, upside: r.upside_note, rent_control: r.rent_control })]);
+ li++;
+ }
+
+ const [{ n: total }] = (await pool.query(`SELECT COUNT(*)::int n FROM listings`)).rows;
+ const [{ n: subs }] = (await pool.query(`SELECT COUNT(*)::int n FROM listings WHERE is_sublease`)).rows;
+ console.log(`brokers: ${b} imported`);
+ console.log(`CRCP listings: ${li} imported (${withGeo} geo-matched from map-points)`);
+ console.log(`CRUnifiedDB now: ${total} listings total (${subs} sublease, ${total - subs} sale)`);
+ await pool.end();
+})();
diff --git a/scripts/migrate-crcp.sql b/scripts/migrate-crcp.sql
new file mode 100644
index 0000000..c745224
--- /dev/null
+++ b/scripts/migrate-crcp.sql
@@ -0,0 +1,30 @@
+-- Evolve CRUnifiedDB to hold the CRCP full-CRE base alongside the sublease inventory.
+ALTER TABLE listings ALTER COLUMN sponsor_id DROP NOT NULL;
+ALTER TABLE listings ADD COLUMN IF NOT EXISTS listing_kind TEXT NOT NULL DEFAULT 'sublease'; -- sublease | lease | sale
+ALTER TABLE listings ADD COLUMN IF NOT EXISTS asset_class TEXT; -- Multifamily | Retail | Office | Mixed-use
+ALTER TABLE listings ADD COLUMN IF NOT EXISTS cap_rate NUMERIC;
+ALTER TABLE listings ADD COLUMN IF NOT EXISTS units INTEGER;
+ALTER TABLE listings ADD COLUMN IF NOT EXISTS year_built INTEGER;
+ALTER TABLE listings ADD COLUMN IF NOT EXISTS broker_id INTEGER;
+ALTER TABLE listings ADD COLUMN IF NOT EXISTS source_label TEXT; -- 'crcp' for the imported base, NULL for sponsor-crawled
+-- CRCP rows have no sponsor; dedupe them on (source_label, external_id)
+CREATE UNIQUE INDEX IF NOT EXISTS ux_listings_source ON listings(source_label, external_id) WHERE source_label IS NOT NULL;
+CREATE INDEX IF NOT EXISTS idx_listings_kind ON listings(listing_kind);
+
+-- Real broker directory (from CRCP brokers-snapshot)
+CREATE TABLE IF NOT EXISTS brokers (
+ id INTEGER PRIMARY KEY, -- keep CRCP id
+ name TEXT NOT NULL,
+ firm TEXT,
+ agent_type TEXT,
+ phone TEXT,
+ email TEXT,
+ website TEXT,
+ linkedin TEXT,
+ office_addr TEXT,
+ total_assets INTEGER,
+ specialties TEXT,
+ source TEXT DEFAULT 'crcp',
+ created_at TIMESTAMPTZ NOT NULL DEFAULT now()
+);
+CREATE INDEX IF NOT EXISTS idx_brokers_firm ON brokers(firm);
diff --git a/server.js b/server.js
index 0b5d114..858362a 100644
--- a/server.js
+++ b/server.js
@@ -1,8 +1,7 @@
'use strict';
-// sublease.agentabrams.com — gated commercial sublease marketplace.
-// Two data backends:
-// • local/dev → Postgres CRUnifiedDB (crawlers write here)
-// • production → USE_SNAPSHOT=1 serves data/snapshot.json (no DB; zero load on shared PG)
+// sublease.agentabrams.com — gated CRE marketplace over CRUnifiedDB.
+// local/dev → Postgres CRUnifiedDB (crawlers + CRCP import write here)
+// production → USE_SNAPSHOT=1 serves data/snapshot.json (no DB; zero load on shared PG)
const fs = require('fs');
const path = require('path');
const express = require('express');
@@ -12,23 +11,19 @@ const PORT = process.env.PORT || 9714;
const PUB = path.join(__dirname, 'public');
const USE_SNAPSHOT = process.env.USE_SNAPSHOT === '1';
-// ── Data layer ──
let snap = null;
function loadSnap() {
try { snap = JSON.parse(fs.readFileSync(path.join(__dirname, 'data', 'snapshot.json'), 'utf8')); }
- catch { snap = { sponsors: [], listings: [], agents: [], map_points: [], recent_runs: [], stats: { listings: 0, sponsors: 0, by_type: [] } }; }
+ catch { snap = { sponsors: [], brokers: [], listings: [], map_points: [], recent_runs: [], stats: {} }; }
}
let pool = null, q = null;
-if (USE_SNAPSHOT) {
- loadSnap();
-} else {
+if (USE_SNAPSHOT) loadSnap();
+else {
const { Pool } = require('pg');
- pool = new Pool({ host: process.env.PGHOST || '/tmp', database: process.env.PGDATABASE || 'crunified',
- user: process.env.PGUSER || undefined, password: process.env.PGPASSWORD || undefined, max: 6 });
+ pool = new Pool({ host: process.env.PGHOST || '/tmp', database: process.env.PGDATABASE || 'crunified', max: 6 });
q = (sql, params) => pool.query(sql, params).then(r => r.rows);
}
-// ── Basic Auth (admin + Boomer client login). Health stays open. ──
const CREDS = ['admin:DW2024!', 'Boomer:Sublease2024!']
.concat((process.env.BASIC_AUTH_EXTRA || '').split(',').map(s => s.trim()).filter(Boolean));
const ACCEPTED = new Set(CREDS.map(c => 'Basic ' + Buffer.from(c).toString('base64')));
@@ -40,13 +35,15 @@ app.use((req, res, next) => {
return res.status(401).send('Authentication required');
});
-// ── APIs (each has a snapshot path + a db path) ──
+const LISTING_COLS = `l.id, l.listing_kind, l.is_sublease, l.title, l.address, l.city, l.state, l.zip, l.lat, l.lng,
+ l.space_type, l.asset_class, l.size_sf, l.units, l.cap_rate, l.year_built, l.price_amount, l.price_unit, l.term,
+ l.source_url, l.image_url, l.source_label, s.slug AS sponsor_slug, s.name AS sponsor_name, s.logo_path`;
+
app.get('/api/sponsors', async (_q, res) => {
try {
if (USE_SNAPSHOT) return res.json({ sponsors: snap.sponsors });
- const rows = await q(`SELECT s.*, COUNT(l.id) FILTER (WHERE l.status='active')::int AS listing_count
- FROM sponsors s LEFT JOIN listings l ON l.sponsor_id=s.id GROUP BY s.id ORDER BY s.role, s.name`);
- res.json({ sponsors: rows });
+ res.json({ sponsors: await q(`SELECT s.*, COUNT(l.id) FILTER (WHERE l.status='active')::int AS listing_count
+ FROM sponsors s LEFT JOIN listings l ON l.sponsor_id=s.id GROUP BY s.id ORDER BY s.role, s.name`) });
} catch (e) { res.status(500).json({ error: String(e) }); }
});
@@ -55,64 +52,95 @@ app.get('/api/sponsors/:slug', async (req, res) => {
if (USE_SNAPSHOT) {
const s = snap.sponsors.find(x => x.slug === req.params.slug);
if (!s) return res.status(404).json({ error: 'not found' });
- return res.json({ sponsor: s,
- listings: snap.listings.filter(l => l.sponsor_slug === s.slug),
- agents: snap.agents.filter(a => a.sponsor_id === s.id) });
+ return res.json({ sponsor: s, listings: snap.listings.filter(l => l.sponsor_slug === s.slug), agents: [] });
}
const [s] = await q(`SELECT * FROM sponsors WHERE slug=$1`, [req.params.slug]);
if (!s) return res.status(404).json({ error: 'not found' });
- const listings = await q(`SELECT l.*, s.slug AS sponsor_slug, s.name AS sponsor_name, s.logo_path
- FROM listings l JOIN sponsors s ON s.id=l.sponsor_id WHERE l.sponsor_id=$1 AND l.status='active'
- ORDER BY l.size_sf DESC NULLS LAST, l.id`, [s.id]);
- const agents = await q(`SELECT * FROM agents WHERE sponsor_id=$1 ORDER BY name`, [s.id]);
- res.json({ sponsor: s, listings, agents });
+ const listings = await q(`SELECT ${LISTING_COLS} FROM listings l LEFT JOIN sponsors s ON s.id=l.sponsor_id
+ WHERE l.sponsor_id=$1 ORDER BY l.size_sf DESC NULLS LAST, l.id`, [s.id]);
+ res.json({ sponsor: s, listings, agents: [] });
} catch (e) { res.status(500).json({ error: String(e) }); }
});
+function filterSnapListings(query) {
+ const { type, sponsor, kind, sublease, asset_class, min_sf, max_sf, q: search } = query;
+ let rows = snap.listings.slice();
+ if (type) rows = rows.filter(l => l.space_type === type);
+ if (asset_class) rows = rows.filter(l => l.asset_class === asset_class);
+ if (kind) rows = rows.filter(l => l.listing_kind === kind);
+ if (sublease === '1') rows = rows.filter(l => l.is_sublease);
+ if (sponsor) rows = rows.filter(l => l.sponsor_slug === sponsor);
+ if (min_sf) rows = rows.filter(l => (l.size_sf || 0) >= +min_sf);
+ if (max_sf) rows = rows.filter(l => (l.size_sf || 1e9) <= +max_sf);
+ if (search) { const s = String(search).toLowerCase(); rows = rows.filter(l => `${l.title} ${l.address} ${l.city}`.toLowerCase().includes(s)); }
+ return rows;
+}
+
app.get('/api/listings', async (req, res) => {
try {
- const { type, sponsor, min_sf, max_sf, q: search } = req.query;
+ const limit = Math.min(+req.query.limit || 500, 4000), offset = +req.query.offset || 0;
if (USE_SNAPSHOT) {
- let rows = snap.listings.slice();
- if (type) rows = rows.filter(l => l.space_type === type);
- if (sponsor) rows = rows.filter(l => l.sponsor_slug === sponsor);
- if (min_sf) rows = rows.filter(l => (l.size_sf || 0) >= +min_sf);
- if (max_sf) rows = rows.filter(l => (l.size_sf || 1e9) <= +max_sf);
- if (search) { const s = String(search).toLowerCase(); rows = rows.filter(l => `${l.title} ${l.address} ${l.city}`.toLowerCase().includes(s)); }
- return res.json({ listings: rows, count: rows.length });
+ const all = filterSnapListings(req.query);
+ return res.json({ listings: all.slice(offset, offset + limit), count: all.length });
}
- const where = [`l.status='active'`]; const p = [];
- if (type) { p.push(type); where.push(`l.space_type = $${p.length}`); }
- if (sponsor) { p.push(sponsor); where.push(`s.slug = $${p.length}`); }
- if (min_sf) { p.push(min_sf); where.push(`l.size_sf >= $${p.length}`); }
- if (max_sf) { p.push(max_sf); where.push(`l.size_sf <= $${p.length}`); }
- if (search) { p.push(`%${search}%`); where.push(`(l.title ILIKE $${p.length} OR l.address ILIKE $${p.length} OR l.city ILIKE $${p.length})`); }
- const rows = await q(`SELECT l.*, s.slug AS sponsor_slug, s.name AS sponsor_name, s.logo_path
- FROM listings l JOIN sponsors s ON s.id=l.sponsor_id WHERE ${where.join(' AND ')}
- ORDER BY l.last_seen DESC, l.id LIMIT 2000`, p);
- res.json({ listings: rows, count: rows.length });
+ const { type, sponsor, kind, sublease, asset_class, min_sf, max_sf, q: search } = req.query;
+ const where = []; const p = [];
+ if (type) { p.push(type); where.push(`l.space_type=$${p.length}`); }
+ if (asset_class) { p.push(asset_class); where.push(`l.asset_class=$${p.length}`); }
+ if (kind) { p.push(kind); where.push(`l.listing_kind=$${p.length}`); }
+ if (sublease === '1') where.push(`l.is_sublease`);
+ if (sponsor) { p.push(sponsor); where.push(`s.slug=$${p.length}`); }
+ if (min_sf) { p.push(min_sf); where.push(`l.size_sf>=$${p.length}`); }
+ if (max_sf) { p.push(max_sf); where.push(`l.size_sf<=$${p.length}`); }
+ if (search) { p.push(`%${search}%`); where.push(`(l.title ILIKE $${p.length} OR l.address ILIKE $${p.length} OR l.city ILIKE $${p.length})`); }
+ const w = where.length ? 'WHERE ' + where.join(' AND ') : '';
+ const [{ n }] = await q(`SELECT COUNT(*)::int n FROM listings l LEFT JOIN sponsors s ON s.id=l.sponsor_id ${w}`, p);
+ p.push(limit, offset);
+ const rows = await q(`SELECT ${LISTING_COLS} FROM listings l LEFT JOIN sponsors s ON s.id=l.sponsor_id ${w}
+ ORDER BY l.is_sublease DESC, l.id LIMIT $${p.length - 1} OFFSET $${p.length}`, p);
+ res.json({ listings: rows, count: n });
} catch (e) { res.status(500).json({ error: String(e) }); }
});
app.get('/api/map-points', async (_q, res) => {
try {
if (USE_SNAPSHOT) return res.json({ built_at: snap.built_at, points: snap.map_points });
- const rows = await q(`SELECT l.id, l.title, l.address, l.city, l.lat, l.lng, l.space_type, l.size_sf,
- l.price_amount, l.price_unit, l.source_url, s.slug AS sponsor_slug, s.name AS sponsor_name
- FROM listings l JOIN sponsors s ON s.id=l.sponsor_id
- WHERE l.status='active' AND l.lat IS NOT NULL AND l.lng IS NOT NULL`);
+ const rows = await q(`SELECT l.id, l.title, l.address, l.city, l.lat, l.lng, l.listing_kind, l.space_type, l.asset_class,
+ l.size_sf, l.price_amount, l.price_unit, l.cap_rate, l.source_url, s.slug AS sponsor_slug, s.name AS sponsor_name
+ FROM listings l LEFT JOIN sponsors s ON s.id=l.sponsor_id WHERE l.lat IS NOT NULL AND l.lng IS NOT NULL`);
res.json({ built_at: new Date().toISOString(), points: rows });
} catch (e) { res.status(500).json({ error: String(e) }); }
});
+app.get('/api/brokers', async (req, res) => {
+ try {
+ const { q: search } = req.query; const limit = Math.min(+req.query.limit || 60, 500), offset = +req.query.offset || 0;
+ if (USE_SNAPSHOT) {
+ let rows = snap.brokers || [];
+ if (search) { const s = String(search).toLowerCase(); rows = rows.filter(b => `${b.name} ${b.firm} ${b.office_addr}`.toLowerCase().includes(s)); }
+ return res.json({ brokers: rows.slice(offset, offset + limit), count: rows.length });
+ }
+ const p = []; let w = '';
+ if (search) { p.push(`%${search}%`); w = `WHERE (name ILIKE $1 OR firm ILIKE $1 OR office_addr ILIKE $1)`; }
+ const [{ n }] = await q(`SELECT COUNT(*)::int n FROM brokers ${w}`, p);
+ p.push(limit, offset);
+ const rows = await q(`SELECT * FROM brokers ${w} ORDER BY total_assets DESC NULLS LAST, name LIMIT $${p.length - 1} OFFSET $${p.length}`, p);
+ res.json({ brokers: rows, count: n });
+ } catch (e) { res.status(500).json({ error: String(e) }); }
+});
+
app.get('/api/stats', async (_q, res) => {
try {
if (USE_SNAPSHOT) return res.json({ ...snap.stats, recent_runs: snap.recent_runs });
- const [tot] = await q(`SELECT COUNT(*)::int n FROM listings WHERE status='active'`);
- const [spn] = await q(`SELECT COUNT(*)::int n FROM sponsors`);
- const byType = await q(`SELECT space_type, COUNT(*)::int n FROM listings WHERE status='active' GROUP BY space_type ORDER BY n DESC`);
+ const [s] = await q(`SELECT
+ (SELECT COUNT(*)::int FROM listings) listings,
+ (SELECT COUNT(*)::int FROM listings WHERE is_sublease) subleases,
+ (SELECT COUNT(*)::int FROM sponsors) sponsors,
+ (SELECT COUNT(*)::int FROM brokers) brokers`);
+ const byType = await q(`SELECT space_type, COUNT(*)::int n FROM listings WHERE space_type IS NOT NULL GROUP BY space_type ORDER BY n DESC`);
+ const byKind = await q(`SELECT listing_kind AS kind, COUNT(*)::int n FROM listings GROUP BY listing_kind ORDER BY n DESC`);
const runs = await q(`SELECT s.slug, cr.status, cr.finished_at, cr.listings_found FROM crawl_runs cr JOIN sponsors s ON s.id=cr.sponsor_id ORDER BY cr.started_at DESC LIMIT 20`);
- res.json({ listings: tot.n, sponsors: spn.n, by_type: byType, recent_runs: runs });
+ res.json({ ...s, by_type: byType, by_kind: byKind, recent_runs: runs });
} catch (e) { res.status(500).json({ error: String(e) }); }
});
← 0276f06 Browserbase crawler for CBRE/NAI (consent-filter, nested-add
·
back to Sublease Agentabrams
·
CRCP base import: 2537 real brokers + 3105 for-sale listings 5e1c425 →