metadata.json
{
"version": "0.1.0",
"author": "Jasim A Basheer (https://x.com/jasim_ab)",
"repository": "https://github.com/jasim/sapporta-skills",
"date": "May 2026",
"abstract": "Application-first guidance for creating or extending Sapporta projects: scaffold the workspace, model resources and workflows, organize the sidebar and home page around everyday work, keep generated and specialist destinations under Advanced, build connected forms and Grids, preserve access boundaries, and validate complete user outcomes."
}
references/app-building/backend/domain-code.md
# User Code Patterns
Sapporta provides table and CRUD plumbing. Write reports, business logic,
workflows, and domain invariants as app code. Keep that code testable and
independent of Hono request objects.
Docs:
- Custom endpoints: https://sapporta.com/docs/guides/application-code/custom-api-endpoints.md
- Auth and row security: https://sapporta.com/docs/reference/server/auth-and-row-security.md
- Row-scoped data helpers: https://sapporta.com/docs/reference/server/row-scoped-data-helpers.md
- Agent development workflow: https://sapporta.com/docs/guides/discovery/develop-with-a-coding-agent.md
## Auth-Aware Workflow Code
Resolve auth in the route handler. Call the narrowest project
ability/data-authority helper once, pass a typed `{ db, auth }` context into
services/stores, and keep domain code independent of Hono.
Persistence code that touches scoped tables should use `scopedRows(db, auth,
table)` for ordinary CRUD and `auth.rowSecurity.forTable(table)` for custom
Drizzle queries, joins, transactions, aggregates, and multi-step writes.
Read the row-scoped data-helper reference before selecting bounded reads, pages,
scans, lookups, counts, or generated HTTP query resolvers. Domain code should
construct Drizzle predicates directly; do not manufacture HTTP query grammar in
a service or store.
Do not thread `workspace_id`, `workspaceId`, `scoped_to_user_id`, or
`scopedToUserId` through service inputs. Do not mutate by primary key alone,
fetch broadly and filter ownership in JavaScript, or let a client-provided
scope field decide row visibility.
When raw SQL is genuinely needed, keep it in the module's store layer rather
than in route or workflow code. Document why scoped helpers do not fit, and
preserve the same data-authority row predicate the corresponding scoped helper
would have applied.
## Routing
- Deep workflow failures that need specific HTTP statuses -> read
[typed-errors.md](typed-errors.md)
references/app-building/backend/endpoints.md
# Application Endpoints
Put app-owned route entrypoints in `packages/api/app/`. Each typed route should
register a shared ts-rest contract with `TsRestApi`.
Use the public docs for exact contract syntax, handler arguments, multipart
uploads, response content types, OpenAPI behavior, and typed clients:
- Custom endpoints: https://sapporta.com/docs/guides/application-code/custom-api-endpoints.md
- Domain workflows and transactions: https://sapporta.com/docs/guides/application-code/domain-workflows-and-transactions.md
- Typed clients: https://sapporta.com/docs/guides/application-code/typed-api-clients.md
- Errors and endpoint patterns: https://sapporta.com/docs/guides/application-code/errors-uploads-and-endpoint-patterns.md
- Serialization and API errors: https://sapporta.com/docs/reference/contracts/serialization-and-api-errors.md
- OpenAPI discovery: https://sapporta.com/docs/reference/http/openapi.md
- Auth and row security: https://sapporta.com/docs/reference/server/auth-and-row-security.md
- Row-scoped data helpers: https://sapporta.com/docs/reference/server/row-scoped-data-helpers.md
- Search table rows and relationships: https://sapporta.com/docs/guides/model-data/search-indexes-and-display-metadata.md
## Contents
- [File Placement](#file-placement)
- [Auth Boundary](#auth-boundary)
- [Atomic Multi-Table Writes](#atomic-multi-table-writes)
- [Backend Organization](#backend-organization)
- [Error Handling](#error-handling)
- [Validation](#validation)
- [Common Pitfalls](#common-pitfalls)
- [Related Reading](#related-reading)
## File Placement
Use this trio for a feature `<feature>`:
- `packages/shared/src/contracts/<feature>.ts` for the contract.
- `packages/api/app/<feature>.ts` for the route adapter.
- `packages/frontend/src/api.ts` for a typed browser client when the frontend
calls the route.
Re-export shared contracts from `packages/shared/src/contracts/index.ts`. Keep
the shared package browser-safe: contracts, Zod schemas, wire types, constants,
pure serializers only. No React, Hono, Drizzle, database handles, file I/O, or
route handlers.
After creating a route file, mount and extend it from `loadApp()` in
`packages/api/app.ts`. `app.route("/", featureApi)` mounts the Hono runtime
handlers. `app.extend(featureApi)` adds the sub-app's registered contracts to
the combined OpenAPI document. A `route()` call alone does not add those
contracts to `app.docEmitters`.
`app` is already scoped to `/api`, so contract paths should be bare app paths
like `/invoices/:id/void`, not `/api/invoices/:id/void`.
```ts
app.route("/", invoiceApi);
app.extend(invoiceApi);
```
Verify the mounted route:
```bash
pnpm exec sapporta endpoints show "METHOD /api/your/path"
```
## Auth Boundary
Resolve auth at the route edge with the narrowest data-authority helper that
fits the workflow.
### Default Data Primitive
Use the highest-level primitive that fits:
1. Standard CRUD for one table -> use the generated table API and surfaces; do
not create an app-owned CRUD endpoint.
2. Ordinary app-owned work on one table -> use
`scopedRows(db, auth, table, { searchPlan: catalog.searchPlanFor(table.sqlName) })`.
3. Joins, aggregates, transactions, or multi-table state transitions -> use
`auth.rowSecurity.forTable(table)` with Drizzle, creating a guard for every
scoped table touched.
4. Raw SQL -> only when these primitives cannot express the query. Keep it in a
store/db module, document why, and establish a named scoped base set for each
scoped table before joining, aggregating, or writing.
Read the row-scoped data-helper reference before implementing a bounded read,
page, complete scan, lookup, count, or custom generated-table adapter. Prefer
`findMany()` when no total is needed, `page()` for page metadata, `scan()` for a
complete sequential selection, and `count()`/`countBy()` for aggregates; use
the reference for current inputs and bounds. Domain code builds Drizzle
expressions; only an HTTP adapter should translate canonical table query strings
through the documented named query resolvers.
Treat `scanTableRows()` as an unscoped storage primitive. Prefer
`scopedRows().scan()`; when raw scanning is deliberate, compose the table
guard's `ownedRows(...)` predicate explicitly.
Never manually stamp or filter `workspace_id`, `workspaceId`,
`scoped_to_user_id`, or `scopedToUserId`. In particular, do not extract a
workspace or user-scope value from `auth` or the request and write your own
scope predicate as a substitute for `scopedRows()` or `rowSecurity`. Never
mutate scoped rows by primary key alone, insert `request.body` directly into
scoped tables, or fetch broadly and filter row ownership in JavaScript.
## Atomic Multi-Table Writes
For a custom endpoint that creates or changes a parent plus details, line
items, history, or another related table, first inspect the app for an existing
local pattern:
```bash
rg -n 'db\.transaction|rowSecurity\.forTable|insertValuesSync|serverValues' packages/api
```
Use one `auth.rowSecurity.forTable(...)` guard per participating table. Scope
reads in SQL, prepare transaction writes with `insertValuesSync(tx, ...)`, and
pass parent keys or other server-authored references through `serverValues`.
The default Sapporta SQLite transaction callback is synchronous; do not mark it
`async` or await work inside it.
If the app has no local example, follow
[parent-detail-transactions.md](parent-detail-transactions.md). It vendors the
canonical recipe in full, so the code needs no doc fetch.
## Backend Organization
Read [domain-code.md](domain-code.md) when workflow orchestration, invariants,
or persistence logic moves outside a thin route adapter. If an expected failure
can originate in that deeper code, also read [typed-errors.md](typed-errors.md)
before implementing the handler.
Keep route files thin:
- Route adapters resolve auth, read `c.get("db")`, call a service, and return
the typed response.
- Services orchestrate domain workflows.
- Store/db modules own database reads and writes.
- Store functions accept `db` plus `auth`, or a small typed context such as
`{ db, auth }`.
- Raw SQL stays inside `db/` modules with a short justification.
Small apps may keep a thin route file directly in `packages/api/app/`. Larger
features should use domain modules under `packages/api/modules/<domain>/`, for
example:
```text
packages/api/modules/orders/
db/order-store.ts
services/create-order.ts
services/fulfill-order.ts
routes/orders.ts
```
Do not pile parser, workflow, and database logic directly into
`packages/api/app/`. Do not create root-level `models/`, `controllers/`,
`services/`, `views/`, or `css/` folders under `packages/api/`.
## Error Handling
Return declared `{ status, body }` responses for expected failures. Declare
every returned status in the contract.
If the contract declares an expected non-2xx response that a service, store, or
other code below the route adapter can raise, read
[typed-errors.md](typed-errors.md) before implementing the handler. This route
is mandatory for that task shape. Catch the workflow error family once at the
route edge, map it to a declared status and stable body, and let unexpected
errors reach the default error handler.
Unexpected errors should bubble to the app's default error handler. Use raw
`Response` returns only for deliberate escape hatches such as streaming,
downloads, custom headers, or response shapes that do not fit the normal
contract return.
## Validation
Run the narrowest proof:
```bash
pnpm exec sapporta endpoints show "METHOD /api/your/path"
pnpm build
```
If `endpoints show` says the route is not found, check `loadApp()` mounting
and the contract path before writing frontend code.
For every expected failure, verify:
- the shared contract declares the status and response schema;
- the route edge contains exactly one mapping for the workflow error family;
- the response body is stable and matches the declared schema;
- a frontend action catches and handles `ApiError` when the screen exposes the
operation; and
- focused checks cover one success response and each expected failure branch.
## Common Pitfalls
- File exists under `packages/api/app/` but is not mounted from `loadApp()`.
- Sub-app is mounted with `route()` but is not merged with `extend()`, so the
handler works at runtime but is absent from OpenAPI and CLI discovery.
- Contract declared inline in `packages/api/app/`, so the frontend cannot import
the same route shape.
- Contract path repeats `/api`.
- Multipart handler reads a file from `request.body` instead of `files`.
- Handler returns a status not declared in `responses`.
- Service-level expected failures bypass the typed-error route or are caught in
multiple layers.
- Client sends scope fields or a full `request.body` directly into a scoped
table write.
- Route updates/deletes a scoped row by primary key alone.
## Related Reading
- Endpoint runs a domain workflow — orchestration across services, a multi-step
state transition, or a transaction:
https://sapporta.com/docs/guides/application-code/domain-workflows-and-transactions.md
- Endpoint reads or writes data — confirm every response and write is scoped to
the authenticated user and workspace:
https://sapporta.com/docs/reference/server/row-scoped-data-helpers.md
references/app-building/backend/parent-detail-transactions.md
# Parent-Detail Transactions
<!-- vendored-from: https://sapporta.com/docs/guides/application-code/parent-detail-transactions.md -->
Vendored copy of the canonical parent-detail recipe. Use it directly; do not
fetch the upstream page to obtain this code.
A parent-detail create is one domain operation. It validates referenced rows,
creates the parent, authors each detail's parent key on the server, and commits
every write or none of them. Resolve authentication and ability at the route
edge before calling this workflow.
## Guard Every Table In The Transaction
Create one row-security guard for every participating table. Scope
referenced-row reads in SQL. Pass parent keys and other trusted fields through
`serverValues`; never copy ownership or parent keys from client input.
`forTable()` takes the table definition (`parents`); Drizzle statements take the
Drizzle table (`parentsTable`). Those are two different exports.
```ts
import { eq } from "drizzle-orm";
const parentAccess = auth.rowSecurity.forTable(parents);
const detailAccess = auth.rowSecurity.forTable(details);
const referencedAccess = auth.rowSecurity.forTable(referencedRows);
const result = db.transaction((tx) => {
const referenced = tx
.select({ id: referencedRowsTable.id })
.from(referencedRowsTable)
.where(
referencedAccess.ownedRows(
eq(referencedRowsTable.id, input.referenced_id),
),
)
.get();
if (!referenced) throw new ReferencedRowNotFoundError();
const parentValues = parentAccess.insertValuesSync(tx, input.parent);
const parent = tx
.insert(parentsTable)
.values(parentValues as typeof parentsTable.$inferInsert)
.returning({ id: parentsTable.id })
.get();
const insertedDetails = input.details.map((detail) => {
const detailValues = detailAccess.insertValuesSync(tx, detail, {
serverValues: { parent_id: parent.id },
});
return tx
.insert(detailsTable)
.values(detailValues as typeof detailsTable.$inferInsert)
.returning()
.get();
});
return { parent, details: insertedDetails };
});
```
`insertValuesSync()` rejects caller ownership fields and server-managed
references, merges trusted `serverValues`, validates final foreign-key
visibility, and stamps request ownership. It prepares values for Drizzle; the
workflow still executes each insert and returns the result.
## Declare The Child Key Server-Owned
The detail row's parent key is authored on the server. Declaring that in table
metadata is what turns a caller who submits it into a refusal:
```ts
// On the detail table: either declaration removes the key from the insert shape.
references: { parent_id: { apiSettable: false } }
columns: { parent_id: { apiWritable: false } }
```
With the declaration, a request carrying `parent_id` answers `422
VALIDATION_FAILED` naming that column, and writes nothing. Without it, the same
request answers `201` and the server's parent key silently replaces the value
the caller sent. The row is correct either way, and the caller is told nothing
about the value it lost.
This is also what the generated master-with-`$details` create branch relies on,
so a table that declares `meta.children` should declare the child key
non-writable on the same pass.
The default `better-sqlite3` transaction callback is synchronous. Keep database
reads, writes, and synchronous row-security preparation inside it. Perform mail,
storage, network, and other awaited effects after commit. Leave the callback
synchronous: marking it `async` or awaiting work inside it breaks the
all-or-nothing guarantee.
## Prove All-Or-Nothing Behavior
Use an isolated fixture and verify:
- a valid parent and every detail commit together;
- an invisible referenced row follows the declared concealed-not-found branch;
- caller-supplied ownership fields are rejected;
- a caller-supplied parent key is refused with `422` where the detail table
declares that reference `apiSettable: false`, and is silently replaced by the
server's parent key where it does not;
- a failure on any detail insert leaves no parent or earlier detail rows; and
- returned IDs and server-authored values match authoritative read-back.
When the contract declares the concealed-not-found branch as an expected
non-2xx outcome, read [typed-errors.md](typed-errors.md) before implementing
`ReferencedRowNotFoundError` and the handler that translates it.
## Canonical Docs
Fetch these with `curl -sL` only when you need behavior beyond the recipe above:
- https://sapporta.com/docs/guides/application-code/parent-detail-transactions.md
- https://sapporta.com/docs/guides/application-code/domain-workflows-and-transactions.md
- https://sapporta.com/docs/reference/server/row-scoped-data-helpers.md
references/app-building/backend/typed-errors.md
# Typed Domain Errors
If a contract declares an expected non-2xx response that can be raised below
the route adapter, use typed domain errors before implementing the handler.
Simple checks directly inside a handler can return declared `{ status, body }`
values instead.
Docs:
- Errors and endpoint patterns: https://sapporta.com/docs/guides/application-code/errors-uploads-and-endpoint-patterns.md
- Serialization and API errors: https://sapporta.com/docs/reference/contracts/serialization-and-api-errors.md
## Imports
The app's own `your-app-shared` package does not re-export the error envelope.
Import it from the framework:
```ts
import { errorBodySchema, type ErrorBody } from "@sapporta/shared/contracts";
import { ApiError } from "@sapporta/shared/client";
```
Declare non-2xx `responses` entries with `errorBodySchema`, type a handler's
error return with `ErrorBody`, and catch `ApiError` in frontend actions.
## Pattern
1. Define one abstract error base per workflow family, near the code that raises
the errors. The base carries HTTP status and a payload method.
2. Raise concrete subclasses at the point where the failure is first known.
3. Catch the family once at the route edge, return a declared `{ status, body }`
with a stable payload, and rethrow everything else to the default 500 path.
Wrap the whole async handler body when adapter-stage parsing/extraction can
raise the same family. Each returned status must be declared in the shared
contract's `responses`.
## Status Semantics
- `409` -> the request is valid, but the resource is in a conflicting state.
- `422` -> request parsed, but the workflow cannot accept what the server
derived or validated.
- `502` -> an upstream service failed or returned unusable data.
- `400` / `404` -> usually return directly in the handler for one-off checks,
unless the failure is naturally raised deep inside the workflow.
## Conventions
- One base per workflow family, not one app-wide base of your own.
- Keep the base module-private unless another module also catches or raises it.
- Set `this.name` in subclasses for useful logs.
- Use snake_case payload keys to match wire conventions.
- When a frontend action exposes the operation, catch `ApiError` and handle the
declared status/body instead of replacing it with an unrelated envelope.
- Test one success result and every declared workflow failure that the service
can raise.
references/app-building/days-and-time-zones.md
# Days And Time Zones
Timestamps are stored in UTC. A day is a calendar day in the **active
workspace's** time zone, not the host's and not the reader's. Every operation
that turns a stored instant into a day — a report bucket, a range filter, a
grid cell, a baseline — resolves it against that one zone.
- Contract, accessors, and codecs:
https://sapporta.com/docs/reference/server/days-and-time-zones.md
- Bounding a range and bucketing by day:
https://sapporta.com/docs/guides/reports/group-and-filter-by-day.md
- Where the zone lives and who may change it:
https://sapporta.com/docs/guides/security/workspaces-ownership-and-row-visibility.md
## Never Read The Host Clock
`Temporal.Now.timeZoneId()` and `Temporal.Now.plainDateISO()` with no argument
read the host's `TZ`. A report built on either returns different rows depending
on how the container was started. A framework test fails the build for any
reader of an ambient zone.
- Server: `workspaceTimeZone(auth)` from `@sapporta/server`. It throws for a
request with no workspace — an anonymous public route, or one holding only
`systemGlobalOnly` authority. Design for that rather than defaulting to UTC.
- Frontend: `appTimeZone()` from `@sapporta/frontend/platform`. A plain value,
no hook, nothing async — it is published before any route renders.
- Inject a clock as `Temporal.Instant`, never as a `PlainDate`. An instant
names a moment and carries no zone; the handler resolves the day.
- Pass the zone into a mapper as an argument, the same way `asOf` is passed. A
mapper that reads an ambient zone makes the same rows produce two datasets.
- `deviceTimeZone()` has two legitimate callers: the sign-up request, and
`pnpm seed`, which puts the seeded workspace on the seeding machine's zone.
## Bound A Range, Then Group
`resolveDateRangeQueryBounds(name, params, zone, now)` returns both shapes a
column can be compared against. Pick by column type, and do not mix them:
- `date` column -> `period.days`, inclusive on both ends.
- `timestamp` column -> `period.instants`, **half-open**: compare `>= from` and
`< until`. An inclusive upper bound on a timestamp column drops its own last
day, and a bound built from `23:59:59` loses an hour across a DST transition.
Group with `to_tz_date(column, :zone)`. Bound the range in `WHERE` first — the
function costs ~6µs a row. Never `CREATE INDEX` over it. A bare `sqlite3` shell
does not have it; `sapporta sql query` does, because it runs on the app's own
connection.
## On Screen
- Render `ReportTimeZoneNote` from `@sapporta/frontend/report` in the toolbar of
any report whose numbers depend on the zone.
- Do not add a per-user or per-device zone preference. The framework ships an
owner-only Workspace settings screen; the zone is a workspace fact.
- Do not pass a `zone` option to a date or timestamp column, and do not convert
CSV export or clipboard copy to display text — both keep the stored UTC
instant on purpose.
## Inspect The Application
```bash
rg -n "Temporal\.Now\.(timeZoneId|plainDateISO)\(\)" packages
rg -n "resolveDateRangeQueryBounds|to_tz_date" packages/api
```
The first command must return nothing outside a `Temporal.Now.plainDateISO(zone)`
call that names its zone.
references/app-building/frontend/form-template/route-wiring.tsx.example
// Adapt this fragment in packages/frontend/src/App.tsx after the corresponding
// TasksWorkspace, TaskDetail, and TaskForm components exist.
import { lazy, Suspense } from "react";
import { Navigate, Route } from "react-router-dom";
import type { Navigation } from "@sapporta/frontend/shell";
import { ListTodo } from "lucide-react";
const TasksWorkspace = lazy(() =>
import("./tasks/TasksWorkspace").then((module) => ({
default: module.TasksWorkspace,
})),
);
const TaskDetail = lazy(() =>
import("./tasks/TaskDetail").then((module) => ({
default: module.TaskDetail,
})),
);
const TaskForm = lazy(() =>
import("./tasks/TaskForm").then((module) => ({
default: module.TaskForm,
})),
);
function RouteFallback() {
return (
<div className="p-[18px] text-sap-data text-sap-muted">Loading...</div>
);
}
export const appNavigation: Navigation = [
{
label: "Work",
items: [
{
label: "Tasks",
icon: ListTodo,
to: "/tasks",
},
],
},
];
export const appHomeRoute = (
<Route index element={<Navigate to="/tasks" replace />} />
);
export const appPublicRoutes = <></>;
export const appProtectedRoutes = (
<>
<Route
path="tasks"
element={
<Suspense fallback={<RouteFallback />}>
<TasksWorkspace />
</Suspense>
}
/>
<Route
path="tasks/new"
element={
<Suspense fallback={<RouteFallback />}>
<TaskForm mode="new" />
</Suspense>
}
/>
<Route
path="tasks/:id"
element={
<Suspense fallback={<RouteFallback />}>
<TaskDetail />
</Suspense>
}
/>
<Route
path="tasks/:id/edit"
element={
<Suspense fallback={<RouteFallback />}>
<TaskForm mode="edit" />
</Suspense>
}
/>
</>
);
// Link the components with these application actions:
//
// TasksWorkspace New -> navigate("/tasks/new")
// list activation with preview -> navigate(`/tasks/${id}/edit`)
// list activation without preview -> navigate(`/tasks/${id}`)
// TaskDetail Edit -> navigate(`/tasks/${id}/edit`)
// create/edit success -> navigate(`/tasks/${id}`)
// project-context create -> navigate(`/tasks/new?project_id=${id}`)
//
// Keep forms and record routes out of appNavigation. They remain reachable
// through list, preview, and detail actions.
references/app-building/frontend/form-template/SimpleTaskForm.tsx.example
// Compact one-table create/edit reference for a generated Sapporta project.
// Adapt its wire schema, fields, routes, error presentation, and layout. This
// example assumes the canonical tasks.project_id -> projects.id relationship.
import { useMemo, type FormEvent } from "react";
import { useForm } from "@tanstack/react-form";
import { useMutation, useQuery, useQueryClient } from "@tanstack/react-query";
import {
FormField,
buildRecordFormFields,
createTableRow,
fieldModelForColumn,
foreignKeyFieldModelForColumn,
parseCreateDraft,
reloadTGridRows,
updateTableRow,
useSchemaStore,
} from "@sapporta/frontend";
import {
FormSubmissionError,
fieldIssuesForSubmissionError,
firstFormErrorMessage,
} from "@sapporta/frontend/form";
import {
tableQueryKeys,
tableRecordQueryOptions,
} from "@sapporta/frontend/table/query";
import { LookupPicker, useLookupStore } from "@sapporta/frontend/lookup";
import { ApiError } from "@sapporta/shared/client";
import { apiProblemFromBody } from "@sapporta/shared/validation";
import { Button } from "@sapporta/ui/button";
import { Label } from "@sapporta/ui/label";
import { useNavigate, useParams, useSearchParams } from "react-router-dom";
import { z } from "zod";
// Row projection: the `tasks` columns this form reads, with date columns as the
// strings the generated routes return. Inline only so this file typechecks
// standalone; in an application it belongs in packages/shared/src/contracts/.
// See ../row-projections.md.
const taskWireSchema = z.object({
id: z.union([z.number(), z.string()]),
title: z.string(),
project_id: z.number(),
due_date: z.string().nullable(),
});
type TaskWire = z.output<typeof taskWireSchema>;
interface TaskFormValues {
title: string;
project_id: number | null;
due_date: string;
}
type TaskFormFields = ReturnType<typeof buildRecordFormFields>;
type TaskFormMode = "new" | "edit";
export function TaskForm({ mode }: { mode: TaskFormMode }) {
return mode === "new" ? <NewTaskForm /> : <EditTaskForm />;
}
function NewTaskForm() {
const [searchParams] = useSearchParams();
const navigate = useNavigate();
const queryClient = useQueryClient();
const { table, fields } = useTaskFormMetadata();
const saveTask = useMutation({
mutationFn: async (values: TaskFormValues) => {
if (!table) {
throw new FormSubmissionError([
{ field: "form", message: "Task metadata is unavailable." },
]);
}
const parsed = parseCreateDraft(table, taskWriteFromForm(values));
if (!parsed.ok) throw new FormSubmissionError(parsed.issues);
return createTableRow("tasks", parsed.value);
},
onSuccess: async (response) => {
await queryClient.invalidateQueries({
queryKey: tableQueryKeys.table("tasks"),
});
reloadTGridRows("tasks");
const id = recordIdFromCreateResponse(response.data);
navigate(`/tasks/${id}`, { replace: true });
},
});
if (!table) return <p>Loading task metadata...</p>;
return (
<TaskEditor
formId="tasks:new"
fields={fields}
initialValues={{
title: "",
project_id: recordIdFromParam(searchParams.get("project_id")),
due_date: "",
}}
submitLabel="Create task"
onCancel={() => navigate("/tasks")}
onSubmit={saveTask.mutateAsync}
/>
);
}
function EditTaskForm() {
const taskId = recordIdFromParam(useParams<{ id: string }>().id ?? null);
if (taskId === null) return <p>The task URL does not contain a valid id.</p>;
return <EditTaskFormForId taskId={taskId} />;
}
function EditTaskFormForId({ taskId }: { taskId: number }) {
const navigate = useNavigate();
const queryClient = useQueryClient();
const { table, fields } = useTaskFormMetadata();
const task = useQuery(
tableRecordQueryOptions({
tableName: "tasks",
recordId: String(taskId),
decodeRow: (row: Record<string, unknown>) => taskWireSchema.parse(row),
}),
);
const saveTask = useMutation({
mutationFn: (values: TaskFormValues) =>
updateTableRow("tasks", String(taskId), taskWriteFromForm(values)),
onSuccess: async () => {
await queryClient.invalidateQueries({
queryKey: tableQueryKeys.table("tasks"),
});
reloadTGridRows("tasks");
navigate(`/tasks/${taskId}`, { replace: true });
},
});
if (!table || task.isPending) return <p>Loading task...</p>;
if (task.isError) {
return (
<p role="alert">
{task.error instanceof Error
? task.error.message
: "Could not load task."}
</p>
);
}
return (
<TaskEditor
key={`tasks:${taskId}`}
formId={`tasks:${taskId}`}
fields={fields}
initialValues={taskFormValuesFromWire(task.data)}
submitLabel="Save changes"
onCancel={() => navigate(`/tasks/${taskId}`)}
onSubmit={saveTask.mutateAsync}
/>
);
}
function TaskEditor({
formId,
fields,
initialValues,
submitLabel,
onCancel,
onSubmit,
}: {
formId: string;
fields: TaskFormFields;
initialValues: TaskFormValues;
submitLabel: string;
onCancel: () => void;
onSubmit: (values: TaskFormValues) => Promise<unknown>;
}) {
const titleField = fieldModelForColumn(fields, "title");
const projectField = foreignKeyFieldModelForColumn(fields, "project_id");
const dueDateField = fieldModelForColumn(fields, "due_date");
const form = useForm({
formId,
defaultValues: initialValues,
validators: { onSubmit: ({ value }) => taskFormErrors(value) },
onSubmit: async ({ value, formApi }) => {
try {
await onSubmit(value);
} catch (error) {
formApi.setErrorMap({ onSubmit: submissionErrorMap(error) });
throw error;
}
},
});
function handleSubmit(event: FormEvent<HTMLFormElement>) {
event.preventDefault();
event.stopPropagation();
form.setErrorMap({ onSubmit: undefined });
void form.handleSubmit().catch(() => undefined);
}
function clearSubmissionError() {
form.setErrorMap({ onSubmit: undefined });
}
return (
<form onSubmit={handleSubmit} noValidate>
<form.Subscribe
selector={(state) =>
[state.isSubmitting, state.errorMap.onSubmit] as const
}
>
{([isSubmitting, submitError]) => (
<fieldset disabled={isSubmitting}>
<form.Field name="title">
{(field) => (
<FormField
field={titleField}
value={field.state.value}
issue={firstFormErrorMessage(field.state.meta.errors)}
onChange={(value) => {
clearSubmissionError();
field.handleChange(String(value ?? ""));
}}
/>
)}
</form.Field>
<form.Field name="project_id">
{(field) => {
const issue = firstFormErrorMessage(field.state.meta.errors);
return (
<div>
<Label htmlFor="task-project">
{projectField.column.label}
</Label>
<LookupPicker
id="task-project"
lookup={projectField.lookup}
value={field.state.value}
onChange={(value) => {
clearSubmissionError();
field.handleChange(
typeof value === "number" ? value : null,
);
}}
ariaInvalid={issue !== undefined}
/>
{issue ? <p role="alert">{issue}</p> : null}
</div>
);
}}
</form.Field>
<form.Field name="due_date">
{(field) => (
<FormField
field={dueDateField}
value={field.state.value}
issue={firstFormErrorMessage(field.state.meta.errors)}
onChange={(value) => {
clearSubmissionError();
field.handleChange(String(value ?? ""));
}}
/>
)}
</form.Field>
{submissionFormMessage(submitError) ? (
<p role="alert">{submissionFormMessage(submitError)}</p>
) : null}
<Button type="button" variant="outline" onClick={onCancel}>
Cancel
</Button>
<Button type="submit" disabled={isSubmitting}>
{isSubmitting ? "Saving..." : submitLabel}
</Button>
</fieldset>
)}
</form.Subscribe>
</form>
);
}
function useTaskFormMetadata() {
const table = useSchemaStore((state) =>
state.tables.find((candidate) => candidate.name === "tasks"),
);
const lookups = useLookupStore();
const fields = useMemo(
() => (table ? buildRecordFormFields({ table, lookups }) : []),
[lookups, table],
);
return { table, fields };
}
function taskFormErrors(values: TaskFormValues) {
const fields: Record<string, string> = {};
if (!values.title.trim()) fields.title = "Task title is required.";
if (values.project_id === null) fields.project_id = "Select a project.";
return Object.keys(fields).length > 0 ? { fields } : undefined;
}
function taskWriteFromForm(values: TaskFormValues) {
const issues: Array<{ field: string; message: string }> = [];
if (!values.title.trim()) {
issues.push({ field: "title", message: "Task title is required." });
}
if (values.project_id === null) {
issues.push({ field: "project_id", message: "Select a project." });
}
if (issues.length > 0) throw new FormSubmissionError(issues);
return {
title: values.title.trim(),
project_id: values.project_id,
due_date: values.due_date.trim() || null,
};
}
function taskFormValuesFromWire(task: TaskWire): TaskFormValues {
return {
title: task.title,
project_id: task.project_id,
due_date: task.due_date ?? "",
};
}
function recordIdFromParam(value: string | null): number | null {
if (!value) return null;
const id = Number(value);
return Number.isInteger(id) && id > 0 ? id : null;
}
function recordIdFromCreateResponse(
data: Record<string, unknown> | Record<string, unknown>[],
): string {
const record = Array.isArray(data) ? data[0] : data;
const parsed = z
.object({ id: z.union([z.number(), z.string()]) })
.safeParse(record);
if (!parsed.success) {
throw new FormSubmissionError([
{ field: "form", message: "The create response has no task id." },
]);
}
return String(parsed.data.id);
}
function submissionErrorMap(error: unknown) {
const issues = fieldIssuesForSubmissionError(error);
const problem =
error instanceof ApiError ? apiProblemFromBody(error.body) : undefined;
return {
form:
issues.find((issue) => issue.field === "form")?.message ??
problem?.summary ??
(error instanceof FormSubmissionError
? "Review the highlighted fields."
: "Could not save task."),
fields: Object.fromEntries(
issues
.filter((issue) => issue.field !== "form")
.map((issue) => [issue.field, issue.message]),
),
};
}
function submissionFormMessage(error: unknown): string | undefined {
if (!error || typeof error !== "object" || !("form" in error)) {
return undefined;
}
return typeof error.form === "string" ? error.form : undefined;
}
references/app-building/frontend/form-template/TaskForm.tsx
/**
* STRUCTURAL REFERENCE ONLY — DO NOT COPY THIS FILE AS A FEATURE SCAFFOLD.
*
* Imports from @sapporta packages represent public APIs available when this
* reference was written; confirm them against the application's installed
* version. Every APP/* module below is an intentionally unresolved
* placeholder. It marks code the application must already own or design for
* its domain. Generated projects already install TanStack Query and mount
* their queryClient. packages/frontend/src/api.ts remains the generated
* client extension point.
*
* Preserve the control-flow boundaries, not the task names, fields, UI, helper
* names, module layout, routes, cache effects, or mutation behavior.
*/
import { useMemo } from "react";
import { useForm } from "@tanstack/react-form";
import { useMutation, useQuery, useQueryClient } from "@tanstack/react-query";
import {
FormField,
buildRecordFormFields,
fieldModelForColumn,
reloadTGridRows,
useSchemaStore,
} from "@sapporta/frontend";
import {
FormSubmissionError,
fieldIssuesForSubmissionError,
firstFormErrorMessage,
} from "@sapporta/frontend/form";
import { useLookupStore } from "@sapporta/frontend/lookup";
import {
tableQueryKeys,
tableRecordQueryOptions,
} from "@sapporta/frontend/table/query";
import { ApiError } from "@sapporta/shared/client";
import {
apiProblemFromBody,
fieldIssuesFromZodError,
} from "@sapporta/shared/validation";
import { useNavigate, useParams } from "react-router-dom";
// Application-owned types and schemas come from the real domain boundary.
// `taskWireSchema` is the row projection — the `tasks` columns this form reads.
// It belongs in packages/shared/src/contracts/; see ../row-projections.md.
import {
taskFormSchema,
taskWireSchema,
type TaskFormValues,
} from "APP/task-domain";
// These functions compose current framework query/error mechanics with the
// application's domain decisions. They are placeholders, not suggested public
// framework exports and not files supplied by this reference.
import {
createTask,
taskFormValuesFromRecord,
taskIdFromRoute,
taskPath,
updateTask,
} from "APP/task-workflow";
import {
LoadingForm,
MissingTask,
TaskRequestFailure,
} from "APP/task-presentation";
type TaskFormMode = "new" | "edit";
export function TaskForm({ mode }: { mode: TaskFormMode }) {
// Choose the workflow before mounting mode-specific hooks. Create mode never
// starts an edit query; an invalid edit route never mounts that query.
return mode === "new" ? <CreateTaskForm /> : <EditTaskForm />;
}
function CreateTaskForm() {
const navigate = useNavigate();
const queryClient = useQueryClient(); // Reuse the generated provider.
const metadata = useTaskFormMetadata();
const saveTask = useMutation({
// Application code owns this transform and mutation. For an ordinary
// one-table create, it should use the current public parseCreateDraft and
// generated-table create APIs rather than another HTTP client.
mutationFn: (values: TaskFormValues) => {
if (!metadata) throw new Error("Task form metadata is unavailable.");
return createTask(metadata.table, values);
},
onSuccess: async (result) => {
// A custom endpoint may affect more than tasks; invalidate every affected
// table/custom-query scope before navigating.
await queryClient.invalidateQueries({
queryKey: tableQueryKeys.table("tasks"),
});
reloadTGridRows("tasks");
navigate(taskPath(result.taskId), { replace: true });
},
});
if (!metadata) return <LoadingForm />;
return (
<TaskEditor
key="task:create"
fields={metadata.fields}
initialValues={taskFormValuesFromRecord(null)}
onSubmit={saveTask.mutateAsync}
/>
);
}
function EditTaskForm() {
// Route parsing is application behavior because identifier shape and invalid
// route UX belong to the feature.
const taskId = taskIdFromRoute(useParams<{ id: string }>().id);
if (taskId === null) return <MissingTask />;
return <EditTaskFormForId taskId={taskId} />;
}
function EditTaskFormForId({ taskId }: { taskId: number }) {
const navigate = useNavigate();
const queryClient = useQueryClient();
const metadata = useTaskFormMetadata();
const taskQuery = useQuery(
tableRecordQueryOptions({
tableName: "tasks",
recordId: String(taskId),
decodeRow: (row: Record<string, unknown>) => taskWireSchema.parse(row),
}),
);
const saveTask = useMutation({
// updateTask owns the domain request and calls the typed client extended in
// the generated project's existing packages/frontend/src/api.ts wiring.
mutationFn: (values: TaskFormValues) => updateTask(taskId, values),
onSuccess: async (result) => {
await queryClient.invalidateQueries({
queryKey: tableQueryKeys.table("tasks"),
});
reloadTGridRows("tasks");
navigate(taskPath(result.taskId), { replace: true });
},
});
if (!metadata || taskQuery.isPending) return <LoadingForm />;
if (taskQuery.isError) {
return <TaskRequestFailure error={taskQuery.error} action="loading task" />;
}
if (!taskQuery.data) return <MissingTask />;
// Do not reset a live form from background query data. Mount the editor only
// after complete defaults exist, and key it to the record identity.
return (
<TaskEditor
key={`task:${taskId}`}
fields={metadata.fields}
initialValues={taskFormValuesFromRecord(taskQuery.data)}
onSubmit={saveTask.mutateAsync}
/>
);
}
type TaskFields = ReturnType<typeof buildRecordFormFields>;
type TaskFieldModel = ReturnType<typeof fieldModelForColumn>;
// A form instance has no nameable type of its own. Name this hook's return type
// instead, and give child components that. One useForm call per form.
function useTaskDraftForm(
initialValues: TaskFormValues,
submitDraft: (values: TaskFormValues) => Promise<unknown>,
) {
return useForm({
defaultValues: initialValues,
onSubmit: async ({ value, formApi }) => {
// The application schema owns domain validation and transformation. Map
// its issues through the current framework form-error surface.
try {
const parsed = taskFormSchema.safeParse(value);
if (!parsed.success) {
throw new FormSubmissionError(fieldIssuesFromZodError(parsed.error));
}
await submitDraft(parsed.data);
} catch (error) {
formApi.setErrorMap({ onSubmit: submissionErrorMap(error) });
throw error;
}
},
});
}
type TaskDraftForm = ReturnType<typeof useTaskDraftForm>;
function TaskEditor({
fields,
initialValues,
onSubmit,
}: {
fields: TaskFields;
initialValues: TaskFormValues;
onSubmit: (values: TaskFormValues) => Promise<unknown>;
}) {
const titleField = fieldModelForColumn(fields, "title");
const form = useTaskDraftForm(initialValues, onSubmit);
return (
// The real application owns page/dialog composition, labels, help text,
// routes, accessibility details, and visual design.
<form
noValidate
onSubmit={(event) => {
event.preventDefault();
event.stopPropagation();
form.setErrorMap({ onSubmit: undefined });
// Render the failure through form state, then consume the rejection.
void form.handleSubmit().catch(() => undefined);
}}
>
<form.Subscribe
selector={(state) =>
[
state.canSubmit,
state.isSubmitting,
state.errorMap.onSubmit,
] as const
}
>
{([canSubmit, isSubmitting, submitError]) => (
<fieldset disabled={isSubmitting}>
<TaskTitleField form={form} field={titleField} />
{/* Add only fields required by this workflow. Use metadata-derived
FormField controls, framework lookups, or application-owned
controls according to the interaction—not this task example. */}
{submissionFormMessage(submitError) ? (
<p role="alert">{submissionFormMessage(submitError)}</p>
) : null}
<button type="submit" disabled={!canSubmit || isSubmitting}>
{isSubmitting ? "Saving…" : "Save"}
</button>
</fieldset>
)}
</form.Subscribe>
</form>
);
}
function TaskTitleField({
form,
field,
}: {
form: TaskDraftForm;
field: TaskFieldModel;
}) {
return (
<form.Field name="title">
{(titleField) => (
<FormField
field={field}
value={titleField.state.value}
issue={firstFormErrorMessage(titleField.state.meta.errors)}
onChange={(value) => {
form.setErrorMap({ onSubmit: undefined });
titleField.handleChange(String(value ?? ""));
}}
/>
)}
</form.Field>
);
}
function useTaskFormMetadata() {
const table = useSchemaStore((state) =>
state.tables.find((candidate) => candidate.name === "tasks"),
);
const lookups = useLookupStore();
return useMemo(() => {
if (!table) return null;
return { table, fields: buildRecordFormFields({ table, lookups }) };
}, [lookups, table]);
}
function submissionErrorMap(error: unknown) {
const issues = fieldIssuesForSubmissionError(error);
const problem =
error instanceof ApiError ? apiProblemFromBody(error.body) : undefined;
return {
form:
issues.find((issue) => issue.field === "form")?.message ??
problem?.summary ??
(error instanceof FormSubmissionError
? "Review the highlighted fields."
: "Could not save task."),
fields: Object.fromEntries(
issues
.filter((issue) => issue.field !== "form")
.map((issue) => [issue.field, issue.message]),
),
};
}
function submissionFormMessage(error: unknown): string | undefined {
if (!error || typeof error !== "object" || !("form" in error)) {
return undefined;
}
return typeof error.form === "string" ? error.form : undefined;
}
references/app-building/frontend/forms.md
# Domain Forms
Use this file as a routing page. Canonical Sapporta documentation owns exact
exports, behavior, examples, and invariants:
Generated projects install TanStack Query and TanStack Form. They mount one
QueryClient and keep its application-wide policy in
`packages/frontend/src/query-client.ts`.
- Custom forms and cached table reads:
https://sapporta.com/docs/guides/application-code/custom-forms-and-table-queries.md
- Custom forms and validation:
https://sapporta.com/docs/guides/application-code/custom-forms-and-validation.md
- Table lookups and record ids:
https://sapporta.com/docs/reference/frontend/lookups.md
- Generated record surfaces and form helpers:
https://sapporta.com/docs/reference/frontend/generated-record-surfaces.md
- Table query options:
https://sapporta.com/docs/reference/frontend/table-query-options.md
- Generated record screens and forms:
https://sapporta.com/docs/guides/generated-surfaces/record-screens-and-forms.md
- Relationships and lookup behavior:
https://sapporta.com/docs/guides/model-data/relationships-and-lookup-behavior.md
- Serialization and API errors:
https://sapporta.com/docs/reference/contracts/serialization-and-api-errors.md
Read the custom-forms guide and both frontend references before implementing a
custom form. Confirm exact declarations against the application's installed
Sapporta version.
## Choose The Form Boundary
- Use the generated table route or `NewRecordPage` for an ordinary one-table
create whose metadata-derived order fits the workflow.
- Compose a domain form when the workflow needs sections, route defaults,
non-column inputs, a different destination, or application-specific layout.
- Use one app-owned typed endpoint when the submit changes several tables or
performs a named domain action. The server owns authorization and the
transaction.
- Read [pickers.md](pickers.md) for a picker beyond the standard scoped lookup.
- Read "Stage Multi-Row Drafts In A Grid" below when the workflow collects
repeating rows rather than a fixed set of fields.
## Stage Multi-Row Drafts In A Grid
Stage repeating draft rows — invoice lines, a roster, a bill of materials — in a
Grid over an application-owned in-memory source. Do not hand-build a stack of
Add/Edit/Delete rows, and do not keep the rows in `useState`.
- Rows want columns, keyboard navigation, or per-row state -> compose GridCore
with ColumnPreset; read [grids.md](grids.md).
- Short repeating group of ordinary inputs -> TanStack Form `mode="array"`.
Keep staged rows in the Grid runtime. The surrounding form owns header fields,
submit state, and submit errors.
Then choose where the draft becomes persistent shape. Post it to one app-owned
typed endpoint when the save spans several tables, needs one transaction, or
encodes a domain rule, and put the draft row type in the shared contract. Read
[../backend/parent-detail-transactions.md](../backend/parent-detail-transactions.md).
Pattern and trade-offs:
https://sapporta.com/docs/guides/application-code/staged-multi-row-drafts.md
## Picker Policy
Every choice control must be searchable and keyboard-operable. Do not use a
native HTML `<select>` or BaseUI's `<Select>` component in application-owned
forms, filters, or report controls.
- For a single record from a Sapporta table or foreign-key column, use
`LookupPicker` with the field's `LookupCapabilities` or `useTableLookup()`.
It preserves scoped remote search, selected-label loading, typed IDs, cache
behavior, and shared styling. Do not fetch a whole table merely to turn its
rows into local options.
- For a static enum, status, priority, or other non-table option set, compose
the Base UI `Combobox` with Sapporta's `comboboxClassNames` tokens. It must
expose text input and filtered keyboard navigation. Read [pickers.md](pickers.md)
before composing it.
- For a specialized item model, grouping, multi-select, tags, or embedded-grid
editor, use the same Base UI `Combobox` primitives; do not add a new
application-local Select wrapper.
Before completing a form, audit every choice control against this policy,
including toolbar and report filters that launch or constrain the workflow.
## Type The Form Instance
Call `useForm` inside one hook per form and name that hook's return type. Child
components declare their `form` prop with that name.
```ts
type MealForm = ReturnType<typeof useMealDraftForm>;
```
`ReturnType<typeof useForm<MealDraft>>` does not resolve, and every `form.Field`
render prop under it degrades to an implicit `any`. Keep validators and the
submit handler inside the hook so they stay part of the type.
## Map Submit Errors
- Map server field issues inside `onSubmit`: catch the rejection, convert it,
and call `formApi.setErrorMap({ onSubmit: { form, fields } })`.
- Never put the write in `validators.onSubmitAsync`. It has already changed the
database by the time validation reports a failure.
- Clear a stale submit error with one form-level `listeners.onChange`, not from
every field's `onChange`.
- A Standard Schema validator checks the schema's input type and discards
transformed output. Parse again inside `onSubmit` when the schema coerces or
transforms.
- Where fields come from table metadata, `parseCreateDraft()` already reports
required and invalid columns. Do not restate them in a second schema.
## Follow TanStack Form's Idioms
Sapporta does not wrap TanStack Form. The custom-forms-and-validation guide
listed above names the idioms this codebase relies on — `useSelector` over the
deprecated `useStore`, persistent `isDirty`, `revalidateLogic()`,
`onSubmitInvalid`, `formOptions()`, `aria-disabled` submit. Confirm each against
the library's own guides:
```bash
curl -sL https://tanstack.com/form/latest/docs/framework/react/guides/[guide].md
```
`basic-concepts`, `validation`, `submission-handling`, `reactivity`,
`listeners`, `linked-fields`, `arrays`, `dynamic-validation`,
`focus-management`, `form-composition`.
## Preserve These Boundaries
- Reuse the generated TanStack Query provider and
`packages/frontend/src/query-client.ts`. Use
`tableRecordQueryOptions()` and `tableRecordsPageQueryOptions()` with
`tableQueryKeys` for generated table reads.
- Read the table-query-options reference before building generated table
selection, page, or cache state. Reuse `buildTableSelectionQuery()`,
`buildTableRowsQuery()`, `fetchTableRow()`, and `fetchTableRows()` where their
documented lifecycle fits; do not replace their lossless repeated-filter
handling with a `Record<string, string>` or an application-local table client.
- Use TanStack Form for draft values, dirty state, local validation, submit
errors, and pending state.
- Derive standard table fields from Sapporta metadata. Do not recreate a
generic form-field, lookup, table-client, query-key, or API-error layer in the
application.
- Type a row the form loads as a shared-package row projection parsed through
`decodeRow`, never `$inferSelect`; read
[row-projections.md](row-projections.md).
- Parse route identity before mounting mode-specific hooks. Load complete edit
defaults before mounting the editor, key it to the record, and do not replace
dirty values from a background refetch.
- Use `parseCreateDraft()` for ordinary creates. Keep update transforms
explicit because omission has different create and patch meanings.
- Use `FormSubmissionError`, `fieldIssuesForSubmissionError()`, and
`firstFormErrorMessage()` for local and API field issues. Keep a form-level
fallback and consume a rendered submit rejection.
- Invalidate every affected TanStack Query scope before navigating or closing.
For ordinary table mutations, invalidate
`tableQueryKeys.table(tableName)` and call `reloadTGridRows(tableName)` for
each affected mounted TGrid. These are separate effects.
- Treat client metadata, hidden fields, fixed filters, query keys, and form
validation as presentation and request-shaping tools. The server remains the
write-policy, row-scope, validation, and authorization boundary.
## Inspect The Application
```bash
sed -n '1,160p' packages/frontend/src/query-client.ts
rg -n "tableRecordQueryOptions|tableRecordsPageQueryOptions|tableQueryKeys" packages/frontend/src
rg -n "FormField|buildRecordFormFields|parseCreateDraft|LookupPicker" packages/frontend/src
rg -n "createApiClient|getApiBase" packages/frontend/src/api.ts packages/frontend/src
```
To learn what a framework symbol is or which specifier exports it, use the API
reference; drop to the installed declarations only for behavior a signature
cannot express. Both routes are in
[../../framework-source-lookup.md](../../framework-source-lookup.md).
The local files under `form-template/` are secondary structural examples. Use
them only after reading the public guide. Adapt domain names, schemas, routes,
cache effects, error mapping, and layout; do not treat application-placeholder
imports as framework exports.
## Validate The Workflow
Ideally in a separate agent/subagent/thread/coding agent task, run
`pnpm typecheck`, then the application's tests and build. Exercise loading,
cancellation, create and edit initialization, dirty-value preservation, required
and optional values, static-combobox and foreign-key lookup fields, server field
issues, query invalidation, Grid reload, authorization, and success navigation.
references/app-building/frontend/grids.md
# Table And Grid Views
Read [views.md](views.md) first. Use this file as a routing page. Canonical
Sapporta documentation owns exact props, runtime APIs, interaction semantics,
examples, and invariants:
- Grid-first record workflows:
https://sapporta.com/docs/guides/generated-surfaces/grid-first-record-workflows.md
- Table-aware Grids and master-detail:
https://sapporta.com/docs/guides/generated-surfaces/table-aware-grids-and-customization.md
- TGrid reference:
https://sapporta.com/docs/reference/frontend/tgrid.md
- Grid interaction reference:
https://sapporta.com/grid/reference/interactions.md
- GridCore reference:
https://sapporta.com/grid/reference/grid-core.md
- Choose a Grid layer:
https://sapporta.com/grid/start/choose-a-grid-layer.md
- Filtering, sorting, search, and pagination:
https://sapporta.com/docs/guides/generated-surfaces/filtering-sorting-search-and-pagination.md
- Hierarchical Grids:
https://sapporta.com/grid/guides/hierarchical-grids.md
- Column presets, cells, and writes:
https://sapporta.com/docs/reference/frontend/tgrid/interactions-columns-and-writes.md
- Advanced rows and drafts:
https://sapporta.com/grid/guides/advanced-rows.md
Read the table-aware guide, TGrid reference, and interaction reference before
implementing row selection, active-row context, row activation, or
master-detail behavior.
## Choose The Grid Layer
- Use the generated table surface for ordinary persisted-record CRUD.
- Use `SchemaTableGridView` or TGrid when registered Sapporta tables own the
records and the application needs tailored columns, query context,
hierarchy, interaction, navigation, or surrounding layout.
- Use GridCore with ColumnPreset when the application owns temporary rows, a
composite draft, a calculated projection, or a custom data source. This is
also the layer for a form's staged multi-row draft; read
[forms.md](forms.md) for where that draft becomes persistent shape.
- Preserve search, filters, sort, pagination, CSV export, lookup labels, URL
state, loading, and errors unless the product requirement changes them.
## Leave Date, Timestamp, And Select Rendering Alone
`date` and `timestamp` are separate presets, chosen from the column's declared
kind rather than from the value in the cell. A date cell reads `2026-08-23`; a
timestamp cell reads `2026-08-23 16:38` in the workspace's zone and describes
the full moment on hover.
- Do not set widths for these columns. Each preset carries its own, and
schema-declared sizing wins where it is present.
- Do not add a date-picker editor to a timestamp column. An `<input type="date">`
has nowhere to put the time component and drops it on commit.
- Do not pass a `zone` option to a column. Read
[../days-and-time-zones.md](../days-and-time-zones.md).
- Do not reintroduce chips for select values. Select-backed columns preserve
exact option identity and render as plain text.
- `on` and `not on` are not offered on a timestamp column. A date control there
names a local day and the operator picks the edge: `on or after`/`before` take
its first instant, `after`/`on or before` its last.
## Choose The Interaction
- Use row-list interaction when arrow keys move between whole rows.
`ROW_PRIMARY_MASTER_DETAIL` keeps Enter for hierarchy expansion.
`ROW_PRIMARY_MASTER_DETAIL_WITH_ACTIVATION` uses Enter and double-click for
one semantic action and keeps left/right expansion.
- Use `ROW_MULTISELECT_LIST` for independent bulk operation targets.
- Use cell-grid interaction for cell focus, range selection, copy, inline
editing, or cell activation. Choose the matching public preset before adding
custom cells.
- Active-row state identifies one current record. Row selection identifies
operation targets. Cell selection identifies a copy or editing range. Do not
mirror any of these into parallel React state.
## Route Master-Detail Correctly
Use the active-row subscription or React hook to render a detail panel or a
foreign-key-constrained related Grid. Use the row-activation event for a
repeatable action such as opening edit, running a named command, or moving
focus into an already mounted detail Grid.
Narrow the active row's kind before treating it as a persisted data record.
Render detail loading, empty, missing, and error states inside the detail
region. Cancel or supersede requests as the active master changes.
`SchemaTableGridView` accepts an interaction but does not expose active-row or
activation props. Use `useSchemaTableGrid()` when the application needs its
session, then render `TGrid` in the application-owned composition.
## Preserve Runtime And Server Boundaries
- Grid owns stable row identity, focus, editing state, selection, hierarchy,
displayed drafts, and runtime subscriptions.
- Application services and save handlers own domain validation, persistence,
conflicts, cache effects, and workflow transactions.
- `fixedFilters`, hidden columns, and client selection are product constraints.
Backend routes and generated table APIs own authorization and row scope.
- Before staging a batch, define validation timing, cancel/discard behavior,
conflict policy, authorization, transaction scope, and partial-failure rules.
## Validate The Workflow
Run `pnpm typecheck`. Then exercise keyboard and pointer input, active-row changes, repeat activation, selection, hierarchy expansion,
loading and empty states, query constraints, persistence failures, and
authorization. Confirm that Enter is assigned to either activation or
expansion, not both.
references/app-building/frontend/pickers.md
# Searchable Pickers
Choose the highest-level existing abstraction before composing primitives.
## Contents
- [Use LookupPicker For Table Records](#use-lookuppicker-for-table-records)
- [Compose Base UI For Specialized Interactions](#compose-base-ui-for-specialized-interactions)
## Use LookupPicker For Table Records
For an ordinary single-value picker over a Sapporta table or foreign key, use
the public lookup module:
```tsx
import { LookupPicker, useTableLookup } from "@sapporta/frontend/lookup";
const accountLookup = useTableLookup<number>("accounts");
<LookupPicker<number>
id="account-id"
lookup={accountLookup}
value={accountId}
onChange={setAccountId}
placeholder="Select account..."
disabled={disabled}
className="w-full"
/>
```
Parameterize `useTableLookup` and `LookupPicker` with the same type, matched to
the target table's primary key. Parameterize both or neither; omit both for a
table whose key type is not known at the call site.
Keep a numeric id a number in component state. Cross to an address — a
`RecordId`, URL segment, or query key — with `toRecordId` from
`@sapporta/shared/record-id`, never a bare `String()`.
Typing rules, the `RecordId` boundary, and `lookupValueEquals`/`lookupValueKey`
identity: https://sapporta.com/docs/reference/frontend/lookups.md
`LookupPicker` already handles scoped remote search through
`LookupCapabilities`, selected-label loading outside the current search page,
lookup cache subscriptions, typed string/number ids, `null` clearing, disabled
state and entries, equality, keys, and shared styling. It also accepts
`allowClear`, `searchLimit`, `id`, and `className`.
If the caller already has `LookupCapabilities`, pass it directly. Otherwise,
use `useTableLookup(tableName)` instead of fetching a large table list solely
to construct picker options. Do not introduce an app-local `*Picker` around
Base UI for behavior `LookupPicker` already provides. If a domain wrapper is
genuinely useful, implement it in terms of `LookupPicker`.
`LookupPicker` remains the app-facing table lookup control. When an old
application-local combobox selects table records, migrate it to `LookupPicker`;
do not copy the `LookupPicker` implementation or materialize a table into local
options.
For short static option sets, use the Base UI `Combobox` with the shared
Sapporta style tokens. It gives users searchable, keyboard-operable choices and
is the required replacement for native HTML `<select>` and the removed
Sapporta `Select` component.
## Compose Base UI For Specialized Interactions
Compose primitives for static option sets and behavior outside `LookupPicker`:
non-table item models, custom rendering or grouping, multi-select or tags,
embedded grid editors, and special trigger, focus, popup, or layout semantics.
Import the Base UI primitives and shared Sapporta styles from focused exports:
```tsx
import { Combobox, comboboxClassNames } from "@sapporta/ui/combobox";
import { cn } from "@sapporta/ui/cn";
```
Sapporta exposes the Base UI `Combobox` namespace and shared style tokens.
Use the Base UI documentation as the canonical source for component anatomy,
props, TypeScript behavior, accessibility, composition patterns, and async
search:
- https://base-ui.com/react/components/combobox
Inspect the installed Base UI declarations when a project version differs
from the current documentation.
### Sapporta-specific composition rules
- Apply the matching `comboboxClassNames` token to each primitive. The map
provides `inputGroup`, `input`, `action`, `trigger`, `positioner`, `popup`,
`empty`, `list`, `item`, and `itemIndicator`. Merge consumer-specific layout
classes with `cn()`.
- Keep `LookupEntry` objects as `items` and as the Base UI selected value.
Translate at the domain boundary with `pickedEntry?.value ?? null`.
- Preserve numeric lookup ids as numbers. Use `lookupValueEquals()` for item
equality and `lookupValueKey()` for React keys. Convert only at an address
boundary, via `toRecordId`.
- Pass `filter={null}` when a lookup or server performs the search. Update the
remote query for the `"input-change"` reason and clear it after selection or
other input-reset events.
- Keep the selected lookup entry in the available items while remote results
change. Forward `entry.disabled` to `Combobox.Item`.
- Populate lookup items through scoped table or lookup APIs. Client filters
and raw SQL do not establish workspace or user authorization.
- For static items, provide an input, popup, empty state, filtered list,
selected-value equality, and visible selection indicator. Preserve the
static domain value at the boundary rather than converting it to labels.
Use these Sapporta monorepo implementations according to their role:
- `packages/frontend/src/lookup/LookupPicker.tsx` is the public reusable
abstraction. Import it; do not copy it into an app.
- `packages/frontend/src/table/filters/ConditionEditor.tsx` directly composes
Base UI for a specialized column picker.
- `packages/grid/src/column-preset/editors/LookupValueEditor.tsx` directly
composes Base UI for embedded grid focus and popup behavior.
references/app-building/frontend/row-projections.md
# Row Projections
The generated `/api/tables/:tableName` routes are name-generic: one route family
serves every table and returns `Row`, an alias for `Record<string, unknown>`.
Declare the columns each screen reads as a zod schema — a row projection — and
check it from `packages/api`, so a renamed or retyped column fails a test rather
than a loaded screen.
- Writing and checking a projection:
https://sapporta.com/docs/guides/application-code/cached-table-reads-and-refresh.md
- Why wire shapes live in the shared package:
https://sapporta.com/docs/guides/application-code/shared-contracts-and-request-validation.md
- Server row aliases and their Temporal types:
https://sapporta.com/docs/reference/schema/table-definitions.md
## Reuse Before Declaring
An app-owned endpoint for the same feature usually already exports the row's
shape. Two schemas for one row drift apart.
```bash
rg -n "z.object|\.extend\(" packages/shared/src/contracts
```
## Declare It In The Shared Package
One file per feature under `packages/shared/src/contracts/`, re-exported from
that directory's `index.ts`. Under `packages/frontend/src/` the API package
cannot check it.
```ts
// packages/shared/src/contracts/task-rows.ts
/** The `tasks` columns the task editor reads. */
export const taskRowSchema = z.object({
id: z.number(),
title: z.string(),
project_id: z.number().nullable(),
due_date: z.string().nullable(),
});
export type TaskRow = z.output<typeof taskRowSchema>;
```
Pass it to the query builder through `decodeRow`:
```ts
const decodeTask = (row: Row): TaskRow => taskRowSchema.parse(row);
```
List only the columns the screen reads; omit `workspace_id`,
`scoped_to_user_id`, and audit timestamps it does not display. Adding a table
column then does not require changing an existing projection. Keep SQL column
names, and convert to a camelCase view model in a separate frontend-package
function.
## Declare Date And Timestamp Columns As Strings
The routes serialize `date()` to `"2026-04-18"` and `timestamp()` to
`"2026-04-18T09:30:00Z"`. Declare both `z.string()`.
This is why `$inferSelect` and `$inferInsert` must never reach
`packages/frontend`: they type the hydrated Drizzle row, where those columns are
`Temporal.PlainDate` and `Temporal.Instant`. They are server types, and the
import would pull Drizzle and `@sapporta/server` toward the browser.
## Check Each Projection With A Test
`packages/api` is the only package that depends on both the schema and the
shared package, so put one test per projected table there. Read the row through
the path the generated route uses, so the test compares the projection against
the serialized wire row rather than the Drizzle row. Renaming `title` to `label`
must fail it.
```ts
// packages/api/schema/tasks.test.ts
import { taskRowSchema } from "task-app-shared";
it("matches the columns the task editor reads", () => {
expect(taskRowSchema.safeParse(readSampleTaskRow()).success).toBe(true);
});
```
Run it with the application's tests, alongside `pnpm typecheck` and
`pnpm build`. The runtime `decodeRow` parse is a loud failure for an unexpected
response, not drift protection: it reports a mismatch only on a loaded screen.
## Inspect The Application
```bash
rg -n "decodeRow" packages/frontend/src
rg -n "z.object" packages/shared/src/contracts
rg -n "\$inferSelect" packages/frontend/src
```
The third command must return nothing. Where it returns a match, replace that
import with a projection.
references/app-building/frontend/views.md
# Custom Frontend Views
Build screens under `packages/frontend/src/`: dashboards, import wizards,
report pages, and custom table/grid workflows. Use public
exports from `@sapporta/frontend`, `@sapporta/shared`, `@sapporta/grid`,
`@sapporta/grid/column-preset`, and generic UI components from `@sapporta/ui`.
Prefer domain-aware components and hooks from `@sapporta/frontend` before
composing generic `@sapporta/ui` primitives. When exact declarations are
needed, inspect the app's installed packages.
Form work has a separate first read. Use [forms.md](forms.md) before choosing
fields, form state, persistence, pickers, or an embedded Grid. It covers direct
TanStack Form composition with `NewRecordPage`, `FormField`,
`buildRecordFormFields`, field-model lookup functions, `parseCreateDraft`, the
generated Sapporta table API, and the generated project's TanStack Query
integration.
Type every row a screen loads from a generated table route — in a dashboard or
list as much as in a form — as a row projection parsed through `decodeRow`,
never `$inferSelect`; read [row-projections.md](row-projections.md).
Before creating an app-local field or picker, search for the public form and
lookup surface:
```bash
rg -n 'NewRecordPage|FormField|buildRecordFormFields|fieldModelForColumn|foreignKeyFieldModelForColumn|parseCreateDraft|LookupPicker|useTableLookup' packages/frontend/src
```
Docs:
- Frontend screens: https://sapporta.com/docs/guides/application-code/custom-frontend-routes-and-screens.md
- Custom forms and cached table reads: https://sapporta.com/docs/guides/application-code/custom-forms-and-table-queries.md
- Table query options: https://sapporta.com/docs/reference/frontend/table-query-options.md
- Typed clients: https://sapporta.com/docs/guides/application-code/typed-api-clients.md
- Grid-first record workflows: https://sapporta.com/docs/guides/generated-surfaces/grid-first-record-workflows.md
- Grid core model: https://sapporta.com/grid/guides/core-model.md
- Choose a Grid layer: https://sapporta.com/grid/start/choose-a-grid-layer.md
- Interactions: https://sapporta.com/grid/reference/interactions.md
- Keyboard and selection: https://sapporta.com/grid/guides/keyboard-and-selection.md
## Record Workflow Surfaces
Apply the application grammar in [../guide.md](../guide.md). For each tabular
list or related-record collection, read [grids.md](grids.md) before choosing the
Grid layer, interaction, query context, or columns.
## Routes And Navigation
Follow the current app convention. `packages/frontend/src/App.tsx` exports
`appNavigation`, `appHomeRoute`, `appPublicHomeRoute`, `appPublicRoutes`, and
`appProtectedRoutes`. Add one file per screen, then add its route.
`appHomeRoute` opens `/` for signed-in users. Put the home page there. Add every
other screen that needs a session to `appProtectedRoutes`; a screen outside
those slots loads for visitors who have not signed in. Slot semantics and
bootstrap order:
https://sapporta.com/docs/reference/frontend/app-shell/application-routes-and-navigation.md
Give every screen a browser tab title so history entries and open tabs stay
readable. Pass `title` to `AppPage` or `PageHeader` — that sets the tab title
too. If the screen renders its own chrome, call `usePageTitle("...")` from
`@sapporta/frontend/shell`.
Read [../workflow-shell.md](../workflow-shell.md) before changing
`appNavigation`, the protected home page, `showFrameworkNavigation`, or an
Advanced/Admin page. Add a protected page to `appNavigation` only when users
routinely start or resume work there. Keep create, edit, detail, and
supporting-resource pages contextual unless they are genuine starting points;
put specialist reports, tools, and maintenance pages under Advanced. Put public
routes in `appPublicRoutes`, and an anonymous landing page at `/` in
`appPublicHomeRoute`, only when their data is intentionally public. For
connected list, detail, create, and edit routes, adapt
[form-template/route-wiring.tsx.example](form-template/route-wiring.tsx.example).
For an app-owned resource workflow, connect these route roles deliberately:
```text
/<resources> list or master-detail workspace
/<resources>/new create form
/<resources>/:id full detail
/<resources>/:id/edit edit form
```
- Open the create route from the list or contextual parent action.
- Open edit on row activation when the workspace already shows a read preview;
otherwise open full detail.
- Open edit from the detail action.
- After create or edit, invalidate affected caches before opening detail.
- Return from cancel to the prior detail or list without saving.
- Parse and validate route or search identity before using it as a form default.
- Add only pages where users routinely begin or resume work to `appNavigation`.
Generated framework routes provide `/tables/:tableName` and
`/tables/:tableName/new`. Do not assume generated detail or edit routes exist.
Use app-owned routes until the installed Sapporta version exposes the needed
surface.
## Auth Boundaries
Let the existing app boot load the session and
`/api/auth-context` before rendering screens that need scoped tables or report
routes. Non-owner workspace users should not see owner-only table, report, or
metadata links.
Client code does not enforce row ownership. Do not add hidden `workspace_id`,
`workspaceId`, `scoped_to_user_id`, or `scopedToUserId` inputs, and do not rely
on `fixedFilters` or URL params as authorization. Use built-in table routes or
typed custom endpoints whose server handlers resolve auth and apply
`scopedRows()` or `rowSecurity`.
Forms must omit system-managed scope fields and columns marked
`apiWritable: false`. This metadata shapes generated clients and forms. The
server applies the authoritative write policy.
## Pickers And Primitives
- For ordinary searchable selection from a Sapporta table or foreign key, use
`LookupPicker` with `useTableLookup` from `@sapporta/frontend/lookup`. It owns
scoped remote search, selected-value loading, caching, clearing, disabled
state, and shared styling. Do not compose Base UI or add an app-local picker
for this standard case.
- Compose the Base UI `Combobox` primitives only when the interaction is not
covered by `LookupPicker`, including all static choices. Do not use native
HTML `<select>` or the BaseUI `Select` component -> read
[pickers.md](pickers.md).
- Table/grid pages should preserve built-in search, filters, sort, pagination,
CSV export, lookup labels, URL state, loading states, and error states unless
the user explicitly asks for less -> read
[grids.md](grids.md).
- Use ColumnPreset before authoring a custom GridCore column. Keep application
validation, persistence, conflicts, and workflow transactions in save
handlers, services, and endpoints rather than cell renderers.
## Backend APIs
Keep typed API clients in `packages/frontend/src/api.ts`. Prefer a typed client
from the shared contract over hand-written `fetch("/api/foo")`.
For generated table reads, first use the public fetch functions, selection/page
serializers, TanStack Query option builders, and cache keys documented in the
table-query-options reference. Preserve `QueryParamRecord` through the client
boundary so repeated same-key filters are not collapsed. Keep app-owned endpoint
clients in `packages/frontend/src/api.ts`.
Typed clients still call server code; they do not make a route auth-safe by
themselves. When adding a frontend call that mutates or reveals scoped data,
confirm the matching backend handler chooses the route's ability/data authority
and uses scoped row helpers.
Preserve server error details where the UI needs them. Use the project's
existing `ApiError` handling pattern instead of inventing a new error envelope.
references/app-building/guide.md
# Application-Building Workflow
Use this mode when the user wants to build or change how a Sapporta app behaves.
Establish the project context, complete the common
[product-model and coherent-slice workflow](product-slice.md), then read the
narrow reference for the specific thing being changed.
## Choose The Execution Model
Run a focused vertical slice in one execution context.
Use staged orchestration for a program of work: multiple workstreams, material
discovery, cross-cutting dependencies, or delivery risk that one context cannot
reliably preserve the product brief, decision log, and acceptance evidence. A
cross-stack slice remains one workstream when its outcome and ownership are
coherent.
The root owns scope, product decisions, dependency sequencing, shared contracts,
integration, and release acceptance. Delegate bounded work packages with
explicit inputs, ownership, dependencies, and exit criteria. Use the lightest
delivery model and rebaseline when discovery changes scope. Infer the model from
the request and repository; do not ask the user to choose the process.
## Inspect The Existing Application
When the requested application does not exist yet, start with
[project/create.md](project/create.md). Run its scaffold workflow before using
the existing-project inspection steps below.
Inspect the existing app before mapping the model to implementation:
```bash
rg --files packages/api/schema
```
Also inspect relevant contracts, routes, migrations, frontend navigation, and
access helpers. When the app server is already running, inspect mounted surfaces
with `pnpm exec sapporta tables list` and
`pnpm exec sapporta endpoints list`.
To discover how to define application tables, continue with
[tables/create.md](tables/create.md). It points to local conventions and the
canonical starter pattern.
If `packages/api/schema/` has no domain tables and its definitions only support
project authentication, take the fresh-app branch in
[tables/create.md](tables/create.md) before inspecting framework internals or
test fixtures. That reference owns the starter-schema workflow.
Prefer the project's existing style. Do not create custom code for behavior
already covered by built-in table APIs or an existing domain endpoint unless the
product workflow actually needs custom behavior.
## Complete The Common Product Slice
After inspecting the project, read [product-slice.md](product-slice.md). Use
its full new-application pass or proportional model-delta/interaction pass
before choosing tables, endpoints, reports, forms, or Grid implementation.
The common workflow owns application grammar, coherent slice boundaries, and
user-outcome acceptance. The narrow references below own Sapporta-specific
implementation details.
## Where To Work
- Tables -> `packages/api/schema/`
- Reports -> shared contract, backend route, frontend screen, navigation, tests
- Backend workflows/endpoints -> `packages/shared/src/contracts/` plus
`packages/api/app/`
- Larger backend workflows -> `packages/api/modules/<domain>/`
- Custom pages, forms, table/grid workflows -> `packages/frontend/src/`
Docs:
- Develop with a coding agent: https://sapporta.com/docs/guides/discovery/develop-with-a-coding-agent.md
- Project files: https://sapporta.com/docs/reference/project/project-files.md
- Tables, columns, and schema metadata: https://sapporta.com/docs/guides/model-data/tables-columns-and-schema-metadata.md
- Custom endpoints: https://sapporta.com/docs/guides/application-code/custom-api-endpoints.md
- Route-based reports: https://sapporta.com/docs/guides/reports/route-based-reports.md
- Frontend screens: https://sapporta.com/docs/guides/application-code/custom-frontend-routes-and-screens.md
## Built-In APIs Or Custom Code
Use built-in table APIs for ordinary list, get, create, update, delete, lookup,
count, and export behavior. Build custom app code for reports, multi-table
workflows, custom validation, file uploads, custom response shapes, and business
transactions.
Custom endpoints should use shared ts-rest contracts. The same contract drives
request validation, typed handler inputs, OpenAPI emission,
`sapporta endpoints show`, and typed frontend clients.
For larger features, keep route files thin. Put workflow orchestration in
services and database reads/writes in module `db/` stores under
`packages/api/modules/<domain>/`.
## Auth And Row Scope
Resolve auth at the route edge with the narrowest ability/data-authority helper
for the workflow. Then use `scopedRows()` or `auth.rowSecurity.forTable(table)`.
Do not manually stamp or filter `workspace_id`, `workspaceId`,
`scoped_to_user_id`, or `scopedToUserId`. Do not mutate scoped rows by primary
key alone, insert `request.body` directly into scoped tables, or fetch broadly
and filter row ownership in JavaScript.
Docs:
- Authentication and abilities: https://sapporta.com/docs/guides/security/authentication-and-abilities.md
- Auth and row security: https://sapporta.com/docs/reference/server/auth-and-row-security.md
## Validation
Use the smallest loop that proves the change. Prefix every `db:*` command with
the `SAPPORTA_DATA_DIR` value from the project's `.env.development` (`data` in
a new project). The `db:*` scripts read that setting only from the environment
and stop when it is missing; never pass a different directory to make them run.
```bash
SAPPORTA_DATA_DIR=data pnpm --filter ./packages/api db:generate --name <change_name>
# Review the generated SQL before continuing.
SAPPORTA_DATA_DIR=data pnpm --filter ./packages/api db:migrate
SAPPORTA_DATA_DIR=data pnpm --filter ./packages/api db:check
pnpm typecheck
pnpm dev
pnpm exec sapporta endpoints show "METHOD /api/path"
```
Run `pnpm typecheck` after every frontend change. A green `vite build` is not a
type check. In an older project whose root `package.json` has no `typecheck`
script, run `pnpm --filter ./packages/frontend exec tsc --noEmit` instead, and
add the script:
https://sapporta.com/docs/reference/project/project-files.md
For native module failures, binding errors, or dev-server startup failures, read
troubleshooting before trying broad dependency changes.
## Read The Narrow Reference
- New Sapporta project, generated workspace, or first connected application
surface -> read [project/create.md](project/create.md)
- Main sidebar, protected home page, Advanced/Admin page, framework navigation,
or placement of a feature that changes the normal user path -> read
[workflow-shell.md](workflow-shell.md)
- Tables, columns, relations, indexes, search config, schema migration, custom
table validation, semantic values -> read [tables/create.md](tables/create.md)
- Route-based reports, summaries, ledgers, route/result validation -> read
[reports/create.md](reports/create.md)
- Days, calendars, time zones, day-bucketed reports, date-range filters, or
relative periods -> read [days-and-time-zones.md](days-and-time-zones.md)
- Demo, sample, or fixture data, seed scripts, `pnpm seed`, or any command-line
script that reads or writes rows -> read [seed-demo-data.md](seed-demo-data.md)
- Cell links, drill-through, cross-report navigation -> read
[reports/linking.md](reports/linking.md)
- Domain endpoints, `TsRestApi`, shared contracts, handlers, uploads,
transactions, OpenAPI registration -> read [backend/endpoints.md](backend/endpoints.md)
- Custom create or edit forms, domain mutation pages, form dialogs or drawers,
and record-entry workflows -> read [frontend/forms.md](frontend/forms.md)
first, then follow its conditional picker or Grid routes
- Typing a table row in frontend code, decoding a generated table response, or a
frontend `$inferSelect` import -> read
[frontend/row-projections.md](frontend/row-projections.md)
- Custom React routes, dashboards, table/grid views, master-detail screens,
side panels, row selection, custom cells, typed API clients -> read
[frontend/views.md](frontend/views.md), then [frontend/grids.md](frontend/grids.md)
- Domain services, module organization, testable TypeScript workflow code ->
read [backend/domain-code.md](backend/domain-code.md)
- Expected non-2xx failures raised below a route adapter -> read
[backend/typed-errors.md](backend/typed-errors.md) before implementing the handler
- Native module or `better-sqlite3` failures -> read
[../operations/troubleshooting.md](../operations/troubleshooting.md)
references/app-building/product-slice.md
# Product Model And Coherent Slice
Use this workflow for every new Sapporta application and every change to
application behavior. Complete it after establishing the project context and
before reading implementation-specific table, endpoint, report, form, or Grid
references.
The gate is common; its depth is proportional to the request. Do not turn a
small, well-understood adjustment into a full product-discovery exercise.
## Contents
- [Calibrate The Design Pass](#calibrate-the-design-pass)
- [Model The Outcome](#model-the-outcome)
- [Define The Coherent Slice](#define-the-coherent-slice)
- [Use The Default Application Grammar](#use-the-default-application-grammar)
- [Design The Workflow Shell](#design-the-workflow-shell)
- [Map The Slice To Sapporta](#map-the-slice-to-sapporta)
- [Accept The User Outcome](#accept-the-user-outcome)
## Calibrate The Design Pass
- **New application:** define the actors, outcomes, operational questions, MVP
boundary, primary and supporting resources, events, relationships, ownership,
states, lifecycles, workflows, invariants, authorization, expected failures,
and slice sequence.
- **New feature:** define the delta to the established model: the affected
actor and outcome, reused or new resources, relationship or lifecycle
changes, invariants, authorization, expected failures, success destination,
and compatibility with existing workflows.
- **Fine-grained behavior or UI adjustment:** define the interaction contract:
source state, triggering event, resulting state or navigation, data
dependency, loading and failure behavior, keyboard and pointer behavior,
authorization boundary, and observable acceptance checks.
Infer established decisions from the inspected application. Ask only about
unresolved choices that materially affect scope, authorization, lifecycle, data
integrity, or the user-visible interaction.
## Model The Outcome
Start with the actor's intended result, not a requested Sapporta primitive.
Use these terms:
- **Actor:** a person, role, or external system that uses or affects the app.
- **Outcome:** a result an actor wants, such as assigning work, completing an
order, or finding overdue accounts.
- **Domain resource:** a durable business object with identity and a lifecycle.
- **Primary resource:** a resource from which users regularly begin work. It
normally needs list, detail, navigation, and change surfaces.
- **Supporting resource:** a resource used within another resource or workflow.
Keep it contextual unless users need to work with it independently.
- **Event:** a durable record of something that happened. Events are normally
inspected rather than edited.
- **Workflow:** a user goal expressed as a named action. It may read or change
several resources.
- **Invariant:** a validation, authorization, ownership, state, or transaction
rule that must remain true throughout the workflow.
- **Surface:** a coherent UI context such as a page, Grid, detail panel, form,
dialog, drawer, or composite workspace.
Present the inferred model or model delta and consequential assumptions before
committing to an implementation shape. Reuse the application's domain language,
schema, contracts, routes, access model, and interaction conventions. Avoid
speculative abstractions.
## Define The Coherent Slice
Define one acceptance statement in actor language, including the entry context,
required information, named action or interaction, success result, expected
failures, and next useful destination.
A coherent vertical slice delivers that outcome across the boundaries it
touches:
- data and relationships;
- backend behavior and transactions;
- UI and navigation;
- authorization and row scope;
- expected failure handling; and
- focused tests or runtime evidence.
Inspect every boundary, but change only the ones required by the outcome. A
frontend-only interaction does not require a new endpoint when existing scoped
reads already provide the data. A metadata-only table change does not require a
custom React screen when generated surfaces satisfy the workflow.
For a program of work, sequence several independently acceptable slices. Do not
organize delivery as disconnected schema, backend, and frontend phases that
leave the user outcome incomplete.
## Use The Default Application Grammar
Design surfaces around workflows:
- **List:** use a Sapporta Grid for substantial tabular collections. Preserve
filtering, search, sorting, pagination, lookup labels, URL state, record
navigation, loading, and errors unless the requirement changes them.
- **Detail:** combine identity, state, ownership, relationships, history, and
available actions. Constrain related-record Grids by their relationship.
- **Mutation:** expose deliberate named actions. Default to a form: a page for
substantial work, a dialog or drawer for short contextual work, and quick-add
only for frequent, low-risk, few-field creation. Keep browsing read-only until
an action begins.
- **Navigation:** promote places where users begin work. Keep supporting
resources and record-specific routes contextual.
- **Reports:** define the operational question, inclusion rule, parameters, and
drill-down destination before choosing the result shape.
Use active-row context for master-detail browsing and independent row selection
for bulk operations. Decide the detail value and row-activation destination
separately. Row movement may update an information panel or related Grid;
activation may open a route, run a named action, or move focus. Distinguish
active-row movement from passive viewport scrolling, which does not itself
select a new record unless the product explicitly defines that behavior.
Treat Manage Data as an administrative surface for direct inspection and
editing, not as the default application workflow. Authorize it separately when
its direct access is more powerful than normal user actions.
## Design The Workflow Shell
For every new application, read [workflow-shell.md](workflow-shell.md) before
accepting the first connected page. Read it again when a later feature changes
where users start or resume routine work, what comes next, or where they open a
result, report, tool, or setting.
## Map The Slice To Sapporta
Translate the accepted model in this order:
```text
intent -> scope -> model or model delta -> workflows and invariants
-> surfaces and navigation -> mutation UX -> access and failures
-> Sapporta tables, operations, endpoints, reports, and views
```
Prefer built-in table APIs and generated surfaces for behavior they already
cover. Add custom contracts, endpoints, services, reports, forms, or Grid
compositions only when the accepted workflow requires them. Then read the
matching narrow references from the application-building guide.
## Accept The User Outcome
Review the completed slice through the actor's path. Verify relationships,
deliberate mutation or interaction, empty and failure states, authorization,
cache or Grid refresh effects, success navigation, and the next useful action.
Refine the model when implementation discovery changes a consequential
assumption.
references/app-building/project/create.md
# Create A Sapporta Project
Use this reference when the requested Sapporta application does not exist.
After scaffolding, continue with [../guide.md](../guide.md) inside the generated
project.
Public references:
- Create a project: https://sapporta.com/docs/getting-started/create-a-project.md
- Tour the generated project: https://sapporta.com/docs/getting-started/tour-the-generated-project.md
- Tables, columns, and schema metadata: https://sapporta.com/docs/guides/model-data/tables-columns-and-schema-metadata.md
- Project files: https://sapporta.com/docs/reference/project/project-files.md
## Contents
- [Resolve The Target](#resolve-the-target)
- [Confirm pnpm 11 Or Later](#confirm-pnpm-11-or-later)
- [Run The Documented Scaffold Command](#run-the-documented-scaffold-command)
- [Inspect The Generated Project](#inspect-the-generated-project)
- [Continue Through The Common Workflow](#continue-through-the-common-workflow)
## Resolve The Target
Resolve an explicit parent directory and project name. The parent must exist and
the target path must not exist. Do not pre-create the target directory; the
Sapporta initializer rejects an existing path.
If the current directory or an ancestor already contains `sapporta.json`, treat
it as an existing application unless the user explicitly requested a separate
project. Do not scaffold a nested application as an implementation shortcut.
The initializer performs consequential setup in one operation. It creates a
pnpm workspace, resolves and installs dependencies, verifies native SQLite
bindings, generates and applies the initial auth migration, initializes Git,
and creates the initial commit. Run it only when the user requested project
creation, and use the environment's network and filesystem approval mechanism.
## Confirm pnpm 11 Or Later
Run `pnpm --version` before scaffolding. On anything older than 11, tell the
user to upgrade with `corepack use pnpm@11` and stop; do not work around the
check. `sapporta init` refuses to run otherwise:
https://sapporta.com/docs/getting-started/create-a-project.md
## Run The Documented Scaffold Command
Run the init command from the create-project guide
(https://sapporta.com/docs/getting-started/create-a-project.md), which always
carries the current Sapporta version.
Run it from the intended parent directory. Do not run a separate `pnpm install`,
auth migration, `git init`, or initial commit after a successful scaffold; the
initializer already owns those steps.
The install may request approval to build `better-sqlite3` and `esbuild`.
Approve both when the user authorized a working local project. If the command
fails, inspect the reported setup step. Retry the complete init command after
fixing the cause because the target is published only after staged setup
succeeds.
## Read The Project's Ports
Every project gets its own development ports, so no port number is shared
between projects. Take them from the table `sapporta init` prints when it
finishes, from the same table `pnpm dev` prints when it starts, or from
`SAPPORTA_API_PORT` and `SAPPORTA_FRONTEND_PORT` in `.env.development`. Give the
user the App URL to open, and use the API URL for direct HTTP calls.
## Inspect The Generated Project
Change into the generated root and read these files before editing:
```text
README.md
AGENTS.md
CODING-PRINCIPLES.md
VISUAL-DESIGN-GUIDELINES.md
sapporta.json
```
Inspect the extension points:
```text
packages/api/schema/ Drizzle tables and Sapporta metadata
packages/api/migrations/ Reviewed generated SQL
packages/api/app.ts Mounted app-owned Hono routes
packages/api/authz/ Abilities and request authority
packages/shared/src/ Browser-safe contracts and wire types
packages/frontend/src/App.tsx App navigation and routes
```
The generated app starts with authentication tables and a placeholder home page
at `/`. Use the fresh-app branch in [../tables/create.md](../tables/create.md)
for the first domain tables after completing the common product workflow. Treat
the generated table list and home page as scaffolding. Before accepting the first connected
product page, read [../workflow-shell.md](../workflow-shell.md).
## Continue Through The Common Workflow
Project creation ends after the generated workspace and its extension points
are available and inspected. Continue with [../guide.md](../guide.md), then read
[../product-slice.md](../product-slice.md) and use its full new-application
design pass before choosing the first implementation branch.
Use [../tables/create.md](../tables/create.md) for the first domain tables.
Follow the guide's conditional routes for endpoints, reports, forms, views, and
Grids. Those references own the implementation and validation loops for the
accepted first slice.
references/app-building/reports/api-reference.md
# Route-Based Report Reference
Use the canonical documentation for report API and type details.
Use the canonical documentation:
- Report datasets: https://sapporta.com/docs/guides/reports/report-datasets-and-formatting.md
- Grid result shape: https://sapporta.com/docs/reference/reports/grid-dataset.md
- Report routes: https://sapporta.com/docs/reference/reports/report-routes-and-registration.md
- Scoped report data: https://sapporta.com/docs/reference/reports/scoped-report-helpers.md
Agent reminder: keep report mappers pure, include hidden IDs for links
(`visuallyHidden: true`), declare `links`/`rowLinks` on dataset columns and
levels for drill-downs (see [linking.md](linking.md)), and validate route
output with `gridDatasetSchema`.
references/app-building/reports/create.md
# Report Creation
Start with the actor's operational question. Define the report's inclusion and
exclusion rules, row or summary grain, parameters, authorization boundary, and
drill-down destination before choosing its route or result shape. Reuse the
accepted product model and the application's existing report language.
Then build the report as an app-owned API route that returns grid-renderable
data, plus a React screen that displays it. Choose the URL, parameters,
permission check, query, screen route, navigation entry, and validation loop for
the report.
Use public docs for exact `GridDataset` shape, report contracts, scoped report
data, frontend renderers, date range helpers, and examples:
- Reports guide: https://sapporta.com/docs/guides/reports/route-based-reports.md
- Report datasets: https://sapporta.com/docs/guides/reports/report-datasets-and-formatting.md
- Report routes: https://sapporta.com/docs/reference/reports/report-routes-and-registration.md
- Grid result shape: https://sapporta.com/docs/reference/reports/grid-dataset.md
- Scoped report data: https://sapporta.com/docs/reference/reports/scoped-report-helpers.md
- Day ranges and day buckets: https://sapporta.com/docs/guides/reports/group-and-filter-by-day.md
- Row-scoped data helpers: https://sapporta.com/docs/reference/server/row-scoped-data-helpers.md
- Agent access: https://sapporta.com/docs/guides/security/agent-access-and-scoped-tokens.md
## Contents
- [Workflow](#workflow)
- [Report Slice Organization](#report-slice-organization)
- [Auth And Data Scope](#auth-and-data-scope)
- [Mapper Rules](#mapper-rules)
- [Frontend Rules](#frontend-rules)
- [Record-Level Linking Completion Gate](#record-level-linking-completion-gate)
- [Validation](#validation)
- [Example Reference Files](#example-reference-files)
- [References](#references)
## Workflow
Use this shape:
1. Define a shared route contract in `packages/shared/src/contracts/`.
2. Implement a thin `TsRestApi` handler under `packages/api/app/`.
3. Mount the report sub-app with `route()` and merge its contracts into OpenAPI
with `extend()`.
4. Put query logic in a domain store/service when it is more than trivial.
5. Map rows to `GridDataset` in a pure function.
6. Build a React screen under `packages/frontend/src/`.
7. Add the screen to React Router and navigation.
8. For a record-level report, complete the mandatory linking gate below before
considering the slice done.
9. Add route tests and mapper tests when hierarchy, rollups, links, or totals
are non-trivial.
Do not create report files in `packages/api/reports/`, use `report({...})`, or
run `sapporta reports`. Route-based reports are normal app routes and should be
discoverable through OpenAPI.
## Report Slice Organization
Treat each report as a vertical slice by default. Follow the app's existing
route, navigation, and file naming conventions, but keep report-specific
decisions with the report instead of hiding them behind broad shared dispatch.
Backend:
- Put each report in its own backend module, commonly under
`packages/api/app/reports/` when the app groups reports there.
- The module owns its `TsRestApi`, one `api.register(...)`, contract key, auth
subject, request params, read/query orchestration, local source row types, and
pure row-to-`GridDataset` mapper.
- For multiple reports, keep the report route entrypoint as a thin aggregator
that imports report modules, mounts their Hono handlers with `route()`, and
merges their registered contracts with `extend()`.
- Share broad report infrastructure only: auth/scope helpers, row helpers,
generic grid columns, date/parameter helpers, footer/flat-result/sum helpers,
and result-shape utilities.
- Put reusable query or domain logic in a domain store/service or a narrowly
named report-family helper after two or more reports already use the same
concept. Do not centralize unrelated report SQL, mappers, or string-dispatch
just because the files look similar.
Frontend:
- Put each report screen in its own component near the app's existing report
routes, commonly under `packages/frontend/src/reports/`.
- The screen owns its component state, URL/search-param handling, toolbar
controls, loading/error/result rendering, and the typed API call for that
report.
- If the app uses a report registry for navigation/routes, keep it metadata
oriented: report id, label, component, route path, and other navigation data.
- Build routes and navigation from the app's existing routing pattern. Preserve
existing report URLs unless the user explicitly asks to change them.
- Avoid generic `reportId` switches and central fetch functions that erase the
typed report contract. A shared request helper is fine when it accepts a typed
endpoint or caller and does not become the place where report behavior is
dispatched by string id.
Shared frontend helpers should be mechanics only: date defaults, date inputs,
run/loading/error handling, and common grid/error body rendering. Report
selection controls are a product decision; add them when they make the workflow
clear, not as a substitute for route-level navigation.
## Days And Periods
Any report that names a day — a baseline, a bucket, a range — resolves it in the
active workspace's zone. Read
[../days-and-time-zones.md](../days-and-time-zones.md) before writing one.
- Inject the application clock as `Temporal.Instant`, never `PlainDate`. Read
the zone in the handler with `workspaceTimeZone(auth)`.
- Pass the zone into the mapper as an argument alongside `asOf`. A mapper that
reads an ambient zone produces two datasets from the same rows.
- Bound with `resolveDateRangeQueryBounds(name, params, zone, now)`. Use
`period.days` against a `date` column and the half-open `period.instants`
(`>= from`, `< until`) against a `timestamp` column.
- Bucket with `to_tz_date(column, :zone)` inside the scoped base relation, after
the range is bounded.
- Render `ReportTimeZoneNote` in the toolbar when the numbers depend on the zone.
## Auth And Data Scope
Resolve auth and request input at the route edge. Use `scopedRows()` or
`auth.rowSecurity.forTable(table)` for visible base rows before joining,
aggregating, or mapping results. For raw SQL, make visible base tables explicit
with CTEs or guarded row sets before composing the report query.
Read the row-scoped data-helper reference before choosing the query primitive.
Use its bounded reads, pages, scans, and counts when they fit. For joins and
aggregates, build scoped Drizzle predicates with one guard per participating
table. Do not route an advanced report to raw SQL merely because a generated
table query is too small for it.
Do not accept `workspace_id`, `workspaceId`, `scoped_to_user_id`, or
`scopedToUserId` from the client as report filters. The route's auth context
owns data authority.
## Mapper Rules
- Keep the row-to-`GridDataset` mapper pure and testable without a database.
- Include hidden IDs in columns when links need them (`visuallyHidden: true`).
- Use rollups and footers intentionally; tests should assert totals and
hierarchy when they matter.
- Declare drill-down links in the mapper by default: `links` on
`GridDataset` columns and `rowLinks` on levels, using the shared
`NavLink` type. Use frontend resolver functions only when a link needs
runtime screen state; read [linking.md](linking.md).
- Keep summary cards out of `GridDataset`. The current schema contains levels,
nodes, and optional footer rows. Pass `ReportSummaryStats` a separate
`ReportStat[]` value in the screen or declare a custom response envelope when
summary values must cross the API boundary.
- For `GridDatasetColumn` width hints, use the canonical [Column sizing](https://sapporta.com/docs/reference/column-sizing.md) reference.
## Frontend Rules
Render normal report results with the report components. Do not copy report-grid
internals. When the screen owns its own row shape, loading behavior, hierarchy,
editing rules, side panels, or toolbar behavior, read the custom grid docs:
- TGrid usage: https://sapporta.com/docs/reference/frontend/tgrid.md
- GridCore guide: https://sapporta.com/grid/reference/grid-core.md
## Record-Level Linking Completion Gate
Complete [Report Linking](linking.md) before finishing any report that exposes
durable records. A report is record-level when a displayed value represents a
record a user would reasonably inspect, edit, or use as a starting point for
related work. Summary-only reports need links only when drill-down is useful.
The report should preserve the identity needed for its intended drill-downs and
make those paths available in the UI — declaratively in the `GridDataset`
mapper by default, with frontend resolvers for runtime-state cases. Verify that
links work for the report's relevant row types and that their targets enforce
authorization.
## Validation
Use the smallest loop that proves the report:
```bash
pnpm exec sapporta endpoints show "GET /api/reports/<name>"
pnpm exec sapporta api get /api/reports/<name> --query '{"...":"..."}'
```
For protected runtime checks, follow the agent-access guide and use the
project's private CLI environment. Parse successful responses with
`gridDatasetSchema` in route tests and unit-test pure mappers for hierarchy,
rollups, hidden IDs, and totals.
## Example Reference Files
When creating a report from scratch, adapt these reference files instead of
expecting them to exist in every generated project:
- [Sample report contract](sample-contract.ts.example)
- [Sample report API route](sample-api.ts.example)
- [Sample report React screen](sample-screen.tsx.example)
- [Report parameter helper](params.ts.example)
- [Report dataset loading helper](use-dataset.ts.example)
Copy only the pieces the project needs. The examples are not scaffolded into new
Sapporta projects by default.
## References
- [Report Linking](linking.md)
- [Canonical report docs](api-reference.md)
- [Canonical report examples](examples.md)
references/app-building/reports/examples.md
# Route-Based Report Examples
Use the canonical documentation for long worked report examples.
Use the canonical documentation:
- Reports guide: https://sapporta.com/docs/guides/reports/route-based-reports.md
- Report routes: https://sapporta.com/docs/reference/reports/report-routes-and-registration.md
- Report datasets: https://sapporta.com/docs/guides/reports/report-datasets-and-formatting.md
Agent reminder: align report files with the project's existing module layout,
keep route adapters thin, and add mapper tests for hierarchy, rollups, footers,
and hidden IDs.
references/app-building/reports/linking.md
# Report Linking
Reports are more useful when the user can jump from a row, cell, or total into
the underlying data. Sapporta renders links from two sources, in this order of
preference:
1. **Declarative links** carried by the data: `links` on a `GridDataset`
column and `rowLinks` on a `GridDataset` level, using the shared
`NavLink` type from `@sapporta/shared/contracts`. Declare these in the
backend mapper that builds the `GridDataset`. This is the default choice —
the frontend resolves and renders them with no screen code.
2. **App resolver functions** passed to `ReportGridDataset` via the `links`
prop (`ReportCellLinkResolvers`): per-level `cell` resolvers and a
per-level `row` resolver. Use these only when a link needs frontend
runtime state — the current date range or filter values from
`linkContext.input`, conditional destinations, or app-owned detail routes
with non-trivial construction. A resolver declared for a column takes
full control of that column: declarative links on it are ignored.
Use the current public types: `NavLink` (`@sapporta/shared/contracts`) for
declarative links; `ReportCellLink`, `ReportCellLinkContext`,
`ReportRowLinkContext`, and `ReportCellLinkResolvers`
(`@sapporta/frontend/report`) for resolvers. Do not use older removed
grid-link type families. Resolver-returned links are
`{ label, href, icon?, target? }` — set `icon` when the default (`external`
for hrefs that leave the app, `drill-into` otherwise) doesn't fit, e.g. a
resolver linking to a report route should say `icon: "report"`.
## Declarative `NavLink`
```ts
// On a GridDataset column: cells of this column link out.
{ id: "customer", label: "Customer", kind: "text",
links: [
{ kind: "table", table: "invoices",
bind: { customer_id: "customer_id" }, label: "Open invoices" },
] }
// On a GridDataset level: row-level related data, shown in the row's
// right-click context menu.
rowLinks: [
{ kind: "report", report: "customer-statement",
bind: { customer_id: "customer_id" }, label: "Customer statement" },
]
```
- `kind: "table"` — each `bind` entry becomes an equality filter on the
destination table: `bind: { customer_id: "customer_id" }` means "filter the
destination's `customer_id` column by this row's `customer_id` value".
Composite binds produce multiple filters.
- `kind: "report"` — `report` is the report route (`"aging"` resolves to
`/reports/aging`; an absolute path like `"/finance/aging"` is kept as-is);
each `bind` entry becomes a query parameter.
- `kind: "url"` — arbitrary destination; `{column}` placeholders in `href`
are substituted with URL-encoded row values, `bind` entries append query
parameters. External (`scheme://`) URLs default to a new tab.
- `label`, `icon` (`drill-up` | `drill-into` | `report` | `external`), and
`target` are optional presentation hints.
Resolution rules the mapper can rely on:
- `bind` sources read from the node's `columns` values, including
`visuallyHidden: true` helper ID columns. Include the identifiers the
drill-down needs in `node.columns` and hide them.
- Bind sources and url `{column}` placeholders must name columns declared on
the link's level. Unknown names fail loudly when the grid binds the
dataset (the counterpart of the boot-time check on table-declared links),
so a typo can't silently produce a link that never appears.
- A link only resolves when every bound source value is present — rows with
NULL sources simply don't offer the link. No guard code needed.
- Synthetic rows (`kind: "opening" | "closing" | "subtotal"`) and footer rows
never resolve declarative links.
- The first resolvable link on a column is the cell's primary link (rendered
as an anchor, opened with Enter). All resolvable cell links plus the
level's row links appear in the row's right-click context menu.
## Agent Workflow
- Treat this as a required completion task for every record-level report; see
[the report-creation linking gate](create.md#record-level-linking-completion-gate).
- Declare links in the backend `GridDataset` mapper by default; add frontend
resolvers only for the runtime-state cases above.
- Include hidden identifiers in backend `node.columns` when navigation needs
them, with `visuallyHidden: true`.
- Pass current query state through `linkContext` when a link needs date range
or filter values — that is a resolver case.
- Exercise non-trivial links in the browser.
When this is one workstream within a larger feature, strongly prefer a bounded
subagent or separate coding agent thread/task for the linking work. Give it the
stable report route, backend row shape, report screen, intended destinations,
and the completion-gate acceptance criteria. The task should return the
hidden-ID, link-declaration/resolver, and browser-validation changes for the
owning feature task to integrate; do not let it independently broaden the
report's product scope.
## Choose Domain-Aware Destinations
Pick destinations from the user's workflow, not from what is mechanically
linkable. For each report, ask: when a user sees this number or name, what do
they do next? Link exactly that.
- FK-like cell (customer, account, order) -> the filtered table view of that
entity's records, or a verified app-owned detail route
- Aggregate cell (total, count, balance) -> the filtered table or detail
report listing the rows behind the number
- Master row -> its children (`drill-into`), its entity statement or
activity report (`kind: "report"`)
- Cross-report -> another report route carrying the identifying parameters
- External systems (payment processor dashboard, tracking page) ->
`kind: "url"` with `target: "_blank"`
Do not attach links to every column. One primary link per key column plus a
small set of row links keeps the grid scannable; the context menu carries the
long tail.
The generated single-row HTTP endpoint does not imply a generated frontend
detail route. Inspect the installed app's routes before emitting a record link.
Every link target must enforce its own authorization. Hidden IDs and URL
filters are not authorization.
## Related
- [report-creation](create.md) - include hidden IDs while
shaping `GridDataset`.
- [table linking](../tables/create.md#table-and-row-linking) - the same
`NavLink` vocabulary on table metadata: FK drill-up and child drill-into
links are derived automatically; declare additional `links` / `rowLinks`
for domain workflows.
references/app-building/reports/params.ts.example
// Optional app-local helper. Copy into packages/frontend/src/reports/report-params.ts when a report screen needs field-level parameter errors.
export type ReportParamError<TParams> = {
field?: keyof TParams;
message: string;
};
export function fieldError<TParams>(
issues: readonly ReportParamError<TParams>[],
field: keyof TParams,
): string | null {
return issues.find((issue) => issue.field === field)?.message ?? null;
}
references/app-building/reports/sample-api.ts.example
// Example only. Copy into packages/api/app/<report-name>.ts and adapt the contract import, auth subject, query, and mapper.
import {
DateRangeParseError,
resolveDateRangeQueryBounds,
} from "@sapporta/shared/daterange";
import { Temporal, type TimeZone } from "@sapporta/shared/temporal";
import type { GridDataset } from "@sapporta/shared/grid-dataset";
import {
forbidUnless,
TsRestApi,
workspaceTimeZone,
type SapportaEnv,
} from "@sapporta/server";
import {
sampleReportContract,
type SampleReportQuery,
type SampleReportSegment,
} from "your-app-shared";
const api = new TsRestApi<SapportaEnv>();
api.register(
"sampleReport",
sampleReportContract.sampleReport,
({ c, request }) => {
const auth = c.get("auth");
forbidUnless(c, auth.ability.can("read", "sample_report"));
const input = readSampleReportInput(request.query, workspaceTimeZone(auth));
if (!input.ok) {
return {
status: 400,
body: {
error: input.error,
code: "invalid_report_parameters",
},
};
}
return {
status: 200,
body: sampleReportDataset(input.value),
};
},
);
export default api;
type SampleReportInput = {
segment: SampleReportSegment;
periodFrom: string | null;
periodTo: string | null;
};
type SampleReportInputResult =
| { ok: true; value: SampleReportInput }
| { ok: false; error: string };
type SampleReportRow = {
rowKey: string;
channel: string;
segment: Exclude<SampleReportSegment, "all">;
revenue: number;
cost: number;
notes: string;
};
const SAMPLE_ROWS: readonly SampleReportRow[] = [
{
rowKey: "self-serve",
channel: "Self-serve",
segment: "new",
revenue: 42_500,
cost: 18_200,
notes: "Website and product-led signups",
},
{
rowKey: "sales-led",
channel: "Sales-led",
segment: "new",
revenue: 68_000,
cost: 31_500,
notes: "Qualified opportunities closed by the team",
},
{
rowKey: "account-expansion",
channel: "Account expansion",
segment: "expansion",
revenue: 37_800,
cost: 9_700,
notes: "Plan upgrades and additional seats",
},
];
function readSampleReportInput(
query: SampleReportQuery,
zone: TimeZone,
): SampleReportInputResult {
try {
const period = resolveDateRangeQueryBounds(
"period",
query,
zone,
Temporal.Now.instant(),
);
// `period.days` is inclusive and compares against a `date` column, which is
// what the hidden period columns below carry. A `timestamp` column takes
// the half-open `period.instants` instead: `>= from` and `< until`.
return {
ok: true,
value: {
segment: query.segment ?? "all",
periodFrom: period.days.from,
periodTo: period.days.to,
},
};
} catch (error) {
if (error instanceof DateRangeParseError) {
return { ok: false, error: error.message };
}
return { ok: false, error: "Report period is invalid." };
}
}
function sampleReportDataset(input: SampleReportInput): GridDataset {
const rows = SAMPLE_ROWS.filter(
(row) => input.segment === "all" || row.segment === input.segment,
);
const totalRevenue = rows.reduce((sum, row) => sum + row.revenue, 0);
const totalCost = rows.reduce((sum, row) => sum + row.cost, 0);
const totalMargin = totalRevenue - totalCost;
return {
name: "sample-report",
label: "Sample Report",
rootLevel: "channel",
levels: {
channel: {
label: "Channels",
columns: [
{ id: "channel", label: "Channel", kind: "text", minWidth: 40 },
{
id: "segment",
label: "Segment",
kind: "text",
visuallyHidden: true,
},
{
id: "periodFrom",
label: "Period From",
kind: "date",
visuallyHidden: true,
},
{
id: "periodTo",
label: "Period To",
kind: "date",
visuallyHidden: true,
},
{
id: "revenue",
label: "Revenue",
kind: "number",
displayFormat: "currency",
colorRule: "positive",
zeroDisplay: "blank",
},
{
id: "cost",
label: "Cost",
kind: "number",
displayFormat: "currency",
colorRule: "negative",
zeroDisplay: "blank",
},
{
id: "margin",
label: "Margin",
kind: "number",
displayFormat: "currency",
colorRule: "signed",
zeroDisplay: "blank",
},
{ id: "notes", label: "Notes", kind: "text", minWidth: 64 },
],
childLevels: [],
},
},
nodes: rows.map((row) => ({
rowKey: row.rowKey,
levelName: "channel",
columns: {
channel: row.channel,
segment: row.segment,
periodFrom: input.periodFrom,
periodTo: input.periodTo,
revenue: row.revenue,
cost: row.cost,
margin: row.revenue - row.cost,
notes: row.notes,
},
})),
footerRows: [
{
rowKey: "total",
columns: {
channel: "Total",
revenue: totalRevenue,
cost: totalCost,
margin: totalMargin,
},
},
],
};
}
references/app-building/reports/sample-contract.ts.example
// Example only. Copy into packages/shared/src/contracts/<report-name>.ts and adapt names, paths, and query params.
import { z } from "zod";
import { initContract } from "@sapporta/rest-core";
import { RELATIVE_DURATIONS } from "@sapporta/shared";
import { errorBodySchema } from "@sapporta/shared/contracts";
import { gridDatasetSchema } from "@sapporta/shared/grid-dataset";
const c = initContract();
const sampleReportSegmentSchema = z.enum(["all", "new", "expansion"]);
export const sampleReportQuerySchema = z.object({
period_relative: z.enum(RELATIVE_DURATIONS).optional(),
period_from: z.string().optional(),
period_to: z.string().optional(),
segment: sampleReportSegmentSchema.optional(),
});
export type SampleReportQuery = z.output<typeof sampleReportQuerySchema>;
export type SampleReportSegment = z.output<typeof sampleReportSegmentSchema>;
export const sampleReportContract = c.router({
sampleReport: c.query({
method: "GET",
path: "/reports/sample",
summary: "Sample report",
metadata: { tags: ["reports"] },
query: sampleReportQuerySchema,
responses: {
200: gridDatasetSchema,
400: errorBodySchema,
},
}),
});
references/app-building/reports/sample-screen.tsx.example
// Example only. Copy into packages/frontend/src/reports/<ReportName>.tsx and adapt route params, typed client, toolbar, and links.
import { useEffect, useMemo, useState } from "react";
import { useSearchParams } from "react-router-dom";
import {
parseDateRange,
relative,
serializeDateRange,
Temporal,
type DateRangeState,
} from "@sapporta/shared";
import {
buildSearchParams,
createSnapshotUrl,
DateRangeField,
ReportError,
ReportGridDataset,
ReportRunButton,
ReportScreenFrame,
ReportTimeZoneNote,
ReportToolbar,
} from "@sapporta/frontend/report";
import {
Select,
SelectContent,
SelectItem,
SelectTrigger,
SelectValue,
} from "@sapporta/ui/select";
import { sampleReportApi } from "../api";
import type {
SampleReportQuery,
SampleReportSegment,
} from "your-app-shared";
import { fieldError, type ReportParamError } from "./report-params";
import {
useReportDataset,
type ReportDatasetLoadContext,
} from "./use-report-dataset";
type SampleReportParams = {
period: DateRangeState;
segment: SampleReportSegment;
};
const sampleReportPath = "/reports/sample";
const defaultSampleReportParams = {
period: relative("30d"),
segment: "all",
} satisfies SampleReportParams;
const segmentLabels = {
all: "All segments",
new: "New business",
expansion: "Expansion",
} satisfies Record<SampleReportSegment, string>;
export function SampleReport() {
const [searchParams, setSearchParams] = useSearchParams();
const urlState = useMemo(
() => readSampleReportUrlParams(searchParams),
[searchParams],
);
const urlValidationErrors = useMemo(
() => validateSampleReportParams(urlState.params),
[urlState.params],
);
const urlErrors = [...urlState.errors, ...urlValidationErrors];
const reportInput = urlErrors.length === 0 ? urlState.params : null;
const [reportInputParams, setReportInputParams] = useState(urlState.params);
const [reportParamErrors, setReportParamErrors] = useState<
ReportParamError<SampleReportParams>[]
>([]);
const urlParamsKey = searchParams.toString();
useEffect(() => {
setReportInputParams(urlState.params);
setReportParamErrors([]);
}, [urlParamsKey]);
const report = useReportDataset({
input: reportInput,
load: loadSampleReport,
});
const visibleErrors =
reportParamErrors.length > 0 ? reportParamErrors : urlErrors;
const snapshotUrl = createSnapshotUrl(
sampleReportPath,
sampleReportQuery(reportInputParams),
);
function runReport() {
const errors = validateSampleReportParams(reportInputParams);
if (errors.length > 0) {
setReportParamErrors(errors);
return;
}
setReportParamErrors([]);
setSearchParams(buildSearchParams(sampleReportQuery(reportInputParams)));
}
return (
<ReportScreenFrame
title="Sample Report"
subtitle="Revenue, cost, and margin by channel."
actions={
<a
href={snapshotUrl}
className="text-sap-data text-sap-muted hover:text-sap-emph"
>
Snapshot
</a>
}
>
<ReportToolbar
actions={
<ReportRunButton
loading={report.loading}
disabled={report.loading}
onClick={runReport}
/>
}
>
<DateRangeField
label="Period"
value={reportInputParams.period}
onChange={(period) =>
setReportInputParams({ ...reportInputParams, period })
}
error={fieldError(visibleErrors, "period")}
/>
<SegmentField
value={reportInputParams.segment}
onChange={(segment) =>
setReportInputParams({ ...reportInputParams, segment })
}
error={fieldError(visibleErrors, "segment")}
/>
{/* Name the calendar the period was resolved in. */}
<ReportTimeZoneNote />
</ReportToolbar>
{urlErrors.length > 0 ? (
<ReportError error={urlErrors[0]?.message ?? "Invalid report input."} />
) : null}
{report.status === "error" ? <ReportError error={report.error} /> : null}
<div className="min-h-0 flex-1 overflow-auto bg-sap-panel">
{report.dataset ? (
<ReportGridDataset
dataset={report.dataset}
linkContext={reportInput ? { input: reportInput } : undefined}
/>
) : (
<div className="p-[18px] text-sap-data text-sap-muted">
Run the report to load results.
</div>
)}
</div>
</ReportScreenFrame>
);
}
function SegmentField({
value,
onChange,
error,
}: {
value: SampleReportSegment;
onChange(value: SampleReportSegment): void;
error?: string | null;
}) {
return (
<label className="flex items-center gap-2 text-sap-data">
<span className="text-sap-subtle">segment:</span>
<Select
value={value}
onValueChange={(next) => onChange(next as SampleReportSegment)}
>
<SelectTrigger
aria-invalid={error ? true : undefined}
className="h-sap-ctl w-[160px] rounded-[5px] text-sap-emph"
>
<SelectValue />
</SelectTrigger>
<SelectContent>
{Object.entries(segmentLabels).map(([segment, label]) => (
<SelectItem key={segment} value={segment}>
{label}
</SelectItem>
))}
</SelectContent>
</Select>
{error ? <span className="text-sap-negative">{error}</span> : null}
</label>
);
}
function readSampleReportUrlParams(searchParams: URLSearchParams): {
params: SampleReportParams;
errors: ReportParamError<SampleReportParams>[];
} {
const raw = Object.fromEntries(searchParams.entries());
const errors: ReportParamError<SampleReportParams>[] = [];
let period = defaultSampleReportParams.period;
try {
period = parseDateRange("period", raw);
} catch {
errors.push({
field: "period",
message: "Period in the URL is invalid.",
});
}
const segment = sampleReportSegment(raw.segment);
if (segment === null) {
errors.push({
field: "segment",
message: "Segment in the URL is invalid.",
});
}
return {
params: {
period,
segment: segment ?? defaultSampleReportParams.segment,
},
errors,
};
}
function validateSampleReportParams(
params: SampleReportParams,
): ReportParamError<SampleReportParams>[] {
if (
params.period.type === "custom" &&
params.period.start &&
params.period.end &&
Temporal.PlainDate.compare(params.period.start, params.period.end) > 0
) {
return [
{
field: "period",
message: "Period start must be on or before period end.",
},
];
}
return [];
}
function sampleReportQuery(params: SampleReportParams): SampleReportQuery {
return {
...serializeDateRange(params.period, "period"),
segment: params.segment === "all" ? undefined : params.segment,
};
}
async function loadSampleReport(
input: SampleReportParams,
context: ReportDatasetLoadContext,
) {
return sampleReportApi.sampleReport({
query: sampleReportQuery(input),
fetchOptions: { signal: context.signal },
});
}
function sampleReportSegment(value: unknown): SampleReportSegment | null {
if (value === undefined || value === null || value === "") return "all";
if (value === "all" || value === "new" || value === "expansion") {
return value;
}
return null;
}
references/app-building/reports/use-dataset.ts.example
// Optional app-local helper. Copy into packages/frontend/src/reports/use-report-dataset.ts when a report screen needs GridDataset loading state.
import { useCallback, useEffect, useRef, useState } from "react";
import type { GridDataset } from "@sapporta/shared/grid-dataset";
export type ReportDatasetLoadContext = {
signal: AbortSignal;
};
export type UseReportDatasetOptions<TInput> = {
input: TInput | null;
load(input: TInput, context: ReportDatasetLoadContext): Promise<GridDataset>;
enabled?: boolean;
};
export type ReportDatasetSnapshot =
| {
status: "idle";
dataset: null;
error: null;
loading: false;
}
| {
status: "loading";
dataset: GridDataset | null;
error: null;
loading: true;
}
| {
status: "success";
dataset: GridDataset;
error: null;
loading: false;
}
| {
status: "error";
dataset: GridDataset | null;
error: Error;
loading: false;
};
export type ReportDatasetState = ReportDatasetSnapshot & {
reload(): void;
};
const IDLE_SNAPSHOT = {
status: "idle",
dataset: null,
error: null,
loading: false,
} satisfies ReportDatasetSnapshot;
export function useReportDataset<TInput>(
options: UseReportDatasetOptions<TInput>,
): ReportDatasetState {
const loadRef = useRef(options.load);
const requestIdRef = useRef(0);
const [reloadKey, setReloadKey] = useState(0);
const [snapshot, setSnapshot] =
useState<ReportDatasetSnapshot>(IDLE_SNAPSHOT);
loadRef.current = options.load;
useEffect(() => {
if (options.input === null || options.enabled === false) {
requestIdRef.current += 1;
setSnapshot(IDLE_SNAPSHOT);
return;
}
const requestId = requestIdRef.current + 1;
requestIdRef.current = requestId;
const controller = new AbortController();
setSnapshot((previous) => ({
status: "loading",
dataset: previous.dataset,
error: null,
loading: true,
}));
loadRef
.current(options.input, { signal: controller.signal })
.then((dataset) => {
if (requestIdRef.current !== requestId || controller.signal.aborted) {
return;
}
setSnapshot({
status: "success",
dataset,
error: null,
loading: false,
});
})
.catch((thrown: unknown) => {
if (requestIdRef.current !== requestId || controller.signal.aborted) {
return;
}
setSnapshot((previous) => ({
status: "error",
dataset: previous.dataset,
error: normalizeReportError(thrown),
loading: false,
}));
});
return () => {
controller.abort();
};
}, [options.enabled, options.input, reloadKey]);
const reload = useCallback(() => {
setReloadKey((current) => current + 1);
}, []);
return {
...snapshot,
reload,
};
}
function normalizeReportError(thrown: unknown): Error {
if (thrown instanceof Error) return thrown;
if (typeof thrown === "string") return new Error(thrown);
return new Error("Report request failed.");
}
references/app-building/seed-demo-data.md
# Seed Demo Data
Fill a development database with realistic sample data so the app can be
demonstrated and its screens judged against real content.
The scaffold owns this. Write rows in `packages/api/seed.ts` and run
`pnpm seed`. Do not build a seeding mechanism of your own.
- Seeding, script runtime, and the permission that guards them:
https://sapporta.com/docs/guides/operations/sample-data-and-scripts.md
- Environment settings the run reads:
https://sapporta.com/docs/reference/project/environment-variables.md
## What Not To Do
- Never sign in over HTTP to seed. No cookie jar, no `fetch` wrapper, no calls
to `/api/auth-context` or `/api/auth/sign-up/email`. A script runs on the same
machine as the database; no server needs to be running.
- Never ask for an agent access token. Only a signed-in person can create one,
and a freshly scaffolded app has no account yet. Tokens are for an existing
app's live data; see [../data-console/guide.md](../data-console/guide.md).
- Never write seed rows with raw SQL. `INSERT` skips validation, column
defaults, and ownership stamping.
- Never call `openScriptRuntime()` from a route, from middleware, or from
anything they reach. A served request already carries the row access it
earned, at `c.get("auth")`.
## Which Runtime
- `pnpm seed` / sample data -> `openSeedRuntime(account)` from
`./seed-runtime.js`. Creates the account on the first run, signs in after.
- A nightly job, one-off import, or maintenance task -> `openScriptRuntime({
email, password })` from `./script-runtime.js`. Signs in and creates nothing.
Both return `{ workspace, rows, db, auth, close }`. Seed plain rows through
`rows(table)`. When the change is more than a row — a total derived across
tables, a transition that writes an event beside it — call the domain workflow
with `{ db: demo.db, auth: demo.auth }`, the same pair a route passes it.
## Rules For The Run
- Apply migrations before seeding. Seeding writes rows; it does not create
tables.
- `.env.development` must set `SAPPORTA_ALLOW_SAMPLE_DATA_SEEDING=true` and
`NODE_ENV` must not be `production`. Never carry that setting into a
deployment: the password is in the source.
- Keep the run idempotent. Guard each write with a count so a second `pnpm seed`
does not duplicate rows.
- Create parent rows first and take foreign keys from the returned row. Never
write a literal id.
- Omit `id`, `created_at`, `updated_at`, `workspace_id`, and
`scoped_to_user_id`. They are generated or stamped, and supplying them is
rejected.
- Use column names exactly as the schema declares them, and write values in the
type the column expects.
- The seeded workspace keeps the time zone of the machine that ran the seed, so
seeded timestamps read on the local clock. An owner changes it at
`/workspace/settings`; see [days-and-time-zones.md](days-and-time-zones.md).
- Verify by signing in as the demo account and opening the pages that show the
seeded data.
## Reference Data
For rows that belong to the application rather than to a person — currencies,
categories, country codes — define the table with a system-wide row scope and
seed it the same way. Such rows carry no workspace or user.
references/app-building/tables/api-reference.md
# Table Definition Reference
Use the canonical documentation for table-definition API details.
Use the canonical documentation:
- Table definitions: https://sapporta.com/docs/reference/schema/table-definitions.md
- Table and column metadata: https://sapporta.com/docs/reference/schema/table-and-column-metadata.md
- Tables, columns, and schema metadata: https://sapporta.com/docs/guides/model-data/tables-columns-and-schema-metadata.md
- Search table rows and relationships: https://sapporta.com/docs/guides/model-data/search-indexes-and-display-metadata.md
- Relationships and lookup behavior: https://sapporta.com/docs/guides/model-data/relationships-and-lookup-behavior.md
- Auth and row security: https://sapporta.com/docs/reference/server/auth-and-row-security.md
Agent reminder: keep schema edits in `packages/api/schema/`, derive row types
from Drizzle exports, declare row scope explicitly, and hide system-managed
scope fields from generated screens.
references/app-building/tables/create.md
# Table Creation
Translate an accepted product model into tables; do not derive the product model
from a requested table or form shape.
Table schema work belongs in `packages/api/schema/`. Inspect existing schema
files before adding a new pattern:
```bash
rg --files packages/api/schema
```
Use local application schemas for project conventions. To discover how to
define application tables from the canonical starter pattern, use the guide
below.
Define the raw Drizzle table and its inferred row types before building a
custom frontend form. Export `typeof table.$inferSelect` and
`typeof table.$inferInsert` aliases from the schema module for server use. After
the table is registered, read [frontend/forms.md](../frontend/forms.md) for the
metadata-driven form workflow.
When the schema directory contains only project-authentication tables, treat
the project as a fresh app. Read the complete starter pattern before framework
source, internal fixtures, or generated declarations. Then adapt the pattern to
the requested domain.
Use the public docs for column factories, metadata fields, row-scope reference,
filter/search behavior, relationship metadata, and migration details:
- Tables, columns, and schema metadata: https://sapporta.com/docs/guides/model-data/tables-columns-and-schema-metadata.md
- Table definitions: https://sapporta.com/docs/reference/schema/table-definitions.md
- Table and column metadata: https://sapporta.com/docs/reference/schema/table-and-column-metadata.md
- Search table rows and relationships: https://sapporta.com/docs/guides/model-data/search-indexes-and-display-metadata.md
- Table validation: https://sapporta.com/docs/reference/schema/table-validation.md
- Semantic value boundaries: https://sapporta.com/docs/reference/schema/semantic-value-boundaries.md
- Relationships and lookup behavior: https://sapporta.com/docs/guides/model-data/relationships-and-lookup-behavior.md
- Schema changes and migrations: https://sapporta.com/docs/guides/model-data/schema-changes-and-migrations.md
- Auth and row security: https://sapporta.com/docs/reference/server/auth-and-row-security.md
## Canonical Starter Pattern
To discover how Sapporta application tables are defined, start with the
canonical [Tables, columns, and schema metadata](https://sapporta.com/docs/guides/model-data/tables-columns-and-schema-metadata.md)
guide. Its complete parent/child starter demonstrates:
- raw Drizzle tables and exported Sapporta wrappers;
- semantic text, date, and timestamp columns;
- primary keys, foreign keys, indexes, and cascade behavior;
- select metadata, search, row labels, child metadata, and row scope;
- Temporal timestamp defaults; and
- the named migration, SQL review, migrate, and check loop.
Use that example as the starter pattern, then rename and adapt the tables, columns,
scope, relationships, and metadata to the user's domain. For an exact option or
type, follow its links to the table-definition and metadata references above.
Do not copy the example's project/task domain into an unrelated app.
The generated application layout is documented at
https://sapporta.com/docs/reference/project/project-files.md when the
package responsibilities or extension points are unclear.
## Minimum `sapportaTable` Metadata
Start every application table wrapper with explicit product metadata:
```ts
meta: {
label: "...",
rowScope: "workspaceUserScoped",
rowLabelColumns: ["display_name"],
},
```
`rowLabelColumns` must be non-empty, name columns on the raw Drizzle table, and
form a meaningful label in lookups and references. Use a human-facing value such
as `name`, `title`, `number`, or a combination such as `first_name` and
`last_name`; do not use an opaque primary key merely to satisfy the requirement.
Default to `workspaceUserScoped`: it is the strictest workspace boundary and
prevents other workspace members from seeing a row unless the product explicitly
allows it. It requires both `workspace_id` and `scoped_to_user_id`. Use
`workspaceGlobal` only after confirming that every authorized member of the
workspace should be able to access every row; use `systemGlobal` only when the
data deliberately has no workspace boundary.
Keep a pure join table contextual rather than treating it as an independently
named resource. If it needs a direct screen or lookup, add a meaningful,
displayable domain value and use that as its row label instead of the join row's
primary key.
## Authoring Rules
- Export the raw Drizzle table so other schema files can reference its columns
in `.references()`.
- Export the Sapporta table wrapper used by the current app. Follow the local
project style and current docs; do not introduce an older wrapper form into a
newer app.
- Before generating a migration, confirm each `sapportaTable()` has explicit
`label`, `rowScope`, and non-empty, human-meaningful `rowLabelColumns`.
Start with `workspaceUserScoped`; document why any broader scope is required.
Keep pure join tables contextual unless they gain a displayable domain label.
- Export row and insert aliases from the raw table definition:
`export type Project = typeof projectsTable.$inferSelect` and
`export type NewProject = typeof projectsTable.$inferInsert`. These are server
types: reuse them in `packages/api` domain code, and never import them into
`packages/frontend`, where a `date()` column resolves to Temporal rather than
the string the generated routes return. Frontend code types a row with a
projection; read
[../frontend/row-projections.md](../frontend/row-projections.md).
- Use Sapporta semantic column factories for values when available. Use raw
Drizzle columns for primary keys, foreign keys, and unsupported edge cases.
Each factory preserves its column-name literal, so a row read through
`TableRow`/`scopedRows()` keeps each column's own type. `pnpm typecheck`
(`tsc --noEmit`) is what reports a mismatch; `vite build` erases types without
checking them.
- When a table declares `meta.children`, declare the child's foreign key
non-writable on the same pass — `references: { fk: { apiSettable: false } }`
on the child, or `columns: { fk: { apiWritable: false } }`. Without it a
`$details` row carrying that key is accepted and the value is silently
replaced by the created master's key.
- Use Temporal for date/time defaults and app-level date work. Do not use
`Date`, `dayjs`, or `date-fns`. A day is a calendar day in the workspace's
zone; read [../days-and-time-zones.md](../days-and-time-zones.md) before
writing day arithmetic.
- Keep table and column names in the app's established convention, normally SQL
`snake_case`, kebab-case filenames, and camelCase exports.
## Column Sizing
For table `width`, `minWidth`, or `maxWidth`, use the canonical [Column sizing](https://sapporta.com/docs/reference/column-sizing.md) reference.
## Auth Row Scope
Projects must declare `meta.rowScope` on every table and include
the required scope columns:
- `workspaceUserScoped` -> `workspace_id` and `scoped_to_user_id`
- `workspaceGlobal` -> `workspace_id`
- `systemGlobal` -> no workspace predicate
Do not infer scope from column presence. Scope columns are system-managed;
clients must not submit or edit `workspace_id`, `workspaceId`,
`scoped_to_user_id`, or `scopedToUserId`. Hide scope columns from generated
screens.
Use Drizzle `.references()` wherever possible. Use metadata references only
when a relationship cannot be proven from Drizzle metadata or needs additional
server-managed policy.
## Relationship Inventory
Before finishing schema work, make a relationship pass:
1. List every FK as `source_table.source_fk -> target_table.id`.
2. Confirm the source column uses `.references()` unless there is a documented
reason it cannot.
3. Add `meta.children` on the target table when users need to inspect, create,
or navigate source rows from the target row.
4. Include multiple child entries when a parent has multiple useful collections.
5. Choose human-facing child labels, compact columns, and stable child sort when
order matters.
6. For self-FKs, avoid recursive `children` unless the UI path is proven; use a
report for hierarchy views when needed.
7. For join tables, add children from both endpoint tables when both browsing
directions matter.
8. Identify parent resources whose useful discovery text lives in child
collections—for example, a book found by a quote, an order found by a line
description, or a case found by a note. Treat these as candidates for
explicit relational search.
## Table And Row Linking
Sapporta derives navigation links from the relationship metadata above and
renders them in the table grid without extra code:
- Every FK column gets a drill-up link to the referenced table's rows (the
cell shows a trailing link adornment; Enter opens it on read-only cells).
- Every `meta.children` entry gets a row-level drill-into link ("Open Line
Items") offered in the row's right-click context menu.
So the relationship pass above is also the linking pass: declaring
`.references()` and `meta.children` correctly is what produces useful default
navigation.
On top of the derived links, declare domain links with the shared
`NavLink` type (`@sapporta/shared/contracts`):
```ts
meta: {
// ...
columns: {
customer_id: {
links: [
{ kind: "report", report: "customer-statement",
bind: { customer_id: "customer_id" }, label: "Customer statement" },
],
},
},
rowLinks: [
{ kind: "table", table: "payments",
bind: { invoice_id: "id" }, label: "Payments received" },
{ kind: "url", href: "https://dashboard.stripe.com/invoices/{stripe_id}",
label: "Open in Stripe", target: "_blank" },
],
},
```
`bind` maps the destination's filter/parameter names to source columns on the
current row (`{ destination: source }`). Schema extraction validates links:
unknown source columns (in binds or url `{column}` placeholders), unknown
destination tables, and unknown destination columns fail at boot, not
silently in the UI. A link only resolves on rows
where every bound value is present, so nullable FKs need no guarding.
Column `links` surface on the cell (first resolvable link is primary) and in
the context menu; `rowLinks` surface in the row's context menu after the
cell's own links. The same declarations flow through the schema metadata API,
so report screens and custom views can reuse them.
Declare links domain-aware: for each table, ask what a user looking at this
row does next in their workflow — settle an invoice, review an account's
ledger, check a shipment's tracking — and link exactly those destinations.
Do not add links whose destination the user cannot act on, and rely on the
derived FK/children links instead of restating them. The linking reference
for reports, [reports/linking.md](../reports/linking.md), documents the full
`NavLink` vocabulary (`table` | `report` | `url`, labels, icons, targets).
## Search Behavior
Leave search unset by default, and retain an existing `search: "allColumns"`
unless requirements say otherwise. Unset search is equivalent to
`"allColumns"` and is the right behavior for most tables; it searches the
visible application values of the current row without walking into has-many
children.
Customize search only when the product requires a different discovery surface.
When searching a parent should also return it for a match in a child collection,
read the canonical [Search table rows and relationships](https://sapporta.com/docs/guides/model-data/search-indexes-and-display-metadata.md)
guide before editing metadata. It owns the recursive `search.children` shape,
field-selection rules, authorization behavior, relationship index guidance, and
the boundary between generated relational search and app-owned full-text search.
Do not infer child traversal merely because `meta.children` exists.
## Numeric And Enum Pitfalls
- Additive numeric fields should be non-null with a default. Use nullable
numeric fields only when `null` has domain meaning, and mark the metadata
accordingly.
- For enum-like fields, use the documented select metadata unless the app needs
a reference table.
## Validation And Semantic Values
When generated CRUD needs constraints beyond the semantic column kind, read the
[table-validation reference](https://sapporta.com/docs/reference/schema/table-validation.md)
before defining `meta.validation`. Confirm create, partial-update, nullable,
defaulted, client-editable, and server-managed field behavior. Keep conversion
or normalization outside validation unless the save path explicitly consumes
the transformed result.
When app-owned code reads or writes generated table values, duplicates a
generated form, or introduces a select-backed domain type, read the
[semantic-value boundary reference](https://sapporta.com/docs/reference/schema/semantic-value-boundaries.md).
Keep select-backed text values as strings across form, wire, runtime, and
database boundaries. Parse and serialize other semantic kinds at the documented
boundary instead of inventing a second conversion path.
## Migration And Validation Loop
After table changes, run the loop below. Replace `data` with the
`SAPPORTA_DATA_DIR` value in the project's `.env.development`; the `db:*`
scripts read it only from the environment and stop when it is missing.
```bash
SAPPORTA_DATA_DIR=data pnpm --filter ./packages/api db:generate --name <change_name>
# Review the generated SQL before continuing.
SAPPORTA_DATA_DIR=data pnpm --filter ./packages/api db:migrate
SAPPORTA_DATA_DIR=data pnpm --filter ./packages/api db:check
pnpm exec sapporta tables show <table_name>
```
The server validates table definitions at startup and does not apply migrations
automatically.
## Reference Files
These files intentionally point to canonical docs instead of carrying API
reference copies:
- [Canonical table docs](api-reference.md)
- [Canonical table examples](examples.md)
references/app-building/tables/examples.md
# Table Definition Examples
To discover table-definition patterns, use the complete parent/child starter
and the canonical references below. When `packages/api/schema/` contains only
project-authentication tables, take the fresh-app branch in
[create.md](create.md) before using these examples.
Use the canonical documentation:
- Complete parent/child starter and schema metadata: https://sapporta.com/docs/guides/model-data/tables-columns-and-schema-metadata.md
- Search table rows and relationships: https://sapporta.com/docs/guides/model-data/search-indexes-and-display-metadata.md
- Relationships and lookup behavior: https://sapporta.com/docs/guides/model-data/relationships-and-lookup-behavior.md
- Schema changes and migrations: https://sapporta.com/docs/guides/model-data/schema-changes-and-migrations.md
- Table definitions reference: https://sapporta.com/docs/reference/schema/table-definitions.md
- Table and column metadata reference: https://sapporta.com/docs/reference/schema/table-and-column-metadata.md
- Table validation reference: https://sapporta.com/docs/reference/schema/table-validation.md
- Semantic value boundaries: https://sapporta.com/docs/reference/schema/semantic-value-boundaries.md
Agent reminder: adapt examples to the current app's wrapper/import style, add
required `rowLabelColumns`, declare `rowScope`, and verify relationships with a
relationship inventory before generating a migration.
references/app-building/workflow-shell.md
# Build The Workflow Shell
Apply this reference after the product model names the users, records, states,
and recurring work. Use the full pass for a new app. Revisit only the affected
parts when a later feature changes where routine work starts, what normally
comes next, or where users inspect the result.
## Contents
- [Map The Everyday Path](#map-the-everyday-path)
- [Build The Main Sidebar](#build-the-main-sidebar)
- [Build One Complete Advanced Or Admin Page](#build-one-complete-advanced-or-admin-page)
- [Build The Protected Home Page](#build-the-protected-home-page)
- [Place Later Features](#place-later-features)
- [Represent Deferred Actions Honestly](#represent-deferred-actions-honestly)
- [Verify The Shell](#verify-the-shell)
## Map The Everyday Path
Inspect the existing sidebar, routes, tables, reports, tools, settings, and
roles. Write the normal path with the words users use:
```text
prerequisite -> recurring action -> checkpoint or review
-> completion -> inspect the result
```
Keep only phases that exist in this app. Do not force a generic
setup-import-review-finish sequence onto another domain.
Place a page by asking whether a regular user intentionally starts or resumes
work there. Put that page in the main sidebar. Keep a create form, edit page,
detail page, and record-specific action contextual when users reach it from a
list or parent record. Put direct table access, specialist reports and tools,
settings, and maintenance pages under Advanced/Admin.
For example, Orders may be a sidebar link while `/orders/new` and
`/orders/:id/edit` remain buttons on the Orders pages. A reconciliation report
used before every import may belong in the main sidebar; a diagnostic report
used during support belongs under Advanced.
Do not build the main sidebar by iterating over schema tables or registered
routes.
## Build The Main Sidebar
- Put the protected home page first, linked to `/`.
- Group links around everyday work and label them with user-facing domain
terms.
- Order groups and links by what normally follows what, but do not number
groups, links, sections, or steps.
- Include only pages where users intentionally begin or resume routine work.
A route's existence does not make it a sidebar item.
- Use a generated table or create route behind a friendly action label when it
already provides the right page.
- Include a report or queue when users routinely open it to decide the next
action. Keep specialist or diagnostic reports under Advanced.
- Put one Advanced or Admin link after the everyday groups.
- Keep the same destinations and order on desktop and mobile.
Do not ship a main sidebar that is the generated table list.
## Build One Complete Advanced Or Admin Page
Make one page reachable from the main sidebar. Include a link to every
destination exposed by Sapporta's automatic navigation or the app's previous
sidebar:
- every generated application table or Manage Data page;
- every report, including specialist and diagnostic reports;
- every app-owned importer, maintenance tool, and other specialist page;
- every settings and configuration page, including the framework's owner-only
Workspace settings screen at `workspace/settings`; and
- every other Sapporta shell destination.
Do not build a per-user or per-device time zone preference. The workspace keeps
one calendar and its owner changes it on that screen; see
[days-and-time-zones.md](days-and-time-zones.md). `SapportaRoutes.tsx` is
app-owned and is not regenerated, so a project scaffolded before that screen
shipped has no route for it and needs one added.
Group the links by concrete nouns such as Tables, Reports, Import tools, and
Settings. Build groups from schema metadata and route or report registries when
those sources already exist, then compare the page with the current automatic
navigation so nothing disappears as the app changes.
Use **Advanced** when regular users may open the page. Use **Admin** when a role
or ability restricts it. Show only authorized links, and keep the same route and
server authorization that each destination already requires. Direct table
editing may need stronger permission than the normal workflow.
Set `showFrameworkNavigation={false}` or use the installed version's equivalent
only after this page has equivalent destination coverage and authorization. Do
not trade a cluttered sidebar for missing or overexposed functionality.
## Build The Protected Home Page
Put the domain home page in `appHomeRoute` in `packages/frontend/src/App.tsx`.
That slot opens `/` for signed-in users and is where sign-in lands, so replace
the generated `Home` screen instead of adding a redirect or a second home path.
Leave `appPublicHomeRoute` empty unless the product needs an anonymous landing
page at `/`; filling it opens `/` to everyone and takes `/` away from
`appHomeRoute`. Do not edit `SapportaApp.tsx` to change where the home page
renders.
Help a new user answer four questions: what is this app for, where do I begin,
what happens next, and where do I inspect the result?
Follow the same path as the sidebar. Start with one short explanation of the
app's purpose. For each part of the normal path, use:
- a domain heading;
- a short paragraph that connects this action to the previous and next work;
and
- an obvious button labeled with a verb and object, such as **Create order**,
**Review requests**, or **Open account history**.
State important boundaries where the user acts. For example, say that imported
rows stay in Draft entries until the user posts them, or that submitting a
request sends it to the Review queue.
Keep the page useful after onboarding. Point every button to a real work page,
and show the next incomplete action only when existing data can determine it
reliably.
Write short connected prose for a person who knows the domain but not the
schema or route layout. Use the terms shown elsewhere in the app. Remove
slogans, implementation names, generic encouragement, repeated explanations,
and copy that merely restates a button.
Make action buttons the visual center. Use one restrained type hierarchy,
spacing, and simple dividers. Do not number sections or present their count as
progress. Avoid step badges, eyebrow labels, a card around every section,
decorative shadows, competing accent colors, and ornamental icons. Keep any
secondary action quieter than the main button.
## Place Later Features
- Put a new routine starting or resuming page in the relevant sidebar group.
Add it to the home page only when it changes the core path.
- When a new checkpoint or completion action changes what normally comes next,
update the sidebar order, home-page explanation, buttons, and result links.
- Keep create, edit, detail, and record-specific pages contextual unless users
routinely start there.
- Put a new specialist report, tool, direct table page, or setting under
Advanced/Admin.
- Update the Advanced/Admin inventory and authorization whenever any shell
destination changes.
- Leave the shell alone when a small implementation change does not alter the
user's path.
## Represent Deferred Actions Honestly
Use an existing working route when it supports the action. Build a missing page
when it belongs to the accepted feature. If the user explicitly defers the
behavior but wants its future place shown, link to a clearly labeled placeholder
page that says the action is not available yet. Do not add a dead link or a
button that appears to perform the action.
## Verify The Shell
- As a new regular user, confirm that the home page answers the four questions,
the sidebar follows everyday work without numbering, and each button reaches
a working authorized route.
- As each Advanced/Admin role, compare the page with Sapporta's automatic
navigation. Confirm access to every table, report, specialist tool, setting,
and other shell destination without granting a new permission.
- Confirm that hidden links do not substitute for route and server
authorization.
- Confirm that desktop and mobile expose the same destinations, order, and
active-route behavior.
- Confirm that every deferred action is an honest placeholder rather than a
dead or fake control.
references/data-console/guide.md
# Data Console
Use this workflow when the user wants to work with records in an existing Sapporta
app rather than change the app's code. Prefer the project-local command form:
```bash
pnpm exec sapporta ...
```
Use docs for CLI grammar, target selection, token setup, table/query syntax, and
recipes:
- Agent data console: https://sapporta.com/docs/guides/discovery/use-the-agent-data-console.md
- CLI workflow: https://sapporta.com/docs/guides/discovery/use-the-sapporta-cli.md
- Agent access: https://sapporta.com/docs/guides/security/agent-access-and-scoped-tokens.md
- CLI commands: https://sapporta.com/docs/reference/cli/overview-and-global-options.md
## Contents
- [Required Preflight](#required-preflight)
- [Discover Before Acting](#discover-before-acting)
- [Answer Data Questions](#answer-data-questions)
- [Change Data Safely](#change-data-safely)
- [Data Safety Rules](#data-safety-rules)
- [Read The Narrow Reference](#read-the-narrow-reference)
## Required Preflight
For API-backed CLI commands, verify the selected app server is reachable:
```bash
pnpm exec sapporta endpoints list
```
Use `endpoints list` for this check. A failure means the server is
unreachable or is the wrong server.
If a command fails with `APP_SERVER_UNREACHABLE`, follow the CLI message before
diagnosing app, auth, or schema behavior. Run the command from inside the
project so the CLI resolves that project's API port from `.env.development`.
Every project gets its own ports, so never assume port 3000 and never pass
`--api-url` to reach the project you are already inside.
If a protected data command returns `unauthenticated`, `token_expired`,
`token_revoked`, or `workspace_required`, read
[server-access.md](server-access.md), stop data
work, and ask the user to create or replace the agent access token. Do not
silently bypass protected app APIs with direct SQLite or local database access
for workspace-user answers or mutations.
## Discover Before Acting
Start with discovery before composing requests or mutating data:
```bash
pnpm exec sapporta endpoints list
pnpm exec sapporta endpoints show "METHOD /api/path"
pnpm exec sapporta tables list
pnpm exec sapporta tables show <name>
pnpm exec sapporta tables sample <name>
```
Endpoint output carries each table's column schema. Prefer it over
`tables` commands for schema discovery.
The CLI can inspect app-owned routes with `endpoints list` and
`endpoints show`, and can invoke arbitrary endpoints with
`api get/post/put/delete`. Use `curl` or a typed client when that is more
convenient for the route.
## Answer Data Questions
Prefer the highest-level app feature that answers the question:
1. Existing report or domain endpoint that owns the business definition.
2. `rows count` for a filtered total or bounded one-column grouping.
3. Built-in table queries for ordinary visible-row retrieval.
4. An app-owned scoped report or read endpoint for a reusable advanced query.
5. Read-only SQL only for explicitly authorized administration or debugging.
Report results back with provenance: name the report, table endpoint, domain
endpoint, or SQL path used. State date parameters, filters, workspace, and row
limits that affect the answer. If repeated ad-hoc SQL would be useful to users,
suggest creating a route-based report.
## Change Data Safely
Only mutate data when the user asked for a data change. Use the highest fitting
option:
1. Existing custom product/domain endpoint from `sapporta endpoints list/show`.
2. Built-in row commands: `sapporta rows create/update/delete`.
3. Raw SQL fallback after reading [sql-fallback.md](sql-fallback.md).
Do not recommend reports as mutation surfaces unless the app deliberately
defines a mutating route.
Direct local database inspection is acceptable for app-development or debugging
tasks where you are acting as a developer with repository access. For
data-console answers, treat the running app API as the user's workspace boundary
unless the user explicitly asks for admin/debug inspection.
## Data Safety Rules
- Accept user-provided data as-is; do not silently coerce business values.
- Do not fabricate foreign keys; resolve them from visible app data.
- Respect NOT NULL constraints.
- Omit generated columns such as `id`, `created_at`, and `updated_at`.
- Omit system-managed `workspace_id`, `workspaceId`,
`scoped_to_user_id`, and `scopedToUserId`.
- Do not use raw SQL to simulate another workspace/user's visibility.
- Prefer built-in row commands for ordinary row creation because they run the
app's normal save behavior.
- Keep writes scoped to the specific records the user asked to change.
## Read The Narrow Reference
- Insert rows or write flat data into a running app -> read
[row-writes.md](row-writes.md)
- Demo or sample data for a development database, seed scripts, `pnpm seed`
-> read
[../app-building/seed-demo-data.md](../app-building/seed-demo-data.md).
Seeding needs no server, no sign-in, and no access token.
- Insert parent and child records atomically -> read
[master-detail-writes.md](master-detail-writes.md)
- Call existing report routes or answer data questions -> read
[report-runs.md](report-runs.md)
- Call generated table HTTP routes or compose their filters, search, sort,
pagination, lookup, count, export, or CLI `--where` -> read
[table-queries.md](table-queries.md)
- Raw SQL fallback reads or writes -> read
[sql-fallback.md](sql-fallback.md)
references/data-console/master-detail-writes.md
# Master-Detail Insertion
Only insert parent-child data when the user has asked for a data change. Inspect
both table schemas first, identify the detail FK, and never fabricate values,
credentials, or foreign keys.
Docs:
- Agent data console: https://sapporta.com/docs/guides/discovery/use-the-agent-data-console.md
- Generated table APIs: https://sapporta.com/docs/guides/generated-surfaces/generated-table-apis.md
- Schema metadata: https://sapporta.com/docs/reference/schema/schema-metadata-types.md
## Workflow
1. Inspect both tables with `pnpm exec sapporta tables show <table>`.
2. Confirm the parent `meta.children` relationship when you expect generated
request schemas to expose the child branch.
3. Identify the detail table FK column that references the parent.
4. Resolve any other FKs from visible app data.
5. Insert the parent with `$details` through
`pnpm exec sapporta rows create <parent_table> --values '<json>'`.
The row create path inserts the parent, reads its primary key, backfills the
detail FK on each child row, inserts details in one transaction, and rolls back
on failure.
Critical rule: do not include the parent FK column in detail rows. The server
backfills it from the inserted parent.
Trusted scope fields are propagated by the server for
both parent and detail rows. Do not include `workspace_id`, `workspaceId`,
`scoped_to_user_id`, `scopedToUserId`, or a reference the child declares
`apiSettable: false` (or a column marked `apiWritable: false`).
Submitting the parent FK is not harmless either way. Where the child declares
it non-writable, the request answers `422 VALIDATION_FAILED` naming that column
and writes nothing. Where it does not, the request answers `201` and the
server's parent key silently replaces the value sent — the row is correct and
the caller is told nothing about the value it lost.
For three or more hierarchy levels, chain commands: insert the first pair,
query the visible child IDs, then insert the next level.
references/data-console/report-runs.md
# Report Execution
Run reports by calling their API endpoints. Use
`pnpm exec sapporta endpoints list` and `pnpm exec sapporta endpoints show` to
inspect the endpoint shape, then call the route with `sapporta api`, `curl`,
the app's typed client, or another HTTP client. Do not use `sapporta reports`;
report routes are described through OpenAPI like other app endpoints.
Docs:
- Reports: https://sapporta.com/docs/guides/reports/route-based-reports.md
- Report datasets: https://sapporta.com/docs/guides/reports/report-datasets-and-formatting.md
- OpenAPI discovery: https://sapporta.com/docs/guides/discovery/openapi-and-endpoint-discovery.md
- Agent data console: https://sapporta.com/docs/guides/discovery/use-the-agent-data-console.md
## Decision: Report Route Or Ad-Hoc Query?
1. Use `pnpm exec sapporta endpoints list` to inspect available routes.
2. If a report endpoint matches, inspect it with
`pnpm exec sapporta endpoints show "METHOD /api/reports/<path>"`.
3. Call the report route with `sapporta api get/post`, `curl`, the app's typed
client, or another HTTP client.
4. If no endpoint answers a one-table question, use `rows count` or a bounded
table query when those surfaces preserve its meaning.
5. For joins, derived measures, or a repeated advanced query, route the work to
an app-owned scoped report/read endpoint.
6. Use read-only SQL only for explicitly authorized administration or
debugging, not as workspace-user query behavior.
Use the route schema from `endpoints show` to choose query parameters or
request body fields. For a protected app, follow
[server-access.md](server-access.md); the configured token selects the
workspace.
For data questions, cite the report endpoint and parameters used, especially
date range, workspace, filters, and row limits. If no report route covers a
repeated summary, suggest creating a route-based report endpoint and screen.
This workflow is about reads. For mutations, use [row-insertion](row-writes.md),
[master-detail-insertion](master-detail-writes.md), a custom
domain endpoint, or the raw SQL fallback only when the user explicitly asks for
that kind of data change.
references/data-console/row-writes.md
# Row Insertion
Only insert or change data when the user has asked for a data change. Inspect
the table schema first, stay within the requested tables, and never fabricate
values, credentials, or foreign keys.
Docs:
- Agent data console: https://sapporta.com/docs/guides/discovery/use-the-agent-data-console.md
- Generated table APIs: https://sapporta.com/docs/guides/generated-surfaces/generated-table-apis.md
- CLI row commands: https://sapporta.com/docs/reference/cli/table-row-and-report-commands.md
## Workflow
1. Inspect the table with `pnpm exec sapporta tables show <table>`.
2. Sample visible data with `pnpm exec sapporta tables sample <table>`.
3. Resolve foreign keys from visible app data. Prefer table samples, table
filters, lookup routes, or existing domain endpoints; use SQL only as a
fallback/admin inspection tool.
4. Insert with `pnpm exec sapporta rows create <table> --values '<json>'`.
5. Verify with a table query, sample, or row get.
`--values` accepts a single JSON object or an array. Use column names exactly
as the schema exposes them, normally `snake_case`.
## Data Rules
- Provide the exact business values the user requested; do not silently coerce
meaning.
- Omit generated columns such as `id`, `created_at`, and `updated_at`.
- Omit `workspace_id`, `workspaceId`,
`scoped_to_user_id`, and `scopedToUserId`.
- Include required NOT NULL columns that the server will not generate.
- Never guess FK ids. If a referenced row does not exist and creating it is
required, insert that row first through row commands or a domain endpoint.
- Prefer row commands over raw SQL because they run server validation, defaults,
trusted ownership stamping, and visible-FK checks.
references/data-console/server-access.md
# CLI Server Access
Use this reference only for protected apps, non-default server URLs, remote
deployments, auth failures, or direct `curl` calls.
## What Needs A Token
Against a local development server, `pnpm exec sapporta endpoints list` and
`endpoints show "<METHOD> <path>"` need no token. Every other API-backed command
— `rows`, `sql`, `api`, `tables` — needs `SAPPORTA_API_TOKEN`. Against a
deployment, `endpoints` needs one too. Read schema from `endpoints`, not
`tables show`.
Why the split, and the policy that controls it:
https://sapporta.com/docs/guides/security/agent-access-and-scoped-tokens.md
Docs:
- Agent access: https://sapporta.com/docs/guides/security/agent-access-and-scoped-tokens.md
- CLI options: https://sapporta.com/docs/reference/cli/overview-and-global-options.md
- OpenAPI discovery: https://sapporta.com/docs/guides/discovery/openapi-and-endpoint-discovery.md
- Agent data console: https://sapporta.com/docs/guides/discovery/use-the-agent-data-console.md
## Agent Procedure
- API-backed CLI commands call the selected running app. Run them from inside
the project, where the CLI resolves the app's port from `SAPPORTA_API_PORT` in
`.env.development`. Pass `--api-url <url>` only to reach a different
deployment.
- If a command fails with `APP_SERVER_UNREACHABLE`, follow the CLI message. It
includes the resolved request URL and may mention sandbox network permission.
- Do not request a token for repository-only inspection or source changes.
- Do not request a token for endpoint discovery against a local development
server; run `endpoints list` first and only ask if it fails.
- For live rows, runtime read-back, or discovery against a protected
deployment, ask the user to open `/account/profile?token=new` in the intended
workspace, create an agent access token, choose **Copy prompt**, and paste
that prompt into the trusted coding-agent session opened at the project
root.
- Only a signed-in person can create a token. On a freshly scaffolded app there
is no user yet, so state plainly that data commands are unavailable until the
user signs up and creates one. Do not create an account to work around this,
and do not rebuild the CLI's job with hand-rolled sign-up calls, cookie jars,
and fetch wrappers.
- Seeding a development database is a different task and needs no token. Do not
request one for it, and do not sign in over HTTP to do it. Read
[../app-building/seed-demo-data.md](../app-building/seed-demo-data.md).
- Treat the setup prompt as secret-bearing. Reuse the project's existing mise,
direnv, or dotenv tooling. If none exists, use a private gitignored wrapper;
do not install a new environment manager for this purpose.
- Record the exact authenticated invocation in `AGENTS.md`, but never put the
token there. Do not invent, transform, print, or store tokens in source,
committed configuration, screenshots, shell history, or later task prompts.
- Verify a configured token with `pnpm exec sapporta api get '/api/auth-context'`.
The reply names the user and workspace the token acts as. Do not verify with
`endpoints list`, which answers without a credential on a local development
server, and do not verify with `rows count`, which needs a table a fresh app
does not have. Request network permission when the sandbox requires it.
- A token belongs to one user and one workspace. To work in another workspace,
the user needs a token created while that workspace is active.
- When auth fails during data-console work, stop and tell the user the targeted
API URL and token-creation link, then repeat the setup-prompt workflow for the
intended workspace. Manual `SAPPORTA_API_URL` and `SAPPORTA_API_TOKEN`
configuration remains the fallback.
- Do not fall back to direct local SQLite/database access for workspace-user
data answers or mutations unless the user explicitly asks for admin/debug
inspection. If you do, state that it is local developer inspection, not
workspace-user API behavior.
Token creation is in the browser-facing app at `/account/profile?token=new`,
not necessarily on the API origin. Prefer the app's configured public base URL when
available; otherwise derive the most likely local frontend URL from the dev
server output or environment.
references/data-console/sql-fallback.md
# Meta SQL Fallback
Use raw SQL only after confirming no higher-level surface fits:
1. Report route or existing read/domain endpoint.
2. Built-in `rows count` or table query.
3. Built-in row command for ordinary mutations.
4. Scoped custom report/read endpoint for advanced queries.
5. Custom product/domain endpoint for business actions.
6. SQL fallback.
Docs:
- Agent data console: https://sapporta.com/docs/guides/discovery/use-the-agent-data-console.md
- SQL commands: https://sapporta.com/docs/reference/cli/api-and-sql-commands.md
- Auth and row security: https://sapporta.com/docs/reference/server/auth-and-row-security.md
## Auth And Row Scope
`sapporta sql query` and `sapporta sql execute` run SQL against the app
database through the app API. They do not go through generated table handlers,
`scopedRows()`, route-edge ability/data authority helpers, table save hooks, or
row-security predicates.
- Prefer report routes, table endpoints, row commands, or custom endpoints for
user-facing reads and writes.
- Treat raw SQL results as database-admin inspection, not workspace-user route
visibility.
- Do not use raw SQL to compensate for missing auth filters in product code.
- Never accept client-provided `workspace_id`, `workspaceId`,
`scoped_to_user_id`, or `scopedToUserId` and pass them through raw SQL.
- For emergency writes to scoped tables, document why no scoped API fits and
verify the target rows belong to the intended workspace/user before executing.
## Command Use
Use `pnpm exec sapporta sql query "<sql>"` for quick read-only inspection. Use
`--params '<json-array>'` for bound parameters and `--limit <number>` for row
limits.
`to_tz_date(column, :zone)` is available here, because the command runs on the
app's own connection; a bare `sqlite3` shell reports `unknown function`. Never
`CREATE INDEX` over it — the framework refuses, and accepting it would leave the
database file writable only by a process that registered the same function.
For risky maintenance SQL, use
`pnpm exec sapporta sql execute "<sql>" --dry-run` first. Treat the command as
a one-statement escape hatch. Do not use it for manual transaction scripts;
write app code or use a supported row/master-detail command.
Raw SQL writes bypass validation, save hooks, default handling, trusted
ownership stamping, and scoped row helpers. Use them only when the user
explicitly requested maintenance/debug SQL and safer surfaces do not fit.
references/data-console/table-queries.md
# Table Querying
`GET /api/tables/<name>` is the built-in list endpoint for every table. Before
constructing a raw generated-table HTTP request, inspect the mounted operation:
```bash
pnpm exec sapporta endpoints show "GET /api/tables/tasks"
pnpm exec sapporta endpoints show "GET /api/tables/{tableName}/_count"
pnpm exec sapporta endpoints show "PUT /api/tables/tasks/{id}"
```
Use the intended method and mounted path. The result verifies the route,
request shape, auth boundary, and declared responses. Generated row updates use
`PUT /api/tables/<name>/<id>`, not `PATCH`.
Use the docs for the exact route, query grammar, operators, response envelope,
semantic values, and error codes:
- Table endpoints: https://sapporta.com/docs/reference/http/table-endpoints.md
- Generated table APIs: https://sapporta.com/docs/guides/generated-surfaces/generated-table-apis.md
- Count visible rows: https://sapporta.com/docs/guides/generated-surfaces/count-visible-rows.md
- Query syntax: https://sapporta.com/docs/reference/http/query-syntax.md
- CLI workflow: https://sapporta.com/docs/guides/discovery/use-the-sapporta-cli.md
- Table, row, and report commands: https://sapporta.com/docs/reference/cli/table-row-and-report-commands.md
- Search table rows and relationships: https://sapporta.com/docs/guides/model-data/search-indexes-and-display-metadata.md
- Contract helpers and wire types: https://sapporta.com/docs/reference/contracts/contract-helpers-and-wire-types.md
- Frontend table query options: https://sapporta.com/docs/reference/frontend/table-query-options.md
- Semantic value boundaries: https://sapporta.com/docs/reference/schema/semantic-value-boundaries.md
- Agent data console: https://sapporta.com/docs/guides/discovery/use-the-agent-data-console.md
## Agent Rules
- Prefer `rows list` and `rows count` for CLI table reads. Read their installed
`--help` plus the CLI workflow before composing JSON `--where`; do not invent
bracket-style CLI flags.
- Every filter must name an operator: `filter[col][op]=value`.
- Preserve repeated identical HTTP filter keys. They are ordered AND
conditions, not indexed keys or an `in` list. JSON `--where` cannot express
duplicate identical object keys; use direct HTTP or the documented generated
frontend client when that query shape is required.
- The HTTP wire grammar is string-based. When frontend table code already has
typed filter values, use `TypedFilterCondition` and `encodeTypedFilters()` at
the URL/API boundary instead of downcasting state to raw strings early.
- In frontend code, use the documented table selection/page serializers and
preserve `QueryParamRecord`; a plain `Record<string, string>` collapses
repeated filters.
- When decoding URL filters inside table-aware frontend code, parse them
against table metadata with `parseFiltersForTable()` so numbers, dates,
timestamps, booleans, and lookup ids keep their table types.
- Use the query-syntax reference for the current defaults, bounds, supported
operators, and stable failure codes. Treat a rejected query as a caller bug.
- A date-shaped value on a `timestamp` column is a string boundary, not a day:
every instant stored on the 24th begins `2026-08-24T`, which sorts after
`2026-08-24`. Ask a day-shaped question with the pair of instants the day
occupies — `gte` its first, `lt` the first instant of the next day, resolved
in the workspace's zone. A `date` column compares calendar days directly.
Read [../app-building/days-and-time-zones.md](../app-building/days-and-time-zones.md).
- Do not catch-and-retry by dropping the filter or operator; that can read or
export a much larger result set.
- Built-in table routes apply row-access predicates.
Do not add raw workspace filters to compensate; use endpoint filters for
product-level criteria only.
- Search with `q=<term>` unless the table declares `search: false`. Search
defaults to the current row's visible application columns; an explicitly
configured relational plan may also match a parent through child rows.
- Always read the documented response envelope; do not assume the response is a
bare array.
- Generated table bodies carry JSON primitives. Preserve select-backed text as
strings, numbers and booleans as their JSON primitives, foreign keys as the
target primary-key type, and dates/timestamps as canonical strings. Parse
domain values only at the documented application boundary.
- URL-encode brackets if the client does not accept raw `[` and `]`.
- Use `rows count` for a filtered total or bounded one-column grouping. If the
question needs joins, derived measures, or reusable business semantics, use a
scoped report or app-owned read endpoint rather than SQL.
## Lookup Boundary
Do not expect the old `{ "data": { "123": "Acme" } }` map shape. Read the query
syntax reference before composing lookup parameters: selected-ID recovery and
search are disjoint modes and must not be mixed. Lookup values are strings or
numbers, matching the target table primary-key type. Preserve that type through
lookup caches, pickers, grid editors, and filters; only serialize values at the
HTTP boundary.
Frontend table-backed selectors should normally use `LookupPicker` and
`useTableLookup` from `@sapporta/frontend/lookup`. Specialized direct Base UI
consumers must also preserve the lookup value type.
For protected or non-local apps, read
[server-access.md](server-access.md)
first.
references/framework-source-lookup.md
# Read A Framework Symbol's Signature Or Behavior
Goal: learn what a `@sapporta/*` symbol is, where to import it from, and — only
when a signature cannot settle it — how it behaves.
## Start With The API Reference
Fetch the index and follow it. It carries every published symbol's exact
declaration, generated from the shipped declaration files:
```bash
curl -sL https://sapporta.com/api-reference/index.md
```
When you know a symbol's name but not its import path, fetch the symbol index
and search it:
```bash
curl -sL https://sapporta.com/api-reference/symbols.md
```
Import from the narrowest specifier the symbol index lists. A root barrel
re-exports its own subpaths, so `Badge` resolves from both `@sapporta/ui/badge`
and `@sapporta/ui`; use the narrower one.
Confirm the installed version matches the version the page states before relying
on a signature:
```bash
node -p "require('@sapporta/server/package.json').version"
```
If they differ, trust the installed package and fall back to the declaration
lookup below.
## Read Declarations Only For What A Signature Cannot Express
Drop to declarations when you need runtime behavior, defaults, or control flow —
not to discover that a symbol exists. Resolve from the workspace package that
declares the dependency:
```bash
PKG=$(dirname "$(node -p "require.resolve('@sapporta/server/package.json', { paths: ['packages/api'] })")")
rg -n "<symbol>" "$PKG/dist" --glob '*.d.ts'
```
| Package | Resolve from |
| ------------------------------------------------------ | ------------------- |
| `@sapporta/server`, `@sapporta/honest` | `packages/api` |
| `@sapporta/frontend`, `@sapporta/ui`, `@sapporta/grid` | `packages/frontend` |
| `@sapporta/shared`, `@sapporta/rest-core` | either |
## Rules
- Keep `--glob '*.d.ts'`. `dist/` also holds source maps with the whole source
inlined.
- On `ERR_PACKAGE_PATH_NOT_EXPORTED`, resolve the bare name and cut at `dist/`.
- Never hand-write or glob a `node_modules` path. A generated project has no
root `node_modules/@sapporta`, and `.pnpm/` directory names embed a
peer-version hash that changes on reinstall.
- A file path is not an import specifier. `@sapporta/ui/badge` is published from
`dist/ui/primitives/badge.d.ts`; importing the file path fails. Take
specifiers from the API reference or the package's `exports` map, never from
where a declaration happens to live.
Package layout and dependency ownership:
https://sapporta.com/docs/reference/project/project-files.md
references/operations/troubleshooting.md
# Sapporta Troubleshooting
Use public troubleshooting docs for known fixes:
- Troubleshooting guide: https://sapporta.com/docs/guides/operations/troubleshooting.md
- Diagnostics reference: https://sapporta.com/docs/reference/operations/error-catalogue-and-diagnostics.md
## Native SQLite Binding Triage
Trigger: startup, tests, or CLI fail with missing native addon output such as
`Could not locate the bindings file` for `better-sqlite3`.
Concise recovery direction:
1. Rebuild `better-sqlite3` in the app package that installed it, usually the
generated API package or project root.
2. If rebuild fails with missing macOS build tools, install the command line
tools.
3. Avoid hard-coding pnpm store paths; installed locations vary by package
manager layout and version.
4. If the docs do not cover the observed failure, inspect the actual error
output before changing Node, package manager, or dependency versions.
Typical causes include a Node version change, wiped package `node_modules`, or
a stale package-manager store entry.
SKILL.md
---
name: sapporta
description: >
Use when the user wants to create a Sapporta application or work inside an
existing one: define or change tables, reports, report links, backend
endpoints, domain workflows, React views, forms, auth or row ownership,
application records, report execution, or Sapporta CLI operations.
---
# Sapporta Agent Dispatch
Sapporta is the reusable framework and library; a Sapporta application is a
downstream product project that depends on it and contains its own schema,
workflows, UI, and data. This skill can scaffold that project or extend an
existing one.
This skill provides operating instructions. Use public docs for product
explanations, API shapes, CLI grammar, and reference details:
- Docs: https://sapporta.com/docs.md
- Agent workflow overview: https://sapporta.com/docs/guides/discovery/develop-with-a-coding-agent.md
- API/tool choice guide: https://sapporta.com/docs/guides/discovery/choose-an-application-interface.md
- Reference index: https://sapporta.com/docs/reference.md
- API reference — every published symbol, its declaration, and the specifier to
import it from: https://sapporta.com/api-reference/index.md
## Fetch Docs With `curl`, Not A Summarizer
Every `sapporta.com` URL here is raw markdown, and its code recipes must arrive
verbatim:
```bash
curl -sL https://sapporta.com/docs/[path].md
```
If a doc seems to be missing the code you came for, re-fetch it with `curl`
before concluding it is absent.
Prefer the project-local CLI after the project exists:
```bash
pnpm exec sapporta ...
```
## Choose The Right Mode
Decide whether the user wants to change app code or operate on existing data.
- Create a new Sapporta application -> read
[app-building/project/create.md](references/app-building/project/create.md),
then continue through the app-building guide inside the generated project.
- Build or change app behavior -> read
[app-building/guide.md](references/app-building/guide.md). Every new
application and behavioral change uses the proportional product-model and
coherent-slice gate in
[app-building/product-slice.md](references/app-building/product-slice.md)
before implementation-specific references.
- Inspect, query, insert, update, validate, or answer questions from records
already in the app -> read [data-console/guide.md](references/data-console/guide.md).
- Native module binding failures, `better-sqlite3`, install/dev-server errors
-> read [operations/troubleshooting.md](references/operations/troubleshooting.md).
Some tasks touch both modes: build a report route and screen with the
`app-building` guide, then call the report endpoint and inspect the numbers
with the `data-console` guide.
## Rules That Prevent Wrong Work
- For a new application, resolve an explicit parent directory and absent target
name before running the versioned scaffold command in
[app-building/project/create.md](references/app-building/project/create.md).
The scaffold command creates the target, installs dependencies, applies the
initial auth migration, and creates the initial Git commit.
- For an existing application, work in the project rooted at `cwd` or the
nearest `sapporta.json`.
- Framework table/meta APIs and app-owned API routes are served under `/api`.
Contract paths should not repeat the `/api` prefix. Health checks and
frontend/static routes may be outside `/api` when the app deliberately mounts
them there.
- For app-development work, inspect local contracts, route files, schema,
migrations, and local database state as needed; do not block on an agent token
unless the task is explicitly API-backed data work.
- To learn what a framework symbol is, or which specifier exports it, fetch the
API reference at https://sapporta.com/api-reference/index.md — it carries every
published symbol's exact declaration and the specifier to import it from. Read
framework declarations only for behavior a signature cannot express, following
[framework-source-lookup.md](references/framework-source-lookup.md). Never
hand-write or glob a `node_modules` path.
- To discover how to define application tables, follow
[app-building/tables/create.md](references/app-building/tables/create.md). It routes to the canonical
worked schema example and exact references.
- Keep the default table search behavior unless the product requires a narrower
or relationship-aware discovery surface. When users should find a parent row
by text stored in related child rows, treat that as relational search and
follow the search guide routed from
[app-building/tables/create.md](references/app-building/tables/create.md).
- When `packages/api/schema/` contains only project-authentication tables, treat
the project as a fresh app. Read the complete starter schema through
[app-building/tables/create.md](references/app-building/tables/create.md) before inspecting framework
source, internal fixtures, or generated declarations.
- For protected endpoint discovery, live records, or runtime read-back, follow
[data-console/server-access.md](references/data-console/server-access.md).
Prefer the app's setup-prompt and private environment workflow over putting a
token in command arguments. Repository-only app development does not require
a bearer token.
- The CLI can inspect mounted routes with `endpoints list` and `endpoints show`,
and can invoke app endpoints with
`api get/post/put/delete`. Use `curl` or a typed client when that is more
convenient for the route.
- Before constructing a raw generated-table HTTP request, read
[data-console/table-queries.md](references/data-console/table-queries.md) and inspect the intended
method and path with `endpoints show`. Generated row updates use `PUT`, not
`PATCH`.
- Apply auth scope on the server. Built-in endpoints apply row visibility;
custom code must choose route-edge ability/data authority and use scoped row
helpers.
- A day is a calendar day in the active workspace's time zone. Never call
`Temporal.Now.timeZoneId()` or argless `Temporal.Now.plainDateISO()`; read the
zone with `workspaceTimeZone(auth)` on the server and `appTimeZone()` in a
screen. Read
[app-building/days-and-time-zones.md](references/app-building/days-and-time-zones.md)
before writing a report bucket, a date-range filter, or any day arithmetic.
- Use the public row-scoped data-helper reference before choosing
`scopedRows()`, a per-table Drizzle guard, a scan, or raw access. Do not infer
method names or HTTP parsing behavior from older examples.
- Raw SQL is a fallback. In app code, contain it in store/db modules with a
justification. In data-console work, treat it as admin/debug inspection unless
the user explicitly asked for maintenance SQL.
Reference docs:
- CLI: https://sapporta.com/docs/reference/cli/overview-and-global-options.md
- Agent data console: https://sapporta.com/docs/guides/discovery/use-the-agent-data-console.md
- OpenAPI discovery: https://sapporta.com/docs/guides/discovery/openapi-and-endpoint-discovery.md
- Auth and row security: https://sapporta.com/docs/reference/server/auth-and-row-security.md
- Row-scoped data helpers: https://sapporta.com/docs/reference/server/row-scoped-data-helpers.md
- Days and time zones: https://sapporta.com/docs/reference/server/days-and-time-zones.md
## Direct Dispatch
For app-building work, use `app-building/guide.md` as the mode entrypoint.
Establish the project context, then read `app-building/product-slice.md` before
the matching narrow reference below. Endpoint work starts with
`backend/endpoints.md`; add `backend/domain-code.md` for service organization
and make `backend/typed-errors.md` the mandatory next read when a declared
failure can originate below the route adapter.
Before direct app-building dispatch, choose the execution model in the
application-building guide. Run a focused vertical slice in the current
context. Use staged orchestration for a program of work whose workstreams,
discovery, dependencies, or delivery risk require bounded contexts.
The narrow branch translates an accepted product model or model delta into
Sapporta code. An artifact-named request such as “add a table” or “build a
form” does not skip project inspection or the proportional common gate.
### App-Building Tasks
- Product model or model delta, outcome acceptance, slice boundary, application
grammar, and proportional handling of new apps, new features, or fine-grained
behavior -> read
[app-building/product-slice.md](references/app-building/product-slice.md)
- Main sidebar, protected home page, Advanced/Admin page, framework navigation,
or placement of a feature that changes the normal user path -> read
[app-building/workflow-shell.md](references/app-building/workflow-shell.md)
- New Sapporta project, generated workspace, or first connected application
surface -> read
[app-building/project/create.md](references/app-building/project/create.md)
- Tables, columns, relations, indexes, search config, parent search through child
rows, generated schema metadata, custom table validation, semantic table
values, and table/row navigation links (FK drill-up, related-data drill-into,
cross-report links in table metadata), including the first application tables
-> read
[app-building/tables/create.md](references/app-building/tables/create.md)
- Demo, sample, example, or fixture data for a development database, seed
scripts, `pnpm seed`, or a first account to sign in as -> read
[app-building/seed-demo-data.md](references/app-building/seed-demo-data.md)
- Days, calendars, time zones, day-bucketed reports, date-range filters,
relative periods, or turning a stored timestamp into a day -> read
[app-building/days-and-time-zones.md](references/app-building/days-and-time-zones.md)
- Route-based reports, summaries, ledgers, route/result validation -> read
[app-building/reports/create.md](references/app-building/reports/create.md)
- Cell links, drill-through, cross-report navigation, declarative
`GridDataset` links, link resolvers -> read
[app-building/reports/linking.md](references/app-building/reports/linking.md)
- Hono sub-apps, `TsRestApi`, ts-rest contracts, route handlers, uploads,
transactions, scoped table reads, counts, scans, atomic parent-detail or
line-item writes, OpenAPI registration
-> read [app-building/backend/endpoints.md](references/app-building/backend/endpoints.md), then follow its
conditional routes to the vendored parent-detail recipe, service
organization, or typed errors
- Custom create or edit forms, domain mutation pages, form dialogs or drawers,
and record-entry workflows -> read
[app-building/frontend/forms.md](references/app-building/frontend/forms.md)
first. It routes to the canonical form, validation, and table-query docs;
follow its conditional routes to picker or Grid specialization.
- Typing a table row in frontend code, decoding a generated table response, or a
frontend `$inferSelect` import -> read
[app-building/frontend/row-projections.md](references/app-building/frontend/row-projections.md)
- Custom React routes, dashboards, table/grid views, master-detail screens,
side panels, row selection, custom cells, or typed API clients -> read
[app-building/frontend/views.md](references/app-building/frontend/views.md), then
[app-building/frontend/grids.md](references/app-building/frontend/grids.md)
for Grid work. The public TGrid and Grid interaction references own exact
active-row, activation, and selection behavior.
- Domain services, module organization, testable TypeScript workflow code
outside an endpoint change ->
read [app-building/backend/domain-code.md](references/app-building/backend/domain-code.md)
- A contract declares an expected non-2xx outcome that a service or store can
raise below the route adapter -> read
[app-building/backend/typed-errors.md](references/app-building/backend/typed-errors.md) before implementing the handler
### Existing Data Tasks
- Protected or remote app access, setup-prompt handling, auth failures, or
direct authenticated HTTP -> read
[data-console/server-access.md](references/data-console/server-access.md)
- Insert rows into a running app, built-in row commands -> read
[data-console/row-writes.md](references/data-console/row-writes.md)
- Atomic parent-child data entry, detail rows, line items -> read
[data-console/master-detail-writes.md](references/data-console/master-detail-writes.md)
- Call report routes, inspect report output, answer data questions -> read
[data-console/report-runs.md](references/data-console/report-runs.md)
- Compose `/api/tables/<name>` URLs, CLI `--where`, filters, repeated query
keys, search, sort, pagination, lookup, count, or export -> read
[data-console/table-queries.md](references/data-console/table-queries.md)
- Raw SQL fallback when no endpoint, CRUD, or report route fits -> read
[data-console/sql-fallback.md](references/data-console/sql-fallback.md)