# Paginating admin tables without a client library

> Offset pages with a total for the users table, keyset cursors for the audit log, both in the URL and rendered on the server. When to use which, and the details that make each correct.

An admin table starts as `select * from users`. It is fine for months. Then a
page load takes nine seconds and the browser holds forty thousand rows, and
the first fix people reach for is a table library with client-side paging.

You do not need one. Pagination is a `limit`, a `where` and a link. The only
real decision is offset or keyset, and an admin panel needs both.

## Users: offset pages with a total

```sql
select id, email, name, role, created_at
from "user"
where email ilike $1 or name ilike $1
order by created_at desc, id desc
limit 20 offset 40;                               -- page 3

select count(*) from "user" where email ilike $1 or name ilike $1;
```

Offset is usually the wrong answer, and here it is the right one:

- **Operators expect "41 to 60 of 134"** and page numbers. They jump to page
  7, share a link to it, and read the total as a fact about the business.
- **Hosted auth providers only page by offset.** Clerk's `getUserList` takes
  `limit` and `offset` and returns `totalCount`. If one provider can only do
  offset, the shared UI does offset.
- **The table is small and people search.** A users table in the tens of
  thousands, filtered by a search box, never reaches the depths where
  `OFFSET` gets slow. Cap the page number anyway (10,000 is a hand-edited URL,
  not a person) so nobody can ask for a slow query on purpose.

Two details make it correct:

- **A unique tiebreaker.** `order by created_at desc, id desc`. Without `id`,
  two accounts created in the same instant swap places between requests and
  one of them never shows.
- **The same `where` for rows and count.** Build it once and use it twice.
  A total that counts something the rows do not is a bug report waiting to be
  filed.

Search with `ILIKE` and escape the input first: `%`, `_` and `\` are
wildcards, so a search for `dara_x` also finds `daraYx`. Check what your ORM
does. Drizzle's `ilike()` passes your pattern through as written; Prisma's
`contains` with `mode: "insensitive"` adds the `%` but escapes nothing.

## The audit log: keyset cursors

The audit log is the opposite case: it only grows, it gets big, people read
the newest entries, and new rows arrive while someone pages. Offset is wrong
twice over there: cost grows with depth, and a row inserted at the top shifts
every page, so entries repeat or vanish.

```sql
select id, action, actor_id, entity_id, metadata, created_at
from audit_log
where (created_at, id) < ($1::timestamptz, $2::uuid)   -- the cursor
order by created_at desc, id desc
limit 51;                                              -- one extra: is there more?
```

- The row comparison `(created_at, id) < (...)` is one condition Postgres can
  answer from an index on `created_at`.
- Fetch `limit + 1` rows to know whether to show "Older entries" without a
  `count(*)`, which on a large log costs more than the page.
- **Carry the timestamp as text with microseconds.** Postgres stores
  `timestamptz` to the microsecond; a JavaScript `Date` keeps milliseconds. A
  cursor built from a `Date` skips every row written in the same millisecond
  as the last one shown. Select `to_char(created_at at time zone 'UTC',
  'YYYY-MM-DD"T"HH24:MI:SS.US"Z"')` for the cursor.
- Keyset gives "Older" and "Newest", not page numbers. For a log that is
  right: nobody wants page 212, they filter.

## State lives in the URL

```
/admin/users?q=acme&role=admin&page=3
/admin/audit?action=admin.user.&person=ana%40x.io&cursor=MjAyNi0...
```

- The page stays a Server Component with no client state.
- A filtered view is a link you can paste into a ticket, and the back button
  works.
- Changing a filter resets to page one. A cursor or page number is only valid
  for the filter that produced it.
- Every param is hostile input. Parse with a schema and fall back to "no
  filter" on anything odd: a mangled link should show page one, not a 500.
  Encode the cursor (base64url of `timestamp|id`) and validate both halves, so
  a forged value never reaches a `::uuid` cast.
- Use real links for pages. They work without JavaScript, open in a new tab
  and prefetch.

## Checking your work

- `explain analyze` on the audit query with a deep cursor: an index scan and
  51 rows, not a sequential scan.
- Insert audit rows while paging: no duplicate, no gap.
- A search for `_` or `%` matches only those characters.
- `?page=abc`, `?page=-1` and a truncated cursor all render the first page.
- The total under the users table matches the rows when you page to the end.

---

Agentic Boilerplate: A Next.js repo your agent already knows. $99 once. Lifetime access and updates.

- Site map for agents: https://agenticboilerplate.com/llms.txt
- Public API: https://agenticboilerplate.com/openapi.json
- Contact: agenticstudio@gmail.com