← back to Stayclaim

db/migrations/014_la_permit_cache.sql

26 lines

-- Cache for LA City building permits from Socrata pi9x-tg5x.
-- Note: City of LA only — Beverly Hills, Santa Monica, Culver City, West
-- Hollywood etc. are SEPARATE jurisdictions with their own portals (each
-- needs its own adapter). This table covers the LA City subset.

CREATE TABLE IF NOT EXISTS la_permit (
  permit_nbr      text PRIMARY KEY,
  apn             text NOT NULL,
  primary_address text,
  zip_code        text,
  permit_type     text,
  permit_subtype  text,
  permit_group    text,
  status          text,
  issue_date      date,
  status_date     date,
  valuation       numeric(14,2),
  use_code        text,
  use_description text,
  jurisdiction    text NOT NULL DEFAULT 'LA City',
  raw             jsonb,
  fetched_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_la_permit_apn         ON la_permit (apn);
CREATE INDEX IF NOT EXISTS idx_la_permit_issue_date  ON la_permit (issue_date);