lms

Tables (24)

assignments

  • id — INT
  • offering_id — INT
  • title — VARCHAR(255) — NOT NULL
  • description — TEXT
  • total_points — DECIMAL(8,2)
  • due_at — TIMESTAMP
  • allow_late — TINYINT(1)
  • settings — TEXT
  • created_by — INT
  • created_at — TIMESTAMP

attendance

  • id — INT
  • offering_id — INT
  • user_id — INT
  • lecture_id — INT
  • status — VARCHAR(20) — NOT NULL
  • recorded_at — TIMESTAMP
  • record_meta — TEXT

audit_logs

  • id — BIGINT
  • performed_by — INT
  • action — VARCHAR(200) — NOT NULL
  • target_table — VARCHAR(200)
  • target_id — INT
  • before_state — TEXT
  • after_state — TEXT
  • ip_addr — VARCHAR(100)
  • created_at — TIMESTAMP

auth_tokens

  • token — VARCHAR(100)
  • user_id — INT
  • issued_at — TIMESTAMP
  • expires_at — TIMESTAMP
  • device_info — TEXT

contents

  • id — INT
  • lecture_id — INT
  • file_id — INT
  • content_type — VARCHAR(50) — NOT NULL
  • title — VARCHAR(255)
  • duration_seconds — INT
  • version — INT
  • published — TINYINT(1)
  • created_at — TIMESTAMP
  • extra — TEXT

course_offerings

  • id — INT
  • course_id — INT — NOT NULL
  • term — VARCHAR(50) — NOT NULL
  • instructor_id — INT
  • sis_external_id — VARCHAR(100)
  • settings — TEXT
  • created_at — TIMESTAMP

courses

  • id — INT
  • course_code — VARCHAR(50) — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • description — TEXT
  • credit — INT
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

enrollments

  • id — INT
  • offering_id — INT — NOT NULL
  • user_id — INT — NOT NULL
  • role — VARCHAR(20) — NOT NULL
  • enrolled_at — TIMESTAMP
  • status — VARCHAR(20)

exam_answers

  • id — INT
  • attempt_id — INT
  • question_id — INT
  • answer — TEXT
  • points_awarded — DECIMAL(6,2)

exam_attempts

  • id — INT
  • exam_id — INT
  • user_id — INT
  • started_at — TIMESTAMP
  • finished_at — TIMESTAMP
  • score — DECIMAL(8,2)
  • status — VARCHAR(20)
  • proctoring_log_id — INT
  • meta — TEXT

exam_questions

  • id — INT
  • exam_id — INT
  • question_id — INT
  • points — DECIMAL(6,2)
  • position — INT

exams

  • id — INT
  • offering_id — INT
  • title — VARCHAR(255)
  • description — TEXT
  • start_at — TIMESTAMP
  • end_at — TIMESTAMP
  • duration_minutes — INT
  • proctoring_required — TINYINT(1)
  • settings — TEXT
  • created_by — INT
  • created_at — TIMESTAMP

file_objects

  • id — INT
  • storage_key — VARCHAR(500) — NOT NULL
  • url — VARCHAR(1000)
  • mime_type — VARCHAR(100)
  • size — BIGINT
  • uploaded_by — INT
  • uploaded_at — TIMESTAMP
  • metadata — TEXT

grades

  • id — INT
  • offering_id — INT
  • user_id — INT
  • source_type — VARCHAR(50)
  • source_id — INT
  • points — DECIMAL(8,2)
  • recorded_by — INT
  • recorded_at — TIMESTAMP

learning_progress

  • id — INT
  • user_id — INT
  • content_id — INT
  • play_position — INT
  • duration — INT
  • completed — TINYINT(1)
  • last_seen — TIMESTAMP

lectures

  • id — INT
  • offering_id — INT — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • description — TEXT
  • position — INT
  • created_at — TIMESTAMP

notifications

  • id — INT
  • user_id — INT
  • title — VARCHAR(255)
  • body — TEXT
  • payload — TEXT
  • is_read — TINYINT(1)
  • created_at — TIMESTAMP

proctoring_logs

  • id — INT
  • attempt_id — INT
  • file_id — INT
  • flagged — TINYINT(1)
  • details — TEXT
  • created_at — TIMESTAMP

question_bank

  • id — INT
  • author_id — INT
  • type — VARCHAR(50)
  • content — TEXT
  • choices — TEXT
  • answer — TEXT
  • metadata — TEXT
  • created_at — TIMESTAMP

roles

  • id (Key) — Primary key — NOT NULL
  • name — VARCHAR(50) — NOT NULL

submissions

  • id — INT
  • assignment_id — INT
  • user_id — INT
  • submitted_at — TIMESTAMP
  • file_id — INT
  • text_submission — TEXT
  • status — VARCHAR(20)
  • grade — DECIMAL(8,2)
  • feedback — TEXT
  • meta — TEXT

sync_jobs

  • id — INT
  • job_type — VARCHAR(100)
  • status — VARCHAR(50)
  • started_at — TIMESTAMP
  • finished_at — TIMESTAMP
  • detail — TEXT

system_configs

  • key — VARCHAR(200)
  • value — TEXT
  • updated_at — TIMESTAMP

users

  • id — INT
  • username — VARCHAR(100) — NOT NULL
  • email — VARCHAR(255)
  • full_name — VARCHAR(200)
  • password_hash — TEXT — NOT NULL
  • role_id — INT — NOT NULL
  • is_active — TINYINT(1) — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • metadata — TEXT