re-flyer /properties: include no-sale parcels in the 4 index counties (Steve 2026-08-24) — fills the LA hole 2,605->126,324; assessed-only rows sink to bottom (mentions:0), deals preserved

b767d0fccbc2538bcf07ed448fd7055ec6716a77 · 2026-08-24 13:23:10 -0700 · Steve Abrams

Files touched

M scripts/ingest-usre-parcels.mjs

Diff

commit b767d0fccbc2538bcf07ed448fd7055ec6716a77
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Mon Aug 24 13:23:10 2026 -0700

    re-flyer /properties: include no-sale parcels in the 4 index counties (Steve 2026-08-24) — fills the LA hole 2,605->126,324; assessed-only rows sink to bottom (mentions:0), deals preserved
---
 scripts/ingest-usre-parcels.mjs | 13 +++++++++----
 1 file changed, 9 insertions(+), 4 deletions(-)

diff --git a/scripts/ingest-usre-parcels.mjs b/scripts/ingest-usre-parcels.mjs
index 652763f..aadbdab 100644
--- a/scripts/ingest-usre-parcels.mjs
+++ b/scripts/ingest-usre-parcels.mjs
@@ -65,7 +65,13 @@ for (const p of news) {
 }
 const newsKeys = new Set(props.keys());
 
-// ── 2. pull every commercial parcel that has a recorded SALE, with its sale events aggregated ─────────────
+// ── 2. pull every ADDRESSED commercial parcel in the index counties, with its sale events aggregated ──────
+// Steve 2026-08-24 (yoloforever re-CRE cycle 5): scope to the 4 counties usre has deal coverage for and
+// include NO-SALE parcels too (not just EXISTS-a-sale) — this fills the LA hole (usre holds 132,582 LA
+// commercial parcels but only 2,606 had a sale event = 2% coverage) WITHOUT the national 8× blowup that
+// pulling all 20 commercial_parcel counties would cause. A no-sale parcel still carries address/type/
+// assessed_total/sqft/year_built and sinks to the bottom (mentions:0); sales attach where they exist.
+const INDEX_COUNTIES = ['12086', '06037', '37183', '41005'];   // Miami-Dade, LA, Wake, Clackamas
 const sql = `SELECT row_to_json(t) FROM (
   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,
@@ -80,8 +86,7 @@ const sql = `SELECT row_to_json(t) FROM (
   FROM commercial_parcel cp
   LEFT JOIN region r ON r.fips = cp.county_fips AND r.region_type = 'county'
   WHERE cp.address IS NOT NULL AND cp.address <> ''
-    AND EXISTS (SELECT 1 FROM parcel_event pe
-                WHERE pe.county_fips = cp.county_fips AND pe.source_id = cp.ain AND pe.event_type = 'sale')
+    AND cp.county_fips IN (${INDEX_COUNTIES.map(c => `'${c}'`).join(', ')})
 ) 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));
@@ -157,5 +162,5 @@ const out = [...props.values()].map(p => {
 const compact = (k, v) => (v === null || v === '') ? undefined : v;
 writeFileSync(INDEX, JSON.stringify(out, compact, 0));
 const newRecords = out.length - newsKeys.size;
-console.log(`usre parcel enrich: ${rows.length} commercial parcels w/ sale events -> matched ${matched} existing news properties, added ${added} new -> ${out.length} total properties (${newsKeys.size} were news-built). -> public/properties/property-index.json`);
+console.log(`usre parcel enrich: ${rows.length} addressed commercial parcels (${INDEX_COUNTIES.length} index counties, sale + no-sale) -> matched ${matched} existing news properties, added ${added} new -> ${out.length} total properties (${newsKeys.size} were news-built). -> public/properties/property-index.json`);
 for (const p of out.filter(p => p.mentions > 1).slice(0, 6)) console.log(`  ${(p.top_price || p.latest_price || '—').padStart(7)}  ${(p.property_type || '?').padEnd(11)} ${(p.city || '').padEnd(15)} ${p.address}  [${p.mentions}× ${p.source_count} src]`);

← b0f6ccf auto-data-snapshot: 2026-08-24T12:55:01 (6 data files) — dat · back to Re Flyer Aggregator · properties viewer: show 'assessed $X' for no-sale public-rec 749d916 →