Pagination is hard to use due to inconsistent ordering

[benasse]

Describe the issue

I recently updated ShellHub to the latest version without major issues.

I’m not sure whether this comes from the React frontend or the API after the migration to PostgreSQL, but pagination is quite difficult to use. For example, when making a request like:

GET /api/devices?page=2&per_page=10&status=accepted

the order of the devices appears to be random and inconsistent.

I believe the issue may be due to the absence of a defined sort order. What do you think?

[zoopp]

I don't want to hijack the issue but in case this might be related: the "Search devices" feature from the devices page appears to only search within the currently visible page, unlike the previous UI where it searched across all devices.

Additionally, even within this limited scope, the results sometimes include entries that don't seem to match the query. I'm pretty sure the search might be looking for a substring match in the UUID of the device too.

[gustavosbarreto]

Thanks for reporting this. The issue is that paginated queries lack a tiebreaker column in the ORDER BY clause. When multiple rows share the same sort value (e.g. last_seen), PostgreSQL returns them in non-deterministic order across pages.

Fix: #6127

@zoopp regarding the search behavior, we're looking into that as well.

[gustavosbarreto]

Both issues should be fixed now on master. Could you give it a try and let us know if it works as expected?

[benasse]

I just tested it with the dev version, and I can confirm that pagination and search are now working perfectly !