[object Object]

← back to Commercialrealestate

auto-save: 2026-07-07T08:59:35 (4 files) — scripts/export-residential-snapshots.js scripts/fetch-condos-redfin.js scripts/fetch-sfr-redfin.js scripts/db/price-drop-migration.sql

18bf3fce8f320ab73814055b73f8876d166a3647 · 2026-07-07 08:59:42 -0700 · Steve Abrams

Files touched

Diff

commit 18bf3fce8f320ab73814055b73f8876d166a3647
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Jul 7 08:59:42 2026 -0700

    auto-save: 2026-07-07T08:59:35 (4 files) — scripts/export-residential-snapshots.js scripts/fetch-condos-redfin.js scripts/fetch-sfr-redfin.js scripts/db/price-drop-migration.sql
---
 scripts/db/price-drop-migration.sql     | 15 +++++++++++++++
 scripts/export-residential-snapshots.js |  2 +-
 scripts/fetch-condos-redfin.js          |  4 +++-
 scripts/fetch-sfr-redfin.js             |  4 +++-
 4 files changed, 22 insertions(+), 3 deletions(-)

diff --git a/scripts/db/price-drop-migration.sql b/scripts/db/price-drop-migration.sql
new file mode 100644
index 0000000..45ced55
--- /dev/null
+++ b/scripts/db/price-drop-migration.sql
@@ -0,0 +1,15 @@
+-- Price-change tracking: prev_price (last different price) + price_changed_at (when it last changed).
+DO $$ DECLARE t text; BEGIN
+  FOREACH t IN ARRAY ARRAY['sfr','condo'] LOOP
+    EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS prev_price numeric', t);
+    EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS price_changed_at timestamptz', t);
+  END LOOP;
+END $$;
+CREATE OR REPLACE VIEW condo_card AS
+ SELECT c.id, c.address, c.city, c.zip, c.price, c.hoa, c.beds, c.baths, c.sqft, c.year_built,
+        c.project_name, c.warrantable_status, c.warrant_source, c.warrant_signals, c.broker_name,
+        COALESCE(c.firm_name, f.name) AS firm_name, c.source, c.created_at,
+        c.status, c.last_seen, c.off_market_at, c.disposition, c.sold_price, c.sold_date,
+        c.days_on_market, c.listed_date, c.market_status, c.prev_market_status,
+        c.prev_price, c.price_changed_at, c.lat, c.lng
+   FROM condo c LEFT JOIN firm f ON f.id = c.firm_id;
diff --git a/scripts/export-residential-snapshots.js b/scripts/export-residential-snapshots.js
index 74de674..4b6c443 100644
--- a/scripts/export-residential-snapshots.js
+++ b/scripts/export-residential-snapshots.js
@@ -20,7 +20,7 @@ const WHERE = `WHERE price > 0 AND (status='active' OR off_market_at > now() - i
   const sfr = (await pool.query(`
     SELECT id, address, city, zip, price, beds, baths, sqft, year_built, firm_name, broker_name,
            source, lat, lng, status, off_market_at, disposition, sold_price, sold_date, last_seen, created_at,
-           days_on_market, listed_date, market_status, prev_market_status
+           days_on_market, listed_date, market_status, prev_market_status, prev_price, price_changed_at
       FROM sfr ${WHERE} ORDER BY (status='active') DESC, created_at DESC, price DESC`)).rows;
   fs.writeFileSync(path.join(ROOT, 'data', 'sfr-redfin.json'), JSON.stringify({
     meta: { source: 'Redfin LA County single-family — feed-first (gis-csv), local Chrome',
diff --git a/scripts/fetch-condos-redfin.js b/scripts/fetch-condos-redfin.js
index b2ac926..5d40bff 100644
--- a/scripts/fetch-condos-redfin.js
+++ b/scripts/fetch-condos-redfin.js
@@ -241,7 +241,9 @@ function condosFromCSV(text, regionName) {
              warrant_signals=EXCLUDED.warrant_signals,
              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,
-             prev_market_status=condo.market_status, market_status=EXCLUDED.market_status`,
+             prev_market_status=condo.market_status, market_status=EXCLUDED.market_status,
+             prev_price=CASE WHEN EXCLUDED.price IS DISTINCT FROM condo.price THEN condo.price ELSE condo.prev_price END,
+             price_changed_at=CASE WHEN EXCLUDED.price IS DISTINCT FROM condo.price THEN now() ELSE condo.price_changed_at END`,
           [c.id, c.address, c.city, c.zip, c.price, c.hoa, c.beds, c.baths, c.sqft, c.year_built,
            c.project_name, c.listing_text, c.warrantable_status, c.warrant_source,
            JSON.stringify(c.warrant_signals), firmId, c.firm_name, c.broker_name, c.source, c.days_on_market ?? null, c.market_status ?? null]);
diff --git a/scripts/fetch-sfr-redfin.js b/scripts/fetch-sfr-redfin.js
index 755a66b..95d026a 100644
--- a/scripts/fetch-sfr-redfin.js
+++ b/scripts/fetch-sfr-redfin.js
@@ -154,7 +154,9 @@ function loadRegions() {
          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,
-         prev_market_status=sfr.market_status, market_status=EXCLUDED.market_status
+         prev_market_status=sfr.market_status, market_status=EXCLUDED.market_status,
+         prev_price=CASE WHEN EXCLUDED.price IS DISTINCT FROM sfr.price THEN sfr.price ELSE sfr.prev_price END,
+         price_changed_at=CASE WHEN EXCLUDED.price IS DISTINCT FROM sfr.price THEN now() ELSE sfr.price_changed_at END
        RETURNING (xmax = 0) AS inserted`,
       [s.id, s.address, s.city, s.zip, s.price, s.beds, s.baths, s.sqft, s.year_built, s.listing_text, s.source, s.days_on_market ?? null, s.market_status ?? null])
       .catch(() => null);

← bf516b7 reports: refresh generated HTML (morning/afternoon/condos/re  ·  back to Commercialrealestate  ·  auto-save: 2026-07-07T08:59:49 (2 files) — scripts/export-re 809771f →