Pagination audit and rollout plan

This report records the API's pagination and request-safety gaps as of 2026-08-30. It focuses on collection endpoints used by the experimental UI and on shared API behavior that affects those endpoints.

The decision is straightforward: do not retrofit new defaults or response envelopes onto existing routes. The Go service promises to preserve legacy paths, query parameters, status codes, and JSON shapes (source). Add a coherent /api/v2 collection contract, migrate one experimental screen at a time, and keep every existing route available with its current behavior.

Executive finding

The API supports pagination, but not as a platform capability. Individual handlers independently implement page/pageSize, page/limit, page/perPage, or limit/offset; other collection handlers return every row. Some paginated responses bound the SQL query, while others fetch and enrich the complete candidate set before slicing it in Go.

The application has useful failure containment—panic recovery, request logs, HTTP timeouts, a bounded database pool, and several route caches—but no application-level rate limiter. A deployed host has returned 429 during contract recording, which proves that some outer layer can throttle traffic; it does not define a quota that API clients can rely on (source, source).

The overfetch observed in the experimental UI is therefore a combination of three separate problems:

1. the UI requests too many rows;
2. several endpoints return fields the screen never renders; and
3. several handlers still perform work for rows that pagination later removes.

A correct rollout must address all three.

Numbered problem inventory

Endpoint inventory

This inventory covers collection endpoints and endpoints with pagination-like parameters. It deliberately excludes detail routes where limit means “last N matches” rather than “rows per page.” Generated OpenAPI currently exposes at least one of page, pageSize, perPage, limit, or offset on 42 GET operations, but the source determines whether each parameter is pagination.

Response and SQL are both paged

The limits and query placement above come directly from the event, screener, team screener, referee, Hunter, and generalized linear model (GLM) handlers (source, source, source, source, source, source, source, source).

Response is paged after broader work

Growing collections without pagination

The fixture-day response builds nested objects with entire database rows (source). The tournament handler loads every visible row before consolidating and grouping it (source, source). These are distinct from unlimited player/team directories: a small lookup directory can remain non-paged if its maximum cardinality is explicit and tested.

Backward-compatibility contract

The rollout must satisfy all of these rules:

1. Existing routes remain mounted at the same paths.
2. Existing query and body parameter names, defaults, parsing quirks, and out-of-range behavior do not change.
3. Existing status codes, headers, cache behavior, field names, nesting, nulls, numeric strings, and array/object envelopes do not change.
4. Existing ordering remains unchanged, including ordering that is not fully deterministic.
5. Existing authentication and authorization behavior does not change.
6. Existing generated OpenAPI operations stay present.
7. No v1 route is silently redirected to a v2 handler.
8. Internal clients move explicitly and can roll back to the old route.

This is stricter than source compatibility. It treats observable behavior as the contract because current callers may rely on undocumented details.

Additive v2 contract

Mount new collection routes under /api/v2. The existing route named /api/value-bets-v2 remains where it is; its suffix is part of a resource name, not the new namespace.

Request

All pageable v2 endpoints use:

Filters keep endpoint-specific names. Pagination does not require a universal filter language.

Use numbered pages for experimental tables because the current UI exposes page numbers and exact totals. A future live feed may use a separate cursor contract, but one operation must not switch between page and cursor envelopes.

Response

Use the shape the experimental UI has already converged on, making pageSize required in v2:

{
  "data": [],
  "pagination": {
    "currentPage": 1,
    "pageSize": 25,
    "totalCount": 0,
    "totalPages": 0,
    "hasNextPage": false,
    "hasPreviousPage": false
  }
}

Rules:

• An empty result has totalPages: 0.
• A valid page beyond the last page returns an empty data array; it is not clamped to the last page.
• totalCount counts rows after every requested filter.
• Every sort ends with a unique tie-breaker: normally id; for grouped boards, a documented composite key.
• Count and page queries run against the same predicate. When consistency across both statements matters, use one statement with a window count or a read-only transaction at an appropriate snapshot.
• The page query applies LIMIT/OFFSET before expensive enrichment. If the sort key is computed, expose it in SQL or a materialized read model so the database can rank keys first.
• Cache keys include every filter, sort, page, page size, authorization scope, and representation variant.

Compact screen DTOs instead of arbitrary field selection

Do not add a global fields= parameter. It creates many implicit schemas, complicates cache keys, and makes authorization review harder. Define typed, screen-oriented data transfer objects (DTOs):

• LeaguePlayerLeaderboardRow
• RefereeBoardRow
• DataQualityEventRow
• DataQualitySummary
• SuperSubLeaderboardRow
• FixtureDayRow

Each DTO should contain only fields rendered by the screen or required to build its links and accessible labels. OpenAPI then documents a finite contract, and unused-field checks can compare the DTO against the component props.

Implementation plan

Phase 0: Freeze and measure the legacy surface

1. Record contract goldens for every legacy collection route before adding v2. Include defaults, malformed values, zero/negative values, the maximum, above the maximum, empty results, and past-the-end pages.
2. Record the legacy database query count and normalized table access with the existing contract harness. It already supports query-log assertions (source).
3. Capture response bytes, returned rows, rows scanned, database duration, p50, p95, and p99 for the experimental UI's actual request URLs.
4. Mark every collection as one of: SQL paged, response-only paged, deliberately bounded directory, or unbounded.

Exit condition: every existing route has a frozen behavior fixture and a work classification.

Phase 1: Build shared v2 pagination primitives

Create an internal package that owns:

• parsing and validation of page and pageSize;
• checked offset calculation that cannot overflow;
• per-endpoint maximums;
• the response metadata type;
• allowlisted sort resolution; and
• helpers for a stable final tie-breaker.

Mount /api/v2 separately from the legacy /api handlers. Do not refactor a v1 handler to call a v2 helper in the first change; sharing implementation before parity is proven makes a supposedly additive change capable of altering legacy behavior.

Exit condition: unit tests cover the complete parameter matrix and one small v2 endpoint proves the contract without changing a legacy golden.

Phase 2: Remove the experimental UI's largest overfetch first

Implement and migrate in this order:

1. League leaderboard. Add a projected endpoint that sorts by an allowlisted statistic in SQL and returns 12 rows by default. Stop generating every aggregate for every season player when the screen only needs goals and basic identity.
2. Referees. Add todayOnly as a server filter, request 25 rows, return a stable referee-ID tie-breaker, and fetch card odds only for fixture IDs on the current page.
3. 100 Club. Move from pageSize=1000 to 25-row pages and add the existing shared pagination control. This screen can initially use the current screener endpoint because it already pages in SQL; move it to a compact v2 DTO after the UI behavior is stable.
4. Data visualizer. Split detailed events from summaries. Page the daily or custom-range detail table; calculate week/month totals, daily coverage, and corrupted-event counts in SQL and return DataQualitySummary.
5. Super Sub Heroes. Send the active stat to the server and return one paged leaderboard instead of nine top-50 lists.
6. Fixture day. Add a compact v2 read model for the fields the experimental fixture board renders. Keep grouped day summaries bounded; paginate only the expandable fixture list if a day exceeds the chosen ceiling.

Each migration keeps the old request implementation behind a short-lived feature flag. Compare v1 and v2 row identities and sort order in non-user-facing telemetry before switching the default, then retain a rollback path for one release.

Exit condition: no experimental screen downloads more rows than it renders or downloads detail rows solely to compute a summary.

Phase 3: Bound work, not only response size

For each response-only paged handler:

1. write a first query that returns only ordered row keys plus the exact total;
2. include the unique tie-breaker in that order;
3. apply the page to the key query;
4. enrich only those keys in bounded batch queries; and
5. restore key order after enrichment without an N+1 query.

For computed sorts such as outlier edge, displayed value-bet edge, positive EV, and trend strength, choose one of:

• express the calculation in SQL;
• maintain a materialized read model refreshed when source rows change; or
• use a bounded candidate query with a documented proof that it cannot omit a higher-ranked result.

Do not claim that a route is properly paginated until the page size bounds its dominant enrichment work. A separate count may still examine all matching keys; index it and measure it independently.

Exit condition: query traces show that returned page size bounds projected and enriched rows, and no endpoint performs an N+1 query.

Phase 4: Cover remaining growing collections

Add paged v2 directories for players, teams, managers, incidents, and any other table whose cardinality grows with ingestion. Define compact sort/search DTOs rather than exposing full database models.

For categories, countries, seasons, visible tournaments, and other lookup data, choose deliberately:

• paginate it if cardinality can grow without a small business maximum; or
• document a hard maximum, enforce it in SQL, return a compact DTO, and test the maximum if the UI genuinely needs the complete directory.

“Pagination everywhere” should therefore mean every growing collection is paged and every unpaged collection is explicitly bounded, not that a country dropdown needs page controls.

Exit condition: no collection has an accidental unlimited scan or response.

Phase 5: Add rate and resource controls to v2

Introduce rate limiting at two layers:

1. edge limits absorb obvious abuse before it reaches Go; and
2. application limits enforce the documented contract by authenticated user or API key, falling back to IP only for anonymous traffic.

Classify routes by cost instead of giving every request equal weight. A cached directory read and a fresh trend aggregation should not consume the same budget. Derive actual quotas from Phase 0 traffic and latency rather than inventing numbers in the interface design.

Every v2 limit response must include:

• HTTP 429;
• Retry-After;
• standard RateLimit-* headers supported by the chosen gateway; and
• the v2 error envelope with a stable RATE_LIMITED code.

Also add, for v2 first:

• a maximum request-body size;
• per-route context deadlines shorter than the server write timeout;
• a concurrency semaphore for expensive aggregations;
• a database statement timeout for v2 transactions;
• a readiness probe that checks required dependencies; and
• a default client timeout with abort propagation and retries only for safe, idempotent requests.

Applying a normal quota to v1 would change observable behavior and violate the compatibility contract. Keep v1 outside the documented v2 quota initially. An outer emergency ceiling may still protect service availability, but it must be set high enough to act as incident containment, not ordinary client policy.

Exit condition: v2 publishes and tests its rate-limit response, and an expensive route cannot occupy the entire database pool indefinitely.

Phase 6: Deprecate usage, not compatibility

After every internal consumer has moved:

1. mark the old collection operation deprecated in generated OpenAPI;
2. measure remaining v1 traffic by route and client identity;
3. stop adding features to v1; and
4. keep v1 mounted until an explicit external deprecation project authorizes removal.

This pagination project does not remove legacy endpoints.

Validation matrix

Contract tests

• All existing goldens remain unchanged.
• V2 rejects invalid page values consistently.
• Default and maximum page sizes are exact.
• Above-maximum page sizes fail with a documented structured error rather than silently changing cost.
• Empty and past-the-end responses follow the v2 rules.
• Filters affect totalCount before pagination.
• Every sort includes and tests its unique tie-breaker.
• Composite rows preserve a documented composite identity.

Database tests

• The data query contains a database-side page boundary where applicable.
• Enrichment queries receive at most the IDs from the page.
• Query count is constant as page size and candidate count grow.
• Appropriate indexes support filter, sort, tie-breaker, and count predicates.
• EXPLAIN (ANALYZE, BUFFERS) is captured for the default and maximum page on a production-sized snapshot.

UI tests

• Page state lives in the URL and survives refresh/back/forward navigation.
• Changing a filter resets the page to one.
• Loading a new page keeps the previous board visible only where that is the screen's established behavior.
• Empty, first, middle, last, and past-the-end pages render correctly.
• Browser network tests assert the requested pageSize and a response-byte ceiling for each screen.
• Summary screens do not fetch their underlying detail collection.

Load and failure tests

• Test default and maximum pages under realistic concurrency.
• Verify 429 and Retry-After at the application and edge layers.
• Abort a client request and verify database work receives cancellation.
• Exhaust the expensive-route semaphore and verify bounded rejection rather than pool starvation.
• Make the database unavailable and verify readiness fails while liveness still reports the process correctly.

Definition of done

Pagination is complete when all of the following are true:

• Every legacy contract test passes without updating its expected response.
• Every growing v2 collection uses page and pageSize with the common response envelope.
• Every unpaged v2 collection has an enforced, documented cardinality ceiling.
• Every paged query has deterministic ordering with a unique final tie-breaker.
• Page size bounds the response and dominant per-row enrichment work.
• Every experimental table requests only the rows it renders.
• Every experimental summary uses an aggregate DTO rather than downloading detail rows.
• Response-byte and database-work budgets are measured in continuous integration for the priority endpoints.
• V2 rate limiting and resource limits have documented, tested behavior.
• V1 remains mounted and observably unchanged.