`db/migrations/003_property.sql`

20 lines
-- M-P1 property-level detail layer.
-- parcel_source: registry of which counties have parcel-level data and where it lives.
-- County-level NRI/FMR metrics go into metric_series (no new columns needed).

CREATE TABLE parcel_source (
  county_fips TEXT PRIMARY KEY,
  source_kind TEXT,                         -- 'sqlite' | 'pg' | 'api' | ...
  path_or_url TEXT,
  parcel_count INT,
  notes TEXT,
  loaded_at TIMESTAMPTZ
);

-- FEMA National Risk Index display strings (scores live in metric_series source 'fema_nri')
CREATE TABLE nri_ratings (
  county_fips TEXT PRIMARY KEY,
  risk_rating TEXT,                         -- 'Very Low' .. 'Very High'
  top_hazards JSONB                         -- top 3 by hazard risk score: [{hazard, score}, ...]
);