Collections

The four collection types (base, auth, view, singleton), how they differ, and when to use each.

A collection is a schema for a kind of record — like a table in a database or a model in an ORM. Vaultbase has four collection types: base, auth, view and singleton. Each has its own storage shape and API surface.

Quick reference

Type	Storage	Records API	Used for
`base`	`vb_<name>` real SQL table	full CRUD	normal records (posts, products, comments…)
`auth`	`vb_<name>` real SQL table + auth columns inline	admin list/edit/delete via records flow; sign-in via `/api/v1/auth/<col>/...`	sign-in identities
`view`	SQLite `VIEW vb_<name>`	read-only — writes return 405	derived/joined data
`singleton`	`vb_<name>` real SQL table holding one record	`GET` / `PATCH /api/v1/<name>` — create and delete return 405	site settings, app config

v0.11 unified auth + base storage — every collection lives in vb_<name>. Auth collections additionally carry inline auth columns (email, password_hash, email_verified, totp_secret, totp_enabled, is_anonymous, password_reset_at).

You set the type when creating a collection from the admin UI. It can’t be changed after creation (would re-create storage and lose data). In particular, a collection can’t be turned into a singleton or out of one: create a new collection and move the data.

A new collection can’t take one of the API’s own path names: admin, auth, files, collections, health, batch, custom, realtime or mcp. Existing collections keep their names.

`base` collections

The default. Each base collection gets its own SQL table named vb_<collection_name> with one column per field, plus id, created_at, updated_at.

-- collection: posts with text "title" + text "body"
CREATE TABLE vb_posts (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  body TEXT,
  created_at INTEGER NOT NULL DEFAULT (unixepoch()),
  updated_at INTEGER NOT NULL DEFAULT (unixepoch())
);

Editing the schema runs ALTER TABLE to add/drop columns. Type changes on an existing field are blocked (422) — drop the field and re-add it instead. Field names id, created, updated, created_at, updated_at, collectionId, collectionName and expand are refused (422) on every collection type. If the table can’t be created, the collection isn’t saved.

`auth` collections

For sign-in identities. Each auth collection has its own vb_<name> table — same as base — plus the auth columns inline:

-- auth collection: users
CREATE TABLE vb_users (
  id TEXT PRIMARY KEY,
  email TEXT NOT NULL,
  password_hash TEXT NOT NULL,
  email_verified INTEGER NOT NULL DEFAULT 0,
  totp_secret TEXT,
  totp_enabled INTEGER NOT NULL DEFAULT 0,
  is_anonymous INTEGER NOT NULL DEFAULT 0,
  password_reset_at INTEGER NOT NULL DEFAULT 0,
  -- + custom fields you defined
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);
CREATE UNIQUE INDEX idx_vb_users_email ON vb_users(email);

Email uniqueness is per-collection — two auth collections can both have a user with the same email (e.g. a vendors and a customers table sharing [email protected] legitimately).

Reserved names you can’t use for your own fields: email, password, verified, tokenKey, password_hash, email_verified, totp_secret, totp_enabled, is_anonymous, password_reset_at.

User-facing endpoints live under /api/v1/auth/<collection>/...:

POST .../register — sign up with email + password
POST .../login — sign in (returns {token, record} or {mfa_required, mfa_token})
POST .../request-verify + .../verify-email — email verification
POST .../request-password-reset + .../confirm-password-reset — password reset
POST .../otp/request + .../otp/auth — magic link / 6-digit code
POST .../totp/setup + .../confirm + .../disable — TOTP MFA
GET .../oauth2/authorize + POST .../oauth2/exchange — OAuth2
POST .../anonymous — guest sessions

Admins manage users via the records flow: GET /api/v1/<col> / PATCH /api/v1/<col>/:id / DELETE /api/v1/<col>/:id. Auth-system columns (password_hash, totp_secret, password_reset_at) never appear in API responses, and PATCH bodies that try to set them are silently stripped. Auth records come back with email_verified, totp_enabled and is_anonymous as 0/1; there is no verified key in API output (rules read it as @request.auth.verified).

JWTs minted by these flows carry a collection: <name> claim — token verification routes lookups to the right per-collection table.

Authentication →

`view` collections

Backed by a SQLite VIEW whose body you supply as a SQL SELECT. Read-only — writes return 405 Method Not Allowed. Useful for joins, aggregates, or projecting subsets of other collections.

-- view collection "post_titles" with this SELECT:
SELECT id, title, created_at AS created
FROM vb_posts
WHERE published = 1

The schema editor renders the SELECT in a Monaco editor with autocomplete for vb_* tables and their columns. Two buttons live beside it:

Validate & refresh columns — re-runs the query at LIMIT 1 to derive field names + sniff types (POST /api/v1/admin/collections/preview-view). Each column is classified from the sample row:
- JS boolean → bool
- integer 0/1 with is_*/has_*/*_enabled name → bool
- 10-digit unix timestamp with *_at suffix → date
- ISO date string → date
- {...} / [...] or JS object → json
- email regex → email
- ^https?:// → url
- else → text (also the fallback when the query returns no rows)
You can override any inferred type per-field afterwards.
Preview 5 rows — runs the query at LIMIT 5 and renders a compact table inline so you can sanity-check the output before saving (POST /api/v1/admin/collections/preview-view-rows { view_query, limit? }, clamps limit to [1, 100], default 5).

Parameters

A view’s query can take values from the request: write :name where a value goes, and declare each parameter on the collection (the parameters table under the query in the admin, or view_params through the API).

-- view collection "customer_revenue"
SELECT o.customer AS id, COUNT(*) AS orders, SUM(o.total) AS revenue
FROM vb_orders o
WHERE o.created_at >= :since AND o.status = :status
GROUP BY o.customer

curl "https://api.example.com/api/v1/customer_revenue?params[since]=2026-10-01&params[status]=paid&sort=-revenue"

That’s what a filter can’t do: a filter applies to the rows a view returns, after the GROUP BY, when the date is already gone.

Each parameter has a type and one of three ways to get its value:

	Value
a default	used when the request doesn’t pass the parameter (none: `NULL`)
`required`	a read without it gets `400`
`from: "auth.id"`	the signed-in user’s id (any signed-in account, admin included); clients can’t set it, and it’s `NULL` for a guest

"view_params": [
  { "name": "since",  "type": "date", "default": "2026-01-01" },
  { "name": "status", "type": "text", "default": "paid" },
  { "name": "me",     "type": "text", "from": "auth.id" }
]

Types: text, number, bool (stored as 1/0), date (ISO 8601 or Unix seconds; compared as Unix seconds, like date fields).
Where they apply: list, get-one, _aggregate and the CSV export take params[name]. Saving checks that every :name is declared and every declared parameter is used.
Safety: values are always bound, each behind a cast to its type; they never become part of the SQL. They’re client input like filter: they choose what the query computes, and don’t grant access. list_rule and view_rule still filter the rows that come back, and can read the values the query used as @request.query.params.<name> (defaults and the signed-in user’s id included).
Other readers: other views, @collection.<view> in rules and relations pointing at the view read it with the defaults, and the view’s rules there see those same values, not the request’s params[...]. Batch requests and nearVector refuse a view with parameters; read it directly. A batch whose URL carries params[...] is refused too (400), since no op in it reads them.
LIMIT: a number parameter is a floating-point number. Write LIMIT CAST(:n AS integer).
Limits: 20 parameters, names up to 32 characters (lowercase letters, digits, _), values up to 1,000 characters.

Like every collection, a view’s rules default to "" (admin-only) — doubly important here, since a view’s SQL can read any table. Open them up explicitly via the admin UI or the API. View queries can’t reference SQLite metadata (sqlite_master, sqlite_schema, pragma_*()), read vaultbase_* tables, use U&"…" names or run SQL from a string. Over tables with private columns they also can’t serialize whole rows or rename columns with an alias list. See the full list. A change to a table a view reads is refused with 409 while the view would expose a private column of it. An existing view that breaks these keeps working, but altering or renaming it needs a compliant query.

Singleton collections

A singleton collection holds exactly one record. Use it for things there is only one of: site settings, app config, a maintenance banner. Its fields are defined like a base collection’s.

The record is created together with the collection, with every field empty. There are no per-field defaults, so a required field is also empty until the first save. Required fields are enforced on writes: a PATCH can’t set one to empty (422). You read and update it at the collection’s own path, without an id:

# read
curl https://api.example.com/api/v1/settings
# → { "data": { "id": "…", "site_name": null, "theme": null, … } }

# update — only the fields you send change
curl -X PATCH https://api.example.com/api/v1/settings \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{ "site_name": "My site", "theme": "dark" }'

With the SDK (0.7.0 and later):

const settings = await vb.singleton("settings").get();
await vb.singleton("settings").update({ theme: "dark" });

GET returns { data: record } with an ETag; PATCH takes If-Match. GET and PATCH on /api/v1/<name>/<id> work too.
The record can’t be created or deleted: POST /api/v1/<name> and DELETE /api/v1/<name>/<id> answer 405. The same holds in batches, hooks and MCP.
Only view_rule and update_rule apply. The list, create and delete rules are ignored.
If the record is removed with raw SQL, the server creates it again, empty, on the next read.
Updates go out over realtime like any record’s.

In the admin UI, pick Singleton as the type when creating the collection. Its records page shows the one record as a form instead of a table.

Records API → Singletons

Records meta

Every record returned by the API includes:

{
  "id": "...",                  // text primary key (UUID)
  "collectionId": "...",        // collection's id
  "collectionName": "posts",
  "created": 1730000000,        // unix seconds
  "updated": 1730000000,
  // ...your fields
}

Field values follow the type encoding rules — see Fields & validation.