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.

17 table(s) · 15 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.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_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_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 · dwh_gold · REMOVED 2026-06-05

The ops schema (workshop / tickets / dispatch / SLA / odometer / cost_rates / kpi_targets) and the dwh_gold schema (dim_vehicles, fact_daily_fleet_metrics, refresh_daily_metrics) were purged 2026-06-05 via migrations 12 / 13 — those features were never implemented. Also dropped: tracksolid.dispatch_log, tracksolid.v_sla_inflight, tracksolid.v_utilisation_daily, and their Grafana panels. The separate tracksolid_dwh server (31.97.44.246:5888) is unrelated and untouched.

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` · 13 panels

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`
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`

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.