fleettickets/docs/phase-2-dashboard.md

# PRD (Phase 2) — INC operations dashboard: read-API layer

> Phase 1 (hourly INC CSV ingestion → `tickets.inc`, geocoding, typed generated
> columns, `inc_open_sla` view) is **complete and deployed** (migrations 01–08,
> Coolify hourly `15 7-19 * * *` EAT). See `docs/phase-1-ingestion.md` /
> `docs/implementation.md`. This document is Phase 2.

## Context

FleetOps needs a **live INC operations map** (modelled on FleetNow):

- A map showing **all currently-open INC tickets** alongside **live vehicle
  positions from FleetNow**.
- A **bottom timeline bar** that overlays **closed tickets** (alongside FleetNow
  vehicle routes) for a selected period.
- **Bottom filters**: `cluster`, ticket `status`, and **time** = today / this week /
  this month / custom date.
- **Top metric cards** that react to the selected filters — **ticket** metrics
  (not vehicle metrics).

**Scope of THIS repo (confirmed): the data / read-API layer only.** `fleettickets`
exposes parameterized SQL in `tracksolid_db` that `dashboard_api` serves to the
**FleetOps SPA**. The map UI, timeline bar, filter controls, metric cards, and the
**FleetNow vehicle positions/routes** are **other repos/systems**. There is no
vehicle id in the INC feed, so we serve **tickets only**; the SPA overlays FleetNow
vehicles/routes.

## Confirmed behaviour

- **Open layer (live):** all `is_actionable = true` INC tickets matching the
  cluster/status filter — **not** time-filtered (open = needs action now).
- **Closed overlay (windowed):** closed tickets whose `closed_at` falls in the
  selected window, matching cluster/status.
- **Metric cards (windowed):** computed for the current selection.
- **Filters combine with AND**, each optional. **Windows are calendar EAT**
  (today / ISO-week / month) or an explicit custom `[from, to)`.
- **Delivery:** one parameterized function returning a single JSON payload
  `{ open: GeoJSON, closed: GeoJSON, metrics: {…}, window, freshness }`, mirroring
  the existing `reporting.fn_tickets_for_map` style.

## Deliverable — `migrations/09_inc_dashboard_fn.sql`

A new read function (and supporting index if needed); additive, idempotent
(`CREATE OR REPLACE`), no change to existing objects.

### `reporting.fn_inc_dashboard(...)`

```
reporting.fn_inc_dashboard(
  p_cluster text        DEFAULT NULL,     -- exact cluster (matches tickets.inc.cluster)
  p_status  text        DEFAULT NULL,     -- normalized_status
  p_window  text        DEFAULT 'today',  -- 'today' | 'week' | 'month' | 'custom'
  p_from    timestamptz DEFAULT NULL,     -- custom window start (inclusive)
  p_to      timestamptz DEFAULT NULL      -- custom window end (exclusive)
) RETURNS jsonb
```

- **Window resolution:** if `p_from`/`p_to` given → use them (custom). Else compute
  **EAT calendar bounds** from `p_window`: `today` = `[date_trunc('day', now_eat),
  +1 day)`, `week` = `date_trunc('week', …)`, `month` = `date_trunc('month', …)` —
  converted back to `timestamptz` via `… AT TIME ZONE 'Africa/Nairobi'`.
- **Returned JSON:**
  ```jsonc
  {
    "window": { "from": "...", "to": "...", "preset": "today" },
    "open":   { "type":"FeatureCollection", "features":[ … ] },   // all open, filtered by cluster/status
    "closed": { "type":"FeatureCollection", "features":[ … ] },   // closed_at in window, filtered
    "metrics": {
      "open_now": int,
      "closed_in_window": int,
      "sla": {
        "open":   { "breached": int, "at_risk": int, "ok": int, "unknown": int },
        "closed": { "compliant": int, "breached": int }
      },
      "by_status":  { "<status>": int, … },
      "by_cluster": { "<cluster>": int, … },
      "closure_rate": { "per_day_avg": num, "series": [ { "day":"YYYY-MM-DD", "count":int }, … ] },
      "avg_mttr_min": num
    },
    "freshness": { … }                           // from tickets.import_meta
  }
  ```
- **Feature properties** (both layers): `ticket_id, normalized_status, cluster,
  region, location_name, assigned_team, owner, geo_source`. Open adds `sla_state,
  hours_open`; closed adds `closed_at, mttr, sla_status`. Geometry from `geom`
  (`ST_AsGeoJSON`). Only `geom IS NOT NULL` rows become features; `metrics` count the
  full filtered set (note the small geocoding gap).

### Reuse (don't reinvent)

- **`tickets.inc_open_sla`** (migration 08) — `sla_state` / `hours_open` for the open
  layer + open-SLA metrics.
- **Typed generated columns** (migrations 03–07): `cluster`, `normalized_status`,
  `closed_at`, `mttr` (minutes), `assigned_team`, `geom`, `geo_source`.
- **`reporting.fn_tickets_for_map`** (migrations 01–02) — GeoJSON
  `jsonb_build_object`/`ST_AsGeoJSON` + `summary.freshness` patterns.
- **Derived SLA logic** — `now() − COALESCE(created_at_service, first_seen_at)` vs
  48h/36h.

### Indexes

In place: `ix_inc_closed_at`, `ix_inc_cluster_col`, `ix_inc_norm_status_col`,
`ix_inc_actionable_col`, `ix_inc_geom`, `ix_inc_geog`. Add composite
`(closed_at, cluster)` only if EXPLAIN shows it's needed.

### Grants

`GRANT EXECUTE ON FUNCTION reporting.fn_inc_dashboard(...) TO dashboard_ro` (guarded).

## Dependencies (other repos)

- **`dashboard_api`** — endpoint e.g. `GET /webhook/inc-dashboard?cluster=&status=&window=&from=&to=`
  calling `fn_inc_dashboard`. *(Contract here; impl there.)*
- **FleetOps SPA** (`fleetops`) — map, timeline bar, filter UI, metric cards;
  overlays FleetNow vehicles/routes.
- **FleetNow** — live vehicle positions + historical routes.

## Data-quality caveats (affect metrics, not delivery)

- Source `sla_status` only meaningful for **closed**; open SLA is derived.
- `created_at_service` null on ~30% → some open are SLA `unknown` (fallback flagged).
- `mttr` is **minutes**, null until closed; closure/MTTR metrics filter accordingly.
- Content lag ~2 days → recent days under-count.
- A few tickets lack `geom` → counted in metrics, absent from map features.

## Verification

1. `SELECT reporting.fn_inc_dashboard();` → valid JSON (open/closed FCs, metrics,
   window=today, freshness).
2. Filters: `p_cluster`, `p_status`, `p_window := 'month'`, and a custom `p_from/p_to`
   — counts match ad-hoc `SELECT`s on `tickets.inc` / `tickets.inc_open_sla`.
3. Window math: today/week/month are correct **EAT** calendar ranges.
4. SLA metrics match the `inc_open_sla` distribution / source `sla_status` in window.
5. `EXPLAIN ANALYZE` on the windowed closed query uses `ix_inc_closed_at`.
6. Apply via `run_migrations.py`; ledgered in `tickets.schema_migrations`.

## Out of scope (future)

- **Open-backlog-over-time** / observed open→closed transitions need the append-only
  history capture (`tickets.closure_events` + daily snapshot) — separate plan.
- **Dispatch surface** (nearest-vehicle off `geog`) — after analytics.