Generated table APIs

View as Markdown

Every registered table receives one HTTP surface for ordinary record work. The generated screens, CLI, scripts, and integrations use the same row-safe operations.

Inspect before calling

List, get, create, update, and delete live under /api/tables/<table>. Inspect the mounted operation before composing direct HTTP:

pnpm exec sapporta endpoints show "GET /api/tables/tasks"
pnpm exec sapporta endpoints show "PUT /api/tables/tasks/{id}"

Discovery shows method, path, request, and declared responses; it does not prove the application authorization boundary. Generated updates use PUT, not PATCH, while accepting a patch-shaped subset of writable fields.

For routine data work, use the CLI and resolve IDs from visible results:

pnpm exec sapporta rows list tasks --where '{"status":{"eq":"open"}}'
pnpm exec sapporta rows update tasks "$TASK_ID" --values '{"priority":"high"}'

The equivalent list route is:

GET /api/tables/tasks?filter[status][eq]=open

List responses contain rows and pagination metadata. Single-row reads and writes return { "data": row }.

Send only caller-owned values

Create and update bodies contain API-writable domain values. They omit default-generated primary keys, managed scope fields, apiWritable: false columns, and references with apiSettable: false. Client-assigned primary keys remain writable when the table permits them.

Generated request bodies preserve semantic JSON values. Numbers and booleans stay primitives, foreign keys retain the target key type, and dates and timestamps use canonical strings. Row security merges trusted scope and server-authored values, verifies reference visibility, then the save pipeline parses, canonicalizes, validates, and writes.

Once the caller has action permission, a generated single-row read, update, or delete uses the same 404 ROW_NOT_FOUND for a missing row and a row hidden by scope. Possessing an ID is not authority.

Create a master and its details together

One POST to the master table creates the parent and its dependent rows in a single transaction:

POST /api/tables/invoices

{
  "number": "INV-1042",
  "customer_id": 7,
  "$details": {
    "table": "invoice_lines",
    "fk": "invoice_id",
    "rows": [
      { "description": "Design", "amount": 1200 },
      { "description": "Build", "amount": 4800 }
    ]
  }
}

The response is 201 with { "data": { "master": row, "details": row[] } }. The caller needs create permission on both tables, and any failure rolls back every write.

Detail rows omit the foreign key; the server stamps it from the created master. Declare the relationship on both sides — children on the master so the form reaches OpenAPI and generated clients, and a server-owned reference on the child so a caller who sends the key gets 422 VALIDATION_FAILED rather than a silent overwrite:

// invoices
meta: { children: [{ table: "invoice_lines", foreignKey: "invoice_id" }] }

// invoice_lines
meta: { references: { invoice_id: { table: "invoices", apiSettable: false } } }

Two round-trips still apply when the details are not known up front: create the parent, read its key from the 201, then post children. Do not assume a fresh database starts at ID 1.

Continue with specialized generated operations

Generated lookups and CSV export covers picker search, selected-ID rehydration, and streaming exports.
Count visible rows covers filtered totals and bounded one-column groups without loading rows.
Filtering, sorting, search, and pagination owns the strict list/export query grammar.

Know when CRUD is no longer the operation

Generated routes fit one-table record operations. Completing a task may need to update tasks, append a task_events row, and return one domain result in a transaction. That is one application endpoint, not two client-coordinated CRUD calls.

Use generated routes while the operation means “read or change this table.” Use an application endpoint when it has its own name, transaction, external effect, or response.