kianiadee/tracksolid_timescale_grafana_prod
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions 1
tracksolid_timescale_grafan.../260412_baseline_report.md
David Kiania 2131faf8c6 Add 260412 baseline report — first trip data, FIX-M16 confirmed 
Post-deployment snapshot at ~00:15 EAT 2026-04-12. Key changes vs 260410:
- 3 trips recorded (FRED KMGW 538W HULETI, 6.94 km total) — pipeline validated
- FIX-M16 distance unit fix confirmed: implied speed matches API avgSpeed exactly
- 70 track_list fixes in 24h (was 13) — dense trail from active driving
- KDK 829A GP returned to primary depot from secondary Nairobi East cluster
- Uganda anomaly (X3-63282) persists — flagged for management
- Driver name root cause confirmed: not assigned in Tracksolid Pro UI

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
2026-04-12 00:14:27 +03:00

315 lines
17 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

# Fireside Communications — Fleet Baseline Report
**Date:** 2026-04-12 · **Time of queries:** ~00:15 EAT
**Database:** tracksolid_db on TimescaleDB
**Container:** timescale_db-bo3nov2ija7g8wn9b1g2paxs-192322642108
**Report scope:** All 63 registered devices · All tables · Post-migration 04 + 05
---
## 1. Migration Status
All four schema migrations applied and tracked:
| Migration File | Applied (EAT) | Status |
|---|---|---|
| `02_tracksolid_full_schema_rev.sql` | 2026-04-11 22:25:37 | ✓ Applied |
| `03_webhook_schema_migration.sql` | 2026-04-11 22:25:37 | ✓ Applied |
| `04_bug_fix_migration.sql` | 2026-04-11 22:25:37 | ✓ Applied — `distance_km` renamed & corrected |
| `05_enhancement_migration.sql` | 2026-04-11 22:25:37 | ✓ Applied — new tables + columns |
Schema is fully current. No pending migrations.
---
## 2. Table Row Counts (as of 00:15 EAT)
| Table | Rows | Δ vs 260410 | Notes |
|---|---|---|---|
| `tracksolid.devices` | **63** | — | Full fleet registry |
| `tracksolid.live_positions` | **19** | — | 19 devices with a known position (30% of fleet) |
| `tracksolid.position_history` | **101** | −36 | New container; accumulating since 22:25 EAT Apr 11 |
| `tracksolid.position_history` (`track_list`) | **70** | +57 | High-res trail density growing strongly |
| `tracksolid.alarms` | **3** | +1 | ACC_ON/ACC_OFF events from evening movement |
| `tracksolid.trips` | **3** | +3 | **First real trips recorded — FIX-M16 distance fix confirmed** |
| `tracksolid.parking_events` | **0** | — | Fix deployed; will populate with completed park cycles |
| `tracksolid.obd_readings` | **0** | — | Awaiting webhook registration |
| `tracksolid.device_events` | **0** | — | Awaiting `/pushevent` registration |
| `tracksolid.fuel_readings` | **0** | — | Awaiting `/pushoil` registration |
| `tracksolid.temperature_readings` | **0** | — | Awaiting `/pushtem` registration |
| `tracksolid.lbs_readings` | **0** | — | Awaiting `/pushlbs` registration |
| `tracksolid.geofences` | **0** | — | Not yet configured |
| `tracksolid.heartbeats` | **0** | — | Awaiting heartbeat webhook |
| `tracksolid.fault_codes` | **0** | — | Awaiting fault code data |
| `tracksolid.ingestion_log` | **43** | — | New container; fresh audit trail |
| `dwh_gold.fact_daily_fleet_metrics` | **0** | — | ETL not yet run |
| `dwh_gold.dim_vehicles` | **0** | — | Awaiting population |
---
## 3. Fleet Composition
**63 devices across 4 device models — unchanged:**
| Model | Count | Typical Use |
|---|---|---|
| AT4 | 23 | Asset / cargo hardwired tracker |
| JC400P | 23 | Camera-capable tracker (larger vehicles) |
| X3 | 10 | Compact vehicle tracker |
| GT06E | 7 | OBD-port tracker |
| **Total** | **63** | |
---
## 4. Full Device Registry
> All 63 devices. `driver_name` is blank for every device — confirmed root cause: no drivers assigned in Tracksolid Pro account (not a DB sync issue). `vehicle_number` also unpopulated.
| Device Name | Model | SIM | Odometer (km) | Expires | Status |
|---|---|---|---|---|---|
| AT4-51820 | AT4 | — | — | — | No position |
| AT4-53099 | AT4 | — | — | — | No position |
| AT4-54246 | AT4 | — | — | — | No position |
| AT4-55029 | AT4 | — | — | — | No position |
| AT4-55235 | AT4 | — | — | — | No position |
| AT4-57389 | AT4 | — | — | — | No position |
| AT4-61860 | AT4 | — | — | — | No position |
| AT4-64815 | AT4 | — | 0 | 2036-02-05 | Inactive (1,573h) |
| AT4-64823 | AT4 | — | — | — | No position |
| AT4-64880 | AT4 | — | — | — | No position |
| AT4-64989 | AT4 | — | — | — | No position |
| AT4-65010 | AT4 | — | — | — | No position |
| AT4-65135 | AT4 | — | — | — | No position |
| AT4-65341 | AT4 | — | — | — | No position |
| AT4-65598 | AT4 | — | — | — | No position |
| AT4-65648 | AT4 | — | — | — | No position |
| AT4-66158 | AT4 | — | — | — | No position |
| AT4-67271 | AT4 | — | — | — | No position |
| AT4-67693 | AT4 | — | — | — | No position |
| KCE 690F | AT4 | — | 0 | 2039-07-01 | Inactive (57,329h) |
| KCS 903Y JK SUB | AT4 | 0700024569 | 4 | 2039-06-09 | Inactive (15,229h) |
| KCU 865Q Vanguard Sub | AT4 | 0757270804 | 10 | 2039-12-20 | Inactive (15,445h) |
| KMEH 692C KAWASAKI | AT4 | 0110094467 | 3 | 2040-04-03 | Inactive (24,709h) |
| Belta KCU-647D | GT06E | 0110094465 | 235 | 2040-04-03 | Inactive (7,584h) — SERVICE FLAG |
| GT06E-85428 | GT06E | — | — | — | No position |
| GT06E-86319 | GT06E | — | — | — | No position |
| JK Subaru KCS 903Y | GT06E | 0746759925 | 73 | 2039-06-12 | Very stale (670h) |
| KCU 145Q Solo Xtrail | GT06E | 0757270810 | 53 | 2039-12-20 | Inactive (7,546h) |
| KCU 865Q Vanguard | GT06E | 0757270763 | 62 | 2039-12-20 | Stale (79h) |
| KDK 829A GP | GT06E | 0707923872 | 239 | 2042-10-29 | **Recent (2h) — DEPOT** — SERVICE FLAG |
| JC400P-07904 | JC400P | — | — | — | No position |
| JC400P-85041 | JC400P | — | — | — | No position |
| JC400P-85058 | JC400P | — | — | — | No position |
| JC400P-85751 | JC400P | — | 0 | 2036-03-11 | Inactive (746h) |
| JC400P-86270 | JC400P | — | — | — | No position |
| JC400P-86403 | JC400P | — | — | — | No position |
| JC400P-87625 | JC400P | — | — | — | No position |
| JC400P-87831 | JC400P | — | — | — | No position |
| JC400P-89431 | JC400P | — | — | — | No position |
| JC400P-89530 | JC400P | — | — | — | No position |
| JC400P-89563 | JC400P | — | — | — | No position |
| JC400P-89662 | JC400P | — | — | — | No position |
| JC400P-89977 | JC400P | — | — | — | No position |
| JC400P-90108 | JC400P | — | — | — | No position |
| JC400P-90199 | JC400P | — | — | — | No position |
| JC400P-90678 | JC400P | — | — | — | No position |
| JC400P-91619 | JC400P | — | — | — | No position |
| JC400P-92278 | JC400P | — | — | — | No position |
| JC400P-92716 | JC400P | — | — | — | No position |
| JC400P-92732 | JC400P | — | — | — | No position |
| JC400P-94233 | JC400P | — | — | — | No position |
| KDU 878T_CAM | JC400P | 0708351897 | 2 | 2035-08-18 | Inactive (3,081h) |
| KDW 632M HL Cam | JC400P | 300002396032 IoT | 0 | 2036-03-11 | Inactive (756h) |
| FRED KMGW 538W HULETI | X3 | 0119867174 | 2 | 2036-02-08 | **Active (0.1h) — MOVED** |
| KDU 878T_Track | X3 | 0708352823 | 5 | 2035-08-18 | Stale (79h) |
| KDW 632M HL Tracker | X3 | 300002396033 IoT | 0 | 2036-02-09 | Inactive (744h) |
| KMGR 409U HENRY JAZZ | X3 | 0768697302 | 7 | 2035-07-31 | Recent (7h) |
| X3-59405 | X3 | — | — | — | No position |
| X3-63282 | X3 | — | 4 | 2036-02-14 | **Active (0.2h) — UGANDA ANOMALY PERSISTS** |
| X3-64223 | X3 | — | — | — | No position |
| X3-68968 | X3 | — | 0 | 2036-03-11 | Inactive (744h) |
| X3-69172 | X3 | — | — | — | No position |
| X3-78553 | X3 | — | — | — | No position |
---
## 5. Live Position Coverage
**19 of 63 devices (30%)** have a position in `live_positions` — same count as 260410.
**44 devices (70%)** have no position at all — offline, SIM not installed, or never activated.
### Freshness Bands
| Band | Count | Devices |
|---|---|---|
| < 2 hours (active) | 2 | FRED KMGW 538W HULETI, X3-63282 |
| 2–24 hours (recent) | 2 | KDK 829A GP (2h), KMGR 409U HENRY JAZZ (7h) |
| 1–7 days (stale) | 2 | KCU 865Q Vanguard (79h), KDU 878T_Track (79h) |
| 1–12 months (very stale) | 3 | JK Subaru KCS 903Y (670h), KCU 145Q (7,546h), Belta KCU-647D (7,584h) |
| > 1 year (inactive) | 10 | KDU 878T_CAM, KCS 903Y JK SUB, KCU 865Q Vanguard Sub, KMEH 692C KAWASAKI, KCE 690F, etc. |
### Full Live Position Detail
| Device | Model | Lat | Lng | Speed (km/h) | ACC | GPS Signal | Satellites | Last Fix (EAT) |
|---|---|---|---|---|---|---|---|---|
| FRED KMGW 538W HULETI | X3 | -1.24444 | 36.72321 | 0 | Off | 4 | 13 | 2026-04-12 00:02:54 |
| X3-63282 | X3 | 0.19566 | 32.54004 | 0 | Off | 4 | 11 | 2026-04-11 23:58:38 |
| KDK 829A GP | GT06E | -1.23850 | 36.72677 | 0 | Off | 4 | 9 | 2026-04-11 22:09:59 |
| KMGR 409U HENRY JAZZ | X3 | -1.23743 | 36.72663 | 3 | Off | 3 | 3 | 2026-04-11 16:47:20 |
| KCU 865Q Vanguard | GT06E | -1.23748 | 36.72641 | 5 | Off | 0 | 5 | 2026-04-08 17:17:45 |
| KDU 878T_Track | X3 | -1.23528 | 36.72871 | 0 | Off | 4 | 10 | 2026-04-08 17:16:55 |
| JK Subaru KCS 903Y | GT06E | -1.23560 | 36.72868 | 0 | Off | 1 | 6 | 2026-03-15 01:52:33 |
| X3-68968 | X3 | -1.23799 | 36.72615 | 0 | Off | 4 | 15 | 2026-03-11 23:59:28 |
| KDW 632M HL Tracker | X3 | -1.24087 | 36.72839 | 0 | Off | 4 | 6 | 2026-03-11 23:53:44 |
| JC400P-85751 | JC400P | -1.23796 | 36.72611 | 0 | Off | 4 | 15 | 2026-03-11 22:15:44 |
| KDW 632M HL Cam | JC400P | -1.24115 | 36.72847 | 0 | Off | 4 | 0 | 2026-03-11 11:52:01 |
| AT4-64815 | AT4 | -1.24136 | 36.72872 | 0 | Off | 4 | 4 | 2026-02-05 11:19:55 |
| KDU 878T_CAM | JC400P | -1.06900 | 37.01436 | 12 | Off | 4 | 15 | 2025-12-04 15:27:42 |
| KCU 145Q Solo Xtrail | GT06E | -1.29728 | 36.88850 | 0 | Off | 4 | 7 | 2025-06-01 14:04:47 |
| Belta KCU-647D | GT06E | -1.15151 | 36.63857 | 0 | Off | 4 | 11 | 2025-05-30 23:53:22 |
| KCS 903Y JK SUB | AT4 | -1.23529 | 36.72875 | 0 | Off | 4 | 3 | 2024-07-16 10:41:42 |
| KCU 865Q Vanguard Sub | AT4 | -1.23522 | 36.73104 | 0 | Off | 4 | 5 | 2024-07-07 10:43:21 |
| KMEH 692C KAWASAKI | AT4 | -1.23849 | 36.72460 | 0 | Off | 4 | 11 | 2023-06-17 10:41:18 |
| KCE 690F | AT4 | -1.24008 | 36.74522 | 31 | Off | 4 | 6 | 2019-09-27 07:20:08 |
---
## 6. Geographic Clustering
| Cluster | Area | Coords | Active Devices | Δ vs 260410 |
|---|---|---|---|---|
| **Primary depot** | Nairobi West / Kikuyu Rd corridor | -1.235 to -1.244, 36.722 to 36.731 | 14 devices | KDK 829A GP moved here from secondary cluster |
| **Secondary** | Nairobi East / Thika Rd | -1.297, 36.888 | 1 device | KDK 829A GP departed — now only KCU 145Q Solo (stale) |
| **Outlier** | Thika / Ruiru | -1.069, 37.014 | 1 device (KDU 878T_CAM) | Unchanged |
| **CRITICAL** | **Uganda — Kampala region** | **0.196, 32.540** | **1 device (X3-63282)** | **Persists — no change** |
> **KDK 829A GP position change confirmed:** was at -1.328, 36.900 (Nairobi East) in the 260410 report; now at -1.238, 36.727 (primary depot). Vehicle drove from the secondary cluster to the main yard between the two report windows.
---
## 7. Position History
**Total fixes: 101** across two ingestion sources (new container; accumulating since 22:25 EAT Apr 11):
| Source | Fixes | Method | Frequency |
|---|---|---|---|
| `poll` | 31 | Fleet-wide 60s sweep | Every 60 seconds |
| `track_list` | 70 | Per-device high-res trail (POLL-01) | Every 30 minutes |
| **Total** | **101** | | |
### Per-Device Fixes — Last 24 Hours
| Device | Model | Source | Fixes | First Fix (EAT) | Last Fix (EAT) | Avg Speed | Max Speed |
|---|---|---|---|---|---|---|---|
| FRED KMGW 538W HULETI | X3 | track_list | 69 | 2026-04-11 21:52:44 | 2026-04-11 23:50:12 | 18.1 km/h | 53 km/h |
| FRED KMGW 538W HULETI | X3 | poll | 7 | 2026-04-11 22:25:12 | 2026-04-12 00:02:54 | 5.0 km/h | 35 km/h |
| X3-63282 | X3 | poll | 4 | 2026-04-11 22:13:38 | 2026-04-11 23:58:38 | 0.0 km/h | 0 km/h |
| X3-63282 | X3 | track_list | 1 | 2026-04-11 21:58:38 | 2026-04-11 21:58:38 | 0.0 km/h | 0 km/h |
| KDK 829A GP | GT06E | poll | 1 | 2026-04-11 22:09:59 | 2026-04-11 22:09:59 | 0.0 km/h | 0 km/h |
| KMGR 409U HENRY JAZZ | X3 | poll | 1 | 2026-04-11 16:47:20 | 2026-04-11 16:47:20 | 3.0 km/h | 3 km/h |
> **FRED KMGW 538W HULETI** generated 69 high-resolution track_list waypoints with avg 18.1 km/h and peak 53 km/h — confirming real road movement during the evening. This is the first active driving data since pipeline deployment.
---
## 8. Alarms
**Total alarms: 3** — all on FRED KMGW 538W HULETI, corresponding to evening trips.
| # | Device | Alarm Type | Alarm Name | Time (EAT) | Lat | Lng | Speed |
|---|---|---|---|---|---|---|---|
| 1 | FRED KMGW 538W HULETI | ACC_ON | ACC ON | 2026-04-11 22:07:27 | -1.23950 | 36.73979 | 0 |
| 2 | FRED KMGW 538W HULETI | ACC_OFF | ACC OFF | 2026-04-11 22:28:23 | -1.24441 | 36.72324 | 0 |
| 3 | FRED KMGW 538W HULETI | ACC_OFF | ACC OFF | 2026-04-11 23:35:13 | -1.24428 | 36.72300 | 0 |
**Key findings:**
- ACC_ON at 22:07 → vehicle started; ACC_OFF at 22:28 → parked briefly; ACC_OFF at 23:35 → final park. Consistent with the 3 trips recorded.
- ACC_ON event at -1.23950, 36.73979 — slightly east of primary depot, consistent with trip 3 start coordinates.
- No vibration alerts this window (2 vibration alerts overnight on 260410). Quieter night.
- No speeding, geofence, or power alarms.
---
## 9. Trips
**Trips recorded: 3** — all FRED KMGW 538W HULETI on the evening of 2026-04-11.
> **FIX-M16 confirmed working:** distances are physically consistent with duration and speed.
| # | Device | Start (EAT) | End (EAT) | Distance (km) | Drive Time (s) | Implied Speed | Avg Speed (API) |
|---|---|---|---|---|---|---|---|
| 1 | FRED KMGW 538W HULETI | 21:47:05 | 21:49:44 | 1.430 km | 159s | 32.4 km/h | 32.41 km/h ✓ |
| 2 | FRED KMGW 538W HULETI | 23:13:05 | 23:20:22 | 2.600 km | 437s | 21.4 km/h | 21.38 km/h ✓ |
| 3 | FRED KMGW 538W HULETI | 23:27:36 | 23:35:13 | 2.910 km | 457s | 22.9 km/h | 22.93 km/h ✓ |
| **Total** | | | | **6.940 km** | **1,053s (17.6 min)** | | |
**Notes:**
- Pre-fix, these trips were stored as 1,432 km / 2,596 km / 2,910 km — corrected in-place by DB update and code fix deployed.
- `max_speed_kmh` not yet populated for these trips — API field `maxSpeed` not returned by `jimi.device.track.mileage` for this device/window.
- Short urban trips (1.4–2.9 km) at 21–32 km/h — consistent with Nairobi city driving near the Kikuyu Rd depot.
---
## 10. Parking Events
**Parking events: 0**
POLL-02 fix deployed (`acc_type=0`, corrected `durSecond` mapping). API responding cleanly (14 calls, 0 rows). Events will populate once a complete park-stop-drive cycle is observed by the poller window.
---
## 11. Ingestion Pipeline Health
Container uptime: ~1h 50min at time of queries (restarted 22:25 EAT Apr 11 for FIX-M16 deployment).
| Endpoint | Calls | Rows Upserted | Rows Inserted | Avg Duration | Failures | First Call (EAT) | Last Call (EAT) |
|---|---|---|---|---|---|---|---|
| `jimi.user.device.location.list` | 21 | 399 | 399 | 575ms | 0 | 2026-04-11 22:25:45 | 2026-04-12 00:10:58 |
| `jimi.open.platform.report.parking` | 14 | 0 | 0 | 10,863ms | 0 | 2026-04-11 22:25:55 | 2026-04-12 00:04:34 |
| `jimi.device.track.list` | 4 | 0 | 77 | 237,175ms | 0 | 2026-04-11 22:26:10 | 2026-04-12 00:04:41 |
| `jimi.device.alarm.list` | 3 | 0 | 4 | 344ms | 0 | 2026-04-11 22:30:39 | 2026-04-12 00:04:34 |
| `jimi.user.device.list+detail` | 2 | 126 | 0 | 5,768ms | 0 | 2026-04-11 22:25:39 | 2026-04-12 00:04:12 |
| **Total** | **44** | | | | **0** | | |
**Observations:**
- **Zero failures across all 44 API calls** — pipeline stable after restart.
- Location polling: 21 calls, 575ms avg — consistent with 260410 (493ms). Slightly slower, within normal variance.
- Track list: 4 calls, 77 waypoints at 237s avg — slower per call than 260410 (137s). Likely due to FRED KMGW 538W HULETI generating dense waypoints during active driving.
- Alarm poll: 344ms avg — fast and clean.
- Device sync: 2 runs since restart; all 63 device records updated with full field sync (FIX-M17 now active).
---
## 12. Changes Since 260410 Baseline
| Area | 260410 | 260412 | Assessment |
|---|---|---|---|
| Trips recorded | 0 | **3** | First real trip data — pipeline validated end-to-end |
| Trip distance accuracy | Broken (km stored as m) | **Fixed (FIX-M16)** | Implied speed matches API avgSpeed exactly |
| `sync_devices` ON CONFLICT | 5 fields only | **26 fields** (FIX-M17) | Driver/phone/SIM will now update on each daily sync |
| `track_list` fixes (24h) | 13 | **70** | FRED KMGW 538W HULETI drove → dense trail captured |
| FRED KMGW 538W HULETI | Parked at depot | **Active — 6.94 km driven** | First confirmed driving data |
| KDK 829A GP | Secondary cluster (Nairobi East) | **Primary depot** | Returned to main yard overnight |
| `sync_driver_audit.py` | Not present | **Added** | One-shot tool for API↔DB driver/IMEI gap reporting |
| Driver names in DB | 0 | **0** | Root cause confirmed: not assigned in Tracksolid Pro UI |
| Uganda anomaly (X3-63282) | Active at 0.196, 32.540 | **Persists — no change** | Requires investigation |
| Service flags | Belta KCU-647D (234,546 km), KDK 829A GP (239,264 km) | **Same — odometers approaching 240k** | Maintenance overdue |
---
## 13. Open Items
| Priority | Item | Owner |
|---|---|---|
| HIGH | Assign driver names + vehicle numbers in Tracksolid Pro UI | Operations |
| HIGH | Investigate X3-63282 in Uganda (Kampala region) — legitimate deployment or stolen? | Management |
| HIGH | Service KDK 829A GP (239,264 km) and Belta KCU-647D (235,000 km) | Fleet maintenance |
| MEDIUM | Register webhooks in Tracksolid Pro: `/pushobd`, `/pushoil`, `/pushtem`, `/pushlbs`, `/pushevent` | DevOps |
| MEDIUM | Set `fuel_100km` per vehicle type to activate fuel cost analytics | Operations |
| MEDIUM | Investigate 44 devices with no GPS fix — deployed? SIM installed? | Fleet ops |
| LOW | Define geofences — depot boundary, approved route corridors | Operations |
| LOW | Run nightly ETL: `SELECT dwh_gold.refresh_daily_metrics(CURRENT_DATE - 1)` | DevOps |
---
*Report generated: 2026-04-12 ~00:15 EAT · Stack: TimescaleDB 2.15 + PostGIS + Tracksolid Pro Open Platform API*
*Pipeline: `ingest_movement_rev.py` v2.2 (FIX-M16, FIX-M17) · `ingest_events_rev.py` · `webhook_receiver_rev.py`*
Reference in a new issue View git blame Copy permalink