`scripts/mark-off-market.js`

71 lines
// mark-off-market.js — after a FULL Redfin sweep, flag listings that stopped appearing (pulled off
// the market) and label each SOLD vs WITHDRAWN.
//
// Logic:
//   1. Any status='active' row whose last_seen is older than MARK_SINCE (the timestamp captured just
//      BEFORE this run's sweep started) did NOT appear in the latest sweep -> mark off_market.
//   2. For each freshly off_market row, look for a sale in closed_sale using a normalized address key
//      (house-number + first street word + zip). Match -> disposition='sold' (+ sold_price/date from
//      the most recent sale); no match -> disposition='withdrawn'.
//   3. SAFETY: refuse to flag if it would take out >40% of active rows — that means the sweep missed
//      most regions (Redfin throttle / crash), not that half the market vanished.
//
// Usage: MARK_SINCE=<ISO8601> node scripts/mark-off-market.js
//   MARK_SINCE MUST be the moment before the sweep began. Without it the script refuses to run.
'use strict';
const { Pool } = require('pg');
const pool = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });

const SINCE = process.env.MARK_SINCE;
const SAFETY_FRACTION = +(process.env.MARK_SAFETY_FRACTION || 0.40);

// normalized address key shared by listings + closed_sale: leading number + first street word + zip
const NUM = `substring(lower(trim({a})) from '^([0-9]+)')`;
const ST1 = `(regexp_split_to_array(lower(regexp_replace(trim({a}),'^[0-9]+\\s+','')),'\\s+'))[1]`;

async function sweepTable(t) {
  const total = (await pool.query(`SELECT count(*)::int n FROM ${t} WHERE status='active'`)).rows[0].n;
  const cand = (await pool.query(
    `SELECT count(*)::int n FROM ${t} WHERE status='active' AND (last_seen IS NULL OR last_seen < $1)`, [SINCE])).rows[0].n;
  if (total > 0 && cand / total > SAFETY_FRACTION) {
    return { table: t, aborted: true, total, would_flag: cand, reason: `>${Math.round(SAFETY_FRACTION*100)}% of active would flag — sweep looks incomplete` };
  }
  // 1) flag the disappeared listings
  const flagged = (await pool.query(
    `UPDATE ${t} SET status='off_market', off_market_at=now()
       WHERE status='active' AND (last_seen IS NULL OR last_seen < $1) RETURNING id`, [SINCE])).rowCount;

  // 2) sold cross-ref. Re-check every off_market row NOT already 'sold' (incl. 'withdrawn') so a home
  //    that closes escrow AFTER we flagged it withdrawn flips to sold once the sale records (30-45d lag).
  const sold = (await pool.query(
    `WITH m AS (
       SELECT DISTINCT ON (l.id) l.id, c.sold_price, c.sold_date
         FROM ${t} l
         JOIN closed_sale c
           ON c.zip = l.zip
          AND ${NUM.replace('{a}','c.address')} = ${NUM.replace('{a}','l.address')}
          AND ${ST1.replace('{a}','c.address')} = ${ST1.replace('{a}','l.address')}
        WHERE l.status='off_market' AND l.disposition IS DISTINCT FROM 'sold'
        ORDER BY l.id, c.sold_date DESC NULLS LAST)
     UPDATE ${t} SET disposition='sold', sold_price=m.sold_price, sold_date=m.sold_date
       FROM m WHERE ${t}.id = m.id RETURNING ${t}.id`)).rowCount;

  // 3) everything else off_market with no sale match = withdrawn
  const withdrawn = (await pool.query(
    `UPDATE ${t} SET disposition='withdrawn'
       WHERE status='off_market' AND disposition IS NULL RETURNING id`)).rowCount;

  return { table: t, total_active_before: total, flagged_off_market: flagged, labeled_sold: sold, labeled_withdrawn: withdrawn };
}

(async () => {
  if (!SINCE || isNaN(Date.parse(SINCE))) {
    console.error('MARK_SINCE (ISO timestamp before the sweep) is required — refusing to run.');
    process.exit(1);
  }
  const out = [];
  for (const t of ['sfr', 'condo']) out.push(await sweepTable(t));
  console.log(JSON.stringify({ mark_since: SINCE, results: out }, null, 2));
  await pool.end();
})().catch(e => { console.error(e.message); process.exit(1); });