← back to Nationalrealestate
src/server/deals.ts
244 lines
/**
* 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],
};
// Named legacy sort presets (kept for backward-compat with existing deep links).
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',
};
// Column whitelist for ?sort=<col>&order=asc|desc — one fixed ORDER-BY fragment per
// EVERY column the deals feed returns (SQL-injection-safe: raw input never reaches SQL,
// only these fixed strings do). event_date is the stable tiebreaker for deterministic paging.
const SORT_COLS: Record<string, string> = {
sale_date: 'pe.event_date',
sale_price: 'pe.amount',
ctype: 'cp.ctype',
address: 'cp.address',
city: 'cp.city',
county_name: 'r.name',
sqft: 'cp.sqft',
year_built: 'cp.year_built',
doc_number: 'pe.doc_number',
grantor: "pe.detail->>'grantor'",
grantee: "pe.detail->>'grantee'",
};
/** Resolve ORDER BY from ?sort=&order= (whitelist) or the legacy ?sort=<preset>. */
function resolveOrderBy(q: Request['query']): string {
const sort = String(q.sort || '');
const col = SORT_COLS[sort];
if (col) {
const desc = String(q.order || 'desc').toLowerCase() !== 'asc';
const dir = desc ? 'DESC' : 'ASC';
return `${col} ${dir} NULLS LAST, pe.event_date DESC, pe.id DESC`;
}
return SORTS[sort] || SORTS.recent;
}
/** 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 = resolveOrderBy(req.query);
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) }); }
});
}