← back to Nationalrealestate
usre: add commercial deals feed + per-deal detail API over recent_commercial_deals view (TK-10482)
41cb360c79a6ae03c6d12f99aaeb8faf94ffb3ac · 2026-08-12 08:25:57 -0700 · Steve Abrams
Files touched
A src/server/deals.tsM src/server/index.ts
Diff
commit 41cb360c79a6ae03c6d12f99aaeb8faf94ffb3ac
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Wed Aug 12 08:25:57 2026 -0700
usre: add commercial deals feed + per-deal detail API over recent_commercial_deals view (TK-10482)
---
src/server/deals.ts | 213 ++++++++++++++++++++++++++++++++++++++++++++++++++++
src/server/index.ts | 2 +
2 files changed, 215 insertions(+)
diff --git a/src/server/deals.ts b/src/server/deals.ts
new file mode 100644
index 0000000..b05b797
--- /dev/null
+++ b/src/server/deals.ts
@@ -0,0 +1,213 @@
+/**
+ * Commercial DEALS feed + per-deal detail — the drill layer over the
+ * `recent_commercial_deals` view (classified commercial_parcel × sale
+ * parcel_event, price > $250k, last 18 months). TK-10482.
+ *
+ * The view itself has no stable row id, so every query here carries
+ * `parcel_event.id` (a unique per-sale-event id) as the deal id, plus the
+ * (county_fips, ain) that keys back to commercial_parcel. That makes every
+ * deal row URL-addressable — the href-drill rule: no dead-end data points.
+ *
+ * GET /api/deals -> filtered/sorted/paged deals feed
+ * GET /api/deals/facets -> county/type/city/price-band facet counts
+ * GET /api/deals/:id -> one deal: sale event + parcel + all sibling sales
+ *
+ * TEXT-ONLY sourcing: buyer/seller (grantor/grantee) render as plain text; no
+ * firm link-out, no third-party asset re-host. County assessor/record links are
+ * the public-records backbone (each event carries its own source_url).
+ */
+import type { Express, Request, Response } from 'express';
+import { query } from '../../db/pool.ts';
+
+const CTYPES = new Set(['industrial', 'retail', 'office', 'hospitality', 'parking', 'other']);
+const fips5 = (v: unknown) => { const t = String(v ?? '').trim(); return /^\d{5}$/.test(t) ? t : null; };
+const clamp = (v: unknown, def: number, max: number) => { const n = Math.floor(Number(v)); return Number.isFinite(n) && n > 0 ? Math.min(n, max) : def; };
+
+// price bands (label -> [min,max]) so a price cell can drill to "deals in this band"
+const BANDS: Record<string, [number, number | null]> = {
+ '250k-1m': [250_000, 1_000_000],
+ '1m-5m': [1_000_000, 5_000_000],
+ '5m-25m': [5_000_000, 25_000_000],
+ '25m-100m': [25_000_000, 100_000_000],
+ '100m+': [100_000_000, null],
+};
+
+const SORTS: Record<string, string> = {
+ recent: 'pe.event_date DESC, pe.amount DESC',
+ oldest: 'pe.event_date ASC, pe.amount DESC',
+ price_desc: 'pe.amount DESC, pe.event_date DESC',
+ price_asc: 'pe.amount ASC, pe.event_date DESC',
+ sqft_desc: 'cp.sqft DESC NULLS LAST, pe.amount DESC',
+};
+
+/** shared WHERE builder over the deals base (parcel_event pe JOIN commercial_parcel cp) */
+function buildFilter(q: Request['query']): { where: string; params: unknown[] } {
+ const params: unknown[] = [];
+ // base predicate mirrors the view definition exactly
+ const parts = [
+ "pe.event_type = 'sale'",
+ 'pe.amount > 250000',
+ "pe.event_date >= (CURRENT_DATE - INTERVAL '1 year 6 mons')",
+ ];
+ const cf = fips5(q.county);
+ if (cf) { params.push(cf); parts.push(`pe.county_fips = $${params.length}`); }
+ if (q.type && CTYPES.has(String(q.type))) { params.push(String(q.type)); parts.push(`cp.ctype = $${params.length}`); }
+ if (q.city) { params.push(String(q.city)); parts.push(`upper(cp.city) = upper($${params.length})`); }
+ if (q.band && BANDS[String(q.band)]) {
+ const [lo, hi] = BANDS[String(q.band)];
+ params.push(lo); parts.push(`pe.amount >= $${params.length}`);
+ if (hi != null) { params.push(hi); parts.push(`pe.amount < $${params.length}`); }
+ }
+ if (q.year) { const y = Number(q.year); if (Number.isFinite(y) && y > 1800 && y < 2100) { params.push(y); parts.push(`cp.year_built = $${params.length}`); } }
+ if (q.q) {
+ const esc = String(q.q).slice(0, 80).replace(/[%_\\]/g, '\\$&');
+ params.push('%' + esc + '%');
+ parts.push(`(cp.address ILIKE $${params.length} ESCAPE '\\' OR cp.city ILIKE $${params.length} ESCAPE '\\' OR cp.ain ILIKE $${params.length} ESCAPE '\\')`);
+ }
+ return { where: parts.join(' AND '), params };
+}
+
+function shapeRow(r: any) {
+ return {
+ id: Number(r.id), // parcel_event.id — the stable deal id
+ ain: r.ain,
+ county_fips: r.county_fips,
+ county_name: r.county_name || r.county_fips,
+ sale_date: r.sale_date,
+ sale_price: r.sale_price != null ? Number(r.sale_price) : null,
+ ctype: r.ctype,
+ address: r.address,
+ city: r.city,
+ sqft: r.sqft != null ? Number(r.sqft) : null,
+ year_built: r.year_built != null ? Number(r.year_built) : null,
+ doc_number: r.doc_number || null,
+ grantor: r.grantor || null, // seller (TEXT)
+ grantee: r.grantee || null, // buyer (TEXT)
+ };
+}
+
+const SELECT_DEAL = `
+ SELECT pe.id, pe.county_fips, cp.ain,
+ to_char(pe.event_date,'YYYY-MM-DD') AS sale_date,
+ pe.amount AS sale_price, cp.ctype, cp.address, cp.city,
+ cp.sqft, cp.year_built, pe.doc_number, r.name AS county_name,
+ pe.detail->>'grantor' AS grantor, pe.detail->>'grantee' AS grantee
+ FROM parcel_event pe
+ JOIN commercial_parcel cp ON cp.county_fips = pe.county_fips AND cp.ain = pe.source_id
+ LEFT JOIN region r ON r.fips = pe.county_fips AND r.region_type = 'county'`;
+
+export function mountDeals(app: Express) {
+ // ── facets: counts by county, type, city, price band (drives the drill chips) ──
+ app.get('/api/deals/facets', async (req: Request, res: Response) => {
+ try {
+ const { where, params } = buildFilter(req.query);
+ const base = `FROM parcel_event pe JOIN commercial_parcel cp ON cp.county_fips=pe.county_fips AND cp.ain=pe.source_id WHERE ${where}`;
+ const [counties, types, cities, bands, total] = await Promise.all([
+ query<any>(`SELECT pe.county_fips AS fips, count(*)::int n, max(r.name) AS name FROM parcel_event pe JOIN commercial_parcel cp ON cp.county_fips=pe.county_fips AND cp.ain=pe.source_id LEFT JOIN region r ON r.fips=pe.county_fips AND r.region_type='county' WHERE ${where} GROUP BY pe.county_fips ORDER BY n DESC`, params),
+ query<any>(`SELECT cp.ctype AS type, count(*)::int n ${base} GROUP BY cp.ctype ORDER BY n DESC`, params),
+ query<any>(`SELECT cp.city, count(*)::int n ${base} AND cp.city IS NOT NULL GROUP BY cp.city ORDER BY n DESC LIMIT 40`, params),
+ query<any>(`SELECT (CASE
+ WHEN pe.amount >= 100000000 THEN '100m+'
+ WHEN pe.amount >= 25000000 THEN '25m-100m'
+ WHEN pe.amount >= 5000000 THEN '5m-25m'
+ WHEN pe.amount >= 1000000 THEN '1m-5m'
+ ELSE '250k-1m' END) AS band, count(*)::int n ${base} GROUP BY band`, params),
+ query<any>(`SELECT count(*)::int n, coalesce(sum(pe.amount),0)::numeric vol ${base}`, params),
+ ]);
+ res.json({
+ total: total.rows[0]?.n || 0,
+ volume: Number(total.rows[0]?.vol || 0),
+ counties: counties.rows.map((x) => ({ fips: x.fips, name: x.name || x.fips, n: x.n })),
+ types: types.rows.map((x) => ({ type: x.type, n: x.n })),
+ cities: cities.rows.map((x) => ({ city: x.city, n: x.n })),
+ bands: bands.rows.map((x) => ({ band: x.band, n: x.n })),
+ });
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+
+ // ── deals feed: filtered / sorted / paged ─────────────────────────────────
+ app.get('/api/deals', async (req: Request, res: Response) => {
+ try {
+ const { where, params } = buildFilter(req.query);
+ const orderBy = SORTS[String(req.query.sort || 'recent')] || SORTS.recent;
+ const limit = clamp(req.query.limit, 60, 200);
+ const offset = Math.max(0, Math.floor(Number(req.query.offset) || 0));
+ const rows = await query<any>(
+ `${SELECT_DEAL} WHERE ${where} ORDER BY ${orderBy} LIMIT $${params.length + 1} OFFSET $${params.length + 2}`,
+ [...params, limit + 1, offset]);
+ const hasMore = rows.rows.length > limit;
+ res.json({ offset, limit, hasMore, rows: rows.rows.slice(0, limit).map(shapeRow) });
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+
+ // ── one deal: the sale event + its parcel + all sibling sale events ───────
+ app.get('/api/deals/:id', async (req: Request, res: Response) => {
+ try {
+ const id = Math.floor(Number(req.params.id));
+ if (!Number.isFinite(id) || id <= 0) return res.status(400).json({ error: 'bad deal id' });
+ const d = await query<any>(
+ `SELECT pe.id, pe.county_fips, pe.source_id AS ain,
+ to_char(pe.event_date,'YYYY-MM-DD') AS sale_date, pe.amount AS sale_price,
+ pe.doc_type, pe.doc_number, pe.source AS source_key, pe.source_url,
+ pe.detail->>'grantor' AS grantor, pe.detail->>'grantee' AS grantee,
+ cp.address, cp.city, cp.zip, cp.ctype, cp.use_desc, cp.use_class,
+ cp.assessed_total, cp.assessed_land, cp.assessed_imp, cp.roll_year,
+ cp.sqft, cp.year_built, cp.units, cp.recording_date,
+ r.name AS county_name, r.state_code
+ FROM parcel_event pe
+ JOIN commercial_parcel cp ON cp.county_fips = pe.county_fips AND cp.ain = pe.source_id
+ LEFT JOIN region r ON r.fips = pe.county_fips AND r.region_type='county'
+ WHERE pe.id = $1 AND pe.event_type='sale'`, [id]);
+ if (!d.rows.length) return res.status(404).json({ error: 'deal not found' });
+ const deal = d.rows[0];
+ // all sale events on the same parcel (deal history) — each is its own deal id
+ const hist = await query<any>(
+ `SELECT id, to_char(event_date,'YYYY-MM-DD') AS event_date, amount, doc_type, doc_number,
+ source_url, detail->>'grantor' AS grantor, detail->>'grantee' AS grantee
+ FROM parcel_event
+ WHERE county_fips=$1 AND source_id=$2 AND event_type='sale'
+ ORDER BY event_date DESC NULLS LAST LIMIT 50`, [deal.county_fips, deal.ain]);
+ const links = await query<any>(
+ `SELECT kind, url, label FROM parcel_links WHERE county_fips=$1 AND source_id=$2`,
+ [deal.county_fips, deal.ain]);
+ res.json({
+ id: Number(deal.id),
+ county_fips: deal.county_fips,
+ ain: deal.ain,
+ county_name: deal.county_name || deal.county_fips,
+ state_code: deal.state_code || null,
+ sale: {
+ date: deal.sale_date,
+ price: deal.sale_price != null ? Number(deal.sale_price) : null,
+ doc_type: deal.doc_type || null,
+ doc_number: deal.doc_number || null,
+ grantor: deal.grantor || null, // seller, TEXT only
+ grantee: deal.grantee || null, // buyer, TEXT only
+ source_key: deal.source_key || null,
+ record_url: deal.source_url || null, // county public record for THIS deed
+ },
+ parcel: {
+ address: deal.address, city: deal.city, zip: deal.zip,
+ ctype: deal.ctype, use_desc: deal.use_desc, use_class: deal.use_class,
+ assessed_total: deal.assessed_total != null ? Number(deal.assessed_total) : null,
+ assessed_land: deal.assessed_land != null ? Number(deal.assessed_land) : null,
+ assessed_improvement: deal.assessed_imp != null ? Number(deal.assessed_imp) : null,
+ roll_year: deal.roll_year || null,
+ recording_date: deal.recording_date || null,
+ sqft: deal.sqft != null ? Number(deal.sqft) : null,
+ year_built: deal.year_built != null ? Number(deal.year_built) : null,
+ units: deal.units != null ? Number(deal.units) : null,
+ },
+ history: hist.rows.map((h) => ({
+ id: Number(h.id), date: h.event_date,
+ price: h.amount != null ? Number(h.amount) : null,
+ doc_type: h.doc_type || null, doc_number: h.doc_number || null,
+ grantor: h.grantor || null, grantee: h.grantee || null,
+ record_url: h.source_url || null,
+ })),
+ links: links.rows.map((l) => ({ kind: l.kind, url: l.url, label: l.label })),
+ pricing_note: 'Sale price + buyer/seller (grantor/grantee) are recorded public deed facts. County assessor/record link is the public-records source; names are TEXT only (no firm links).',
+ });
+ } catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
+ });
+}
diff --git a/src/server/index.ts b/src/server/index.ts
index 9ce239b..937c530 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -13,6 +13,7 @@ import { mountListings } from './listings.ts';
import { mountCommercial } from './commercial.ts';
import { mountSublease } from './sublease.ts';
import { mountParcels } from './parcels.ts';
+import { mountDeals } from './deals.ts';
const __dirname = dirname(fileURLToPath(import.meta.url));
const ROOT = join(__dirname, '..', '..');
@@ -515,6 +516,7 @@ mountListings(app);
mountCommercial(app);
mountSublease(app);
mountParcels(app);
+mountDeals(app);
app.use(express.static(join(ROOT, 'public')));
← fcf4fb9 TK-5: fix Hennepin assemblage guard to be prod-scale safe (A
·
back to Nationalrealestate
·
usre: deals.html feed grid with full href-drill + /deal/:id 26fa1e5 →