Api Pagination Filtering

API Pagination & Filtering

When to Activate

• Adding pagination to a list endpoint (choosing offset vs cursor)
• Implementing filtering with operators (gte, lte, in, after)
• Designing multi-field sort parameters
• Adding full-text search or field-specific search
• Implementing sparse fieldsets (fields=id,name,email)
• Designing the nextcursor / hasnext response envelope for infinite scroll

> For REST URL design, HTTP methods, RFC 7807 errors, auth, rate limiting, and versioning — see skill api-design.

Pagination

Offset-Based (Simple)

GET /api/v1/users?page=2&per_page=20

# Implementation
SELECT * FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET 20;

Pros: Easy to implement, supports "jump to page N" Cons: Slow on large offsets (OFFSET 100000), inconsistent with concurrent inserts

Cursor-Based (Scalable)

GET /api/v1/users?cursor=eyJpZCI6MTIzfQ&limit=20

# Implementation
SELECT * FROM users
WHERE id > :cursor_id
ORDER BY id ASC
LIMIT 21;  -- fetch one extra to determine has_next

{
  "data": [...],
  "meta": {
    "has_next": true,
    "next_cursor": "eyJpZCI6MTQzfQ"
  }
}

Pros: Consistent performance regardless of position, stable with concurrent inserts Cons: Cannot jump to arbitrary page, cursor is opaque

When to Use Which

| Use Case | Pagination Type | |----------|----------------| | Admin dashboards, small datasets (&limit=20 async function listPostsHandler(req, reply) { const limit = Math.min(Number(req.query.limit) || 20, 100); const rawCursor = req.query.cursor as string | undefined;

// Decode opaque cursor → { id, createdAt } const after: CursorPayload | null = rawCursor ? JSON.parse(decodeBase64(rawCursor)) : null;

const rows = await db('posts') .where(function () { if (after) { // Tie-break sort: (createdAt, id) to handle same-timestamp rows this.where('created_at', ' limit; const data = hasNext ? rows.slice(0, limit) : rows;

const lastRow = data.at(-1); const nextCursor = hasNext && lastRow ? encodeBase64(JSON.stringify({ id: lastRow.id, createdAt: lastRow.created_at })) : null;

return reply.send({ data, meta: { hasnext: hasNext, nextcursor: nextCursor }, }); }

**Why tie-break on `(createdAt, id)`:** Sorting by timestamp alone causes rows with identical timestamps to appear in arbitrary order across pages. Adding `id` as a secondary sort key makes the cursor deterministic even under bulk inserts.

## Combined Request — Cursor + Filter + Sort + Sparse Fieldset

A single request using all four features at once:

```http
GET /api/v1/orders?cursor=eyJpZCI6NDIwfQ&limit=10&status=active&created_at[after]=2025-01-01&sort=-total,created_at&fields=id,total,status,customer.name
Authorization: Bearer 

What each parameter does:

| Parameter | Meaning | |-----------|---------| | cursor=eyJpZCI6NDIwfQ | Resume after order id=420 (opaque, base64-encoded) | | limit=10 | Return up to 10 results | | status=active | Filter: only active orders | | created_at[after]=2025-01-01 | Filter: created after Jan 1 2025 | | sort=-total,created_at | Sort by total descending, then created_at ascending | | fields=id,total,status,customer.name | Sparse fieldset — omit heavy fields |

Response:

{
  "data": [
    { "id": "421", "total": 299.99, "status": "active", "customer": { "name": "Alice" } },
    { "id": "430", "total": 149.00, "status": "active", "customer": { "name": "Bob" } }
  ],
  "meta": {
    "has_next": true,
    "next_cursor": "eyJpZCI6NDMwfQ"
  }
}

The client passes next_cursor value as cursor in the next request to get the following page.

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: marvinrichter
• Source: marvinrichter/clarc
• License: MIT
• Homepage: https://marvinrichter.github.io/clarc

Install and usage instructions live in the source repository linked above.