← back to Nationalrealestate
TK-10139: canonical address_key normalizer (strip trailing state/zip), honest commercial BOR empty-state
39e9dbe0c173e01bd6962cc2e83a88350e9848f8 · 2026-08-02 08:09:21 -0700 · Steve Abrams
- computeAddressKey now strips trailing ", ST", " ZIP" tokens so a listing's full
"street, city, ST, ZIP" and a parcel's (address, city) fields reduce to the same key.
- new addressKeySql() shared SQL builder mirrors it exactly; imported by commercial.ts,
listings.ts, and the backfill so the join keys can never drift.
- commercial-property.html BOR card: null both -> explicit muted note instead of bare '—'.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M db/backfill-broker-of-record.tsM public/commercial-property.htmlM src/server/broker-of-record.tsM src/server/commercial.tsM src/server/listings.ts
Diff
commit 39e9dbe0c173e01bd6962cc2e83a88350e9848f8
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun Aug 2 08:09:21 2026 -0700
TK-10139: canonical address_key normalizer (strip trailing state/zip), honest commercial BOR empty-state
- computeAddressKey now strips trailing ", ST", " ZIP" tokens so a listing's full
"street, city, ST, ZIP" and a parcel's (address, city) fields reduce to the same key.
- new addressKeySql() shared SQL builder mirrors it exactly; imported by commercial.ts,
listings.ts, and the backfill so the join keys can never drift.
- commercial-property.html BOR card: null both -> explicit muted note instead of bare '—'.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
db/backfill-broker-of-record.ts | 18 +++++---------
public/commercial-property.html | 8 ++++---
src/server/broker-of-record.ts | 52 +++++++++++++++++++++++++++++++++++++++--
src/server/commercial.ts | 9 ++++---
src/server/listings.ts | 3 ++-
5 files changed, 67 insertions(+), 23 deletions(-)
diff --git a/db/backfill-broker-of-record.ts b/db/backfill-broker-of-record.ts
index eabdebe..7954eeb 100644
--- a/db/backfill-broker-of-record.ts
+++ b/db/backfill-broker-of-record.ts
@@ -11,20 +11,14 @@
*/
import 'dotenv/config';
import { pool, query } from './pool.ts';
+import { addressKeySql } from '../src/server/broker-of-record.ts';
-// normalized address_key must match computeAddressKey() in src/server/broker-of-record.ts:
-// lower(trim(collapse-whitespace("address, city"))).
+// CANONICAL address_key — uses the SINGLE shared normalizer (addressKeySql), identical to
+// computeAddressKey() in src/server/broker-of-record.ts, so backfilled keys match the recorder,
+// the listings-ingest engine, and the commercial + listings API joins (state/zip stripped).
// NOTE: the `listing` table has NO separate city column — its `address` is already the
-// full "street, city, state, zip" string — so here address_key = normalized full address
-// (city is null). computeAddressKey with a null city produces exactly this form.
-const ADDRESS_KEY_SQL = `
- lower(
- regexp_replace(
- btrim(coalesce(l.address,'')),
- '\\s+', ' ', 'g'
- )
- )
-`;
+// full "street, city, state, zip" string — so address_key = normalized full address.
+const ADDRESS_KEY_SQL = addressKeySql(`coalesce(l.address,'')`);
async function main() {
const before = await query<{ n: string }>(`SELECT count(*)::text n FROM broker_of_record_history`);
diff --git a/public/commercial-property.html b/public/commercial-property.html
index b546d35..53724ce 100644
--- a/public/commercial-property.html
+++ b/public/commercial-property.html
@@ -77,11 +77,13 @@ function dstr(d){return d?new Date(d).toLocaleDateString(undefined,{year:'numeri
</div>
<div class="note">${p.pricing_note||''}</div>
<h2>Broker of Record</h2>
+ ${(p.last_broker_of_record||p.last_firm_of_record)?`
<div class="stats">
- <div class="stat"><div class="k">Last Broker of Record</div><div class="v">${p.last_broker_of_record||'—'}</div></div>
- <div class="stat"><div class="k">Last Firm of Record</div><div class="v${p.last_firm_of_record?' g':''}">${p.last_firm_of_record||'—'}</div></div>
+ ${p.last_broker_of_record?`<div class="stat"><div class="k">Last Broker of Record</div><div class="v">${p.last_broker_of_record}</div></div>`:''}
+ ${p.last_firm_of_record?`<div class="stat"><div class="k">Last Firm of Record</div><div class="v g">${p.last_firm_of_record}</div></div>`:''}
</div>
- ${p.last_broker_observed_at?`<div class="note">Broker/firm of record last observed ${dstr(p.last_broker_observed_at)}. Recorded from public listing activity; every change is logged (append-only history).</div>`:'<div class="note">No broker/firm of record recorded for this address yet.</div>'}
+ ${p.last_broker_observed_at?`<div class="note">Broker/firm of record last observed ${dstr(p.last_broker_observed_at)}. Recorded from public listing activity; every change is logged (append-only history).</div>`:''}
+ `:'<div class="note">No listing broker on record for this parcel. The LA County assessor roll carries no broker/firm of record; this field populates from public commercial listing activity when a match exists for this address.</div>'}
<h2>Comparable ${p.type_label} Assets${p.city?` in ${p.city}`:''}</h2>
<div class="row">${compRows.map(c=>`<a class="mini" href="/commercial-property.html?ain=${c.ain}">
<div class="a">${c.address||'—'}</div>
diff --git a/src/server/broker-of-record.ts b/src/server/broker-of-record.ts
index f9637fa..df14fa6 100644
--- a/src/server/broker-of-record.ts
+++ b/src/server/broker-of-record.ts
@@ -31,12 +31,60 @@ export interface BrokerOfRecordInput {
source_url?: string | null;
}
-/** normalized "address, city": lowercased, trimmed, internal whitespace collapsed */
+/**
+ * CANONICAL address_key normalization — the SINGLE source of truth, imported by the
+ * recorder, the backfill, the listings-ingest engine, and the commercial + listings API
+ * joins so they can never drift apart.
+ *
+ * Goal: a listing's full "street, city, ST, ZIP" string and a commercial_parcel's separate
+ * (address="street", city="city") fields for the SAME street both reduce to the SAME key,
+ * e.g. both "3601 Roosevelt Ave, San Antonio, TX, 78225" and ("3601 Roosevelt Ave","San Antonio")
+ * → "3601 roosevelt ave, san antonio".
+ *
+ * Steps:
+ * 1. combine address + optional city as "address, city"
+ * 2. lowercase, trim, collapse internal whitespace
+ * 3. strip trailing state/zip tokens from the END — a 2-letter US state and/or a 5(-4) ZIP,
+ * whether comma-separated (", tx, 78225") or space-separated ("tx 78225"), repeatedly.
+ *
+ * IMPORTANT: the matching SQL in `addressKeySql()` MUST stay behaviorally identical.
+ */
export function computeAddressKey(address?: string | null, city?: string | null): string {
const a = (address || '').trim();
const c = (city || '').trim();
const combined = c ? `${a}, ${c}` : a;
- return combined.toLowerCase().replace(/\s+/g, ' ').trim();
+ let k = combined.toLowerCase().replace(/\s+/g, ' ').trim();
+ // strip trailing ZIP (5 or 5-4), then trailing 2-letter state, comma- OR space-separated,
+ // repeatedly so ", tx, 78225" / " tx 78225" / ", tx" / ", 78225" all fall off.
+ let prev: string;
+ do {
+ prev = k;
+ k = k.replace(/[\s,]+\d{5}(?:-\d{4})?$/, ''); // trailing zip
+ k = k.replace(/[\s,]+[a-z]{2}$/, ''); // trailing 2-letter state
+ } while (k !== prev);
+ return k.trim().replace(/[\s,]+$/, '');
+}
+
+/**
+ * SQL expression producing the SAME canonical key as computeAddressKey(), given a raw
+ * "address, city" text expression `expr`. Callers build `expr` themselves (e.g. the listing's
+ * `address` column alone, or a parcel's `address || ', ' || city`) then wrap it here so the
+ * join key matches the recorder/backfill exactly.
+ *
+ * Mirrors computeAddressKey: lower(collapse(trim(expr))) then strip trailing zip/state tokens.
+ */
+export function addressKeySql(expr: string): string {
+ // 1. lowercase + collapse whitespace + trim
+ const base = `btrim(regexp_replace(lower(${expr}), '\\s+', ' ', 'g'))`;
+ // 2. strip trailing zip then trailing state, applied twice to peel ", ST, ZIP".
+ // Each regexp_replace peels one trailing token; two rounds cover the ST+ZIP pair
+ // (and any single leftover), matching the JS loop's fixed point for our data.
+ const stripZip = (e: string) => `regexp_replace(${e}, '[[:space:],]+[0-9]{5}(-[0-9]{4})?$', '')`;
+ const stripState = (e: string) => `regexp_replace(${e}, '[[:space:],]+[a-z]{2}$', '')`;
+ let e = base;
+ for (let i = 0; i < 2; i++) { e = stripZip(e); e = stripState(e); }
+ // final trailing punctuation/space trim
+ return `regexp_replace(${e}, '[[:space:],]+$', '')`;
}
/** stable parcel key when both county_fips + ain are present, else null */
diff --git a/src/server/commercial.ts b/src/server/commercial.ts
index bc437b8..58fc602 100644
--- a/src/server/commercial.ts
+++ b/src/server/commercial.ts
@@ -14,6 +14,7 @@
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);
@@ -176,11 +177,9 @@ export function mountCommercial(app: Express) {
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'))
+ 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];
diff --git a/src/server/listings.ts b/src/server/listings.ts
index e99a7c3..dae929d 100644
--- a/src/server/listings.ts
+++ b/src/server/listings.ts
@@ -9,6 +9,7 @@
*/
import type { Express } from 'express';
import { query } from '../../db/pool.ts';
+import { addressKeySql } from './broker-of-record.ts';
export function mountListings(app: Express): void {
// GET /api/listings?firm=<id>&county=<region_id>&source=&limit=
@@ -35,7 +36,7 @@ export function mountListings(app: Express): void {
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'))
+ ON lbor.address_key = ${addressKeySql(`coalesce(l.address,'')`)}
${where}
ORDER BY l.last_seen DESC NULLS LAST, l.id DESC
LIMIT $${params.length}`,
← 53836d2 TK-10139: fix recorder change-detection — pg returns BIGINT
·
back to Nationalrealestate
·
TK-10155: listing lifecycle detector + sweep + backfill + in a7cc0b6 →