Version: 0.1.1
Date: Jul 22, 2026
Status: In Sync (migration 001–016)
erDiagram
users {
TEXT id PK "UUID v7"
TEXT email UK "unique, not null"
TEXT name "not null"
TEXT avatar_url "nullable"
TEXT role "default: student"
TEXT password_hash "nullable"
INTEGER token_version "default 0 (015)"
INTEGER is_active "default 1 (016)"
TEXT created_at "TIMESTAMP"
TEXT updated_at "TIMESTAMP"
TEXT last_login "TIMESTAMP nullable"
}
user_identities {
TEXT id PK "UUID v7"
TEXT user_id FK "-> users.id"
TEXT provider "google | github | keycloak"
TEXT provider_id "provider-specific"
TEXT created_at "TIMESTAMP"
}
organizations {
TEXT id PK "UUID v7"
TEXT slug UK "unique"
TEXT name "not null"
TEXT logo_url "nullable"
TEXT config "JSON nullable"
TEXT created_at "TIMESTAMP"
TEXT updated_at "TIMESTAMP"
}
user_organizations {
TEXT user_id FK "-> users.id"
TEXT org_id FK "-> organizations.id"
TEXT role "owner | admin | member"
TEXT created_at "TIMESTAMP"
}
courses {
TEXT id PK "UUID v7"
TEXT org_id FK "-> organizations.id"
TEXT author_id FK "-> users.id"
TEXT name "not null"
TEXT description "nullable"
TEXT thumbnail_image "nullable"
BOOLEAN published "default false"
BOOLEAN public "default false"
TEXT created_at "TIMESTAMP"
TEXT updated_at "TIMESTAMP"
}
chapters {
TEXT id PK "UUID v7"
TEXT course_id FK "-> courses.id (denormalized)"
TEXT name "not null"
TEXT description "nullable"
INTEGER sort_order "not null"
TEXT created_at "TIMESTAMP"
TEXT updated_at "TIMESTAMP"
}
course_chapters {
TEXT course_id FK "-> courses.id"
TEXT chapter_id FK "-> chapters.id"
INTEGER sort_order "not null"
}
activities {
TEXT id PK "UUID v7"
TEXT chapter_id FK "-> chapters.id (denormalized)"
TEXT course_id FK "-> courses.id (denormalized)"
TEXT name "not null"
TEXT activity_type "TYPE_DYNAMIC default"
TEXT activity_sub_type "nullable"
TEXT content "JSON nullable (TipTap)"
TEXT content_blocks "JSON nullable"
BOOLEAN published "default false"
TEXT lock_type "public default"
TEXT created_at "TIMESTAMP"
TEXT updated_at "TIMESTAMP"
}
chapter_activities {
TEXT chapter_id FK "-> chapters.id"
TEXT activity_id FK "-> activities.id"
INTEGER sort_order "not null"
}
media {
TEXT id PK "UUID v7"
TEXT org_id FK "-> organizations.id"
TEXT filename "not null"
TEXT original_name "not null"
TEXT mime_type "not null"
INTEGER size_bytes "not null"
TEXT url "not null"
TEXT storage_type "default local"
TEXT uploaded_by FK "-> users.id"
TEXT created_at "TIMESTAMP"
TEXT updated_at "TIMESTAMP"
}
api_tokens {
TEXT id PK "UUID v7"
TEXT user_id FK "-> users.id"
TEXT token_hash "not null"
TEXT name "not null"
TEXT scope "read default"
TEXT last_used_at "nullable"
TEXT created_at "TIMESTAMP"
TEXT expires_at "nullable"
}
webhooks {
TEXT id PK "UUID v7"
TEXT org_id FK "-> organizations.id"
TEXT url "not null"
TEXT secret "not null"
TEXT events "JSON"
BOOLEAN active "default true"
TEXT created_at "TIMESTAMP"
}
folders {
TEXT id PK "UUID v7"
TEXT org_id FK "-> organizations.id"
TEXT parent_id FK "-> folders.id (self-ref, nullable)"
TEXT name "not null"
TEXT description "nullable"
INTEGER sort_order "default 0"
TEXT created_at "TIMESTAMP"
TEXT updated_at "TIMESTAMP"
}
user_groups {
TEXT id PK "UUID v7"
TEXT org_id FK "-> organizations.id"
TEXT name "not null"
TEXT description "nullable"
TEXT created_at "TIMESTAMP"
TEXT updated_at "TIMESTAMP"
}
user_group_members {
TEXT group_id FK "-> user_groups.id"
TEXT user_id FK "-> users.id"
TEXT role "default member"
TEXT created_at "TIMESTAMP"
}
course_access {
TEXT group_id FK "-> user_groups.id"
TEXT course_id FK "-> courses.id"
TEXT access_level "default view"
}
enrollments {
TEXT id PK "UUID v7"
TEXT course_id FK "-> courses.id"
TEXT user_id FK "-> users.id"
TEXT status "active|suspended|completed|dropped"
TEXT enrolled_at "TIMESTAMP"
TEXT completed_at "nullable"
}
activity_progress {
TEXT id PK "UUID v7"
TEXT enrollment_id FK "-> enrollments.id"
TEXT activity_id FK "-> activities.id"
TEXT status "not_started|in_progress|completed"
INTEGER score "nullable"
INTEGER time_spent_seconds "nullable"
TEXT started_at "nullable"
TEXT completed_at "nullable"
}
communities {
TEXT id PK "UUID v7"
TEXT course_id FK "-> courses.id"
TEXT author_id FK "-> users.id"
TEXT title "not null"
TEXT body "default empty"
TEXT created_at "TIMESTAMP"
TEXT updated_at "TIMESTAMP"
}
community_replies {
TEXT id PK "UUID v7"
TEXT thread_id FK "-> communities.id"
TEXT author_id FK "-> users.id"
TEXT body "default empty"
TEXT created_at "TIMESTAMP"
TEXT updated_at "TIMESTAMP"
}
assignments {
TEXT id PK "UUID v7"
TEXT activity_id FK "-> activities.id"
TEXT title "not null"
TEXT description "default empty"
TEXT due_date "nullable"
INTEGER max_score "default 100"
TEXT created_at "TIMESTAMP"
TEXT updated_at "TIMESTAMP"
}
submissions {
TEXT id PK "UUID v7"
TEXT assignment_id FK "-> assignments.id"
TEXT enrollment_id FK "-> enrollments.id"
TEXT content "default empty"
INTEGER score "nullable"
TEXT graded_at "nullable"
TEXT submitted_at "TIMESTAMP"
}
courses_fts {
TEXT course_id "FTS5 virtual table"
TEXT name
TEXT description
}
activities_fts {
TEXT activity_id "FTS5 virtual table"
TEXT name
TEXT content
}
users ||--o{ user_identities : "has"
users ||--o{ user_organizations : "belongs to"
organizations ||--o{ user_organizations : "has members"
organizations ||--o{ courses : "owns"
users ||--o{ courses : "authors"
courses ||--o{ course_chapters : "contains"
chapters ||--o{ course_chapters : "belongs to"
courses ||--o{ chapters : "has (denormalized)"
chapters ||--o{ chapter_activities : "contains"
activities ||--o{ chapter_activities : "belongs to"
chapters ||--o{ activities : "has (denormalized)"
courses ||--o{ activities : "has (denormalized)"
organizations ||--o{ media : "stores"
users ||--o{ media : "uploads"
users ||--o{ api_tokens : "owns"
organizations ||--o{ webhooks : "configures"
organizations ||--o{ folders : "organizes"
folders ||--o{ folders : "nested (self-ref)"
organizations ||--o{ user_groups : "manages"
user_groups ||--o{ user_group_members : "has members"
users ||--o{ user_group_members : "joins"
user_groups ||--o{ course_access : "grants"
courses ||--o{ course_access : "accessed by groups"
courses ||--o{ enrollments : "has enrollments"
users ||--o{ enrollments : "enrolled in"
enrollments ||--o{ activity_progress : "tracks"
activities ||--o{ activity_progress : "progress"
courses ||--o{ communities : "has discussions"
users ||--o{ communities : "creates threads"
communities ||--o{ community_replies : "has replies"
users ||--o{ community_replies : "replies"
activities ||--o{ assignments : "extends"
assignments ||--o{ submissions : "receives"
enrollments ||--o{ submissions : "submits"
courses ||--|| courses_fts : "synced via triggers"
activities ||--|| activities_fts : "synced via triggers"
| Relationship | Cardinality | Notes |
|---|---|---|
| User → UserIdentities | 1:N | One user can link multiple OAuth providers |
| User → UserOrganizations | 1:N | One user can belong to multiple orgs |
| Organization → UserOrganizations | 1:N | One org has many members |
| Organization → Courses | 1:N | Courses belong to an org |
| User (author) → Courses | 1:N | User can author many courses |
| Course → CourseChapters → Chapters | M:N | Ordered via sort_order |
| Chapter → ChapterActivities → Activities | M:N | Ordered via sort_order |
| Chapter → Activities (denormalized) | 1:N | Convenience FK for direct queries |
| Course → Activities (denormalized) | 1:N | Convenience FK for direct queries |
| Organization → Media | 1:N | Media scoped to org |
| User → Media | 1:N | Track who uploaded |
| User → ApiTokens | 1:N | Bearer tokens for headless access |
| Organization → Webhooks | 1:N | Per-org event subscriptions |
| Organization → Folders | 1:N | Hierarchical folders, self-ref via parent_id |
| Organization → UserGroups | 1:N | Group-based access control |
| UserGroup → UserGroupMembers → Users | M:N | Group membership with role |
| UserGroup → CourseAccess → Courses | M:N | Group-level course access |
| Course → Enrollments → Users | M:N | Unique per course+user, with status |
| Enrollment → ActivityProgress → Activities | M:N | Per-activity progress tracking |
| Course → Communities | 1:N | Discussion threads per course |
| Community → CommunityReplies | 1:N | Flat replies (not nested) |
| User → CommunityReplies | 1:N | Reply authorship |
| Activity → Assignments | 1:1 | Assignment extends activity with due date/scoring |
| Assignment → Submissions | 1:N | One submission per assignment+enrollment |
| Courses → courses_fts | 1:1 | FTS5 virtual table, synced via triggers |
| Activities → activities_fts | 1:1 | FTS5 virtual table, synced via triggers |
All migrations in server/migrations/ directory. Naming: {NNN}_{description}.sql.
| # | Migration | Tables |
|---|---|---|
| 001 | Create users | users, user_identities |
| 002 | Create organizations | organizations, user_organizations |
| 003 | Create courses | courses |
| 004 | Create chapters | chapters, course_chapters |
| 005 | Create activities | activities, chapter_activities |
| 006 | Create api_tokens | api_tokens |
| 007 | Create media | media |
| 008 | Create webhooks | webhooks |
| 009 | Create folders | folders |
| 010 | Create user groups | user_groups, user_group_members, course_access |
| 011 | Create enrollments | enrollments, activity_progress |
| 012 | Create communities | communities, community_replies |
| 013 | Create assignments | assignments, submissions |
| 014 | Create FTS5 | courses_fts, activities_fts (virtual tables + triggers) |
| 015 | Add token_version | users ALTER (JWT revocation) |
| 016 | Add is_active | users ALTER (account status) |
| Feature | SQLite | PostgreSQL |
|---|---|---|
| UUID generation | uuid v7 via Rust (not DB) | uuid_generate_v7() |
| JSON | TEXT column, serde_json in Rust | JSONB native |
| BOOLEAN | INTEGER 0/1 | BOOLEAN native |
| TIMESTAMP | TEXT (ISO 8601) | TIMESTAMPTZ |
| FTS (search) | FTS5 virtual table | tsvector + GIN index |
| AI (pgvector) | Not supported | pgvector extension |
Migration strategy: Use SQLite-compatible SQL syntax for all migrations. When switching to PostgreSQL, run sqlx database create with Postgres URL. Schema is designed to work on both — only minor syntax differences handled by sqlx (e.g., AUTOINCREMENT vs SERIAL handled by sqlx).