fix(server): route project-only test case run listings through a slim MV

Summary

The API endpoint GET /projects/:account/:project/test_case_runs filters only by project_id when neither test_run_id nor test_case_id is provided. With the main test_case_runs ORDER BY (project_id, test_case_id, ran_at, id), that query has to scan every row for the project before ORDER BY ran_at DESC LIMIT N can resolve, because ran_at is only sorted within each test_case_id. On a busy project this is ~110M rows and times out at 15s. The historical proj_test_case_runs_by_project_ran_at projection was dropped when the parent table moved to ReplacingMergeTree (projections + RMT don’t coexist).

Fix

Add a slim materialized view test_case_runs_by_project ordered by (project_id, ran_at, id) (ReplacingMergeTree(inserted_at)), and route project-only listings through it, mirroring the established test_case_runs_by_test_run / test_case_runs_by_shard_id pattern: slim MV serves filter+sort+paginate, then we look up the page rows in the main table by id.

Filter precedence in Tuist.Tests.list_test_case_runs/2:

1. test_run_id -> TestCaseRunByTestRun MV
2. shard_id -> TestCaseRunByShardId MV
3. test_case_id -> main table (PK (project_id, test_case_id, ran_at, id) already covers it cheaply)
4. project_id -> new TestCaseRunByProject MV

Why this approach

Considered alternatives:

• Re-add the projection on the main table. Not possible: test_case_runs is ReplacingMergeTree, which doesn’t support projections.
• Reorder the main table. Would invalidate every existing access pattern and require a full backfill.
• Add a skip index. Doesn’t help when the filter is the entire project and the bottleneck is the sort, not the scan.

A slim MV matches the pattern already used for test_run_id and shard_id scopes and keeps the main table untouched.

Impact

• API list endpoint and any LiveView / MCP tool path that lists test case runs scoped to a project (no test_run_id / test_case_id) goes from a 110M-row scan to an ordered slim-MV read of page size.
• New ingest path writes to one additional MV; throughput impact is the same as the existing per-test-run and per-shard MVs.
• No schema change to the main test_case_runs table.

How to test locally

1. Apply migrations: cd server && mise exec -- mix ecto.migrate.
2. Run the targeted test:

   cd server && mise exec -- mix test test/tuist/tests_test.exs --only describe:\"list_test_case_runs/1\"
   
   

3. Broader sanity:

   mise exec -- mix test test/tuist/tests_test.exs test/tuist_web/controllers/api/test_case_runs_controller_test.exs test/tuist_web/live/test_case_live_test.exs test/tuist_web/live/test_run_live_test.exs test/tuist/mcp/components/tools/test_tools_test.exs
   
   

Validation done

• list_test_case_runs/1 describe block: 4/4 green (new test covers project-scoped listing across multiple test runs, isolated from other projects).
• Full affected file set: 266/266 green across tests_test.exs, test_case_runs_controller_test.exs, test_case_live_test.exs, test_run_live_test.exs, test_tools_test.exs.
• mix format and mix credo clean on touched files.

🤖 Generated with Claude Code

No GitHub comments yet.