Scoped report helpers

Public surfaces

Ordinary table-shaped access and single-table totals: scopedRows(...), .count(), and .countBy() from @sapporta/server.
Custom Drizzle shapes: auth.rowSecurity.forTable(table) from the request auth context.

Use the general row-helper reference for the full read/write API. Reports need the narrower contracts below.

Per-table guard

const projectAccess = auth.rowSecurity.forTable(projects);
const taskAccess = auth.rowSecurity.forTable(tasks);

forTable(table) binds the request data authority and loaded table catalogue to that table. It does not accept a database or transaction handle. The database or transaction belongs to the Drizzle query and to write helpers whose signatures explicitly require it.

Create a separate guard for every base table that can contribute a row, count, identifier, rollup, or footer.

`ownedRows(productFilter?)`

const visibleTasks = await db
  .select()
  .from(tasksTable)
  .where(
    taskAccess.ownedRows(
      projectId === undefined
        ? undefined
        : eq(tasksTable.project_id, projectId),
    ),
  );

ownedRows() returns the trusted row-visibility predicate. ownedRows(productFilter) AND-composes the supplied SQL filter with that predicate. Apply the combined predicate before the query reads rows.

Scope each base relation before joining, grouping, mapping, or calculating totals. Applying a predicate after aggregation is too late.

Aggregation boundary

Use scopedRows().count() for a scalar total and .countBy() for a bounded one-column group when one table owns the question. Both compose a Drizzle where expression with the request’s row predicate.

Small report routes may map already-visible rows in memory. Large reports should push grouping into a store query while retaining one scoped predicate per base relation. If raw SQL is necessary, construct explicit guarded base-row CTEs before joins or aggregates; raw SQL does not gain row-scope enforcement automatically.

Ability checks remain separate. The route first authorizes the report action and resolves data authority; these helpers then constrain the data visible to that authorized action.

`attachReferenceComputed()`

function attachReferenceComputed(
  dataset: GridDataset,
  input: {
    db: BetterSQLite3Database;
    auth: SapportaAuthContext;
    tables: readonly TableDef[] | Pick<TableCatalog, "get">;
    names?: Readonly<Record<string, readonly string[]>>;
  },
): Promise<GridDataset>;

attachReferenceComputed() from @sapporta/server computes the fields that the rows named by the dataset’s reference columns are drawn with, and returns the dataset with computed set:

return c.json(
  await attachReferenceComputed(dataset, {
    db,
    auth,
    tables: [accounts],
    names: { accounts: ["parentPath"] },
  }),
  200,
);

It collects each referenced table’s ids with gridDatasetReferenceIds() and runs computeFields() once per table.
The fields for a table are its rowCaption, when the caption is a computed field, and the names names lists for the table, such as the needs of the table’s label slot.
A table with no fields to compute, or no ids, is left out. A dataset with nothing to compute is returned unchanged. Existing dataset.computed values are kept, and new values for the same table and field replace them.
tables takes the table definitions as a list, or the table catalog. A referenced table missing from it throws an error naming the dataset and the table. A failing field rejects the promise.
computeFields() does not check that the request may read the ids. Call the helper only on a dataset whose ids come from the report’s own scoped reads.

Tree paths in report SQL

treePathRelation(table, auth, options?) from @sapporta/server returns one member of a with recursive list, tree_paths(id, parent_path, path), holding the path of every row of a tree table that auth may see. A report joins it like any other guarded base relation. See Computed fields and tree paths.