[object Object]

← back to Re Flyer Aggregator

ingest-usre-parcels: deterministic byte-identical output (re-usre, TK-10708)

62868fe0338fa0964789ab5e2f974a9a991708de · 2026-08-20 09:19:07 -0700 · Steve Abrams

The outer parcel SQL has no ORDER BY (Postgres scan order varies run-to-run), which reshuffled tie-groups and
made the 54MB index differ every cycle -> the refine loop would commit 54MB no-op diffs. Added stable
tie-breakers: rows sorted county->address->city->AIN (AIN final so condo-unit AINs sharing one canonical
address pick the same sqft/assessed each run under fill-null-first-wins), entry sort broken by price then esig,
final property sort broken by address|city. Verified byte-identical across fresh-baseline x2 + rerun.
Second-model (Kimi) review pass done; its one actionable flag (city .trim()) correctly rejected — trimming
would diverge from build-property-index's verbatim key and break the news-attach guarantee.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

Diff

commit 62868fe0338fa0964789ab5e2f974a9a991708de
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Thu Aug 20 09:19:07 2026 -0700

    ingest-usre-parcels: deterministic byte-identical output (re-usre, TK-10708)
    
    The outer parcel SQL has no ORDER BY (Postgres scan order varies run-to-run), which reshuffled tie-groups and
    made the 54MB index differ every cycle -> the refine loop would commit 54MB no-op diffs. Added stable
    tie-breakers: rows sorted county->address->city->AIN (AIN final so condo-unit AINs sharing one canonical
    address pick the same sqft/assessed each run under fill-null-first-wins), entry sort broken by price then esig,
    final property sort broken by address|city. Verified byte-identical across fresh-baseline x2 + rerun.
    Second-model (Kimi) review pass done; its one actionable flag (city .trim()) correctly rejected — trimming
    would diverge from build-property-index's verbatim key and break the news-attach guarantee.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 scripts/ingest-usre-parcels.mjs | 12 +++++++++---
 1 file changed, 9 insertions(+), 3 deletions(-)

diff --git a/scripts/ingest-usre-parcels.mjs b/scripts/ingest-usre-parcels.mjs
index 6eb5902..652763f 100644
--- a/scripts/ingest-usre-parcels.mjs
+++ b/scripts/ingest-usre-parcels.mjs
@@ -67,7 +67,7 @@ const newsKeys = new Set(props.keys());
 
 // ── 2. pull every commercial parcel that has a recorded SALE, with its sale events aggregated ─────────────
 const sql = `SELECT row_to_json(t) FROM (
-  SELECT cp.address, cp.city, cp.zip, cp.ctype, cp.use_desc, cp.sqft, cp.year_built, cp.units,
+  SELECT cp.ain, cp.address, cp.city, cp.zip, cp.ctype, cp.use_desc, cp.sqft, cp.year_built, cp.units,
          cp.assessed_total::bigint AS assessed_total, cp.county_fips,
          r.name AS county, r.state_code AS state,
          (SELECT json_agg(json_build_object(
@@ -85,6 +85,11 @@ const sql = `SELECT row_to_json(t) FROM (
 ) t`;
 const rows = execFileSync('psql', ['usre', '-t', '-A', '-c', sql], { encoding: 'utf8', maxBuffer: 1024 * 1024 * 768 })
   .trim().split('\n').filter(Boolean).map(l => JSON.parse(l));
+// deterministic parcel order (the SQL has no ORDER BY → Postgres scan order varies run-to-run) so the
+// 54MB output is byte-identical across cycles and the refine loop doesn't commit reshuffled no-op diffs.
+// county → address → city → AIN: AIN is the final tiebreaker so buildings where several parcel AINs (condo
+// units) share one canonical address pick the SAME AIN's sqft/assessed each run (fill-null is first-wins).
+rows.sort((a, b) => (a.county_fips || '').localeCompare(b.county_fips || '') || (a.address || '').localeCompare(b.address || '') || (a.city || '').localeCompare(b.city || '') || (a.ain || '').localeCompare(b.ain || ''));
 
 // keep only per-folio deep links (miami-dade #/?folio=…); LA's endpoint is a generic query, not per-property
 const usefulLink = u => u && /folio=|parcel=|ain=|pin=|[?#].*id=/.test(u) ? u : null;
@@ -132,7 +137,7 @@ for (const r of rows) {
 
 // ── 3. recompute aggregates over the UNION (news entries always retained), dedup same-sale across paths ────
 const out = [...props.values()].map(p => {
-  const sorted = p.entries.slice().sort((a, b) => (Date.parse(b.date) || 0) - (Date.parse(a.date) || 0));
+  const sorted = p.entries.slice().sort((a, b) => (Date.parse(b.date) || 0) - (Date.parse(a.date) || 0) || (b.price || 0) - (a.price || 0) || esig(a).localeCompare(esig(b)));
   const seen = new Set(), ent = [];
   for (const x of sorted) { const s = esig(x); if (!seen.has(s)) { seen.add(s); ent.push(x); } }
   const priced = ent.filter(x => x.price).sort((a, b) => (b.price || 0) - (a.price || 0));
@@ -145,7 +150,8 @@ const out = [...props.values()].map(p => {
     top_price: priced[0]?.price_label || null,
     mentions: ent.length, source_count: sources.size, sources: [...sources], entries: ent,
   };
-}).sort((a, b) => b.mentions - a.mentions || (Date.parse(b.latest) || 0) - (Date.parse(a.latest) || 0));
+}).sort((a, b) => b.mentions - a.mentions || (Date.parse(b.latest) || 0) - (Date.parse(a.latest) || 0)
+  || (a.address + '|' + (a.city || '')).localeCompare(b.address + '|' + (b.city || '')));   // stable final tie-break
 
 // drop null / empty-string keys to keep the full-universe file as lean as possible (viewer tolerates missing keys)
 const compact = (k, v) => (v === null || v === '') ? undefined : v;

← 7d71042 refine-loop cycle 7: +articles/deals 08-20 09:13  ·  back to Re Flyer Aggregator  ·  chore: lint + light refactor of news/property scripts (sessi b12f6f0 →