Pagination

The four shapes — offset, cursor/keyset, numbered pages, infinite-scroll — their performance characteristics, and what forge-orm exposes for each. Cursor pagination is the right default for almost everything; this page also covers total-count strategies, Relay/REST response shapes, and per-dialect behavior under load.

Queries deep-dive sketches the cursor API in two paragraphs. This doc is the full reference: every shape with its failure mode, the per-dialect emit forge actually produces, how cursors interact with sort keys / filters / soft-delete, and the response envelopes you wrap them in at the HTTP and GraphQL boundaries.

Contents

• The four shapes — at a glance
• Offset (take + skip) — and why it stops scaling
• Cursor — forge's cursor + take
• Keyset — the raw SQL form
• Numbered pages — when you actually need them
• Total count strategies
• Bidirectional cursors — take: -N
• Sort + cursor — stable keys and tie-breakers
• cursorAll vs cursorSnapshot — consistency under writes
• GraphQL Relay connection mapping
• REST response shapes — body, headers, Link
• Infinite-scroll UX with TanStack Query
• Pagination + filters — invalidation and filter-hashed cursors
• Pagination + soft-delete
• Per-dialect pagination
• Three worked examples
• Common bugs
• See also

---

The four shapes — at a glance

Shape	API surface	Cost on page N	Stable under writes	Random access	Best for
Offset	take + skip	O(N × pageSize)	No	Yes	Stable historical data, admin reports
Cursor (keyset)	cursor + take	O(pageSize)	Yes	No	Feeds, infinite scroll, realtime lists
Numbered pages	take + skip + count	Offset cost plus COUNT(*)	No	Yes — jump-to-page	Small admin tables (< ~100k rows)
Infinite-scroll / load-more	Cursor under a UI veneer	O(pageSize) per fetchNextPage	Yes	No	Mobile feeds, search results

"Best default" is cursor. The rest are special cases.

The performance gap is not theoretical. On Postgres the planner reads and discards every skipped row before returning the first row of the page — OFFSET 10000 LIMIT 20 reads 10020 rows. The same query under a keyset cursor reads 20 rows. By page 500 the offset form is 500× slower than the cursor form on the same index, and the difference is wall-clock time the user is staring at.

---

Offset (take + skip) — and why it stops scaling

The classic form. LIMIT + OFFSET plus an ORDER BY to make the boundaries deterministic.

await db.post.findMany({
  where:   { status: 'PUBLISHED' },
  orderBy: { created_at: 'desc' },
  take:    20,
  skip:    40,                         // page 3 of 20-row pages
});

Per-dialect emit:

Dialect	Emit
Postgres	... ORDER BY "created_at" DESC LIMIT 20 OFFSET 40
MySQL	... ORDER BY `created_at` DESC LIMIT 20 OFFSET 40
SQLite	... ORDER BY "created_at" DESC LIMIT 20 OFFSET 40
DuckDB	... ORDER BY "created_at" DESC LIMIT 20 OFFSET 40
MSSQL	... ORDER BY "created_at" DESC OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY
Mongo	.find(filter).sort([['created_at', -1]]).skip(40).limit(20)

Two distinct failure modes. Cost scales with OFFSET — every engine implements OFFSET as "read the first N rows from the ordered result and throw them away". Two thousand reads per page is fine; twenty thousand is the whole latency budget. On a 100k-row table users will hit pages where the response takes seconds. Non-stable under concurrent writes — offset addresses rows by position, not identity. A new row arriving between page loads bumps every offset by one and the user sees the last row of page 2 again at the top of page 3. Or a row gets skipped entirely.

Use offset when the result set is stable for the session (admin reports against historical data), the dataset is small enough that OFFSET 1000 is not a latency problem, you need jump-to-page and the cost is acceptable, or duplicates and skips are acceptable (audit logs the user is scrolling, not selecting). For anything else, reach for cursor.

---

Cursor — forge's cursor + take

Cursor pagination addresses the boundary by row identity, not position. The next page is "rows whose ordered key is after this row's ordered key", which compiles to an index range scan — sargable on every dialect.

const page1 = await db.post.findMany({
  where: { status: 'PUBLISHED' }, orderBy: { id: 'asc' }, take: 20,
});
const page2 = await db.post.findMany({
  where: { status: 'PUBLISHED' }, orderBy: { id: 'asc' }, take: 20,
  cursor: { id: page1.at(-1)!.id }, skip: 1,    // skip the cursor row itself
});

The IR emits a half-open inequality on the cursor key. On PG with orderBy: { id: 'asc' }:

SELECT … FROM "post"
WHERE "status" = $1 AND "id" > $2
ORDER BY "id" ASC LIMIT 20;

That "id" > $2 is sargable on the primary key index. The page is O(pageSize) regardless of how deep the user has scrolled — no skipped rows, no boundary drift under writes, no COUNT(*) round-trip.

cursor accepts a unique-by selector — { id }, anything declared .unique(), or a compound-unique key. Forge validates this at build time the same way findUnique does; passing a non-unique field throws.

skip: 1 is the usual partner. Forge uses the inclusive form under the hood (so you can resume from a cursor that no longer exists) and skip: 1 peels the duplicate off when you have the cursor row in hand.

The trade-off is no random access — no "page 47" because there is no notion of position. The UI either pages forward / back from a known cursor, or it gives up the requirement.

---

Keyset — the raw SQL form

"Cursor pagination" and "keyset pagination" are the same idea — the forge cursor API is a typed wrapper on the keyset technique. When you drop to $queryRaw (recursive CTEs, window functions), the pattern is:

-- pageSize = 20, lastSeen = (createdAt, id) of the previous page's last row
SELECT id, title, created_at
FROM post
WHERE status = $1
  AND (created_at, id) < ($lastCreatedAt, $lastId)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The composite tuple (created_at, id) is the keyset; the comparison is the boundary. The trailing id is the tie-breaker — created_at alone is not unique, so without id the page boundary is ambiguous when two posts share a timestamp.

Per-dialect emit of the tuple form:

Dialect	Emit
Postgres	("created_at", "id") < ($1, $2) — true row-value compare
SQLite	("created_at", "id") < (?, ?) — supported since 3.15
DuckDB	("created_at", "id") < ($1, $2)
MySQL	("created_at", "id") < (?, ?) — supported since 5.7
MSSQL	Not supported. Expanded to ("created_at" < @p1 OR ("created_at" = @p1 AND "id" < @p2))
Mongo	Not supported. Expanded to { $or: [{ created_at: { $lt: a } }, { $and: [{ created_at: a }, { id: { $lt: b } }] }] }

The expanded form is more verbose but the planner sees the same range scan on every dialect — provided you have a compound index on (created_at, id). Without it the OR-of-AND form falls back to a sequential scan and the page-N pattern is as slow as offset. See INDEXES for the declaration:

const post = f.model('post', { /* … */ }, {
  indexes: [{ fields: ['created_at', 'id'] }],
});

Leading column matches the leading orderBy key. Putting id first would not help — the range scan needs created_at first to be selective.

---

Numbered pages — when you actually need them

Some surfaces genuinely require numbered pagination — admin tables where "page 47 of 312" is a legitimate affordance, exports where the user needs a deterministic position.

const PAGE_SIZE = 25;

async function adminUsersPage(pageNum: number) {
  const [rows, total] = await Promise.all([
    db.user.findMany({
      orderBy: { created_at: 'desc' },
      take:    PAGE_SIZE,
      skip:    (pageNum - 1) * PAGE_SIZE,
    }),
    db.user.count(),
  ]);

  return {
    rows,
    pageInfo: {
      page:        pageNum,
      pageSize:    PAGE_SIZE,
      totalRows:   total,
      totalPages:  Math.ceil(total / PAGE_SIZE),
      hasPrevPage: pageNum > 1,
      hasNextPage: pageNum * PAGE_SIZE < total,
    },
  };
}

Works when:

• The total is small enough that OFFSET stays sub-millisecond (rule of thumb: under 50k rows, or under 5k with a heavy projection).
• The user genuinely needs to know how many pages there are.
• Drift between page loads is acceptable.

Past those limits, switch to cursor and drop the page numbers.

---

Total count strategies

The COUNT(*) query that fills the "of ???" half of "page 47 of ???" is its own performance story. Four strategies, in order of preference.

1. Exact count via db.x.count().

const total = await db.user.count({ where: { active: true } });

PG / MySQL / SQLite / DuckDB / MSSQL emit SELECT COUNT(*) FROM "user" WHERE …. Mongo uses countDocuments. Cost scales with the result set; on a 10M row table the count itself is the slow request. Always run count and findMany in parallel — most ORMs serialise them and double latency:

const [rows, total] = await Promise.all([
  db.user.findMany({ where, take: 25, skip: page * 25 }),
  db.user.count({ where }),
]);

2. Estimated count from pg_class.reltuples (Postgres). For unfiltered or very common-filter counts on huge tables, the planner estimate is the difference between "loads in 2 ms" and "loads in 800 ms".

const [{ estimate }] = await db.$queryRaw<{ estimate: number }[]>`
  SELECT reltuples::bigint AS estimate
  FROM pg_class WHERE relname = 'user';
`;

Updated by autovacuum + ANALYZE; wrong by up to a few percent on a busy table. For "about 12,000 users" precision is more than enough.

DuckDB exposes duckdb_tables; MySQL has INFORMATION_SCHEMA.TABLES.TABLE_ROWS (also estimated); SQLite has no equivalent. For filtered PG estimates wrap the query in EXPLAIN (FORMAT JSON) and read the Plan Rows field — the trick GitLab and Discourse use for huge filtered counts.

3. Cached count. For totals that change slowly relative to the read rate, cache and invalidate on writes. For tenanted counts, hook into model events (EVENTS) and increment / decrement:

db.$on('user.created',     ({ org_id }) => cache.incr(`count:user:${org_id}`));
db.$on('user.softDeleted', ({ org_id }) => cache.decr(`count:user:${org_id}`));

The cached value drifts under cache-miss scenarios — refresh on a schedule to bound the drift. Billing surfaces should always do the live count.

4. The "is there a next page?" shortcut. Cursor pages do not need a total — hasNextPage only needs to know whether any row exists beyond the current page. Read one extra row past the page size:

const PAGE_SIZE = 20;
const rows = await db.post.findMany({
  where,
  orderBy: { id: 'asc' },
  take:    PAGE_SIZE + 1,              // ask for one extra
  ...(cursor ? { cursor: { id: cursor }, skip: 1 } : {}),
});

const hasNextPage = rows.length > PAGE_SIZE;
const visible     = rows.slice(0, PAGE_SIZE);
const nextCursor  = hasNextPage ? visible[PAGE_SIZE - 1].id : null;

That extra row is one index read; COUNT(*) over the same filter is potentially every index read.

---

Bidirectional cursors — take: -N

take accepts a negative value. The result is the previous page relative to the cursor — same boundary, opposite direction. The compiler flips the predicate (< $cursor), flips the ORDER BY, and re-reverses the result array so the caller sees rows in the original sort order.

await db.post.findMany({
  orderBy: { id: 'asc' },
  take:    -20,
  cursor:  { id: lastSeenId },
  skip:    1,
});

For a feed with up and down arrows, carry two cursors — firstCursor and lastCursor. "Next page" is cursor: { id: lastCursor }, skip: 1, take: PAGE_SIZE + 1; "previous" is cursor: { id: firstCursor }, skip: 1, take: -(PAGE_SIZE + 1). hasNextPage and hasPrevPage come from the extra-row trick in both directions.

Mongo does not support negative take natively — the adapter reverses the sort and re-reverses the result in code. Cost on a covered sort is identical, but the cursor stage reads in the opposite direction.

---

Sort + cursor — stable keys and tie-breakers

The single most common cursor bug is "the sort key is not unique and the cursor is not stable". A cursor on created_at alone duplicates or skips rows whenever two rows share a timestamp — and on a busy table they will.

The rule. The cursor key must be unique. If the visible sort key is not unique, pair it with a unique tie-breaker (almost always id) and use a single-column cursor on id with a multi-columnorderBy:

await db.post.findMany({
  orderBy: [{ created_at: 'desc' }, { id: 'desc' }],
  take:    20,
  cursor:  { id: lastSeenId },
  skip:    1,
});

Forge translates this on PG/SQLite/DuckDB/MySQL to the tuple form; on MSSQL/Mongo to the expanded OR-of-AND form. Either way the compound index on (created_at, id) makes the range scan covered.

Compound cursor. When the unique key is itself compound — (user_id, video_id) on a watched table — pass the whole key:

await db.watched.findMany({
  orderBy: [{ watched_at: 'desc' }, { user_id: 'asc' }, { video_id: 'asc' }],
  take:    50,
  cursor:  { user_id_video_id: { user_id: 'u1', video_id: 'v9' } },
  skip:    1,
});

The compound-unique selector must match a unique constraint declared on the model — same rule as findUnique.

Sorting on a relation field. orderBy: { author: { name: 'asc' } } is documented at the README surface but the IR builder silently drops it today (same gap as in QUERIES). Denormalise the sort key onto the row or fall back to $queryRaw.

---

cursorAll vs cursorSnapshot — consistency under writes

Cursor pagination is stable under writes for the boundary. The question is what guarantee you want for the rows between boundaries.

cursorAll — see writes as they happen. The default. Each page is a fresh findMany against current data. Rows inserted between page 1 and page 2 will appear on subsequent visits to page 1, but not retroactively on page 2. Use for feeds, search, anything where freshness is the desired behaviour.

cursorSnapshot — fix the result set at session start. Some surfaces (data exports, paginated batch jobs, "back" navigation on search results) want the page set fixed at the moment the first page is fetched. Capture a timestamp on first request, round-trip it through each subsequent page (in the URL, query params, or cursor blob), and apply it as a filter:

const snapshotAt = new Date();
const where = { created_at: { lte: snapshotAt } };

const p1 = await db.post.findMany({ where, orderBy, take: 20 });
const p2 = await db.post.findMany({
  where, orderBy, take: 20,
  cursor: { id: p1.at(-1)!.id }, skip: 1,
});

The created_at <= snapshotAt filter freezes the result set against inserts. For delete stability, soft delete is the answer (see Pagination + soft-delete). Hard-deleted rows simply disappear and there is no per-row way to keep them in a snapshot. Forge does not expose a built-in cursorSnapshot mode — it is a pattern over findMany, not a separate call.

---

GraphQL Relay connection mapping

The Relay cursor connection spec is the canonical GraphQL pagination shape — edges { cursor node }, pageInfo { hasNextPage hasPreviousPage startCursor endCursor }, and the first / after / last / before argument quartet. It maps cleanly onto forge cursors.

async function postsResolver(_: unknown, args: {
  first?: number; after?:  string;
  last?:  number; before?: string;
}) {
  const PAGE_SIZE = args.first ?? args.last ?? 20;
  const forward   = args.last == null;
  const opaque    = forward ? args.after : args.before;
  const cursor    = opaque ? decodeCursor(opaque) : undefined;

  const rows = await db.post.findMany({
    orderBy: [{ created_at: 'desc' }, { id: 'desc' }],
    take:    forward ? PAGE_SIZE + 1 : -(PAGE_SIZE + 1),
    ...(cursor ? { cursor: { id: cursor }, skip: 1 } : {}),
  });

  const hasMore = rows.length > PAGE_SIZE;
  const visible = rows.slice(0, PAGE_SIZE);
  return {
    edges: visible.map(p => ({ cursor: encodeCursor(p.id), node: p })),
    pageInfo: {
      hasNextPage:     forward  ? hasMore : Boolean(args.after),
      hasPreviousPage: !forward ? hasMore : Boolean(args.before),
      startCursor: visible.length ? encodeCursor(visible[0].id)        : null,
      endCursor:   visible.length ? encodeCursor(visible.at(-1)!.id)   : null,
    },
  };
}

const encodeCursor = (id: string) => Buffer.from(id).toString('base64');
const decodeCursor = (c:  string) => Buffer.from(c, 'base64').toString();

The cursor is an opaque base64 string from the client's perspective — the Relay convention. If you ever need to switch from id-cursor to a compound (created_at, id)-cursor, encode a JSON blob inside the base64 and clients see the same opaque string. totalCount is optional in the Relay spec; set it only when the UI needs it.

---

REST response shapes — body, headers, Link

Three patterns are common; pick one and stick to it across the API.

1. Cursor in the body (recommended default). Self-describing, no header inspection, works through proxies that strip headers. Mirrors the GraphQL connection shape so client code is uniform.

{ "data": [ ... ], "pageInfo": { "hasNextPage": true, "hasPreviousPage": false, "nextCursor": "abc123", "prevCursor": null } }

async function listPosts(req: Request, res: Response) {
  const PAGE_SIZE = 20;
  const after = req.query.after as string | undefined;

  const rows = await db.post.findMany({
    where:   { status: 'PUBLISHED' },
    orderBy: [{ created_at: 'desc' }, { id: 'desc' }],
    take:    PAGE_SIZE + 1,
    ...(after ? { cursor: { id: after }, skip: 1 } : {}),
  });

  const hasNext = rows.length > PAGE_SIZE;
  const visible = rows.slice(0, PAGE_SIZE);

  res.json({
    data: visible,
    pageInfo: {
      hasNextPage: hasNext,
      hasPreviousPage: Boolean(after),
      nextCursor: hasNext ? visible.at(-1)!.id : null,
      prevCursor: after   ? visible[0]?.id     : null,
    },
  });
}

2. Link header (RFC 5988, GitHub-style). Link: <…?after=abc123>; rel="next", <…?before=xyz789>; rel="prev" plus X-Total-Count: 12483. HATEOAS — the client follows hrefs without knowing the cursor format. Downsides: header parsing is annoying, some proxies strip non-standard headers, many clients ignore Link and read the body anyway.

3. Page number in the body (numbered pages). { data, pageInfo: { page, pageSize, totalRows, totalPages } }. Only when you really need numbered pages.

Cursor opacity. Raw id is simplest but anyone who knows your id format can synthesise cursors. Base64 JSON is opaque and easy to evolve (add fields). Signed JWT is tamper-proof — use when the cursor encodes filter state the client must not modify.

---

Infinite-scroll UX with TanStack Query

useInfiniteQuery from TanStack Query is the canonical client-side shape. The forge cursor maps to pageParam:

'use client';
import { useInfiniteQuery } from '@tanstack/react-query';
import { getDb } from '@/db';

export function PostFeed({ orgId }: { orgId: string }) {
  const q = useInfiniteQuery({
    queryKey: [orgId, 'post', 'feed'],
    initialPageParam: undefined as string | undefined,
    queryFn: async ({ pageParam }) => {
      const db = await getDb();
      const rows = await db.post.findMany({
        where:   { org_id: orgId, status: 'PUBLISHED', deleted_at: null },
        orderBy: [{ created_at: 'desc' }, { id: 'desc' }],
        take:    21,                     // 20 + 1 for hasNext
        ...(pageParam ? { cursor: { id: pageParam }, skip: 1 } : {}),
      });
      const hasNext = rows.length > 20;
      const visible = rows.slice(0, 20);
      return { rows: visible, nextCursor: hasNext ? visible[19].id : undefined };
    },
    getNextPageParam: (p) => p.nextCursor,
  });

  return (
    <>
      {q.data?.pages.flatMap(p => p.rows).map(p => <PostCard key={p.id} post={p} />)}
      {q.hasNextPage && (
        <button onClick={() => q.fetchNextPage()} disabled={q.isFetchingNextPage}>
          {q.isFetchingNextPage ? 'Loading…' : 'Load more'}
        </button>
      )}
    </>
  );
}

See React patterns for the search-as-you-type variant with useDeferredValue + useTransition.

For "auto-load on scroll-to-bottom", wire an intersection observer to fetchNextPage and throttle aggressively — without it, fast scrolls trigger multiple page loads before the first one resolves.

---

Pagination + filters — invalidation and filter-hashed cursors

Cursors are stable under writes. They are not stable under filter changes — a cursor anchored at row abc in the unfiltered list points nowhere meaningful in the filtered list, because the filtered list may not contain abc at all.

Reset to head on filter change. Simplest and almost always right. When the filter changes, drop the cursor and refetch from the first page. With TanStack Query, putting the filter in the queryKey does this for free — changing the filter mounts a fresh query starting at initialPageParam: undefined.

const [filter, setFilter] = useState('all');
const q = useInfiniteQuery({
  queryKey: [orgId, 'post', 'feed', filter],   // filter in the key
  // …
});

Filter hash inside the cursor. When the cursor must round-trip through stateless URLs (a paginated public list with shareable links), encode the filter state inside the cursor so the server can reject mismatched filter + cursor combinations:

function encodeCursor(rowId: string, filterHash: string) {
  return Buffer.from(JSON.stringify({ id: rowId, f: filterHash })).toString('base64url');
}

function decodeCursor(c: string, expectedFilterHash: string) {
  const { id, f } = JSON.parse(Buffer.from(c, 'base64url').toString());
  if (f !== expectedFilterHash) throw new Error('Filter mismatch — cursor invalid');
  return id;
}

The hash is a stable digest of the filter object. The server recomputes it and rejects mismatched pairs, so a user who keeps a cursor in a bookmark and changes the filter on the page gets a 400 rather than a confusing result. For signed cursors, swap the hash for a server-signed JWT and reject unsigned cursors.

---

Pagination + soft-delete

Two ways soft-deleted rows interact with cursor pagination.

The cursor row gets deleted. Fine — the cursor is the boundary, not a member of the result. The next page starts at the first row whose key is strictly greater than the cursor row, independent of whether the cursor row still exists. Forge's default WHERE "deleted_at" IS NULL filters out deleted rows and the cursor inequality is applied independently.

The compound index that backs the cursor should be partial on deleted_at IS NULL — otherwise the index includes deleted rows, the scan reads them, and the filter discards them downstream:

const post = f.model('post', { /* … */ }, {
  indexes: [{ fields: ['created_at', 'id'], where: { deleted_at: null } }],
});

See Soft delete partial-filter indexes for the per-dialect emit. PG/SQLite/Mongo support partial indexes natively; MySQL needs the generated-column workaround.

---

Per-dialect pagination

The pagination predicates and limits compile differently on each engine. Behaviour is uniform from the caller's perspective; the wire-level cost and edge cases differ.

Postgres. LIMIT 20 OFFSET 40 for offset; WHERE "id" > $1 ORDER BY "id" LIMIT 20 for cursor; row-value tuple ("created_at", "id") < ($1, $2) for the compound form. ROW_NUMBER() over a window is sometimes proposed as an offset workaround — it is not faster, the planner still materialises every row up to the offset.

MySQL. Same LIMIT … OFFSET … shape. 5.7+ supports row-value tuples; 5.6 does not and forge expands to the OR-of-AND form. The deep-offset problem is worse than on PG — InnoDB secondary indexes carry the primary key as the leaf value, and OFFSET over a secondary index walks both. Switch to cursor sooner.

SQLite. Same LIMIT … OFFSET … shape. Tuple compare since 3.15 (2016), so every supported runtime including the sqlite-wasm browser adapter has it. OFFSET on SQLite is cheaper than PG/MySQL (in-process b-tree walk) but the scan cost is still O(skip + take).

DuckDB. DuckDB is a column store; OFFSET on a sorted column is sub-millisecond even at depth, because the engine seeks into the sorted chunk directly. Cursor still wins for stable boundaries under writes, but the deep-offset concern is less severe.

MSSQL. OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY — ORDER BY is mandatory; MSSQL refuses without it. Tuple compare is not supported; forge expands cursors to the OR-of-AND form. The planner handles it well when the compound index is declared.

Mongo. .find(filter).sort(...).skip(40).limit(20) for offset; { _id: { $gt: lastId } } filter for cursor; OR-of-AND expansion for compound cursor. .skip() has the same trap as SQL OFFSET — and slower per row because of BSON deserialisation. hint() is sometimes necessary to force the index (the planner occasionally picks a covered sort over the cursor index); the forge adapter accepts a hint option on findMany. See MONGO.

---

Three worked examples

(a) Timeline cursor with (created_at, id)

Forward-paged feed with a non-unique sort key and a unique tie-breaker — the pattern under almost every "list of recent things".

async function postTimeline(input: { orgId: string; cursor?: string; limit?: number }) {
  const PAGE_SIZE = input.limit ?? 20;

  const rows = await db.post.findMany({
    where:   { org_id: input.orgId, status: 'PUBLISHED', deleted_at: null },
    orderBy: [{ created_at: 'desc' }, { id: 'desc' }],
    take:    PAGE_SIZE + 1,
    ...(input.cursor ? { cursor: { id: input.cursor }, skip: 1 } : {}),
    select: { id: true, title: true, created_at: true, author_id: true },
  });

  const hasNextPage = rows.length > PAGE_SIZE;
  const visible     = rows.slice(0, PAGE_SIZE);
  return {
    data: visible,
    pageInfo: { hasNextPage, nextCursor: hasNextPage ? visible.at(-1)!.id : null },
  };
}

Backing index — leading column is the multi-tenant scope, then the in-filter equality, then the sort tuple:

const post = f.model('post', { /* … */ }, {
  indexes: [{
    fields: ['org_id', 'status', 'created_at', 'id'],
    where:  { deleted_at: null },
  }],
});

PG and SQLite emit a true partial index; MySQL falls back to the generated-column workaround (SOFT-DELETE).

(b) Admin table with numbered pages + count cache

Numbered pagination over a slowly-growing table. Count cached with event-driven invalidation.

import { lru } from 'tiny-lru';
const userCountCache = lru<number>(1000, 60_000);

db.$on('user.created',     (u) => userCountCache.set(`count:${u.org_id}`,
  (userCountCache.get(`count:${u.org_id}`) ?? 0) + 1));
db.$on('user.softDeleted', (u) => userCountCache.set(`count:${u.org_id}`,
  Math.max(0, (userCountCache.get(`count:${u.org_id}`) ?? 0) - 1)));

async function adminUsersPage(orgId: string, page: number) {
  const PAGE_SIZE = 25;

  let total = userCountCache.get(`count:${orgId}`);
  if (total == null) {
    total = await db.user.count({ where: { org_id: orgId } });
    userCountCache.set(`count:${orgId}`, total);
  }

  const rows = await db.user.findMany({
    where:   { org_id: orgId },
    orderBy: [{ created_at: 'desc' }, { id: 'desc' }],
    take:    PAGE_SIZE,
    skip:    (page - 1) * PAGE_SIZE,
  });

  return {
    data: rows,
    pageInfo: {
      page, pageSize: PAGE_SIZE,
      totalRows:  total,
      totalPages: Math.ceil(total / PAGE_SIZE),
    },
  };
}

For a 50k-user tenant the OFFSET 1225 on page 50 is sub-millisecond on PG. Past ~500k rows per tenant, switch to cursor and drop the "page 50" affordance.

(c) Relay-style GraphQL connection

The resolver from GraphQL Relay connection mapping extended with multi-tenant scope and soft-delete:

posts: async (_, args: {
  first?: number; after?:  string;
  last?:  number; before?: string;
  orgId:  string;
}) => {
  const PAGE_SIZE = args.first ?? args.last ?? 20;
  const forward   = args.last == null;
  const opaque    = forward ? args.after : args.before;
  const cursor    = opaque ? decodeCursor(opaque) : undefined;

  const rows = await db.post.findMany({
    where:   { org_id: args.orgId, status: 'PUBLISHED', deleted_at: null },
    orderBy: [{ created_at: 'desc' }, { id: 'desc' }],
    take:    forward ? PAGE_SIZE + 1 : -(PAGE_SIZE + 1),
    ...(cursor ? { cursor: { id: cursor }, skip: 1 } : {}),
  });

  const hasMore = rows.length > PAGE_SIZE;
  const visible = rows.slice(0, PAGE_SIZE);
  return {
    edges: visible.map(p => ({ cursor: encodeCursor(p.id), node: p })),
    pageInfo: {
      hasNextPage:     forward  ? hasMore : Boolean(args.after),
      hasPreviousPage: !forward ? hasMore : Boolean(args.before),
      startCursor: visible.length ? encodeCursor(visible[0].id)        : null,
      endCursor:   visible.length ? encodeCursor(visible.at(-1)!.id)   : null,
    },
  };
};

The cursor is the raw row id, base64-encoded. Swap to a JSON blob if you need a compound cursor — clients see the same opaque string.

---

Common bugs

Cursor on a non-unique column. cursor: { created_at: lastSeen } on a non-unique created_at throws at build time. Fix: sort by (created_at, id), cursor by id.

take + skip for "jump to page 1000". By page 50 the response triples; by page 1000 it is unusable. There is no fix for jump-to-page on a large table — accept the cost (small table), denormalise a page_number column at write time (awkward, fragile under deletes), or drop random-access and switch to cursor.

Cursor reused after filter change. A cursor anchored under filter A points nowhere under filter B. The visible bug is "I changed the filter and the page is empty or in the wrong order". Either reset to head on filter change (client-side queryKey trick) or include a filter hash in the cursor and reject mismatches server-side.

count + findMany serialised. Both queries hit the same pool — run them concurrently with Promise.all. Most ORMs serialise the two and double the latency by default.

Mongo .skip() at depth. Reads and discards every skipped document, slower per row than SQL OFFSET because of BSON deserialisation. skip: 10000 on a few-million-doc collection is hundreds of milliseconds. Cursor is the only workable answer at scale.

Forgetting skip: 1 on the cursor row. Without it, the inclusive boundary includes the cursor row at the top of the next page and the user sees a duplicate. Always pair cursor with skip: 1 unless you specifically want the cursor row included.

Tuple compare on MSSQL/Mongo without the compound index. The expanded OR-of-AND form runs without a compound index, but the planner falls back to a sequential scan and the page load is O(n). Always declare the compound index on tables you paginate.

Offset + soft-delete double-cost. Without a partial index filtered on deleted_at IS NULL, the engine reads every skipped row including deleted ones, then filters them out post-scan. Declare the partial index from SOFT-DELETE — or switch to cursor.

---

See also

• Queries deep-dive — findMany operator reference, where / orderBy shapes, and the original cursor sketch.
• React patterns — TanStack Query useInfiniteQuery with debounced search.
• Backend patterns — HTTP envelopes, ETag, idempotency.
• Indexes — compound and partial indexes that back stable cursor pagination.
• Soft delete — partial indexes filtered on deleted_at IS NULL.
• Streaming — findManyStream for "paginate forever".
• Mongo — hint() and cursor-stage notes.
• MSSQL — OFFSET FETCH NEXT and tuple-compare expansion.