`db/migrations/024_measure_attribution_phase_a.sql`

225 lines
-- 024 · Measure attribution Phase A structural contract
--
-- ADDITIVE / DORMANT ONLY. This migration defines storage and integrity
-- boundaries; it does not add API routes, enable feature flags, send messages,
-- create prices, backfill attribution, or alter legacy measure-job outcomes.

BEGIN;

ALTER TABLE measure_jobs
  ADD COLUMN IF NOT EXISTS uuid UUID,
  ADD COLUMN IF NOT EXISTS capture_idempotency_key TEXT,
  ADD COLUMN IF NOT EXISTS captured_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS capture_source TEXT,
  ADD COLUMN IF NOT EXISTS environment TEXT,
  ADD COLUMN IF NOT EXISTS is_test BOOLEAN,
  ADD COLUMN IF NOT EXISTS test_reason TEXT,
  ADD COLUMN IF NOT EXISTS consent_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS consent_version TEXT,
  ADD COLUMN IF NOT EXISTS consent_purposes TEXT[],
  ADD COLUMN IF NOT EXISTS contact_preference TEXT,
  ADD COLUMN IF NOT EXISTS measurement_method TEXT,
  -- NULL deliberately preserves the distinction between legacy unknown media
  -- and an explicit empty capture set.
  ADD COLUMN IF NOT EXISTS room_captures JSONB,
  ADD COLUMN IF NOT EXISTS surface_state TEXT,
  ADD COLUMN IF NOT EXISTS access_constraints TEXT[],
  ADD COLUMN IF NOT EXISTS timeline_band TEXT,
  ADD COLUMN IF NOT EXISTS lead_state TEXT,
  ADD COLUMN IF NOT EXISTS responded_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS accepted_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS declined_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS expired_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS tier_snapshot TEXT,
  ADD COLUMN IF NOT EXISTS tier_reason_codes TEXT[],
  ADD COLUMN IF NOT EXISTS tier_policy_version TEXT,
  ADD COLUMN IF NOT EXISTS retention_class TEXT,
  ADD COLUMN IF NOT EXISTS subject_erased_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS current_routing_attempt_id BIGINT;

CREATE UNIQUE INDEX IF NOT EXISTS measure_jobs_uuid_uq
  ON measure_jobs (uuid) WHERE uuid IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS measure_jobs_capture_idempotency_uq
  ON measure_jobs (capture_idempotency_key) WHERE capture_idempotency_key IS NOT NULL;

ALTER TABLE measure_jobs
  ADD CONSTRAINT measure_jobs_capture_source_ck CHECK (
    capture_source IS NULL OR capture_source IN ('measure_web','booking_handoff','partner','admin_test')
  ) NOT VALID,
  ADD CONSTRAINT measure_jobs_environment_ck CHECK (
    environment IS NULL OR environment IN ('production','staging','development','test')
  ) NOT VALID,
  ADD CONSTRAINT measure_jobs_contact_preference_ck CHECK (
    contact_preference IS NULL OR contact_preference IN ('email','phone','either')
  ) NOT VALID,
  ADD CONSTRAINT measure_jobs_measurement_method_ck CHECK (
    measurement_method IS NULL OR measurement_method IN ('manual','calculator','camera','installer')
  ) NOT VALID,
  ADD CONSTRAINT measure_jobs_lead_state_ck CHECK (
    lead_state IS NULL OR lead_state IN ('captured','no_installer','routed','viewed','accepted','declined','expired')
  ) NOT VALID,
  ADD CONSTRAINT measure_jobs_tier_snapshot_ck CHECK (
    tier_snapshot IS NULL OR tier_snapshot IN ('standard','spec_ready','ineligible')
  ) NOT VALID,
  ADD CONSTRAINT measure_jobs_retention_class_ck CHECK (
    retention_class IS NULL OR retention_class IN ('test','unconverted','converted','legal_hold')
  ) NOT VALID,
  ADD CONSTRAINT measure_jobs_room_captures_array_ck CHECK (
    room_captures IS NULL OR jsonb_typeof(room_captures) = 'array'
  ) NOT VALID;

CREATE TABLE measure_job_routing_attempts (
  id BIGSERIAL PRIMARY KEY,
  measure_job_id BIGINT NOT NULL REFERENCES measure_jobs(id) ON DELETE RESTRICT,
  attempt_no INTEGER NOT NULL CHECK (attempt_no > 0),
  installer_id BIGINT NOT NULL REFERENCES installers(id) ON DELETE RESTRICT,
  state TEXT NOT NULL CHECK (state IN ('routed','viewed','accepted','declined','expired','superseded')),
  routed_at TIMESTAMPTZ NOT NULL,
  viewed_at TIMESTAMPTZ,
  responded_at TIMESTAMPTZ,
  accepted_at TIMESTAMPTZ,
  declined_at TIMESTAMPTZ,
  expired_at TIMESTAMPTZ,
  superseded_at TIMESTAMPTZ,
  supersedes_job_id BIGINT,
  supersedes_attempt_id BIGINT,
  correlation_id UUID NOT NULL UNIQUE,
  created_by TEXT NOT NULL,
  UNIQUE (measure_job_id, attempt_no),
  UNIQUE (id, measure_job_id, installer_id),
  UNIQUE (measure_job_id, id),
  CHECK (
    (attempt_no = 1 AND supersedes_attempt_id IS NULL AND supersedes_job_id IS NULL)
    OR (attempt_no > 1 AND supersedes_attempt_id IS NOT NULL
      AND supersedes_job_id IS NOT NULL AND supersedes_job_id = measure_job_id
      AND supersedes_attempt_id <> id)
  )
);

ALTER TABLE measure_job_routing_attempts
  ADD CONSTRAINT routing_attempt_supersedes_same_job_fk
  FOREIGN KEY (supersedes_job_id, supersedes_attempt_id)
  REFERENCES measure_job_routing_attempts (measure_job_id, id)
  MATCH FULL ON DELETE RESTRICT;

CREATE UNIQUE INDEX routing_attempt_one_successor_idx
  ON measure_job_routing_attempts (supersedes_attempt_id)
  WHERE supersedes_attempt_id IS NOT NULL;
CREATE UNIQUE INDEX routing_attempt_one_root_per_job_idx
  ON measure_job_routing_attempts (measure_job_id)
  WHERE supersedes_attempt_id IS NULL;
CREATE UNIQUE INDEX routing_attempt_one_active_per_job_idx
  ON measure_job_routing_attempts (measure_job_id)
  WHERE state IN ('routed','viewed','accepted');

ALTER TABLE measure_jobs
  ADD CONSTRAINT measure_jobs_current_route_fk
    FOREIGN KEY (current_routing_attempt_id, id, routed_to)
    REFERENCES measure_job_routing_attempts (id, measure_job_id, installer_id)
    ON DELETE RESTRICT,
  ADD CONSTRAINT measure_jobs_current_route_pair_ck CHECK (
    current_routing_attempt_id IS NULL OR routed_to IS NOT NULL
  ) NOT VALID;

ALTER TABLE bookings
  ADD CONSTRAINT bookings_id_installer_uq UNIQUE (id, installer_id);

CREATE TABLE measure_booking_attribution_candidates (
  id BIGSERIAL PRIMARY KEY,
  booking_id INTEGER NOT NULL,
  measure_job_id BIGINT NOT NULL REFERENCES measure_jobs(id) ON DELETE RESTRICT,
  routing_attempt_id BIGINT NOT NULL,
  installer_id BIGINT NOT NULL,
  attribution_method TEXT NOT NULL CHECK (attribution_method IN ('signed_handoff','admin_verified')),
  policy_version TEXT NOT NULL,
  qualified_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  correlation_id UUID NOT NULL UNIQUE,
  created_by TEXT NOT NULL,
  UNIQUE (booking_id, id),
  UNIQUE (booking_id, routing_attempt_id, attribution_method, policy_version),
  FOREIGN KEY (booking_id, installer_id)
    REFERENCES bookings (id, installer_id) ON DELETE RESTRICT,
  FOREIGN KEY (routing_attempt_id, measure_job_id, installer_id)
    REFERENCES measure_job_routing_attempts (id, measure_job_id, installer_id)
    ON DELETE RESTRICT
);

CREATE TABLE measure_booking_primary_decisions (
  id BIGSERIAL PRIMARY KEY,
  booking_id INTEGER NOT NULL REFERENCES bookings(id) ON DELETE RESTRICT,
  decision_kind TEXT NOT NULL CHECK (decision_kind IN ('select','clear')),
  selected_booking_id INTEGER,
  selected_candidate_id BIGINT,
  supersedes_booking_id INTEGER,
  supersedes_decision_id BIGINT,
  decided_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  correlation_id UUID NOT NULL UNIQUE,
  policy_version TEXT NOT NULL,
  reason_code TEXT NOT NULL,
  decided_by TEXT NOT NULL,
  UNIQUE (booking_id, id),
  CHECK (
    (decision_kind = 'select' AND selected_booking_id IS NOT NULL
      AND selected_booking_id = booking_id AND selected_candidate_id IS NOT NULL)
    OR (decision_kind = 'clear' AND selected_booking_id IS NULL AND selected_candidate_id IS NULL)
  ),
  CHECK (
    (supersedes_decision_id IS NULL AND supersedes_booking_id IS NULL
      AND decision_kind = 'select')
    OR (supersedes_decision_id IS NOT NULL AND supersedes_booking_id IS NOT NULL
      AND supersedes_booking_id = booking_id AND supersedes_decision_id <> id)
  ),
  CHECK (decision_kind <> 'clear' OR supersedes_decision_id IS NOT NULL),
  FOREIGN KEY (selected_booking_id, selected_candidate_id)
    REFERENCES measure_booking_attribution_candidates (booking_id, id)
    MATCH FULL ON DELETE RESTRICT,
  FOREIGN KEY (supersedes_booking_id, supersedes_decision_id)
    REFERENCES measure_booking_primary_decisions (booking_id, id)
    MATCH FULL ON DELETE RESTRICT
);

CREATE UNIQUE INDEX primary_decision_one_root_per_booking_idx
  ON measure_booking_primary_decisions (booking_id)
  WHERE supersedes_decision_id IS NULL;
CREATE UNIQUE INDEX primary_decision_one_successor_idx
  ON measure_booking_primary_decisions (supersedes_decision_id)
  WHERE supersedes_decision_id IS NOT NULL;

CREATE TABLE measure_booking_primary_current (
  booking_id INTEGER PRIMARY KEY REFERENCES bookings(id) ON DELETE RESTRICT,
  decision_id BIGINT NOT NULL,
  version BIGINT NOT NULL CHECK (version > 0),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  FOREIGN KEY (booking_id, decision_id)
    REFERENCES measure_booking_primary_decisions (booking_id, id) ON DELETE RESTRICT
);

CREATE TABLE measure_job_events (
  id BIGSERIAL PRIMARY KEY,
  measure_job_id BIGINT NOT NULL REFERENCES measure_jobs(id) ON DELETE RESTRICT,
  event_type TEXT NOT NULL,
  occurred_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  actor_type TEXT NOT NULL,
  installer_id BIGINT REFERENCES installers(id) ON DELETE RESTRICT,
  booking_id INTEGER REFERENCES bookings(id) ON DELETE RESTRICT,
  correlation_id UUID NOT NULL,
  source_system TEXT,
  source_event_id TEXT,
  schema_version TEXT NOT NULL,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  CHECK (jsonb_typeof(metadata) = 'object'),
  CHECK (
    (source_event_id IS NULL AND source_system IS NULL)
    OR (source_event_id IS NOT NULL AND source_system IS NOT NULL)
  )
);

CREATE UNIQUE INDEX measure_job_events_source_idempotency_idx
  ON measure_job_events (source_system, source_event_id)
  WHERE source_event_id IS NOT NULL;
CREATE UNIQUE INDEX measure_job_events_internal_idempotency_idx
  ON measure_job_events (measure_job_id, event_type, correlation_id)
  WHERE source_event_id IS NULL;

COMMIT;