tracksolid_timescale_grafana_prod/docs/superpowers/specs/2026-04-19-daily-operations-dashboard-design.md

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.