Fireside Communications — Tracksolid Fleet Intelligence

Platform reference · generated 2026-06-05 09:46 UTC from the live database (tracksolid_db @ twala.rahamafresh.com:5433)

What this document is. A current-state reference for the fleet telematics platform after the map dashboards were moved off n8n onto the dedicated dashboard_api service (fleetapi.rahamafresh.com). Covers data flow, deployment, the read-API, and the full database schema (tables, views, functions).

1 · Architecture & data flow

Tracksolid Pro / Jimi IoT APIeu-open.tracksolidpro.com — GPS, trips, alarms, OBD

→

Ingestion (Python)ingest_movement · ingest_events · webhook_receiver

→

TimescaleDB + PostGISschema tracksolid — single source of truth

reporting.* read layerv_live_positions · v_trips (matview) · fn_* functions

→

dashboard_api (FastAPI)https://fleetapi.rahamafresh.com :8890

→

Map SPAs (rustfs/S3)liveposition · fleetintelligence

Grafana reads the same TimescaleDB directly via the tracksolid.v_* views (NOC + daily-ops dashboards).

2 · The n8n → fleetapi migration

The two map dashboards previously fetched data from n8n webhooks at https://automate.rahamafresh.com/webhook/…. n8n was only a thin HTTP→SQL proxy and a fragile link in the live-data path. It has been replaced by dashboard_api, a small FastAPI service that calls the proven reporting.* functions directly. The SPAs now point their N8N_BASE constant at https://fleetapi.rahamafresh.com; all webhook paths are unchanged, so the migration was a one-line front-end change per SPA plus the new service.

Aspect	Before (n8n)	After (fleetapi)
SPA base URL	automate.rahamafresh.com	fleetapi.rahamafresh.com
Live Positions	/webhook/live-positions	same path → reporting.fn_live_positions
Vehicle trail	/webhook/live-positions/track	same path (route alias added) → fn_vehicle_track
Fleet trips	/webhook/fleet-dashboard (GET+POST)	same paths → fn_trips_for_map
Transport	n8n workflow + credentials	FastAPI + psycopg2 pool (shared with ingestion)
v_trips refresh	n8n scheduled workflow	dashboard_api background loop (every 5 min)

Cutover completed 2026-06-05. Two follow-ups closed the last gaps left when n8n was switched off: (1) the POST body parser was made Content-Type aware so the SPA's form-encoded filters actually apply (f1387d1); (2) the reporting.v_trips refresh — previously a scheduled n8n workflow — was moved into dashboard_api so the read layer keeps itself fresh (30b3515). See §4 and §7.

3 · Deployment topology

Host twala.rahamafresh.com (31.97.44.246, Hostinger VPS) running Coolify 4.1. The Tracksolid stack is a docker-compose application (container suffix bo3nov2ija7g8wn9b1g2paxs); the reverse proxy is Traefik on the coolify network with Let's Encrypt TLS.

Service	Role	Port / domain
timescale_db	PostgreSQL 16 + TimescaleDB 2.15 + PostGIS 3	5432 internal · 5433 host
ingest_movement	GPS positions, trips, parking, track-list, device sync	—
ingest_events	Alarm event polling	—
webhook_receiver	Push receiver (/pushobd /pushevent /pushtripreport …)	8888 (Traefik)
dashboard_api	Map read-API (replaces n8n)	8890 → fleetapi.rahamafresh.com
grafana	NOC + daily-ops dashboards	3000 → grafana.rahamafresh.com
pgbouncer	Connection pooler (SCRAM via public.user_lookup)	6432 internal
db_backup	pg_dump → rustfs S3 (bucket fleet-db), scheduled	—

The two map front-ends are single-file SPAs stored as index.html objects in rustfs (S3) at s3.rahamafresh.com (buckets liveposition, fleetintelligence), each served by a small nginx proxy container. The dashboard_api enforces CORS for both SPA origins.

Operational note. dashboard_api currently runs as a standalone Traefik-labelled container reusing the app image with dashboard_api_rev.py bind-mounted. The durable form is the compose dashboard_api service (already in docker-compose.yaml) once the branch is merged and the fleetapi domain is set in Coolify.

4 · Read-API reference (dashboard_api)

Base: https://fleetapi.rahamafresh.com · all responses JSON · CORS limited to the two SPA origins.

Method	Path	Params	Returns	Notes
GET	/health	—	{status: ok}	Liveness probe.
GET	/webhook/live-positions	cost_centre?, acc_status?	{summary, geojson}	Live Positions map feed → reporting.fn_live_positions. ~80 vehicle point features.
GET	/webhook/live-positions/track	vehicle_number, hours(1-24)	GeoJSON Feature (LineString)	One vehicle's recent trail. Alias: /webhook/vehicle-track.
GET	/webhook/vehicle-track	vehicle_number, hours	GeoJSON Feature	Alias of live-positions/track (kept for compatibility).
GET	/webhook/fleet-dashboard	—	{drivers, cost_centres, cities, vehicles}	Filter options for the Fleet Intelligence UI.
POST	/webhook/fleet-dashboard	form-urlencoded: period\|start_date\|end_date, vehicle_numbers, driver, cost_centre, assigned_city	trips GeoJSON payload	Trips for the map → reporting.fn_trips_for_map. period preset: today\|7d\|30d\|custom. Body parsed by Content-Type — the SPA posts `application/x-www-form-urlencoded`; JSON also accepted.

Fix (2026-06-05). The POST handler originally read the body as JSON only, but the SPA sends x-www-form-urlencoded; the mismatch silently dropped every filter so the map always returned the whole fleet. Now parsed by Content-Type (parse_qs for form bodies). Commit f1387d1.

5 · Database schema

tracksolid · Live data — the single source of truth. Ingested from the Tracksolid API.

18 table(s) · 17 view(s) · 1 function(s) documented.

tracksolid.alarmstable344,356 rows

Alarm events (alarm_type, alarm_name, alarm_time).

Column	Type	Null	Default	Comment
id	bigint	NOT NULL	nextval('alarms_id_seq'::regclass)
imei	text
alarm_type	text
alarm_time	timestamp with time zone
geom	geometry(Point,4326)
lat	double precision
lng	double precision
speed	numeric(7,2)
acc_status	text
updated_at	timestamp with time zone		now()
alarm_name	text
source	text		'poll'::text
severity	text			Alarm severity level: critical \| warning \| info
geofence_id	text			Tracksolid geofence ID if this is a geofence alarm
geofence_name	text			Human-readable geofence name
acknowledged_at	timestamp with time zone			Timestamp when alarm was acknowledged by an operator
acknowledged_by	text			Username or ID of operator who acknowledged the alarm

tracksolid.api_token_cachetable1 rows

OAuth2 token cache for the Jimi/Tracksolid API.

Column	Type	Null	Default
id	bigint	NOT NULL	nextval('api_token_cache_id_seq'::regclass)
account	text	NOT NULL
access_token	text	NOT NULL
refresh_token	text
app_key	text
expires_at	timestamp with time zone	NOT NULL
obtained_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()

tracksolid.device_eventstable0 rows

Device network connection and disconnection events from /pushevent webhook.

Column	Type	Null	Default	Comment
id	bigint	NOT NULL	nextval('device_events_id_seq'::regclass)
imei	text	NOT NULL
event_type	text	NOT NULL		LOGIN = device connected to network; LOGOUT = device disconnected
event_time	timestamp with time zone	NOT NULL
timezone	text
created_at	timestamp with time zone	NOT NULL	now()

tracksolid.devicestable181 rows

Device / driver / vehicle registry. One row per tracker (IMEI). Source of plate, driver, SIM, model.

Column	Type	Null	Default	Comment
imei	text	NOT NULL
device_name	text
mc_type	text
mc_type_use_scope	text
vehicle_name	text
vehicle_number	text
vehicle_models	text
vehicle_icon	text
vin	text
engine_number	text
vehicle_brand	text
fuel_100km	numeric(6,2)
driver_name	text
driver_phone	text
sim	text
iccid	text
imsi	text
account	text
customer_name	text
device_group_id	text
device_group	text
activation_time	timestamp with time zone
expiration	timestamp with time zone
enabled_flag	smallint	NOT NULL	1
status	text		'active'::text
city	text
current_mileage_km	numeric(12,2)
created_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()
last_synced_at	timestamp with time zone
vehicle_category	text			Vehicle type: truck \| van \| motorcycle \| car \| other
cost_centre	text			Business unit or department this vehicle belongs to
assigned_route	text			Regular route name or ID for route-based reporting
depot_geom	geometry(Point,4326)			Home base/depot coordinates (WGS84)
depot_address	text			Human-readable depot address
assigned_city	text			Operating territory code: NBO (Nairobi) \| MBA (Mombasa) \| KLA (Kampala). Used for city-cohort analytics and geographic drift detection.

tracksolid.dispatch_logtable0 rows

Persistent record of every dispatch decision. Powers SLA metrics: dispatch latency, depart delay, time-to-site, wrench time.

Column	Type	Null	Default	Comment
dispatch_id	bigint	NOT NULL	nextval('dispatch_log_dispatch_id_seq'::regclass)
ticket_id	text	NOT NULL
imei	text	NOT NULL
driver_name	text
job_lat	double precision	NOT NULL
job_lng	double precision	NOT NULL
job_geom	geometry(Point,4326)
assigned_at	timestamp with time zone	NOT NULL	now()
first_movement_at	timestamp with time zone			First trip start after assigned_at. Back-filled nightly from trips.
on_site_at	timestamp with time zone			Time vehicle entered 150 m radius of job_geom. Back-filled nightly.
resolved_at	timestamp with time zone			Ticket close time from the ops system (ops.tickets.closed_at).
cancelled_at	timestamp with time zone
distance_km	numeric(8,2)
created_at	timestamp with time zone	NOT NULL	now()

tracksolid.fault_codestable0 rows

—

Column	Type	Null	Default
id	bigint	NOT NULL	nextval('fault_codes_id_seq'::regclass)
imei	text	NOT NULL
reported_at	timestamp with time zone	NOT NULL
fault_code	text	NOT NULL
status_flags	integer
lat	double precision
lng	double precision
geom	geometry(Point,4326)
event_time	timestamp with time zone
created_at	timestamp with time zone	NOT NULL	now()

tracksolid.fuel_readingshypertable~0 rows

Fuel/oil sensor readings from /pushoil webhook. Unit varies per sensor: cm | % | V | L.

Column	Type	Null	Default	Comment
imei	text	NOT NULL
reading_time	timestamp with time zone	NOT NULL
sensor_path	text			Sensor channel identifier from the device (path field in API payload)
value	numeric(10,3)
unit	text			Measurement unit: cm (tank depth), % (percentage), V (voltage), L (litres)
lat	double precision
lng	double precision
geom	geometry(Point,4326)
created_at	timestamp with time zone	NOT NULL	now()

tracksolid.geofencestable0 rows

Geofence boundary definitions synced from the Tracksolid platform.

Column	Type	Null	Default	Comment
id	bigint	NOT NULL	nextval('geofences_id_seq'::regclass)
fence_id	text
fence_name	text	NOT NULL
fence_type	text			circle \| polygon
geom	geometry(Geometry,4326)
radius_m	numeric(10,2)			Radius in metres — only applicable for circle type geofences
description	text
created_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()

tracksolid.heartbeatshypertable~0 rows

Device heartbeat hypertable.

Column	Type	Null	Default
imei	text	NOT NULL
gate_time	timestamp with time zone	NOT NULL
power_level	smallint
gsm_signal	smallint
acc_status	smallint
power_status	smallint
fortify	smallint
created_at	timestamp with time zone	NOT NULL	now()

tracksolid.ingestion_logtable274,181 rows

API call audit trail — one row per ingestion run.

Column	Type	Null	Default
id	bigint	NOT NULL	nextval('ingestion_log_id_seq'::regclass)
run_at	timestamp with time zone	NOT NULL	now()
endpoint	text	NOT NULL
imei_count	integer	NOT NULL	0
rows_upserted	integer	NOT NULL	0
rows_inserted	integer	NOT NULL	0
duration_ms	integer	NOT NULL	0
success	boolean	NOT NULL	true
error_code	text
error_message	text

tracksolid.lbs_readingstable0 rows

Cell tower / WiFi positioning fallback data from /pushlbs webhook. Used when GPS signal is unavailable.

Column	Type	Null	Default	Comment
id	bigint	NOT NULL	nextval('lbs_readings_id_seq'::regclass)
imei	text	NOT NULL
gate_time	timestamp with time zone	NOT NULL
post_type	text			Positioning technology: WIFI \| LBS (cell tower)
lbs_data	jsonb			Raw JSON payload containing MCC, MNC, and cell tower list for approximate geocoding.
created_at	timestamp with time zone	NOT NULL	now()

tracksolid.live_positionstable179 rows

Latest fix per IMEI, refreshed every 60s by ingest_movement. Feeds reporting.v_live_positions.

Column	Type	Null	Default
imei	text	NOT NULL
geom	geometry(Point,4326)
lat	double precision
lng	double precision
pos_type	text
confidence	smallint
gps_time	timestamp with time zone
hb_time	timestamp with time zone
speed	numeric(7,2)
direction	numeric(6,2)
acc_status	text
gps_signal	smallint
gps_num	smallint
elec_quantity	numeric(5,2)
power_value	numeric(5,2)
battery_power_val	numeric(5,2)
tracker_oil	text
temperature	numeric(8,2)
current_mileage	numeric(12,2)
device_status	text
expire_flag	text
activation_flag	text
loc_desc	text
recorded_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()

tracksolid.obd_readingstable0 rows

OBD diagnostics — push only via /pushobd webhook.

Column	Type	Null	Default	Comment
id	bigint	NOT NULL	nextval('obd_readings_id_seq'::regclass)
imei	text
reading_time	timestamp with time zone
engine_rpm	integer			Engine RPM from OBD PID 0x0C
fuel_level_pct	numeric(5,2)			Fuel tank level % from OBD PID 0x2F
updated_at	timestamp with time zone		now()
car_type	smallint
acc_state	smallint
status_flags	integer
lat	double precision
lng	double precision
geom	geometry(Point,4326)
obd_data	jsonb			Raw obdJson from Jimi push. Contains dataID1..N fields (engine RPM, coolant temp, fuel level, etc.)
coolant_temp_c	numeric(6,2)			Coolant temperature °C from OBD PID 0x05
battery_voltage	numeric(5,2)			Battery voltage (V) from OBD PID 0x42
intake_pressure	numeric(6,2)			Intake manifold pressure kPa from OBD PID 0x0B
throttle_pct	numeric(5,2)			Throttle position % from OBD PID 0x11
vehicle_speed	numeric(7,2)			Vehicle speed km/h from OBD PID 0x0D
engine_load_pct	numeric(5,2)			Calculated engine load % from OBD PID 0x04

tracksolid.parking_eventstable0 rows

Stop events with duration + address.

Column	Type	Null	Default
id	bigint	NOT NULL	nextval('parking_events_id_seq'::regclass)
imei	text	NOT NULL
event_type	text
start_time	timestamp with time zone	NOT NULL
end_time	timestamp with time zone
duration_seconds	integer
geom	geometry(Point,4326)
address	text
updated_at	timestamp with time zone		now()

tracksolid.position_historyhypertable~3,596,013 rows

All GPS fixes (hypertable, partitioned by gps_time). source='poll' (60s) or 'track_list' (30m high-res).

Column	Type	Null	Default
imei	text	NOT NULL
gps_time	timestamp with time zone	NOT NULL
geom	geometry(Point,4326)
lat	double precision
lng	double precision
speed	numeric(7,2)
direction	numeric(6,2)
acc_status	text
satellite	smallint
current_mileage	numeric(12,2)
recorded_at	timestamp with time zone		now()
altitude	numeric(8,2)
post_type	smallint
source	text		'poll'::text

tracksolid.schema_migrationstable9 rows

—

Column	Type	Null	Default	Comment
filename	text	NOT NULL
applied_at	timestamp with time zone	NOT NULL	now()

tracksolid.temperature_readingshypertable~0 rows

Temperature and humidity sensor readings from /pushtem webhook. For cold-chain / refrigerated cargo monitoring.

Column	Type	Null	Default
imei	text	NOT NULL
reading_time	timestamp with time zone	NOT NULL
temperature	numeric(6,2)
humidity_pct	numeric(5,2)
created_at	timestamp with time zone	NOT NULL	now()

tracksolid.tripstable43,765 rows

Trip summaries: distance_km, driving time, avg/max speed, route geometry + addresses (FIX-M20).

Column	Type	Null	Default	Comment
id	bigint	NOT NULL	nextval('trips_id_seq'::regclass)
imei	text	NOT NULL
start_time	timestamp with time zone	NOT NULL
end_time	timestamp with time zone
start_geom	geometry(Point,4326)
end_geom	geometry(Point,4326)
distance_km	numeric(12,2)			Trip distance in kilometres. Corrected from mm storage on migration 04 (2026-04-10).
avg_speed_kmh	numeric(7,2)
max_speed_kmh	numeric(7,2)
updated_at	timestamp with time zone		now()
fuel_consumed_l	numeric(8,2)
idle_time_s	integer
driving_time_s	integer			runTimeSecond from API: total driving time in seconds
trip_seq	integer
source	text		'poll'::text	poll = from API polling, push = from webhook push
route_geom	geometry(LineString,4326)			Full GPS route polyline built at ingest from position_history points where gps_time BETWEEN start_time AND end_time. NULL when fewer than 2 fixes are available for the trip window.
start_address	text			Reverse-geocoded human-readable address near start_geom (Nominatim). NULL on lookup failure; address is best-effort, not authoritative.
end_address	text			Reverse-geocoded human-readable address near end_geom (Nominatim). NULL on lookup failure; address is best-effort, not authoritative.
vehicle_plate	text			Denormalised tracksolid.devices.vehicle_number cached at trip-insert time. Avoids a join for trip displays; refreshed only on next ingest.
waypoints_count	integer			Number of position_history fixes that contributed to route_geom. Audit aid: 0 or 1 means route_geom is NULL or degenerate.

tracksolid.v_active_dispatch_mapview

01_BusinessAnalytics.md §4.3 All Active Vehicles Map. Geomap source.

Column	Type	Null	Default	Comment
imei	text
vehicle_number	text
vehicle_name	text
driver_name	text
driver_phone	text
assigned_city	text
lat	double precision
lng	double precision
speed	numeric(7,2)
direction	numeric(6,2)
acc_status	text
last_fix	timestamp without time zone
status	text

tracksolid.v_alarms_dailyview

01_BusinessAnalytics.md §7 Panel 7 Alarm Frequency. Stacked-by-alarm_name time series.

Column	Type	Null	Default	Comment
day	date
alarm_name	text
alarm_count	bigint

tracksolid.v_currently_idleview

01_BusinessAnalytics.md §2.2 idle lens. Engine on, speed <2 km/h, fix in last 15m.

Column	Type	Null	Default	Comment
imei	text
vehicle_number	text
driver_name	text
assigned_city	text
lat	double precision
lng	double precision
since	timestamp without time zone
idle_seconds	integer

tracksolid.v_driver_aggregates_dailyview

01_BusinessAnalytics.md §3.1 (speeding) + §3.2 (harsh driving). Daily grain; panels window via $__timeFilter(day).

Column	Type	Null	Default	Comment
imei	text
driver_name	text
vehicle_number	text
assigned_city	text
day	date
km	numeric
trips	bigint
events_80	bigint
events_100	bigint
events_120	bigint
harsh_events	bigint
speeding_per_100km	numeric
harsh_per_100km	numeric

tracksolid.v_driver_attendance_dailyview

—

Column	Type	Null	Default	Comment
report_date	date
driver_name	text
vehicle_number	text
cost_centre	text
assigned_city	text
reporting_time	time without time zone
start_address	text
start_lat	double precision
start_lng	double precision
status	text
mins_from_start	integer

tracksolid.v_driver_clock_dailyview

Driver clock-in / clock-out daily series. One row per IMEI per Africa/Nairobi date with at least one trip. Reporting/closing times are derived from trip start_time/end_time bounded by local-date start; closing_ts may cross midnight UTC. No policy embedded — n8n applies tardiness rules and cost-centre filtering. See plan i-would-like-to-wobbly-volcano (2026-05-04).

Column	Type	Null	Default	Comment
imei	text
driver_name	text
vehicle_number	text
cost_centre	text
assigned_city	text
report_date	date
reporting_time	time without time zone
closing_time	time without time zone
reporting_ts	timestamp with time zone
closing_ts	timestamp with time zone
start_lat	double precision
start_lng	double precision
start_address	text
end_lat	double precision
end_lng	double precision
end_address	text
trips_count	bigint
total_km	numeric
drive_hours	numeric

tracksolid.v_driver_clock_todayview

Today snapshot of v_driver_clock_daily, filtered to (NOW() AT TIME ZONE 'Africa/Nairobi')::date. Refreshes as trips land throughout the day.

Column	Type	Null	Default	Comment
imei	text
driver_name	text
vehicle_number	text
cost_centre	text
assigned_city	text
report_date	date
reporting_time	time without time zone
closing_time	time without time zone
reporting_ts	timestamp with time zone
closing_ts	timestamp with time zone
start_lat	double precision
start_lng	double precision
start_address	text
end_lat	double precision
end_lng	double precision
end_address	text
trips_count	bigint
total_km	numeric
drive_hours	numeric

tracksolid.v_fleet_km_dailyview

01_BusinessAnalytics.md §7 Panel 5 Distance Trend. City-cohort cut built in.

Column	Type	Null	Default	Comment
day	date
assigned_city	text
km	numeric
active_vehicles	bigint
trips	bigint

tracksolid.v_fleet_statusview

—

Column	Type	Null	Default	Comment
imei	text
vehicle_number	text
driver_name	text
lat	double precision
lng	double precision
geom	geometry(Point,4326)
speed	numeric(7,2)
acc_status	text
gps_time	timestamp with time zone
connectivity_status	text
seconds_since_fix	integer

tracksolid.v_fleet_todayview

01_BusinessAnalytics.md §9 Fleet Readiness Scorecard. One row per device with today's roll-up.

Column	Type	Null	Default	Comment
imei	text
driver_name	text
vehicle_number	text
vehicle_name	text
assigned_city	text
enabled_flag	smallint
km_today	numeric
trips_today	bigint
drive_hours	numeric
idle_hours	numeric
first_departure	time without time zone
last_return	time without time zone
alarms_today	bigint
last_fix	timestamp without time zone
last_speed	numeric(7,2)
did_not_move	boolean

tracksolid.v_fleet_traceview

—

Column	Type	Null	Default	Comment
gid	bigint
driver_name	text
vehicle_name	text
device_name	text
imei	text
geom	geometry(Point,4326)
lat	numeric
lng	numeric
start_time	timestamp without time zone
end_time	timestamp without time zone
day_local	date
hour_local	integer
dow_local	integer
gps_time_utc	timestamp with time zone
recorded_at	timestamp with time zone
speed	numeric(7,2)
direction	numeric(6,2)
current_mileage	numeric(12,2)
stationary	boolean
trip_id	bigint

tracksolid.v_ingestion_healthview

—

Column	Type	Null	Default	Comment
endpoint	text
run_at	timestamp with time zone
success	boolean
error_message	text
seconds_ago	integer

tracksolid.v_mileage_daily_caggview

—

Column	Type	Null	Default	Comment
bucket	timestamp with time zone
imei	text
dist_km	numeric
avg_speed	numeric

tracksolid.v_sla_inflightview

01_BusinessAnalytics.md §4.5 Field-Service SLA Metrics. Open tickets + last 24h resolved.

Column	Type	Null	Default	Comment
ticket_id	text
customer	text
priority	text
job_type	text
status	text
created_at	timestamp with time zone
assigned_at	timestamp with time zone
closed_at	timestamp with time zone
assigned_imei	text
driver_name	text
first_movement_at	timestamp with time zone
on_site_at	timestamp with time zone
resolved_at	timestamp with time zone
dispatch_mins	numeric
enroute_mins	numeric
onsite_mins	numeric
resolution_mins	numeric
ticket_stage	text

tracksolid.v_trips_enrichedview

tracksolid.trips with computed daily_seq (Nth trip per IMEI per local Africa/Nairobi day) and trip_date_eat. Replaces reliance on the device-supplied trip_seq column, which is NULL for poll-ingested trips.

Column	Type	Null	Default	Comment
id	bigint
imei	text
start_time	timestamp with time zone
end_time	timestamp with time zone
start_geom	geometry(Point,4326)
end_geom	geometry(Point,4326)
distance_km	numeric(12,2)
avg_speed_kmh	numeric(7,2)
max_speed_kmh	numeric(7,2)
updated_at	timestamp with time zone
fuel_consumed_l	numeric(8,2)
idle_time_s	integer
driving_time_s	integer
trip_seq	integer
source	text
route_geom	geometry(LineString,4326)
start_address	text
end_address	text
vehicle_plate	text
waypoints_count	integer
trip_date_eat	date
daily_seq	bigint

tracksolid.v_utilisation_dailyview

01_BusinessAnalytics.md §7 Panel 8 Utilisation Heatmap. Empty until nightly ETL runs.

Column	Type	Null	Default	Comment
day	date
imei	text
vehicle_number	text
driver_name	text
assigned_city	text
total_distance_km	numeric(12,2)
total_drive_hours	numeric(8,2)
total_idle_hours	numeric(8,2)
alarm_count	integer
overspeed_count	integer
utilisation_pct	numeric

tracksolid.v_vehicles_not_moved_todayview

01_BusinessAnalytics.md §2.3. Enabled vehicles with zero trips today.

Column	Type	Null	Default	Comment
imei	text
vehicle_name	text
vehicle_number	text
driver_name	text
assigned_city	text
last_seen	timestamp without time zone
speed	numeric(7,2)

tracksolid.set_updated_at(…)function · plpgsql

—

arguments	()
returns	trigger

reporting · Read layer that backs the map dashboards (consumed by dashboard_api).

2 table(s) · 12 view(s) · 4 function(s) documented.

reporting.refresh_logtable3,558 rows

One row per REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.v_trips. Written by dashboard_api's built-in refresher (source='dashboard_api', every 5 min); the retired n8n job (source='n8n') was the previous writer. Read MAX(refreshed_at) for staleness.

Column	Type	Null	Default
refreshed_at	timestamp with time zone	NOT NULL	now()
source	text	NOT NULL	'n8n'::text
duration_ms	integer
row_count	integer
notes	text

reporting.v_tripsmatview31,670 rows

Canonical trip view, MATERIALIZED for dashboard read latency (see header diagnostic note). All timestamps in Africa/Nairobi. LEFT JOINs devices so trips with no device row keep NULL cost_centre rather than disappearing. Refresh via n8n every 5 min; see reporting.refresh_log. See 260519_trips_kepler_deployment.md §Phase 1+6.

Column	Type	Null	Default	Comment
trip_id	bigint
imei	text
device_name	text
vehicle_number	text
vehicle_models	text
vehicle_category	text
cost_centre	text
assigned_city	text
assigned_driver	text
start_time	timestamp without time zone
end_time	timestamp without time zone
trip_date	date
start_hour	integer
start_dow	integer
daily_seq	bigint
distance_km	numeric(12,2)
avg_speed_kmh	numeric(7,2)
max_speed_kmh	numeric(7,2)
idle_time_s	integer
driving_time_s	integer
fuel_consumed_l	numeric(8,2)
waypoints_count	integer
start_address	text
end_address	text
start_geom	geometry(Point,4326)
end_geom	geometry(Point,4326)
route_geom	geometry(LineString,4326)
route_geojson	json
is_meaningful_route	boolean
updated_at	timestamp without time zone

reporting.v_daily_cost_centreview

Cost-centre × day rollup. Excludes trips with NULL cost_centre.

Column	Type	Null	Default	Comment
trip_date	date
cost_centre	text
active_vehicles	bigint
active_drivers	bigint
trip_count	bigint
total_km	numeric
driving_hours	numeric
idle_hours	numeric
idle_pct	numeric
km_per_vehicle	numeric

reporting.v_daily_summaryview

Vehicle × day rollup. Filtered by is_meaningful_route. day_routes_geojson is the daily multi-line geometry for long-range Kepler.gl fallback.

Column	Type	Null	Default	Comment
trip_date	date
cost_centre	text
assigned_city	text
vehicle_number	text
assigned_driver	text
trip_count	bigint
total_km	numeric
driving_hours	numeric
idle_hours	numeric
idle_pct	numeric
first_trip_start	timestamp without time zone
last_trip_end	timestamp without time zone
avg_speed_kmh	numeric
max_speed_kmh	numeric
day_routes_geojson	json

reporting.v_filter_citiesview

Assigned-city dropdown source for the n8n dashboard. Distinct non-null cities.

Column	Type	Null	Default	Comment
assigned_city	text

reporting.v_filter_cost_centresview

Cost-centre dropdown source for the n8n dashboard. Distinct non-null centres.

Column	Type	Null	Default	Comment
cost_centre	text

reporting.v_filter_driversview

Driver dropdown source for the n8n dashboard. Distinct non-null drivers.

Column	Type	Null	Default	Comment
driver	text

reporting.v_filter_vehiclesview

Vehicle dropdown source for the n8n dashboard. Plate is the value; drivers column lists every driver historically associated with the vehicle. cost_centre and assigned_city carry the vehicle's most-recent assignment so the frontend can auto-set those dependent dropdowns.

Column	Type	Null	Default	Comment
vehicle_number	text
drivers	text
cost_centre	text
assigned_city	text

reporting.v_live_positionsview

Latest known fix per vehicle, deduped to one primary IMEI per normalised plate. Provenance: 260521_liveposition_deployment.md §Phase 1.

Column	Type	Null	Default	Comment
imei	text
vehicle_number	text
assigned_driver	text
cost_centre	text
assigned_city	text
vehicle_category	text
vehicle_models	text
mc_type	text
device_kind	text
lat	double precision
lng	double precision
speed	numeric(7,2)
direction	numeric(6,2)
acc_status	text
device_status	text
gps_signal	smallint
gps_num	smallint
current_mileage	numeric(12,2)
loc_desc	text
gps_time	timestamp with time zone
updated_at	timestamp with time zone
gps_time_eat	timestamp without time zone
updated_at_eat	timestamp without time zone
source_age_hours	numeric

reporting.v_monthly_cost_centreview

Cost-centre × month rollup. Executive grain. Excludes trips with NULL cost_centre.

Column	Type	Null	Default	Comment
month_start	date
month_label	text
cost_centre	text
active_vehicles	bigint
active_drivers	bigint
active_days	bigint
trip_count	bigint
total_km	numeric
driving_hours	numeric
idle_hours	numeric
idle_pct	numeric
km_per_vehicle	numeric

reporting.v_monthly_summaryview

Vehicle × month rollup with trend metrics (km_per_active_day, km_per_trip).

Column	Type	Null	Default	Comment
month_start	date
month_label	text
cost_centre	text
assigned_city	text
vehicle_category	text
vehicle_number	text
assigned_driver	text
trip_count	bigint
active_days	bigint
total_km	numeric
driving_hours	numeric
idle_hours	numeric
idle_pct	numeric
km_per_active_day	numeric
km_per_trip	numeric
avg_speed_kmh	numeric
peak_speed_kmh	numeric

reporting.v_trips_todayview

Today snapshot of reporting.v_trips, filtered to (NOW() AT TIME ZONE 'Africa/Nairobi')::date. Refreshes as trips land throughout the day.

Column	Type	Null	Default	Comment
trip_id	bigint
imei	text
device_name	text
vehicle_number	text
vehicle_models	text
vehicle_category	text
cost_centre	text
assigned_city	text
assigned_driver	text
start_time	timestamp without time zone
end_time	timestamp without time zone
trip_date	date
start_hour	integer
start_dow	integer
daily_seq	bigint
distance_km	numeric(12,2)
avg_speed_kmh	numeric(7,2)
max_speed_kmh	numeric(7,2)
idle_time_s	integer
driving_time_s	integer
fuel_consumed_l	numeric(8,2)
waypoints_count	integer
start_address	text
end_address	text
start_geom	geometry(Point,4326)
end_geom	geometry(Point,4326)
route_geom	geometry(LineString,4326)
route_geojson	json
is_meaningful_route	boolean
updated_at	timestamp without time zone

reporting.v_weekly_cost_centreview

Cost-centre × week rollup. Excludes trips with NULL cost_centre.

Column	Type	Null	Default	Comment
week_start	date
cost_centre	text
active_vehicles	bigint
active_drivers	bigint
active_days	bigint
trip_count	bigint
total_km	numeric
driving_hours	numeric
idle_hours	numeric
idle_pct	numeric
km_per_vehicle	numeric

reporting.v_weekly_summaryview

Vehicle × week rollup. Numeric only, no geometry.

Column	Type	Null	Default	Comment
week_start	date
cost_centre	text
assigned_city	text
vehicle_number	text
assigned_driver	text
trip_count	bigint
active_days	bigint
total_km	numeric
driving_hours	numeric
idle_hours	numeric
avg_trip_km	numeric

reporting.fn_live_positions(…)function · plpgsql

Dashboard contract for liveposition.rahamafresh.com. Returns {summary, geojson}. NULL params = wildcard. Reads reporting.v_live_positions (already deduped). Provenance: 260521_liveposition_deployment.md §Phase 2.

arguments	p_cost_centre text DEFAULT NULL::text, p_acc_status text DEFAULT NULL::text
returns	jsonb

reporting.fn_trips_for_map(…)function · plpgsql

Dashboard contract: see 260519_trips_kepler_deployment.md §Phase 2. Args (positional): vehicle_numbers[], driver, cost_centre, assigned_city, start_date, end_date. NULL = wildcard; empty array also = wildcard. Returns {summary, geojson} where each feature carries daily_seq for client-side sequence colouring. Refuses unfiltered ranges >31 days.

arguments	p_vehicle_numbers text[] DEFAULT NULL::text[], p_driver text DEFAULT NULL::text, p_cost_centre text DEFAULT NULL::text, p_assigned_city text DEFAULT NULL::text, p_start_date date DEFAULT NULL::date, p_end_date date DEFAULT NULL::date
returns	jsonb

reporting.fn_vehicle_track(…)function · sql

Returns a GeoJSON Feature(LineString) of the vehicle's last N hours of fixes from tracksolid.position_history. Provenance: 260521_liveposition_deployment.md §Phase 3.

arguments	p_vehicle_number text, p_hours integer DEFAULT 1
returns	jsonb

reporting.normalize_plate(…)function · sql

Canonicalise Kenyan vehicle plates: trim, collapse internal whitespace, and remove the space before a single trailing letter (e.g. "KDS 453 Y" → "KDS 453Y"). Used in v_trips so plate-level grouping/joining works consistently regardless of how the plate was typed into the registry.

arguments	p text
returns	text

ops · Workshop / tickets / odometer integrations.

5 table(s) · 1 view(s) · 0 function(s) documented.

ops.cost_ratestable3 rows

Reference rates for analytics monetisation: fuel price per litre by city, labour cost per hour by role. Resolution order in views: scope_type=city > scope_type=role > scope_type=global.

Column	Type	Null	Default	Comment
rate_key	text	NOT NULL
scope_type	text	NOT NULL		city \| role \| global
scope_value	text
metric	text	NOT NULL		fuel_per_litre \| labour_per_hour
amount	numeric(12,2)	NOT NULL
currency	text	NOT NULL
effective_from	date	NOT NULL	CURRENT_DATE
notes	text
created_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()

ops.kpi_targetstable12 rows

Traffic-light targets per KPI per scope. Resolution order in views: cost_centre > vehicle_category > city > global.

Column	Type	Null	Default	Comment
target_id	bigint	NOT NULL	nextval('ops.kpi_targets_target_id_seq'::regclass)
kpi_key	text	NOT NULL
scope_type	text	NOT NULL
scope_value	text
target_value	numeric(12,2)	NOT NULL
amber_threshold	numeric(12,2)
red_threshold	numeric(12,2)
direction	text	NOT NULL	'higher_is_better'::text	higher_is_better -> green when value >= target. lower_is_better -> green when value <= target.
effective_from	date	NOT NULL	CURRENT_DATE
notes	text
created_at	timestamp with time zone	NOT NULL	now()
updated_at	timestamp with time zone	NOT NULL	now()

ops.odometer_readingstable0 rows

Physical odometer captures from service, fuel card, or manual entry. Powers §3.8 Odometer Divergence audit.

Column	Type	Null	Default	Comment
reading_id	bigint	NOT NULL	nextval('ops.odometer_readings_reading_id_seq'::regclass)
imei	text	NOT NULL
reading_date	date	NOT NULL
reading_km	integer	NOT NULL
source	text			service \| fuel_card \| driver_manual \| workshop_form
recorded_by	text
created_at	timestamp with time zone	NOT NULL	now()

ops.service_logtable0 rows

Workshop service history. Powers §10 Service-Interval Forecaster.

Column	Type	Null	Default	Comment
service_id	bigint	NOT NULL	nextval('ops.service_log_service_id_seq'::regclass)
imei	text	NOT NULL
service_date	date	NOT NULL
odometer_km	integer	NOT NULL		Physical odometer reading at service time (integer km).
service_type	text			scheduled \| repair \| tyre \| bodywork \| inspection \| other
cost_kes	integer
notes	text
created_at	timestamp with time zone	NOT NULL	now()

ops.ticketstable0 rows

Skeleton for ticket data sourced from the Fireside ops system. Replace or extend to match the actual feed (Zoho Desk, Freshdesk, etc).

Column	Type	Null	Default	Comment
ticket_id	text	NOT NULL
assigned_imei	text
driver_name	text
customer	text
job_type	text
priority	text
status	text	NOT NULL	'open'::text	open \| assigned \| in_progress \| resolved \| cancelled
created_at	timestamp with time zone	NOT NULL
assigned_at	timestamp with time zone
closed_at	timestamp with time zone
job_lat	double precision
job_lng	double precision
job_geom	geometry(Point,4326)
ingested_at	timestamp with time zone	NOT NULL	now()

ops.vw_service_forecastview

Projected next-service date per vehicle based on 30-day km rate. Service interval default 10,000 km — override at query time if needed.

Column	Type	Null	Default	Comment
imei	text
driver_name	text
vehicle_number	text
last_service_date	date
last_service_odo	integer
current_odo	numeric(12,2)
km_since_service	numeric
km_to_next_service	numeric
km_per_day_30d	numeric
projected_service_date	date

dwh_gold · Nightly ETL aggregates.

2 table(s) · 0 view(s) · 1 function(s) documented.

dwh_gold.dim_vehiclestable0 rows

—

Column	Type	Null	Default
vehicle_key	integer	NOT NULL	nextval('dim_vehicles_vehicle_key_seq'::regclass)
imei	text
vehicle_number	text
is_active	boolean		true

dwh_gold.fact_daily_fleet_metricstable0 rows

—

Column	Type	Null	Comment
day	date	NOT NULL
vehicle_key	integer	NOT NULL
total_distance_km	numeric(12,2)		Total km driven that day across all trips
max_speed_kmh	numeric(7,2)
idle_hours	numeric(5,2)
total_trips	integer		Number of completed trips
total_drive_hours	numeric(8,2)		Total hours of active driving (engine on + moving)
total_idle_hours	numeric(8,2)		Total hours engine on but stationary
fuel_consumed_l	numeric(10,3)		Total fuel consumed in litres (from webhook trip reports)
alarm_count	integer		Total alarm events triggered that day
overspeed_count	integer		Number of overspeed alarm events
day_start_time	time without time zone		Time of first trip start (Africa/Nairobi)
day_end_time	time without time zone		Time of last trip end (Africa/Nairobi)
avg_speed_kmh	numeric(7,2)		Fleet average speed across all trips that day
peak_speed_kmh	numeric(7,2)		Highest max_speed_kmh recorded across all trips

dwh_gold.refresh_daily_metrics(…)function · plpgsql

Populates or refreshes fact_daily_fleet_metrics for the given date. Call nightly: SELECT dwh_gold.refresh_daily_metrics(CURRENT_DATE - 1);

arguments	target_date date
returns	void

public · Extension objects + pgbouncer auth helper.

1 table(s) · 3 view(s) · 1 function(s) documented.

public.spatial_ref_systable8,500 rows

—

Column	Type	Null
srid	integer	NOT NULL
auth_name	character varying(256)
auth_srid	integer
srtext	character varying(2048)
proj4text	character varying(2048)

public.geography_columnsview

—

Column	Type	Null	Default	Comment
f_table_catalog	name
f_table_schema	name
f_table_name	name
f_geography_column	name
coord_dimension	integer
srid	integer
type	text

public.geometry_columnsview

—

Column	Type	Null	Default	Comment
f_table_catalog	character varying(256)
f_table_schema	name
f_table_name	name
f_geometry_column	name
coord_dimension	integer
srid	integer
type	character varying(30)

public.trips_viz_v1view

—

Column	Type	Null	Default	Comment
trip_id	bigint
imei	text
vehicle_name	text
vehicle_number	text
cost_centre	text
start_time	timestamp with time zone
end_time	timestamp with time zone
distance_km	numeric(12,2)
avg_speed_kmh	numeric(7,2)
max_speed_kmh	numeric(7,2)
vehicle_plate	text
start_address	text
end_address	text
waypoints_count	integer
driving_time_s	integer
idle_time_s	integer
fuel_consumed_l	numeric(8,2)
trip_date_eat	date
daily_seq	bigint

public.user_lookup(…)function · plpgsql

pgbouncer SCRAM auth_query — returns (username, password-hash) for connection pooling.

arguments	in_user text, OUT uname text, OUT phash text
returns	record

6 · Grafana dashboards

Grafana is provisioned via a baked image and reads TimescaleDB directly (read-only role grafana_ro), mostly through the tracksolid.v_* analytics views documented in §5. Two dashboards ship with the stack.

NOC Fleet Operations — Live · uid `noc-fleet-live` · 9 panels

Panel	Type	Source view / table
Total Vehicles	stat	`tracksolid.devices`
Online Now	stat	`tracksolid.v_fleet_status`
Recent (5-30 min)	stat	`tracksolid.v_fleet_status`
Offline	stat	`tracksolid.v_fleet_status`
Moving Now	stat	`tracksolid.v_fleet_status`
Avg Speed (km/h)	stat	`tracksolid.v_fleet_status`
Live Vehicle Locations	geomap	`tracksolid.devices`, `tracksolid.live_positions`
Vehicle Status	table	`tracksolid.devices`, `tracksolid.live_positions`
Ingestion Health	table	`tracksolid.v_ingestion_health`

Daily Operations — Fleet & Dispatch · uid `daily-ops` · 21 panels

Section	Panel	Type	Source view / table
	Last GPS Fix (fleet)	stat	`tracksolid.live_positions`
	Vehicles reporting today	stat	`tracksolid.v_fleet_today`
	Fleet km today	stat	`tracksolid.v_fleet_today`
	Drive hours today	stat	`tracksolid.v_fleet_today`
	Idle hours today	stat	`tracksolid.v_fleet_today`
	Open alarms (24h)	stat	`tracksolid.alarms`
	In-flight SLA jobs	stat	`tracksolid.v_sla_inflight`
	Active Vehicles Map	geomap	`tracksolid.v_active_dispatch_map`
	Currently Idle (engine on, speed < 2)	table	`tracksolid.v_currently_idle`
	Vehicles Not Moved Today	table	`tracksolid.v_vehicles_not_moved_today`
	Per-Vehicle Daily Roll-up	table	`tracksolid.v_fleet_today`
	Driver Leaderboard	table	`tracksolid.v_driver_aggregates_daily`
	Fleet Distance — 7-day (by city)	timeseries	`tracksolid.v_fleet_km_daily`
	Alarm Frequency — 30-day (by type)	timeseries	`tracksolid.v_alarms_daily`
	Idle Cost (month-to-date)	stat	`tracksolid.v_utilisation_daily`
	Utilisation Heatmap (30-day)	heatmap	`tracksolid.v_utilisation_daily`
Row 7 — Field-Service SLAs (data-gated)	Dispatch SLA (median mins, 24h)	stat	`tracksolid.v_sla_inflight`
	En-route SLA (median mins, 24h)	stat	`tracksolid.v_sla_inflight`
	On-site SLA (median mins, 24h)	stat	`tracksolid.v_sla_inflight`
	Resolution SLA (median mins, 24h)	stat	`tracksolid.v_sla_inflight`
	At-risk tickets	table	`tracksolid.v_sla_inflight`

SLA panels (v_sla_inflight) and the utilisation heatmap (v_utilisation_daily) are data-gated on the ops ticket integration and nightly dwh_gold ETL respectively.

7 · Operational notes

Hypertables (position_history, heartbeats, fuel_readings, temperature_readings) report 0 in pg_stat_user_tables — counts above use TimescaleDB's approximate_row_count().
API distance fields are metres, not km (divide by 1000) — see FIX-M16.
reporting.v_trips is a materialized view (owned by reporting_refresher, DDL in migrations/11_reporting_schema.sql). It is kept current by dashboard_api's built-in background refresher — REFRESH MATERIALIZED VIEW CONCURRENTLY every VTRIPS_REFRESH_INTERVAL_S (default 300s), advisory-lock guarded so only one uvicorn worker refreshes per tick. This replaced the n8n scheduled workflow that refreshed it until n8n was retired (2026-06-01); a stale matview was the cause of the "no trips" incident on 2026-06-05. The container's DATABASE_URL connects as a superuser, which is why it may REFRESH a matview it does not own.
Migrations apply at container startup via run_migrations.py (idempotent, tracked in tracksolid.schema_migrations). Latest: 11_reporting_schema.sql.
Backups: db_backup sidecar → rustfs bucket fleet-db, scheduled (default 02:30/08:30/14:30/20:30 Africa/Nairobi).

See also: docs/CONNECTIONS.md, docs/reference/01_BusinessAnalytics.md, docs/manuals/OPERATIONS_MANUAL.md, and the project CLAUDE.md.