The original refresh_daily_metrics() joined trips × alarms in one SELECT,
producing one row per (trip, alarm) pair. Every SUM/COUNT over trip
columns was multiplied by the per-IMEI alarm count, so spot-checks
showed total_trips identical to alarm_count, drive_hours > 1000/day,
and distance_km in the tens of thousands per vehicle per day.
Migration 08 carried that flawed join forward when fixing the
TEXT→INTEGER vehicle_key crash. Rewriting the function so trip_agg and
alarm_agg are computed in separate CTEs and then joined on imei
restores correct per-vehicle aggregates: total_trips reflects real trip
count, drive_hours ≤ 24, alarms are counted once.
This bug is being fixed in the same migration file (08) before PR #12
merges; no deploy has applied the prior version, so no second migration
is needed.
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
BUG-01 (CRITICAL): dwh_gold.refresh_daily_metrics inserted t.imei (TEXT) into
fact_daily_fleet_metrics.vehicle_key (INTEGER REFERENCES dim_vehicles), so the
nightly ETL would have raised "invalid input syntax for type integer" on every
run. Migration 08 backfills dim_vehicles from tracksolid.devices and rewrites
the function to JOIN through dim_vehicles, returning the serial vehicle_key.
The function also re-syncs dim_vehicles at the top of each call so newly
registered devices appear in the warehouse without manual seeding.
BUG-02 (HIGH): sync_driver_audit.py only queried TARGET_ACCOUNT, ignoring the
Fireside@HQ and Fireside_MSA sub-accounts. The audit now iterates TARGETS
(matching FIX-M19 in ingest_movement_rev.sync_devices), dedupes devices by
IMEI, and tolerates per-target failures.
BUG-03 (HIGH, diagnostic only): the webhook trip handler stores item["miles"]
straight into distance_km. The field name is suspicious and FIX-M16 already
proved the polling endpoint mis-documents its units. Added a SQL diagnostic
that compares the distribution of stored-km / great-circle-km for push-source
vs poll-source trips over 30 days — the ratio test will tell us whether the
push value needs a /1.609 (miles), /1000 (metres), or no conversion. The
existing calculation is left unchanged until the data confirms the unit; the
old FIX-M11 comment is replaced with a BUG-03 pointer to the diagnostic.
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
