Add measure_jobs schema (migration 023) — Council Idea #5 lead-handoff capture table

5ea9d112bd1ad1da6f81a5d4247d5433bc51f448 · 2026-06-18 18:45:07 -0700 · SteveStudio2

Files touched

A db/migrations/023_measure_jobs.sql

Diff

commit 5ea9d112bd1ad1da6f81a5d4247d5433bc51f448
Author: SteveStudio2 <steve@designerwallcoverings.com>
Date:   Thu Jun 18 18:45:07 2026 -0700

    Add measure_jobs schema (migration 023) — Council Idea #5 lead-handoff capture table
---
 db/migrations/023_measure_jobs.sql | 52 ++++++++++++++++++++++++++++++++++++++
 1 file changed, 52 insertions(+)

diff --git a/db/migrations/023_measure_jobs.sql b/db/migrations/023_measure_jobs.sql
new file mode 100644
index 0000000..7c8fa7d
--- /dev/null
+++ b/db/migrations/023_measure_jobs.sql
@@ -0,0 +1,52 @@
+-- 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);

← 8c10bac Add P3 order-handoff to /measure: PDP deep-link prefill + ho · back to NationalPaperHangers · chore: macstudio3 migration — reconcile from mac2 + repoint 365532c →