`db/migrations/004_place_event.sql`

23 lines
-- Append-only event ledger per research report § "How to handle the big-data side":
--   Never overwrite history. Insert new events with valid_from, valid_to, recorded_at, source_id.
-- Each event ties to a listing (place) and carries its own source tier.
CREATE TABLE IF NOT EXISTS place_event (
  id            uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  listing_id    uuid NOT NULL REFERENCES listing(id) ON DELETE CASCADE,
  kind          text NOT NULL,                       -- permit | sale | remodel | demolition | designation | media | occupancy
  summary       text NOT NULL,
  valid_from    date,                                -- when the event began (permit issued, sale recorded)
  valid_to      date,                                -- when the event ended (occupancy range; null for instant)
  recorded_at   timestamptz NOT NULL DEFAULT now(),  -- when WE recorded the event
  source_tier   char(1) NOT NULL CHECK (source_tier IN ('A','B','C','D')),
  source_label  text,
  source_url    text,
  source_id     text,                                -- external reference (permit #, doc #, etc.)
  confidence    numeric(3,2) NOT NULL DEFAULT 0.80 CHECK (confidence BETWEEN 0 AND 1),
  public_visible boolean NOT NULL DEFAULT true
);
CREATE INDEX IF NOT EXISTS idx_place_event_listing  ON place_event (listing_id);
CREATE INDEX IF NOT EXISTS idx_place_event_kind     ON place_event (kind);
CREATE INDEX IF NOT EXISTS idx_place_event_recent   ON place_event (recorded_at DESC);
CREATE INDEX IF NOT EXISTS idx_place_event_validfrom ON place_event (valid_from DESC);