← back to Nationalrealestate
src/server/commercial.ts
234 lines
/**
* LA-region commercial real-estate API — the "local commercial RE news" layer.
* Reads the materialized `commercial_parcel` table (see 005_commercial.sql +
* src/ingest/commercial/la_assessor.ts). LA carries assessed values + last
* recording date but NO sale price / owner (assessor-roll limits), so the feed is
* "recorded activity + assessed value" — never fabricated prices.
*
* GET /api/commercial/types -> taxonomy + per-type counts
* GET /api/commercial/search?type&city&q&min&max&sort&limit&offset
* GET /api/commercial/feed?type&city&limit -> recent recorded activity
* GET /api/commercial/marquee?type&city&limit -> top assets by assessed value
* GET /api/commercial/property/:ain -> single-parcel detail + context
*/
import type { Express, Request, Response } from 'express';
import { query } from '../../db/pool.ts';
import { COMMERCIAL_CATEGORIES, categoryLabel, type CommercialType } from '../lib/commercial_types.ts';
import { addressKeySql } from './broker-of-record.ts';
const COUNTY = '06037'; // LA County (first region; multi-county-ready via ?county)
const likeEscape = (s: string) => s.replace(/[\\%_]/g, c => '\\' + c);
const TYPE_KEYS = new Set(COMMERCIAL_CATEGORIES.map(c => c.key));
const clampLimit = (v: unknown, def: number, max: number) => Math.min(max, Math.max(1, Number(v) || def));
/** shared WHERE builder for type/city/q/value filters */
function buildFilter(q: Request['query']): { where: string; params: unknown[] } {
const county = String(q.county || COUNTY);
const params: unknown[] = [county];
const parts = ['county_fips = $1'];
if (q.type && TYPE_KEYS.has(String(q.type) as CommercialType)) { params.push(String(q.type)); parts.push(`ctype = $${params.length}`); }
if (q.city) { params.push(String(q.city).toUpperCase()); parts.push(`city = $${params.length}`); }
if (q.q) { params.push('%' + likeEscape(String(q.q).toUpperCase()) + '%'); parts.push(`(upper(address) LIKE $${params.length} ESCAPE '\\' OR upper(city) LIKE $${params.length} ESCAPE '\\')`); }
if (q.min) { params.push(Number(q.min)); parts.push(`assessed_total >= $${params.length}`); }
if (q.max) { params.push(Number(q.max)); parts.push(`assessed_total <= $${params.length}`); }
return { where: parts.join(' AND '), params };
}
// Named legacy sort presets (kept for backward-compat with existing links).
const SORTS: Record<string, string> = {
value_desc: 'assessed_total DESC NULLS LAST',
value_asc: 'assessed_total ASC NULLS LAST',
recent: 'recording_date DESC NULLS LAST',
sqft_desc: 'sqft DESC NULLS LAST',
};
// Column whitelist for ?sort=<col>&order=asc|desc over EVERY column the shape returns
// (SQL-injection-safe: raw input never reaches SQL — only these fixed fragments do).
// `pfx` optionally prefixes the column (the /feed + /marquee queries alias the table `p`).
const SORT_COLS: Record<string, string> = {
ain: 'ain',
address: 'address',
city: 'city',
zip: 'zip',
type: 'ctype',
use_desc: 'use_desc',
assessed_total: 'assessed_total',
assessed_land: 'assessed_land',
assessed_improvement: 'assessed_imp',
roll_year: 'roll_year',
recording_date: 'recording_date',
sqft: 'sqft',
year_built: 'year_built',
units: 'units',
};
/** Resolve ORDER BY from ?sort=&order= (whitelist) or a legacy ?sort=<preset>.
* Falls back to `def` when nothing matches. `pfx` (e.g. 'p.') prefixes whitelist cols. */
function resolveOrderBy(q: Request['query'], def: string, pfx = ''): string {
const sort = String(q.sort || '');
const col = SORT_COLS[sort];
if (col) {
const desc = String(q.order || 'desc').toLowerCase() !== 'asc';
return `${pfx}${col} ${desc ? 'DESC' : 'ASC'} NULLS LAST`;
}
return SORTS[sort] || def;
}
function shape(r: any) {
return {
ain: r.ain, address: r.address, city: r.city, zip: r.zip,
type: r.ctype, type_label: categoryLabel(r.ctype), use_desc: r.use_desc,
assessed_total: r.assessed_total != null ? Number(r.assessed_total) : null,
assessed_land: r.assessed_land != null ? Number(r.assessed_land) : null,
assessed_improvement: r.assessed_imp != null ? Number(r.assessed_imp) : null,
roll_year: r.roll_year, recording_date: r.recording_date,
sqft: r.sqft, year_built: r.year_built, units: r.units,
};
}
export function mountCommercial(app: Express) {
// taxonomy + per-type counts (drives the filter UI)
app.get('/api/commercial/types', async (req: Request, res: Response) => {
try {
const county = String(req.query.county || COUNTY);
const r = await query<{ ctype: string; n: string }>(
`SELECT ctype, count(*)::text n FROM commercial_parcel WHERE county_fips=$1 GROUP BY ctype`, [county]);
const counts = Object.fromEntries(r.rows.map(x => [x.ctype, Number(x.n)]));
const total = r.rows.reduce((s, x) => s + Number(x.n), 0);
res.json({ county, total, types: COMMERCIAL_CATEGORIES.map(c => ({ ...c, count: counts[c.key] || 0 })) });
} catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
});
// top cities by commercial count (for the filter dropdown)
app.get('/api/commercial/cities', async (req: Request, res: Response) => {
try {
const county = String(req.query.county || COUNTY);
const r = await query<{ city: string; n: string }>(
`SELECT city, count(*)::text n FROM commercial_parcel WHERE county_fips=$1 AND city IS NOT NULL
GROUP BY city ORDER BY count(*) DESC LIMIT 60`, [county]);
res.json({ cities: r.rows.map(x => ({ city: x.city, count: Number(x.n) })) });
} catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
});
// filtered browse/search
app.get('/api/commercial/search', async (req: Request, res: Response) => {
try {
const { where, params } = buildFilter(req.query);
const sort = resolveOrderBy(req.query, SORTS.value_desc);
const limit = clampLimit(req.query.limit, 60, 200);
const offset = Math.max(0, Number(req.query.offset) || 0);
const total = await query<{ n: string }>(`SELECT count(*)::text n FROM commercial_parcel WHERE ${where}`, params);
const rows = await query(
`SELECT * FROM commercial_parcel WHERE ${where} ORDER BY ${sort}, ain LIMIT $${params.length + 1} OFFSET $${params.length + 2}`,
[...params, limit, offset]);
res.json({ total: Number(total.rows[0].n), count: rows.rows.length, offset, results: rows.rows.map(shape) });
} catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
});
// recent recorded activity — the "news feed" (recording date, no price for LA)
app.get('/api/commercial/feed', async (req: Request, res: Response) => {
try {
const { where, params } = buildFilter(req.query);
const limit = clampLimit(req.query.limit, 40, 100);
// default keeps the "newest recorded first" news order; ?sort=&order= overrides (whitelist, p.-prefixed)
const orderBy = resolveOrderBy(req.query, 'p.recording_date DESC, p.assessed_total DESC NULLS LAST', 'p.');
const rows = await query(
`SELECT p.*, b.brief FROM commercial_parcel p
LEFT JOIN commercial_brief b ON b.county_fips=p.county_fips AND b.ain=p.ain
WHERE ${where.replace(/\b(county_fips|ctype|city|address|assessed_total)\b/g, 'p.$1')}
AND p.recording_date IS NOT NULL
ORDER BY ${orderBy} LIMIT $${params.length + 1}`,
[...params, limit]);
res.json({ count: rows.rows.length, results: rows.rows.map(r => ({ ...shape(r), brief: r.brief || null })) });
} catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
});
// marquee assets — top by assessed value (the newsworthy trophy hook), with briefs
app.get('/api/commercial/marquee', async (req: Request, res: Response) => {
try {
const { where, params } = buildFilter(req.query);
const limit = clampLimit(req.query.limit, 12, 50);
const rows = await query(
`SELECT p.*, b.brief FROM commercial_parcel p
LEFT JOIN commercial_brief b ON b.county_fips=p.county_fips AND b.ain=p.ain
WHERE ${where.replace(/\b(county_fips|ctype|city|address|assessed_total)\b/g, 'p.$1')}
AND p.assessed_total IS NOT NULL
ORDER BY p.assessed_total DESC LIMIT $${params.length + 1}`, [...params, limit]);
res.json({ count: rows.rows.length, results: rows.rows.map(r => ({ ...shape(r), brief: r.brief || null })) });
} catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
});
// one-shot digest for syndication (e.g. RENTV): marquee+briefs, recent activity, top LA firms
app.get('/api/commercial/digest', async (req: Request, res: Response) => {
try {
const county = String(req.query.county || COUNTY);
const [marquee, recent, firms] = await Promise.all([
query(`SELECT p.*, b.brief FROM commercial_parcel p
LEFT JOIN commercial_brief b ON b.county_fips=p.county_fips AND b.ain=p.ain
WHERE p.county_fips=$1 AND p.assessed_total IS NOT NULL AND b.brief IS NOT NULL
ORDER BY p.assessed_total DESC LIMIT 12`, [county]),
query(`SELECT p.*, b.brief FROM commercial_parcel p
LEFT JOIN commercial_brief b ON b.county_fips=p.county_fips AND b.ain=p.ain
WHERE p.county_fips=$1 AND p.recording_date IS NOT NULL
ORDER BY p.recording_date DESC, p.assessed_total DESC NULLS LAST LIMIT 20`, [county]),
query(`SELECT name, agent_count, hq_city, license_no FROM firm
WHERE license_state='CA' AND upper(hq_city)='LOS ANGELES' AND agent_count>0
ORDER BY agent_count DESC NULLS LAST LIMIT 12`),
]);
res.json({
fetched_at: new Date().toISOString(), county,
marquee: marquee.rows.map(r => ({ ...shape(r), brief: r.brief || null })),
recent: recent.rows.map(r => ({ ...shape(r), brief: r.brief || null })),
firms: firms.rows.map((f: any) => ({ name: f.name, agent_count: f.agent_count, city: f.hq_city, license_no: f.license_no })),
});
} catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
});
// generated news blurb for one asset (precomputed by ingest:commercial-briefs)
app.get('/api/commercial/brief/:ain', async (req: Request, res: Response) => {
try {
const county = String(req.query.county || COUNTY);
const r = await query<{ brief: string; model: string; generated_at: string }>(
`SELECT brief, model, generated_at FROM commercial_brief WHERE county_fips=$1 AND ain=$2`, [county, req.params.ain]);
if (!r.rows.length) return res.status(404).json({ error: 'no brief for this asset' });
res.json(r.rows[0]);
} catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
});
// single-parcel detail + county market context
app.get('/api/commercial/property/:ain', async (req: Request, res: Response) => {
try {
const county = String(req.query.county || COUNTY);
const r = await query(
`SELECT p.*, b.brief,
-- TK-10139: newest broker/firm of record for this parcel. Matches on the
-- stable parcel_key first, then on the normalized "address, city" address_key.
lbor.broker_name AS last_broker_of_record,
lbor.firm_name AS last_firm_of_record,
lbor.broker_id AS last_broker_id,
lbor.firm_id AS last_firm_id,
lbor.observed_at AS last_broker_observed_at
FROM commercial_parcel p
LEFT JOIN commercial_brief b ON b.county_fips=p.county_fips AND b.ain=p.ain
LEFT JOIN last_broker_of_record lbor
ON lbor.parcel_key = p.county_fips || ':' || p.ain
OR lbor.address_key = ${addressKeySql(
`coalesce(p.address,'') ||
CASE WHEN btrim(coalesce(p.city,'')) <> '' THEN ', ' || btrim(p.city) ELSE '' END`)}
WHERE p.county_fips=$1 AND p.ain=$2`, [county, req.params.ain]);
if (!r.rows.length) return res.status(404).json({ error: 'not found' });
const row = r.rows[0];
const p = { ...shape(row), brief: row.brief || null };
res.json({
...p,
last_broker_of_record: row.last_broker_of_record || null,
last_firm_of_record: row.last_firm_of_record || null,
last_broker_id: row.last_broker_id ?? null,
last_firm_id: row.last_firm_id ?? null,
last_broker_observed_at: row.last_broker_observed_at || null,
pricing_note: 'LA County assessor roll — assessed values + last recording date only. Sale price and owner are not in the public roll; market listing prices come from the commercial listings feed.',
});
} catch (e: any) { res.status(500).json({ error: String(e.message || e) }); }
});
}