kianiadee/tracksolid_timescale_grafana_prod
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions 1
tracksolid_timescale_grafan.../260427_audit_output.txt

75 lines
6.8 KiB
Text
Raw Permalink Normal View History

feat(analytics): Phase 0 — analytics-config migration and CSV importer rewrite Phase 0 of the three-stakeholder analytics redesign: - 08_analytics_config.sql: ops.cost_rates + ops.kpi_targets with seed fuel rates (KES 195/L NBO+MBA, UGX 5200/L KLA) and 6 seed KPI targets (utilisation_pct, idle_pct global+osp-patrol, fuel_kes_per_100km, mttr_hours, alarms_per_100km). Granted SELECT to grafana_ro. Wired into run_migrations.py MIGRATIONS. - import_drivers_csv.py: full rewrite for the new Mitieng CSV (20260427_FSG_Vehicles_mitieng.csv). Snake_case columns, drops _infer_city() plate-prefix logic in favour of reading assigned_city directly. Adds cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address. Treats the literal "NULL" string as missing. Reuses clean(), clean_num(), clean_ts(), get_conn(), get_logger() from ts_shared_rev. Special-cases numeric and timestamptz columns in the UPDATE clause. - audit_device_reconciliation.py: read-only audit comparing the CSV against tracksolid.devices. Reports per-account row counts, IMEIs on one side only, and devices on both sides whose metadata is still NULL. - 260427_device_reconciliation.md + 260427_audit_output.txt: Phase 0.2 reconciliation record. First run: DB has 172 devices, CSV has 162, delta +10 (10 IMEIs in DB-only, mostly fireside-account auto-syncs). Importer run with --only-null --apply filled 154 rows; coverage now assigned_city 152/172, cost_centre 150/172. Applied to stage on 2026-04-27 23:35 UTC. Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
2026-04-27 20:42:37 +00:00
============================================================================
Device reconciliation — CSV vs tracksolid.devices
============================================================================
CSV file : 20260427_FSG_Vehicles_mitieng.csv
CSV row count : 162
DB row count : 172
Delta (DB-CSV) : +10
─ Per-account breakdown ─────────────────────────────────────────────────
account CSV DB delta
(blank) 0 10 +10
Fireside@HQ 52 52 +0
Fireside_MSA 41 41 +0
NULL 6 0 -6
fireside 63 69 +6
─ IMEIs in DB but NOT in CSV (10) ─────────────────────────────
imei account city last_synced_at device_name
359857081891921 JK Subaru KCS 903Y
359857082898297 KDK 829A GP
862798052707995 fireside 2026-04-27 19:30:53.484218+00:00 JC400P-07995
862798052715071 KDU 878T_CAM
862798052785751 fireside 2026-04-27 19:30:53.484218+00:00 JC400P-85751
862798052786270 fireside 2026-04-27 19:30:53.484218+00:00 JC400P-86270
865135061040349 KDU 878T_Track
865135061559405 X3-59405
865135061569172 fireside 2026-04-27 19:30:53.484218+00:00 X3-69172
865135061569529 fireside 2026-04-27 19:30:53.484218+00:00 X3-69529
─ IMEIs in CSV but NOT in DB (0) ─────────────────────────────
(none — every CSV row has a corresponding device row)
─ Devices in both, but DB metadata still NULL (162) ──────────────
Likely cause: import_drivers_csv.py has not been re-run with --apply
against the new CSV, or rows had 'Identification' placeholders.
imei blank_fields
353549090553685 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
353549090561720 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address, driver_name, vehicle_number
353549090566281 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address, driver_name, vehicle_number
353549090566885 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address, driver_name, vehicle_number
353549090567685 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
353549090567701 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address, driver_name, vehicle_number
359857081885410 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857081886467 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, depot_address
359857081886871 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address, driver_name, vehicle_number
359857081886905 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857081887069 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857081887192 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857081891566 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857081891590 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857081891632 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857081891798 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857081892101 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857081892309 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857081892440 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address, driver_name, vehicle_number
359857081892762 assigned_city, cost_centre, assigned_route, vehicle_category, fuel_100km, depot_address, driver_name, vehicle_number
359857082037185 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857082038977 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857082040981 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857082042052 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857082042854 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857082042953 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address, driver_name, vehicle_number
359857082044280 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857082046145 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857082896911 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
359857082897091 assigned_city, cost_centre, assigned_route, vehicle_category, vehicle_brand, fuel_100km, depot_address
... and 132 more
─ Suggested next step ───────────────────────────────────────────────────
Inspect the IMEIs above. Decide one of:
(a) Prune — delete from tracksolid.devices if they are stale test/decommissioned units.
(b) Leave-as-NULL — keep them as auto-synced API rows; their metadata stays NULL until added to a future CSV.
(c) Addendum — add them to the CSV (or a sidecar CSV) and re-run import_drivers_csv.py --apply.
Document the choice in 260427_device_reconciliation.md.
Reference in a new issue Copy permalink