`db/migrations/023_measure_jobs.sql`

53 lines
-- Council Idea #5 — Measure→Installer Lead Handoff (SAFE subset, BUNDLE-A).
-- Captures a structured installer-lead from the /measure result page when a
-- homeowner explicitly opts in ("Get this quoted by a local installer"), and
-- routes it to the single NEAREST CLAIMED installer who services that ZIP.
--
-- Why a DEDICATED table (not a reuse of installer_interest):
--   installer_interest has UNIQUE(installer_id, email) — an email-signup row
--   semantically — which structurally breaks one-email-many-jobs (a homeowner
--   can measure several projects). A job-spec is a different record than a
--   "notify me when live" email capture, and we want sqft/rolls queryable.
--
-- Channel (a) in-app ONLY at this stage: a routed row IS the installer's inbox.
-- The installer dashboard reads measure_jobs WHERE routed_to = <me>. NOTHING is
-- emailed/sent from here — outbound email (channel b) + the prod deploy are
-- separate Steve-gated steps (drafted to pending-approval).
--
-- Routing is claimed-only by design: pushing a lead to an UNCLAIMED (scraped)
-- installer would be unsolicited B2B contact = CAN-SPAM exposure. route_status
-- 'no_installer' is the graceful path when no claimed installer is in range —
-- the homeowner is shown the standard /find browse list instead of a dead end.

CREATE TABLE IF NOT EXISTS measure_jobs (
  id             BIGSERIAL PRIMARY KEY,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  zip            TEXT,
  sqft           NUMERIC,
  rolls          INTEGER,
  wall_count     INTEGER,
  material       TEXT,              -- product/material context from the form, if any
  product_sku    TEXT,             -- when launched from a DW/wallco PDP deep-link
  product_name   TEXT,
  customer_name  TEXT,
  customer_email TEXT,
  customer_phone TEXT,
  notes          TEXT,
  ip_hash        TEXT,              -- sha256 prefix, same pattern as installer_interest (never raw IP)
  routed_to      BIGINT REFERENCES installers(id) ON DELETE SET NULL,  -- NULL until routed
  routed_at      TIMESTAMPTZ,
  route_status   TEXT NOT NULL DEFAULT 'captured'
                 CHECK (route_status IN ('captured','routed','delivered','declined','no_installer')),
  -- in-app read receipt: set when the routed installer first views the lead in
  -- their dashboard (channel a "delivery"). Distinct from any future email send.
  viewed_at      TIMESTAMPTZ
);

-- The installer's "inbox" query: their routed, not-yet-actioned leads, newest first.
CREATE INDEX IF NOT EXISTS measure_jobs_routed_to_idx
  ON measure_jobs (routed_to, created_at DESC) WHERE routed_to IS NOT NULL;

-- Ops/funnel slicing by status + recency.
CREATE INDEX IF NOT EXISTS measure_jobs_status_idx
  ON measure_jobs (route_status, created_at DESC);