Stop To-Do List pagination from skipping to-dos that share a date (!264946) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

The To-Do List sorts pending to-dos by the coalesced snooze and creation date and pages them with a keyset order built on that one expression (todo.rb#L242-257). TodosFinder picks that order for the pending tab with the created_desc and created_asc sorts (todos_finder.rb#L163-184), and CREATED_DESC ("Recommended") is the To-Do List's default (todos_app.vue#L102).

The date is not unique: to-dos snoozed together come back with the same snooze time, which is then their date. The bulk Snooze action writes one time to every selected to-do (snoozing_service.rb#L28-30), and the "Until tomorrow" and "Until next week" presets snooze until 08:00 local time on the day they name (todo_snooze_until_picker.vue#L33-44), so to-dos snoozed one by one with the same preset on the same day share it too. To-dos inserted in one transaction also share their creation time, since TodoService leaves it to insert_all, which takes PostgreSQL's CURRENT_TIMESTAMP, the start time of the transaction. The cursor of the next page holds only that date, and the next page starts strictly after it, so every remaining to-do with the same date as the last to-do on a page is skipped and no later page shows it. With three pending to-dos created in the same instant and currentUser.todos(first: 1, sort: CREATED_DESC), the first page returns one to-do with hasNextPage: true and the second page returns none.

sort_by_attribute adds its ID tie-breaker only to orders that are not keyset aware (todo.rb#L236-239), so it never reached this order. This MR adds the ID, in the same direction, as the last column of the keyset order, the way the label priority order already does (todo.rb#L286-290).

Behaviour changes:

To-dos that share a date are now all listed, ordered by ID in the direction of the sort; before, their order was whatever PostgreSQL returned, and the to-dos of a group that did not fit on the page were skipped. To-dos with different dates keep their order.
The keyset cursor of these two sorts now carries the to-do ID beside the date. A cursor issued before the deployment has no ID, and the keyset order refuses it ("Incorrect cursor values were given. Missing items: id", order.rb#L267-282), and a cursor from an updated node is refused by a node that is not yet updated. The To-Do List keeps its cursor in memory and not in the URL, so a To-Do List open across the deployment can fail once on Next or Previous, and reloading the page recovers. I did not add a feature flag: flipping one would change the cursor the same way.

Specs I ran and their output

On master at 797e8e3c with Ruby 3.3.11, PostgreSQL 17 and Redis 7.2:

spec/models/todo_spec.rb and spec/finders/todos_finder_spec.rb without the model change: 163 examples, 6 failures. Five of them are new examples: the four pagination examples and the :desc order example (the :asc order example passes on master by chance, since PostgreSQL happened to return the tied rows by ID). The sixth, Todo.for_note, does not use this order: it creates notes on the let_it_be issue, which the specs freeze, and ThrottledTouch writes to that issue once more than a minute has passed since it was created, so the example stops at a FrozenError whenever the run reaches it after that minute. With the model change and the same seed: 163 examples, 0 failures. A second run with a random seed had 1 failure, Todo.for_note again with the same FrozenError. The finder examples page six pending to-dos: three created in the same instant, and three created on different days that came back from one snooze. Making the ID column always descending fails both :asc model examples.
A throwaway GraphQL request spec, not part of this MR, that pages currentUser.todos(first: 1) over three to-dos created in the same instant: on master 1 of 3 to-dos for CREATED_DESC and for CREATED_ASC; on this branch 3 of 3, one per page.
spec/graphql/resolvers/todos_resolver_spec.rb, spec/requests/api/graphql/current_user/todos_query_spec.rb, ee/spec/requests/api/graphql/current_user/todos_query_spec.rb, spec/requests/api/todos_spec.rb and spec/controllers/dashboard/todos_controller_spec.rb in one run on this branch: 109 examples, 3 failures, none from this change. The two Dashboard::TodosController examples stop at cannot load such file -- sass from the page stylesheet, because I have not built the frontend CSS, and fail the same way on master. The N+1 example of GET /todos (todos_spec.rb:341) failed only after the other files in the same process: alone and with the whole todos_spec.rb (68 examples, 0 failures) it passes on master and on this branch, and GET /todos does not use this order.
bundle exec rubocop on the three changed files: no offenses. scripts/lint/commit_linter.rb on the commit: no findings.

$ bundle exec rspec spec/models/todo_spec.rb spec/finders/todos_finder_spec.rb   # master
rspec ./spec/models/todo_spec.rb:869 # Todo.for_note returns todos that belongs to notes
rspec ./spec/models/todo_spec.rb[1:34:3:1:2] # Todo.sort_by_snoozed_and_creation_dates when to-dos share the same date direction: :asc returns every to-do once when keyset paginated
rspec ./spec/models/todo_spec.rb[1:34:3:2:1] # Todo.sort_by_snoozed_and_creation_dates when to-dos share the same date direction: :desc breaks the tie by ID in the same direction
rspec ./spec/models/todo_spec.rb[1:34:3:2:2] # Todo.sort_by_snoozed_and_creation_dates when to-dos share the same date direction: :desc returns every to-do once when keyset paginated
rspec ./spec/finders/todos_finder_spec.rb[1:1:2:2:1:1] # TodosFinder#execute #sort when keyset paginating pending to-dos that share a date sort: :created_desc returns every to-do once across pages
rspec ./spec/finders/todos_finder_spec.rb[1:1:2:2:2:1] # TodosFinder#execute #sort when keyset paginating pending to-dos that share a date sort: :created_asc returns every to-do once across pages
163 examples, 6 failures
Randomized with seed 1215

$ bundle exec rspec --seed 1215 spec/models/todo_spec.rb spec/finders/todos_finder_spec.rb   # this branch
163 examples, 0 failures

$ bundle exec rspec spec/models/todo_spec.rb spec/finders/todos_finder_spec.rb   # this branch
rspec ./spec/models/todo_spec.rb:869 # Todo.for_note returns todos that belongs to notes
163 examples, 1 failure
Randomized with seed 21649

Database

The query changes only in its ORDER BY and, on pages after the first, its cursor condition. Both read the existing index_todos_coalesced_snoozed_until_created_at (user_id, state, timestamp_coalesce(snoozed_until, created_at)): PostgreSQL scans it in the date order as before and adds an Incremental Sort that orders each group of equal dates by ID. On pages after the first, the cursor becomes a row comparison, which PostgreSQL turns into an index bound on the date (<=) and a filter on the ID, so the extra rows read are bounded by the same-date group at the cursor, and the Incremental Sort also reads to the end of the group the page ends in. No migration and no new index.

The SQL below is what the GraphQL connection sent in a spec run, for the pending tab, CREATED_DESC and first: 1 (the To-Do List asks for 20). The plans come from a local PostgreSQL 17 database filled with synthetic data, not from GitLab.com: 2,000,000 to-dos over 20,000 users, and one user with 20,000 pending to-dos created in bursts that share their timestamp (1,810 distinct dates, the largest burst 310 to-dos), 5 % of them snoozed. I'd be glad to have a database reviewer check them against GitLab.com.

SQL before and after

First page, before:

SELECT todos.*, timestamp_coalesce(todos.snoozed_until, todos.created_at) AS coalesced_date FROM "todos" LEFT JOIN banned_users ON todos.author_id = banned_users.user_id WHERE "todos"."user_id" = 96 AND "todos"."state" = 'pending' AND "banned_users"."user_id" IS NULL AND "todos"."state" = 'pending' AND ("todos"."snoozed_until" <= '2026-10-22 08:53:59.864312' OR "todos"."snoozed_until" IS NULL) ORDER BY timestamp_coalesce(todos.snoozed_until, todos.created_at) DESC LIMIT 2

First page, after:

SELECT todos.*, timestamp_coalesce(todos.snoozed_until, todos.created_at) AS coalesced_date FROM "todos" LEFT JOIN banned_users ON todos.author_id = banned_users.user_id WHERE "todos"."user_id" = 254 AND "todos"."state" = 'pending' AND "banned_users"."user_id" IS NULL AND "todos"."state" = 'pending' AND ("todos"."snoozed_until" <= '2026-10-22 09:05:25.972322' OR "todos"."snoozed_until" IS NULL) ORDER BY timestamp_coalesce(todos.snoozed_until, todos.created_at) DESC, "todos"."id" DESC LIMIT 2

Next page, before:

SELECT todos.*, timestamp_coalesce(todos.snoozed_until, todos.created_at) AS coalesced_date FROM "todos" LEFT JOIN banned_users ON todos.author_id = banned_users.user_id WHERE "todos"."user_id" = 96 AND "todos"."state" = 'pending' AND "banned_users"."user_id" IS NULL AND "todos"."state" = 'pending' AND ("todos"."snoozed_until" <= '2026-10-22 08:54:05.006867' OR "todos"."snoozed_until" IS NULL) AND (timestamp_coalesce(todos.snoozed_until, todos.created_at) < '2026-10-21 08:53:49.000000000 +0000') ORDER BY timestamp_coalesce(todos.snoozed_until, todos.created_at) DESC LIMIT 2

Next page, after:

SELECT todos.*, timestamp_coalesce(todos.snoozed_until, todos.created_at) AS coalesced_date FROM "todos" LEFT JOIN banned_users ON todos.author_id = banned_users.user_id WHERE "todos"."user_id" = 254 AND "todos"."state" = 'pending' AND "banned_users"."user_id" IS NULL AND "todos"."state" = 'pending' AND ("todos"."snoozed_until" <= '2026-10-22 09:05:27.969895' OR "todos"."snoozed_until" IS NULL) AND ((timestamp_coalesce(todos.snoozed_until, todos.created_at), "todos"."id") < ('2026-10-21 09:05:17.000000000 +0000', 718)) ORDER BY timestamp_coalesce(todos.snoozed_until, todos.created_at) DESC, "todos"."id" DESC LIMIT 2

A previous page (last:) uses the reversed order: (...) > (...) with ORDER BY ... ASC, "todos"."id" ASC.

Plans (synthetic data, warm cache, LIMIT 21, Buffers lines left out)

First page, before:

 Limit (actual time=1.321..1.505 rows=21 loops=1)
   ->  Nested Loop Anti Join (actual time=1.318..1.497 rows=21 loops=1)
         ->  Index Scan Backward using index_todos_coalesced_snoozed_until_created_at on todos (actual time=0.861..0.903 rows=23 loops=1)
               Index Cond: ((user_id = 1) AND ((state)::text = 'pending'::text))
               Filter: ((snoozed_until <= '2026-10-22 09:00:00+00'::timestamp with time zone) OR (snoozed_until IS NULL))
               Rows Removed by Filter: 500
         ->  Index Only Scan using banned_users_pkey on banned_users (actual time=0.002..0.002 rows=0 loops=23)
               Index Cond: (user_id = todos.author_id)
               Heap Fetches: 0
 Planning Time: 2.806 ms
 Execution Time: 2.795 ms

First page, after:

 Limit (actual time=0.965..0.973 rows=21 loops=1)
   ->  Incremental Sort (actual time=0.964..0.967 rows=21 loops=1)
         Sort Key: (timestamp_coalesce(todos.snoozed_until, todos.created_at)) DESC, todos.id DESC
         Presorted Key: (timestamp_coalesce(todos.snoozed_until, todos.created_at))
         Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB
         ->  Nested Loop Anti Join (actual time=0.748..0.863 rows=22 loops=1)
               ->  Index Scan Backward using index_todos_coalesced_snoozed_until_created_at on todos (actual time=0.680..0.718 rows=24 loops=1)
                     Index Cond: ((user_id = 1) AND ((state)::text = 'pending'::text))
                     Filter: ((snoozed_until <= '2026-10-22 09:00:00+00'::timestamp with time zone) OR (snoozed_until IS NULL))
                     Rows Removed by Filter: 500
               ->  Index Only Scan using banned_users_pkey on banned_users (actual time=0.002..0.002 rows=0 loops=24)
                     Index Cond: (user_id = todos.author_id)
                     Heap Fetches: 0
 Planning Time: 0.592 ms
 Execution Time: 1.041 ms

Next page with the cursor inside the largest same-date group, before (it skips the rest of the group):

 Limit (actual time=0.054..0.138 rows=21 loops=1)
   ->  Nested Loop Anti Join (actual time=0.052..0.131 rows=21 loops=1)
         ->  Index Scan Backward using index_todos_coalesced_snoozed_until_created_at on todos (actual time=0.024..0.041 rows=21 loops=1)
               Index Cond: ((user_id = 1) AND ((state)::text = 'pending'::text) AND (timestamp_coalesce(snoozed_until, created_at) < '2026-06-07 01:20:41.623456'::timestamp without time zone))
               Filter: ((snoozed_until <= '2026-10-22 09:00:00+00'::timestamp with time zone) OR (snoozed_until IS NULL))
         ->  Index Only Scan using banned_users_pkey on banned_users (actual time=0.001..0.001 rows=0 loops=21)
               Index Cond: (user_id = todos.author_id)
               Heap Fetches: 0
 Planning Time: 0.527 ms
 Execution Time: 0.189 ms

Next page with the cursor inside the largest same-date group, after:

 Limit (actual time=1.110..1.117 rows=21 loops=1)
   ->  Incremental Sort (actual time=1.107..1.111 rows=21 loops=1)
         Sort Key: (timestamp_coalesce(todos.snoozed_until, todos.created_at)) DESC, todos.id DESC
         Presorted Key: (timestamp_coalesce(todos.snoozed_until, todos.created_at))
         Full-sort Groups: 1  Sort Method: top-N heapsort  Average Memory: 30kB  Peak Memory: 30kB
         Pre-sorted Groups: 1  Sort Method: quicksort  Average Memory: 30kB  Peak Memory: 30kB
         ->  Nested Loop Anti Join (actual time=0.396..0.921 rows=83 loops=1)
               ->  Index Scan Backward using index_todos_coalesced_snoozed_until_created_at on todos (actual time=0.378..0.636 rows=84 loops=1)
                     Index Cond: ((user_id = 1) AND ((state)::text = 'pending'::text) AND (timestamp_coalesce(snoozed_until, created_at) <= '2026-06-07 01:20:41.623456'::timestamp without time zone))
                     Filter: (((snoozed_until <= '2026-10-22 09:00:00+00'::timestamp with time zone) OR (snoozed_until IS NULL)) AND (ROW(timestamp_coalesce(snoozed_until, created_at), id) < ROW('2026-06-07 01:20:41.623456'::timestamp without time zone, 3015000)))
                     Rows Removed by Filter: 227
               ->  Index Only Scan using banned_users_pkey on banned_users (actual time=0.001..0.001 rows=0 loops=84)
                     Index Cond: (user_id = todos.author_id)
                     Heap Fetches: 0
 Planning Time: 0.533 ms
 Execution Time: 1.197 ms

Two more pairs have the same shapes: the next page with the cursor on the last row of the first page (0.250 ms before, 0.362 ms after), and the previous page (last:) with the cursor inside the largest group, a forward scan of the same index (0.209 ms before, 1.771 ms after).

References

The order was added in !178170 (merged) and moved to timestamp_coalesce and the coalesced_date cursor attribute in !180244 (merged); neither had a tie-breaker.
I found no issue for this. I found it while working on gitlab-mcp-server; its upstream-bugs.md records everything done on its dependencies and sibling projects.

Screenshots or screen recordings

Not applicable: a backend change. The To-Do List looks the same and now lists the to-dos it used to skip.

How to set up and validate locally

In a Rails console, create three pending to-dos for your user that share their creation time:

user = User.find_by_username('root')
issue = Issue.first
created_at = 1.minute.ago.change(usec: 0)
3.times { Todo.create!(user: user, author: user, project: issue.project, target: issue, action: Todo::MENTIONED, state: :pending, created_at: created_at) }

In GraphiQL (/-/graphql-explorer), as that user, run:

{ currentUser { todos(first: 1, sort: CREATED_DESC) { nodes { id createdAt } pageInfo { hasNextPage endCursor } } } }

Run it again with after: "<endCursor>" until hasNextPage is false. On master, the second page skips the other two new to-dos. On this branch, the three new to-dos come one per page, newest ID first.
Optionally, on /dashboard/todos, with more than 20 pending to-dos and a group of to-dos that share their creation time across the end of the first page: on master the second page starts after the whole group, on this branch it starts with the rest of the group. I checked this through the GraphQL connection the page uses, not in a browser.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Stop To-Do List pagination from skipping to-dos that share a date