Tenant-scoped invoice list with a cursor

The schema is migrated and the database is full of invoices, but the query behind the inspector’s list panel is still a stub, so every org sees the empty state. In this lesson you write that query, listInvoices: it lists one org’s invoices, pages through them with a cursor, and filters by status on the server.

Every later list view inherits this read pattern. Three decisions have to be right the first time, because getting them wrong throws no error: it silently leaks another tenant’s data, or silently skips rows as the user pages.

When you are done, the list panel renders rows with invoice number, customer name, status badge, total, and due date. “Next page” advances with a fresh ?cursor=... and never repeats a row; ?status=paid filters to paid invoices and survives a reload. The plan panel confirms the planner serves the query from a composite index, never a Seq Scan.

Your mission

Implement listInvoices in src/lib/invoices/queries.ts. The inspector already calls it from list-panel.tsx; fill in the body and the panel comes alive.

The function takes a typed ListInvoicesInput and does not validate it. The shape — organizationId required, status optional, cursor an already-decoded Cursor object (not the URL token), pageSize defaulting to 20 and capped at 100 — comes from listInvoicesInputSchema in lib/invoices/schema.ts. The inspector page parses against that schema and runs decodeCursor before it ever calls you. Each entry point parses once at its own boundary; the query trusts its typed argument, so do not call .parse inside it.

Three decisions carry the query.

The first is the tenant guard: put the organizationId filter inside the where, AND-ed with everything else. Load rows first and filter for the org afterward and you have the classic IDOR leak — any row whose id an attacker can guess comes back regardless of who owns it. The filter is the security boundary, written by hand on every query.

The second is the compound cursor tiebreaker. You order by createdAt descending, but the moment two invoices share a timestamp, a cursor comparing on createdAt alone skips or repeats rows across pages. Break the tie on id: the cursor carries the (createdAt, id) pair, and the predicate asks for rows older than the cursor’s createdAt plus rows at exactly that createdAt with a smaller id. Include this predicate only when a cursor is present, since the first page has no boundary to compare against.

The third is the pageSize + 1 trick. To show the “Next page” link you need to know whether another page exists, and count() on the whole filtered set is a wasted round trip on every render. Ask for pageSize + 1 instead. If the extra row comes back, a next page exists: drop it, keep pageSize rows, and build nextCursor from the last row kept. If it does not, this is the final page and nextCursor is null.

Out of scope: leave the getInvoiceDetail stub for the next lesson. The plan panel and its EXPLAIN ANALYZE probes are provided — you read their output, you do not build them. The whole project is read-only.

The list returns one org’s invoices, scoped to organizationId; switching orgs in the switcher changes which rows appear.

tested

A cross-org pairing returns no leaked rows: invoices for a different org never appear under the requested org.

tested

Paging forward via the returned nextCursor yields a fresh page with no row repeated across pages, and nextCursor is null on the last page.

tested

Passing status: 'paid' returns only paid rows; the paid-filtered set matches the paid rows inside the unfiltered list, whether or not the status arrived via the URL.

tested

A page never returns more than pageSize rows even when more exist; the extra + 1 probe row is dropped, not emitted.

tested

Each returned row carries its customer (e.g. the customer name) from the same query, with no per-row follow-up fetch.

untested

In the inspector, ?status=paid puts the status in the URL and a hard reload (or a second tab on the same URL) reproduces the filtered view.

untested

The list query’s plan uses idx_invoices_org_created_at_id without a status filter and idx_invoices_org_status_created_at_id with one, never a Seq Scan.

untested

Coding time

Implement listInvoices against the brief and the Lesson 5 tests, then open the inspector and click through a few pages before reading the walkthrough.

Reference solution and walkthrough

The file splits into three moves: a private builder that constructs the query, a row type inferred from that builder, and the public function that runs it and computes the page boundary. Here is the builder.

const listInvoiceRows = (input: ListInvoicesInput) => {
  const { organizationId, status, cursor, pageSize } = input;

  return db.query.invoices.findMany({
    where: (t, { and, eq, lt, or }) =>
      and(
        eq(t.organizationId, organizationId),
        status ? eq(t.status, status) : undefined,
        // The compound cursor predicate: rows strictly older than the cursor's
        // createdAt, plus rows at the same createdAt with a smaller id (the
        // (createdAt, id) tiebreaker). createdAt is pinned to millisecond
        // precision, so the cursor's ISO string round-trips exactly.
        cursor
          ? or(
              lt(t.createdAt, new Date(cursor.createdAt)),
              and(
                eq(t.createdAt, new Date(cursor.createdAt)),
                lt(t.id, cursor.id),
              ),
            )
          : undefined,
      ),
    orderBy: (t, { desc }) => [desc(t.createdAt), desc(t.id)],
    limit: pageSize + 1,
    with: { customer: true },
  });
};

The callback where form: Drizzle hands you the table t and the operator helpers, and you return one combined condition. The first leaf is the tenant guard. This is the IDOR defense: the org filter lives in the query, not in a check after the rows come back.

const listInvoiceRows = (input: ListInvoicesInput) => {
  const { organizationId, status, cursor, pageSize } = input;

  return db.query.invoices.findMany({
    where: (t, { and, eq, lt, or }) =>
      and(
        eq(t.organizationId, organizationId),
        status ? eq(t.status, status) : undefined,
        // The compound cursor predicate: rows strictly older than the cursor's
        // createdAt, plus rows at the same createdAt with a smaller id (the
        // (createdAt, id) tiebreaker). createdAt is pinned to millisecond
        // precision, so the cursor's ISO string round-trips exactly.
        cursor
          ? or(
              lt(t.createdAt, new Date(cursor.createdAt)),
              and(
                eq(t.createdAt, new Date(cursor.createdAt)),
                lt(t.id, cursor.id),
              ),
            )
          : undefined,
      ),
    orderBy: (t, { desc }) => [desc(t.createdAt), desc(t.id)],
    limit: pageSize + 1,
    with: { customer: true },
  });
};

A status adds eq(t.status, status); no status passes undefined, which and() drops. The filter vanishes from the SQL with no branching, so one findMany covers the filtered and unfiltered list alike.

const listInvoiceRows = (input: ListInvoicesInput) => {
  const { organizationId, status, cursor, pageSize } = input;

  return db.query.invoices.findMany({
    where: (t, { and, eq, lt, or }) =>
      and(
        eq(t.organizationId, organizationId),
        status ? eq(t.status, status) : undefined,
        // The compound cursor predicate: rows strictly older than the cursor's
        // createdAt, plus rows at the same createdAt with a smaller id (the
        // (createdAt, id) tiebreaker). createdAt is pinned to millisecond
        // precision, so the cursor's ISO string round-trips exactly.
        cursor
          ? or(
              lt(t.createdAt, new Date(cursor.createdAt)),
              and(
                eq(t.createdAt, new Date(cursor.createdAt)),
                lt(t.id, cursor.id),
              ),
            )
          : undefined,
      ),
    orderBy: (t, { desc }) => [desc(t.createdAt), desc(t.id)],
    limit: pageSize + 1,
    with: { customer: true },
  });
};

The cursor predicate, the heart of the lesson. “The next page” means rows strictly older than the cursor’s createdAt, OR rows at exactly that createdAt with a smaller id. That (createdAt, id) tiebreaker keeps paging correct when timestamps collide; without it, equal timestamps make the cursor ambiguous and rows get skipped or duplicated. cursor.createdAt is wrapped in new Date(...) because the column is a Date; pinned to millisecond precision (the timestamps group uses precision: 3), its ISO string round-trips back to the exact stored value.

const listInvoiceRows = (input: ListInvoicesInput) => {
  const { organizationId, status, cursor, pageSize } = input;

  return db.query.invoices.findMany({
    where: (t, { and, eq, lt, or }) =>
      and(
        eq(t.organizationId, organizationId),
        status ? eq(t.status, status) : undefined,
        // The compound cursor predicate: rows strictly older than the cursor's
        // createdAt, plus rows at the same createdAt with a smaller id (the
        // (createdAt, id) tiebreaker). createdAt is pinned to millisecond
        // precision, so the cursor's ISO string round-trips exactly.
        cursor
          ? or(
              lt(t.createdAt, new Date(cursor.createdAt)),
              and(
                eq(t.createdAt, new Date(cursor.createdAt)),
                lt(t.id, cursor.id),
              ),
            )
          : undefined,
      ),
    orderBy: (t, { desc }) => [desc(t.createdAt), desc(t.id)],
    limit: pageSize + 1,
    with: { customer: true },
  });
};

The ordering matches the cursor’s comparison direction, newest first with ties broken by id descending, and it matches the column order of idx_invoices_org_created_at_id. That alignment between orderBy, the cursor predicate, and the index is what lets the planner serve the page from the index instead of sorting the whole table.

const listInvoiceRows = (input: ListInvoicesInput) => {
  const { organizationId, status, cursor, pageSize } = input;

  return db.query.invoices.findMany({
    where: (t, { and, eq, lt, or }) =>
      and(
        eq(t.organizationId, organizationId),
        status ? eq(t.status, status) : undefined,
        // The compound cursor predicate: rows strictly older than the cursor's
        // createdAt, plus rows at the same createdAt with a smaller id (the
        // (createdAt, id) tiebreaker). createdAt is pinned to millisecond
        // precision, so the cursor's ISO string round-trips exactly.
        cursor
          ? or(
              lt(t.createdAt, new Date(cursor.createdAt)),
              and(
                eq(t.createdAt, new Date(cursor.createdAt)),
                lt(t.id, cursor.id),
              ),
            )
          : undefined,
      ),
    orderBy: (t, { desc }) => [desc(t.createdAt), desc(t.id)],
    limit: pageSize + 1,
    with: { customer: true },
  });
};

limit: pageSize + 1 fetches one extra row, so the public function can tell whether a next page exists without a second count(). with: { customer: true } pulls each row’s customer in the same round trip; the alternative loops one query per row, the N+1 problem, and avoiding it is exactly what relational with is for.

← Previous 1 / 1 Next →

The extra row, the slice, and the cursor live in the public function:

export type InvoiceListRow = Awaited<
  ReturnType<typeof listInvoiceRows>
>[number];

export const listInvoices = async (
  input: ListInvoicesInput,
): Promise<{ rows: InvoiceListRow[]; nextCursor: string | null }> => {
  const { pageSize } = input;

  const rows = await listInvoiceRows(input);

  // Fetched pageSize + 1: the extra row proves a next page exists. Drop it and
  // emit a cursor from the last kept row; otherwise this is the final page.
  const hasNextPage = rows.length > pageSize;
  const kept = hasNextPage ? rows.slice(0, pageSize) : rows;
  const last = kept.at(-1);

  const nextCursor =
    hasNextPage && last
      ? encodeCursor({ createdAt: last.createdAt.toISOString(), id: last.id })
      : null;

  return { rows: kept, nextCursor };
};

Build nextCursor from the last row you kept, not the one you dropped, serializing its (createdAt, id) pair with encodeCursor. The panel drops that token into the next page’s URL, and decodeCursor reads it back into the Cursor object this query expects. On the final page there is no extra row, so nextCursor is null and the panel renders “End of list”.

Why the builder is split out. Pulling the findMany into a private listInvoiceRows lets InvoiceListRow be inferred as Awaited<ReturnType<typeof listInvoiceRows>>[number], the exact shape the query returns, joined customer included. Add lines to the with later and the type updates itself with no edits. Hover the alias to see what the compiler infers:

export type InvoiceListRow = Awaited<
  ReturnType<typeof listInvoiceRows>
>[number];

Caution

The tenant guard belongs inside the where, on every read, no exceptions. Loading the invoice first and checking if (invoice.organizationId === orgId) leaks any row whose id an attacker can guess: the database returned it before your check ran.

Note

To read the list query’s plan, open the plan panel at the bottom of the inspector. With no status filter you should see Index Scan using idx_invoices_org_created_at_id ...; with a status, ... using idx_invoices_org_status_created_at_id .... A Seq Scan here is a red flag: the index’s column order does not line up with this query’s where plus orderBy.

Drizzle — Cursor-based pagination

orm.drizzle.team

The official guide for the (createdAt, id) multi-column cursor and the index it rides on.

Drizzle — Relational queries

orm.drizzle.team

The findMany + with API that loads each row's customer in one round trip.

Moment of truth

Run the lesson’s suite:

pnpm test:lesson 5

It runs your listInvoices against the seeded database. It does not re-seed, so bring the database up, migrate, and seed first:

docker compose up -d
pnpm db:migrate
pnpm db:seed

A green run covers tenant scope, no cross-org leak, paging with no repeats and a null cursor on the last page, the status filter, and the pageSize cap. The tiebreaker tests bite because the seed commits every invoice in one transaction, so they all share a createdAt. A createdAt-only cursor would skip or duplicate rows at each page boundary; the (createdAt, id) tiebreaker is what prevents that.

Three things the tests can’t reach. Confirm them by hand in the inspector:

Against one org, click “Next page” three times and confirm no row repeats across the three pages (open pnpm db:studio to spot-check if needed); switch orgs in the switcher and confirm the rows differ.

untested

Click the paid filter, confirm the URL shows ?status=paid, only paid rows render, and a hard reload (or opening the same URL in a second tab) preserves the filtered view.

untested

Expand the plan panel on the list query and confirm it uses idx_invoices_org_created_at_id with no status filter and idx_invoices_org_status_created_at_id with one, never a Seq Scan — if it falls back to a scan, the composite index’s column order from the schema lesson is wrong.

untested

Next, the invoice detail read: a single round trip guarded by the same organizationId rule.