Collections

These are the row read/write routes the client SDK calls. Schema management (create a collection, add columns/indexes) lives in the admin collections reference; the SDK-level usage of everything below is covered in the Collections feature guide.

By default every route here is auto-scoped to the signed-in user. Reads filter by user_id = <session user> AND deleted_at IS NULL; writes stamp user_id from the session token and reject any client-supplied user_id — a client can never set another user's id.

A collection can opt into broader access via its access policy: read_policy: "public" lets every signed-in user read all rows (public catalogs), and write_policy: "authenticated" lets any signed-in user write. The default policy (owner/owner) keeps the strict per-user scoping described above.

A collection can also be group-owned (owner_scope: "group"): rows belong to a group and access is resolved from the caller's membership, gated by role (read_policy: "member", write_policy: "member:admin"; rank owner > admin > member > viewer). Reads return the rows of every group the signed-in user belongs to at the read role; to act within one specific group — required when inserting, so Amba knows which group owns the new row — send X-Group-Context: <groupId> alongside the session token (the Node SDK exposes this as amba.asUser(uid).asGroup(gid)). Membership is verified server-side: a group the caller isn't a member of is rejected with 403 NOT_A_GROUP_MEMBER, an insert with no group context returns 400 GROUP_CONTEXT_REQUIRED, a role below the write floor returns 403 GROUP_ROLE_FORBIDDEN, and only a private group may own rows (400 GROUP_NOT_PRIVATE). See team-owned collections.

All routes require both the client API key and a session token:

X-Api-Key: <client key>
Authorization: Bearer <session token>

Endpoints

Collection names must match ^[a-z][a-z0-9_]*$; reserved prefixes are rejected with 400 RESERVED_NAME. Hitting a collection that doesn't exist returns 404 COLLECTION_NOT_FOUND.

POST /client/collections/:name

Insert one row. The body is a flat column → value map. Server-managed columns (id, created_at, updated_at, deleted_at) and any client-supplied user_id are stripped; user_id is injected from the session.

Request

{ "title": "Hello amba", "body": "My first post." }

Response 201

{
  "data": {
    "id": "…",
    "user_id": "…",
    "title": "Hello amba",
    "body": "My first post.",
    "created_at": "…"
  }
}

Upsert (on_conflict / conflict_target)

Upsert requires a unique index to arbitrate on. You declare unique (and composite) indexes when you create the collection — pass them in the indexes array with unique: true. See the admin collections reference.

By default a plain insert that collides with a unique index returns 409 CONFLICT. To turn the insert into an upsert, pass two query params:

• error (default) — a plain insert. A unique-index clash returns 409 CONFLICT (not a 500).
• ignore — ON-CONFLICT-do-nothing. If the row already exists, the existing row is returned with 200; if it actually inserted, 201.
• update — merges the columns you provided into the existing row. 201 on a first insert, 200 on a merge.

conflict_target is required when on_conflict is ignore or update. Every column you name must be present in the row and covered by a unique index, or the request is rejected:

• A conflict_target column missing from the row → 400 INVALID_CONFLICT_TARGET.
• A conflict_target that doesn't match any unique index on the collection → 400 INVALID_CONFLICT_TARGET.

# Upsert: insert-or-merge on the (user_id, kind) unique index.
curl -X POST '${BASE_URL}/client/collections/preferences?on_conflict=update&conflict_target=kind' \
  -H 'X-Api-Key: ${CLIENT_API_KEY}' \
  -H 'Authorization: Bearer ${SESSION_TOKEN}' \
  -H 'Content-Type: application/json' \
  -d '{"kind":"theme","value":"dark"}'

(user_id is part of the unique index server-side but is injected from the session, so you only name the columns you actually supply — here kind.)

Errors

• 400 INVALID_BODY / INVALID_JSON — body isn't a JSON object.
• 400 INVALID_COLUMN — unknown column or unsafe identifier.
• 400 INVALID_CONFLICT_TARGET — see above.
• 400 NOT_NULL_VIOLATION — a required column (often user_id on a per-user collection) was left unset.
• 409 CONFLICT — unique-index clash on a plain insert, or an update upsert blocked because the key belongs to another user.

Try it:

curl -X POST 'https://api.amba.dev/v1/client/collections/%7B%7BcollectionName%7D%7D' \
  -H 'Content-Type: application/json' \
  -d '{
  "title": "Hello amba",
  "body": "My first post."
}'

POST /client/collections/:name/batch

Bulk-insert up to 500 rows in a single atomic statement — the whole batch commits or none of it does. user_id is injected on every row.

Request

{ "rows": [{ "title": "One" }, { "title": "Two" }] }

Rows may be sparse — a column absent from one row is inserted as NULL.

Response 201

{
  "data": [
    { "id": "…", "title": "One" },
    { "id": "…", "title": "Two" }
  ]
}

An empty rows array returns 201 { "data": [] }.

Errors

• 400 BATCH_TOO_LARGE — more than 500 rows. Split into multiple calls.
• 400 INVALID_BODY — body isn't { "rows": [...] }, or a row isn't an object.
• 400 INVALID_COLUMN / NOT_NULL_VIOLATION.
• 409 CONFLICT — any row collides with a unique index (the whole batch aborts).

Try it:

curl -X POST 'https://api.amba.dev/v1/client/collections/%7B%7BcollectionName%7D%7D/batch' \
  -H 'Content-Type: application/json' \
  -d '{
  "rows": [
    {
      "title": "One"
    },
    {
      "title": "Two"
    }
  ]
}'

POST /client/collections/:name/find

Find rows using the SDK filter shape. This is the route the SDK's find() calls.

Request (FindOptions)

Each filter leaf is { "column": "...", "op": "...", "value": ... }. op is one of:

Response 200

{ "data": [{ "id": "…", "title": "…" }], "next_cursor": "…" }

next_cursor is null when there are no more pages (only present on the cursor path).

Array columns

Collections support native array column types (text[], integer[], bigint[], numeric[], boolean[], uuid[]) — declare them at create time. The three set operators filter on them:

• contains (@>) — the column has all of the given values.
• overlaps (&&) — the column shares at least one value.
• contained_by (<@) — every column element is in the given set.

{ "filter": { "column": "tags", "op": "contains", "value": ["enemies-to-lovers"] } }

On the raw where shape (the GET ?where= form, admin row reads, and include.where) the operators are keys on the column — { "tags": { "contains": ["enemies-to-lovers"] } } — and contained_by is spelled containedBy there. Prefer an array column over jsonb for any flat, typed list you filter on; jsonb is for nested documents and can't use these operators.

Text search

You don't need to ship rows to the client and filter them yourself, and you don't need a separate search service — find does keyword search server-side. Add a search object alongside (or instead of) filter:

• Substring (default): a case-insensitive contains match across the named columns — q: "ocean" matches "The Ocean at the End". Works on any collection. Literal % / _ in the query are matched literally, not as wildcards.
• Fuzzy: typo-tolerant word-similarity matching — the query matches when it's close to a word or phrase inside the column, so a short query still matches a long title and a near-miss (q: "oceaen") still hits. threshold (default 0.3) sets how close is close enough: raise it toward 1 for stricter matching, lower it for recall. When fuzzy is on and you haven't supplied an explicit order or cursor, results are ranked best-match-first automatically (a row ranks by its best-matching column).

search AND's with filter, respects the collection's read scoping, and works with limit / cursor like any other find. The same search clause works on GET .../count via ?query=.

A catalog search — misspelled trope, still finds the right novels, best match first:

{
  "search": { "q": "enemis to lovers", "columns": ["title", "summary"], "fuzzy": true },
  "filter": { "column": "status", "op": "eq", "value": "published" },
  "limit": 20
}

Which text tool when? search for user-typed queries (substring by default, fuzzy: true for typo tolerance + ranking); the like / ilike filter operators when you control the pattern (prefix/suffix matches); find-nearest for semantic similarity over a vector column — meaning, not keywords.

Eager-load related rows (include)

Fetch related rows from other collections in the same find call — one batched query per relation instead of an N+1 fetch per row.

{
  "where": { "status": "active" },
  "include": [
    {
      "collection": "chapters",
      "foreignKey": "novel_id",
      "as": "chapters",
      "where": { "status": "published" },
      "select": ["id", "heading"],
      "include": [{ "collection": "comments", "foreignKey": "chapter_id", "as": "comments" }]
    }
  ]
}

Each novel row comes back with chapters: [...], and each chapter with comments: [...] — three collections, three queries, regardless of row count. The included collection's own read policy and soft-delete filter are honored independently (an owner-scoped include only returns the caller's related rows). At most 5 include clauses per level.

Try it:

curl -X POST 'https://api.amba.dev/v1/client/collections/%7B%7BcollectionName%7D%7D/find' \
  -H 'Content-Type: application/json' \
  -d '{
  "filter": {
    "column": "status",
    "op": "eq",
    "value": "published"
  },
  "limit": 50
}'

GET /client/collections/:name

The query-string variant of find. Pass ?query=<JSON FindQuery> for the full DSL, or the friendly shortcuts (?where=, ?limit=, ?order=, ?offset=, ?cursor=, ?select=, ?includeDeleted=). The two can't be combined — a friendly shortcut alongside ?query= returns 400 INVALID_QUERY_PARAM. Unknown query params are rejected loudly with the same code.

Response 200

Same shape as POST .../find.

Try it:

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

POST /client/collections/:name/find-nearest

Vector nearest-neighbor search over a vector column. Supply either a to_vector (a number array) or a to_text string (the server fetches the embedding for you).

Request

Exactly one of to_vector / to_text is required. Each returned row carries a synthesized _distance column.

Response 200

{ "data": [{ "id": "…", "_distance": 0.12 }], "next_cursor": "…" }

Errors

• 400 INVALID_NEAREST_INPUT — neither or both of to_vector / to_text.
• 400 INVALID_DISTANCE — distance not one of the three metrics.
• 400 INVALID_VECTOR — to_vector isn't an array of finite numbers.

Try it:

curl -X POST 'https://api.amba.dev/v1/client/collections/%7B%7BcollectionName%7D%7D/find-nearest' \
  -H 'Content-Type: application/json' \
  -d '{
  "to_text": "a calm beach at sunset",
  "k": 5
}'

GET /client/collections/:name/count

Exact server-side row count, scoped to the caller. Honors where and includeDeleted only (pagination/projection params are rejected with 400 INVALID_QUERY_PARAM).

Response 200

{ "data": { "count": 42 } }

Try it:

curl -X GET 'https://api.amba.dev/v1/client/collections/%7B%7BcollectionName%7D%7D/count'

POST /client/collections/:name/aggregate

Group-by aggregation, scoped to the caller.

Request

count without a column is COUNT(*); pass a column for COUNT(col). as sets the output alias (defaults to count, count_<col>, or <fn>_<col>). sum/avg/min/max require a column. having rebuilds its aggregate from fn/column, so it can filter on an aggregate the select doesn't return.

Request example

"Top 5 statuses by revenue, ignoring statuses with fewer than 3 orders":

{
  "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
}

Response 200

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 }
  ]
}

Try it:

curl -X POST 'https://api.amba.dev/v1/client/collections/%7B%7BcollectionName%7D%7D/aggregate' \
  -H 'Content-Type: application/json' \
  -d '{
  "select": [
    {
      "fn": "count",
      "as": "total"
    }
  ],
  "group_by": "status"
}'

GET /client/collections/:name/:id

Get one row by id. Returns 404 NOT_FOUND if the row doesn't exist or doesn't belong to the caller. 400 INVALID_ID if id isn't a UUID.

Response 200

{ "data": { "id": "…", "title": "…" } }

Try it:

curl -X GET 'https://api.amba.dev/v1/client/collections/%7B%7BcollectionName%7D%7D/%7B%7BrowId%7D%7D'

PATCH /client/collections/:name/:id

Update one row by id. The canonical body puts the columns to change inside a set envelope; a flat { "col": value } body (the same shape INSERT takes) is also accepted on this single-row route as an alias. A body with no usable columns returns 400 INVALID_BODY naming the top-level keys you sent. user_id and server-managed columns can't be updated. (The bulk update route still requires the set wrapper — there it disambiguates the update payload from the sibling where filter.)

Request

{ "set": { "title": "New title" } }

or, equivalently, flat:

{ "title": "New title" }

A column literally named set holding an object resolves to the wrapper interpretation — use the explicit { "set": … } envelope if you have one.

JSON object columns merge (merge-patch)

When a set value is a JSON object targeting a jsonb column, the keys are merged into the stored object server-side instead of replacing it — two devices each patching their own key both survive:

// stored: { "theme": "dark", "lang": "en" }
{ "set": { "prefs": { "lang": "fr" } } }
// → stored: { "theme": "dark", "lang": "fr" }

The merge is shallow (top-level key union, applied atomically in one statement): a nested object replaces that key wholesale, and a key set to null stores JSON null (it does not delete the key). Arrays, scalars and null always replace the whole column value, and if the stored value isn't a JSON object the patch object replaces it too. To overwrite the entire stored object, opt out per request with ?objects=replace:

curl -X PATCH '${BASE_URL}/client/collections/notes/$ROW_ID?objects=replace' \
  -H 'X-Api-Key: ${CLIENT_API_KEY}' \
  -H 'Authorization: Bearer ${SESSION_TOKEN}' \
  -H 'Content-Type: application/json' \
  -d '{ "set": { "prefs": { "lang": "fr" } } }'

The same semantics (and the same ?objects=replace opt-out) apply to the bulk update routes; on a transaction update operation, pass "objects": "replace" on the op instead.

Compare-and-set (expected)

Add an expected map to make the update conditional — optimistic concurrency without a transaction. The update applies only if the row still matches every column in expected; otherwise it returns 409 PRECONDITION_FAILED and nothing changes.

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

expected is null-safe: "expected": { "assignee": null } matches a row whose assignee is currently NULL. A common pattern is read → modify → write-back with expected set to the values you read, so a concurrent writer can't silently clobber your change.

Response 200

The updated row.

Errors

• 400 INVALID_BODY — no columns to update (empty body / empty set), or expected isn't an object.
• 400 INVALID_COLUMN — attempt to write user_id or a server-managed column.
• 404 NOT_FOUND — row doesn't exist or isn't the caller's.
• 409 PRECONDITION_FAILED — the row no longer matches expected.

Try it:

curl -X PATCH 'https://api.amba.dev/v1/client/collections/%7B%7BcollectionName%7D%7D/%7B%7BrowId%7D%7D' \
  -H 'Content-Type: application/json' \
  -d '{
  "set": {
    "title": "New title"
  }
}'

DELETE /client/collections/:name/:id

Soft-delete one row by id — sets deleted_at and excludes the row from subsequent reads. Hard delete is admin-only.

Response 200

{ "data": { "id": "…", "deleted": true } }

Returns 404 NOT_FOUND if the row doesn't exist or isn't the caller's.

Try it:

curl -X DELETE 'https://api.amba.dev/v1/client/collections/%7B%7BcollectionName%7D%7D/%7B%7BrowId%7D%7D'

MCP tools

For agentic row writes, amba_client_insert_row takes on_conflict and conflict_target for upserts, and amba_client_update_row takes expected for compare-and-set. Schema-side row tooling (admin) is documented in the admin collections reference.