fix(sfr): upsert arbiter must be source (URL), not id — kills ~4,900 sfr_source_key dupe-key errors

be0c49da929ceb8fbca1a4127b2f53c9b4d6743f · 2026-09-18 13:59:06 -0700 · Steve

The sfr table has two unique constraints (id PK, source UNIQUE). The Redfin
upsert declared ON CONFLICT(id), but id drifts across re-sweeps (MLS# appears
late / addr+zip synthetic hash shifts) while the URL stays constant. A same-URL
new-id row slipped past the id check and violated sfr_source_key; the error was
swallowed by .catch(() => null), silently dropping the fresh price/status update
(stale listings). source is the schema's stated idempotency key. DO UPDATE never
rewrites id, so broker_sfr's FK to sfr.id stays stable. Verified in a rolled-back
txn: old arbiter throws the exact error, new arbiter updates in place, id kept,
no duplicate.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01Hi31Ew38GE15TY3oCFtz5d

Files touched

M scripts/fetch-sfr-redfin.js

Diff

commit be0c49da929ceb8fbca1a4127b2f53c9b4d6743f
Author: Steve <steve@designerwallcoverings.com>
Date:   Fri Sep 18 13:59:06 2026 -0700

    fix(sfr): upsert arbiter must be source (URL), not id — kills ~4,900 sfr_source_key dupe-key errors
    
    The sfr table has two unique constraints (id PK, source UNIQUE). The Redfin
    upsert declared ON CONFLICT(id), but id drifts across re-sweeps (MLS# appears
    late / addr+zip synthetic hash shifts) while the URL stays constant. A same-URL
    new-id row slipped past the id check and violated sfr_source_key; the error was
    swallowed by .catch(() => null), silently dropping the fresh price/status update
    (stale listings). source is the schema's stated idempotency key. DO UPDATE never
    rewrites id, so broker_sfr's FK to sfr.id stays stable. Verified in a rolled-back
    txn: old arbiter throws the exact error, new arbiter updates in place, id kept,
    no duplicate.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
    Claude-Session: https://claude.ai/code/session_01Hi31Ew38GE15TY3oCFtz5d
---
 scripts/fetch-sfr-redfin.js | 8 ++++++--
 1 file changed, 6 insertions(+), 2 deletions(-)

diff --git a/scripts/fetch-sfr-redfin.js b/scripts/fetch-sfr-redfin.js
index 9415034..63ac875 100644
--- a/scripts/fetch-sfr-redfin.js
+++ b/scripts/fetch-sfr-redfin.js
@@ -152,13 +152,17 @@ function loadRegions() {
   let added = 0, captured = 0;
   const num = v => { const n = +String(v == null ? '' : v).replace(/[^0-9.]/g, ''); return Number.isFinite(n) && n > 0 ? Math.round(n) : null; };
 
-  // Upsert one SFR (idempotent on id; source/url is also UNIQUE).
+  // Upsert one SFR. The idempotency key is the Redfin URL (source, UNIQUE) — id can drift
+  // across re-sweeps (MLS# appears late, or the addr+zip synthetic hash shifts), so we MUST
+  // arbiter on source, not id. Arbitering on id let a same-URL/new-id row slip past the ON
+  // CONFLICT check and slam into sfr_source_key (~4,900 swallowed dupe-key errors → stale rows).
+  // id is never re-written on conflict, so broker_sfr's FK to sfr.id stays stable.
   const upsert = async (s) => {
     if (!s.source) return;
     const ins = await pool.query(
       `INSERT INTO sfr(id,address,city,zip,price,beds,baths,sqft,year_built,listing_text,source,last_seen,status,days_on_market,market_status,listed_date,lat,lng)
        VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,now(),'active',$12::int,$13, CASE WHEN $12::int IS NOT NULL THEN CURRENT_DATE - $12::int ELSE NULL END,$14::float8,$15::float8)
-       ON CONFLICT(id) DO UPDATE SET price=EXCLUDED.price, beds=EXCLUDED.beds, baths=EXCLUDED.baths,
+       ON CONFLICT(source) DO UPDATE SET price=EXCLUDED.price, beds=EXCLUDED.beds, baths=EXCLUDED.baths,
          sqft=EXCLUDED.sqft, year_built=EXCLUDED.year_built,
          last_seen=now(), status='active', off_market_at=NULL, disposition=NULL, sold_price=NULL, sold_date=NULL,
          days_on_market=EXCLUDED.days_on_market, listed_date=EXCLUDED.listed_date,

← aa6fd57 afternoon CRE update 2026-09-18 · back to Commercialrealestate · auto-data-snapshot: 2026-09-18T15:26:25 (2 data files) — dat e021548 →