Digging Deeper

Data Tables

Introduction#

A list that people sort, filter, page through, tick rows in, act on and download is where the classic leaks of a web app live: a hidden column that still travels in the props, a sort parameter that reaches ORDER BY, a bulk action that takes another tenant's ids, a cell that a spreadsheet runs as a formula. A data table handles them once. You write one definition per table on the server; the URL holds its state and is parsed strictly; its actions and exports are endpoints that check everything again. The browser half is headless: useTable() gives a component what the props allow and renders nothing itself.

Install the package first:

pnpm add @marmeon/table

This page builds the members table of the web app: every member with a confirmed address and the number of their links, an e-mail column only administrators see, a row action, a bulk action and a CSV export.

import type { Authenticated } from '@marmeon/auth';
import { action, bulk, column, defineTable, exportCsv, filter, type TableContext } from '@marmeon/table';
import { RemoveMemberLinks } from '../actions/RemoveMemberLinks.ts';
import { RemoveMembersLinks } from '../actions/RemoveMembersLinks.ts';
import { MemberPolicy } from '../policies/MemberPolicy.ts';

export const MembersTable = defineTable({
  name: 'members',
  query: (db, _ctx: TableContext<Authenticated>) =>
    db.selectFrom('users').withCount('profile_links', { as: 'links' }).where('users.email_verified_at', 'is not', null),
  key: 'users.id',
  columns: {
    name: column('users.name').sortable().searchable(),
    email: column('users.email').visible(MemberPolicy, 'viewEmails').sortable().searchable(),
    joined: column('users.created_at').sortable(),
    links: column('links').sortable(),
  },
  filters: {
    joined: filter.dateRange('users.created_at'),
    locale: filter.select('users.locale', ['en', 'de']),
  },
  defaultSort: 'name',
  pagination: { perPage: [10, 25, 50] },
  actions: { removeLinks: action(RemoveMemberLinks).visible(MemberPolicy, 'moderate').authorize(MemberPolicy, 'removeLinks').confirm() },
  bulk: { removeLinks: bulk(RemoveMembersLinks).visible(MemberPolicy, 'moderate').authorize(MemberPolicy, 'removeLinks').confirm().max(100) },
  export: { csv: exportCsv().authorize(MemberPolicy, 'export') },
});

defineTable() is typed from the base query's tables, so a column that does not exist, a sort that is not sortable or an ability the policy lacks is a compile error.

Defining a table#

The base query#

query(db, ctx) returns every row the table may ever show in this request. Pages, actions, bulk actions and exports all start from it, so it is the boundary: put the tenant here, .where('users.team_id', '=', ctx.user.team_id), and nothing of another tenant can be shown, changed or downloaded. The base query selects nothing but values it computes, such as a count. The table selects the visible columns and the key itself. Type its ctx as TableContext<…> with what the routes' middleware adds, such as Authenticated.

Columns#

A column is a column of the query: column('name'), or column('users.name') for a join. Its key in columns is its alias, the only name the URL, the props and the export ever use. The members table calls users.created_at joined, and no URL ever says created_at.

A hidden column is not selected, not sent and not exported, and in the row's type it is optional. A column that a migration marked .secret(), such as a password hash, cannot be a column at all: that is a compile error, and a column that names a secret(eb, …) of the base query is refused when the table runs.

A column can also show a value the base query computes under a name: withCount('profile_links', { as: 'links' }) gives links: column('links'), typed as a number, sorted by in ORDER BY without a join. Its key is that name. It cannot be searched or filtered by, since WHERE cannot see a value the query computes; both are compile errors. Hidden by .visible(…), the value is left out of the base query too, so it never reaches the SQL.

The key#

The key identifies a row, and it is what actions send. A number column goes by name, key: 'users.id', and is read as an integer. A text key says how to read it: key: { column: 'users.uuid', type: 'uuid' }, with the types integer, bigint, string and uuid. A text key by name alone is a compile error. Every key from the browser is parsed by its type before any query. One that does not parse is no row, so text never reaches an integer column. In the props, the key of a row is under its column's name: users.id becomes id.

Filters#

Filters are an allowlist too:

A filter takes .visible(…) like a column. A filter on the column of a hidden column is hidden with it, because the number of rows it leaves would tell that column's values.

Sorting and pages#

defaultSort is the sort without a ?sort=: an alias, with - in front for descending. Without it, the table sorts by its first sortable column that everyone sees. The key always breaks ties. A default sort by a column with .visible(…) is refused, as a compile error and when defineTable() runs, because it would answer 400 to everyone who may not see the column.

pagination pages by number with a total, by default. perPage lists the page sizes ?per_page= allows, and the first one is the default: [25, 50, 100] unless you say otherwise. { cursor: true } pages by an encrypted cursor instead, for "load more", without counting every row. The pagination page explains both.

Who may see the table#

authorize decides whether the user may see the table at all. It is asked on the page and in every endpoint. can() of @marmeon/auth takes a policy and one of its abilities, here an ability viewAny that you add to the policy:

authorize: can(MemberPolicy, 'viewAny'),

The endpoints run the middleware of the page's routes, but not the authorize of the page's controller. A table that needs a check says so here.

More than one table on a page#

prefix: 'teams' puts a prefix before the table's keys in the URL: teams_sort, teams_filter[role]. Spread both tables' query schemas into the page's request: defineRequest({ query: r.query({ ...MembersTable.query.entries, ...TeamsTable.query.entries }) }).

The controller and the routes#

The page's controller validates the table's state as its query and resolves the table as a prop:

import type { Authenticated } from '@marmeon/auth';
import { Controller, defineRequest, type ContextOf } from '@marmeon/http';
import { Tables } from '@marmeon/table';
import { MembersTable } from '../tables/MembersTable.ts';

export const ListMembersRequest = defineRequest({ query: MembersTable.query });

export class ListMembersController extends Controller {
  static request = ListMembersRequest;

  readonly #tables: Tables;

  constructor(tables: Tables) {
    super();
    this.#tables = tables;
  }

  handle(ctx: ContextOf<typeof ListMembersRequest> & Authenticated) {
    return this.view('members/Index', { members: () => this.#tables.resolve(MembersTable, ctx) });
  }
}

The prop is a function, so a partial reload of another prop leaves the table out. tableRoutes() registers the table's endpoints on the registrar you pass, the page's:

import { authenticate, verified } from '@marmeon/auth';
import { throttle } from '@marmeon/cache';
import { defineRoutes } from '@marmeon/http';
import { tableRoutes } from '@marmeon/table';
import { ListMembersController } from './controllers/ListMembersController.ts';
import { MembersTable } from './tables/MembersTable.ts';

export default defineRoutes((Route) => {
  const members = Route.prefix('/members').middleware(authenticate()).middleware(verified()).name('members.');
  members.get('/', ListMembersController).name('index');
  tableRoutes(members.middleware(throttle('members-table')), MembersTable);
});

The endpoints are ordinary routes of the page's group, with its middleware, its route bindings, the session and CSRF protection for the posts. A guest or an unconfirmed account never reaches them. A registrar that lacks what the base query needs, such as ctx.user without authenticate(), is a compile error. The routes have no names, because the props carry their URLs. Throttle them: an export reads the whole filtered table. The rate limiting page shows the members-table limiter.

What the browser gets#

tables.resolve(MembersTable, ctx) resolves to TableProps<typeof MembersTable>:

{
  name: 'members', key: 'id',
  columns: [{ name: 'name', label: null, sortable: true, searchable: true, sort: 'asc' }, …],
  rows: [{ id: 7, name: 'Ada', joined: '2026-01-10T09:00:00.000Z', can: { removeLinks: true } }, …],
  sort: { column: 'name', direction: 'asc' },
  search: { value: '' },
  filters: [{ name: 'locale', type: 'select', key: 'filter[locale]', options: ['en', 'de'], value: null }, …],
  page: { type: 'offset', page: 1, perPage: 10, perPageOptions: [10, 25, 50], total: 42, lastPage: 5, from: 1, to: 10 },
  query: { sort: 'sort', page: 'page', perPage: 'per_page', cursor: 'cursor', search: 'search' },
  state: { sort: '-joined', 'filter[locale]': 'de' },
  actions: [{ name: 'removeLinks', url: '/members/_tables/members/actions/removeLinks', confirm: true }],
  bulk: [{ name: 'removeLinks', url: '/members/_tables/members/bulk/removeLinks', confirm: true, max: 100 }],
  exports: [{ name: 'csv', format: 'csv', url: '/members/_tables/members/export/csv?sort=-joined&filter%5Blocale%5D=de', max: 10000 }],
}

• columns holds the visible columns, in the definition's order.
• Each row has the key and the visible columns, nothing else. can holds one boolean per row action, computed for the whole page with one policy call per ability, never a policy's reason.
• search is null when no visible column is searchable.
• state is the table's state in the URL, without defaults.
• Actions, bulk actions and exports the user may not use are not in the props at all.

The URLs carry the page's own route parameters: a table under /teams/:team links to /teams/7/_tables/….

The URL is strict#

MembersTable.query is the table's state as a query schema: sort, page or cursor, per_page, search when a column is searchable, and filter[<name>], with the table's prefix. It is typed, so ctx.query.sort is 'name' | '-name' | 'joined' | ….

Other query schemas fall back per field: a link with a broken value shows the default. A table does not. Each of these is a 400:

• a sort that is not offered, or a raw column name: ?sort=password, ?sort=created_at instead of joined,
• a filter that does not exist, ?filter[password]=x, or a value its filter does not offer,
• a page size that is not in perPage, ?per_page=10000, or a page that is no whole number,
• a repeated key, or a search longer than 100 characters,
• sorting by a column the user cannot see, or a filter the user cannot see,
• a search when no column the user can see is searchable.

The search never looks into a column the user cannot see. It leaves such a column out without an error, so a search finds nothing in a hidden column and tells nothing about it.

Nothing from the URL reaches ORDER BY or WHERE that the definition did not name. A sort or a filter is an alias that is looked up, and only its column goes into the query.

Actions#

Row actions#

A row action is a class with handle(row, ctx), built per request through the container:

import { ProfileLinkRepository } from '#modules/user-profile';

export class RemoveMemberLinks {
  readonly #links: ProfileLinkRepository;

  constructor(links: ProfileLinkRepository) {
    this.#links = links;
  }

  async handle(member: { readonly id: number }) {
    return { removed: await this.#links.removeAllOf([member.id]) };
  }
}

action(Handler) puts it into the table. .visible(Policy, 'ability') offers it only to some users, asked once per request. .authorize(Policy, 'ability') asks the policy for each row, with the row as the table hands it out. .confirm() makes the browser ask first. The endpoint checks, in this order:

1. the table's authorize, else 403,
2. that the action exists, else 404, and is visible, else 403,
3. the body,
4. that the row is in the base query: another tenant's row, a deleted one or a made-up key is a 404,
5. the action's policy for that row, else 403.

Only then does handle() run. A policy that decides per row sees the key and the visible columns, by alias. One that needs more looks the row up by its key, or the column becomes part of the table.

Bulk actions#

A bulk action is a class with handle(rows, ctx). It acts on the chosen keys ∩ the base query ∩ the policy per row. Keys that are no rows of the table are left out and counted as missing, and rows the policy refuses as skipped. The handler gets only the rest, and none of it runs when nothing is left.

"All matching" sends the table's state, { all: true, query }, never a list of keys. The server parses it again, as strictly as the URL, and refuses more rows than the action's max, 1000 by default, with a 422. It never acts on a list that was cut short. Chosen keys are held to max as well.

The answer#

An action request, the kind useTable() sends, and an API client get JSON: { done, skipped, missing }, and data with what the handler returned. The answer is never cached. A form posted without JavaScript, or a page visit, goes back to the page with a 303. A handler that returns a redirect, a JSON result or a Response answers for itself.

Exports#

exportCsv() downloads the visible columns, filtered and sorted as the URL says:

• Its own authorize. Without one, whoever sees the table may download what they see.
• A limit. exportCsv().max(rows), 10000 by default. One count runs first, and an export over the limit is a 422 before a byte is sent, never a file cut short.
• Batches. Rows stream in batches of 500. The first batch runs in the handler, and the later ones after it has answered: in the request's context, for the log and the request id, and outside any transaction that was open around the handler. Each batch goes on after the last row written, by the table's order and then the key, never at an offset. A row deleted meanwhile moves no later row out of the file, and a row added meanwhile shows at most once.
• Headers. Content-Disposition: attachment with a safe file name such as members-2026-10-06.csv, Cache-Control: no-store, X-Content-Type-Options: nosniff, and a byte order mark, so spreadsheets read umlauts right.
• Formula injection is escaped. A text cell that starts with =, +, -, @, a tab, a carriage return or a line feed gets a ' in front. Every field is quoted, with quotes doubled. Numbers stay numbers, so -5 is no formula.

The security model#

Every endpoint starts again at the beginning. Nothing the browser sends is trusted, whatever the page showed:

The security page puts the table's model next to the app's other defaults.

In the browser#

useTable(props.members) from @marmeon/react/table gives a component what the props allow, typed by them: only sortable columns sort, only the table's filters filter, only its actions run. The markup is yours:

import type { PageProps } from '@marmeon/http';
import { useTranslation } from '@marmeon/react';
import { useTable } from '@marmeon/react/table';
import type { ListMembersController } from '../controllers/ListMembersController.ts';

export default function Index({ members }: PageProps<ListMembersController>) {
  const t = useTranslation('members');
  const table = useTable(members, {
    labels: { column: (name) => t(`column.${name}`), row: (row) => row.name, action: (_action, name) => t('remove_links_of', { name }) },
  });
  const csv = table.exports.find((entry) => entry.name === 'csv');

  return (
    <main>
      <p {...table.statusProps}>{table.status}</p>
      <table>
        <caption>{t('caption')}</caption>
        <thead>
          <tr>
            <th scope="col">
              <input {...table.pageCheckboxProps()} />
            </th>
            {table.columns.map((column) => (
              <th key={column.name} {...column.headerProps}>
                {column.sortLinkProps ? <a {...column.sortLinkProps}>{column.label}</a> : column.label}
              </th>
            ))}
            <th scope="col">{t('actions')}</th>
          </tr>
        </thead>
        <tbody>
          {table.rows.map((row) => (
            <tr key={row.id}>
              <td>
                <input {...table.checkboxProps(row)} />
              </td>
              <th scope="row">{row.name}</th>
              <td>{row.email ?? ''}</td>
              <td>{row.joined}</td>
              <td>
                {table.can('removeLinks', row) && (
                  <button type="button" aria-label={table.actionLabel('removeLinks', row)} onClick={() => void table.run('removeLinks', row)}>
                    {t('remove_links')}
                  </button>
                )}
              </td>
            </tr>
          ))}
        </tbody>
      </table>
      <button type="button" onClick={() => void table.runBulk('removeLinks')}>
        {t('bulk_remove', { count: table.selectedCount })}
      </button>
      {csv && (
        <a href={table.exportUrl('csv')} download>
          {t('export')}
        </a>
      )}
    </main>
  );
}

The example assumes the e-mail column is visible. The web app's page renders each cell from table.columns, so a column the user cannot see leaves no empty cell.

• Sorting, filtering, searching and paging are visits of the page's URL with the props' keys and the columns' aliases: table.sort('joined'), table.filter('locale', 'de'), table.filterBy({ … }), table.setSearch('ada'), table.goTo(2), table.perPage(25) and table.next() for a cursor. Only the table's prop reloads, and the server parses the state again. Asking for something the props do not offer throws before a request goes out, with an error code from [marmeon:C9] to [marmeon:C12]. Sorting, filtering and paging add a history entry. The search waits 300 milliseconds after the last keystroke and replaces the current history entry instead of adding one. Back after a search therefore returns to the entry before it: an earlier sort, filter or page, or the page you came from when the search was the table's first change. Sort links are real links: without JavaScript, or opened in a new tab, they work. table.page.type is offset or cursor, so a page knows which pagination to render.
• The selection lives with the history entry, in memory, never in history.state or the browser's storage. A new sort, filter or page starts with nothing selected, and Back brings the old selection back. Leaving the document, by signing out or after a new build, forgets it. table.selectMatching() selects every matching row, not only those on screen.
• Actions are action requests: table.run(action, row) and table.runBulk(action). Afterwards the table's prop reloads and replaces what the page had, also a merged table. A bulk action clears the selection. The action's code loads with the table's first run, in a chunk of its own. An action with .confirm() asks first, with labels.confirm or the run's confirm option.
• table.exportUrl('csv') is the download's URL with the table's current sort, search and filters. Use it in a link with download, not in a visit.

The web app's page works without JavaScript as well: sort links are links, the filters are a GET form, and the row and bulk actions are plain forms that come back with a 303. The table's browser code is in the chunk of the page that imports useTable(), so an app's first download carries none of it unless its first page shows a table.

Accessible markup#

useTable() hands out the attributes for markup that follows the WAI-ARIA Authoring Practices for sortable tables:

• A native <table> with a <caption> that names it. No role="grid" without real cell navigation by arrow keys.
• aria-sort only on the heading the table is sorted by, through headerProps. Never none on the others.
• The sort control is a link or a button inside the <th>, named after its column: sortLinkProps or toggleSort().
• The cell that names the row is a <th scope="row">, so a screen reader says whose value a cell holds.
• The number of rows, with the sort and the page, goes into a live region: statusProps and status. What an action did goes into a second one, because the list's count often stays the same.
• Checkboxes and row buttons are named after their row. A button's name starts with its visible text, "Remove links: Ada Lovelace" for a button that says "Remove links", so a voice user can say what they see.
• Focus is never dropped. A control that cannot be used right now stays focusable, with aria-disabled and a click that does nothing, instead of disabled. A confirmation dialog gives focus back to the button that opened it.

TanStack Table#

An app that wants TanStack Table's column model, sizing or pinning keeps the server in charge with a bridge:

import type { PageProps } from '@marmeon/http';
import { useTable } from '@marmeon/react/table';
import { toTanStack } from '@marmeon/react/table-tanstack';
import { rowPaginationFeature, rowSelectionFeature, rowSortingFeature, tableFeatures, useTable as useTanStackTable } from '@tanstack/react-table';
import type { ListMembersController } from '../controllers/ListMembersController.ts';

const features = tableFeatures({ rowSortingFeature, rowPaginationFeature, rowSelectionFeature });

export default function Grid({ members }: PageProps<ListMembersController>) {
  const table = useTable(members);
  const tanstack = useTanStackTable(toTanStack(table, { features }));
  return <p>{tanstack.getRowModel().rows.length}</p>;
}

TanStack runs in manual mode: it sees one page and sorts nothing itself, and its sorting, paging and row selection go back through useTable(). Install TanStack yourself with pnpm add @tanstack/react-table@^9. The bridge imports only its types, so TanStack is in a bundle only where the app imports it. Define features at module scope, so it stays the same object between renders.

Type checks#

Mistakes in a definition are compile errors with readable messages:

'emial' is not a column of the table's query — it has avatar_path, bio, created_at, email, …
'password' is a secret column — a table never reads it (its migration marked it .secret())
the key 'email' holds text — say how to read it: key: { column: 'email', type: 'string' } (or 'uuid', 'bigint')
'-email' is not a sortable column — it sorts by -name, name
'deactivat' is not an ability of this policy — it has deactivate, export, …

The same goes for a default sort by a hidden column, an action whose handler takes other rows than the table's, a .visible() ability that wants a row, and tableRoutes() on a registrar that lacks what the base query needs. A handler's ctx is not compared with the base query's context, so type it as TableContext<…> like the query.

Testing#

A test runs an action the way the browser does, and downloads an export of any length:

import { UserRepository, type User } from '#modules/auth';
import { createTestApp } from '@marmeon/testing';
import { it } from 'vitest';
import application from '../../bootstrap/app.ts';

it('a member who is no administrator can neither sort by e-mail nor export', async () => {
  const app = await createTestApp(application, { database: 'refresh', seed: true });
  const [, alan] = (await app.make(UserRepository).query().selectAll().orderBy('id').execute()) as User[];

  await app.actingAs(alan!).navigating().get('/members?sort=password').assertStatus(400);
  await app.actingAs(alan!).navigating().get('/members?sort=email').assertStatus(400);
  await app.actingAs(alan!).get('/members/_tables/members/export/csv').assertForbidden();
});

app.actingAs(user).acting().post(url, { json: { key } }) sends a row action as useTable() would. The HTTP tests page covers requests in tests.