Show a table as a tree

View as Markdown

A table whose rows name a parent row of the same table forms a tree: a chart of accounts, a category hierarchy, an organization chart. meta.tree declares that shape. As soon as a table declares it, the table page shows the rows as one indented list under one header, and a search shows each match under its ancestors.

Declare the parent column

The parent column is a nullable foreign key to the table’s own primary key. A top-level row leaves it empty:

import {
  integer,
  sqliteTable,
  type AnySQLiteColumn,
} from "drizzle-orm/sqlite-core";
import { bool, sapportaTable, text } from "@sapporta/server/table";

export const accountsTable = sqliteTable("accounts", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  workspace_id: text("workspace_id").notNull(),
  name: text("name").notNull(),
  parent_id: integer("parent_id").references(
    (): AnySQLiteColumn => accountsTable.id,
  ),
  archived: bool("archived").notNull().default(false),
});

export const accounts = sapportaTable({
  drizzle: accountsTable,
  meta: {
    label: "Accounts",
    rowScope: "workspaceGlobal",
    rowLabelColumns: ["name"],
    search: { self: ["name"] },
    tree: { parentColumn: "parent_id" },
  },
});

export default accounts;

The (): AnySQLiteColumn return type lets TypeScript accept a column that refers to the table it belongs to. parent_id has no .notNull(), so a row without a parent is a top-level row.

Sapporta checks the declaration when the application loads. parentColumn must be a nullable, single-column foreign key to the same table’s primary key, declared with Drizzle .references() or a meta.references rule. The column that shows the hierarchy must be visible, and the table cannot also list itself in meta.children. A violation stops the boot with the table and column in the message.

Generate, review, and apply the migration as for any table change:

SAPPORTA_DATA_DIR=data pnpm --filter ./packages/api db:generate --name add_accounts
SAPPORTA_DATA_DIR=data pnpm --filter ./packages/api db:migrate

Changing only meta.tree changes no storage and needs no migration. Schema changes and migrations covers reviewing the generated SQL.

Choose the tree options

parentColumn is the only required field. The others have defaults:

Field	Default	Sets
`column`	first `rowLabelColumns` entry	the column that shows the indentation and chevrons
`defaultExpanded`	`true`	whether rows start expanded
`matchContext`	`"ancestors-and-descendants"`	what a search or filter keeps besides the rows that match

With "ancestors-and-descendants", a search for a parent account shows the account with its whole subtree. "ancestors" keeps only the path from the top to each match:

tree: {
  parentColumn: "parent_id",
  defaultExpanded: false,
  matchContext: "ancestors",
},

The browser receives the resolved declaration as TableSchema.tree.

Work with the tree on the table page

Open /tables/accounts. The page shows every account in one list. The name column indents each account by its depth and shows a chevron on accounts with children. The other columns stay aligned across all depths. A sort orders the siblings at every depth.

Expand and collapse. Click a chevron, or press Space on the name cell.
Add a child. Right-click an account and choose Add child row. The page adds a draft under that account with parent_id filled in and opens the name editor. Leaving the draft saves it like any new row; a draft left untouched is removed.
Search and filter. A search or filter shows each match under its ancestors, so a match deep in the tree appears in place. The header shows the number of matches, such as “3 matches”. Ancestors that do not match themselves are drawn in a muted color.

The page loads the whole tree in one request of up to 1,000 rows, so it shows no pager. When the table holds more rows than that, a notice above the grid says the tree may be missing rows, and a row whose parent is not loaded appears at the top level. Search or filter to reach the other rows.

Entries in the table’s meta.children appear as row links, because expanding a row shows its child rows of the same table. CSV export writes the rows that match the current search and filters as a flat list.

Read the tree through the API and CLI

A list read on a tree table accepts tree. With a filter or search, it returns each match with its ancestors, and with ancestors-and-descendants also its subtree:

GET /api/tables/accounts?q=federal&tree=ancestors-and-descendants&limit=1000

The response meta.tree carries matchCount, the rows that match themselves, and contextIds, the ancestors present only because a descendant matched. The walk applies row scope at every step, so it never returns or passes through a row the caller cannot read.

Constraints that belong to the screen go in fixed[col][op] conditions. Every returned row satisfies them, including the ancestors and descendants around a match. This read never brings back an archived parent or child of a matching account:

GET /api/tables/accounts?q=taxes&tree=ancestors&fixed[archived][eq]=false

The CLI takes the same options:

pnpm exec sapporta rows list accounts --q taxes --tree ancestors \
  --fixed '{"archived":{"eq":false}}'

Query syntax lists the parameters, the response fields, and the no_tree_config error.

Adjust a tree in a custom grid

A defineTGrid() level follows the table’s declared tree. Its tree option overrides single fields, or false shows the table as a flat, paged list:

import { defineTGrid } from "@sapporta/frontend";
import type { TableSchema } from "@sapporta/shared/contracts";
import { mintFilterId } from "@sapporta/shared/filter";

export function accountsGridDefinition(table: TableSchema) {
  return defineTGrid({
    rootLevel: "accounts",
    levels: {
      accounts: {
        table,
        childLevels: [],
        tree: { defaultExpanded: false },
        query: {
          fixedFilters: [
            {
              id: mintFilterId("archived", "eq"),
              column: "archived",
              op: "eq",
              value: "false",
            },
          ],
        },
      },
    },
  });
}

The level sends its fixedFilters as fixed conditions, so a search keeps only unarchived ancestors and descendants. A custom page built from the table hooks reads session.levelInfoById[level].pagination, which is "all" for a tree level, and useTableLevelPager() reports one page. TGrid definitions, sessions, and queries lists every tree option.

Match a tree in an application endpoint

scopedRows().treeMatch() runs the same walk for an application route. It returns a where for an ordinary scoped read, with the match count and the context row ids:

import { asc, eq, like } from "drizzle-orm";

const rows = scopedRows(c.get("db"), auth, accounts);
const match = await rows.treeMatch({
  fixed: eq(accountsTable.archived, false),
  match: like(accountsTable.name, "%tax%"),
  matchContext: "ancestors",
});
const result = await rows.page({
  where: match.where,
  orderBy: asc(accountsTable.name),
  limit: 1000,
});

Scoped CRUD and bounded reads describes the inputs and the result.