85d02c81a5
Add a second Grafana dashboard focused on daily operational KPIs and live dispatch, keeping the NOC Live dashboard untouched. - grafana/provisioning/dashboards-json/daily_operations_dashboard.json New dashboard covering §7 Blueprint Panels 3-8 and the §4 dispatch lens: freshness banner, today-at-a-glance stat row, active vehicles map, currently-idle table, vehicles-not-moved-today, per-vehicle daily KPI roll-up, driver behaviour leaderboard, distance trend, alarm frequency, idle cost MTD, utilisation heatmap, SLA row (collapsed, data-gated). - 07_analytics_views.sql Nine views in tracksolid.* wrapping the BA-file [DASHBOARD]-tagged queries. Each view carries COMMENT ON VIEW with its spec section. SELECT granted to grafana_ro. Smoke-tested against live DB. - run_migrations.py Register 06 and 07 in MIGRATIONS list with idempotent seed checks so future fresh deploys apply them correctly. - CLAUDE.md Retire the tracksolid_2 schema references (schema no longer exists); §9 Fleet State dated 2026-04-19 with correct pipeline status (running, 875 runs/24h, 0 failures) and accurate position_history row counts (hypertable stats don't show in pg_stat_user_tables). - docs/superpowers/specs/2026-04-19-daily-operations-dashboard-design.md Design spec covering architecture, views, panel layout, deployment, rollback, and known data gaps.
140 lines
6.7 KiB
Markdown
140 lines
6.7 KiB
Markdown
# Daily Operations Dashboard — Design Spec
|
||
|
||
**Date:** 2026-04-19
|
||
**Author:** David Kiania / Claude
|
||
**Status:** Approved for implementation
|
||
**Target:** `stage.rahamafresh.com` · `tracksolid_db` · `tracksolid` schema
|
||
|
||
---
|
||
|
||
## 1. Purpose
|
||
|
||
Extend the Grafana deployment with a second dashboard — **Daily Operations — Fleet & Dispatch** — that surfaces the `[DASHBOARD]`-tagged queries from `01_BusinessAnalytics.md`. Leaves the existing NOC Live dashboard untouched.
|
||
|
||
Two lenses combined in one dashboard:
|
||
|
||
- **Live Dispatch** — active map, currently-idle vehicles, vehicles-not-moved-today, in-flight SLAs.
|
||
- **§7 Blueprint Panels 3–8** — daily KPI table, driver leaderboard, distance trend, idle cost, alarm frequency, utilisation heatmap.
|
||
|
||
Refresh cadence is 1 min. The existing `noc_fleet_dashboard.json` keeps its 30s refresh and its NOC folder.
|
||
|
||
## 2. Scope
|
||
|
||
### In scope
|
||
- New dashboard JSON: `grafana/provisioning/dashboards-json/daily_operations_dashboard.json`.
|
||
- New migration: `07_analytics_views.sql` — nine views in `tracksolid.*` wrapping the BA-file queries.
|
||
- Provisioning config update: add a second dashboard provider (or extend the existing one) so Grafana picks up the new dashboard on container start.
|
||
- CLAUDE.md corrections (stale schema + fleet state facts).
|
||
|
||
### Out of scope
|
||
- Populating `ops.tickets` data (blocks SLA panels — they ship empty).
|
||
- Scheduling the nightly ETL (`dwh_gold.refresh_daily_metrics`) — utilisation heatmap renders empty until it runs.
|
||
- Any change to the NOC Live dashboard.
|
||
- Any ingestion code changes.
|
||
|
||
## 3. Architecture
|
||
|
||
Panels read **only** from views in `tracksolid.*`. No inline SQL in dashboards beyond a `SELECT * FROM tracksolid.v_* WHERE $__timeFilter(day) AND $city_filter`.
|
||
|
||
Rationale: `01_BusinessAnalytics.md` §0 says "build panels against the tag, not the SQL text." Views give a single edit point when a metric definition changes.
|
||
|
||
Views are **regular** (not materialised). `v_driver_aggregates_daily` is the one with performance risk once `position_history` grows; a `TODO` comment marks the spot for future conversion to a TimescaleDB continuous aggregate.
|
||
|
||
## 4. Views
|
||
|
||
All created in schema `tracksolid`, owned by `postgres`, `SELECT` granted to `grafana_ro`. Every view carries a `COMMENT ON VIEW` pointing back to the BA-file section.
|
||
|
||
| View | Grain | Source | BA § |
|
||
|---|---|---|---|
|
||
| `v_fleet_today` | imei × today | `devices`, `trips`, `live_positions`, `alarms` | §9 |
|
||
| `v_vehicles_not_moved_today` | imei | `devices`, `trips`, `live_positions` | §2.3 |
|
||
| `v_active_dispatch_map` | imei | `live_positions`, `devices` | §4.3 |
|
||
| `v_currently_idle` | imei | `live_positions`, `devices` | §2.2 |
|
||
| `v_driver_aggregates_daily` | imei × day | `position_history`, `trips`, `devices` | §3.1 + §3.2 |
|
||
| `v_fleet_km_daily` | city × day | `trips`, `devices` | §7 P5 |
|
||
| `v_alarms_daily` | day × alarm_name | `alarms` | §7 P7 |
|
||
| `v_utilisation_daily` | imei × day | `dwh_gold.fact_daily_fleet_metrics`, `dwh_gold.dim_vehicles`, `devices` | §7 P8 |
|
||
| `v_sla_inflight` | ticket | `ops.tickets`, `dispatch_log` | §4.5 |
|
||
|
||
## 5. Dashboard
|
||
|
||
**File:** `grafana/provisioning/dashboards-json/daily_operations_dashboard.json`
|
||
**UID:** `daily-ops`
|
||
**Refresh:** `1m`
|
||
**Default time range:** `now/d` → `now` (Africa/Nairobi)
|
||
|
||
### Variables
|
||
- `$city` — multi-select, query `SELECT DISTINCT COALESCE(assigned_city, 'unassigned') FROM tracksolid.devices ORDER BY 1`. Default All.
|
||
- `$active_only` — boolean, default true. Filters `devices.enabled_flag = 1` when true.
|
||
|
||
### Layout
|
||
|
||
**Freshness banner (top, full width):**
|
||
- Last GPS fix across all devices; green if < 5 min, amber 5–30 min, red > 30 min.
|
||
|
||
**Row 1 — Today at a Glance** (6 stats across)
|
||
- Vehicles reporting today · Fleet km today · Drive hours · Idle hours · Open alarms (24h) · In-flight SLA jobs.
|
||
|
||
**Row 2 — Live Dispatch**
|
||
- Active Vehicles Map (geomap) · Currently Idle Vehicles (table) · Vehicles Not Moved Today (table).
|
||
|
||
**Row 3 — Daily KPI Table**
|
||
- One row per vehicle: Vehicle · Driver · Km Today · Trips · Drive h · Idle h · First Departure · Last Return · Alarms.
|
||
|
||
**Row 4 — Driver Behaviour Leaderboard** (30-day)
|
||
- Columns: Driver · Vehicle · Km · Speeding/100km · Harsh/100km · Late starts · After-hours trips.
|
||
- Red/amber/green thresholds per BA-file §3.1 and §3.2 bands.
|
||
|
||
**Row 5 — Trends**
|
||
- Distance Trend 7-day (time series, stacked by city).
|
||
- Alarm Frequency 30-day (bar chart, stacked by alarm_name).
|
||
|
||
**Row 6 — Efficiency**
|
||
- Idle Cost Tracker (MTD idle hours and estimated KES wasted).
|
||
- Utilisation Heatmap (Y=vehicle, X=day-of-week).
|
||
|
||
**Row 7 — Field-Service SLAs** (collapsed by default)
|
||
- Four SLA compliance stats + at-risk tickets table. All empty until `ops.tickets` flows.
|
||
|
||
## 6. Provisioning
|
||
|
||
Extend `grafana/provisioning/dashboards/noc_fleet.yaml` to expose both dashboards, or add a sibling provider file. Both dashboards live under `/etc/grafana/provisioning/dashboards-json/` and are baked into the image via `grafana/Dockerfile`.
|
||
|
||
UI edits remain throwaway.
|
||
|
||
## 7. Deployment
|
||
|
||
1. Commit and push to `repo.rahamafresh.com`.
|
||
2. Coolify auto-pulls the commit, rebuilds the Grafana image, restarts.
|
||
3. `run_migrations.py` applies `07_analytics_views.sql` at `ingest_movement` container start.
|
||
4. New dashboard appears under the NOC folder on first Grafana load.
|
||
|
||
### Rollback
|
||
- Revert the commit and push. Grafana image rebuilds without the new dashboard.
|
||
- Drop the views manually: `DROP VIEW IF EXISTS tracksolid.v_* CASCADE;`
|
||
- Remove the `schema_migrations` row for `07_analytics_views.sql`.
|
||
|
||
## 8. Testing
|
||
|
||
**Pre-push, against live DB:**
|
||
- Apply `07_analytics_views.sql` with `docker exec -i $DB psql -U postgres -d tracksolid_db`.
|
||
- `SELECT COUNT(*) FROM tracksolid.v_*` for each view — must return without error. Empty is OK.
|
||
- `SET ROLE grafana_ro; SELECT * FROM tracksolid.v_fleet_today LIMIT 1;` to confirm read grant.
|
||
|
||
**Post-deploy, via browser:**
|
||
- Log into Grafana at the Coolify URL.
|
||
- Open "Daily Operations — Fleet & Dispatch".
|
||
- Confirm no red Grafana query errors. Empty panels acceptable for gated views (`v_utilisation_daily`, `v_sla_inflight`).
|
||
- Confirm `$city` variable populates (all `unassigned` today).
|
||
- Confirm freshness banner reflects latest `live_positions.gps_time`.
|
||
|
||
## 9. Known Data Gaps (panels ship, light up later)
|
||
|
||
| Panel | Blocked on |
|
||
|---|---|
|
||
| Utilisation Heatmap | Nightly `dwh_gold.refresh_daily_metrics` job not yet scheduled |
|
||
| SLA row | `ops.tickets` and `dispatch_log` empty until Zoho/Freshdesk integration |
|
||
| Driver Leaderboard (harsh) | `position_history` growth; 519 rows today |
|
||
| Idle Cost Tracker | `dwh_gold.fact_daily_fleet_metrics` (same dependency as heatmap) |
|
||
|
||
All queries are written against the target tables so panels light up automatically when upstream data flows.
|