# SQL patterns

Each section is a business need, the manifest that meets it, and what the database refuses. Every block here is
generated by the docs check, and `packages/mantle/tests/sql-patterns.test.ts` runs the same manifest on bun:sqlite,
local D1 and PostgreSQL through the generated REST boundary. The SQL is the SQLite and D1 spelling; where PostgreSQL
differs, the section says so. What each dialect accepts is listed in [the SQL reference](../reference/sql.md).

For an order with any number of lines, see [several rows that commit together](typed-queries.md#several-rows-that-commit-together).

The sections share two Schemas:

```yaml
apiVersion: cms.mantle.aotter.net/v2
kind: Schema
metadata: { name: products }
spec:
  title: Products
  lifecycle: operational
  uniqueIndexes: [[sku]]
  checks: ["reserved <= stock"]
  schema:
    type: object
    required: [sku, name, price, stock, reserved, active]
    properties:
      sku: { type: string }
      name: { type: string, minLength: 1 }
      note: { type: string }
      price: { type: integer, minimum: 0 }
      stock: { type: integer, minimum: 0 }
      reserved: { type: integer, minimum: 0 }
      active: { type: boolean }
---
apiVersion: cms.mantle.aotter.net/v2
kind: Schema
metadata: { name: orders }
spec:
  title: Orders
  lifecycle: operational
  indexes: [[productId, createdAt, id]]
  schema:
    type: object
    required: [productId, qty, total, orderStatus]
    properties:
      productId: { type: string, x-mantle-ref: products }
      qty: { type: integer, minimum: 1 }
      total: { type: integer, minimum: 0 }
      orderStatus: { type: string, enum: [reserved, pending_approval] }
```

## Choose a status while writing: CASE

An order over 5000 waits for a manager; a smaller one reserves stock at once. `CASE` picks the status inside the
INSERT, and the reservation runs only for a small order, in the same program, so the two never disagree. The total
comes from the catalogue's `price`, never from the caller: a price in the input would let a caller choose the total,
and with it whether the order needs approval.

```yaml
apiVersion: cms.mantle.aotter.net/v2
kind: Procedure
metadata: { name: place-order }
spec:
  description: Order a product. An order over 5000 waits for approval and reserves nothing yet.
  requires: { auth: { all: [ctx.user] } }
  input:
    type: object
    additionalProperties: false
    required: [productId, qty]
    properties:
      productId: { type: string }
      qty: { type: integer, minimum: 1 }
  output: { type: object }
  handler:
    sql: |
      INSERT INTO orders (productId, qty, total, orderStatus)
        SELECT p.id, input.qty, input.qty * p.price,
          CASE WHEN input.qty * p.price > 5000 THEN 'pending_approval' ELSE 'reserved' END
        FROM products p WHERE p.id = input.productId AND p.active
        RETURNING id, orderStatus AS "orderStatus";
      UPDATE products SET reserved = reserved + input.qty
        WHERE id IN (SELECT p.id FROM products p WHERE p.id = input.productId AND p.active AND input.qty * p.price <= 5000)
```

The second statement is written as `WHERE id IN (SELECT …)` on purpose. `WHERE id = input.productId AND …` is a
row-classified UPDATE, which must change exactly one row: for an order that waits, it would change none and refuse the
whole call with `CONFLICT`. A write that should do nothing when its condition is false needs the set form. The first
statement's join on `products` with `p.active` means an unknown or discontinued product writes no order.

## Approve before reserving

Approval changes the status and reserves the stock in one program. The first statement is the guard: a stale
`expectedVersion` or an order that is no longer `pending_approval` matches no row, which is `CONFLICT`. The second
statement fails `reserved <= stock` when the stock is gone by now, and that rolls back the status change too, so the
order stays pending. [Purchasing](https://github.com/aotter/mantle/blob/3c107cbcc7b513105094870e239c17bd28f43e97/docs/examples/purchasing/README.md) does the same with a department budget
(`approve-request-write`), and approves small requests at submission with `CASE` (`submit-request`).

```yaml
apiVersion: cms.mantle.aotter.net/v2
kind: Procedure
metadata: { name: approve-order }
spec:
  description: Approve a pending order at the version you reviewed; reserves its stock.
  requires: { auth: { all: [ctx.admin] } }
  input:
    type: object
    additionalProperties: false
    required: [id, expectedVersion]
    properties:
      id: { type: string }
      expectedVersion: { type: integer }
  output: { type: object }
  handler:
    sql: |
      UPDATE orders SET orderStatus = 'reserved'
        WHERE id = input.id AND version = input.expectedVersion AND orderStatus = 'pending_approval'
        RETURNING id, version;
      UPDATE products SET reserved = reserved + (SELECT o.qty FROM orders o WHERE o.id = input.id)
        WHERE id = (SELECT o.productId FROM orders o WHERE o.id = input.id)
```

## Change only the fields given: COALESCE

A partial update keeps every field the caller left out: `COALESCE(input.name, name)` is the new name when one is given
and the current one otherwise. `NULLIF(input.note, '')` also treats an empty string as left out. Because of that, this
Procedure cannot clear a note; give clearing its own input (for example `clearNote: true` with
`CASE WHEN input.clearNote THEN NULL ELSE … END`) if the app needs it. The version guard makes a stale edit a 409.

```yaml
apiVersion: cms.mantle.aotter.net/v2
kind: Procedure
metadata: { name: update-product }
spec:
  description: Change a product's name or note; a field you leave out keeps its value.
  requires: { auth: { all: [ctx.admin] } }
  input:
    type: object
    additionalProperties: false
    required: [id, expectedVersion]
    properties:
      id: { type: string }
      expectedVersion: { type: integer }
      name: { type: string, minLength: 1 }
      note: { type: string }
  output: { type: object }
  handler:
    sql: |
      UPDATE products SET name = COALESCE(input.name, name), note = COALESCE(NULLIF(input.note, ''), note)
        WHERE id = input.id AND version = input.expectedVersion
        RETURNING id, version, name, note
```

## Create or add: ON CONFLICT DO UPDATE

Restocking a SKU creates the product the first time and adds to its stock afterwards, in one statement. The conflict
target must be a declared unique index (`uniqueIndexes: [[sku]]`). `excluded` is the row the INSERT proposed;
qualify the existing row with the table name (`products.stock`).

```yaml
apiVersion: cms.mantle.aotter.net/v2
kind: Procedure
metadata: { name: restock }
spec:
  description: Add stock by SKU, creating the product the first time.
  requires: { auth: { all: [ctx.admin] } }
  input:
    type: object
    additionalProperties: false
    required: [sku, name, price, qty]
    properties:
      sku: { type: string }
      name: { type: string, minLength: 1 }
      price: { type: integer, minimum: 0 }
      qty: { type: integer, minimum: 1 }
  output: { type: object }
  handler:
    sql: |
      INSERT INTO products (sku, name, price, stock, reserved, active) VALUES (input.sku, input.name, input.price, input.qty, 0, true)
        ON CONFLICT (sku) DO UPDATE SET stock = products.stock + excluded.stock
        RETURNING id, stock
```

## Refuse while something refers to it: NOT EXISTS

A product cannot be discontinued while an order for it waits for approval, since approving that order would reserve
stock of a product no longer sold. `NOT EXISTS (…)` is part of the guard, so while such an order exists the
row-classified UPDATE changes nothing and the call is `CONFLICT`; the product stays on sale. Use `EXISTS` the same way
for "only if a related row exists". [Purchasing](https://github.com/aotter/mantle/blob/3c107cbcc7b513105094870e239c17bd28f43e97/docs/examples/purchasing/README.md) uses `NOT EXISTS` to write the
ledger row only for the request its program just approved (`submit-request`).

```yaml
apiVersion: cms.mantle.aotter.net/v2
kind: Procedure
metadata: { name: discontinue-product }
spec:
  description: Stop selling a product; refused while an order for it waits for approval.
  requires: { auth: { all: [ctx.admin] } }
  input:
    type: object
    additionalProperties: false
    required: [id]
    properties:
      id: { type: string }
  output: { type: object }
  handler:
    sql: |
      UPDATE products SET active = false
        WHERE id = input.id
          AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.productId = products.id AND o.orderStatus = 'pending_approval')
        RETURNING id
```

## A row with its children in one read

An admin, or an AI client, reads each product with its orders in one call instead of one call per product. A
correlated subquery builds the array, so a product without orders has `[]`; `LEFT JOIN … GROUP BY` would give it one
empty object instead.

```yaml
apiVersion: cms.mantle.aotter.net/v2
kind: View
metadata: { name: products-with-orders }
spec:
  description: Each product with its orders as one JSON array.
  surface: admin
  requires: { auth: { all: [ctx.admin] } }
  sql: |
    SELECT p.id, p.name,
      (SELECT json_group_array(json_set('{}', '$.id', o.id, '$.qty', o.qty)) FROM orders o WHERE o.productId = p.id) AS orders
    FROM products p ORDER BY p.id
```

SQLite and D1 build each object with `json_set('{}', '$.id', o.id, …)` (`json_object` is not on the allowlist).
PostgreSQL writes it as `COALESCE(jsonb_agg(jsonb_build_object('id', o.id, 'qty', o.qty)), CAST('[]' AS jsonb))`;
`jsonb_agg` of no rows is `NULL`, hence the `COALESCE`. PostgreSQL can also order inside the aggregate
(`jsonb_agg(… ORDER BY o.id)`); SQLite cannot, so sort the array.

Generation types the column as JSON because its expression can only produce a JSON document: a `json_group_array`,
`json_group_object`, `json_set`, `json_insert`, `json_remove`, `jsonb_agg`, `jsonb_object_agg`, `jsonb_build_object`,
`jsonb_build_array`, `jsonb_strip_nulls`, `to_jsonb` or `CAST(… AS jsonb)` call, a scalar subquery of one, or a
`COALESCE` of those with JSON literal fallbacks such as `'[]'`. REST, MCP (`structuredContent`) and Admin therefore
return an array on every target, and `WireRow` types the column `unknown`. The bare View and the Store keep the
driver's value: on SQLite and D1 that is JSON text, so code calling the bare View parses it. `json_extract`, `->>`
and `CASE` can return a scalar, so their output stays native.

## The latest row of each group: row_number()

The most recent order of each product. `row_number() OVER (PARTITION BY … ORDER BY …)` numbers each product's orders
newest first, and the outer query keeps number 1. End the window's ORDER BY with `id` so two rows created in the same
instant still have one order. `rank()` instead gives tied rows the same number.

```yaml
apiVersion: cms.mantle.aotter.net/v2
kind: View
metadata: { name: latest-order-per-product }
spec:
  description: The most recent order of each product.
  surface: admin
  requires: { auth: { all: [ctx.admin] } }
  sql: |
    SELECT x.productId AS "productId", x.id, x.qty FROM (
      SELECT o.productId, o.id, o.qty, row_number() OVER (PARTITION BY o.productId ORDER BY o.created_at DESC, o.id DESC) AS n
      FROM orders o
    ) x WHERE x.n = 1 ORDER BY x.productId
```