API lists: pages ordered by a column that is not unique can repeat one row and skip another #440

New issue

Closed

opened 2026-10-01 10:46:43 +00:00 by tiagoagueda · 0 comments

tiagoagueda commented

2026-10-01 10:46:43 +00:00

Owner

Found on 2026-09-29 in a read-only review of postulo at 95348908d, reading the ordering of every paginated list in the API.

Checked again on 2026-10-01 against main at 0a5cad587: still present. The six lists have not moved; the captures ordering given for comparison is now at api/api.py:652.

Asked without updated_since, several lists are cut into pages with LIMIT/OFFSET over an ordering that ties. SQL gives tied rows no order, and PostgreSQL says outright that different LIMIT/OFFSET values can return tied rows in different orders because the planner takes the limit into account. So a client reading a list page by page can be given one row twice and another never. Captures, listings and offers already break ties with the primary key; these do not.

Where:
- api/routers/applications.py:70, list_applications: .order_by("-created_at").
- api/routers/companies.py:42, list_companies: .order_by("name"); a company name is not unique.
- api/routers/documents.py:73 and :89, list_cvs and list_letters: .order_by("name").
- api/routers/interviews.py:53, :55 and :57, list_interviews: order_by("starts_at") or ("-starts_at"); the default upcoming state takes its order from InterviewQuerySet.upcoming() in applications/models.py:680, also order_by("starts_at").
- api/routers/reminders.py:29, list_reminders: .order_by("due_at").
- For comparison: api/api.py:650 ("-created_at", "-pk"), api/routers/listings.py:89 and :127 ("-noted_at", "-pk"), api/routers/offers.py:32 ("-created_at", "-pk"). changed_since in api/paging.py orders by ("updated_at", "pk"), so only the cursor path is safe.
What happens: on PostgreSQL, an agent reads GET /api/v1/reminders?limit=20, then offset=20. Several reminders share one due_at (answer-by reminders set for the same morning, say) and straddle the boundary. Because the two queries may order the tied rows differently, one of them can appear on both pages and another on neither. Nothing reports it, and the agent's copy is wrong. SQLite happens to return ties in a stable order, which is why the test suite does not see it.
Why it matters: limit/offset is the pagination the wiki documents for every list, and a client that walks a list end to end has no way to notice a row that was skipped.
Fix: end every list ordering with the primary key: order_by("-created_at", "-pk") for applications, ("name", "pk") for companies, CVs and letters, ("starts_at", "pk") or ("-starts_at", "-pk") for interviews (including upcoming()), and ("due_at", "pk") for reminders.

Done when

A test in tests/test_api_lists.py walks every paginated list with limit/offset and asserts that the queryset each view hands to Page has an ordering ending in pk or -pk (the tie cannot be reproduced on SQLite, so the ordering is what is checked). It fails today for the six lists above.

Found on 2026-09-29 in a read-only review of postulo at 95348908d, reading the ordering of every paginated list in the API. Checked again on 2026-10-01 against `main` at `0a5cad587`: still present. The six lists have not moved; the captures ordering given for comparison is now at `api/api.py:652`. Asked without `updated_since`, several lists are cut into pages with `LIMIT`/`OFFSET` over an ordering that ties. SQL gives tied rows no order, and PostgreSQL says outright that different `LIMIT`/`OFFSET` values can return tied rows in different orders because the planner takes the limit into account. So a client reading a list page by page can be given one row twice and another never. Captures, listings and offers already break ties with the primary key; these do not. - **Where:** - `api/routers/applications.py:70`, `list_applications`: `.order_by("-created_at")`. - `api/routers/companies.py:42`, `list_companies`: `.order_by("name")`; a company name is not unique. - `api/routers/documents.py:73` and `:89`, `list_cvs` and `list_letters`: `.order_by("name")`. - `api/routers/interviews.py:53`, `:55` and `:57`, `list_interviews`: `order_by("starts_at")` or `("-starts_at")`; the default `upcoming` state takes its order from `InterviewQuerySet.upcoming()` in `applications/models.py:680`, also `order_by("starts_at")`. - `api/routers/reminders.py:29`, `list_reminders`: `.order_by("due_at")`. - For comparison: `api/api.py:650` (`"-created_at", "-pk"`), `api/routers/listings.py:89` and `:127` (`"-noted_at", "-pk"`), `api/routers/offers.py:32` (`"-created_at", "-pk"`). `changed_since` in `api/paging.py` orders by `("updated_at", "pk")`, so only the cursor path is safe. - **What happens:** on PostgreSQL, an agent reads `GET /api/v1/reminders?limit=20`, then `offset=20`. Several reminders share one `due_at` (answer-by reminders set for the same morning, say) and straddle the boundary. Because the two queries may order the tied rows differently, one of them can appear on both pages and another on neither. Nothing reports it, and the agent's copy is wrong. SQLite happens to return ties in a stable order, which is why the test suite does not see it. - **Why it matters:** `limit`/`offset` is the pagination the wiki documents for every list, and a client that walks a list end to end has no way to notice a row that was skipped. - **Fix:** end every list ordering with the primary key: `order_by("-created_at", "-pk")` for applications, `("name", "pk")` for companies, CVs and letters, `("starts_at", "pk")` or `("-starts_at", "-pk")` for interviews (including `upcoming()`), and `("due_at", "pk")` for reminders. ## Done when - A test in `tests/test_api_lists.py` walks every paginated list with `limit`/`offset` and asserts that the queryset each view hands to `Page` has an ordering ending in `pk` or `-pk` (the tie cannot be reproduced on SQLite, so the ordering is what is checked). It fails today for the six lists above.

tiagoagueda added this to the 0.6.0 milestone

2026-10-01 10:46:43 +00:00

tiagoagueda added the

tier

bug

labels

2026-10-01 10:46:43 +00:00

tiagoagueda referenced this issue from a commit

2026-10-03 22:56:00 +00:00

End every paginated list's ordering with the primary key

tiagoagueda closed this issue

2026-10-03 22:56:00 +00:00