Database Schema

Overview

Campus uses PocketBase with an embedded SQLite database. The schema is version-controlled through migration files in pb_migrations/, with each migration defining collections, fields, indexes, and API access rules.

Entity Relationship Diagram

Core Collections

users (Built-in PocketBase Auth Collection)

Purpose: Authenticated user accounts Custom Fields Added:

Field	Type	Description	Migration
`is_admin`	bool	Admin flag for moderation	1758649100
`prefersDarkMode`	bool	UI theme preference	1759885001
`locale`	text	User language/locale	1760800000

Built-in Fields:

id, email, username, avatar, verified, created, updated

Access Rules:

Managed by PocketBase authentication system
Public view allowed for basic profile info (1764200000)

spaces

Collection ID: pbc_3929545014
Migration: 1758629337_created_spaces.js Purpose: Top-level community organizations (e.g., departments, faculties) Schema:

{
  id: "text(15)",           // Auto-generated
  name: "text",             // Required
  slug: "text",             // Required, URL-friendly identifier
  description: "text",      // Optional
  avatar: "file",           // Optional image
  isPublic: "bool",         // Required
  owners: "relation[]",     // users (required, multiple)
  moderators: "relation[]", // users (optional, multiple)
  created: "autodate",
  updated: "autodate"
}

Access Rules:

List/View: Authenticated users
Create: Authenticated users (become owner)
Update/Delete: Space owners only

Example:

Name: "Computer Science Department"
Slug: "cs-dept"
isPublic: true

groups

Collection ID: pbc_3346940990
Migration: 1758629345_created_groups.js Purpose: Sub-communities within spaces (e.g., study groups, courses) Schema:

{
  id: "text(15)",
  space: "relation",        // Required, links to spaces
  name: "text",             // Required
  description: "text",      // Optional
  isPublic: "bool",         // Required
  moderators: "relation[]", // users (optional)
  created: "autodate",
  updated: "autodate"
}

Access Rules:

List/View: Authenticated users
Create: Authenticated users
Update/Delete: Space owners only

Hierarchy: Space → Groups

posts

Collection ID: pbc_1125843985
Migration: 1758629354_created_posts.js + updates Purpose: User-generated content (feed posts) Schema:

{
  id: "text(15)",
  author: "relation",        // users (required, single)
  content: "text",           // Required
  attachments: "file[]",     // Optional (multiple files)
  scope: "select",           // Required: "global" | "space" | "group"
  space: "relation",         // Optional, if scope="space"
  group: "relation",         // Optional, if scope="group"
  likeCount: "number",       // Cached counter
  commentCount: "number",    // Cached counter
  created: "autodate",
  updated: "autodate"
}

Access Rules:

Create: Author must be authenticated user
Update: Author only
Delete: Author OR space/group owners/moderators (1758630000)

Scopes:

global - Visible to all users
space - Visible to space members
group - Visible to group members

Media Support: Migration 1758671000_extend_posts_for_media.js added attachments field

comments

Collection ID: pbc_533777971
Migration: 1758629362_created_comments.js Purpose: Replies to posts Schema:

{
  id: "text(15)",
  post: "relation",          // posts (required)
  author: "relation",        // users (required)
  content: "text",           // Required
  parent: "relation",        // comments (optional, for nested replies)
  created: "autodate",
  updated: "autodate"
}

Access Rules:

Create: Author must be authenticated
Update: Author only
Delete: Author OR post’s space/group moderators

Features:

Threaded comments via parent field (1758629400)
Cascading delete when post is deleted

likes

Collection ID: pbc_2190274710
Migration: 1758629368_created_likes.js Purpose: Track post likes/reactions Schema:

{
  id: "text(15)",
  post: "relation",          // posts (required)
  user: "relation",          // users (required)
  created: "autodate",
  updated: "autodate"
}

Access Rules:

Create: User can like as themselves
Delete: User can unlike their own

Constraints:

Unique per user+post (enforced by database logic)

Membership Collections

space_members

Collection ID: pbc_540001234
Migration: 1758629500_created_space_members.js Purpose: Track user membership and roles in spaces Schema:

{
  id: "text(15)",
  space: "relation",         // spaces (required)
  user: "relation",          // users (required)
  role: "select",            // "member" | "moderator" | "owner"
  created: "autodate",
  updated: "autodate"
}

Indexes:

idx_space_user_unique - Prevents duplicate memberships

Access Rules:

Create: User joins public spaces OR invited by owner
Update: Space owners/moderators (for role changes)
Delete: User leaves OR removed by owners/moderators

group_members

Collection ID: pbc_640001234
Migration: 1758629600_created_group_members.js Purpose: Track user membership and roles in groups Schema:

{
  id: "text(15)",
  group: "relation",         // groups (required)
  user: "relation",          // users (required)
  role: "select",            // "member" | "moderator"
  created: "autodate",
  updated: "autodate"
}

Indexes:

idx_group_user_unique - Prevents duplicate memberships

Access Rules:

Create: User joins public groups OR space owner invites
Update: Group moderators OR space owners
Delete: User leaves OR removed by moderators

Academic Collections

profiles

Collection ID: pbc_profiles_001
Migration: 1758674000_create_profiles.js Purpose: Extended academic profiles for users Schema:

{
  id: "text(15)",
  user: "relation",          // users (required, unique)
  displayName: "text",       // Required
  department: "text",        // Required
  role: "select",            // "student" | "professor" | "researcher" | "staff"
  biography: "text",         // Optional
  pronouns: "text",          // Optional
  links: "json",             // Optional (social links, website, etc.)
  created: "autodate",
  updated: "autodate"
}

Indexes:

idx_profiles_user - Unique per user
idx_profiles_department_role - Search by department/role

Access Rules:

View: All authenticated users
Update: Profile owner OR admin

Note: Fields made optional in 1764108182_make_profile_fields_optional.js

publication_records

Collection ID: pbc_publication_records_001
Migration: 1758674000_create_profiles.js Purpose: Research publications and papers Schema:

{
  id: "text(15)",
  title: "text",             // Required
  doi: "text",               // Optional, Digital Object Identifier
  slugHash: "text",          // Required, unique identifier
  abstract: "text",          // Optional
  year: "number",            // Optional (1900-2100)
  venue: "text",             // Optional (journal/conference name)
  authors: "json",           // Optional (array of author objects)
  created: "autodate",
  updated: "autodate"
}

Indexes:

idx_publications_slug - Unique slug
idx_publications_doi - Unique DOI (if provided)

profile_publications (Join Table)

Collection ID: pbc_profile_publications_001
Migration: 1758674000_create_profiles.js Purpose: Link profiles to publications with contribution role Schema:

{
  id: "text(15)",
  profile: "relation",       // profiles (required)
  publication: "relation",   // publication_records (required)
  contributionRole: "select", // "author" | "editor" | "advisor"
  created: "autodate",
  updated: "autodate"
}

Indexes:

idx_profile_publications_unique - Prevent duplicate links

events

Collection ID: pbc_events_001
Migration: 1758673000_create_events.js Purpose: Calendar events (deadlines, meetings, seminars) Schema:

{
  id: "text(15)",
  title: "text",             // Required
  description: "text",       // Optional
  scopeType: "select",       // "global" | "space" | "group" | "course"
  scopeId: "text",           // Optional, ID of related scope entity
  start: "date",             // Required (ISO 8601)
  end: "date",               // Required
  location: "json",          // Optional (address, room, online link)
  reminderLeadMinutes: "number", // Optional (notification timing)
  createdBy: "relation",     // users (required)
  icsUid: "text",            // Optional (iCalendar UID)
  created: "autodate",
  updated: "autodate"
}

Indexes:

idx_events_scope_window - Query by scope and date range
idx_events_createdBy - User’s created events

event_participants

Collection ID: pbc_event_participants_001
Migration: 1758673000_create_events.js Purpose: RSVP status for events Schema:

{
  id: "text(15)",
  event: "relation",         // events (required, cascade delete)
  user: "relation",          // users (required)
  status: "select",          // "going" | "maybe" | "declined"
  created: "autodate",
  updated: "autodate"
}

Indexes:

idx_event_participants_unique - One RSVP per user per event
idx_event_participants_user - User’s RSVPs

materials

Collection ID: pbc_4282183725
Migration: 1759878990_created_materials.js Purpose: Educational resources and files Schema:

{
  id: "text(15)",
  uploader: "relation",      // users (required)
  title: "text",             // Required
  description: "text",       // Optional
  courseCode: "text",        // Optional (e.g., "CS101")
  tags: "json",              // Optional (array of strings)
  format: "select",          // "document" | "slide" | "dataset" | "video" | "link"
  file: "file[]",            // Optional (uploaded files)
  linkUrl: "url",            // Optional (external link)
  visibility: "select",      // "institution" | "course" | "group" | "public"
  keywords: "text",          // Optional (for search)
  searchTerms: "text",       // Generated (normalized search)
  created: "autodate",
  updated: "autodate"
}

Access Rules:

Create: Authenticated uploader
View: Based on visibility level
Update/Delete: Uploader only

material_access_logs

Collection ID: Not specified
Migration: 1759878997_created_material_access_logs.js Purpose: Track material downloads/views for analytics

Moderation Collections

reports

Collection ID: pbc_reports_001
Migration: 1758630000_created_reports_and_moderation.js Purpose: User-submitted content reports Schema:

{
  id: "text(15)",
  reporter: "relation",      // users (required)
  targetType: "select",      // "post" | "comment"
  targetId: "text(15)",      // ID of reported content
  reason: "text",            // Required explanation
  status: "select",          // "open" | "reviewing" | "resolved" | "dismissed"
  created: "autodate",
  updated: "autodate"
}

Indexes:

idx_reports_target - Query by target type/ID

moderation_logs

Collection ID: pbc_modlogs_001
Migration: 1758630000_created_reports_and_moderation.js Purpose: Audit log of moderation actions Schema:

{
  id: "text(15)",
  actor: "relation",         // users (required, who took action)
  action: "select",          // "delete_post" | "delete_comment" | "resolve_report" | "dismiss_report"
  meta: "json",              // Optional (additional context)
  created: "autodate"
}

Access Rules:

Create: Moderators/admins
Update/Delete: Immutable (empty rules)

notifications

Collection ID: Not specified
Migration: 1758645000_created_notifications.js Purpose: User notifications (likes, comments, mentions) Schema:

{
  id: "text(15)",
  user: "relation",          // users (required, recipient)
  actor: "relation",         // users (required, who triggered)
  type: "select",            // "like" | "comment" | "mention"
  post: "relation",          // posts (optional)
  comment: "relation",       // comments (optional)
  read: "bool",              // Required
  created: "autodate",
  updated: "autodate"
}

Indexes:

idx_notifications_user_read - Unread notifications query
idx_notifications_user_created - Recent notifications

Access Rules:

View/Update/Delete: Notification owner only
Create: System only (null rule)

analytics_events

Collection ID: Not specified
Migration: 1758649000_created_analytics_events.js Purpose: Track user actions for analytics

Performance Indexes

Migration: 1758635000_add_performance_indexes.js Additional indexes added for query optimization:

Post queries by scope
Comment queries by post
User activity timelines
Membership lookups

Database File Location

pb_data/
├── data.db              # Main SQLite database
├── data.db-shm         # Shared memory file
├── data.db-wal         # Write-ahead log
└── storage/            # Uploaded files
    ├── posts/
    ├── materials/
    └── users/

Migration Management

Apply Migrations:

./pocketbase migrate

Create New Migration:

./pocketbase migrate create "description"

Rollback: Migrations include rollback functions (down migrations)

Access Rule Syntax

PocketBase uses a SQL-like syntax for access rules:

// Examples:
"@request.auth.id != ''"                    // Authenticated
"@request.auth.id = author"                 // Is author
"space.owners.id ?= @request.auth.id"       // Is space owner
"@request.body.user = @request.auth.id"     // Creating for self

Operators:

=, != - Equality
?= - Array contains
&&, || - Logical AND/OR
@request.auth - Current user
@request.body - Request payload

Data Integrity

Cascade Deletes:

Deleting a user cascades to their posts, comments, likes
Deleting a space cascades to groups and memberships
Deleting a post cascades to comments and likes

Unique Constraints:

User profiles (one per user)
Space members (one membership per user per space)
Group members (one membership per user per group)
Publication DOIs

Backup Strategy

Recommended:

# Daily backup
cp pb_data/data.db backups/data_$(date +%Y%m%d).db

# Or use PocketBase backup command
./pocketbase backup

Important Files:

pb_data/data.db - Database
pb_data/storage/ - Uploaded files
pb_migrations/ - Version controlled

Architecture

Deployment

API Reference

Database Schema

Overview

Entity Relationship Diagram

Core Collections

users (Built-in PocketBase Auth Collection)

spaces

groups

posts

comments

likes

Membership Collections

space_members

group_members

Academic Collections

profiles

publication_records

profile_publications (Join Table)

events

event_participants

materials

material_access_logs

Moderation Collections

reports

moderation_logs

notifications

analytics_events

Performance Indexes

Database File Location

Migration Management

Access Rule Syntax

Data Integrity

Backup Strategy

Build docs developers (and LLMs) love

Architecture

Deployment

API Reference

​Overview

​Entity Relationship Diagram

​Core Collections

​users (Built-in PocketBase Auth Collection)

​spaces

​groups

​posts

​comments

​likes

​Membership Collections

​space_members

​group_members

​Academic Collections

​profiles

​publication_records

​profile_publications (Join Table)

​events

​event_participants

​materials

​material_access_logs

​Moderation Collections

​reports

​moderation_logs

​notifications

​analytics_events

​Performance Indexes

​Database File Location

​Migration Management

​Access Rule Syntax

​Data Integrity

​Backup Strategy

Build docs developers (and LLMs) love

Overview

Entity Relationship Diagram

Core Collections

users (Built-in PocketBase Auth Collection)

spaces

groups

posts

comments

likes

Membership Collections

space_members

group_members

Academic Collections

profiles

publication_records

profile_publications (Join Table)

events

event_participants

materials

material_access_logs

Moderation Collections

reports

moderation_logs

notifications

analytics_events

Performance Indexes

Database File Location

Migration Management

Access Rule Syntax

Data Integrity

Backup Strategy