`db/migrations/013_la_parcel_cache.sql`

35 lines
-- Cache layer for LA County parcel data fetched from the public ArcGIS REST.
-- Keyed primarily on APN (canonical), with listing_id as a convenience FK.
-- TTL is informal — refreshed when fetched_at > 30 days ago. Public records
-- don't change daily, so a generous TTL is fine.

CREATE TABLE IF NOT EXISTS la_parcel (
  apn               text PRIMARY KEY,
  listing_id        uuid REFERENCES listing(id) ON DELETE SET NULL,
  situs_address     text,
  city              text,
  zip               text,
  use_code          text,
  use_type          text,
  use_description   text,
  year_built        int,
  effective_year    int,
  sqft_main         int,
  bedrooms          int,
  bathrooms         numeric(3,1),
  units             int,
  roll_year         int,
  land_value        numeric(14,0),
  improvement_value numeric(14,0),
  total_value       numeric(14,0),
  homeowner_exempt  boolean DEFAULT false,
  lat               numeric(10,7),
  lng               numeric(10,7),
  raw               jsonb,                  -- full ArcGIS payload for forward-compat
  fetched_at        timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_la_parcel_listing  ON la_parcel (listing_id);
CREATE INDEX IF NOT EXISTS idx_la_parcel_situs    ON la_parcel (situs_address);
CREATE INDEX IF NOT EXISTS idx_la_parcel_year     ON la_parcel (year_built);