`db/migrations/016_restaurants.sql`

52 lines
-- Famous LA celebrity restaurants + sightings.
--
-- Schema is two tables:
--   restaurant         — the venue (name, addr, lat/lng, opened/closed)
--   restaurant_sighting — a person seen at the venue, with date_range +
--                         source_tier (per PLAN.md privacy guardrail)
--
-- Sightings are CLAIMS, never eternal truths. Every sighting requires a
-- source citation. Tier system: A=govt, B=archive, C=verified secondary
-- (Wikipedia article, LATimes), D=tabloid/social. Each sighting can be
-- specific-date OR era-based ("regular in the 70s") via the era field.

CREATE TABLE IF NOT EXISTS restaurant (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  slug            text UNIQUE NOT NULL,
  name            text NOT NULL,
  cuisine         text,                 -- "Italian", "Steakhouse", etc.
  address         text,
  city            text,
  state           text DEFAULT 'CA',
  zip             text,
  lat             numeric(10,7),
  lng             numeric(10,7),
  opened_year     int,
  closed_year     int,                  -- null = still open
  blurb           text,                 -- 1-paragraph editorial intro
  source_url      text,                 -- Wikipedia or other primary source
  hero_image      text,                 -- public-domain image only (no stock)
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_restaurant_city  ON restaurant (lower(city));
CREATE INDEX IF NOT EXISTS idx_restaurant_geo   ON restaurant (lat, lng);

CREATE TABLE IF NOT EXISTS restaurant_sighting (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  restaurant_id   uuid NOT NULL REFERENCES restaurant(id) ON DELETE CASCADE,
  entity_id       uuid REFERENCES entity(id) ON DELETE SET NULL,  -- if entity exists
  person_name     text NOT NULL,        -- always populated, even if entity_id null
  era             text,                  -- "1970s", "Golden Age", etc.
  seen_date       date,                  -- specific if known
  notes           text,                  -- "regular for decades", "last visit before death"
  source_tier     char(1) NOT NULL CHECK (source_tier IN ('A','B','C','D')),
  source_url      text,                  -- Wikipedia, LATimes, etc.
  source_label    text,                  -- "Wikipedia · The Ivy article"
  public_visible  boolean NOT NULL DEFAULT true,
  recorded_at     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_sighting_rest    ON restaurant_sighting (restaurant_id);
CREATE INDEX IF NOT EXISTS idx_sighting_entity  ON restaurant_sighting (entity_id);
CREATE INDEX IF NOT EXISTS idx_sighting_date    ON restaurant_sighting (seen_date);