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.

For an order with any number of lines, see 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 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 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

SQL patterns ​

Choose a status while writing: CASE ​

Approve before reserving ​

Change only the fields given: COALESCE ​

Create or add: ON CONFLICT DO UPDATE ​

Refuse while something refers to it: NOT EXISTS ​

A row with its children in one read ​

The latest row of each group: row_number() ​

SQL patterns

Choose a status while writing: CASE

Approve before reserving

Change only the fields given: COALESCE

Create or add: ON CONFLICT DO UPDATE

Refuse while something refers to it: NOT EXISTS

A row with its children in one read

The latest row of each group: row_number()