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.

For reference, this is the shape that does not guarantee removal. The response is a translation of the statement, not a confirmation the row is gone - don't depend on it to delete data.

# 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",
  "pk":     "o_003"
}