6808-report-query-api

Design #6808 — report-query-api (FastAPI + DuckDB over Wasabi Parquet)

Issue: #6808 · Parent context: #6138 Track B (read-side) · Status: design / plan Author: dev-coder worker · Date: 2026-09-02

This is the read half of #6138 Track B (“Fuel/GPS data export to Parquet on Wasabi”). The write half already ships: scripts/nightly-etl/export_atrax.py dumps the Atrax daily tables to s3://p24-infra/reporting/atrax/{table}/{YYYY-MM-DD}.parquet (cron atrax-etl, 01:00 UTC on vps-i1, live since 2026-08-20, #6570). Today Wasabi is write-only for this data — nothing reads it back out. This design adds the queryable read surface Track B named but never built: a FastAPI service backed by DuckDB querying the Parquet exports in place.

Do not close #6138 — it covers other still-open tracks. This design does not touch the unrelated monthly scripts/atrax-archival.py per-car/driver JSON store.

---

1. Live data verification (design-phase pre-requisite)

Queried live via the Supabase service-role read (2026-09-02, PostgREST count=exact):

Table	Live rows	Latest key	Verdict for this API
p24_driver_ecodriving_daily	2130	stat_date=2026-09-01	FLOWING — ecodriving endpoints are READY
p24_gps_trips	6980	—	ready
p24_route_consumption_norm	5175	—	ready
p24_route_consumption_anomalies	243	—	ready
p24_atrax_drivers_daily	6540	—	ready
p24_consumption_rolling_averages	0	—	table created 2026-09-02 (#6777); empty until first nightly compute — served, tolerates-empty
p24_gps_daily_stats	4994¹	—	DEFERRED — do not build a read surface until #5918 ships (per issue)
p24_gps_fuel_snapshots	1 609 400	—	raw high-volume input, not a report table — excluded from the API

¹ Data-point flag for #5918 owners: the issue states p24_gps_daily_stats is “NOT populated — blocked”. Live it shows 4994 rows. This design still defers it per the issue’s explicit instruction (“Do not build a read surface over this table until #5918 ships”), but the non-zero count is worth confirming against #5918’s own status — the rows may be stale/partial. No endpoint is built against it here.

Ecodriving decision: because p24_driver_ecodriving_daily is confirmed populated and current, the ecodriving endpoints are scoped as in-scope and ready to implement, not deferred. (The #5921 signature blocker is resolved and its two feared downstream parser bugs did not, in fact, leave the table empty.)

EU AI Act note on eco_score: p24_driver_ecodriving_daily carries an eco_score column (a per-driver performance figure). This API only serves that already-computed, rule-based value — it does not itself score, rank, or evaluate drivers, and adds no ML. See §7.

---

2. Scope

In scope (this design → implementation):

• New infra-src/report-query-api/ FastAPI service (Python 3.13, containerised — mirrors audit-engine/’s pattern), backed by DuckDB querying the Wasabi Parquet exports in place via the httpfs/S3 extension (no re-ingest into a separate DB).
• Per-car report endpoints: trips, consumption norm, anomalies, rolling averages for a plate/vehicle
  • date range.
• Per-driver report endpoints: driver-scoped daily snapshot, driver-scoped rolling averages, driver-scoped anomalies.
• Ecodriving endpoints (ready — §1).
• Access control (§5) + deployment/credential plan (§6) + compliance registration (§7).

Prerequisite shipped in this PR: p24_consumption_rolling_averages added to export_atrax.py’s TABLES list (full_table=True, optional=True, keyed on computed_at — identical treatment to p24_route_consumption_norm, since it is rolling current-state with no per-day key). Without this, the six new #6777 rolling-average metrics would never land in Wasabi and so could not be served. Registration test added.

Out of scope (per issue):

• Fixing #5918 (daily-stats pipeline) — separate, in progress.
• scripts/atrax-archival.py monthly JSON store — unrelated pipeline.
• Any UI/dashboard consumption — that is a dependent et-operational-platform follow-up once the API exists (mirrors the #6777 → et-op#1910 sequencing).
• Re-litigating any other #6138 track.

---

3. Endpoints + response shapes

Base path /api/v1. All report endpoints take from / to query params (YYYY-MM-DD, inclusive) and require auth (§5). /health, /ping, /metrics are unauthenticated (Prometheus scrape + healthcheck), matching audit-engine.

Method	Path	Purpose	Backing Parquet
GET	/health, /ping	liveness	—
GET	/metrics	Prometheus	—
GET	/api/v1/cars	distinct vehicles seen in the export window	trips + drivers_daily
GET	/api/v1/car/{vehicle_id}/report?from&to	combined car report (trips + norm + anomalies + rolling averages)	all four car tables
GET	/api/v1/car/{vehicle_id}/trips?from&to	trips only	p24_gps_trips
GET	/api/v1/car/{vehicle_id}/consumption-norm	current norm rows for the car	p24_route_consumption_norm
GET	/api/v1/car/{vehicle_id}/anomalies?from&to	route anomalies	p24_route_consumption_anomalies
GET	/api/v1/car/{vehicle_id}/rolling-averages	rolling averages (vehicle/route window types)	p24_consumption_rolling_averages
GET	/api/v1/drivers	distinct drivers in window	drivers_daily + ecodriving
GET	/api/v1/driver/{driver_id}/report?from&to	combined driver report (daily snapshot + rolling averages + anomalies)	drivers_daily + rolling avgs + anomalies
GET	/api/v1/driver/{driver_id}/daily?from&to	per-day driver snapshot	p24_atrax_drivers_daily
GET	/api/v1/driver/{driver_id}/rolling-averages	driver-scoped rolling averages	p24_consumption_rolling_averages (window_type=driver_last_10_days, route_driver_last_10_trips)
GET	/api/v1/driver/{driver_id}/ecodriving?from&to	ecodriving daily (ready)	p24_driver_ecodriving_daily

Response envelope (consistent across endpoints):

{
  "query": { "vehicle_id": "…", "from": "2026-08-01", "to": "2026-08-31" },
  "generated_at": "2026-09-02T10:00:00Z",
  "source": "wasabi-parquet",       // or "materialized" if §4 cache is later added
  "row_count": 42,
  "data": [ { /* row shaped 1:1 to the Parquet/Supabase column set */ } ]
}

Rows are returned with the exact column names of the source table (no renaming) so the schema stays a thin pass-through of the ETL output — a column added upstream needs no API change. The combined /report endpoints return {"trips": {...}, "norm": {...}, "anomalies": {...}, "rolling_averages": {...}}, each an envelope as above. Empty backing table → row_count: 0, data: [] (never a 500 — the optional tables can legitimately be empty).

Identifier normalisation — OPEN, must be resolved in implementation

The car identifier is not uniform across tables (confirmed from the live column sets):

• p24_gps_trips, p24_route_consumption_norm, p24_consumption_rolling_averages → vehicle_id
• p24_driver_ecodriving_daily → atrax_car_id and vehicle_name (no vehicle_id)

The issue asks for a “per-plate” lookup. Decision for v1: the path parameter is vehicle_id (the common key across the three route tables); ecodriving is joined on its own key. Whether vehicle_name is the human plate, and whether a small vehicle-registry map (vehicle_id ↔ plate ↔ atrax_car_id) is needed for a true /car/{plate} lookup, is the first implementation task — resolve by inspecting the live column values before finalising the path scheme. Driver identifier (driver_id) is consistent across drivers_daily, ecodriving, and the driver-scoped rolling averages.

---

4. DuckDB-over-S3-Parquet architecture

DuckDB with the httpfs extension can read s3://…/*.parquet in place with predicate + projection pushdown, so a date-range query touches only the daily Parquet files in range.

INSTALL httpfs; LOAD httpfs;
SET s3_endpoint='s3.eu-central-2.wasabisys.com';
SET s3_region='eu-central-2';
SET s3_url_style='path';
SET s3_access_key_id=?; SET s3_secret_access_key=?;   -- Wasabi READ creds, never logged
 
-- per-day tables: glob the date-partitioned files, filename_date filter via read_parquet(filename)
SELECT * FROM read_parquet('s3://p24-infra/reporting/atrax/p24_gps_trips/*.parquet',
                           filename=true)
WHERE vehicle_id = ?
  AND trip_date BETWEEN ? AND ?;
 
-- full-snapshot tables (norm, rolling averages): read only the newest daily file
SELECT * FROM read_parquet('s3://p24-infra/reporting/atrax/p24_consumption_rolling_averages/2026-09-01.parquet')
WHERE vehicle_id = ?;

Volume reality check. The served report tables are small: trips 6980, norm 5175, drivers_daily 6540, anomalies 243, rolling averages ≤ a few thousand — all cumulative totals, so a single day’s Parquet file is a few hundred KB at most. p24_gps_fuel_snapshots (1.6 M rows) is the one large table and is deliberately excluded — it is the raw input, not a report surface. A month-range query = globbing ~30 small files. Predicate pushdown + Parquet column stats make this cheap.

Recommended v1 architecture: direct per-request DuckDB-over-S3, no materialisation. One long-lived in-process DuckDB connection (:memory:, httpfs loaded once at startup), one query per request. This is the simplest correct design and avoids a stale-cache class of bugs.

Materialisation is a documented fallback, not v1. If the PoC (below) shows cold Wasabi read p95 is too high for the eventual dashboard, add a periodic step that pulls the newest Parquet files into a local DuckDB file (or the container’s disk) on the ETL cadence, and flip source to "materialized". This is additive and needs no API contract change. Do not build it pre-emptively.

PoC validation — REQUIRED before committing the architecture (credential-gated → delegated)

The issue’s acceptance requires a real DuckDB-over-S3 query against live Wasabi. That needs the Wasabi read credentials (P24_INFRA_WASABI_ACCESS_KEY / _SECRET_KEY), which live in secrets/monitoring.env.sops and, in plaintext, only on vps-i1 (/opt/p24-infra/monitoring/.env). A dev-coder worker cannot decrypt SOPS or run commands on vps-i1, so the PoC is scoped as an implementation-phase validation task to be run by a sys-admin role on vps-i1:

# On vps-i1 (sys-admin), creds already present in /opt/p24-infra/monitoring/.env — never echo them:
#   duckdb -c "INSTALL httpfs;LOAD httpfs; SET s3_endpoint=...; SET s3_access_key_id=getenv('P24_INFRA_WASABI_ACCESS_KEY'); ...
#             SELECT count(*),min(trip_date),max(trip_date)
#             FROM read_parquet('s3://p24-infra/reporting/atrax/p24_gps_trips/*.parquet');"
# Measure wall-clock for: (a) a single-day file, (b) a 30-day glob. Record p50/p95 in the PR.

Acceptance for that task: cold and warm latency for a single-day read and a 30-day-glob read, recorded on the implementation PR. If 30-day p95 > ~2 s, adopt the §4 materialisation fallback.

---

5. Access control

This is a new internal HTTP service exposing fleet operational data — it must not be wide open.

Recommended v1: shared-secret Bearer key, verified in a FastAPI dependency/middleware, matching the established meta-dispatcher QUEUE_API_KEY precedent (server-to-server, simplest robust option). The sole planned consumer is et-operational-platform server-side (per the #6777 → et-op#1910 sequencing), which can hold a server secret — no browser-side token is needed for v1.

• Authorization: Bearer <REPORT_QUERY_API_KEY> on every /api/v1/* route; /health, /ping, /metrics stay open.
• Constant-time compare; 401 on missing/bad key. Never log the key or the request Authorization header.
• HTTPS termination + not-publicly-exposed-beyond-need via the existing Caddy reverse proxy on vps-i1 (same as the monitoring stack).

Considered, deferred: Supabase-JWT verification via the p24-auth-worker CF Worker precedent — warranted only if a browser/end-user ever needs to call the API directly. Note it as the v2 upgrade path; do not build it now.

---

6. Deployment target + credential plan

Target: vps-i1. It is co-located with the export_atrax.py producer cron and already holds the Wasabi credentials, so the API reads the same bucket with no new credential distribution to a second host. Deploy as a container in the monitoring docker-compose.yml (FastAPI on :8000, Prometheus /metrics scraped like the other exporters, healthcheck like audit-engine), behind Caddy. (bms-4 was considered and rejected: it would require shipping Wasabi read creds to a second host, widening blast radius for no benefit.)

Credential plan (all credential ops → secret-manager; this design does not touch SOPS):

1. New REPORT_QUERY_API_KEY (the §5 Bearer key) → a narrow, single-purpose SOPS file secrets/report-query-api.env.sops, following the secrets/anthropic-admin.env.sops precedent (2 recipients — developer + CI AGE_KEY_GHA; a dedicated secrets-sync.yml job that writes only this service’s env and recreates only this container, never the full stack). This avoids the monitoring.env.sops env_file: .env broad-blast-radius that #5685 called out.
2. Wasabi read access. v1 may reuse the existing P24_INFRA_WASABI_* read keys already on vps-i1. Preferred hardening: a dedicated read-only Wasabi IAM key scoped to the reporting/atrax/* prefix (precedent: infra-src/wasabi-iam-rotator), so a serve-side compromise cannot write/delete exports. Decide read-only-scoped-key vs reuse during implementation; either way the key is provisioned by secret-manager, not here.
3. The same REPORT_QUERY_API_KEY is delivered to the et-op consumer via that repo’s own secret path (its Vercel env, from secrets/et-operational-platform.env.sops) — a separate secret-manager task at consumer-integration time.

---

7. Compliance (CLAUDE.md §Compliance — MANDATORY for all new elements)

On implementation (not in this design PR):

• Add a dev_r_services row (compliance_workbook='yes', workbook_url) for report-query-api.
• Create docs/report-query-api-operations.md ops doc.

EU AI Act: this service is a read-only serving layer over pre-computed, rule-based statistics (the Atrax pipeline’s own stated “rule-based statistics, not ML” — trips, consumption norms/medians, plain-arithmetic rolling averages, and the ecodriving eco_score). It performs no scoring, ranking, profiling, or evaluation of its own and contains no model. It therefore does not introduce a new Annex III high-risk system and does not need a dev_r_ai_systems entry — matching the existing Atrax ETL precedent. Confirm this classification with the compliance owner during implementation (the analysis is by design intent, not assumed by analogy alone), given that it serves per-driver performance data (eco_score) whose downstream use in an employment context is what would be regulated — that obligation sits with the consuming dashboard/decision process, not this data tap.

---

8. Regression risk (this PR)

• export_atrax.py change adds one optional=True table. If the table is absent/empty the export logs a warning and skips (existing optional behaviour) — no nightly-run failure. Registration test added; full test_export_atrax* suite green (40 passed, 1 skipped).

---

9. Implementation task list (for the follow-up implementation issue)

1. Resolve car-identifier normalisation (§3) against live column values.
2. Run the Wasabi DuckDB PoC on vps-i1 (§4) → sys-admin; record latency; pick direct vs materialised.
3. Scaffold infra-src/report-query-api/ (FastAPI + DuckDB + Dockerfile, mirror audit-engine/).
4. Implement endpoints (§3) incl. ecodriving (ready).
5. Bearer-key auth (§5).
6. Provision REPORT_QUERY_API_KEY + Wasabi read scope → secret-manager (§6).
7. Wire into monitoring/docker-compose.yml + Caddy + Prometheus scrape → sys-admin.
8. dev_r_services row + docs/report-query-api-operations.md (§7).
9. et-op consumer follow-up issue (out of scope here).

Follows the standard design → /review-plan → iterate → implement → PR workflow.