`db/migrations/010_response_tracking.sql`

61 lines
-- Migration 010 — pitch response & outcome tracking
-- Once Steve walks the corridor with /walk-route.html or DMs via /linkedin.html,
-- replies/wins/losses need a structured DB home. Free-form `notes` already exists,
-- but the pipeline needs queryable response text + reply channel + win amount
-- + structured loss reasons + scheduled follow-up.

ALTER TABLE pitches
  ADD COLUMN IF NOT EXISTS outreach_channel TEXT,
  ADD COLUMN IF NOT EXISTS dw_proximity     TEXT,
  ADD COLUMN IF NOT EXISTS contact_name     TEXT,
  ADD COLUMN IF NOT EXISTS email            TEXT,
  ADD COLUMN IF NOT EXISTS phone            TEXT,
  ADD COLUMN IF NOT EXISTS linkedin         TEXT,
  ADD COLUMN IF NOT EXISTS reply_text       TEXT,
  ADD COLUMN IF NOT EXISTS reply_channel    TEXT,
  ADD COLUMN IF NOT EXISTS won_value_usd    NUMERIC(10,2),
  ADD COLUMN IF NOT EXISTS lost_reason      TEXT,
  ADD COLUMN IF NOT EXISTS next_followup_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS followup_notes   TEXT;

CREATE INDEX IF NOT EXISTS idx_pitches_replied_at  ON pitches (replied_at) WHERE replied_at IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_pitches_followup_at ON pitches (next_followup_at) WHERE next_followup_at IS NOT NULL AND closed_at IS NULL;

-- View: response funnel by channel
CREATE OR REPLACE VIEW v_response_funnel AS
SELECT
  COALESCE(outreach_channel, 'unspecified') AS channel,
  count(*) FILTER (WHERE sent_at IS NOT NULL)                    AS sent,
  count(*) FILTER (WHERE replied_at IS NOT NULL)                 AS replied,
  count(*) FILTER (WHERE status = 'won')                         AS won,
  count(*) FILTER (WHERE status = 'lost')                        AS lost,
  COALESCE(SUM(won_value_usd) FILTER (WHERE status = 'won'), 0)  AS won_value_usd,
  ROUND(
    100.0 * count(*) FILTER (WHERE replied_at IS NOT NULL)
    / NULLIF(count(*) FILTER (WHERE sent_at IS NOT NULL), 0),
    1
  ) AS reply_rate_pct
FROM pitches
WHERE sent_at IS NOT NULL
GROUP BY outreach_channel
ORDER BY sent DESC;

-- View: today's follow-ups (and any overdue)
CREATE OR REPLACE VIEW v_followups_due AS
SELECT
  p.id, p.business_id, b.name, b.address, b.city, p.pitch_type, p.status,
  p.outreach_channel, p.next_followup_at, p.followup_notes, p.replied_at,
  p.contact_name, p.email, p.phone, p.linkedin,
  CASE
    WHEN p.next_followup_at::date < CURRENT_DATE THEN 'overdue'
    WHEN p.next_followup_at::date = CURRENT_DATE THEN 'today'
    ELSE 'upcoming'
  END AS due_bucket,
  (CURRENT_DATE - p.next_followup_at::date) AS days_overdue
FROM pitches p
JOIN businesses b ON b.id = p.business_id
WHERE p.next_followup_at IS NOT NULL
  AND p.closed_at IS NULL
  AND p.status NOT IN ('won','lost','skip')
ORDER BY p.next_followup_at ASC NULLS LAST;