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.

`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 bob@example.com 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).

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_*()).

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.