Route + driver-aware fuel-consumption norm-control layer over the Atrax GPS/fuel stream.
Tracked in #6194 (follow-up of the design on #6138). This is the parallel, richer
route+driver layer on top of the vehicle-only Track A/C tables — it coexists with them, it
does not replace them.
This doc covers the Lane D + Lane E pieces (the nightly Python jobs and the Parquet
export/retention extension). The DB foundation (Lane B + Lane C — five tables, two views,
RLS/grants) shipped in **PR 6198. Both sys-admin handoffs are now done: the append-only n8n
INSERT node (Lane B) via pr #6554, and cron scheduling of the three norm jobs on bms-4 via
PR #6568 (this doc). The Parquet export + retention prune is scheduled separately on vps-i1
(#6570 — see Nightly schedule below).
Nightly RUNTIME order is strictly sequential: trip-builder → route-norm-compute →
route-rolling-avg → route-anomaly-daily → export. Only the coding was parallelised; the run
order is not. (route-rolling-avg reads only p24_gps_trips, so it is independent of the anomaly
step — placed after route-norm-compute per the #6777 spec.)
All jobs are rule-based statistics (median/MAD + fixed thresholds), not ML — no
dev_r_ai_systems registration. Because attribution='driver_pattern' names a person, outputs
are advisory/investigatory only (dispatcher alerting) — never wire them into an automated
disciplinary/performance system without a fresh compliance review.
The nightly jobs (scripts/nightly-etl/)
Shared plumbing (env loading, PostgREST read/write, Europe/Berlin day boundaries, the Discord
GH-issue Error Notification Standard) lives in atrax_norm_common.py. Each job filters to
vehicles active that day (never a blind loop over the full ~263 fleet) and isolates
per-vehicle errors (one malformed vehicle is logged + skipped, never aborts the night).
Day boundary is Europe/Berlin everywhere (matches the existing compute_yesterday
convention). Each job defaults to “yesterday (Berlin)” and takes --date YYYY-MM-DD +
--dry-run.
Snapshot unit conventions (confirmed live 2026-09-01, #6707)
p24_gps_fuel_snapshots stores raw device fields whose units are not what an early read
assumed. Getting these wrong produced physically-impossible output (l/100km up to ~2,000,000;
unexplained tank drops of 3342 L/day). Confirmed against real rows:
Field
Unit
How confirmed
distance_total
kilometres (lifetime odometer)
top value 1,080,953 → 1.08 M km; metres would mean a 1080 km lifetime (impossible). Matches Atrax firstOdometer/lastOdometer (km) per docs/playbooks/atrax-request-signing.md §2.
fuel_counter
decilitres (tenths of a litre, lifetime)
matches p24_gps_daily_stats.fuel_used_liters = (counter_end−counter_start)/10; a trip’s counter Δ1200 (=120 L) matched a same-day tank-level drop of ~111 L.
fuel_level
litres (current tank contents)
global max 1160 == largest p24_l_cars_atrax.fuel_tank_capacity (1160); values > 100 rule out percent.
So: distance_km = Δdistance_total (no /1000); fuel_burned_L = Δfuel_counter / 10;
tank drop is summed litre decreases of fuel_level (never × capacity/100). A permanent
data-quality guardrail (atrax_route_anomaly_daily.is_plausible_l100km, band 0–200 l/100km,
and unexplained_liters > fuel_tank_capacity) drops measurement artefacts with a WARN instead of
escalating them — GPS/sensor noise will always exist, so this stays useful beyond the original bug.
One-off backfill after a unit fix (backfill_atrax_units_6707.py)
Fixing the scripts only corrects future nightly runs; already-stored trips/norms/anomalies keep
their corrupted values. The whole affected window (2026-08-20..08-31) is inside the 35-day snapshot
retention, so the backfill just re-runs the fixed pipeline per day (re-deriving distance from the
odometer) rather than applying a correction factor:
# DRY-RUN first (writes nothing):python3 /opt/p24-infra/scripts/nightly-etl/backfill_atrax_units_6707.py --env-file /opt/p24-infra/bms-4/.env# APPLY once — ONLY after the fixed scripts are live on the bms-4 ETL host (git-sync of main),# else that night's 02:15 run re-corrupts the day with the old code:python3 /opt/p24-infra/scripts/nightly-etl/backfill_atrax_units_6707.py --apply --env-file /opt/p24-infra/bms-4/.env
It rebuilds in order: trips (per day) → clear anomalies → clear norm → norm-compute (per day) →
anomaly-daily (per day). Anomalies are cleared before norm-compute so its escalate-exclusion is
not driven by the poisoned rows. Idempotent and re-runnable.
Segments contiguous ignition=true runs into trips. A stop is confirmed only by ≥2
consecutive off snapshots (~20 min) — a single off blip is sensor flapping and does not
split. A data gap ≤30 min with ignition true on both sides stays one trip (Atrax
blackout); a longer gap splits. trip_date = the trip start day (Berlin); the read window
looks back 12 h before Berlin-midnight so an evening trip is not clipped. Multi-driver trips
get driver_id NULL, multi_driver=true. Idempotent: clears the target date’s trips before
re-inserting.
median + MAD per (vehicle, route) (aggregate, driver_id NULL, always) and per
(vehicle, route, driver) where a driver has ≥5 trips on that exact vehicle+route.
robust_sigma = 1.4826 × max(MAD, 0.3). Trips previously flagged severity='escalate'
(consumption_deviation) are excluded from the recompute population (circularity guard);
watch-flagged trips stay in. Recomputes all routes of each active vehicle from full history
and replaces that vehicle’s norm rows (idempotent). p24_route_labels refresh (every route
with ≥5 trips, auto-generated placeholder label) is a non-fatal step that runs only after
the norm write committed — it can never fail norm-compute or block the anomaly job.
There is no rename mechanism for route labels yet (no UI) — every label starts
auto-generated (Route near {lat},{lon}). Named-zone routing is a separate future issue
(needs a real geofence table).
Time-of-day route norm (#7287)
A single (vehicle, route) median/MAD mixes every departure time of day into one norm, so a
route consistently driven in rush-hour traffic (slower, more stop/start → higher l/100km) can look
like a fuel deviation when it is just congestion. For a route with real coverage in ≥2 time-of-day
windows (each with ≥5 trips), atrax_route_norm_compute.py now also emits a separate
aggregate norm row per window (still driver_id NULL, with time_bucket set); the daily detector
scores each trip against its own window’s norm when one exists, else the combined per-route norm.
Windows (from the trip’s departure hour, start_ts in Berlin local time; defined once in
atrax_norm_common.time_bucket_of, shared with ecodriving #7292):
AM_PEAK 06–10 · MIDDAY 10–16 · PM_PEAK 16–20 · OFFPEAK_NIGHT 20–06.
No-op for ~92% of pairs: a route driven in only one window (feasibility check on #7287: the
large majority) produces no per-window rows, so scoring falls back to the combined norm —
bit-identical to the pre-#7287 behaviour. The split only bites on the ~8% of pairs actually driven
across multiple times of day.
Scope: aggregate rows only. Driver slices stay combined (time_bucket NULL) — per-driver
samples are too sparse to split further. baseline_drift and the fraud/leak check also stay on the
combined norm.
Schema:p24_route_consumption_norm.time_bucket text (NULL = combined). ux_route_norm_agg
keys on (vehicle_id, route_id, COALESCE(time_bucket,'')) so one combined row + one row per window
coexist. p24_v_route_performance_report’s join is pinned to time_bucket IS NULL so the report
reads only the combined norm (migration 20260929160846_route_norm_time_bucket_7287.sql).
Six plain arithmetic-mean (NOT median/MAD) l/100km metrics computed alongside the median
norm — additive trend/context signals (“is this driver/vehicle trending better or worse than its own
long-run baseline”), never a replacement for the anomaly-detection median. One table,
p24_consumption_rolling_averages, one row per (grouping key, window_type):
#
window_type
scope (grouping key)
window
1
driver_last_10_days
driver_id only (any vehicle/route)
time: trip_date ≥ target − 10d
2
route_last_10_trips
vehicle_id + route_id (any driver)
count: most recent 10 trips
3
route_driver_last_10_trips
vehicle_id + route_id + driver_id
count: most recent 10 trips
4
vehicle_last_1000km
vehicle_id only
dist: most recent ~1000 km
5
vehicle_prev_1000km
vehicle_id only
dist: the 1000 km before #4
6
vehicle_prev_20000km
vehicle_id only
dist: independent most-recent ~20000 km
Storage choice. A new table rather than an extension of p24_route_consumption_norm: the six
metrics have genuinely different grouping keys (driver-only, vehicle+route, vehicle-only) and none
fit the norm table’s fixed vehicle+route[+driver], full-history shape. The grouping columns are
nullable per-metric (vehicle_id NULL only for #1; route_id NULL for 6; driver_id
NULL for 6), so the unique key is UNIQUE NULLS NOT DISTINCT (vehicle_id, route_id, driver_id, window_type) — a plain UNIQUE treats NULLs as distinct and would not dedupe. RLS grants
authenticated SELECT from day one (the gap #6751 fixed retroactively on the norm/anomaly
tables), plus the usual grafana_readonly/service_role.
Distance windows (#4/#5/#6). Walk the vehicle’s trips newest-first accumulating distance_km
until the target km is reached; the crossing trip is included, so the actual covered distance
usually slightly overshoots the target — sample_distance_km records the actual accumulated
km (no fractional trip splitting). #5 starts exactly where #4 stopped (offset trend pair). #6 is
independent — it starts from the newest trip again and therefore overlaps5; it is a
long-run baseline to compare the short-term #4 against, not a window chained past #5. Despite the
prev in its name it is the most-recent ~20000 km, not an offset window. (This overlap-not-offset
call was the one ambiguous point in the spec — confirmed with the user 2026-09-02, #6777.)
Only l100km-valid trips are used anywhere (matching route-norm-compute) — a fuel-counter-reset
trip has no consumption to average and is excluded from both the mean and the distance walk, so
sample_distance_km always matches the trips actually averaged. Active-population-only: the vehicle
loop is driven by vehicles active on the target day, the driver loop by drivers active that day.
Replace-all idempotency per vehicle (metrics #2–#6) and per driver (metric #1). Partial windows
(fewer trips than the target km) are emitted with the actual sample_distance_km. Dashboard display
of these averages is a separate dependent et-op issue — out of scope here.
Scores each day’s trips with a sample-size-gated severity (classify_severity, design
§2 table); only watch/escalate rows are persisted. Each trip is scored against its own
time-of-day window norm when the route was split for that window (#7287, see above), else the
combined per-route norm; p90 corroboration uses the matching window’s population. Also emits two
population/day-level signals:
baseline_drift (trip_id NULL): a long-horizon anchor from the earliest ≥15 trips
vs the rolling norm median; >15 % drift escalates (boiling-frog guard). The anchor is derived
on demand from p24_gps_trips (retained in full), so no anchor column is needed.
fuel_theft_or_leak (trip_id NULL, signal_source='diy_snapshot', severity watch):
a DIY snapshot cross-check — unexplained tank drop vs ECU-measured burn per vehicle-day.
attribution='driver_pattern' (attribution_confidence='high') only when a driver-specific
baseline on the same vehicle+route exists and is itself elevated vs the aggregate; otherwise
vehicle_route. Cold-start fallback (§3): when a driver has no same-vehicle+route baseline
yet, they are still checked across every route they’ve driven on that vehicle via a
leave-one-driver-out deviation ratio — each historical trip normalised by the median of the
other drivers’ trips on its route (so the denominator is never built from the driver’s own
trips; needs ≥5 other-driver trips per route and ≥5 usable ratios total). A cross-route median
ratio > 1.0 tags driver_pattern at attribution_confidence='low'. Idempotent: replaces the
target day’s anomaly rows (prior days’ escalations preserved, so the exclusion above holds).
Manufacturer cold-start baseline (#7049)
A (vehicle, route) with no empirical norm yet used to be skipped entirely, so a brand-new
vehicle or route was blind until it accumulated history. When the vehicle carries a per-vehicle
manufacturer figure (p24_l_cars_atrax.standard_fuel_consumption) the trip is now scored against
it as a provisional baseline from day one — deliberately at lower confidence/severity than
an empirical norm:
norm_source='manufacturer_baseline' (vs 'empirical') — a dedicated column, orthogonal to
signal_source, so the provisional origin is never mixed into one statistic with the empirical
rows. It is exposed on p24_v_route_anomalies_report for dashboard filtering.
attribution_confidence='low' and severity capped at watch — a manufacturer figure is
a single point, not a fleet-observed distribution, so it never escalates on its own (advisory,
same EU-AI-Act guardrail as the §3 cross-route hint). Scoring is a flat percent-over-norm
(mad_l100km/modified_z are NULL, sample_trips=0). No theft branch on this path.
The empirical norm supersedes the manufacturer baseline the moment the route accrues enough
history (agg_norms is checked first; the manufacturer path is the else).
Threshold — MANUFACTURER_PCT_WATCH = 30 % over the manufacturer figure. Justified by the
Etap-1 live-data pass (2026-09-15): 48/264 vehicles carry a value, dominated by one fleet-generic
figure (26.5), and where a vehicle also has an empirical route norm the empirical median runs
~0.91× the manufacturer value — i.e. real-world consumption is usually slightly below the
manufacturer figure, so a trip exceeding it by ≥30 % is a genuine signal. Round, human-tunable
starting value (this codebase’s convention of shipping tunable, not derived, thresholds);
recalibrate against the labelled false-positive rate (#6678). is_usable_manufacturer_norm() bins
out NULL/0/garbage before scoring, so a missing or implausible figure degrades gracefully to
the prior cold-start skip.
Lane E — alerting (REMOVED in #7153; superseded by fleet-anomaly escalation)
Historical. Lane E staged every watch/escalate finding into dev_r_alert_events via
scripts/alert-ingest.py (source atrax-route-anomaly), folding a run into one alert-triage-batch
GitHub issue. #7153 (plan-7107 PR-1) removed this path. Findings now escalate into the shared
p24_fleet_anomaly_escalations table (source_system fuel_consumption) with a shared Discord digest
— fleet managers triage in the et-operational-platform panel, not GitHub issues. The
atrax-route-anomaly entry was removed from VALID_SOURCE in both scripts/alert-ingest.py and
infra-src/meta-dispatcher/src/alert-ingest.ts; dev_r_alert_events / alert-triage-batch stay for
genuine infra alerts only. The classification (native_fraud_flag / reconcile_fraud) is unchanged —
only the delivery moved. See the “Fleet-anomaly escalation (#7153)” section below and
docs/atrax-fleet-anomaly-escalation-operations.md.
The old admitting migration 20260813041441_alert_source_atrax_route_anomaly_6194.sql is left in
place (historical); the source value it inserted is simply no longer produced.
Lane E — Parquet export + snapshot retention (export_atrax.py)
The nightly export now also writes p24_gps_fuel_snapshots (one UTC day per file, timestamptz
range), p24_gps_trips, p24_route_consumption_anomalies (per-day), and
p24_route_consumption_norm (full daily snapshot) to
s3://p24-infra/reporting/atrax/{table}/{date}.parquet. All four are optional — absent /
empty until their producers ship is a warning, not a failure.
Snapshot retention (--prune-snapshots, default 35 days):p24_gps_fuel_snapshots grows
~1.1 M rows/month, so it is pruned. The prune archives the aged-out day to Parquet first,
then requires the exported row count to equal the live source count (fail-loud — a truncated
fetch aborts before any delete), then deletes exactly that day. Un-archived data is never
dropped; the delete is idempotent. Retention is skipped on any run where an export failed.
Sys-admin handoffs
Both handoffs cross the dev-coder/sys-admin boundary. Both are now done (#6194):
Lane B — n8n append-only INSERT node — DONE via #6548 / PR #6554. The snapshot branch
was re-targeted onto the live canonical producer atrax-kravag-scheduled-fleet-updates
(CCx9UMdphmGficDX, every 3 min) — not the orphaned fleet-update-v2-batch the original
PR #6275 assumed — and P24_GPS_SNAPSHOTS_ENABLED=true was set. p24_gps_fuel_snapshots has
accumulated ~1 row/vehicle every 3 min since 2026-08-20 06:58Z. Kill-switch ownership below.
Scheduled from the claude-runner user crontab on bms-4 — this host runs its p24-infra
nightly jobs from the user crontab (no /etc/cron.d; the worker has no sudo). One wrapper,
scripts/run-atrax-norm-nightly.sh, runs the norm jobs in strict sequential order:
# bms-4 claude-runner — 02:15 UTC (= 04:15 Berlin), after the 00:30 git-sync nightly.# "yesterday (Berlin)" closed at 22:00 UTC, so the target day is always complete.15 2 * * * /opt/p24-infra/scripts/run-atrax-norm-nightly.sh
The wrapper runs atrax_trip_builder → atrax_route_norm_compute → atrax_route_rolling_avg
→ atrax_route_anomaly_daily, each --env-file /opt/p24-infra/bms-4/.env, logging to
/var/log/p24-infra-workers/atrax-norm-nightly.log. All are Supabase-only and need no
object-store credentials.
The Parquet export + retention prune (export_atrax.py --prune-snapshots) is NOT on bms-4.
It uploads to the Wasabi reporting bucket, whose credential P24_INFRA_WASABI_ACCESS_KEY is
absent on bms-4 — the host’s generic WASABI_ACCESS_KEY is a different key and Wasabi rejects
it (InvalidAccessKeyId) against the reporting bucket. The export’s canonical home is vps-i1
(monitoring/.env, where the reporting creds and the sibling Track B export already live — see
atrax-analytics-etl-operations.md). Scheduling
export_atrax.py --prune-snapshots on vps-i1 (bundling Track B’s own still-unscheduled export) is
a separate infra-task follow-up → #6570. No snapshot is old enough to prune until ~2026-09-24,
so there is no urgency; the append-only table simply grows in Supabase until the vps-i1 export is
live.
Interpreter pin — /usr/bin/python3.12 is MANDATORY, not the default python3 (3.10).
bms-4’s default python3 is 3.10, whose datetime.fromisoformat() rejects the 2-digit
fractional-second timestamptz values Atrax emits (e.g. 2026-08-20T06:58:08.85+00:00). On 3.10
atrax_trip_builder’s parse_ts throws per-vehicle and silently drops ~80% of the fleet
(260 active → only ~53 processed). 3.12 parses them correctly (260 active, 0 skipped). The
wrapper hard-codes 3.12. Making parse_ts interpreter-independent is a tracked dev-coder
follow-up (#6567); until it lands, do not run these jobs under bare python3.
Host deps (claude-runner user site, 3.12):requests, boto3, certifi are already present;
pyarrow (Parquet, export_atrax only) was installed via
python3.12 -m ensurepip --user && python3.12 -m pip install --user 'pyarrow>=14,<21'
(the host’s system pip for 3.12 is broken — missing distutils). Re-run that if the user site is
ever rebuilt.
Kill-switch ownership (§5a-ops)
P24_GPS_SNAPSHOTS_ENABLED (the n8n snapshot-branch IF gate on
atrax-kravag-scheduled-fleet-updates) is toggled as an incident-response action on the
existing sys-admin/on-call path — not a scheduled handoff and not a new process. Flip it off if the
append-only INSERT ever threatens the live GPS upsert; the nightly chain then runs cleanly on
whatever snapshots already exist.
Verification / soft-launch
Dry-run each job (--dry-run --date <day>) against a few real days before scheduling.
Confirm the active-vehicles-only filter: a day with N active vehicles yields ≤ N processed
rows, not ~263.
After scheduling, watch 1–2 weeks of real anomaly rows before trusting escalation output
for dispatcher action. Then compute the real false-positive rate per severity tier and revisit
the fixed z-score / drift cutoffs (they were picked, not derived) — see §Threshold calibration.
Threshold calibration (#6678)
The severity cutoffs in atrax_route_anomaly_daily.py (classify_severity() z-scores
3.0/3.5/4.0 across the n-gated tiers, plus DRIFT_THRESHOLD = 0.15) were picked, not derived.
Calibrating them needs ground truth: dispatcher verdicts on whether a flagged trip was real.
Labeling (Part 1 — done, #6708): a dispatcher marks an anomaly confirmed / false_positive
via the review_route_anomaly RPC, surfaced as a write-back action in the
radieu/et-operational-platform#1849 dashboard. The verdict lands in
p24_route_consumption_anomalies.review_status (one-way, unreviewed → confirmed|false_positive).
Analysis (Part 2 — this tool): once a meaningful sample is labeled, run the one-off
calibration script by hand (it is not a scheduled job):
python3 scripts/nightly-etl/atrax_route_anomaly_calibrate.py --env-file /opt/p24-infra/bms-4/.env# machine-readable: add --json ; require more verdicts per tier: --min-labeled 30
It reports the false-positive rate per severity tier and per anomaly_type, then emits an
advisory recommendation (tighten / loosen / leave-as-is) per tier. It is guard-railed: a tier
with fewer than --min-labeled (default 20) verdicts gets no recommendation, and a run with zero
labels exits cleanly with “not possible yet” — it never calibrates on a tiny sample.
Applying a change stays a human decision: review the recommendation, then open a follow-up
PR adjusting the constants in atrax_route_anomaly_daily.py. The system is rule-based statistics,
not ML — this tool informs the human, it changes no cutoff on its own.
Current state (2026-09-01): the units fix + backfill (#6707) produced 217 real anomalies, but
all are unreviewed — zero dispatcher verdicts exist yet, so no calibration is possible. The
tool is ready; it needs labels to accumulate first.
Known limitations (documented, not fixed here)
MVP route_id (lat/lon @ 2 dp, directional) can split one physical route via parking-spot
wobble or merge two depots ~500 m–1 km apart — both revisited once a real geofence table
exists.
A low l/100km reading can come from odometer/GPS inflation (a real fraud vector), not just
efficient driving — it currently classifies marginal and is discarded (future Phase 9).
Atrax-native fraud (daily[].losses/frauds/refuelings) is Phase 7, gated on #5921’s
external-API access — the DIY proxy above is the only fraud signal until then.
Cross-references
DB foundation: PR #6198 · migration supabase/migrations/20260812110751_route_driver_fuel_norm_schema_6194.sql
Watch/escalate findings from this detector are escalated into the shared
p24_fleet_anomaly_escalations table (per-row upsert via atrax_norm_common.escalate_and_digest),
with a shared Discord digest for the NEW rows. Fleet managers triage in the et-operational-platform
escalation panel. Severity is normalized to warning/critical (plan §2). See
docs/atrax-fleet-anomaly-escalation-operations.md.
Known limitation (shared): a rerun that recomputes a row as no longer anomalous does not delete
an existing open escalation row — it must be dismissed by a human in the panel. The detection table
above keeps its own delete+reinsert-per-day behaviour and is unchanged.