`db/migrations/001_init.sql`

145 lines
-- USRealEstate canonical schema v1.
-- Regions are the anchor; every metric, score, broker, and listing hangs off region_id.

CREATE TABLE region (
  id SERIAL PRIMARY KEY,
  region_type TEXT NOT NULL CHECK (region_type IN ('county','metro','state','zip')),
  canonical_key TEXT NOT NULL UNIQUE,      -- 'county:06037' | 'metro:31080' | 'state:CA' | 'zip:90210'
  name TEXT NOT NULL,
  state_code TEXT,
  fips TEXT,
  cbsa_code TEXT,
  lat DOUBLE PRECISION,
  lng DOUBLE PRECISION,
  population INTEGER,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_region_type ON region (region_type);
CREATE INDEX idx_region_fips ON region (fips) WHERE fips IS NOT NULL;

-- crosswalk: every source's own region id -> our region (Zillow RegionID, Redfin table_id, ACS geoid)
CREATE TABLE source_region_map (
  source TEXT NOT NULL,
  source_region_id TEXT NOT NULL,
  region_id INT NOT NULL REFERENCES region(id),
  match_method TEXT,                        -- 'fips' | 'cbsa' | 'name' | 'manual'
  PRIMARY KEY (source, source_region_id)
);

CREATE TABLE ingest_runs (
  id SERIAL PRIMARY KEY,
  source TEXT NOT NULL,
  url TEXT,
  file_hash TEXT,
  started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  finished_at TIMESTAMPTZ,
  status TEXT NOT NULL DEFAULT 'running',   -- running|ok|failed
  rows_upserted INT DEFAULT 0,
  rows_skipped INT DEFAULT 0,
  notes TEXT
);

CREATE TABLE metric_series (
  region_id INT NOT NULL REFERENCES region(id),
  source TEXT NOT NULL,                     -- 'redfin'|'zillow'|'acs'|'fhfa'|'derived'
  metric TEXT NOT NULL,                     -- 'median_sale_price','inventory','dom','sale_to_list',
                                            -- 'zhvi','zori','hpi','median_hh_income','median_gross_rent',
                                            -- 'vacancy_rate','affordability','rent_yield', ...
  period DATE NOT NULL,
  value NUMERIC,
  ingest_run_id INT REFERENCES ingest_runs(id),
  PRIMARY KEY (region_id, source, metric, period)
);
CREATE INDEX idx_ms_metric_period ON metric_series (metric, period);

CREATE TABLE region_scores (
  region_id INT NOT NULL REFERENCES region(id),
  score_date DATE NOT NULL,
  opportunity_score NUMERIC NOT NULL,
  components JSONB NOT NULL,                -- {momentum:.., affordability:.., rent_yield:.., inventory_trend:.., velocity:.., data_completeness:.., weights:{...}}
  rank INT,
  PRIMARY KEY (region_id, score_date)
);

CREATE TABLE watchlist (
  id SERIAL PRIMARY KEY,
  region_id INT NOT NULL REFERENCES region(id),
  label TEXT,
  thresholds JSONB,                         -- {metric:'zhvi_yoy', op:'>', value:0.08}
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE alerts_log (
  id SERIAL PRIMARY KEY,
  watchlist_id INT REFERENCES watchlist(id),
  fired_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  metric TEXT,
  observed NUMERIC,
  detail JSONB
);

-- ── Broker track (Steve 2026-07-21): all US brokers/brokerages, national CRCP model ──
CREATE TABLE firm (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  normalized_name TEXT,                     -- lower/stripped for dedupe across states
  website TEXT,
  phone TEXT,
  hq_city TEXT,
  hq_state TEXT,
  license_no TEXT,
  license_state TEXT,
  source TEXT NOT NULL,                     -- 'ca_dre'|'tx_trec'|'fl_dbpr'|'ny_dos'|...
  source_id TEXT NOT NULL,
  agent_count INT,
  region_id INT REFERENCES region(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (source, source_id)
);
CREATE INDEX idx_firm_norm ON firm (normalized_name);
CREATE INDEX idx_firm_state ON firm (license_state);

CREATE TABLE broker (
  id SERIAL PRIMARY KEY,
  firm_id INT REFERENCES firm(id),
  name TEXT NOT NULL,
  license_no TEXT,
  license_state TEXT,
  license_type TEXT,                        -- broker | salesperson | associate-broker (per state vocab)
  license_status TEXT,                      -- active | expired | suspended ...
  email TEXT,
  phone TEXT,
  city TEXT,
  state_code TEXT,
  source TEXT NOT NULL,
  source_id TEXT NOT NULL,
  region_id INT REFERENCES region(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (source, source_id)
);
CREATE INDEX idx_broker_firm ON broker (firm_id);
CREATE INDEX idx_broker_state ON broker (license_state);

-- pluggable listings slot: fills from broker-site pilots (M-B3) / future licensed feeds
CREATE TABLE listing (
  id SERIAL PRIMARY KEY,
  region_id INT REFERENCES region(id),
  firm_id INT REFERENCES firm(id),
  broker_id INT REFERENCES broker(id),
  source TEXT NOT NULL,
  source_id TEXT NOT NULL,
  address TEXT,
  price NUMERIC,
  beds NUMERIC,
  baths NUMERIC,
  sqft NUMERIC,
  lat DOUBLE PRECISION,
  lng DOUBLE PRECISION,
  url TEXT,
  status TEXT,
  first_seen TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_seen TIMESTAMPTZ,
  UNIQUE (source, source_id)
);
CREATE INDEX idx_listing_region ON listing (region_id);