Collections

Collections are relational tables — each project gets its own managed Postgres database, and a collection is a real table in it with typed columns, foreign keys, transactions, and unique indexes. You define the schema once per project and read/write from any SDK. Every read auto-scopes to the signed-in user — you never see another user's rows even if you forget to add a filter. Writes stamp the user from the session token; client-supplied user_id fields are ignored.

Define a collection's schema via the CLI (amba collections create <name> --field title:text --field body:text) or the admin API before reading or writing from a client SDK.

Quick start

import { Amba } from '@layers/amba-web';
 
await Amba.auth.signInAnonymously();
 
const post = await Amba.collections.insert<{ id: string; title: string; body: string }>('posts', {
  title: 'Hello amba',
  body: 'My first post.',
});
 
const refetched = await Amba.collections.findOne<typeof post>('posts', post.id);

Operations

Insert

The server stamps user_id from your session. Any user_id in the payload is rejected.

const post = await Amba.collections.insert<{ id: string; title: string }>('posts', {
  title: 'Hello',
});

Find one

const post = await Amba.collections.findOne<Post>('posts', 'post_123');

Returns null (or throws NotFound, depending on platform) if the row doesn't exist or doesn't belong to the signed-in user.

Find many

find returns { data, next_cursor, has_more }. The filter DSL composes expressive operators server-side.

const { where } = Amba.collections;
 
// Equality
const { data: published } = await Amba.collections.find('posts', {
  filter: where.eq('status', 'published'),
  order: [{ column: 'created_at', direction: 'desc' }],
  limit: 50,
});
 
// Composition
const { data: hot } = await Amba.collections.find('posts', {
  filter: where.and(
    where.eq('status', 'published'),
    where.gte('score', 100),
    where.or(where.like('title', 'Hello%'), where.in('tag', ['featured', 'trending'])),
  ),
  order: [{ column: 'score', direction: 'desc' }],
  limit: 20,
});

Filter operators

Array columns and set operators

Collections support native array column types — text[], integer[], bigint[], numeric[], boolean[], and uuid[] — for flat lists of scalars you filter on: tags, tropes, category slugs, moods, related ids. Declare them in the schema like any other column type (via the admin API or MCP):

{ "name": "tropes", "type": "text[]", "nullable": true }

Three set operators work on array columns:

In the SDKs, pass a filter leaf with the operator name — the same { column, op, value } shape the where.* helpers produce:

// Novels tagged with BOTH tropes:
const { data } = await Amba.collections.find('novels', {
  filter: { column: 'tropes', op: 'contains', value: ['enemies-to-lovers', 'slow-burn'] },
});
 
// Novels matching ANY of the moods (composes with where.and / where.or):
const { data: cozy } = await Amba.collections.find('novels', {
  filter: where.and(where.eq('status', 'published'), {
    column: 'moods',
    op: 'overlaps',
    value: ['cozy', 'lighthearted'],
  }),
});

On the REST where shape (the GET ?where= / ?query= form, admin row reads, and include.where), the operators are keys on the column:

{ "tags": { "contains": ["enemies-to-lovers"] } }

Search

For "search this catalog" UX (title, author, tags) use search — built-in full-text keyword search, server-side, no separate search service:

const { data } = await Amba.collections.find('novels', {
  search: { q: 'enemis to lovers', columns: ['title', 'summary'], fuzzy: true },
  limit: 20,
});
// Finds "Enemies to Lovers" despite the typo, ranked best-match-first.

The search clause:

Fuzzy matching uses word-similarity semantics: 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 misspelling (enemis, slowburn) still hits. Results are ranked by descending similarity automatically when you don't pass an explicit order or cursor; with multiple columns, a row ranks by its best-matching column.

search composes with filter (both are AND'd) and respects the collection's read scoping like any other find.

Which text tool when?

• search (default) — "user typed something in a search box": substring match across columns.
• search + fuzzy: true — same, but typo-tolerant and relevance-ranked. The right default for catalog search.
• like / ilike filters — you control the pattern ('Hello%' prefix match, '%@example.com' suffix match). Exact, not typo-tolerant.
• findNearest — semantic similarity over a vector column ("books about found family" with no keyword overlap). Use search for keywords and typos; find-nearest for meaning.

Update

const updated = await Amba.collections.update('posts', 'post_123', {
  title: 'New title',
  body: 'Updated body.',
});

The SDK takes the fields object positionally — the wire format wraps them in a set envelope, so a raw HTTP PATCH body is {"set": {"title": "New title", "body": "Updated body."}}, not {"title": ...}. The SDK does this for you; you only need to know it if you're calling the REST endpoint directly (a 400 INVALID_BODY naming your top-level keys means you forgot the set wrapper).

jsonb columns merge, not replace. When the value you pass for a jsonb column is a JSON object, its keys merge into the stored object server-side (shallow, atomic in one statement) — so two devices each updating their own key both survive:

// stored prefs: { theme: 'dark', lang: 'en' }
await Amba.collections.update('settings', id, { prefs: { lang: 'fr' } });
// stored prefs is now { theme: 'dark', lang: 'fr' }

The merge is top-level only (a nested object replaces that key wholesale), and a key set to null stores JSON null rather than deleting the key. Arrays, scalars and null replace the whole column value. To overwrite the entire stored object, call the REST endpoint with ?objects=replace — see the client API reference.

Update by id is the canonical shape. Bulk update (update many rows matching a filter) is exposed via the admin API; on the client SDK, iterate and update individually.

Delete

await Amba.collections.delete('posts', 'post_123');

Soft delete — rows are marked deleted_at and excluded from subsequent reads. Hard delete is admin-only.

Bulk soft-delete (deleteWhere)

Soft-delete every row matching a filter and get back the count removed. Auto-scoped to your own rows.

const { data } = await Amba.collections.deleteWhere(
  'todos',
  Amba.collections.where.eq('status', 'done'),
);
console.log(`${data.deleted} removed`);

Restore (restore)

Bring a soft-deleted row back by id (clears deleted_at) and get the restored row. Only a row you own that's currently soft-deleted can be restored — anything else resolves as not-found.

const post = await Amba.collections.restore('posts', 'post_123');

Bulk insert

Insert many rows in one atomic call (up to 500 per batch — the whole batch commits or none of it does). user_id is stamped on every row, same as a single insert. Far faster than looping inserts — POST /client/collections/:name/batch:

const res = await fetch(`${API}/v1/client/collections/todos/batch`, {
  method: 'POST',
  headers: { 'X-Api-Key': apiKey, Authorization: `Bearer ${sessionToken}` },
  body: JSON.stringify({ rows: [{ title: 'Buy milk' }, { title: 'Walk dog' }] }),
});
const { data } = await res.json(); // inserted rows

A unique-index clash on any row aborts the whole batch with 409 CONFLICT; more than 500 rows returns 400 BATCH_TOO_LARGE.

Upsert

A plain insert clashes with a unique index as 409 CONFLICT. To make it idempotent, use upsert with an onConflict policy and the conflictTarget columns (the unique index) — web and Node SDKs:

// Insert-or-merge: if a row with this (user_id, kind) already exists, merge the
// new columns into it; otherwise create it.
await Amba.collections.upsert(
  'preferences',
  { kind: 'theme', value: 'dark' },
  { onConflict: 'update', conflictTarget: ['kind'] },
);

• ignore — if the row already exists, return it unchanged; otherwise insert.
• update — merge the provided columns into the existing row; otherwise insert.

conflictTarget is required; every column you name must be present in the row and covered by a unique index (otherwise 400 INVALID_CONFLICT_TARGET). Upsert stays auto-scoped: an update can only merge into a row you own — if the unique key belongs to another user you get a 409 CONFLICT rather than taking over their row.

On SDKs without a typed upsert method, the same controls are query params on the insert — POST /client/collections/:name?on_conflict=update&conflict_target=kind.

Compare-and-set

Pass an expected map on a single-row update for optimistic concurrency without a transaction — the write applies only if the row still matches every column in expected, otherwise it's 409 PRECONDITION_FAILED and nothing changes (PATCH /client/collections/:name/:id):

// Advance the order only if it's still 'packing' — a concurrent writer that
// already moved it loses the race instead of silently clobbering.
await fetch(`${API}/v1/client/collections/orders/${orderId}`, {
  method: 'PATCH',
  headers: { 'X-Api-Key': apiKey, Authorization: `Bearer ${sessionToken}` },
  body: JSON.stringify({ set: { status: 'shipped' }, expected: { status: 'packing' } }),
});

expected is null-safe: expected: { assignee: null } matches a row whose assignee is currently NULL. The canonical pattern is read → modify → write-back with expected set to the values you read.

Aggregate

Run a grouped count / sum / avg / min / max server-side instead of pulling rows and reducing on the client. Results stay auto-scoped to the signed-in user (POST /client/collections/:name/aggregate):

const res = await fetch(`${API}/v1/client/collections/orders/aggregate`, {
  method: 'POST',
  headers: { 'X-Api-Key': apiKey, Authorization: `Bearer ${sessionToken}` },
  body: JSON.stringify({
    select: [
      { fn: 'count', as: 'total' },
      { fn: 'sum', column: 'amount', as: 'revenue' },
    ],
    group_by: 'status',
  }),
});
const { data } = await res.json();
// data: [{ status: 'paid', total: 12, revenue: 4200 }, …] — one row per group.

count with no column is COUNT(*); pass a column for COUNT(col). sum/avg/min/max require a column. Omit group_by for a whole-collection aggregate. Pass a filter (the same DSL find uses) to scope the rows first.

Patterns

Cursor pagination

For large collections, prefer cursor pagination over offset (it's stable under concurrent inserts and constant-cost per page):

const page1 = await Amba.collections.find('posts', {
  order: [{ column: 'created_at', direction: 'desc' }],
  limit: 50,
});
 
if (page1.has_more) {
  const page2 = await Amba.collections.find('posts', {
    order: [{ column: 'created_at', direction: 'desc' }],
    limit: 50,
    cursor: page1.next_cursor!,
  });
}

Typed reads

Pass a generic to insert / findOne / find so the response is typed end-to-end:

type Post = { id: string; title: string; body: string; created_at: string };
 
const post = await Amba.collections.insert<Post>('posts', { title: 'Hi', body: '' });
// `post.title` is typed string.
 
const { data } = await Amba.collections.find<Post>('posts', { limit: 20 });
// `data` is typed Post[].

React hook reactivity

useCollection re-fetches on mount, on options change, and on auth state change. Use refetch for manual refresh:

const { data, loading, error, refetch } = useCollection<Post>('posts', {
  order: [{ column: 'created_at', direction: 'desc' }],
  limit: 50,
});

Shared collections and seeding

Every collection is per-user by default: each row carries the signed-in user's id, and a client can only see its own rows. For global content that no single user owns — question banks, lookup tables, reference data — create the collection as shared so rows don't need a user_id.

Pass "shared": true to the create endpoint of the admin API, or set shared: true on the amba_collections_create MCP tool:

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": "questions",
    "columns": [{ "name": "prompt", "type": "text" }, { "name": "answer", "type": "text" }],
    "shared": true
  }'

You can also flip an existing collection to shared after the fact with the admin API (relax_user_id). Once a collection is shared, its rows (the ones with no owner) are returned by client SDK reads to every signed-in user — the per-user read filter is widened to also match unowned rows. Per-user rows in a mixed collection still stay private to their owner; only the unowned (shared) rows are globally visible.

Public-read collections (read_policy: "public")

shared only exposes unowned rows. If your catalog rows carry owners — you seeded them through the admin API with a user_id, or migrated per-user data you now want everyone to read — set the collection's access policy instead. read_policy: "public" makes every row readable by every signed-in user, regardless of owner; writes stay owner-scoped unless you also set write_policy: "authenticated". Set it at create time or flip it later with the set_policy PATCH op — no re-import needed:

curl -X PATCH 'https://api.amba.dev/v1/admin/projects/$PROJECT_ID/collections/novels' \
  -H 'Authorization: Bearer $AMBA_PAT' \
  -H 'Content-Type: application/json' \
  -d '{ "set_policy": { "read_policy": "public" } }'

See access policies in the admin reference for the full matrix.

Because the client SDK can never set user_id (the server injects it from the session), shared/global data is seeded through the admin API or MCP tools, not from a client SDK. The fastest path is the bulk-insert endpoint (POST .../rows:batch, or the amba_admin_insert_rows MCP tool), which inserts up to 500 rows per call atomically — use it for migrations and content seeding instead of looping single inserts. See the admin collections reference for the full request shape. Trying to insert into a per-user collection without a user_id returns 400 NOT_NULL_VIOLATION, pointing you at the shared / relax_user_id fix.

Team-owned collections (groups)

Per-user ownership is the default, but real apps often need data owned by a team — a workspace, an org, a shared project — where every member reads the same rows and some can write. Instead of storing shared data under one "system" user and re-checking permissions in your own code, declare the collection group-owned and let Amba enforce membership for you.

A group-owned collection's rows belong to a group (group_id) rather than a single user_id. Access is resolved from the caller's membership in that group, gated by role rank (owner > admin > member > viewer):

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": "team_docs",
    "columns": [{ "name": "title", "type": "text" }, { "name": "body", "type": "text" }],
    "owner_scope": "group",
    "read_policy": "member",
    "write_policy": "member:admin"
  }'

• owner_scope: "group" — rows are owned by a group_id. The collection carries group_id as its owner column (and keeps user_id as the row's creator, for audit).
• read_policy: "member" — any member of the owning group may read. Add a role floor with member:<role> (e.g. member:viewer).
• write_policy: "member:admin" — only members at role admin or higher may write (the classic "members read, admins write"). member alone means any member may write.

Reading and writing as a member

When a signed-in user reads a group-owned collection, Amba automatically returns the rows of every group they belong to (at the read role) — no extra parameters needed. To act within one specific group — required when inserting (so Amba knows which group owns the new row), and useful to narrow reads — scope the call to a group:

// Node (server) SDK — act as a user, within a group:
await amba.asUser(userId).asGroup(groupId).collections.insert('team_docs', {
  title: 'Q3 plan',
  body: '…',
});

On the client SDKs the same is expressed by sending the X-Group-Context: <groupId> header alongside the session token. Membership is verified server-side — a group the caller doesn't belong to is rejected with 403 NOT_A_GROUP_MEMBER, never silently honored, so the header is not a trust boundary you have to defend.

A few rules Amba enforces for you:

• Inserts require a group context (400 GROUP_CONTEXT_REQUIRED otherwise), and the caller's role must meet the write floor (403 GROUP_ROLE_FORBIDDEN).
• Only private groups can own data. A public, open-join group is rejected (400 GROUP_NOT_PRIVATE) — otherwise anyone could join it and read the rows. Create owning groups with is_public: false and an invite/approval join.
• Reads, writes, counts, aggregates, vector search, and transactions are all scoped the same way — there is no path that sees another team's rows.

Manage the owning groups and their members with the groups API or the amba_groups_* MCP tools; change a collection's policy later with the set_policy PATCH op (owner scope is fixed at create time).

Limits

• Max page size: 500 rows per find call. Use cursor pagination for larger result sets.
• Filter depth: and / or / not can nest up to 8 levels deep.
• Row payload size: up to 1 MB per insert / update (JSON-encoded).
• Auto-scoping: client SDKs cannot read or write another user's rows. To act across users from a server context, use amba.asUser(uid) (Node SDK) or the admin API. For team data, use a group-owned collection and amba.asUser(uid).asGroup(groupId).
• Schema is define-once: schemas are managed via the CLI / admin API, not from client SDKs.

Reference

• Client API — collections — endpoint reference.
• CLI: amba collections — schema management.
• Auth feature — prerequisite.
• Per-platform quickstarts: Web, Node, iOS, Android, Flutter, Unity.