Skip to content

Architecture: Dashboard Query Service

Status: Draft Date: 2026-02-24 Companion to: ADR: Replacing Evidence Dashboards


1. What This Service Does

A Python/FastAPI service that queries per-organization DuckDB files and returns shaped JSON for analytics consumers. One endpoint per dashboard view. Replaces the Evidence static-site pipeline.

The service is consumer-agnostic — it serves JSON over HTTP. Any authenticated client can use it.

Known Consumers

Consumer Auth Method Description
Stencil dashboard components JWT (user’s token) In-platform interactive dashboards, replacing Evidence iframes
Weekly email digest API key (per-customer) Scheduled job that fetches dashboard data and renders email summaries
Stencil Component ──→ HTTP (JWT) ──→ ──→ JSON response
Dashboard Query Service
Email Digest Job ──→ HTTP (API key) ──→ ──→ JSON response

2. Design Principles

  • Direct SQL, no semantic layer. Queries are plain .sql files executed against DuckDB. No Cube.js, no ORM. The dashboard and query count don’t justify an intermediate abstraction today.
  • Single process. One FastAPI process per container instance. No sidecar, no separate query compilation server.
  • Convention over tooling. Folder structure and tests manage query ownership across dashboards. No dbt — revisit when views depend on other views or dashboard count exceeds ~10.
  • Consumer-agnostic. The service serves JSON over HTTP. It doesn’t know or care whether the caller is a Stencil component, an email renderer, or a CLI tool. Auth is the only difference between consumers.
  • Typed contracts. Every request and response is a Pydantic model. This is the API contract with all consumers.

3. Technology Choices

Layer Choice Why
Language Python 3.11+ DuckDB’s best-supported driver, Dagster pipeline is Python
Framework FastAPI Auto-generated OpenAPI docs, Pydantic integration
Database DuckDB (read-only) Already produced by Dagster, fast for analytical queries on local files
Deployment ECS Fargate Long-lived containers that cache DuckDB files locally
Package manager uv Fast, lockfile support

Why Not Lambda

DuckDB files are tens to hundreds of MB. Lambda would need to download one on every cold start, and concurrent users mean concurrent downloads of the same file. This workload wants a persistent process that downloads once and serves many requests.

Why Not Cube.js

Evaluated in a parallel PoC. Cube adds a semantic YAML layer and caching, but for our workload it introduced more complexity than value:

  • Required two processes (Cube server + Express proxy) instead of one
  • Complex queries (percentile bands) needed raw SQL in YAML anyway, breaking the abstraction
  • The Express proxy was doing the real work — building Cube query JSON, handling retries, reshaping responses
  • DuckDB is fast enough on 2-3M rows without Cube’s caching layer

Cube makes sense when many teams write ad-hoc queries against shared metrics. We own both the queries and the consumer — a semantic layer would sit in the middle without adding value.


4. DuckDB File Management

Data Source

Dagster’s organization_duck_db asset produces per-organization .duckdb files and uploads them to S3. Each contains three tables: asset_data (hourly telemetry, ~2-3M rows), assets (metadata, ~600 rows), and control_room_asset_map (control room → asset relationships, ~4K rows).

Caching Strategy

DuckDB’s native file format isn’t designed for range requests over HTTP. The service must read local files.

  • Download .duckdb files from S3 to a local cache directory on first request for an organization
  • Check S3 LastModified (HEAD request) periodically to detect when Dagster produces a new file
  • Re-download when stale
  • Frequency of staleness checks is configurable (default: every 5 minutes per org)

All DuckDB connections are opened read-only.


5. Query Architecture

Three Layers

Layer 1 — Raw tables. The three tables in the DuckDB file. Owned by Dagster, not this service.

Layer 2 — Views. Temporary SQL views created per-request on the DuckDB connection. They filter raw tables to a single control room and compute derived columns (date spines, daily aggregations, fuel consumption calculations). Four views today, all independent of each other — none references another view.

Layer 3 — Dashboard queries. SQL files that query the views and return the data a specific dashboard needs (hero metrics, vehicle tables, chart data, performer rankings). Each assumes the relevant views have been created first.

Query Organization

Queries are organized by dashboard in the folder structure:

queries/
views/ # Shared view definitions (Layer 2)
fleet_overview/ # Dashboard-specific queries (Layer 3)
asset_utilisation/
{future_dashboard}/
shared/ # Queries used by 2+ dashboards

A query starts in its dashboard folder. When a second dashboard needs it, it moves to shared/. That movement is the signal that changes require cross-dashboard verification.

A simple registry maps each dashboard to the views and queries it depends on, so you can answer “what breaks if I change this view?” without tracing imports.

Parameterization

All query parameters use DuckDB’s native parameter binding. Two places where string substitution is required (DuckDB doesn’t support parameterized bindings in CREATE VIEW statements or for table names):

  1. controlRoomId in view definitions — sourced from the URL path parameter, validated as UUID by the router before reaching the query layer
  2. Table name swaps (e.g., business hours vs 24/7 view) — value comes from a Python enum, not user input

Neither involves raw user input reaching SQL.

Heavy Queries

The telemetry views (fuel consumption, wend, bus_wend) are the most expensive. They generate a date spine, CROSS JOIN with all assets, and LEFT JOIN aggregated data to produce zero-filled rows. This is necessary for table/chart outputs that display days with no activity, but wasteful for aggregate queries that sum everything down.

Optimization options if this becomes a bottleneck:

  • Create only the views each dashboard actually needs (not all 4)
  • Push date range filters into view definitions to narrow the date spine
  • Split into “aggregate” and “table” view variants
  • Cache DuckDB connections per control room to avoid recreating views

6. API Design

Conventions (Aligned with Skyledge Platform API)

The dashboard query service follows the same conventions as the existing Skyledge API (api.skyledge.com):

Aspect Convention Example
Path segments Lowercase, kebab-case for compound words /dashboards/fleet-overview
Path parameters camelCase {controlRoomId}
Query parameters camelCase startDate, vehicleTypes, fuelUnit
Response fields camelCase averageUtilisation, totalDistanceKm, deviceName
Request body fields camelCase startDate, vehicleTypes, fuelUnit, yearWeek
Auth header X-Authorization for API keys, Authorization: Bearer for JWT See Section 7
Versioning Per-dashboard version in path /dashboards/fleet-overview/v1
Collections Plural vehicles, assetTypes, weeks

Nesting Under Control Rooms

Following the platform pattern where resources are accessed in the context of a control room. Dashboard queries use POST with a JSON request body — this is consistent with the platform’s existing search endpoints (e.g., POST /controlrooms/{controlRoomId}/events/search) and avoids long query strings with repeated filter values.

POST /controlrooms/{controlRoomId}/dashboards/{dashboardName}/v{version}
{
"startDate": "2025-12-01",
"endDate": "2025-12-31",
"vehicleTypes": ["Truck", "Van"],
"fuelUnit": "l_per_100km"
}

The organizationId is not in the request body — it is resolved from the authenticated identity (JWT claims or API key mapping), matching how the platform API scopes requests to an organization implicitly.

Per-Dashboard Versioning

The version is scoped to each dashboard, not the whole API. Different dashboards can be at different versions. This allows breaking changes to one dashboard’s request/response contract without affecting others.

What constitutes a breaking change (requires version bump):

  • Removing or renaming a response field
  • Changing the type or structure of a response field
  • Removing a request parameter
  • Changing the meaning of an existing parameter

What does not require a version bump:

  • Adding new optional request fields
  • Adding new fields to the response
  • Adding new dashboards
  • Performance improvements or query changes that produce the same output

Version lifecycle:

  • New dashboards start at v1
  • When a breaking change is needed, add v2 alongside v1
  • Old versions are maintained for a deprecation period (communicated to API key consumers)
  • Stencil components are updated in lockstep with version bumps (same team owns both)
  • Email digest and other machine consumers are notified and given time to migrate

Current Endpoints

Endpoint Request Body Fields Response Fields
POST /controlrooms/{controlRoomId}/dashboards/fleet-overview/v1 startDate, endDate, vehicleTypes, fuelUnit, timezoneOffsetHours heroMetrics, vehicles, assetTypes, fuelUnit
POST /controlrooms/{controlRoomId}/dashboards/asset-utilisation/v1 yearWeek, vehicleTypes, sourceTable, selectedVehicles heroMetrics (with week-over-week deltas), bestPerformers, worstPerformers, chartData, performanceTable, weeks, assetTypes

Responses are self-contained — they include the primary data plus all filter options (asset types for dropdowns, available weeks, etc.) so the consumer doesn’t need a second request.

Adding a New Dashboard

  1. Add a router with the POST endpoint under /controlrooms/{controlRoomId}/dashboards/{name}/v1
  2. Add a query folder with SQL files and an orchestrator
  3. Add request/response Pydantic models (camelCase field aliases)
  4. Register in the dependency map
  5. Add tests

Introducing a Breaking Change to an Existing Dashboard

  1. Add a v2 router alongside the existing v1
  2. Create new request/response models for v2 (can share the orchestrator if only the API shape changed)
  3. Keep v1 working — do not modify its contract
  4. Communicate deprecation timeline to machine consumers (API key users)

7. Authentication & Authorization

Two Auth Methods

The service supports two authentication methods, selected by the Authorization header format:

Method Header Format Use Case Identity Source
JWT Authorization: Bearer {jwt_token} Interactive users via Stencil components Auth0 — user’s existing session token passed via WidgetSkyledgeInterface
API key X-Authorization: {key} Machine-to-machine — email digest jobs, internal tools, customer integrations Platform API keys — same X-Authorization header used by the existing Skyledge API

Both methods resolve to the same internal identity: an organization_id and a set of permissions. Downstream of auth, the service doesn’t distinguish between a human user and a scheduled job.

JWT Auth (Interactive Users)

  • Stencil components pass the user’s JWT in the Authorization header
  • Service validates against Auth0 JWKS
  • Extracts organization_id and permissions from claims

API Key Auth (Machine Consumers)

  • Uses the same X-Authorization header and API key format as the existing Skyledge platform API
  • Each API key is already scoped to an organization in the platform
  • The dashboard service validates the API key against the platform’s key registry (same source of truth)
  • No separate key management — customers use their existing Skyledge API keys

Authorization

Regardless of auth method, the service verifies:

  • The authenticated identity has access to the controlRoomId in the path — verified by calling the platform backend (e.g., the control rooms API) and checking the user is a collaborator or has the appropriate role
  • The identity has the dashboard/report permission

The organizationId is derived from the authenticated identity, not from the request — there is nothing for the caller to spoof.

Control Room Access Cache

The control room access check is a backend call on every request. To avoid repeated round-trips, results are cached using cachetools with a TTL:

  • Cache key: (identityId, controlRoomId)
  • TTL: Short-lived (e.g., 5 minutes) — balances performance with timely revocation when a user’s access is removed
  • Eviction: TTL-based expiry, no manual invalidation needed at this scale
  • Scope: Per-worker process (each uvicorn worker maintains its own cache instance)

Development Mode

A skip_auth flag bypasses all auth checks for local development. Must not be enabled in production.


8. Concurrency

DuckDB supports multiple concurrent read-only connections to the same file. The service runs multiple uvicorn worker processes (one per vCPU). Each worker handles one request at a time — DuckDB calls are synchronous.

4 workers on 1 vCPU is a reasonable starting point. Scale horizontally (more container instances) before vertically.

For responsiveness, DuckDB calls should be wrapped with asyncio.to_thread() so the event loop isn’t blocked (health checks and metrics endpoints remain responsive during queries).


9. Deployment

Container

ECS Fargate (or equivalent). Long-lived containers that:

  • Download DuckDB files from S3 on first request per org
  • Cache files locally on ephemeral disk
  • Serve many requests from the cache
  • Health check on GET /health

Resource Sizing (Starting Point)

Resource Estimate Rationale
CPU 1 vCPU DuckDB is single-threaded per query; parallelism via workers
Memory 2 GB DuckDB memory-maps files
Ephemeral disk 10 GB ~20 cached org files
Workers 4 One query in flight per worker

S3 Access

Via IAM role on the ECS task. No access keys in environment.


10. Observability

Telemetry (OpenTelemetry)

All telemetry — traces, metrics, and logs — flows through OpenTelemetry. This gives a single instrumentation layer and allows export to any backend (Jaeger, Datadog, AWS X-Ray, Prometheus via OTel Collector, etc.).

Traces: Each request produces a trace spanning auth, DuckDB file resolution, view creation, and individual SQL query execution. Trace context propagates via traceparent header for correlation with upstream services.

Metrics: Consider instrumenting at minimum:

  • HTTP request duration and error rates
  • Individual DuckDB query duration (per dashboard, per SQL file)
  • DuckDB file cache hit/miss rates and S3 download times
  • Auth access check latency and failure rates

Logs: Structured JSON with trace ID correlation.

Logging

Structured JSON per request: endpoint, organization/control room IDs, duration, query count, cache hit/miss, trace ID (for correlation with traces). Warn on queries exceeding 2 seconds.

Health Check

GET /health for load balancer. Extended detail endpoint for debugging (cached org count, cache size, uptime).

Alerting Recommendations

  • Request error rate (5xx) exceeds 1% over 5 minutes
  • P95 request duration exceeds 5 seconds
  • DuckDB file download failures
  • Auth access check backend failures (upstream dependency down)

11. Testing Strategy

Three layers:

Layer What It Tests Example
View integrity View definitions produce valid data No negative fuel consumption, utilisation in 0-100 range, business hours view excludes Sundays
Query logic Orchestrator functions return correct results Weighted fuel consumption matches hand-calculated value, week-over-week deltas across year boundary, empty date range returns zeros not errors, vehicle type filter applies to hero metrics not just table
API integration Endpoints accept params and return correct shapes 200 with valid params, 422 with invalid UUID, response contains expected keys

View and query tests run against a real DuckDB file (the same ogroup.duckdb sample used in development). API tests use FastAPI’s test client.


12. Security

  • SQL injection: DuckDB parameter binding for all query parameters. The two string substitutions (view definitions, table name swaps) use router-validated path parameters and Python enums respectively — no raw user input reaches SQL.
  • Read-only database: All connections opened read-only. No writes possible.
  • No user-supplied SQL: The service executes pre-defined .sql files only.
  • CORS: Restricted to specific origins for browser-based consumers. Server-to-server consumers (email digest) are not subject to CORS.
  • API key management: Keys are per-organization, long-lived, revocable. No wildcard keys that grant cross-org access.
  • S3 access: IAM role, no keys in environment.

13. Error Handling

Scenario Response
Invalid parameters 422 (automatic via Pydantic)
Auth failure 401 / 403
DuckDB file not found in S3 404 — no data for this organization
S3 download failure 503 — retry with backoff, then fail
Query timeout 504 — configurable timeout (default 30s)
Unexpected query error 500 — log details, return generic message

14. Configuration

All via environment variables with DASHBOARD_ prefix:

Variable Purpose
S3_BUCKET, S3_PREFIX DuckDB file location
CACHE_DIR Local cache path
STALENESS_CHECK_INTERVAL_SECONDS How often to check S3 for updated files
CORS_ORIGINS Allowed origins
AUTH_JWKS_URL, AUTH_AUDIENCE JWT validation
API_KEYS_SOURCE Where to load API keys from (env, secrets_manager)
SKIP_AUTH Dev-only auth bypass
QUERY_TIMEOUT_MS DuckDB query timeout
LOG_LEVEL Logging verbosity
WORKERS Uvicorn worker count

15. When to Revisit These Decisions

Signal Revisit
Query times consistently > 2s Optimize views: push date filters down, split aggregate vs table variants, cache connections
Same view being modified for different dashboards Extract shared logic, consider dbt for view management
Views depending on other views Adopt dbt or build-time materialization
Dashboard count exceeds ~10 Revisit folder convention, add tooling for dependency tracking
Multiple consumers querying same metrics Consider a semantic layer
DuckDB files exceed ~1GB Consider Parquet storage with DuckDB’s S3 range-request support
Non-engineers need to author dashboards Consider a config-driven system