CRUD helpers

Most of a plain list / get / create / update / delete handler is the same five lines every time — and getting those lines subtly wrong is how data leaks. The crud.* helpers from @voltro/runtime give you the executor with the secure defaults baked in; you still write the descriptor (schemas + guards), which is where the browser-safe wire contract and the authorization live.

// accounts.list.query.server.ts
import { crud } from '@voltro/runtime'

export default crud.list('accounts', { redact: ['apiSecret'] })

// accounts.list.query.ts — the descriptor stays hand-written + browser-safe
import { defineQuery } from '@voltro/protocol'
import { Schema } from 'effect'

export default defineQuery({
  name: 'accounts.list',
  input: Schema.Struct({}),
  // note: the wire output OMITS apiSecret, so it never reaches the client
  output: Schema.Array(Schema.Struct({ id: Schema.String, name: Schema.String })),
})

#What the defaults bake in

• Tenant scope. crud.list and crud.getById read through ctx.store, which auto-scopes a tenant() table. They never call .unscoped(), so a cross-tenant read is impossible through them — payslips.list cannot return another tenant's rows.
• Redaction. A column a generated read must never ship — a credential, a token hash, a salary — is stripped from every returned row (reads and the row a create / update echoes). Two sources: a column marked .serverOnly() is stripped automatically (declare the exposure policy once at the schema and every crud read respects it — the single-source form), plus the per-call redact: [...] option for anything not worth a schema marker. Declare the same omission in the descriptor's output schema so the column never reaches the client at all; the helper is the runtime guarantee that it doesn't, whatever the schema says.
• getById returns null, never throws. A reactive getter that throws takes its shared-WebSocket siblings down with it. crud.getById resolves null for an absent row.

#The helpers

Helper	Executor it returns
crud.list(table, { redact? })	tenant-scoped list of every row, redacted
crud.getById(table, { redact? })	one row by input.id, or null — redacted
crud.create(table, { redact? })	insert input; id/tenant/audit auto-stamped; echoes the redacted row
crud.update(table, { redact? })	patch { id, ...patch }; returns the updated row or null
crud.remove(table)	delete input.id; returns { deleted }
crud.count(table, { filter? })	COUNT(*) of the filtered, tenant-scoped set — the total for page-based UIs

redactColumns(rows, cols) is exported standalone for a hand-written handler that isn't plain CRUD but still needs to redact declaratively.

#crud.list read ergonomics — filter, sort, paginate, include

A generated list isn't limited to "all rows". crud.list takes the ergonomics every real list view needs — all optional and additive on top of redact:

export default crud.list('absenceRequests', {
  filter:   (input) => ({ employeeId: input.employeeId, status: input.status }), // → WHERE
  paginate: true,                                        // ?page=3&pageSize=20  (or ?limit=20&offset=40)
  sort:     [{ column: 'createdAt', direction: 'desc' }], // multi-column
  include:  { employee: { with: { team: true } } },      // eager relations, nested filter/sort
  redact:   ['internalNote'],
})

• filter maps the request input to a WHERE — return a column→value map; an undefined field is ignored (an absent filter param is a no-op). Applied through the tenant-scoped .where.
• paginate: true accepts BOTH paging styles, so a caller uses whichever its UI thinks in: page (1-based) + pageSize (default 100), or limit / offset (defaults 100 / 0). page wins when both are sent, and a page below 1 clamps to the first page rather than producing a negative offset. For the total a page-based UI needs ("page 3 of 12"), pair it with crud.count — see below.
• maxPageSize caps how many rows ONE request may ask for (default 1000). The page size is caller-controlled, so without a cap ?limit=1000000 is a one-request read of the whole table — and with publicApi that caller is anyone who can reach the URL. Values above the cap are clamped, not rejected; raise it deliberately for an export-style endpoint.
• sort is a multi-column orderBy, applied in order.
• include is the SAME spec .with(...) takes, so nested relations and per-branch where / orderBy / limit (nested filtering and sort) all work. getById takes include too.

Pass crud.count the same filter as the list (share the option object) so the total and the pages can't disagree about which rows they mean. It ignores paging fields on the input — it counts the whole filtered set, not the current page:

// the total, for rendering "page 3 of 12" — SAME filter as the list
export default crud.count('absenceRequests', {
  filter: (input) => ({ employeeId: input.employeeId, status: input.status }),
})

Declare the filter / pagination fields in the descriptor's input schema so the client can pass them; the executor reads them off input.

You don't need a toView projection layer — the output schema already shapes the wire result. crud.list returns full rows, and on encode Effect strips every column the output schema doesn't declare (a tight Schema.Struct({ id, name }) ships only id + name, whatever else the row holds), and a timestampMs field normalizes a Date to epoch ms. So select fields by naming them in output, and normalize dates with the wire field schemas — no per-table view function. (Renames are expressible via a Schema transform if you need them.)

#columns — don't even READ what you drop

The output schema stops a column reaching the client; columns stops it being read at all. Use it when a table carries something wide that a list view never shows — a long text body, a big json() blob:

export default crud.list('articles', { columns: ['id', 'title', 'createdAt'] })
// the large `body` is never SELECTed, transferred from the DB, or decoded

.serverOnly() columns are removed from the projection automatically — they're stripped from the response anyway, so reading them is pure waste.

Trap: an eager include branch joins on a foreign key, so a projection that omits that FK breaks the relation. Keep the FK in columns when you also pass include.

#scope — keep per-subject narrowing when you adopt the helper

filter builds the WHERE from the request; scope builds it from the caller:

crud.list('timeEntries', {
  filter: (input) => ({ status: input.status }),      // what the caller ASKED for
  scope:  (ctx)   => ({ ownerId: ctx.request.subject.id }),  // what it MAY SEE
})

A WHERE built only from input can express the rows the caller asked for, never the rows the caller may see.

scope is merged last, so a request field of the same name can never widen it — ?ownerId=someone-else is simply overridden. That ordering is why the two are separate options rather than one: only one of them is a security boundary, and kept apart, "does this list declare a scope?" is a question a reviewer — or a future boot audit — can actually ask. Folded into filter, it becomes "does this filter happen to read ctx somewhere in its body?", which nothing can check. Tenant scope still applies automatically; anything narrower — owner, team, role — does not. So replacing a hand-written handler that carried such a narrowing with crud.list widens the result set, silently and without an error. One app lost exactly that across eight list views.

Use the same filter for crud.count, or the total contradicts the pages — "showing 10 of 4000" on a page holding ten rows.

The reason this is an option rather than a reason to leave: hand-writing the query to get the narrowing also forfeits serverOnly stripping and the page-size clamp. A narrowing requirement should not cost you the safety rails.

#What they deliberately don't do — authorization

A guard runs before the executor, so gating lives on the descriptor, not the handler — an executor cannot gate itself. Keep every write descriptor guarded:

export default defineMutation({
  name: 'accounts.create',
  input: AccountInput,
  output: Account,
  guards: [requireScope('accounts:write')],   // ← the gate; crud.create does not add one
})

#Scope — why the schema is still hand-written

These helpers give you the secure handler, not schema derivation. Deriving the descriptor's input/output from the table automatically would need the table VALUE inside the descriptor file — and a descriptor is loaded value-level by the browser client, so importing a table there drags the store and driver into the browser bundle (the boundary guard aborts the boot; rowSchema is server-only for exactly this reason). Full schema-derivation, and a boot audit that fails when a tenant() table's list reads unscoped or a write goes ungated, are a separate planned pass — the handler defaults above are the part that ships browser-safe today and closes the leak class.