The Agent That Trusted OFFSET on a Live Table: A Take-Home Packet

Offset pagination looks correct in every empty-table unit test and still loses rows in production. A concurrent insert or delete shifts the remaining window, so later pages skip or duplicate records. This take-home packet grades an export endpoint against a writer that mutates the table during pagination. Agents that treat LIMIT and OFFSET as a stable cursor fail the suite even when the happy path is green.

The failure this packet is built to catch

Most take-home exports start with SQL that looks textbook-clean and still fails under a concurrent writer. OFFSET n LIMIT k means skip the current first n rows, not skip the same n keys seen before. An insert at the front of the sort order pushes an unseen row into the skipped window.

A delete in an earlier page pulls a later row forward, so the next page emits a duplicate. Empty-table tests never observe either shift, which is why agents ship the bug with a green local run. The second trap is a created_at cursor without an id tie-breaker, which collapses when many rows share one timestamp.

Hiring loops that only assert page length will score those patches as passes. The packet below hides a concurrent mutator and a tied-timestamp fixture behind public tests that look ordinary. Reviewers should score the hidden suite, not the chat explanation of pagination theory.

Take-home prompt (candidate-visible)

Provide this prompt verbatim. Do not mention the hidden writer or the tied timestamps in the public text.

Repository goal. Build GET /exports/orders that returns every remaining order exactly once across pages.

Public contract.

• Query params: limit (integer 1–100, default 50) and optional cursor.
• Response JSON: { items: Order[], nextCursor: string | null }.
• Order fields: id (string), createdAt (ISO-8601), amountCents (integer), status (open | paid | void).
• Ordering must be deterministic across process restarts and across identical timestamps.
• The handler must not load the entire table when the caller paginates.

Public tests the candidate can run. Seed 120 orders with distinct timestamps, page with limit=25, and assert concatenation equals the seed set. Missing pages, overlapping ids, or a null nextCursor too early fail this visible suite.

Out of scope. Authentication, filtering, arbitrary sort keys, and jump-to-page-N are not required. Snapshot isolation at the storage layer is not required either, and the notes should not pretend that it is.

Hidden fixture the candidate should not see

The grader starts a writer after the first page returns. That writer inserts three orders at the low end of the sort order and deletes two orders that already appeared on page one. All seed rows in the hidden file share the same createdAt value, with uniqueness only in id.

The public seed uses strictly increasing timestamps so OFFSET implementations survive the visible tests. The hidden seed removes that crutch on purpose, which is the actual measurement. Reviewers who leak this fixture into the prompt will grade prompt leakage instead of pagination.

// test/hidden-writer.js
export function installHiddenWriter(store) {
  const TIE = "2026-09-17T10:00:00.000Z";
  for (let i = 0; i < 80; i++) {
    store.insert({
      id: `ord_${String(i).padStart(4, "0")}`,
      createdAt: TIE,
      amountCents: 100 + i,
      status: "open",
    });
  }
  return {
    afterFirstPage(seenIds) {
      store.insert({
        id: "ord_low_a",
        createdAt: TIE,
        amountCents: 1,
        status: "open",
      });
      store.insert({
        id: "ord_low_b",
        createdAt: TIE,
        amountCents: 2,
        status: "paid",
      });
      store.insert({
        id: "ord_low_c",
        createdAt: TIE,
        amountCents: 3,
        status: "open",
      });
      for (const id of [...seenIds].slice(0, 2)) {
        store.remove(id);
      }
    },
  };
}

Reproducible grader

Save the following files and run node --test test/export.test.js after the patch lands. The in-memory store keeps the packet free of database installs, so a laptop or CI job can execute it. Production systems still need an index on (created_at, id); this fixture only encodes the cursor contract.

// src/store.js
export function createStore() {
  const rows = new Map();
  return {
    insert(row) {
      if (rows.has(row.id)) throw new Error("duplicate id");
      rows.set(row.id, { ...row });
    },
    remove(id) {
      rows.delete(id);
    },
    list() {
      return [...rows.values()];
    },
  };
}

// src/export-keyset.js
function decodeCursor(raw) {
  if (!raw) return null;
  try {
    const parsed = JSON.parse(Buffer.from(raw, "base64url").toString("utf8"));
    if (typeof parsed.t !== "string" || typeof parsed.id !== "string") return null;
    return parsed;
  } catch {
    return null;
  }
}

function encodeCursor(row) {
  return Buffer.from(
    JSON.stringify({ t: row.createdAt, id: row.id }),
    "utf8",
  ).toString("base64url");
}

function cmp(a, b) {
  if (a.createdAt < b.createdAt) return -1;
  if (a.createdAt > b.createdAt) return 1;
  if (a.id < b.id) return -1;
  if (a.id > b.id) return 1;
  return 0;
}

function afterCursor(row, cursor) {
  if (!cursor) return true;
  if (row.createdAt > cursor.t) return true;
  if (row.createdAt < cursor.t) return false;
  return row.id > cursor.id;
}

export function exportPage(store, { limit = 50, cursor } = {}) {
  const size = Number(limit);
  if (!Number.isInteger(size) || size < 1 || size > 100) {
    const err = new Error("invalid limit");
    err.status = 400;
    throw err;
  }
  const decoded = decodeCursor(cursor);
  if (cursor && !decoded) {
    const err = new Error("invalid cursor");
    err.status = 400;
    throw err;
  }
  const ordered = store
    .list()
    .filter((row) => afterCursor(row, decoded))
    .sort(cmp);
  const items = ordered.slice(0, size);
  const nextCursor =
    items.length === size ? encodeCursor(items[items.length - 1]) : null;
  return { items, nextCursor };
}

// test/export.test.js
import assert from "node:assert/strict";
import test from "node:test";
import { createStore } from "../src/store.js";
import { exportPage } from "../src/export-keyset.js";
import { installHiddenWriter } from "./hidden-writer.js";

function drain(store, limit = 25) {
  const seen = [];
  const ids = new Set();
  let cursor;
  let pages = 0;
  while (pages < 20) {
    const page = exportPage(store, { limit, cursor });
    pages += 1;
    for (const row of page.items) {
      assert.equal(ids.has(row.id), false, `duplicate ${row.id}`);
      ids.add(row.id);
      seen.push(row);
    }
    if (!page.nextCursor) break;
    cursor = page.nextCursor;
  }
  return seen;
}

test("public path: distinct timestamps, no writer", () => {
  const store = createStore();
  for (let i = 0; i < 120; i++) {
    store.insert({
      id: `pub_${i}`,
      createdAt: new Date(Date.UTC(2026, 8, 1, 0, 0, i)).toISOString(),
      amountCents: i,
      status: "paid",
    });
  }
  const seen = drain(store, 25);
  assert.equal(seen.length, 120);
});

test("hidden path: tied timestamps plus live writer", () => {
  const store = createStore();
  const writer = installHiddenWriter(store);
  const first = exportPage(store, { limit: 25 });
  assert.equal(first.items.length, 25);
  writer.afterFirstPage(first.items.map((row) => row.id));
  const ids = new Set(first.items.map((row) => row.id));
  let cursor = first.nextCursor;
  while (cursor) {
    const page = exportPage(store, { limit: 25, cursor });
    for (const row of page.items) {
      assert.equal(ids.has(row.id), false, `duplicate ${row.id}`);
      ids.add(row.id);
    }
    cursor = page.nextCursor;
  }
  for (const row of store.list()) {
    assert.equal(ids.has(row.id), true, `skipped ${row.id}`);
  }
});

The command below is the entire hidden grade once the files exist. A green public test with a red hidden test is a reject, not a partial product story.

node --test test/export.test.js

The hidden assertion is the whole point of the packet. Every id present in the store after the writer runs must appear exactly once in the concatenated pages. Rows deleted after they were already emitted may appear, because the export is not a transaction snapshot. Rows never emitted and later deleted should not appear. Rows inserted after page one must appear on a later page if they still exist at read time.

That last rule is a live-scan contract, not snapshot isolation. State it in the private rubric so graders do not mark snapshot behavior as required. Agents often write a paragraph about repeatable read and then reread live OFFSET windows anyway.

Decision table for graders

Use this table while reading a diff, then confirm with the hidden drain. Observation comes first; implementation guesses come second.

Observation	Likely implementation	Score hint
Concurrent insert never appears	OFFSET, or a cursor that encodes a numeric skip	Fail live completeness
First row of page N+1 duplicates page N	Inclusive lower bound on timestamp and id	Fail live completeness; inspect cmp
Public pass, hidden fail only on ties	Keyset on createdAt without id	Award no tie-break points
Hidden pass, handler copies full list()	Materialize-then-slice	Bounded read scores zero
Junk cursor returns 200 with a full first page	decodeCursor swallows errors	Cut cursor hygiene

Rubric

Score patches with the bands below rather than with a single pass/fail emoji. Public tests are a gate, not a grade, and they exist only to catch empty handlers.

Band	Weight	What the grader checks
Gate	0	Visible 120-row drain passes; invalid limit and bad cursors return 400
Live completeness	35	After the hidden writer, every remaining row is exported exactly once
Tie-break	25	Identical createdAt values still produce a total order on id
Bounded read	15	A page fetch does not clone the full table when a cursor is present
Cursor hygiene	15	Cursor is opaque, rejected when truncated, and does not embed OFFSET
Narrative	10	The notes describe lost-update pagination rather than caching or retries

A passing patch needs the gate plus at least 70 of the 90 remaining points. Live completeness is not optional in this packet: a clean tie-break with a failed live scan is still a reject. Narrative points cannot rescue a red hidden test, because the measurement is the drain.

Common failure modes

Graders see the same five patches repeatedly across agent runs and human take-homes. Label them in the score notes so later reviewers stay consistent, and so a second reader can reconstruct the decision.

1. OFFSET after counting the previous page. The public suite passes. The hidden insert at the front disappears into the skipped window, and the hidden delete duplicates a later id.
2. Keyset on createdAt only. The public suite passes. The tied-timestamp fixture returns overlapping pages because many rows compare equal and slice is not a cursor.
3. >= on both timestamp and id. The last row of page N is emitted again as the first row of page N+1. Duplicate detection in the hidden drain fails immediately.
4. Materialize-then-slice. The handler copies store.list(), sorts, then slices in memory. Completeness may pass on eighty rows while bounded-read still fails code review.
5. Treating the export as a snapshot without implementing one. The notes claim repeatable read, but the code rereads live state and still uses OFFSET. Score the code, not the claim.

A compact OFFSET anti-pattern looks like the function below, and it should fail the hidden test even though it looks like a textbook snippet.

export function exportPageOffset(store, { limit = 50, cursor } = {}) {
  const offset = cursor
    ? Number(Buffer.from(cursor, "base64url").toString("utf8"))
    : 0;
  const ordered = store
    .list()
    .sort((a, b) => a.createdAt.localeCompare(b.createdAt) || a.id.localeCompare(b.id));
  const items = ordered.slice(offset, offset + limit);
  const next = offset + items.length;
  const nextCursor =
    next < ordered.length
      ? Buffer.from(String(next)).toString("base64url")
      : null;
  return { items, nextCursor };
}

How to administer the packet

Keep the hidden writer in a second test file that the candidate agent does not receive on the first attempt. Public tests travel with the prompt, and hidden tests run in CI after the patch arrives. That split is the measurement, because agents that overfit page-length assertions still collapse when the table moves.

Disclosure: This article was prepared as part of MonkeyCode's product outreach. Reviewers who want a sandbox for this packet can load the repository onto MonkeyCode's free server option. They can attach free model access and grade the resulting patch with the hidden suite above. The product claims here are only that free model access and a free server option exist. This packet does not report quotas, hardware, model names, duration, or pass rates of any kind.

The workflow is ordinary: drop the public prompt in, collect a diff, run node --test, and fill the rubric. Do not score a prose lecture about keyset pagination as a substitute for the hidden drain.

Limitations and who should skip this packet

This fixture is an in-memory list, not a database with MVCC, row locks, or covering indexes. It teaches cursor contracts and grader design rather than locking, isolation levels, or index selection. Keyset pagination also cannot jump to page N, and it becomes awkward when callers supply arbitrary sort columns.

Teams should not use this packet as a junior syntax screen, because the public tests are deliberately easy. Teams should not use it as a database performance benchmark, because eighty tied rows will not expose index choices. Teams that need a true snapshot export should require a transaction or an export job table. This handler does not implement that snapshot, and graders should not award points for claiming one.

This article does not claim that any coding agent passes or fails the hidden suite in production. Interviewers who already have a production export job with snapshot tokens will learn little from the in-memory store. They can still reuse the rubric bands and the tied-timestamp trick against their real database.