12. DELETE via /sql

Inside an open transaction, a primary-key DELETE buffers a row-removal op and the COMMIT applies it durably in one frame. Carry the same session id across all three calls so they share one transaction buffer.

# Supported SQL route: wrap the DELETE in an explicit transaction.
# The same session id must accompany BEGIN, the DELETE, and COMMIT so
# the buffered row-removal is applied atomically on COMMIT.
SID="sess-$(date +%s)"

# 1. open the transaction
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "X-OC-Session-Id: $SID" \
  -H "Content-Type: application/json" \
  -d '{ "sql": "BEGIN" }'

# 2. buffer the delete (matched by primary key)
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "X-OC-Session-Id: $SID" \
  -H "Content-Type: application/json" \
  -d '{ "sql": "DELETE FROM shop.orders WHERE id = '\''o_003'\''" }'

# 3. commit - the buffered row-removal lands durably here
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "X-OC-Session-Id: $SID" \
  -H "Content-Type: application/json" \
  -d '{ "sql": "COMMIT" }'

// the DELETE step, inside the open transaction:
{
  "kind":          "delete",
  "schema":        "shop.orders",
  "pk":            "o_003",
  "rows_buffered": 1
}

// the COMMIT step - the buffered op is now durable:
{
  "kind":          "tx",
  "op":            "commit",
  "ops_committed": 1
}

rows_buffered is 1 when the row existed (and 0 when it didn't). The removal is not visible to other readers until COMMIT returns ops_committed: 1.

When you already hold the primary key and don't need SQL, delete the row through the typed row endpoints. The row delete path carries the same idempotency-key plumbing as the rest of the typed /rows surface.

With no BEGIN around it, the same statement autocommits: the rows are removed in one frame before the response returns, and rows_affected counts them. Foreign keys are enforced on this path too. Note that /sql is the one surface that accepts a bare DELETE FROM t with no WHERE and empties the table - the typed row endpoints refuse that shape.

# A bare DELETE outside a transaction does NOT reliably remove the row.
# The response echoes the translated delete (the pk it resolved) - treat
# it as a translation, not a confirmation that the row is gone.
curl -X POST "https://$OC_HOST/v1/tenants/$OC_TENANT/sql" \
  -H "Authorization: Bearer $OC_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "DELETE FROM shop.orders WHERE id = '\''o_003'\''"
  }'

{
  "kind":   "delete",
  "schema": "shop.orders",
  "rows_affected": 1
}

Add RETURNING order_id, total_cents (or RETURNING *) and the response also carries a rows array holding the deleted rows' values, projected to that column list. A no-match delete answers rows_affected: 0 with an empty rows array rather than an error.