feat(server): cursor-only generations API + faster flaky-marking scan

Two ClickHouse read-path fixes for “Slow ClickHouse query” Grafana alerts (p90 > 1s), profiled against production system.query_log and validated against prod data + the local test suite.

1. ⚠️ Generations API is now cursor-only (breaking)

GET .../generations (listGenerations) paginated with page/page_size (offset). A consumer requesting a high page number (e.g. page ~320 → LIMIT 32000, 100) with the name = 'generate' filter made ClickHouse scan ~2.8M rows / 3.7 GB / 2.8 GB RAM to return 100 rows, and the cost grows with page depth — anyone could strain ClickHouse just by incrementing page.

This PR removes offset pagination from the endpoint in favor of cursor (keyset) pagination:

• Paginate with after / before cursors from pagination_metadata (end_cursor / start_cursor). Omitting both returns the first page.
• A cursor seek reads ~64K rows / 2 MB / 35 MB for a page, flat regardless of depth, and runs no count query.
• The page parameter is deprecated and ignored (kept as an accepted no-op because the endpoint’s strict validation would otherwise 400 on it — so older clients degrade gracefully to the first page rather than erroring).
• total_count / current_page / total_pages are nil for this endpoint (cursor pagination can’t compute them cheaply). total_count is relaxed to nullable/non-required in the shared PaginationMetadata schema; the ~20 offset-based endpoints still populate it.

BREAKING CHANGE — see the commit footer. Consumers iterating generations must switch from page to the after/before cursors.

Follow-up (not in this PR): the CLI’s tuist generation list uses this endpoint’s offset pagination + total_pages for its interactive table, so it will degrade to first-page-only until migrated to cursors. It is not the source of the abusive deep paging (it uses page_size=10, loads on demand); the abuser is an external/direct API client. A separate PR should move the CLI to cursors.

2. Prune the flaky-marking scan with a git_commit_sha bloom filter

mark_test_case_runs_as_flaky re-fetched full rows filtering only project_id + test_case_id + id, still scanning the entire ran_at span of a high-volume test case (up to ~888K rows). Every historical flaky run comes from get_existing_ci_runs_for_commit/4 for one commit, so we now also constrain git_commit_sha, engaging the table’s GRANULARITY 1 bloom filter to prune the scan to the granules holding that commit’s runs (6→2 granules on the profiled sample).

How to test locally

cd server
mix test test/tuist_web/controllers/api/generations_controller_test.exs
mix test test/tuist/tests_test.exs   # cross-run flakiness back-marking

Generations controller tests cover forward (after) / backward (before) cursor iteration, the default first page (non-nil cursors, nil total_count/current_page/total_pages), and graceful handling of a legacy ?page=N request (200 + first page). The cross-run flakiness tests exercise the git_commit_sha-filtered back-marking path. mix compile --warnings-as-errors, mix format, mix credo --strict are clean.

🤖 Generated with Claude Code

No GitHub comments yet.