← back to Nationalrealestate
TK-10139: expose last_broker_of_record + last_firm_of_record (+observed_at) on /api/commercial/property/:ain and /api/listings
90264718aec7aef7d31016f48fef7f0e160065da · 2026-08-02 07:54:36 -0700 · Steve
Files touched
M src/server/commercial.tsM src/server/listings.ts
Diff
commit 90264718aec7aef7d31016f48fef7f0e160065da
Author: Steve <steve@designerwallcoverings.com>
Date: Sun Aug 2 07:54:36 2026 -0700
TK-10139: expose last_broker_of_record + last_firm_of_record (+observed_at) on /api/commercial/property/:ain and /api/listings
---
src/server/commercial.ts | 25 +++++++++++++++++++++++--
src/server/listings.ts | 8 +++++++-
2 files changed, 30 insertions(+), 3 deletions(-)
diff --git a/src/server/commercial.ts b/src/server/commercial.ts
index 2dde07a..bc437b8 100644
--- a/src/server/commercial.ts
+++ b/src/server/commercial.ts
@@ -164,13 +164,34 @@ export function mountCommercial(app: Express) {
try {
const county = String(req.query.county || COUNTY);
const r = await query(
- `SELECT p.*, b.brief FROM commercial_parcel p
+ `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 = lower(
+ regexp_replace(
+ btrim(coalesce(p.address,'')) ||
+ CASE WHEN btrim(coalesce(p.city,'')) <> '' THEN ', ' || btrim(p.city) ELSE '' END,
+ '\\s+', ' ', 'g'))
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 p = { ...shape(r.rows[0]), brief: r.rows[0].brief || null };
+ 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) }); }
diff --git a/src/server/listings.ts b/src/server/listings.ts
index bb375d7..e99a7c3 100644
--- a/src/server/listings.ts
+++ b/src/server/listings.ts
@@ -26,10 +26,16 @@ export function mountListings(app: Express): void {
`SELECT l.id, l.source, l.source_id, l.address, l.price, l.beds, l.baths, l.sqft,
l.lat, l.lng, l.url, l.status, l.first_seen, l.last_seen,
l.firm_id, f.name AS firm_name,
- l.region_id, rg.name AS county_name, rg.state_code
+ l.region_id, rg.name AS county_name, rg.state_code,
+ -- TK-10139: newest broker/firm of record for this listing's address.
+ lbor.broker_name AS last_broker_of_record,
+ lbor.firm_name AS last_firm_of_record,
+ lbor.observed_at AS last_broker_observed_at
FROM listing l
LEFT JOIN firm f ON f.id = l.firm_id
LEFT JOIN region rg ON rg.id = l.region_id
+ LEFT JOIN last_broker_of_record lbor
+ ON lbor.address_key = lower(regexp_replace(btrim(coalesce(l.address,'')), '\\s+', ' ', 'g'))
${where}
ORDER BY l.last_seen DESC NULLS LAST, l.id DESC
LIMIT $${params.length}`,
← cd763bf TK-10139: broker-of-record history — append-only table + las
·
back to Nationalrealestate
·
TK-10139: surface Last Broker/Firm of Record — card on comme e24220d →