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 ServiceEmail Digest Job ──→ HTTP (API key) ──→ ──→ JSON response2. Design Principles
- Direct SQL, no semantic layer. Queries are plain
.sqlfiles 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
.duckdbfiles 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+ dashboardsA 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):
controlRoomIdin view definitions — sourced from the URL path parameter, validated as UUID by the router before reaching the query layer- 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
v2alongsidev1 - 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
- Add a router with the
POSTendpoint under/controlrooms/{controlRoomId}/dashboards/{name}/v1 - Add a query folder with SQL files and an orchestrator
- Add request/response Pydantic models (camelCase field aliases)
- Register in the dependency map
- Add tests
Introducing a Breaking Change to an Existing Dashboard
- Add a
v2router alongside the existingv1 - Create new request/response models for v2 (can share the orchestrator if only the API shape changed)
- Keep
v1working — do not modify its contract - 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
Authorizationheader - Service validates against Auth0 JWKS
- Extracts
organization_idand permissions from claims
API Key Auth (Machine Consumers)
- Uses the same
X-Authorizationheader 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
controlRoomIdin 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
.sqlfiles 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 |