p24-infra Docs

report-query-api-operations

4 min read

report-query-api — operations

FastAPI + DuckDB service that serves the Atrax fleet-report Parquet exports on Wasabi as per-car / per-driver JSON reports. Read half of #6138 Track B. Source: infra-src/report-query-api/. Design: docs/designs/6808-report-query-api.md.

  • Write half (upstream): scripts/nightly-etl/export_atrax.py — nightly on vps-i1 (01:00 UTC, cron atrax-etl) → s3://p24-infra/reporting/atrax/{table}/{YYYY-MM-DD}.parquet.
  • This service reads those Parquet files in place via DuckDB httpfs (no re-ingest) and never writes to Wasabi.

Architecture

  • One long-lived in-process DuckDB :memory: connection; httpfs + Wasabi read credentials set once at startup (duck.py).
  • Two read modes, matching the exporter’s two write modes (tables.py):
    • PER_DAY (p24_gps_trips, p24_route_consumption_anomalies, p24_atrax_drivers_daily, p24_driver_ecodriving_daily): each daily file holds one day. A range query globs {table}/*.parquet and filters the date column.
    • SNAPSHOT (p24_route_consumption_norm, p24_consumption_rolling_averages): each daily file is a full dump; only the newest file is read (older files are superseded).
  • Car identifier: the same Atrax car ObjectId is stored as atrax_car_id in trips/ecodriving and as vehicle_id in the route/rolling tables. The API path param {vehicle_id} is that ObjectId; each table is filtered on its own column. vehicle_name (ecodriving only) is the human plate (e.g. ZPL71820), surfaced by /api/v1/cars.
  • All user input (ids, dates, window types) is bound as DuckDB ? parameters; only the Parquet path (from the fixed table whitelist) is interpolated — no SQL-injection surface.

Endpoints

All /api/v1/* require Authorization: Bearer $REPORT_QUERY_API_KEY; /health, /ping, /metrics are open. from/to are inclusive YYYY-MM-DD. Empty backing table → row_count: 0 (never a 500). See README.md for the full table.

Deployment (vps-i1) — DELEGATED sys-admin + secret-manager tasks

This service is not deployed by the implementation PR. It requires a server operation and a new credential, which a dev-coder cannot perform. Ordered checklist:

1. Credentials → secret-manager (#6808 design §6)

  1. New Bearer key REPORT_QUERY_API_KEY → narrow secrets/report-query-api.env.sops (2 recipients: developer + CI AGE_KEY_GHA; the secrets/anthropic-admin.env.sops precedent), with a dedicated secrets-sync.yml job that writes only this service’s env and recreates only this container.
  2. Wasabi read access: either reuse the existing P24_INFRA_WASABI_* read keys already on vps-i1, or (preferred hardening) a dedicated read-only Wasabi IAM key scoped to reporting/atrax/*.
  3. Deliver the same REPORT_QUERY_API_KEY to the et-op consumer via that repo’s Vercel env (secrets/et-operational-platform.env.sops) at consumer-integration time.

2. PoC latency validation → sys-admin on vps-i1 (design §4)

Before committing to direct-read (vs the materialisation fallback), run a real DuckDB-over-S3 query against live Wasabi (creds already in /opt/p24-infra/monitoring/.env — never echo them) and record p50/p95 for a single-day read and a 30-day glob on the PR. If 30-day p95 > ~2 s, adopt the §4 materialisation fallback (additive, no API-contract change).

3. Compose service (add to monitoring/docker-compose.yml)

  report-query-api:
    build: ../infra-src/report-query-api
    container_name: report-query-api
    restart: unless-stopped
    env_file:
      - .env.report-query-api        # written 0600 by the dedicated secrets-sync job
    environment:
      - WASABI_BUCKET=p24-infra
      - WASABI_ENDPOINT=s3.eu-central-2.wasabisys.com
      - WASABI_REGION=eu-central-2
    expose:
      - "8000"
    networks:
      - monitoring

4. Prometheus scrape (add to monitoring/prometheus/prometheus.yml)

  - job_name: report-query-api
    static_configs:
      - targets: ['report-query-api:8000']

5. Caddy reverse proxy

Add an internal route on the existing monitoring Caddy (HTTPS termination, not exposed beyond need), matching the other stack services.

6. Compliance (CLAUDE.md §Compliance — do at deploy time)

  • Insert a dev_r_services row for report-query-api (compliance_workbook='yes', workbook_url → this doc). dev_r_services is a VIEW — insert against its underlying relation, never ALTER it (see docs/playbooks/supabase-migrations.md).
  • EU AI Act: read-only serving of pre-computed, rule-based statistics (incl. the ecodriving eco_score); no scoring/ranking/model of its own → no new Annex III system, no dev_r_ai_systems entry (matches the Atrax ETL precedent). Confirm with the compliance owner at deploy time (design §7).

Local dev / test

cd infra-src/report-query-api
pip install -r requirements.txt -r requirements-test.txt
python -m pytest -q                            # local Parquet fixtures — no Wasabi, no creds
REPORT_QUERY_API_KEY=dev uvicorn main:app --port 8000

Troubleshooting

SymptomCause / fix
503 service not configured on /api/v1/*REPORT_QUERY_API_KEY unset — fails closed by design. Set it.
401 on /api/v1/*missing/wrong Bearer key.
row_count: 0 for a known carcheck the date range; snapshot tables (norm, rolling-averages) ignore from/to (latest snapshot only).
502 backend query failedDuckDB/Wasabi error (bad creds, endpoint). Check container logs; the actual error is logged, never returned.
rolling-averages always emptyp24_consumption_rolling_averages had 0 rows until the first nightly compute after #6777; verify the export ran and the table populated.

Graph View