PocketBase Collection & Schema Design

Collection Types

Base Collection

Standard data collection. System fields: id, created, updated.

Auth Collection

Extends base with authentication. Additional system fields: email, emailVisibility, verified, password, tokenKey.

Cannot delete system fields. Can disable email/password auth in collection options.

View Collection

Read-only, backed by a SQL SELECT query. No create/update/delete. Fields are auto-detected from the query. Useful for aggregations, joins, and computed views.

-- Example: view collection query
SELECT p.id, p.title, COUNT(c.id) as comments_count
FROM posts p LEFT JOIN comments c ON c.post = p.id
GROUP BY p.id

View collections support API rules (list/view only) and can be used in relations.

Field Types

Critical: all types default to their zero value, NOT null. Only json fields can be null.

Field Modifiers

Use in collection schema definitions:

• required — field cannot be empty/zero
• unique — unique constraint (composite via unique indexes)
• presentable — included in relation display
• hidden — excluded from API responses unless explicitly requested
• :autogenerate — for text fields: auto-generate value (e.g., slug from other field)

Relation Patterns

One-to-many

posts.author -> users (maxSelect: 1)

Each post has one author. Query posts by author: author = "USER_ID".

Many-to-many

posts.tags -> tags (maxSelect: 0, meaning unlimited)

Multi-select relation. Filter: tags ?= "TAG_ID" (contains).

Back-relations

No explicit back-relation field needed. Use @collection.posts.author in API rules or expand from either side:

GET /api/collections/users/records/USER_ID?expand=posts_via_author

Cascade Delete

Set cascadeDelete: true on the relation field. When the referenced record is deleted, all records pointing to it are also deleted. Default is false (sets to empty string).

Self-referencing

A collection can reference itself:

categories.parent -> categories (maxSelect: 1)

Indexes

• Created in the collection settings (not field-level)
• Format: CREATE [UNIQUE] INDEX idx_name ON collection (field1, field2)
• Unique indexes enforce composite uniqueness
• Partial indexes: CREATE INDEX ... WHERE condition
• Indexes on relation fields improve join performance

Auth Collection Specifics

OAuth2

Enable per-provider in collection settings. Each provider needs client ID + secret. PocketBase handles the full OAuth2 flow.

OTP (One-Time Password)

Enable in auth collection settings. Sends code via email. Configure otp.enabled, otp.duration, otp.length.

MFA (Multi-Factor Authentication)

Enable in auth collection settings. Requires a second factor after primary auth. mfa.enabled, mfa.duration, mfa.rule (filter to determine which users need MFA).

Password Auth

Enabled by default. Can customize minPasswordLength. Can disable entirely if using only OAuth2/OTP.

Auth Options

• authToken.duration — token lifetime (seconds)
• passwordAuth.enabled — toggle email/password
• passwordAuth.identityFields — fields used for login (default: email; can add username)
• oauth2.enabled — toggle OAuth2
• otp.enabled — toggle OTP

Best Practices

1. Prefer select over bool when there might be more than 2 states in the future
2. Use relation not text for foreign keys — you get cascade, expand, and type safety
3. json fields are schemaless — use sparingly, prefer typed fields
4. Name collections in lowercase snake_case (e.g., blog_posts, user_profiles)
5. Index early — add indexes for any field used in filters or sorts
6. maxSelect: 1 on relations returns a string ID; maxSelect: >1 or 0 returns an array