c05b47abe2
BUG-01 [FIX-E06]: jimi.device.alarm.list poll response uses alertTypeId/ alarmTypeName/alertTime, not the webhook field names. All 1,054 stored alarm records had null alarm_type/alarm_name as a result. Corrected field mapping in ingest_events_rev.py; also added alarm_name and source columns to INSERT. BUG-02 [FIX-M11/M12]: trips.distance_m was storing millimetres due to an erroneous * 1000 on an already-km API value. Removed the multiplication in poll_trips() and push_trip_report(). Column renamed to distance_km in migration 04 (historical rows divided by 1,000,000 to correct to km). All SQL in both ingestion files updated to reference distance_km. POLL-02 [FIX-M13]: parking poll returned 0 rows because the required account and acc_type=0 parameters were missing. Also fixed response field mapping: durSecond was incorrectly read as 'seconds'. Migration 04: corrects and renames distance_m → distance_km. Migration 05: adds normalized OBD columns, alarm/device enrichment columns, new tables (device_events, fuel_readings, temperature_readings, lbs_readings, geofences), expands dwh_gold fact table, and adds refresh_daily_metrics() ETL. tracksolid_DB_manual.md updated to reflect column rename and mark fixed issues. Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
36 lines
1.9 KiB
PL/PgSQL
36 lines
1.9 KiB
PL/PgSQL
-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
|
||
-- Migration 04 — Bug Fix: distance unit correction + column rename
|
||
-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
|
||
-- BUG-02: tracksolid.trips.distance_m was storing millimetres not metres.
|
||
--
|
||
-- Root cause: ingestion code applied `km * 1000` but the API already returns
|
||
-- values in km, producing mm. Confirmed by cross-checking stored values against
|
||
-- avg_speed_kmh × driving_time_s — e.g. 4,203,000 stored for a 4.203 km trip.
|
||
--
|
||
-- Fix applied here:
|
||
-- 1. Divide all existing rows by 1,000,000 to convert mm → km.
|
||
-- 2. Rename the column to distance_km to eliminate future ambiguity.
|
||
--
|
||
-- Corresponding code fix: removed * 1000 from poll_trips() in
|
||
-- ingest_movement_rev.py and push_trip_report() in webhook_receiver_rev.py.
|
||
-- Both now store the raw API value directly as km.
|
||
--
|
||
-- Run once against tracksolid_db before deploying updated ingestion containers.
|
||
-- ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
|
||
|
||
BEGIN;
|
||
|
||
-- Step 1: Correct all existing stored values (mm → km)
|
||
UPDATE tracksolid.trips
|
||
SET distance_m = distance_m / 1000000.0
|
||
WHERE distance_m IS NOT NULL;
|
||
|
||
-- Step 2: Rename column
|
||
ALTER TABLE tracksolid.trips
|
||
RENAME COLUMN distance_m TO distance_km;
|
||
|
||
-- Step 3: Update column comment
|
||
COMMENT ON COLUMN tracksolid.trips.distance_km
|
||
IS 'Trip distance in kilometres. Corrected from mm storage on migration 04 (2026-04-10).';
|
||
|
||
COMMIT;
|