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

text

string

""

editor

string

""

number

float64

0

noDecimal

bool

bool

false

email

string

""

url

string

""

date

string

""

2024-01-01 00:00:00.000Z

select

string

[]string

""

[]

values

maxSelect

file

string

[]string

""

[]

maxSelect

maxSize

mimeTypes

relation

string

[]string

""

[]

collectionId

cascadeDelete

maxSelect

json

any

null

maxSize

autodate

string

onCreate

onUpdate

password

string

""

Critical: all types default to their zero value, NOT null. Only

json

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"

Back-relations

No explicit back-relation field needed. Use

@collection.posts.author

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

Cascade Delete

Set

cascadeDelete: true

false

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

Password Auth

Enabled by default. Can customize

minPasswordLength

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