db-function-contract

Files (2)

• SKILL.md
• response-envelope.json

SKILL.md (first lines)

# Database Function Contract Skill

Use this skill when:
- defining model-level PostgreSQL functions
- standardizing JSONB request and response envelopes
- replacing inline backend SQL with function calls
- designing multidataset responses

## Generated CRUD (default path — do not hand-write the five)

Standard `list / get / save / delete / lookup` are produced **deterministically by a
build script**, not written by hand and not written by the agent. The single source
of truth is the model's `<model>.schema.ts` (TypeBox) + `manifest.json`. The generator
(`deno task sql:gen <family>/<model>`) reads the schema and emits
`db/_generated/<model>.crud.gen.sql` — a committed source file, not a build product.

So when adding a model, the agent writes **only**:
- `manifest.json`, `<model>.schema.ts`,
- `db/struc.sql` (DDL),
- UI components,
- and a `db/<model>.custom.sql` **only** if a command needs non-standard logic.

Then run, in this order:

```bash
deno task sql:gen <family>/<model>   # only after the schema changed
deno task sql:registry && deno task sql:assemble && deno task sql:publish
```

**Schema annotations the generator reads** (in `<model>.schema.ts`):
- `x-search: true` — field participates in the `ilike` search (fallback: all string fields).
- `x-list.sortable: true` — field is allowed in `sortBy`.
- `x-lookup: true` — field shown in the picker.
- `x-ref: { model, fk?, display?, as?, sortable?, searchable? }` — reference to another
  model: stores the FK id, returns a nested `{id, <display>}` object in get/list, sorts
  and searches by the target's display column.
- `x-table: { table, parentFk, orderBy? }` — tabular part (master-detail); the array's
  item schema describes the line columns (which may themselves carry `x-ref`).
- `x-db-type` / `x-db-col` — column type cast / column-name override. The type is a bare
  name from a closed list — `bigint`, `int`, `integer`, `numeric`, `json`, `jsonb`, `date`,
  `timestamp`, `timestamptz`, `text`, `varchar`. Precision and length belong to the DDL:
  `numeric(10,2)` here is a generation error, not a cast.

**Override semantics (standard vs custom):** decided by file presence, per function.
The assembler concatenates, in order, `db/_generated/<model>.crud.gen.sql` then
`db/<model>.custom.sql`. Both use `drop function … (argtypes); create function …`, so a
function present in the custom file overrides the generated one; the other four stay
generated. The runtime is unchanged — it still calls `{schema}.{model}_{command}`.

**Documents:** the generator emits `save` as `MERGE` (header upsert + one `MERGE` per
tabular part, with the mandatory `when not matched by source and <parentFk> = v_id`
guard). `post`/`unpost` are emitted as stubs — implement real posting in
`db/<model>.custom.sql`.

**A `not null default` column has to be sent explicitly.** The generated `save` builds
its `MERGE` source from `jsonb_to_recordset`, so a key that is **absent** from the
payload arrives as an explicit `null` — and an explicit `null` does not trigger the
column default:

```
null value in column "rate" of relation "supplier_invoice" violates not-null constraint
```

`not null default 1` in the DDL does not save an API call that omits `rate`. Forms never
hit this — `Value.Create` seeds `$root` from the schema, so every field of the schema is
in the payload; the first call from the side — the agent, `deno task api`, an import —
does. Give the field a `default` in the TypeBox schema (so it is at least declared in one
place both sides read) and treat the DDL default as what it is: a value for rows written
around the API, not a substitute for the key.

Hand-write a SQL function only as an exception (non-standard logic), and put it in
`db/<model>.custom.sql`. Deep dive on the generator (framework repository, not part of
an application): `docs/sql-codegen.md`.

The sections below describe the *contract* every command (generated or custom) must honor.

## Standard command names

Use these names consistently everywhere: SQL functions, backend command routing, frontend API calls, and model config files.

| Command   | Purpose                                              |
|-----------|------------------------------------------------------|
| `list`    | Paginated or filtered list of records                |
| `get`     | Load a single record by id (for form)                |
| `save`    | Create or update a record (upsert semantics)         |
| `delete`  | **Mark** a record for deletion by id (does not destroy it) |
| `undelete`| Lift the deletion mark                                |
| `lookup`  | Search records for picker / autocomplete             |
| `post`    | Post a document (accounting posting)                 |
| `unpost`  | Reverse the posting of a document                    |

Extra commands follow the same naming style: `nextCode`, `nextNumber`, `<verb><Noun>`.

## Core rules

- Standard catalog-style models have a base function contract: list, get, save, delete, lookup.
- Document models keep the same base contract and add `post` and `unpost` when the manifest declares `type: "document"`.
- Every document also gets `postPreview` for free — a **dry run**: the core calls your
  `{model}_post` inside a transaction, returns the entries it produced together with their
  analytics, and rolls everything back. You write nothing for it. What this asks of your
  `_post`: it must not depend on being committed, and it must leave its refusals in the
  envelope (or as `raise exception`) rather than half-writing and returning `ok`. Both are
  already required — the dry run just makes a violation visible.
- Every document also gets `related` — the **tree of related documents** around it
  (`app.document_related`, permission `view`). You write nothing for it either: its edges
  are every `x-ref` of a document to a document (header or tabular section), collected from
  the schemas by `sql:assemble`. What this asks of you: a reference to a document is an
  `x-ref`, not a bare `bigint` column — a bare id with an FK in DDL does not reach the tree.
  A technical reference that is not a relation says so with `"x-ref": { …, related: false }`.
  Register rows that point at their registrar are not edges: that is the document's movements.
  `sql:publish` warns (`⚠ Пов'язані документи`) about both ways this goes wrong — an FK
  from a document to a document that is not an edge, and an edge column with no index
  starting at it (the tree walks every edge both ways). Treat both as work to do: the
  index goes into the model's `db/struc.sql`. On the form the tree is one button in
  `renderAuxActions()` — `<ui-related-documents model=… .documentId=… .routeOf=…>`; `routeOf`
  maps a document type code to the form's route through your view manifest, because the
  framework cannot see it. The agent gets `related` in a document's default command set (read
  only, so a read-only token sees it too); a document with its own `agent.allowCommands`
  must name it.

From IgorAIvanov/altera03, MIT.

More from IgorAIvanov/altera03