Collections

Collections are the primary data model in BunBase. Each collection is a dynamic SQLite table. You don’t define schemas upfront — columns are created on-demand when new fields appear in records.

Creating a Collection

Use the Admin API to create a collection with per-operation access rules:

POST /api/v1/admin/collections
Authorization: Bearer <admin-secret>
Content-Type: application/json

{
  "name": "posts",
  "rules": {
    "read": "public",
    "create": "authenticated",
    "update": "owner",
    "delete": "owner"
  }
}

Access Rules

Each collection has four independent access rules — one per operation:

Operation	Default	When checked
read	"public"	GET /:collection and GET /:collection/:id
create	"authenticated"	POST /:collection
update	"owner"	PATCH /:collection/:id
delete	"owner"	DELETE /:collection/:id

Rule values:

Rule	Description
"public"	Anyone, including unauthenticated requests
"authenticated"	Any logged-in user
"owner"	Must be authenticated and the _owner_id of the record
"disabled"	Operation not permitted for anyone
{ "role": "admin" }	Must be authenticated with the named role (see Roles)

Role-based rule example:

{
  "rules": {
    "read": "public",
    "create": { "role": "editor" },
    "update": { "role": "editor" },
    "delete": { "role": "admin" }
  }
}

Only users with the matching role in their roles array may perform the operation.

CRUD Operations

Create a record

POST /api/v1/posts
Authorization: Bearer <access-token>
Content-Type: application/json

{ "title": "Hello world", "body": "...", "published": true }

Response 201:

{
  "_id": "01JKX...",
  "_created_at": 1709900000000,
  "_updated_at": 1709900000000,
  "_owner_id": "01JKW...",
  "title": "Hello world",
  "body": "...",
  "published": true
}

List records

GET /api/v1/posts?limit=20&sort=_created_at:desc&filter=published:eq:true

Query parameters:

Parameter	Description
limit	Max records per page (default 50, max 500)
page	Page number (1-based, offset pagination)
after	Cursor: _id of last record (keyset pagination, faster than page)
sort	field:asc or field:desc. Multiple: sort=name:asc&sort=_created_at:desc
filter	field:op:value. Op: eq, ne, gt, lt, gte, lte, like, in
fields	Comma-separated field names to return
include_deleted	true to include soft-deleted records
search	Full-text search query (see Full-text search)

Full-text search

BunBase uses SQLite FTS5 to index all text content in a collection. FTS is opt-in — you must create the index before ?search= queries will work.

GET /api/v1/posts?search=hello+world

The search parameter is composable with all other filters, sort, and pagination:

GET /api/v1/posts?search=bun+runtime&filter[status]=published&sort=-_created_at&limit=10

Enabling FTS

Via Studio: Open a collection → click Indexes → scroll to “Full-text search” → click Enable full-text search. Studio shows how many records were indexed.

Via Admin API:

# Check status
GET /api/v1/admin/collections/posts/fts
X-Admin-Token: <admin-token>

# Create index (rebuilds from all existing records)
POST /api/v1/admin/collections/posts/fts
X-Admin-Token: <admin-token>

# Drop index
DELETE /api/v1/admin/collections/posts/fts
X-Admin-Token: <admin-token>

How it works

• Creating the index builds a FTS5 virtual table and indexes all existing records.
• The index is kept up to date on every insert, update, and soft-delete.
• Searches use the Porter stemmer by default — run matches running, runs, etc.
• FTS5 MATCH syntax is supported: prefix queries (bun*), phrase queries ("bun native"), boolean operators (bun AND native).

SDK usage

const results = await client.collection("posts").list({
  search: "hello world",
  filter: { status: "published" },
  sort: "-_created_at",
});

Get a record

GET /api/v1/posts/01JKX...

Update a record

PATCH /api/v1/posts/01JKX...
Authorization: Bearer <access-token>
Content-Type: application/json

{ "title": "Updated title" }

Only the provided fields are updated. System fields (_id, _created_at, etc.) are ignored.

Delete a record (soft delete)

DELETE /api/v1/posts/01JKX...
Authorization: Bearer <access-token>

Soft-deleted records have _deleted_at set and are excluded from list/get by default. To hard-delete, use the Admin API.

Restore a soft-deleted record

POST /api/v1/posts/01JKX.../restore
Authorization: Bearer <access-token>

Count records

GET /api/v1/posts/count?filter=published:eq:true

Returns { "total": 42 }.

Bulk create (atomic)

Insert up to 500 records in a single SQL transaction. If any record fails, none are saved.

POST /api/v1/posts/bulk
Authorization: Bearer <access-token>
Content-Type: application/json

[
  { "title": "Post 1", "published": true },
  { "title": "Post 2", "published": false }
]

Returns a 201 array of the created records.

Aggregate

Compute sum, avg, min, max, or count over a collection, with optional grouping:

GET /api/v1/orders/aggregate?fn=sum&field=amount
→ { "value": 12450 }

GET /api/v1/orders/aggregate?fn=count&group_by=status
→ { "groups": [{ "group": "paid", "value": 80 }, { "group": "pending", "value": 20 }] }

Query parameters:

Parameter	Required	Description
fn	Yes	sum, avg, min, max, count
field	For non-count	Field to aggregate
group_by	No	Group results by this field
filter[...]	No	Same filter syntax as list
include_deleted	No	true to include soft-deleted records

Collection stats

Get the live row count and last write timestamp for a collection:

GET /api/v1/admin/collections/:name/stats
Authorization: Bearer <admin-secret>

Response 200:

{
  "row_count": 1042,
  "last_write": 1709900000000
}

• row_count — number of non-deleted records.
• last_write — Unix milliseconds of the most recent insert or update among non-deleted records. null if the collection is empty.

This endpoint is also used by Studio to display the stats bar on the collection detail page.

Field Validation Rules

Field validation rules let you enforce required fields, value constraints, and regex patterns on a per-collection basis. Rules are evaluated server-side on create and update — validation failures return 422.

Set rules via Admin API

PUT /api/v1/admin/collections/posts/rules
Authorization: Bearer <admin-secret>
Content-Type: application/json

{
  "field_rules": [
    { "field": "title", "required": true, "min": 1, "max": 200 },
    { "field": "status", "enum": ["draft", "published", "archived"] },
    { "field": "score", "min": 0, "max": 100 },
    { "field": "slug", "regex": "^[a-z0-9-]+$" }
  ]
}

Rule fields:

Field	Description
field	Field name (required)
required	Fail if field is missing on create
min	Min value (numbers), min length (strings), min items (arrays)
max	Max value (numbers), max length (strings), max items (arrays)
regex	Regex pattern — strings only
enum	Allowed values — any type

Validation error response

HTTP 422
{
  "errors": [
    { "field": "title", "rule": "required", "message": "\"title\" is required." },
    { "field": "status", "rule": "enum", "message": "\"status\" must be one of: \"draft\", \"published\", \"archived\"." }
  ]
}

For bulk inserts, the response also includes "index" to identify which record failed.

System Fields

Every record includes these system fields:

Field	Type	Description
_id	ULID string	Unique, time-ordered identifier
_created_at	Unix ms	Creation timestamp
_updated_at	Unix ms	Last update timestamp
_owner_id	string or null	User ID of creator
_deleted_at	Unix ms or null	Soft-delete timestamp; null if not deleted

Cursor Pagination

Cursor pagination with after is more efficient than offset pagination for large datasets:

# Page 1
GET /api/v1/posts?limit=20
→ { "items": [...], "next_cursor": "01JKX...", "total": null }

# Page 2
GET /api/v1/posts?limit=20&after=01JKX...

When after is set, page is ignored.

Relations

Collections support typed relations for relational queries:

POST /api/v1/admin/relations
Content-Type: application/json

{
  "from_col": "posts",
  "from_field": "author_id",
  "to_col": "users",
  "type": "one",
  "on_delete": "cascade"
}

Relation types: one (foreign key), many (reverse lookup), many_via (junction table).

Batch operations

Run up to 100 create/update/delete operations atomically in a single request. All operations succeed or all are rolled back.

POST /api/v1/posts/batch
Authorization: Bearer <access-token>
Content-Type: application/json

{
  "operations": [
    { "op": "create", "data": { "title": "Hello" } },
    { "op": "update", "id": "01JKX...", "data": { "status": "published" } },
    { "op": "delete", "id": "01JKW..." }
  ]
}

Response 200:

{
  "results": [
    { "op": "create", "record": { "_id": "01JKY...", "title": "Hello", ... } },
    { "op": "update", "record": { "_id": "01JKX...", "status": "published", ... } },
    { "op": "delete", "id": "01JKW..." }
  ]
}

Error handling

If any operation fails, the entire batch is rolled back and a 400 error is returned:

{ "error": "Operation 1 (update) failed: Record \"01JKX...\" not found." }

Limits

• Maximum 100 operations per batch.
• Permission checks apply per operation using the requester’s identity.
• Lifecycle hooks run for each operation inside the transaction.

SDK usage

const results = await client.collection("posts").batch([
  { op: "create", data: { title: "Hello" } },
  { op: "update", id: "01JKX...", data: { status: "published" } },
  { op: "delete", id: "01JKW..." },
]);