← back to La Socrata Ingester
db/schema.sql
156 lines
-- LA Socrata/ArcGIS ingester — landing schema for the `realestate` DB.
-- Convention follows the existing *_raw tables (cslb_raw, azdre_raw): raw source
-- fidelity kept in `raw jsonb`, plus typed columns for the fields we query/join on,
-- plus provenance (dataset_id, source_url, fetched_at). Every table has a real
-- dedup key so incremental refreshes upsert cleanly (ON CONFLICT).
-- Idempotent: safe to run repeatedly.
-- ---------------------------------------------------------------------------
-- 1. Building permits (LA City LADBS) — Socrata.
-- One table holds all permit sub-datasets (bldg 2020+/2010-19/pre-2010,
-- electrical, mechanical/plumbing); dataset_id distinguishes them, so the
-- dedup key is (dataset_id, permit_nbr) to avoid cross-dataset collisions.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS la_building_permits_raw (
dataset_id text NOT NULL,
permit_nbr text NOT NULL,
primary_address text,
apn text, -- 10-digit APN → joins la_assessor_parcels_raw.ain
zip_code text,
council_district text,
permit_group text,
permit_type text,
permit_sub_type text,
use_desc text,
issue_date timestamptz,
status_desc text,
valuation numeric,
lat double precision,
lon double precision,
work_desc text,
raw jsonb,
source_url text,
fetched_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (dataset_id, permit_nbr)
);
CREATE INDEX IF NOT EXISTS idx_la_permits_apn ON la_building_permits_raw (apn);
CREATE INDEX IF NOT EXISTS idx_la_permits_issue_date ON la_building_permits_raw (issue_date);
CREATE INDEX IF NOT EXISTS idx_la_permits_zip ON la_building_permits_raw (zip_code);
-- ---------------------------------------------------------------------------
-- 2. Assessor parcels (LA County) — ArcGIS FeatureServer (NOT Socrata).
-- Annual snapshot; composite key (ain, roll_year) keeps the time-series.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS la_assessor_parcels_raw (
ain text NOT NULL, -- 10-digit AIN → joins la_building_permits_raw.apn
roll_year text NOT NULL,
assessor_id text, -- formatted APN e.g. 2038-020-084
property_location text,
situs_house_no text,
situs_street text,
situs_city text,
situs_zip5 text,
use_type text,
use_code text,
year_built text,
sqft_main numeric,
bedrooms text,
bathrooms text,
units text,
land_value numeric,
imp_value numeric,
total_value numeric,
recording_date bigint, -- unix ms epoch of last deed recording
center_lat double precision,
center_lon double precision,
raw jsonb,
source_url text,
fetched_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (ain, roll_year)
);
CREATE INDEX IF NOT EXISTS idx_la_parcels_ain ON la_assessor_parcels_raw (ain);
CREATE INDEX IF NOT EXISTS idx_la_parcels_zip ON la_assessor_parcels_raw (situs_zip5);
CREATE INDEX IF NOT EXISTS idx_la_parcels_year ON la_assessor_parcels_raw (roll_year);
-- ---------------------------------------------------------------------------
-- 3. Code enforcement cases (LA City LADBS) — Socrata (open + closed).
-- apno is unique across both datasets; a case moves open→closed by updating
-- the same row, so the key is apno alone.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS la_code_enforcement_raw (
apno text NOT NULL,
apname text,
address text, -- concatenated from stno/predir/stname/suffix/postdir
zip text,
add_dttm timestamptz, -- case opened
res_dttm timestamptz, -- case resolved (null if open)
prclid text, -- LADBS internal parcel id (NOT the AIN)
ap_type text,
apc text, -- area planning commission
stat text, -- 'O' open / 'C' closed
dataset_id text,
raw jsonb,
source_url text,
fetched_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (apno)
);
CREATE INDEX IF NOT EXISTS idx_la_code_zip ON la_code_enforcement_raw (zip);
CREATE INDEX IF NOT EXISTS idx_la_code_stat ON la_code_enforcement_raw (stat);
CREATE INDEX IF NOT EXISTS idx_la_code_add ON la_code_enforcement_raw (add_dttm);
-- ---------------------------------------------------------------------------
-- 4. Active business registrations (LA City) — Socrata.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS la_business_registrations_raw (
location_account text NOT NULL,
business_name text,
dba_name text,
street_address text,
city text,
zip_code text,
naics text,
primary_naics_description text,
council_district text,
location_start_date date,
location_end_date date, -- null if active
lat double precision,
lon double precision,
raw jsonb,
source_url text,
fetched_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (location_account)
);
CREATE INDEX IF NOT EXISTS idx_la_biz_zip ON la_business_registrations_raw (zip_code);
CREATE INDEX IF NOT EXISTS idx_la_biz_naics ON la_business_registrations_raw (naics);
CREATE INDEX IF NOT EXISTS idx_la_biz_start ON la_business_registrations_raw (location_start_date);
-- ---------------------------------------------------------------------------
-- 5. Film permits (WeHo) — Socrata. LA City proper has NO live public dataset
-- (both deprecated); FilmLA bulk is CPRA-only. WeHo is the live free feed.
-- Schema kept tolerant (raw jsonb + best-effort typed fields).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS la_film_permits_raw (
permit_id text NOT NULL,
title text,
permit_type text,
address text,
start_date timestamptz,
end_date timestamptz,
raw jsonb,
source_url text,
fetched_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (permit_id)
);
-- ---------------------------------------------------------------------------
-- Incremental high-water-mark state (one row per source).
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS la_ingest_state (
source text NOT NULL PRIMARY KEY,
dataset_id text,
last_cursor text, -- max value of the source's cursor field seen so far
last_run timestamptz,
rows_upserted bigint NOT NULL DEFAULT 0,
last_status text
);