`db/migrations/006_place_first_satellites.sql`

90 lines
-- Place-first satellite tables per docs/deep-research-report.md.
-- The `listing` table remains the canonical place record root (its `id` is
-- the de-facto place_id). These satellites add the dimensions the report
-- mandates without renaming/restructuring existing app surfaces.

-- 1. privacy_class on every place record
ALTER TABLE listing
  ADD COLUMN IF NOT EXISTS privacy_class text NOT NULL DEFAULT 'public_history_only';

-- 2. Alternate addresses (one place can have many address strings over time)
CREATE TABLE IF NOT EXISTS address_alt (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  listing_id    uuid NOT NULL REFERENCES listing(id) ON DELETE CASCADE,
  full_address  text NOT NULL,                     -- "30874 PCH, Malibu CA 90265"
  valid_from    date,
  valid_to      date,
  canonical     boolean NOT NULL DEFAULT false,    -- exactly one canonical at a time, app-enforced
  source_tier   char(1) NOT NULL CHECK (source_tier IN ('A','B','C','D')) DEFAULT 'A',
  source_label  text,
  created_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_address_alt_listing ON address_alt (listing_id);

-- 3. Media assets — Sanborn scans, archival photos, drone imagery, postcards
CREATE TABLE IF NOT EXISTS media_asset (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  listing_id    uuid NOT NULL REFERENCES listing(id) ON DELETE CASCADE,
  kind          text NOT NULL,                     -- archival_photo | historic_map | postcard | drone | streetview_thumb | newspaper_clip
  year          int,                                -- year of the asset; nullable when undated
  caption       text,
  storage_url   text NOT NULL,                     -- where the bytes live (S3/R2/object store)
  source_tier   char(1) NOT NULL CHECK (source_tier IN ('A','B','C','D')) DEFAULT 'B',
  source_label  text,
  source_url    text,
  rights        text,                              -- "Beverly Hills Public Library — research use only", "PD", "CC-BY-SA-4.0", etc.
  added_at      timestamptz NOT NULL DEFAULT now(),
  review_status text NOT NULL DEFAULT 'approved',  -- pending | approved | rejected
  public_visible boolean NOT NULL DEFAULT true
);
CREATE INDEX IF NOT EXISTS idx_media_listing ON media_asset (listing_id);
CREATE INDEX IF NOT EXISTS idx_media_year ON media_asset (year);
CREATE INDEX IF NOT EXISTS idx_media_visible ON media_asset (public_visible) WHERE public_visible;

-- 4. Parcels with versioning. Geometry as GeoJSON text until PostGIS lands (pg17).
CREATE TABLE IF NOT EXISTS parcel (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  apn           text UNIQUE NOT NULL,              -- e.g. "4349-020-012"
  jurisdiction  text NOT NULL DEFAULT 'Los Angeles County',
  current_listing_id uuid REFERENCES listing(id),  -- the place this parcel currently maps to
  notes         text,
  created_at    timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_parcel_listing ON parcel (current_listing_id);

CREATE TABLE IF NOT EXISTS parcel_version (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  parcel_id     uuid NOT NULL REFERENCES parcel(id) ON DELETE CASCADE,
  valid_from    date NOT NULL,
  valid_to      date,                              -- NULL = current
  geometry_geojson jsonb,                          -- GeoJSON polygon; will move to PostGIS geometry later
  area_sqft     numeric(12,2),
  parent_versions uuid[],                          -- for split/merge lineage
  source_tier   char(1) NOT NULL CHECK (source_tier IN ('A','B','C','D')) DEFAULT 'A',
  source_label  text,
  recorded_at   timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_parcel_version_parcel ON parcel_version (parcel_id);
CREATE INDEX IF NOT EXISTS idx_parcel_version_validfrom ON parcel_version (valid_from);

-- 5. Structure — one place can have multiple structures across time
--    (original 1928 build → 1972 teardown → 1973 rebuild = three rows)
CREATE TABLE IF NOT EXISTS structure (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  listing_id      uuid NOT NULL REFERENCES listing(id) ON DELETE CASCADE,
  year_built      int,
  year_demolished int,
  property_type   text,                             -- single_family | duplex | apartment | commercial | mixed_use
  style           text,                             -- spanish_colonial | tudor_revival | mid_century | etc.
  bedrooms        int,
  bathrooms       numeric(3,1),
  building_area_sqft numeric(10,2),
  lot_size_sqft   numeric(12,2),
  notes           text,
  source_tier     char(1) NOT NULL CHECK (source_tier IN ('A','B','C','D')) DEFAULT 'A',
  source_label    text,
  recorded_at     timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_structure_listing ON structure (listing_id);
CREATE INDEX IF NOT EXISTS idx_structure_year_built ON structure (year_built);