Use keyset pagination instead of offset

[ThisIsMissEm]

Currently Adonis's built in paginate method relies on offset pagination, which means pagination cursors aren't stable. This can cause issues in annotation collections which are frequently updated, as you'll miss items.

Keyset pagination is the solution.

• https://taylorbrazelton.com/2019/03/13/2019-03-13-offset-vs-seek-keyset-pagination/
• https://use-the-index-luke.com/sql/partial-results/fetch-next-page

Essentially instead of:

SELECT * FROM "annotations" WHERE collection_id = decode(?, 'hex') LIMIT ? OFFSET ?

We do:

SELECT * FROM "annotations" WHERE collection_id = decode(?, 'hex') AND id > ? ORDER BY id DESC LIMIT ?

It does change the pagination URLs too, from:

/annotations/781bc04ec9bd049cdfacaec4ae2026102118d13c79413246f0c88eeef6ebec4e?page=1

to:

/annotations/781bc04ec9bd049cdfacaec4ae2026102118d13c79413246f0c88eeef6ebec4e?min_id=0192e517-1e93-742d-a8bf-e8f491c570da

or

/annotations/781bc04ec9bd049cdfacaec4ae2026102118d13c79413246f0c88eeef6ebec4e?max_id=0192e517-1e93-742d-a8bf-e8f491c570da

[ThisIsMissEm]

We can also rely on sorting with the ID column, since that's a UUID v7 which is time-orderable.