Collections

Collections are schema-first, relational tables in your project's own managed Postgres database. These admin routes manage the schema — create a collection, add or drop columns and indexes, and inspect migration history. End-user row reads and writes (with automatic per-user scoping) are covered in the Collections SDK guide.

Schema changes are applied asynchronously through a managed migration; the create / alter / delete routes wait for the result and return the migration version and status.

Paths below are shown relative to the API version base. The canonical external prefix is https://api.amba.dev/v1, so /admin/projects/:projectId/... below means /v1/admin/projects/:projectId/... on the wire.

Endpoints

Collection names must match ^[a-z][a-z0-9_]*$. Reserved prefixes (amba_, coll_amba_) are rejected with 400 RESERVED_NAME.

POST /admin/projects/:projectId/collections

Create a collection. You supply the columns; Amba adds id, user_id, created_at, updated_at, and deleted_at automatically, plus a primary key and a user_id index.

Request (CollectionSchema)

Each column is { name, type, nullable?, references?, dimension? }. type is one of text, integer, bigint, numeric, boolean, timestamptz, date, uuid, jsonb, vector, or a native array type: text[], integer[], bigint[], numeric[], boolean[], uuid[]. vector columns require a dimension (1–4096).

date and timestamptz have deliberately different JSON shapes. A date round-trips as a date-only YYYY-MM-DD string; a timestamptz remains a full ISO-8601 timestamp such as 2026-08-23T14:15:16.789Z.

Array columns hold a flat, uniformly-typed list (tags, tropes, category slugs, related ids) and unlock the contains / containedBy / overlaps query operators. Prefer an array column over jsonb for any flat list you filter on; keep jsonb for nested documents and mixed-shape objects.

Shared (global) collections

By default every row carries a non-null user_id — collections are per-user. For developer-seeded GLOBAL content that no single user owns (question banks, content libraries, lookup tables, reference data), create the collection with "shared": true. That drops the user_id NOT NULL constraint so you can insert rows without a user_id.

Insert into a per-user collection without a user_id (e.g. seeding from a function or the admin API) returns 400 NOT_NULL_VIOLATION. The fix is either "shared": true at create time, or "relax_user_id": true on an existing collection (see PATCH below). The end-user client SDK can never set user_id — the server injects it from the session — so shared/global data must be seeded through the admin API or MCP tools.

Access policies (public catalogs)

By default a collection is owner-scoped: client reads and writes are auto-scoped to the signed-in user's own rows (plus any shared null-owner rows on a shared collection). Two optional policies relax that per collection:

• read_policy: "public" — every signed-in user can read all rows. This is how you build a public catalog (a shared product list, a global leaderboard table, community posts) that everyone reads but only the owner edits.
• write_policy: "authenticated" — any signed-in user can write/update rows, not just the owner.

Policies are independent — a common pattern is read_policy: "public" with the default write_policy: "owner" (everyone reads, each user only edits their own). Omitting both keeps the historical owner/owner behavior. Set or change a policy at create time, or later via set_policy on PATCH (below);

Group-owned collections (teams)

For data owned by a team rather than one user, create the collection with owner_scope: "group" plus a member policy. Rows are owned by a group_id (a group); access is resolved from the caller's membership in that group, gated by role rank (owner > admin > member > viewer):

{
  "name": "team_docs",
  "columns": [{ "name": "title", "type": "text" }],
  "owner_scope": "group",
  "read_policy": "member",
  "write_policy": "member:admin"
}

The collection carries group_id as its owner column (and a nullable user_id for the creator). Clients act within a group via X-Group-Context: <groupId> (or amba.asUser(uid).asGroup(gid) in the Node SDK), verified server-side (403 NOT_A_GROUP_MEMBER if the caller isn't a member). Inserts require a group context (400 GROUP_CONTEXT_REQUIRED) at the write role floor (403 GROUP_ROLE_FORBIDDEN), and the owning group must be private (400 GROUP_NOT_PRIVATE for an open-join group). owner_scope is fixed at create time; set_policy can change the member role floors but not the scope. See team-owned collections for the full guide.

a bare re-create that omits the policy fields never clobbers a policy you set earlier.

Each index is { columns, name?, unique?, using? }, where using is one of btree (default), hash, gin, gist, brin, hnsw, ivfflat.

curl -X POST 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections' \
  -H 'Authorization: Bearer $AMBA_PAT' \
  -H 'Content-Type: application/json' \
  -d '{
    "name": "notes",
    "columns": [
      { "name": "title", "type": "text" },
      { "name": "body", "type": "text", "nullable": true },
      { "name": "pinned", "type": "boolean", "nullable": true },
      { "name": "tags", "type": "text[]", "nullable": true }
    ],
    "indexes": [{ "columns": ["title"] }]
  }'

Response 201

{
  "data": {
    "name": "notes",
    "version": 1,
    "workflow_id": "…",
    "run_id": "…",
    "status": "applied"
  }
}

A DDL error returns 400 with error.code COLLECTION_MIGRATION_FAILED, INVALID_COLLECTION_SCHEMA, or INVALID_IDENTIFIER.

Try it:

curl -X POST 'https://api.amba.dev/v1/admin/projects/%7B%7BprojectId%7D%7D/collections'

GET /admin/projects/:projectId/collections

List collections. Offset/limit pagination — limit defaults to 50 (max 200), offset defaults to 0.

Response 200

{
  "data": [{ "name": "notes", "created_at": "2026-05-27T10:00:00.000Z" }],
  "pagination": { "limit": 50, "offset": 0, "total": 1 }
}

Try it:

curl -X GET 'https://api.amba.dev/v1/admin/projects/%7B%7BprojectId%7D%7D/collections'

GET /admin/projects/:projectId/collections/:name

Describe a collection: its live columns and indexes (read from the database catalog) plus the most recent migration record.

Response 200

{
  "data": {
    "name": "notes",
    "columns": [
      { "column_name": "id", "data_type": "uuid", "is_nullable": "NO", "column_default": "…" },
      { "column_name": "title", "data_type": "text", "is_nullable": "NO", "column_default": null }
    ],
    "indexes": [{ "indexname": "notes_pkey", "indexdef": "CREATE UNIQUE INDEX …" }],
    "latest_migration": { "version": 1, "status": "applied", "applied_at": "…", "error": null }
  }
}

404 NOT_FOUND if the collection doesn't exist.

Try it:

curl -X GET 'https://api.amba.dev/v1/admin/projects/%7B%7BprojectId%7D%7D/collections/%7B%7BcollectionName%7D%7D'

PATCH /admin/projects/:projectId/collections/:name

Alter a collection. Exactly one operation per call: add_column, add_index, change_type, rename_column, drop_column, or relax_user_id. Chain multiple PATCHes for multiple changes.

curl -X PATCH 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/notes' \
  -H 'Authorization: Bearer $AMBA_PAT' \
  -H 'Content-Type: application/json' \
  -d '{ "add_column": { "name": "color", "type": "text", "nullable": true } }'

change_type

Migrates a column to a new type in place — no drop-and-re-add, so existing data is preserved.

# Safe cast (any type → text) — no `using` needed.
curl -X PATCH '…/collections/notes' -H 'Authorization: Bearer $AMBA_PAT' \
  -d '{ "change_type": { "column": "priority", "to_type": "text" } }'
 
# Non-trivial cast — supply a `using` expression.
curl -X PATCH '…/collections/notes' -H 'Authorization: Bearer $AMBA_PAT' \
  -d '{ "change_type": { "column": "score", "to_type": "integer", "using": "score::integer" } }'

Cast safety. Only a subset of type pairs convert automatically. A using cast expression (e.g. "score::integer") is required for any cast that isn't trivially safe (such as text → integer); the request is rejected up front with 400 INVALID_COLLECTION_SCHEMA rather than failing mid-migration. Casts that are always safe don't need using: any type → text, and (when you pass from_type) the widening casts integer → bigint, integer → numeric, and bigint → numeric. If a using cast still fails on real row data at apply time, the call returns 400 COLLECTION_MIGRATION_FAILED and the collection is left unchanged.

rename_column

curl -X PATCH '…/collections/notes' -H 'Authorization: Bearer $AMBA_PAT' \
  -d '{ "rename_column": { "from": "body", "to": "content" } }'

A rename is a fast metadata-only change — no table rewrite. It breaks any app code that still references the old column name, so re-run amba types generate and update your call sites afterward. The new name can't collide with a server-managed column (id, user_id, created_at, updated_at, deleted_at) or a reserved query keyword (and, or, not).

Response 200

{
  "data": { "name": "notes", "version": 2, "workflow_id": "…", "run_id": "…", "status": "applied" }
}

400 INVALID_PATCH if not exactly one operation; 400 CONFIRMATION_REQUIRED if drop_column is set without a matching ?confirm=.

Try it:

curl -X PATCH 'https://api.amba.dev/v1/admin/projects/%7B%7BprojectId%7D%7D/collections/%7B%7BcollectionName%7D%7D'

DELETE /admin/projects/:projectId/collections/:name

Drop a collection. Destructive — requires ?confirm=<name>.

curl -X DELETE 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/notes?confirm=notes' \
  -H 'Authorization: Bearer $AMBA_PAT'

{ "data": { "name": "notes", "deleted": true, "version": 3 } }

Try it:

curl -X DELETE 'https://api.amba.dev/v1/admin/projects/%7B%7BprojectId%7D%7D/collections/%7B%7BcollectionName%7D%7D?confirm=%7B%7BcollectionName%7D%7D'

DELETE /admin/projects/:projectId/collections/:name/data

Reset a collection's data — empty every row but keep the collection, its schema, indexes, and config. The collection-scoped analogue of a per-user reset; ideal for wiping a fixture table between test runs without rebuilding it. To delete the collection itself, use the DELETE …/collections/:name route above.

hard by default — the rows are gone, not tombstoned. Pass ?hard=false to soft-delete instead (stamps deleted_at on currently-live rows so they stay recoverable). No ?confirm= is required: the path already names the exact collection and this is a Bearer-authed admin-only surface.

curl -X DELETE 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/fixtures/data' \
  -H 'Authorization: Bearer $AMBA_PAT'

Response 200

{ "data": { "collection": "fixtures", "deleted": 128, "mode": "hard" } }

Errors

• 404 COLLECTION_NOT_FOUND — the collection doesn't exist.
• 500 RESET_FAILED.

Try it:

curl -X DELETE 'https://api.amba.dev/v1/admin/projects/%7B%7BprojectId%7D%7D/collections/%7B%7BcollectionName%7D%7D/data'

GET /admin/projects/:projectId/collections/:name/migrations

Migration history for the project, newest first (limit default 50, max 500). Each row carries the version, status, applied_at, sql_text, and error.

Response 200

{
  "data": [
    {
      "version": 3,
      "status": "applied",
      "applied_at": "…",
      "sql_text": "DROP TABLE …",
      "error": null
    }
  ]
}

Try it:

curl -X GET 'https://api.amba.dev/v1/admin/projects/%7B%7BprojectId%7D%7D/collections/%7B%7BcollectionName%7D%7D/migrations'

Rows (admin)

These routes read and write rows as the developer/agent. Unlike the end-user client SDK, they bypass per-user row scoping: an admin insert can attribute a row to any user_id (pass it in the body) or leave it NULL on a shared collection.

POST /admin/projects/:projectId/collections/:name/rows

Insert one row. The body is a flat column → value map (no wrapper). Server-managed columns (id, created_at, updated_at, deleted_at) are stripped; user_id is honored if present. Two control fields are recognised on the body: as_system, plus the legacy idempotency_key request-control alias when the collection does not define a physical column with that name. Idempotency-Key is the canonical request-idempotency surface.

curl -X POST 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/notes/rows' \
  -H 'Authorization: Bearer $AMBA_PAT' \
  -H 'Content-Type: application/json' \
  -d '{ "title": "Welcome", "body": "First note", "pinned": true }'

Returns 201 { "data": { ...row } }.

Shared (system) rows (as_system)

Pass "as_system": true to write a global, null-owner row (user_id = NULL) — the developer-seeded-content shape. It's mutually exclusive with an explicit user_id (sending both is 400). The collection must allow a null owner: create it with "shared": true or PATCH "relax_user_id": true, otherwise an as_system insert returns 409 NOT_SHARED_COLLECTION.

curl -X POST '…/collections/announcements/rows' \
  -H 'Authorization: Bearer $AMBA_PAT' -H 'Content-Type: application/json' \
  -d '{ "as_system": true, "title": "Maintenance window Sunday" }'

Idempotency (Idempotency-Key)

To make a retried or double-submitted insert safe, supply an idempotency key with the Idempotency-Key request header. The historical idempotency_key body alias remains supported only for collections that do not have a customer column named idempotency_key; new integrations should use the header. If that physical column exists, its body value is stored as ordinary row data and the header independently controls request idempotency. The first insert with a given key creates the row (201); any replay returns the original row unchanged (200) instead of a duplicate. The key is scoped to (collection, key). If the original row was since soft-deleted, the replay returns 409 IDEMPOTENCY_REPLAY_GONE rather than silently returning data: null, resurrecting it, or creating a duplicate.

curl -X POST '…/collections/orders/rows' \
  -H 'Authorization: Bearer $AMBA_PAT' \
  -H 'Idempotency-Key: order-2026-0042' \
  -H 'Content-Type: application/json' \
  -d '{ "sku": "WIDGET", "qty": 2 }'

Return shape (return=minimal)

By default the insert echoes the full row (RETURNING *). For agent-driven bulk migrations — rows with large JSONB columns (word timings, embeddings, chunk blobs) — that echo can exhaust an agent's token budget after a couple of inserts. Pass ?return=minimal (or the idiomatic header Prefer: return=minimal) to trim the response to the row id only. The row is still inserted in full; only the response is trimmed.

# Default — full row
curl -X POST '…/collections/chapters/rows' \
  -H 'Authorization: Bearer $AMBA_PAT' -H 'Content-Type: application/json' \
  -d '{ "title": "Chapter 1", "timings": { /* 100KB of word timings */ } }'
# → 201 { "data": { "id": "…", "title": "Chapter 1", "timings": { …all 100KB echoed… }, … } }
 
# Minimal — id only
curl -X POST '…/collections/chapters/rows?return=minimal' \
  -H 'Authorization: Bearer $AMBA_PAT' -H 'Content-Type: application/json' \
  -d '{ "title": "Chapter 1", "timings": { /* 100KB of word timings */ } }'
# → 201 { "data": { "id": "…" } }

The same option applies to the batch endpoint below, where it matters most — a 500-row batch echoes rows: [{ "id": "…" }, …] instead of megabytes of inserted rows.

A required column left unset returns 400 NOT_NULL_VIOLATION — most often user_id on a per-user collection; create the collection with "shared": true (or PATCH "relax_user_id": true) if the rows are global/shared. 400 INVALID_COLUMN for an unknown column; 404 COLLECTION_NOT_FOUND if the collection doesn't exist. A row that collides with a unique index returns 409 CONFLICT (not an opaque 500) — pass on_conflict / conflict_target query params to turn the insert into an upsert instead.

Upsert (on_conflict / conflict_target)

The single-row insert accepts the same upsert query params as the client route: ?on_conflict=error|ignore|update (default error) plus ?conflict_target=col1,col2 naming the unique-index columns. ignore returns the existing row, update merges the supplied columns; both require conflict_target, and a target not covered by a unique index (or absent from the row) returns 400 INVALID_CONFLICT_TARGET. See the client collections reference for the full status-code matrix (admin inserts aren't user-scoped, so the "owned by another user" 409 case doesn't apply).

POST /admin/projects/:projectId/collections/:name/rows:batch

Bulk-insert up to 500 rows in a single atomic statement — the canonical path for seeding and migrations (question banks, content libraries, reference data). Far faster than calling the single-row insert in a loop.

curl -X POST 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/questions/rows:batch' \
  -H 'Authorization: Bearer $AMBA_PAT' \
  -H 'Content-Type: application/json' \
  -d '{
    "rows": [
      { "prompt": "Capital of France?", "answer": "Paris" },
      { "prompt": "Capital of Japan?", "answer": "Tokyo" }
    ],
    "on_conflict": "skip"
  }'

Returns 201 { "data": { "inserted": <n>, "rows": [...] } }; with "on_conflict": "skip" the response also carries "skipped": <n>. Pass ?return=minimal (or Prefer: return=minimal) to trim rows to [{ "id": "…" }, …] — essential when seeding rows with large JSONB columns so the response doesn't exhaust an agent's token budget. The rows are still inserted in full. Errors: 400 BATCH_TOO_LARGE (more than 500 rows — split into multiple calls), 400 NOT_NULL_VIOLATION / INVALID_COLUMN, 404 COLLECTION_NOT_FOUND, 409 CONFLICT (when on_conflict is "error").

GET /admin/projects/:projectId/collections/:name/rows

Find rows with the full query DSL — pass the [FindQuery] as a JSON body or via ?query=<json> (and the friendly ?where= / ?limit= / ?order= shortcuts). Unlike the client route, admin reads are not user-scoped, so you see every user's rows.

# Default — excludes soft-deleted.
curl 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/notes/rows?limit=50' \
  -H 'Authorization: Bearer $AMBA_PAT'
 
# Include soft-deleted rows.
curl 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/notes/rows?includeDeleted=true' \
  -H 'Authorization: Bearer $AMBA_PAT'

Returns 200 { "data": [...], "next_cursor": ... }.

where syntax

where is a column-keyed JSON object, not the Node SDK's { column, op, value } filter shape. On GET routes, URL-encode it in the query string (?where=<json>), or put it inside the full ?query=<FindQuery> object. Bulk write routes take the same object directly in the JSON body.

{ "status": "queued" }
{ "status": { "in": ["queued", "running"] } }
{ "status": { "notIn": ["done", "failed"] } }
{ "attempt": { "gte": 3 } }
{ "deleted_at": { "isNull": true } }
{ "name": { "ilike": "%dream%" } }
{ "and": [{ "status": "queued" }, { "attempt": { "lt": 3 } }] }

Supported field operators: eq, ne, gt, gte, lt, lte, in, notIn, like, ilike, isNull, isNotNull, contains, containedBy, and overlaps. Operators are not $-prefixed. The null checks must be literal true ({ "deleted_at": { "isNull": true } }).

If you are translating from the Node SDK filter shape, map not_in → notIn, is_null → isNull, is_not_null → isNotNull, and contained_by → containedBy.

GET /admin/projects/:projectId/collections/:name/rows/count

Exact row count. Honors where + includeDeleted only (pagination / projection params are rejected). Same default as the find route — includeDeleted defaults to false, so soft-deleted rows are not counted unless you pass ?includeDeleted=true.

curl 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/notes/rows/count?where={"pinned":true}' \
  -H 'Authorization: Bearer $AMBA_PAT'

Returns 200 { "data": { "count": <n> } }.

GET /admin/projects/:projectId/collections/:name/rows/:id

Fetch one row by id.

curl 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/notes/rows/$ROW_ID' \
  -H 'Authorization: Bearer $AMBA_PAT'

Returns 200 { "data": { ...row } }, 404 NOT_FOUND if no row exists, or 400 INVALID_UUID for a malformed id.

POST /admin/projects/:projectId/collections/:name/rows/aggregate

Group-by aggregation across all rows (no user-scope). Run a count / sum / avg / min / max server-side instead of paging through rows.

count with no column is COUNT(*); pass a column for COUNT(col). sum/avg/min/max require a column. as sets the output alias. having rebuilds its aggregate from fn/column, so it can filter on an aggregate the select doesn't return. Like the row reads above, soft-deleted rows are excluded by default — pass "includeDeleted": true to include them.

curl -X POST 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/orders/rows/aggregate' \
  -H 'Authorization: Bearer $AMBA_PAT' \
  -H 'Content-Type: application/json' \
  -d '{
    "select": [{ "fn": "count", "as": "total" }, { "fn": "sum", "column": "amount", "as": "revenue" }],
    "group_by": "status",
    "having": [{ "fn": "count", "op": "gte", "value": 3 }],
    "order_by": [{ "by": "revenue", "direction": "desc" }],
    "limit": 5
  }'

Returns one row per group, each merging the group columns with the aggregate aliases:

{
  "data": [
    { "status": "paid", "total": 12, "revenue": 4200 },
    { "status": "pending", "total": 3, "revenue": 900 }
  ]
}

POST /admin/projects/:projectId/collections/transaction

Run multiple collection writes atomically inside one project tenant database. This is the admin equivalent of POST /v1/client/collections/transaction: all operations commit or all roll back. Admin transactions bypass user row scoping and can read/write any row in the project.

curl -X POST 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/transaction' \
  -H 'Authorization: Bearer $AMBA_PAT' \
  -H 'Content-Type: application/json' \
  -d '{
    "operations": [
      {
        "collection": "sessions",
        "op": "update",
        "id": "00000000-0000-0000-0000-000000000000",
        "set": { "status": "running" },
        "expected": { "status": "queued" }
      },
      {
        "collection": "webhook_outbox",
        "op": "insert",
        "row": { "kind": "session_started" }
      }
    ]
  }'

Each operation has collection plus op: "insert" | "update" | "delete". Inserts take row and optional on_conflict / conflict_target. Updates take id, non-empty set, optional expected, optional where, and optional objects: "merge" | "replace" for JSONB object update behavior. Deletes take id, optional expected, optional where, and optional hard: true for an admin hard delete.

expected is a compare-and-set guard: the write applies only if the current row still matches the supplied column values. A mismatch returns 409 PRECONDITION_FAILED; a missing row returns 404 NOT_FOUND. On transaction failure, the response includes details.opIndex naming the failing operation. Transactions are project-local; they do not span multiple Amba projects.

INSERT vs PATCH body shapes

The write verbs deliberately take different body shapes — this is a contract, not an accident:

Side by side:

# INSERT — flat column → value map
curl -X POST 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/notes/rows' \
  -H 'Authorization: Bearer $AMBA_PAT' \
  -H 'Content-Type: application/json' \
  -d '{ "title": "Welcome", "pinned": true }'
 
# UPDATE — columns inside the `set` envelope
curl -X PATCH 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/notes/rows/$ROW_ID' \
  -H 'Authorization: Bearer $AMBA_PAT' \
  -H 'Content-Type: application/json' \
  -d '{ "set": { "pinned": false } }'

A PATCH body with no usable set (an empty body, or a flat body on the bulk route, where the wrapper is required) returns 400 INVALID_BODY with a pointed hint naming the top-level keys you actually sent:

{
  "error": {
    "code": "INVALID_BODY",
    "message": "PATCH expects the fields you want to update inside a \"set\" object, e.g. {\"set\": {…}}. Received top-level keys: [title, pinned]."
  }
}

PATCH /admin/projects/:projectId/collections/:name/rows/:id

Update one row by id. The canonical body is { "set": { ...columns } }. A flat { "col": value } body (the same shape INSERT takes) is also accepted on this single-row route as an alias — there's no where here, so the shape is unambiguous.

An unknown field in either shape returns 400 INVALID_COLUMN and names the field; it is a caller-validation error, not a retryable server failure.

# Canonical
curl -X PATCH 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/notes/rows/$ROW_ID' \
  -H 'Authorization: Bearer $AMBA_PAT' \
  -H 'Content-Type: application/json' \
  -d '{ "set": { "pinned": false } }'
 
# Flat alias — equivalent
curl -X PATCH 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/notes/rows/$ROW_ID' \
  -H 'Authorization: Bearer $AMBA_PAT' \
  -H 'Content-Type: application/json' \
  -d '{ "pinned": false }'

Two edge cases of the flat alias: a column literally named set holding an object resolves to the wrapper interpretation — use the explicit { "set": … } wrapper if you have one. And expected (compare-and-set, below) is a top-level control key — it's stripped on the flat path, while inside a { "set": { … } } envelope a real column named expected is written normally.

Compare-and-set (expected)

Optional optimistic concurrency, same contract as the client PATCH: include a top-level expected map of column → value and the update applies only if the row's current values match — otherwise 409 PRECONDITION_FAILED (vs 404 NOT_FOUND when the row doesn't exist at all). Null-safe: "expected": { "col": null } matches a NULL column. Admin CAS is not user-scoped — it can target any owner's row.

{ "set": { "status": "shipped" }, "expected": { "status": "packing" } }

JSON object columns merge (merge-patch)

A set value that is a JSON object targeting a jsonb column merges into the stored object (shallow, top-level key union, atomic in one statement) instead of replacing it — same contract as the client PATCH. Arrays, scalars and null replace the column value, as does any patch whose stored value isn't a JSON object. Opt out per request with ?objects=replace to overwrite the whole stored object.

PATCH /admin/projects/:projectId/collections/:name/rows

Bulk update rows matching a filter. Body is { "where": {...}, "set": {...} }. Unlike the single-row route, the set wrapper is required here — set and where are siblings, and the wrapper is what tells the update payload apart from the filter; a flat body returns 400 INVALID_BODY with the top-level-keys hint shown above. where is required and must produce at least one predicate (400 WHERE_REQUIRED otherwise — an empty {} doesn't count) so you can't accidentally rewrite the whole collection; to genuinely touch every row, use an always-true predicate such as { "id": { "isNotNull": true } }. JSON object values targeting jsonb columns merge exactly like the single-row PATCH above (?objects=replace opts out).

curl -X PATCH 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/notes/rows' \
  -H 'Authorization: Bearer $AMBA_PAT' \
  -H 'Content-Type: application/json' \
  -d '{ "where": { "pinned": true }, "set": { "pinned": false } }'

DELETE /admin/projects/:projectId/collections/:name/rows/:id

Soft-delete one row by id. Pass ?hard=true only when you intentionally need to remove the row from the table. Hard delete works for both a live row and an existing soft-delete tombstone and returns mode: "hard".

curl -X DELETE 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/notes/rows/$ROW_ID' \
  -H 'Authorization: Bearer $AMBA_PAT'

Returns 200 { "data": { "id": "...", "deleted": true, "mode": "soft" } }. If a soft-delete targets an already soft-deleted row, the route returns deleted: false with already_deleted: true. Missing rows return 404 NOT_FOUND.

DELETE /admin/projects/:projectId/collections/:name/rows

Bulk delete rows matching where. You must pass ?confirm=<count> with the exact number of rows that should be deleted; preview that value with the rows/count endpoint first. The default is a soft delete; ?hard=true permanently deletes matching rows.

curl -X DELETE 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/notes/rows?confirm=3' \
  -H 'Authorization: Bearer $AMBA_PAT' \
  -H 'Content-Type: application/json' \
  -d '{ "where": { "status": "archived" } }'

MCP tools

amba_collections_create, amba_collections_list, amba_collections_get, amba_collections_alter, and amba_collections_delete wrap the schema routes for agentic use — pass your pat and project_id. The destructive operations set their confirm guard automatically. For rows, amba_admin_insert_row (one row), amba_admin_insert_rows (bulk, up to 500 — the seeding/migration path), amba_admin_list_rows, and amba_admin_aggregate_rows (group-by count/sum/avg/min/max) wrap the row routes. amba_admin_insert_row takes on_conflict + conflict_target for upserts, plus as_system to write a shared null-owner row. amba_collections_create takes shared (and read_policy / write_policy), and amba_collections_alter takes relax_user_id, for shared/global collections. amba_collections_reset_data empties a collection's rows while keeping its schema (the reset path above).