`db/migrations/017_coi_requests.sql`

83 lines
-- 017 · Live COI request flow (UX idea #5)
--
-- Today: designers calling installers, calling brokers, 3-day phone-tag
-- before a $4k/roll de Gournay install. Generic directories treat insurance
-- as a yes/no signal; luxury commercial + hospitality need an actual
-- designer-named-additional-insured cert PER JOB.
--
-- v0: capture structured insurance metadata on the installer side, give
-- designers a one-form COI request that emails the installer + their
-- broker with the designer-named additional-insured details. PDF
-- generation is intentionally NOT in v0 — the broker does that.
--
-- Reversible:
--   ALTER TABLE installers DROP COLUMN insurance;
--   DROP TABLE coi_requests;

BEGIN;

-- Structured insurance metadata. Existing boolean `insurance_on_file` +
-- `insurance_expires DATE` stay as the lightweight summary. JSONB carries
-- the rich data so we don't reshape the table later.
ALTER TABLE installers
  ADD COLUMN IF NOT EXISTS insurance JSONB;
-- Expected shape:
-- {
--   "carrier": "Hartford",
--   "policy_number": "GL-2024-12345",
--   "limits": { "general_aggregate_usd": 2000000, "per_occurrence_usd": 1000000 },
--   "expiry": "2026-12-31",
--   "broker_name": "Jane Broker",
--   "broker_email": "jane@broker.com",
--   "broker_phone": "+1-555-0100",
--   "auto_attach_to_email": true
-- }

CREATE TABLE IF NOT EXISTS coi_requests (
  id                          SERIAL PRIMARY KEY,
  installer_id                INTEGER NOT NULL REFERENCES installers(id) ON DELETE CASCADE,

  -- Designer / requester
  designer_name               TEXT NOT NULL,
  designer_company            TEXT,
  designer_email              TEXT NOT NULL,
  designer_phone              TEXT,

  -- Project / job
  project_name                TEXT,
  project_address             TEXT,
  project_start_date          DATE,
  project_value_usd           NUMERIC(12,2),

  -- Additional-insured party (often the design firm itself, sometimes the
  -- end client). The broker NEEDS this exact phrasing on the cert.
  additional_insured_name     TEXT NOT NULL,
  additional_insured_address  TEXT,

  notes                       TEXT,

  -- Workflow
  status                      TEXT NOT NULL DEFAULT 'pending',
                              -- pending | acknowledged | fulfilled | declined | expired
  installer_notified_at       TIMESTAMPTZ,
  broker_notified_at          TIMESTAMPTZ,
  fulfilled_at                TIMESTAMPTZ,
  fulfilled_pdf_url           TEXT,
  decline_reason              TEXT,

  source_ip                   TEXT,
  source_user_agent           TEXT,
  created_at                  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at                  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_coi_requests_installer ON coi_requests(installer_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_coi_requests_status ON coi_requests(status, created_at DESC);

ALTER TABLE coi_requests
  DROP CONSTRAINT IF EXISTS coi_requests_status_check,
  ADD CONSTRAINT coi_requests_status_check
    CHECK (status IN ('pending','acknowledged','fulfilled','declined','expired'));

COMMIT;