`scripts/db/assessor-schema.sql`

45 lines
-- assessor-schema.sql — LA County Assessor public parcel roll, ingested into the local "cre" DB.
-- Public open data (Assessor Parcel Data Rolls 2021-2025 feature layer). PROPERTY/PARCEL records only —
-- assessor roll carries NO owner-name PII in this public layer (situs address + valuation + physical attrs).
-- One row per parcel for the loaded roll year (default 2025 snapshot).

CREATE TABLE IF NOT EXISTS assessor_parcel (
  ain                  text PRIMARY KEY,            -- Assessor Identification Number (parcel id)
  roll_year            text,
  property_location    text,                         -- full situs address string
  situs_house_no       integer,
  situs_street         text,
  situs_unit           text,
  situs_city           text,
  situs_zip5           text,
  use_type             text,                         -- SFR / CND / COM / etc.
  use_code             text,
  use_desc1            text,                         -- Residential / Commercial / ...
  use_desc2            text,                         -- Single Family Residence / ...
  year_built           integer,
  effective_year_built integer,
  sqft_main            integer,
  bedrooms             integer,
  bathrooms            integer,
  units                integer,
  recording_date       date,
  roll_land_value      bigint,
  roll_imp_value       bigint,
  roll_tot_land_imp    bigint,
  roll_total_value     bigint,
  net_taxable_value    bigint,
  is_taxable           text,
  parcel_classification text,
  admin_region         text,
  lat                  double precision,
  lng                  double precision,
  loaded_at            timestamptz DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_assessor_zip   ON assessor_parcel (situs_zip5);
CREATE INDEX IF NOT EXISTS idx_assessor_city  ON assessor_parcel (situs_city);
CREATE INDEX IF NOT EXISTS idx_assessor_use   ON assessor_parcel (use_type);
CREATE INDEX IF NOT EXISTS idx_assessor_street ON assessor_parcel (situs_street);
-- normalized house# + street for address joins (broker->listing->parcel by situs).
CREATE INDEX IF NOT EXISTS idx_assessor_addr  ON assessor_parcel (situs_house_no, situs_street, situs_zip5);