Atrax driver utilization operations

Status: active · Owner: radieu · Project: p24-infra

Per-driver driving-hour utilization KPI: alongside tachograph violations, this reports how much of a driver’s legally-available driving time was actually used — an operational / fleet under-utilization signal, not compliance and not anomaly detection. It never writes to p24_fleet_anomaly_escalations (that escalation mechanism is #7107 PR-1); it is a stand-alone informational metric served read-only via report-query-api.

Shipped by #7154 (PR-3 of plan-7107 §10). Architecture pattern: p24_consumption_rolling_averages (#6777) — a dedicated table with a window_type discriminator, a standalone nightly ETL, and a SNAPSHOT registration in report-query-api. Not a SQL view and not aggregation inside the API.

Windows

window_type	Formula	Denominator source	Confidence
weekly	this_week_driving_min / this_week_limit_min	real Atrax counter	high
fortnightly	last_2w_driving_min / (last_2w_driving_min + last_2w_remaining_min)	real Atrax counters	high
daily	day_driving_min / 540	EU constant (assumption)	medium
monthly (MTD)	SUM(day_driving_min) / (days_with_real_row × 540)	EU constant × observed days	low (business KPI)

⚠️ Live-data reality (verified 2026-09-17) — only the weekly window populates today

A read-only --dry-run against the live p24_atrax_drivers_daily (whole table, 2026-08-08 → 2026-09-17, 10 538 rows) found that only the this_week_* counters are ever populated. Across the entire table:

Source column	Non-null rows	Window that depends on it
this_week_driving_min / this_week_limit_min	all	weekly ✅ populates
day_driving_min	0 (never)	daily ⇒ empty
last_2w_driving_min / last_2w_remaining_min	0 (never)	fortnightly, monthly ⇒ empty

So at the time of that observation the ETL wrote only weekly rows (≈255 drivers/day) — the issue’s verification target (this_week_driving_min / this_week_limit_min, high confidence). The daily, fortnightly, and monthly windows are fully implemented, unit-tested, and forward-compatible: the compute cleanly skips a window whose source counters are NULL (never writes a bogus row), so those windows begin populating automatically — no ETL code change needed — the moment p24_atrax_drivers_daily carries non-NULL day_driving_min / last_2w_driving_min / last_2w_remaining_min.

Root cause corrected (#7304 / #7388) — the gap was a map_drivers transform bug, NOT upstream Atrax. The earlier “upstream” diagnosis above was wrong. Atrax’s /api/rtdrivers does return the counters; the n8n map_drivers node was reading them under the wrong source keys (d.dayDrivingTime, d.last2WeeksDrivingTime, d.last2WeeksRemainingTime), which are undefined in the real payload, so the columns landed NULL. #7304 (PR #7308, merged 2026-09-29) fixed the mapping to the real Atrax field names — d.dayDriving, d.lastTwoWeeksDrivingTime, d.lastTwoWeeksRemainingDrivingTime (and d.thisWeekRemainingDrivingTime). The fix did not reach the scheduled runs: it was written by direct SQL into workflow_entity.nodes (the editor copy) only. n8n 2.x scheduled runs execute the active version in workflow_history, which kept the old keys. Every scheduled run up to 2026-10-05 04:00 UTC therefore still wrote NULL for these columns. See playbooks/n8n-queue-worker-restart.md.

Correction (2026-10-05). The earlier “verified live 2026-10-04” note was wrong. It relied on a manual run (557892), which reads the editor copy, and blamed stale queue-worker containers (#7388). The next scheduled run (560389) was NULL again. #7391’s “Schedule Trigger never fires” was the same versioning issue. On 2026-10-05 the editor copy was copied into the active workflow_history row and n8n was restarted. Verified with a scheduled run (561228, mode trigger): stat_date 2026-10-04 has 260/262 rows non-NULL in all four columns. The daily / fortnightly / monthly windows now populate with no further ETL work. (A per-day driving-limit field is genuinely absent from the Atrax payload, see Etap 1 below; that is unrelated to this bug.)

Etap 1 — daily-limit field check & the 540-minute assumption

The issue’s Etap 1 asks whether the raw Atrax /api/rtdrivers response carries a per-day driving limit that we don’t map today. Finding (confirms plan §10, no live call needed): the n8n map_drivers transform (n8n-workflows/atrax-drivers-daily-snapshot_akgnvueDkDPbLJ49.json) maps dayDrivingTime, thisWeek{Driving,Remaining,Limit}Time, last2Weeks{Driving,Remaining}Time, weeklyRest*, and the extended/shortenedRestPeriods counters — but no daily-limit field (dayLimitTime / dayRemainingTime). No raw sample payload is stored in the repo, and the dev-coder role has no live Atrax credentials to issue a fresh call.

Per the issue’s stated fallback, the daily (and, via the per-day constant, the monthly) denominator uses the EU regulatory constant DAILY_LIMIT_MIN = 540 minutes (Reg. (EC) 561/2006: 9 h standard daily driving time, extendable to 10 h/600 min at most twice a week). This is an explicitly-flagged assumption:

• It lives in one named module constant (atrax_driver_utilization_compute.DAILY_LIMIT_MIN).
• Every row records both the numerator (driving_min) and the denominator actually used (limit_min) plus a limit_basis discriminator, so a consumer can always recompute against a real limit if Atrax ever exposes one, without a schema change.
• A legal 10 h day scores >100 % against the 540 baseline — that is intended: it surfaces the (exceptional, twice-weekly) extended day rather than hiding it.

If a real per-day limit field is discovered later, add a new limit_basis value (e.g. atrax_day_limit) and switch the daily/monthly denominator to it — no migration needed (the column is a documented discriminator, not a constrained enum).

Table — p24_driver_utilization

Migration 20260918083312_p24_driver_utilization_7154.sql. One row per (driver_id, window_type), replaced each night (current-state snapshot).

Column	Meaning
driver_id	Atrax driver id
window_type	daily | weekly | fortnightly | monthly
period_start / period_end	window bounds (period_end target Berlin day; daily: both target)
driving_min	numerator — minutes driven in the window
limit_min	denominator — legally-available driving minutes in the window
utilization_pct	round(driving_min / limit_min × 100, 2)
limit_basis	how limit_min was derived: atrax_this_week_limit | atrax_2w_driving_plus_remain | eu_daily_9h_constant | observed_days_x_eu_daily
confidence	high | medium | low (per the windows table above)
sample_days	days with a real row contributing (meaningful for monthly)
computed_at	run timestamp

RLS: service_role FOR ALL; grafana_readonly FOR SELECT (policy and GRANT, #4645); authenticated FOR SELECT (from creation, #6751); anon nothing.

Monthly denominator counts reported days, not calendar days

p24_atrax_drivers_daily does not backfill gaps — a missing day is no data, not zero driving. So the monthly (month-to-date) denominator is days_with_real_row × 540, where days_with_real_row is the count of days in the calendar month (up to the target day) that actually have a drivers-daily row. Using calendar days would understate utilization for partially-reported months.

ETL — scripts/nightly-etl/atrax_driver_utilization_compute.py

Reads p24_atrax_drivers_daily, computes the four windows for every driver active on the target Berlin day (a driver with a row that day), and delete+reinserts that driver’s rows (idempotent snapshot). Per-driver error isolation; a no-op on days with no drivers-daily rows. Reuses atrax_norm_common for env loading, PostgREST I/O, and the Error Notification Standard (Discord + GH issue on failure).

# yesterday (Europe/Berlin)
/usr/bin/python3.12 scripts/nightly-etl/atrax_driver_utilization_compute.py --env-file /opt/p24-infra/bms-4/.env
# a specific day, no writes
/usr/bin/python3.12 scripts/nightly-etl/atrax_driver_utilization_compute.py --date 2026-09-01 --dry-run --env-file .../.env

Scheduling. Wired into scripts/run-atrax-norm-nightly.sh as the last step, after atrax_vehicle_health_daily. (The tachograph-compliance job is not yet in that runner — #7107 PR-1’s task; ordering relative to it, if it lands later, is free since this step reads the drivers-daily snapshot and writes only its own table.) As with the other nightly steps, the sys-admin redeploy of the wrapper on bms-4 is the existing handoff.

Parquet export. scripts/nightly-etl/export_atrax.py exports p24_driver_utilization as a full_table snapshot (optional until first populated), so report-query-api can read it from Wasabi. That export’s canonical home is vps-i1 (see docs/atrax-analytics-etl-operations.md, Track B).

Read API — report-query-api

• GET /api/v1/driver/{driver_id}/utilization — current-state utilization rows for the driver (all window types). Snapshot read (newest export only), no date range.
• Also folded into GET /api/v1/driver/{driver_id}/report under the utilization key.

Registered in infra-src/report-query-api/tables.py as the driver_utilization SNAPSHOT TableSpec (driver_col='driver_id', not car-scoped).

EU AI Act

This analytic computes a deterministic arithmetic ratio (minutes driven ÷ legally-available driving minutes) — no ML, no inference, no scoring model. It is advisory / informational only: a human reads the percentage and decides what, if anything, to do; it is not wired into any automated disciplinary, performance-evaluation, or task-allocation decision. A plain ratio is not an “AI system” in the sense of EU AI Act Annex III.4(a), so it sits outside that obligation and carries no dev_r_ai_systems row — the same reasoning applied to p24_vehicle_health_anomalies (#7044) and the fuel metric p24_route_consumption_anomalies. (Contrast #6848’s ecodriving analytic, a statistical median/MAD evaluation of a named driver, which is registered.) It is registered in dev_r_services (migration 20260918083313_register_driver_utilization_7154.sql). If this metric is ever fed into an automated employment decision, a fresh conformity assessment is required first (docs/eu-ai-act-compliance.md).