Collections

A collection is a schema for a kind of record — like a table in a database or a model in an ORM. Vaultbase has three collection types: base, auth, and view. 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

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).

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 — drop the field and re-add it instead.

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
• POST .../oauth2/authorize + .../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.

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).

Defaults for view collections lean safe — list_rule and view_rule default to "" (admin-only) since arbitrary SQL can read anything in the database. Open them up explicitly via the admin UI or the API.

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.