[BUG][FIX]: Endless-list pagination returns HTTP 500 when using before[listChangedAt] + before[id]

[Bug]: Endless-list pagination returns HTTP 500 when using before[listChangedAt] + before[id]

Summary

We reproduced and diagnosed this on PLANKA 2.1.1 with PostgreSQL 16.

The original report identified the failing Waterline cursor path and proposed routing cursor requests through the existing native SQL implementation with deterministic ordering. After building and deploying that first patch, the HTTP 500 still occurred.

Further instrumentation showed a second, earlier failure in the controller input definition: the nested before[...] query object is declared as type: 'json'. In this path PLANKA/Sails fails during input processing with:

Cannot read properties of undefined (reading 'name')

Changing the before input to type: 'ref' allows the parsed object to reach the existing isBefore() validator correctly.

The complete fix therefore touches two files and consists of three changes:

1. accept the nested before object as type: 'ref' in the controller;
2. route requests with before through the native SQL path;
3. add deterministic ORDER BY card.list_changed_at DESC, card.id DESC before LIMIT.

Root cause

1. Controller input coercion fails for before

File:

server/api/controllers/cards/index.js

Current code:

before: {
  type: 'json',
  custom: isBefore,
},

With a real query such as:

GET /api/lists/<listId>/cards?before[listChangedAt]=2026-08-26T06:11:45.932Z&before[id]=1849965776484697144

PLANKA receives the nested query object, but the type: 'json' input path fails before the controller handler can use it, producing HTTP 500 and:

Cannot read properties of undefined (reading 'name')

In an instrumented 2.1.1 test instance, changing the input to:

before: {
  type: 'ref',
  custom: isBefore,
},

caused the parsed object to reach isBefore() unchanged. The existing validator then correctly verified:

• plain object shape;
• exactly listChangedAt + id;
• strict ISO-8601 timestamp;
• valid PLANKA id.

2. The native SQL path must be used for cursor pagination

File:

server/api/hooks/query-methods/models/Card.js

The native SQL implementation already contains the correct compound cursor predicate:

card.list_changed_at < cursor_timestamp
OR (
  card.list_changed_at = cursor_timestamp
  AND card.id < cursor_id
)

The existing Waterline path expresses the same condition through criteria, but this was the path originally reached for before-only requests.

Using the native SQL implementation for before avoids that cursor criteria path and reuses the already-correct compound predicate.

3. Native SQL pagination requires deterministic ordering before LIMIT

The native SQL branch currently ends with:

query += ` LIMIT ${LIMIT}`;

without an ORDER BY.

For cursor pagination, sorting only after the query cannot repair an unordered server-side LIMIT: rows may already have been excluded from the page. This can create omissions or duplicates across page boundaries.

The query therefore needs:

ORDER BY card.list_changed_at DESC, card.id DESC

before LIMIT.

Complete patch

diff --git a/server/api/controllers/cards/index.js b/server/api/controllers/cards/index.js
--- a/server/api/controllers/cards/index.js
+++ b/server/api/controllers/cards/index.js
@@
     before: {
-      type: 'json',
+      type: 'ref',
       custom: isBefore,
     },

diff --git a/server/api/hooks/query-methods/models/Card.js b/server/api/hooks/query-methods/models/Card.js
--- a/server/api/hooks/query-methods/models/Card.js
+++ b/server/api/hooks/query-methods/models/Card.js
@@
 const getByEndlessListId = async (listId, { before, search, userIds, labelIds }) => {
-  if (search || userIds || labelIds) {
+  if (before || search || userIds || labelIds) {
@@
     if (labelIds) {
       const inValues = labelIds.map((labelId) => {
         queryValues.push(labelId);
         return `$${queryValues.length}`;
       });

       query += ` AND card_label.label_id IN (${inValues.join(', ')})`;
     }

+    query += ' ORDER BY card.list_changed_at DESC, card.id DESC';
     query += ` LIMIT ${LIMIT}`;

Validation

Disposable integration test

A clean derived image based on the official PLANKA 2.1.1 image was tested against a disposable PostgreSQL 16 database.

Test dataset: 63 archived cards, forcing more than one server page with PLANKA's LIMIT = 50.

Results:

page 1: HTTP 200, 50 cards
page 2: HTTP 200, 13 cards
page 3: HTTP 200, 0 cards
combined: 63 cards
unique ids: 63
duplicates: 0
page 1 order: correct
page 2 order: correct
page boundary: correct

The direct cursor requests in this test did not require any search/filter workaround.

Equal-timestamp tiebreaker

Two cards were deliberately assigned exactly the same listChangedAt and positioned on opposite sides of the 50/13 page boundary.

Observed boundary:

page 1 last:
  id = 1849965776484697144
  listChangedAt = 2026-08-26T06:11:45.932Z

page 2 first:
  id = 1849965776123986997
  listChangedAt = 2026-08-26T06:11:45.932Z

The larger id appeared first, as required by id DESC.

Full traversal still produced:

63 cards
63 unique ids
0 duplicates
correct global ordering

The disposable database timestamps were restored after this test.

Tested in production

The complete patch was then deployed as a derived PLANKA 2.1.1 image in our production installation.

Base image:

ghcr.io/plankanban/planka:2.1.1
sha256:19b507ae3ab5cb1855c3f6984249e4a4881ed0b912febdfd492139c29bf10f39

Derived image:

lodlp/planka:2.1.1-1761-v2
image id: sha256:f9db571fd456b845a7b91ed9d339c36b3cdbf01234eb35fb91e969f2ba9c6bb8

Deployed file hashes:

5493b1e9ae8936b6d6bc8949b872e5d4dbc08ed649df62161ef8c713449f0f4e  /app/api/hooks/query-methods/models/Card.js
fd1de1f664e165976ffe1dd83725c822007fb99038153b21828aa30322dbfebe  /app/api/controllers/cards/index.js

Production archive traversal after deployment:

77 archived cards returned
2 server pages scanned
next_cursor = null
scan_truncated = false
undated_cards_omitted = 0
HTTP 500 = none

A separate explicit two-page production read also succeeded with the cursor returned by page 1, where the same request had previously returned HTTP 500 consistently.

Our current client still adds a neutral search=/.* on cursor pages as a temporary 2.1.1 workaround introduced before this PLANKA patch. Therefore the production traversal proves that the fixed before parsing and deterministically ordered SQL cursor path work against real production data. The direct before-without-search path was validated separately in the disposable integration test above.

We intend to remove that client-side workaround now that the PLANKA-side fix is deployed.

Upstream status checked

The relevant implementations in PLANKA 2.2.1 and current master were checked on 2026-08-26 and still contained the same original code at that time:

before: {
  type: 'json',
  custom: isBefore,
},

and:

if (search || userIds || labelIds) {

with the native SQL branch still applying LIMIT without the required cursor ordering.

Environment

PLANKA: 2.1.1
Database: PostgreSQL 16
Deployment: Docker / docker compose

Suggested regression tests

The endpoint should cover at least:

• first page without before;
• cursor in the middle of a result set;
• direct before request without filters;
• pagination across more than LIMIT cards;
• complete traversal with no duplicates;
• complete traversal with no omissions;
• identical listChangedAt values crossing a page boundary, verifying id DESC;
• terminal cursor returning items: [] with HTTP 200;
• pagination with search;
• pagination with userIds;
• pagination with labelIds.