`db/migrations/011_pitch_daily_snapshot.sql`

39 lines
-- Migration 011 — daily activity snapshot
-- Captures (snap_date, dimension_key, dimension_value, count) once a day so
-- /today.html can plot a 30-day trend chart of corridor velocity.
--
-- A single table holds three "dimensions" — status, outreach_channel, dw_proximity —
-- so we can render multiple sparklines from one source.

CREATE TABLE IF NOT EXISTS pitch_daily_snapshot (
  snap_date     DATE        NOT NULL,
  dim           TEXT        NOT NULL,    -- 'status' | 'outreach_channel' | 'dw_proximity' | 'totals'
  bucket        TEXT        NOT NULL,    -- e.g. 'draft', 'walk-in', 'same_block', 'all'
  count         INTEGER     NOT NULL DEFAULT 0,
  captured_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (snap_date, dim, bucket)
);

CREATE INDEX IF NOT EXISTS idx_snapshot_dim ON pitch_daily_snapshot (dim, snap_date);

-- Snapshot helper view so the snapshot script is a one-liner UPSERT
CREATE OR REPLACE VIEW v_pitch_today_status AS
SELECT 'status'::text AS dim, status AS bucket, count(*)::int AS count
FROM pitches GROUP BY status
UNION ALL
SELECT 'outreach_channel'::text, COALESCE(outreach_channel, 'unspecified'), count(*)::int
FROM pitches GROUP BY outreach_channel
UNION ALL
SELECT 'dw_proximity'::text, COALESCE(dw_proximity, 'unknown'), count(*)::int
FROM pitches GROUP BY dw_proximity
UNION ALL
SELECT 'totals'::text, 'all', count(*)::int FROM pitches
UNION ALL
SELECT 'totals'::text, 'sent', count(*)::int FROM pitches WHERE sent_at IS NOT NULL
UNION ALL
SELECT 'totals'::text, 'replied', count(*)::int FROM pitches WHERE replied_at IS NOT NULL
UNION ALL
SELECT 'totals'::text, 'won', count(*)::int FROM pitches WHERE status = 'won'
UNION ALL
SELECT 'totals'::text, 'walked', count(*)::int FROM pitches WHERE outreach_channel = 'walk-in' AND sent_at IS NOT NULL;