p24-infra Docs

6841-ecodriving-per-route-control

8 min read

Design: Ecodriving per-route control on Atrax data

Issue: #6841 · Type: design/plan spike Follow-up to: #6138 Track E (deferred design spike) — #6138 closed 2026-09-02 (merged via #6826). This design does not close #6138.

Discovery-gated design. The issue is explicitly two-staged: Etap 1 verifies the real shape of the data we already pull from Atrax; Etap 2 designs the analytics on top of it. Etap 1 requires decrypting Atrax credentials and calling the live Atrax API + reading live Supabase rows — a server-operations + credential task outside the dev-coder role scope. This document therefore:

  • fully specifies the Etap 1 discovery runbook (to be executed by a sys-admin/infra-task worker — see §5), and
  • designs Etap 2 conditionally on the three possible Etap 1 outcomes (§4).

The dev-coder deliverable of #6841 is this design + the runbook. The live Etap 1 execution and the subsequent implementation are separate, follow-up issues (§6).

1. Problem

We already run fuel-consumption norm control over Atrax data (per-vehicle + per-route deviation detection, p24_route_consumption_anomalies, attribution='driver_pattern' — see docs/atrax-route-norm-control-operations.md). The goal of #6841 is to determine whether the same comparative machinery can be extended to ecodriving — per vehicle, per route, comparing driving style across drivers and across days, flagging deviations — using only data Atrax already returns, with no new/additional report pulled from Atrax.

2. What is already known (research, this session — grounded in code)

#FactSource
1p24_driver_ecodriving_daily exists; unique index on (driver_id, atrax_car_id, stat_date). Fed by n8n atrax-ecodriving-daily (zdzyIQ8UBsDAF0wI, POST /api/statistics/daily/drivers). Issue lists its metric columns: eco_score, speeding count/duration, idle-fuel %, high-RPM-fuel %, max speed/RPM.supabase/migrations/20260806144718_atrax_daily_unique_indexes_5729.sql, docs/n8n-operations.md
2atrax-ecodriving-daily had never populated as of #6138 — blocked by request-signing (#5921, fixed 2026-08-12) then 3 n8n config bugs (fixed PR #6302). Current fill state must be re-verified live (Etap 1).docs/atrax-analytics-etl-operations.md §Hard dependency, docs/playbooks/atrax-request-signing.md
3/api/rides/summary (used by scripts/atrax-report-generator.py:288 and scripts/atrax-archival.py:330) sends "features": ["driving_state","standing_state"] — a list, strongly implying more feature codes are accepted than the two we use. Response is ridesRowsList[].scripts/atrax-report-generator.py:296, :310
4Our parser (_extract_entry, scripts/atrax-report-generator.py:402) reads from each entry only: groupID (DRIVING/STANDING), begin/end timestamps, x/y GPS, duration, and a heuristic location name (_LOCATION_NAME_FIELDS, ~17 candidate keys). All other keys of the raw entry are discarded — _resolve_location logs sorted(raw.keys()) once per run at WARNING on GPS-fallback, and nothing else. No per-event accel/brake/RPM field is read or persisted today.scripts/atrax-report-generator.py:350-384, :402-460
5A diagnostic dump procedure is already documented: temporary log.debug("raw entry: %s", raw) in _extract_entry + run with --dry-run.docs/scripts/atrax-report-generator.md §Troubleshooting
6Precedent for discovering undocumented Atrax API surface: fetch the public unauthenticated JS bundle https://tronik.atrax4.com/assets/j-atrax-*.js (~14 MB, no creds) and grep it for field/function names — this is how request-signing was reverse-engineered. Same technique finds the full features enum and the ridesRowsList model.docs/playbooks/atrax-request-signing.md:140-149
7Comparison machinery already exists for fuel: p24_route_consumption_anomalies (modified-Z / MAD vs a per-route norm, sample_trips >= 5 cold-start gate), leave-one-driver-out driver_pattern attribution with attribution_confidence high/low. Directly transferable to ecodriving.supabase/migrations/20260812110751_route_driver_fuel_norm_schema_6194.sql:123, scripts/nightly-etl/atrax_route_anomaly_daily.py:400-414
8Atrax has no native “route” entity (per #6138 Track E). Existing approach: derive a route from (start_zone, end_zone) pairs on already-pulled GPS zone data (current_zone_id, docs/playbooks/atrax-vehicle-tags-api.md), with a min-sample gate (≥5 trips) before trusting a route norm. p24_route_labels (route_id PK, sample_trips) already exists as the interim “searchable-by-route” table.supabase/migrations/20260812110751_...6194.sql:159 (p24_route_labels)

3. Design principle

Discover before designing persistence. The single unknown that determines the whole architecture is: does ridesRowsList (or another already-called endpoint) already carry per-event harsh-accel / harsh-braking / RPM / speeding data that we currently throw away? Nobody has ever dumped a full raw entry. Etap 1 answers that with a read-only diagnostic; Etap 2 forks on the answer.

4. Etap 2 — analytics design, conditional on Etap 1 outcome

Three mutually-exclusive branches. Etap 1 (§5) selects exactly one.

Branch A — raw per-event accel/brake/RPM exists in ridesRowsList

Best case: richest data, no new Atrax report.

  1. Persist the raw fields. Extend _extract_entry to read the confirmed keys and add a new table p24_ride_events (one row per ridesRowsList DRIVING entry), e.g.:

    columntypenote
    idbigint identity PK
    atrax_car_idtext NOT NULL
    driver_idtextmay be null if entry has no driver binding
    event_start / event_endtimestamptz NOT NULL / nullfrom begin/end
    start_zone_id / end_zone_idtextfor derived-route keying (fact 8)
    harsh_accel_count / harsh_brake_countintconfirmed field names from Etap 1
    max_rpm / high_rpm_duration_snumeric / int
    max_speed_kmh / speeding_duration_snumeric / int
    distance_km / duration_snumeric / int
    raw_extrajsonbcatch-all for other newly-discovered keys, so we never silently drop again
    created_attimestamptz default now()

    Unique index on (atrax_car_id, event_start); RLS = service_role FOR ALL + grafana_readonly SELECT (mirror p24_gps_daily_stats_native).

  2. Comparative analytics — reuse the fuel machinery verbatim. Add p24_ecodriving_anomalies modelled 1:1 on p24_route_consumption_anomalies: per (vehicle, derived-route, driver, trip_date), modified-Z vs a per-route ecodriving norm (harsh events / km, or a composite eco index), sample_trips >= 5 cold-start gate, attribution IN ('vehicle_route','driver_pattern') with leave-one-driver-out ratio and attribution_confidence high/low — identical structure to scripts/nightly-etl/atrax_route_anomaly_daily.py. Route derivation = (start_zone,end_zone) per fact 8; reuse p24_route_labels.

  3. ETL cadence. A nightly job analogous to atrax_route_anomaly_daily.py, reading p24_ride_events. No new Atrax calls beyond what atrax-report-generator / atrax-archival already make.

Branch B — raw per-event data does not exist in ridesRowsList, but features accepts a richer code

If Etap 1’s JS-bundle grep finds an additional features value (e.g. an eco/rpm/driving_style code) that returns per-event style data on the same /api/rides/summary endpoint: this is still “no new report” (same endpoint, richer feature flag). Design = Branch A, but Etap 1’s implementation first adds the confirmed feature code to the features list in both scripts and re-dumps to confirm the extra fields. Note the 5921 lesson: Atrax endpoints are picky about exact body field names and require request signing — budget for that.

Branch C — no per-event style data anywhere in already-called endpoints

Confirmed-negative outcome (still a valid design result). Then:

  1. Fall back to p24_driver_ecodriving_daily (the daily eco_score + speeding/idle/RPM aggregates, fact 1) as the only available ecodriving signal. Granularity is per vehicle+driver+day, not per-route — Atrax aggregates before we see it.
  2. Comparative analytics at daily granularity: compare eco_score (and its components) across drivers of the same vehicle and across days, using the same modified-Z/MAD + leave-one-driver-out attribution pattern, but keyed on (atrax_car_id, driver_id, stat_date) rather than a derived route. Per-route control is not achievable without raw events — document that as the confirmed answer to #6841’s core question.
  3. Optionally recommend requesting a genuinely new per-event report from Atrax as a separate business decision (out of “no new report” scope), or accept daily eco_score as sufficient.

EU AI Act note (all branches)

Any comparison that names a driver (attribution='driver_pattern', driver-level ecodriving ranking) is worker-performance data. p24_route_consumption_anomalies already handles this via dev_r_ai_systems registration (see docs/atrax-route-norm-control-operations.md §EU AI Act). Etap 2 implementation must register the ecodriving analytic the same way and reuse that guardrail — do not ship driver-level ranking without the registration.

5. Etap 1 — discovery runbook (execute as sys-admin / infra-task)

Read-only / diagnostic only. No writes to production or repo. No secret values in any output. Atrax credentials are key names only here; the executor decrypts them from secrets/monitoring.env.sops using the safe extraction pattern (grep → $VAR → use → unset; never echo).

5.1 Full raw-entry key dump (answers Branch A vs C)

  1. On a checkout, apply the documented temporary diagnostic (docs/scripts/atrax-report-generator.md §Troubleshooting): add log.debug("raw ridesRowsList entry: %s", raw) at the top of _extract_entry (scripts/atrax-report-generator.py:402) — a throwaway edit, never committed.
  2. Run scripts/atrax-report-generator.py --dry-run at DEBUG for one vehicle over a date range known to contain driving, e.g. a recent weekday. Capture one complete DRIVING entry and one STANDING entry.
  3. Record the complete key list of each (sorted(raw.keys())) into the Etap 1 findings comment on #6841. Flag any key suggesting accel/brake/RPM/speeding/coasting/cruise-control/eco.
  4. Revert the diagnostic edit.

5.2 features enum + ridesRowsList model discovery (answers Branch B)

  1. Fetch the public JS bundle (no credentials): find the current https://tronik.atrax4.com/assets/j-atrax-*.js from the app shell, download it (fact 6).
  2. Grep it for: driving_state, standing_state, features, ridesRowsList, and style terms (rpm, acceler, brak, harsh, eco, speeding, cruise, coast). Record the full set of features values and any ridesRowsList field model found.
  3. If a richer feature code exists, do a read-only single call to /api/rides/summary with it added to features (respecting request signing, docs/playbooks/atrax-request-signing.md) and dump the response keys — do not wire it into the scripts yet.

5.3 Live table-fill verification (answers “is the daily fallback real?”)

Read-only Supabase (PostgREST or execute_sql):

SELECT count(*) AS rows,
       min(stat_date) AS first_date, max(stat_date) AS last_date,
       count(DISTINCT driver_id) AS drivers, count(DISTINCT atrax_car_id) AS vehicles
FROM public.p24_driver_ecodriving_daily;
 
SELECT count(*), min(stat_date), max(stat_date) FROM public.p24_gps_daily_stats;

Record row counts + date ranges on #6841. Confirms whether Branch C’s fallback has usable history post-#5921/#6302.

5.4 Etap 1 deliverable

A single findings comment on #6841 stating: the two full key lists (5.1), the features enum + any richer model (5.2), the live table-fill numbers (5.3), and which branch (A/B/C) is selected. That comment unblocks the Etap 2 implementation issue (§6).

6. Sequencing / follow-ups

  1. This PR — commits this design; the dev-coder scope of #6841 is complete. Uses Partially implements: #6841 because Etap 1 live execution is delegated, not performed here.
  2. Etap 1 execution — a sys-admin/infra-task issue running §5 (needs Atrax creds + live API + live Supabase read). Its output is the branch-selection comment on #6841.
  3. Etap 2 implementation — a dev-issue implementing the selected branch (§4): schema migration, parser/ETL changes, dev_r_ai_systems registration, tests, ops doc. Scoped only after Etap 1.

No code path in this repo changes until §6.2 reports back. Nothing here touches secrets or production.

Graph View

Backlinks