Data Model

Design Principles

The schema follows four consistent patterns across all tables.

1. Root + Revision Pattern

Authorable content entities — modules, lessons, assignments, and materials — use a two-table structure:

• A root table holds the stable identity of the entity (ID, class association, created-by, soft-delete columns).
• A revision table holds the versioned content (title, body, configuration) with a visibility_state of DRAFT or PUBLISHED.

This allows Teachers to edit draft content without affecting what students currently see, and to publish when ready.

2. Inline Draft/Published Columns

Grading rows use a different pattern. assignment_grade_rows stores both draft and published values as separate columns on a single row. This supports the operational autosave pattern where a Teacher's in-progress scoring is saved continuously without affecting what students see until explicitly published.

3. Computed Aggregates

Lesson and module grade aggregates are not stored. They are calculated at query time as weighted or simple averages of the underlying assignment grade rows. This avoids stale aggregate state.

4. Soft Delete Everywhere

No product flow performs a hard delete. Every deletable entity has:

• deleted_at — timestamp of deletion
• deleted_by_user_id — who performed the deletion

Rows with a non-null deleted_at are excluded from normal queries but remain in the database for audit purposes.

---

Entity Groups

Identity

Table	Purpose
app_users	Application user records linked to Supabase Auth

Classes

Table	Purpose
classes	Class roots
class_memberships	Student and Teacher enrollment records

Content

Table	Purpose
modules	Module roots
module_revisions	Versioned module content (draft/published)
lesson_units	Lesson roots
lesson_unit_revisions	Versioned lesson content (draft/published)

Materials

Table	Purpose
materials	Material roots (uploaded files)
material_revisions	Versioned material metadata
lesson_unit_materials	Join table linking materials to lessons
material_chunks	Text chunks and vector embeddings for RAG

Assignments

Table	Purpose
assignments	Assignment roots
assignment_revisions	Versioned assignment content and configuration
assignment_submissions	Student submission records

Grading

Table	Purpose
assignment_grade_rows	Per-student grade rows with inline draft and published columns

Chat

Table	Purpose
chats	Chat session records (scoped to student + context)
chat_messages	Individual messages within a chat
chat_message_citations	Source chunk citations attached to assistant messages
chat_message_feedback	Student thumbs-up/down feedback on messages

Admin

Table	Purpose
app_config	Global application configuration key-value store
admin_model_backends	Configured LLM provider connections
admin_models	Individual model definitions within a backend
admin_knowledge_settings	RAG retrieval and knowledge base configuration

Audit

Table	Purpose
audit_logs	Immutable log of auditable events

---

Key Enumerations

Enum	Values
global_role	UNASSIGNED, STUDENT, TEACHER, ADMIN
visibility_state	DRAFT, PUBLISHED
processing_status	PENDING, PROCESSING, READY, FAILED
message_role	USER, ASSISTANT
generation_status	STREAMING, COMPLETED, STOPPED, FAILED
source_mode	LESSON, CLASS, GENERAL

---

PostgreSQL Extensions

The following extensions must be enabled before running migrations:

Extension	Purpose
pgcrypto	UUID generation and cryptographic functions
vector (pgvector)	Vector column type and similarity search operators
citext	Case-insensitive text type (used for email columns)

---

Vector Index

Material chunk embeddings are indexed for approximate nearest-neighbor search:

• Index type: IVFFlat
• Distance metric: Cosine similarity
• Dimension: VECTOR(1536) — matches the OpenAI text-embedding-3-small output dimension. This value is configurable if a different embedding model is used.

When switching embedding models, the vector dimension in the schema and the index must be updated, and all existing embeddings must be re-generated.