`db/migrations/012_pitch_event_log.sql`

86 lines
-- Migration 012 — pitch_event_log audit trail
-- Every change to a pitch's lifecycle (status, outreach_channel, sent/replied/closed/won)
-- writes a row to pitch_event_log via trigger. This unblocks future analytics
-- (cycle time, time-to-first-reply, time-to-close), legal-compliance recall
-- ("when did Steve first contact this firm?"), and lifecycle visualization.

CREATE TABLE IF NOT EXISTS pitch_event_log (
  id           BIGSERIAL PRIMARY KEY,
  pitch_id     BIGINT       NOT NULL REFERENCES pitches(id) ON DELETE CASCADE,
  event_type   TEXT         NOT NULL,  -- 'status_change' | 'channel_change' | 'sent' | 'replied' | 'closed' | 'won' | 'lost' | 'reply_text' | 'followup_scheduled' | 'created'
  old_value    TEXT,
  new_value    TEXT,
  source       TEXT,                   -- typically NULL (we don't track operator yet); reserved
  occurred_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_pel_pitch        ON pitch_event_log (pitch_id, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_pel_event_type   ON pitch_event_log (event_type, occurred_at DESC);
CREATE INDEX IF NOT EXISTS idx_pel_occurred_at  ON pitch_event_log (occurred_at DESC);

-- Trigger: fire on UPDATE of any tracked column
CREATE OR REPLACE FUNCTION trg_pitch_event_log_fn() RETURNS trigger AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO pitch_event_log (pitch_id, event_type, new_value)
      VALUES (NEW.id, 'created', NEW.status);
    RETURN NEW;
  END IF;

  IF NEW.status IS DISTINCT FROM OLD.status THEN
    INSERT INTO pitch_event_log (pitch_id, event_type, old_value, new_value)
      VALUES (NEW.id, 'status_change', OLD.status, NEW.status);
  END IF;

  IF NEW.outreach_channel IS DISTINCT FROM OLD.outreach_channel THEN
    INSERT INTO pitch_event_log (pitch_id, event_type, old_value, new_value)
      VALUES (NEW.id, 'channel_change', OLD.outreach_channel, NEW.outreach_channel);
  END IF;

  IF NEW.sent_at IS DISTINCT FROM OLD.sent_at AND NEW.sent_at IS NOT NULL THEN
    INSERT INTO pitch_event_log (pitch_id, event_type, new_value)
      VALUES (NEW.id, 'sent', COALESCE(NEW.outreach_channel, 'unspecified'));
  END IF;

  IF NEW.replied_at IS DISTINCT FROM OLD.replied_at AND NEW.replied_at IS NOT NULL THEN
    INSERT INTO pitch_event_log (pitch_id, event_type, new_value)
      VALUES (NEW.id, 'replied', COALESCE(NEW.reply_channel, NEW.outreach_channel, 'unspecified'));
  END IF;

  IF NEW.closed_at IS DISTINCT FROM OLD.closed_at AND NEW.closed_at IS NOT NULL THEN
    INSERT INTO pitch_event_log (pitch_id, event_type, new_value)
      VALUES (NEW.id, 'closed', NEW.status);
  END IF;

  IF NEW.reply_text IS DISTINCT FROM OLD.reply_text AND NEW.reply_text IS NOT NULL THEN
    INSERT INTO pitch_event_log (pitch_id, event_type, new_value)
      VALUES (NEW.id, 'reply_text', LEFT(NEW.reply_text, 200));
  END IF;

  IF NEW.next_followup_at IS DISTINCT FROM OLD.next_followup_at AND NEW.next_followup_at IS NOT NULL THEN
    INSERT INTO pitch_event_log (pitch_id, event_type, new_value)
      VALUES (NEW.id, 'followup_scheduled', NEW.next_followup_at::text);
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_pitch_event_log ON pitches;
CREATE TRIGGER trg_pitch_event_log
  AFTER INSERT OR UPDATE ON pitches
  FOR EACH ROW EXECUTE FUNCTION trg_pitch_event_log_fn();

-- View: cycle-time analytics (time from sent to first reply / close)
CREATE OR REPLACE VIEW v_pitch_cycle_time AS
SELECT
  p.id, p.business_id, b.name, p.outreach_channel, p.status,
  p.sent_at, p.replied_at, p.closed_at,
  EXTRACT(EPOCH FROM (p.replied_at - p.sent_at))/3600  AS hours_to_reply,
  EXTRACT(EPOCH FROM (p.closed_at  - p.sent_at))/86400 AS days_to_close,
  EXTRACT(EPOCH FROM (p.closed_at  - p.replied_at))/86400 AS days_reply_to_close
FROM pitches p
JOIN businesses b ON b.id = p.business_id
WHERE p.sent_at IS NOT NULL
ORDER BY p.sent_at DESC;