Case Study: Freeze Cursor Stability Rules Before an Agent Writes the List Endpoint

Offset pagination looks complete until concurrent inserts make a catalog list skip rows and duplicate others. You should freeze cursor stability rules, including the sort key and a unique tie-breaker, before any agent writes the list endpoint. This case study walks a small product-catalog API from that frozen contract through tests, a seek query, and a generated handler. The useful result is not prettier SQL; it is a page that stays stable when inventory changes under your readers.

Background: a twenty-product catalog that still breaks

You are shipping a tiny inventory catalog that stores sku, name, quantity, and updated_at for each product. The first agent draft uses ORDER BY name LIMIT 10 OFFSET n because that pattern appears in every tutorial. That draft passes a happy-path test that inserts twenty static rows and walks page one and page two. It fails the moment a merchant adds "Aardvark Adapter" while a buyer is still paging through names.

Offset pagination is a moving window over a live table, not a snapshot of a result set. Concurrent inserts before the current offset push later rows down, so page two skips items the buyer never saw. Concurrent deletes pull later rows up, so page two repeats items the buyer already scanned. You cannot prompt that failure away after the handler exists, because the handler already encoded the wrong contract.

Goal: freeze the page contract before any list SQL

The project goal is deliberately small and easy to review in a single pull request. You will publish GET /products with a page size of ten, a stable next-page token, and no total count. You will refuse client-supplied offsets rather than treating them as a compatibility feature. You will require that two readers who hold the same cursor, at the same committed table state, receive the same next page.

You freeze those rules in a spec file the agent is not allowed to edit. Tests import that spec instead of restating policy in comments. The implementation may change queries, encodings, and variable names as needed. It may not change sort keys, tie-breakers, null order, or error codes once those values are frozen.

Freeze order you actually run

1. Write the decision table for inserts, deletes, equal names, and bad cursors.
2. Encode tests that fail on OFFSET under a concurrent insert.
3. Lock the spec file in review so prompts cannot widen page size or add counts.
4. Allow the agent to touch only the list handler and the cursor codec.
5. Reject any patch that reintroduces offset, silent clamping, or query-string direction.

That order is the whole method. Code generation is step four, not step one, and the tests already know the answer.

The frozen cursor spec

Treat the following contract as source, not as comments inside generated code. If a later prompt wants page=3, you reject the prompt instead of extending the handler.

• Pagination mode: keyset (seek) on (name ASC, id ASC).
• Tie-breaker: id, a unique and immutable product identifier.
• Page size: client may request 1..10; default 10; values above 10 become 400.
• Cursor: opaque base64url of JSON {"n": name, "i": id, "d": "asc"}.
• Empty catalog: 200 with items: [] and next_cursor: null.
• Invalid or truncated cursor: 400 with error code invalid_cursor, never an empty page.
• Unknown id in a well-formed cursor: skip forward from the sort position; do not 404.
• Null names: sort last in ASC and first in DESC, and that choice is frozen.
• Deletes: a product removed after the cursor was issued is omitted; the next live row is returned.
• Total count: not included; adding it later is a separate spec change.

Decision table you pin in review

Situation	Required behavior	Failing shortcut
Insert with a name before the current page	Later pages must not skip already-unseen later names	OFFSET
Insert with a name after the cursor	Next page may include it	Undocumented snapshot-at-request
Two products with the same name	Order by id ascending	ORDER BY name only
Client sends offset=10	Ignore or 400; do not query	Backward-compatible query param
Cursor decoded to DESC while URL says ASC	400 invalid_cursor	Trust the query string
Page size 50	400 page_size_out_of_range	Silent clamp to 10
Malformed base64	400 invalid_cursor	Return the first page

You keep this table next to the tests so review stays mechanical. An agent that "simplifies" any row has failed the task, even if TypeScript compiles and the happy path stays green.

Tests that fail on offset pagination

Write the tests first and keep them in the repository the agent can read. The suite below is a worked example; run it in your own database before you trust the assertions.

// tests/catalog-list.contract.test.ts
// Worked example: unexecuted against your database until you wire a test harness.

import { describe, it, expect, beforeEach } from "vitest";
import { listProducts, seedProducts, insertProduct } from "../src/catalog";

describe("GET /products cursor contract", () => {
  beforeEach(async () => {
    await seedProducts([
      { id: "p01", name: "Bolt" },
      { id: "p02", name: "Clamp" },
      { id: "p03", name: "Drill" },
      { id: "p04", name: "Clamp" }, // same name, later id
    ]);
  });

  it("orders equal names by id, not by insert timing", async () => {
    const page = await listProducts({ pageSize: 10, cursor: null });
    const clamps = page.items.filter((p) => p.name === "Clamp");
    expect(clamps.map((p) => p.id)).toEqual(["p02", "p04"]);
  });

  it("does not skip a later name when an earlier name is inserted", async () => {
    const first = await listProducts({ pageSize: 2, cursor: null });
    expect(first.items.map((p) => p.id)).toEqual(["p01", "p02"]);

    await insertProduct({ id: "p00", name: "Adapter" });

    const second = await listProducts({
      pageSize: 2,
      cursor: first.next_cursor,
    });
    expect(second.items.map((p) => p.id)).toEqual(["p04", "p03"]);
  });

  it("rejects a page size above the frozen cap", async () => {
    await expect(listProducts({ pageSize: 50, cursor: null })).rejects.toMatchObject({
      code: "page_size_out_of_range",
    });
  });

  it("rejects a cursor whose direction does not match the request", async () => {
    const first = await listProducts({ pageSize: 2, cursor: null });
    await expect(
      listProducts({ pageSize: 2, cursor: first.next_cursor, direction: "desc" }),
    ).rejects.toMatchObject({ code: "invalid_cursor" });
  });
});

The second test is the whole point of this case study. Under OFFSET 2, inserting "Adapter" at the front shifts Clamp p04 and Drill p03, and page two skips p04. Under a seek cursor of (name=Clamp, id=p02), the next page still starts after that tuple. You should watch that test fail on the agent's first offset draft, then stay red until the query uses tuple comparison.

Implementation the tests will accept

Once the spec and tests are frozen, the handler is comparatively boring and should stay that way. You encode the last row of a page into an opaque cursor, then resume with a tuple comparison that includes the unique id. Direction lives inside the cursor so a caller cannot mix an ascending token with a descending query string.

// src/cursor.ts
// Worked example: proposed encoding, not a published library.

export type Direction = "asc" | "desc";

export type ProductCursor = {
  n: string | null;
  i: string;
  d: Direction;
};

export function encodeCursor(c: ProductCursor): string {
  return Buffer.from(JSON.stringify(c), "utf8").toString("base64url");
}

export function decodeCursor(token: string, expected: Direction): ProductCursor {
  let parsed: unknown;
  try {
    parsed = JSON.parse(Buffer.from(token, "base64url").toString("utf8"));
  } catch {
    throw Object.assign(new Error("invalid_cursor"), { code: "invalid_cursor" });
  }
  if (
    typeof parsed !== "object" ||
    parsed === null ||
    !("n" in parsed) ||
    !("i" in parsed) ||
    !("d" in parsed)
  ) {
    throw Object.assign(new Error("invalid_cursor"), { code: "invalid_cursor" });
  }
  const cursor = parsed as ProductCursor;
  if (cursor.d !== expected || typeof cursor.i !== "string") {
    throw Object.assign(new Error("invalid_cursor"), { code: "invalid_cursor" });
  }
  return cursor;
}

-- ASC seek; bind :name, :id, :limit from the decoded cursor.
SELECT id, name, quantity
FROM products
WHERE name IS NOT NULL
  AND (
    name > :name
    OR (name = :name AND id > :id)
  )
ORDER BY name ASC, id ASC
LIMIT :limit;

You still need a separate branch for name IS NULL if nulls sort last, because SQL null comparisons will not do that work for you. Freeze that branch in the spec rather than leaving the agent to pick a dialect default. Postgres NULLS LAST can express the rule, but only if the tests assert the actual order, not the presence of a SQL keyword.

Command you run before reviewing the patch

# Worked example: replace with your package scripts.
npx vitest run tests/catalog-list.contract.test.ts

If that file is green and OFFSET is absent from the handler, you have the smallest passing implementation this case study cares about. If the agent added COUNT(*) or a page query param, you revert those lines even when the tests still pass, because they changed the frozen contract.

Where a free coding environment fits

After the spec file and the failing tests exist, you may let a coding agent write the handler and the SQL. Disclosure: This article was prepared as part of MonkeyCode's product outreach. MonkeyCode offers free model access and a free server option, which is enough for this generate-against-tests loop on a small catalog service.

You paste the frozen spec, the decision table, and the test file. You instruct the agent to change only src/catalog.ts and to stop when the contract tests pass. You do not ask it to invent pagination policy, page size, or error codes. If it reintroduces OFFSET, the insert-during-paging test stays red, and you reject the patch.

This is ordinary test-driven work with a narrower file allowlist than most agent demos use. The free model access and free server option are just a place to run that loop without mixing policy into prompts.

Results of the worked example

When the offset draft is scored against the frozen suite, the equal-name test and the insert-during-paging test fail first. After a seek implementation lands, those two tests pass without changing seed data. The page-size and direction-mismatch tests stay valuable because agents often clamp silently or trust the query string over the cursor payload.

You should not treat a green suite as proof of production readiness for a public catalog. The example does not cover replica lag, schema migrations that rewrite ids, or locale-aware name collation. It does prove that the list contract is explicit enough to fail loudly when an agent reaches for offset pagination again.

Limitations and who should skip this approach

Keyset pagination is the wrong default for some products, and you should not force it onto every list.

• You need jump-to-page or "page 17 of 40" in a UI that finance or ops already depends on.
• You cannot add a unique, immutable tie-breaker, so equal sort keys remain unstable.
• You must return a total count on every request and cannot afford a separate count query.
• Your sort column is updated in place, for example a popularity score that moves while users page.
• You are exposing an unauthenticated dump where opaque cursors still leak business order.

Cursor tokens also leak sort values unless you encrypt them. Base64url is opaque to casual readers, not a security boundary. If product names are sensitive, the frozen spec must require a server-side signed payload, which this case study does not implement. You should also avoid this loop when you do not yet know the sort key, because generating SQL before that choice is how offset drafts get merged.

Lessons learned

You should freeze the list contract as data the tests import, not as a paragraph inside a prompt. Offset pagination will keep looking correct in screenshots while skipping rows under concurrent writes. A unique tie-breaker is part of the public API, even when the cursor is opaque, because it decides which row is next. Agents are useful once those rules are already failing tests; they are a poor place to invent pagination policy.

If you want a throwaway environment for that generate-against-tests loop, try MonkeyCode's free model access and free server option on this catalog exercise.