Skip to content
Sapporta

Scoped report data

View as Markdown

A report can leak a row without displaying it. Counts, percentages, empty groups, totals, and drill-through IDs all reveal facts about their inputs. The safe sequence is fixed: authorize the report action, resolve data authority, scope every base read, and only then join, group, or map.

Guard every participating table

Section titled “Guard every participating table”

The project-progress store receives the already-authorized workspace context. It creates one guard for projects and another for tasks, then combines the product filter with each table’s row predicate before either query executes:

import { eq } from "drizzle-orm";
import type { SapportaEnv } from "@sapporta/server";
import type { AuthorizedWorkspaceDataContext } from "../project-auth/middleware.js";


type ReadProjectProgressRowsOptions = {
  db: SapportaEnv["Variables"]["db"];
  auth: AuthorizedWorkspaceDataContext;
  projectId?: number;
};


export async function readProjectProgressRows({
  db,
  auth,
  projectId,
}: ReadProjectProgressRowsOptions) {
  const projectAccess = auth.rowSecurity.forTable(projects);
  const taskAccess = auth.rowSecurity.forTable(tasks);


  const projectFilter =
    projectId === undefined ? undefined : eq(projectsTable.id, projectId);
  const taskFilter =
    projectId === undefined ? undefined : eq(tasksTable.project_id, projectId);


  const visibleProjects = await db
    .select()
    .from(projectsTable)
    .where(projectAccess.ownedRows(projectFilter));


  const visibleTasks = await db
    .select()
    .from(tasksTable)
    .where(taskAccess.ownedRows(taskFilter));


  return { projects: visibleProjects, tasks: visibleTasks };
}

ownedRows(productFilter) AND-composes the product filter with the trusted row predicate. Scoping the project query does not make an unscoped task query safe. The same rule applies to every joined table, including tables that contribute only a count or footer value.

The store returns ordinary visible rows. The route passes those rows and its explicit date baseline to projectProgressDataset(...); the mapper contains no auth or database code.

Keep authority out of report input

Section titled “Keep authority out of report input”

The request may choose product filters such as project_id, status, or a date range. It must not choose workspace_id, workspaceId, scoped_to_user_id, or scopedToUserId.

export const projectProgressQuerySchema = z.object({
  project_id: z.coerce.number().int().positive().optional(),
});

Even a valid primary key is only a filter. The route’s authorized context and ownedRows(...) supply the authority boundary.

Avoid this contract:

// Wrong: the caller is selecting its own authority boundary.
z.object({
  workspace_id: z.string(),
  project_id: z.coerce.number().int().positive().optional(),
});

Move large aggregation without dropping scope

Section titled “Move large aggregation without dropping scope”

The two-read example is intentionally educational and in-memory. Moving it behind a route centralizes reuse, but it still loads every visible input row. When a question needs only a filtered total or one-column group from one table, scopedRows().count() and .countBy() keep that work in SQL under the table’s row predicate. When the report combines tables or calculates a reusable business measure, move the grouping and totals into a store query.

Keep the same ordering in the store:

  1. build one row guard for each base table;
  2. apply ownedRows(productFilter) to each base relation;
  3. join or aggregate only those scoped relations; and
  4. return a small ordinary result for dataset mapping.

Drizzle queries should carry the predicates into the database. If a required shape cannot be expressed safely with the scoped primitives, isolate raw SQL in a store module and build explicit guarded base-row CTEs before joining or grouping. Raw SQL bypasses row helpers, so its review and negative tests are part of the security boundary.

A grouped store query that buckets rows by day uses to_tz_date(column, :zone) with the zone the route resolved, inside the scoped base relation and after the range is bounded:

SELECT to_tz_date(created_at, :zone) AS day, count(*) AS n
FROM   visible_txns
WHERE  (:from  IS NULL OR created_at >= :from)
  AND  (:until IS NULL OR created_at <  :until)
GROUP  BY day

The zone is a report input, not a row predicate, so it changes which day a row is counted under and never which rows are visible. Group and filter by day owns the function’s cost and constraints.

Prove the absence of cross-workspace input

Section titled “Prove the absence of cross-workspace input”

Use a local test fixture that creates its own records:

  • Workspace A has visible projects and tasks with known totals.
  • Workspace B has at least one project and task that would change those totals if either base read leaked.
  • A caller without the report ability is rejected.
  • Workspace A filtering by Workspace B’s project ID yields the declared empty or not-found behavior without identifying the hidden project.

Run the report as Workspace A before and after inserting the Workspace B rows. Its nodes, footer totals, hidden IDs, and completion ratio must remain byte-for-byte equivalent. Then compare the report totals with generated, row-scoped reads of projects and tasks under the same authority.

A 200 response proves only that the route ran. It does not prove that hidden rows contributed zero values.

Section titled “Related documentation”