feat: [BE] Add pipeline run stats endpoint with caching

[Mbeaulne]

Description

Add a new GET /api/pipeline_runs/stats endpoint that returns aggregate run counts grouped by status. Supports the same filter_query format used by the existing GET /api/pipeline_runs/ list endpoint, so the stats cards on the homepage stay in sync with whatever filters the user has applied.

Filtering

The endpoint accepts filter_query — the same JSON predicate format already used by the list endpoint.

Param	Type	Description
filter_query	string | null	JSON filter query (same format as the list endpoint)
current_user	string | null	Resolves "me" placeholder in filter predicates

Supported filter predicates

These are the filters currently used on the homepage:

Filter	Backend key	Predicate type	Example
Created by	system/pipeline_run.created_by	value_equals	{"value_equals": {"key": "system/pipeline_run.created_by", "value": "me"}}
Pipeline name	system/pipeline_run.name	value_contains	{"value_contains": {"key": "system/pipeline_run.name", "value_substring": "training"}}
Date range	system/pipeline_run.date.created_at	time_range	{"time_range": {"key": "system/pipeline_run.date.created_at", "start_time": "...", "end_time": "..."}}
Annotations	Custom annotation keys	key_exists / value_contains	{"key_exists": {"key": "my_annotation"}}

Predicates are combined with "and" logic:

{
  "and": [
    { "value_equals": { "key": "system/pipeline_run.created_by", "value": "me" } },
    { "time_range": { "key": "system/pipeline_run.date.created_at", "start_time": "2026-03-01T00:00:00Z", "end_time": "2026-03-31T23:59:59Z" } }
  ]
}

Examples

• No filters → stats for all runs in the system
• created_by: "me" → stats for my runs
• created_by: "me" + date range → stats for my runs in the last 7 days
• pipeline_name contains "training" → stats for all "training" runs

Response shape

{
  "total_runs": 1250,
  "counts_by_status": {
    "RUNNING": 3,
    "SUCCEEDED": 1100,
    "FAILED": 87,
    "CANCELLED": 50,
    "PENDING": 10
  },
  "cached_at": "2026-03-19T12:00:00Z"
}

Performance approach

Doing a full table scan on every request is not acceptable at scale. The solution:

• Server-side in-memory cache with 30-60s TTL, keyed by the hash of the resolved filter_query
• Runs a single SELECT container_execution_status, COUNT(*) ... WHERE <filter_clauses> GROUP BY container_execution_status query at most once per cache window
• No impact on existing list queries — this is a completely separate endpoint
• The existing filter_query_sql.py already builds WHERE clauses from filter predicates — reuse those same clauses for the stats query

Relevant files

• api_server_sql.py — add new stats() method
• api_router.py — register route
• filter_query_sql.py — reuse existing WHERE clause builder for the stats query
• filter_query_models.py — existing filter predicate models (no changes needed)
• backend_types_sql.py — existing PipelineRun model, existing indexes support this query

[yuechao-qin]

Some initial thoughts:

• current_user - If this is introducing a new param, which the backend needs to understand and translate it to be "me" in the backend, my current thought is to not have this and make it the UI responsibility to setup the filter_query appropriately. The filter_query for /api/pipeline_runs/ do support "me" string in the query, which this new filtery_query shall do the same.
• An architectural thought -- I would like to make oasis-backend a wrapper to tangle APIs, which will handle experimental APIs for faster turnaround times.

[Ark-kun]

Than you for the idea and the detailed description, Matt.

/api/pipeline_runs/

If that's OK, let's put the new API in /api/experimental/pipeline_runs/search_stats for now. I really want to see how this full feature will turn out.

counts_by_status

In other routes we used the container_execution_status_stats or execution_status_stats naming.

SELECT container_execution_status

So the numbers reflect the statistics of container execution statuses, correct (The total number is the number of executions, not runs)?

Server-side in-memory cache

We can also try using HTTP caching headers in the beginning.

Performance

Would be interesting to see the the latency percentiles later. We should be able to get them for free with observability.