← back to Restaurant Directory
db/schema.sql
154 lines
-- LA County Eats — schema
-- Database: restaurant_professional_directory
--
-- Source of truth: LA County DPH Environmental Health Restaurant & Market
-- Inventory + Inspections datasets (ArcGIS Hub, item IDs in PLAN.md).
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;
-- ──────────────────────────────────────────────────────────────────────────
-- facility — one row per LA County permitted food establishment
-- ──────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS facility (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
facility_id text UNIQUE NOT NULL, -- "FA0177013" — county PK
slug text UNIQUE NOT NULL, -- "facility-name-1234-main-st-los-angeles"
name text NOT NULL, -- DBA from county
address text,
city text,
state text DEFAULT 'CA',
zip text,
lat numeric(10,7),
lng numeric(10,7),
pe_code int, -- "1632"
pe_description text, -- "RESTAURANT (0-30) SEATS HIGH RISK"
facility_type text, -- normalized: 'restaurant' | 'market' | 'bakery' etc.
seat_tier text, -- '0-30' | '31-60' | '61+'
risk_tier text, -- 'low' | 'moderate' | 'high'
owner_id text, -- "OW0005080"
owner_name text,
owner_address text,
owner_city text,
owner_state text,
owner_zip text,
source_dataset_id text NOT NULL DEFAULT '4f31c9a99e444a40a3806e3bbe7b5fdd',
source_pulled_at timestamptz NOT NULL, -- when this row's source CSV was downloaded
first_seen_at timestamptz NOT NULL DEFAULT now(), -- when WE first saw this facility
last_updated_at timestamptz NOT NULL DEFAULT now(),
is_active boolean NOT NULL DEFAULT true,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_facility_city ON facility (lower(city));
CREATE INDEX IF NOT EXISTS idx_facility_zip ON facility (zip);
CREATE INDEX IF NOT EXISTS idx_facility_geo ON facility (lat, lng);
CREATE INDEX IF NOT EXISTS idx_facility_pe ON facility (pe_code);
CREATE INDEX IF NOT EXISTS idx_facility_active ON facility (is_active) WHERE is_active = true;
CREATE INDEX IF NOT EXISTS idx_facility_name_trgm ON facility USING gin (name gin_trgm_ops);
CREATE INDEX IF NOT EXISTS idx_facility_first_seen ON facility (first_seen_at);
-- ──────────────────────────────────────────────────────────────────────────
-- inspection — many-to-one with facility (rolling 3-yr window)
-- ──────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS inspection (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
facility_id text NOT NULL REFERENCES facility(facility_id) ON DELETE CASCADE,
serial_number text UNIQUE NOT NULL, -- joins to violation
activity_date date NOT NULL,
service_code text,
service_description text,
score int, -- 0-100
grade char(1), -- 'A' | 'B' | 'C'
program_status text, -- 'ACTIVE' | 'INACTIVE'
employee_id text,
source_pulled_at timestamptz NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_inspection_facility ON inspection (facility_id);
CREATE INDEX IF NOT EXISTS idx_inspection_date ON inspection (activity_date DESC);
CREATE INDEX IF NOT EXISTS idx_inspection_grade ON inspection (grade);
-- ──────────────────────────────────────────────────────────────────────────
-- violation — many-to-one with inspection
-- ──────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS violation (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
serial_number text NOT NULL REFERENCES inspection(serial_number) ON DELETE CASCADE,
violation_status text,
violation_code text,
violation_description text,
points int,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_violation_inspection ON violation (serial_number);
-- ──────────────────────────────────────────────────────────────────────────
-- facility_enrichment — 1:1 with facility, fields we add on top
-- ──────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS facility_enrichment (
facility_id text PRIMARY KEY REFERENCES facility(facility_id) ON DELETE CASCADE,
cuisine text, -- 'Mexican' | 'Italian' | 'Sushi' (LLM-classified)
cuisine_confidence real, -- 0..1
phone text,
website text,
hours_json jsonb, -- { mon: '...', tue: '...' }
hero_image text,
hero_image_credit text,
hero_image_license text,
hero_image_source_url text,
enriched_at timestamptz,
source text -- 'osm' | 'yelp' | 'google' | 'manual' | 'llm'
);
-- ──────────────────────────────────────────────────────────────────────────
-- facility_mockup — 3 variants per facility (Four Horsemen output)
-- ──────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS facility_mockup (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
facility_id text NOT NULL REFERENCES facility(facility_id) ON DELETE CASCADE,
variant_index smallint NOT NULL CHECK (variant_index BETWEEN 1 AND 3),
horseman text NOT NULL, -- 'paper' | 'figma' | 'twentyfirst' | 'canva'
html text,
jsx text,
preview_image text,
debate_score real, -- claude-codex consensus score
debate_winner boolean DEFAULT false, -- true for the picked variant
debate_log_path text, -- file path to debate transcript
generated_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (facility_id, variant_index)
);
CREATE INDEX IF NOT EXISTS idx_mockup_facility ON facility_mockup (facility_id);
-- ──────────────────────────────────────────────────────────────────────────
-- ingest_run — log every quarterly pull
-- ──────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ingest_run (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
dataset text NOT NULL, -- 'inventory' | 'inspections' | 'violations'
source_url text NOT NULL,
started_at timestamptz NOT NULL DEFAULT now(),
finished_at timestamptz,
rows_pulled int,
rows_inserted int,
rows_updated int,
rows_unchanged int,
rows_failed int,
error text
);
-- ──────────────────────────────────────────────────────────────────────────
-- claim — claim-your-listing flow (monetization rail v2)
-- ──────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS facility_claim (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
facility_id text NOT NULL REFERENCES facility(facility_id) ON DELETE CASCADE,
claimant_email text NOT NULL,
claimant_name text,
status text NOT NULL DEFAULT 'pending', -- 'pending' | 'verified' | 'rejected'
verified_at timestamptz,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_claim_facility ON facility_claim (facility_id);
CREATE INDEX IF NOT EXISTS idx_claim_email ON facility_claim (lower(claimant_email));