`scripts/db/off-market-migration.sql`

35 lines
-- off-market-migration.sql — add listing lifecycle tracking to sfr + condo so we can surface homes
-- that were LISTED but PULLED OFF the market (no longer in Redfin's active status=9 feed), and label
-- each pulled home SOLD (cross-referenced against closed_sale) vs WITHDRAWN.
--
-- last_seen    : stamped now() every time the scraper sees the listing in the active feed
-- status       : 'active' (in latest full sweep) | 'off_market' (stopped appearing)
-- off_market_at: when the mark-and-sweep first flagged it gone
-- disposition  : 'sold' | 'withdrawn' (only meaningful when off_market)
-- sold_price/sold_date : filled from closed_sale when a sold match is found

DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY['sfr','condo'] LOOP
    EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS last_seen timestamptz', t);
    EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS status text DEFAULT ''active''', t);
    EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS off_market_at timestamptz', t);
    EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS disposition text', t);
    EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS sold_price numeric', t);
    EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS sold_date date', t);
    EXECUTE format('UPDATE %I SET last_seen=created_at WHERE last_seen IS NULL', t);
    EXECUTE format('UPDATE %I SET status=''active'' WHERE status IS NULL', t);
    EXECUTE format('CREATE INDEX IF NOT EXISTS %I ON %I(status)', t||'_status_idx', t);
  END LOOP;
END $$;

-- Recreate condo_card so /api/condos sees the new lifecycle columns (view had an explicit column list).
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
   FROM condo c
   LEFT JOIN firm f ON f.id = c.firm_id;