Skip to main content

Database Schema & Models

Overviewโ€‹

EduVision ITS uses PostgreSQL as its primary relational database. The schema is designed to support high-throughput interactions while maintaining relational integrity for complex educational data.

Key extensions used:

  • pgvector: For storing and querying high-dimensional vector embeddings (768 dimensions for all-MiniLM-L6-v2).

๐Ÿ—„๏ธ Core Tables (models_prod.py)โ€‹

These tables handle the administrative and structural aspects of the LMS.

Userโ€‹

  • id (UUID, PK)
  • tenant_id (FK -> Tenant)
  • email (Unique)
  • hashed_password
  • role (Enum: admin, teacher, student)

Courseโ€‹

  • id (UUID, PK)
  • teacher_id (FK -> User)
  • title
  • description
  • is_published (Boolean)

Sessionโ€‹

  • id (UUID, PK)
  • student_id (FK -> User)
  • course_id (FK -> Course)
  • started_at (DateTime)
  • ended_at (DateTime, Nullable)

๐Ÿง  Knowledge Graph (models_knowledge.py)โ€‹

These tables persist the semantic structure of the course material and enable RAG.

KnowledgeSourceโ€‹

  • id (UUID, PK)
  • course_id (FK -> Course)
  • filename (String)
  • created_at (Float)

KnowledgeChunkโ€‹

  • id (UUID, PK)
  • source_id (FK -> KnowledgeSource)
  • text (Text) - The actual educational content.
  • position (Integer) - Order in original document.

KnowledgeEmbeddingโ€‹

  • chunk_id (FK -> KnowledgeChunk)
  • vector (Vector[384]) - PGVector column storing semantic embeddings from sentence-transformers/all-MiniLM-L6-v2.

KnowledgeEdgeโ€‹

  • source_chunk_id (FK)
  • target_chunk_id (FK)
  • relation_type (Enum: requires, part_of, similar_to)

๐Ÿ“ˆ Adaptive Learning (models_adaptive.py)โ€‹

These tables store the probabilistic state of each learner.

LearnerSkill (BKT State)โ€‹

Tracks mastery for a specific skill.

  • student_id (FK)
  • skill_id (String)
  • mastery_probability (Float: 0.0 - 1.0)
  • slip_probability (Float)
  • guess_probability (Float)
  • last_updated (DateTime)

LearnerSchedule (SRS State)โ€‹

Tracks review timing for spaced repetition.

  • student_id (FK)
  • item_id (String)
  • next_review (DateTime)
  • interval_days (Integer)
  • ease_factor (Float)

๐Ÿ“Š Diagnostics (models_diagnostics.py)โ€‹

Used for psychometric analysis (IRT - Item Response Theory).

LearnerThetaโ€‹

  • student_id (FK)
  • theta (Float) - Latent ability estimate.
  • standard_error (Float)

SkillDifficultyโ€‹

  • skill_id (String)
  • difficulty (Float) - Calibrated difficulty parameter.
  • discrimination (Float) - How well the item differentiates ability levels.