[api-server]: support keyset based pagination

Before proceeding, is there an existing issue or discussion for this?

• I have done a search for similar issues and discussions.

Description

We are currently using offset based pagination, which has some drawbacks

1. It gets slower as the offset increases.
2. Result will be inconsistent if rows are added or removed. This mainly affects use cases like exporting data to csv.

Implementation Considerations

Keyset based pagination solves the above problems but has some other drawbacks as well

1. Pages must be retrieved in order, it is not possible to jump to the middle of the result set.
2. Typically it is not possible to show the number of results.

These 2 drawbacks means that we need to redesign the frontend, either some kind of infinite scrolling or infinite paging.

We can also do a mixture where the the offset pagination has a hard limit on how deep it can go, more than that will require using keyset pagination.

Alternatives

No response

Additional information

No response

Start by reviewing the existing offset-based pagination in the api-server and the frontend consumers that depend on it. Compare the proposed keyset approach with the hard-limit alternative, then define the required frontend pagination behavior; done means the API and frontend support a consistent, ordered pagination flow.