fleettickets/migrations/10_inc_history_capture.sql

-- 10_inc_history_capture.sql — fleettickets · history capture for time-series
-- ─────────────────────────────────────────────────────────────────────────────
-- tickets.inc is current-state (upsert), so it can't answer "how many were OPEN on
-- day X". This adds two durable history artifacts, populated by the ingest via
-- tickets.capture_history():
--
--   tickets.closure_events    — append-only log of observed ticket closures
--                               (keyed (ticket_id, closed_at); survives row churn,
--                               records observed_at = when WE first saw the closure).
--   tickets.inc_daily_snapshot— one row per EAT day: open backlog (+ SLA split,
--                               by cluster/status) and the day's created/closed flow.
--                               Upserted each run → backlog-over-time accrues going
--                               forward (past backlog can't be reconstructed).
--
-- Idempotent: safe on a fresh DB and re-appliable. The first capture backfills
-- closure_events from existing closed rows (observed_at = first-run time).
-- ─────────────────────────────────────────────────────────────────────────────

SET search_path = tickets, public;

CREATE TABLE IF NOT EXISTS tickets.closure_events (
  ticket_id     text        NOT NULL,
  closed_at     timestamptz NOT NULL,
  cluster       text,
  region        text,
  assigned_team text,
  owner         text,
  mttr          numeric,                       -- minutes
  sla_status    text,
  observed_at   timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (ticket_id, closed_at)
);
CREATE INDEX IF NOT EXISTS ix_closure_events_closed_at ON tickets.closure_events (closed_at);
CREATE INDEX IF NOT EXISTS ix_closure_events_team      ON tickets.closure_events (assigned_team);
CREATE INDEX IF NOT EXISTS ix_closure_events_cluster   ON tickets.closure_events (cluster);

CREATE TABLE IF NOT EXISTS tickets.inc_daily_snapshot (
  snapshot_date   date PRIMARY KEY,            -- EAT calendar day
  open_total      integer,
  open_breached   integer,
  open_at_risk    integer,
  open_ok         integer,
  open_unknown    integer,
  created_today   integer,                     -- created_at_service on this EAT day
  closed_today    integer,                     -- closed_at on this EAT day
  by_cluster_open jsonb,
  by_status_open  jsonb,
  captured_at     timestamptz NOT NULL DEFAULT now()
);

-- capture: append new closures + upsert today's backlog snapshot. Called by the
-- ingest after each --apply run (and available standalone).
CREATE OR REPLACE FUNCTION tickets.capture_history()
 RETURNS jsonb LANGUAGE plpgsql AS $fn$
DECLARE
  v_today  date := (now() AT TIME ZONE 'Africa/Nairobi')::date;
  v_events integer;
  v_open   integer;
BEGIN
  -- 1) append newly-observed closures (closed_at is authoritative; dedup by id+closed_at)
  INSERT INTO tickets.closure_events
        (ticket_id, closed_at, cluster, region, assigned_team, owner, mttr, sla_status)
  SELECT ticket_id, closed_at, cluster, region, assigned_team, owner, mttr, sla_status
  FROM tickets.inc
  WHERE closed_at IS NOT NULL
  ON CONFLICT (ticket_id, closed_at) DO NOTHING;
  GET DIAGNOSTICS v_events = ROW_COUNT;

  -- 2) upsert today's backlog snapshot (open metrics from inc_open_sla)
  INSERT INTO tickets.inc_daily_snapshot
        (snapshot_date, open_total, open_breached, open_at_risk, open_ok, open_unknown,
         created_today, closed_today, by_cluster_open, by_status_open, captured_at)
  SELECT
    v_today,
    (SELECT count(*) FROM tickets.inc_open_sla),
    (SELECT count(*) FROM tickets.inc_open_sla WHERE sla_state = 'breached'),
    (SELECT count(*) FROM tickets.inc_open_sla WHERE sla_state = 'at_risk'),
    (SELECT count(*) FROM tickets.inc_open_sla WHERE sla_state = 'ok'),
    (SELECT count(*) FROM tickets.inc_open_sla WHERE sla_state = 'unknown'),
    (SELECT count(*) FROM tickets.inc
       WHERE (created_at_service AT TIME ZONE 'Africa/Nairobi')::date = v_today),
    (SELECT count(*) FROM tickets.inc
       WHERE (closed_at AT TIME ZONE 'Africa/Nairobi')::date = v_today),
    (SELECT COALESCE(jsonb_object_agg(cl, c), '{}'::jsonb)
       FROM (SELECT COALESCE(cluster, '(none)') cl, count(*) c
               FROM tickets.inc_open_sla GROUP BY 1) z),
    (SELECT COALESCE(jsonb_object_agg(st, c), '{}'::jsonb)
       FROM (SELECT COALESCE(normalized_status, '(none)') st, count(*) c
               FROM tickets.inc_open_sla GROUP BY 1) z),
    now()
  ON CONFLICT (snapshot_date) DO UPDATE SET
    open_total = EXCLUDED.open_total, open_breached = EXCLUDED.open_breached,
    open_at_risk = EXCLUDED.open_at_risk, open_ok = EXCLUDED.open_ok,
    open_unknown = EXCLUDED.open_unknown, created_today = EXCLUDED.created_today,
    closed_today = EXCLUDED.closed_today, by_cluster_open = EXCLUDED.by_cluster_open,
    by_status_open = EXCLUDED.by_status_open, captured_at = now();

  SELECT open_total INTO v_open FROM tickets.inc_daily_snapshot WHERE snapshot_date = v_today;
  RETURN jsonb_build_object('closure_events_added', v_events,
                            'snapshot_date', v_today, 'open_total', v_open);
END $fn$;

COMMENT ON FUNCTION tickets.capture_history() IS
  'Append new closures to tickets.closure_events + upsert today''s tickets.inc_daily_snapshot. fleettickets 10.';

-- grants (guarded: roles may not exist on a fresh DB)
DO $grants$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'tracksolid_owner') THEN
    GRANT SELECT, INSERT, UPDATE, DELETE ON tickets.closure_events, tickets.inc_daily_snapshot TO tracksolid_owner;
    GRANT EXECUTE ON FUNCTION tickets.capture_history() TO tracksolid_owner;
  END IF;
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'dashboard_ro') THEN
    GRANT SELECT ON tickets.closure_events, tickets.inc_daily_snapshot TO dashboard_ro;
  END IF;
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'grafana_ro') THEN
    GRANT SELECT ON tickets.closure_events, tickets.inc_daily_snapshot TO grafana_ro;
  END IF;
END $grants$;