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

[Mbeaulne]

Description

Add a new GET /api/experimental/pipeline_runs/search_stats endpoint that returns aggregate pipeline 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.

The goal is to return counts of pipeline runs broken down by their statuses, making it easy to surface how many runs are running, succeeded, failed, etc. for a given filter. The exact semantics of what constitutes a "run status" for counting purposes is left to the implementer's discretion.

Filtering

The endpoint accepts filter_query — the same JSON predicate format already used by the list endpoint. The "me" placeholder in filter predicates is resolved server-side from the authenticated session, consistent with how the existing list endpoint handles it. No additional current_user param is needed — it is the UI's responsibility to set the filter_query appropriately.

Param	Type	Description
filter_query	string | null	JSON filter query (same format as the list endpoint)

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,
  "container_execution_status_stats": {
    "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. Some form of caching should be used — whether that is server-side in-memory caching, HTTP caching headers, or another mechanism is left to the implementer's discretion. The important thing is that repeated identical queries are not hitting the database on every request.

• 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.

[Mbeaulne]

Thanks for the feedback, Alexey! I've updated the issue to reflect your suggestions:

• Path: Updated to /api/experimental/pipeline_runs/search_stats — agreed, good to keep it experimental for now.
• Naming: Updated the response field to container_execution_status_stats to stay consistent with other routes.
• Runs vs. executions: The intention is to count pipeline runs broken down by status — so the total reflects the number of runs matching the filter, not container executions. I've clarified this in the description and left the exact implementation semantics to the implementer's discretion.
• Caching: Good call — I've made the caching approach non-prescriptive. HTTP headers, in-memory cache, or something else is fine; just needs to avoid hitting the DB on every request.
• Observability: Agreed, would be great to track latency percentiles once it's live.

[Mbeaulne]

Thanks Yuechao! Agreed on both points:

• current_user param: Removed it — the UI will set the filter_query appropriately and the backend will resolve "me" from the authenticated session, consistent with how the list endpoint already works.
• Architecture: Noted, the experimental endpoint will be handled at the wrapper layer for faster turnaround.

[Ark-kun]

The intention is to count pipeline runs broken down by status

I'm not sure we have well-defined "pipeline run status". It's non-trivial to represent the state of the whole run with a single status.

[yuechao-qin]

@Mbeaulne this is what I'm envisioning to support this feature with, to be able to support without code changes in OSS.

https://docs.google.com/document/d/1-LYqrfGvKFgxIBUQLPWSQk4N0G9BI6gWsY04_fV7NGA/edit?tab=t.0#heading=h.4mmzayxdsbuz