`db/migrations/005_commercial.sql`

32 lines
-- Commercial-property layer for the LA-region commercial-RE-news product.
-- Materialized from each county's parcel source (LA: read-only assessor sqlite;
-- future CA counties: their own assessor feeds) so the feed / explorer / rankings
-- query fast pg indexes instead of scanning a 2.4M-row read-only sqlite per request.
-- Multi-county by design: (county_fips, ain) is the key; LA (06037) is populated first.

CREATE TABLE IF NOT EXISTS commercial_parcel (
  county_fips    text    NOT NULL,
  ain            text    NOT NULL,           -- parcel id (LA AIN; other counties their pid)
  address        text,
  city           text,
  zip            text,
  ctype          text    NOT NULL,           -- canonical type: office|retail|industrial|hospitality|studio|parking|other
  use_desc       text,                       -- raw assessor sub-type ("Office Building", ...)
  use_class      text,                       -- Commercial | Industrial
  assessed_total numeric,
  assessed_land  numeric,
  assessed_imp   numeric,
  roll_year      text,
  recording_date date,                       -- last recorded transfer (NO price in LA roll)
  sqft           integer,
  year_built     integer,
  units          integer,
  updated_at     timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (county_fips, ain)
);

CREATE INDEX IF NOT EXISTS idx_commercial_type    ON commercial_parcel (county_fips, ctype);
CREATE INDEX IF NOT EXISTS idx_commercial_recdate ON commercial_parcel (county_fips, recording_date DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_commercial_value   ON commercial_parcel (county_fips, assessed_total DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_commercial_city    ON commercial_parcel (county_fips, city);