`scripts/db/listing-signals-migration.sql`

20 lines
-- Real Redfin listing signals (was discarding these): days_on_market + market_status from the feed,
-- derived listed_date (gap-immune "just listed"), and prev_market_status for escrow transitions.
DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY['sfr','condo'] LOOP
    EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS days_on_market int', t);
    EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS listed_date date', t);
    EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS market_status text', t);
    EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS prev_market_status text', 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
   FROM condo c LEFT JOIN firm f ON f.id = c.firm_id;