Перейти к основному содержимому

Схема БД LMS

Зачем нужно​

Документ задаёт стартовую SQL-схему LMS. Она не привязана к конкретному ORM, но должна быть достаточной, чтобы реализовать домен с нуля.

Общие правила​

  • Все таблицы имеют id, created_at, updated_at.
  • Все внешние ссылки на identity, CRM и task-bank хранятся как external references.
  • Published course version не редактируется; новая версия создаётся копированием.
  • Soft delete допустим только через archived_at, retired_at, revoked_at или state transition.
  • Для всех ручных изменений учебного состояния пишется audit.

Enum-типы​

create type lms_course_status as enum ('draft', 'review', 'published', 'archived');
create type lms_course_version_status as enum ('draft', 'review', 'published', 'retired');
create type lms_node_type as enum ('module', 'section', 'lesson', 'intensive_day', 'checkpoint', 'project_stage', 'supplement');
create type lms_block_type as enum ('text', 'video', 'file', 'image', 'embed', 'quiz', 'task_bank_ref', 'assignment', 'workbook_prompt', 'project_milestone', 'interactive');
create type lms_enrollment_status as enum ('pending', 'active', 'paused', 'completed', 'revoked');
create type lms_attempt_status as enum ('started', 'submitted', 'checking', 'checked', 'accepted', 'returned', 'cancelled');
create type lms_submission_status as enum ('draft', 'submitted', 'in_review', 'returned', 'accepted', 'reopened');
create type lms_progress_status as enum ('not_started', 'available', 'in_progress', 'ready_for_completion', 'gap', 'completed', 'needs_review', 'enrichment');
create type roadmap_layer as enum ('core', 'supplemental', 'free_entry', 'gap_closing', 'event', 'competition', 'enrichment', 'diagnostic', 'individual');
create type pathway_availability_state as enum ('available', 'not_available', 'enrolled', 'purchased', 'free', 'waitlist', 'closed');
create type pathway_completion_contribution as enum ('none', 'evidence_only', 'partial', 'full_if_rule_allows');
create type lms_booking_status as enum ('available', 'held', 'booked', 'cancelled', 'completed', 'missed');
create type lms_chat_status as enum ('open', 'readonly', 'closed', 'archived');
create type lms_session_status as enum ('planned','confirmed','in_progress','completed','cancelled','rescheduled','missed');
create type lms_attendance_status as enum ('expected','present','absent','late','excused_absence','makeup_planned','makeup_completed','cancelled');
create type learning_group_status as enum ('draft', 'active', 'archived');
create type learning_group_participant_status as enum ('invited', 'active', 'paused', 'removed');
create type learning_group_invite_status as enum ('created', 'sent', 'accepted', 'expired', 'cancelled', 'revoked');
create type learning_group_assignment_status as enum ('draft', 'assigned', 'in_progress', 'completed', 'cancelled', 'expired');

Courses​

create table lms_courses (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  title text not null,
  description text,
  subject_key text not null,
  status lms_course_status not null default 'draft',
  visibility text not null default 'private',
  default_locale text not null default 'ru',
  created_by_user_id uuid not null,
  archived_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index lms_courses_subject_idx on lms_courses(subject_key);
create index lms_courses_status_idx on lms_courses(status);

Course versions​

create table lms_course_versions (
  id uuid primary key default gen_random_uuid(),
  course_id uuid not null references lms_courses(id),
  version int not null,
  status lms_course_version_status not null default 'draft',
  source_version_id uuid references lms_course_versions(id),
  content_hash text,
  published_at timestamptz,
  published_by_user_id uuid,
  retired_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (course_id, version)
);

create unique index lms_course_versions_one_draft_idx
  on lms_course_versions(course_id)
  where status = 'draft';

create index lms_course_versions_course_status_idx
  on lms_course_versions(course_id, status);

Nodes​

create table lms_nodes (
  id uuid primary key default gen_random_uuid(),
  course_version_id uuid not null references lms_course_versions(id),
  parent_id uuid references lms_nodes(id),
  type lms_node_type not null,
  title text not null,
  description text,
  position int not null,
  unlock_rule jsonb not null default '{}'::jsonb,
  completion_rule jsonb not null default '{}'::jsonb,
  roadmap_topic_refs jsonb not null default '[]'::jsonb,
  estimated_minutes int,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (course_version_id, parent_id, position)
);

create index lms_nodes_version_idx on lms_nodes(course_version_id);
create index lms_nodes_parent_idx on lms_nodes(parent_id);
create index lms_nodes_type_idx on lms_nodes(type);

Проверка ацикличности дерева выполняется на уровне сервиса или recursive constraint trigger.

Roadmap​

create table roadmap_programs (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  title text not null,
  subject_key text not null,
  level_key text,
  status text not null default 'draft',
  published_version int,
  supports_tracks boolean not null default false,
  owner_user_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table roadmap_modules (
  id uuid primary key default gen_random_uuid(),
  program_id uuid not null references roadmap_programs(id),
  title text not null,
  position int not null,
  completion_rule jsonb not null default '{}'::jsonb,
  status text not null default 'draft',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (program_id, position)
);

create table roadmap_topics (
  id uuid primary key default gen_random_uuid(),
  program_id uuid not null references roadmap_programs(id),
  module_id uuid references roadmap_modules(id),
  code text not null,
  title text not null,
  difficulty text,
  prerequisite_topic_refs jsonb not null default '[]'::jsonb,
  status text not null default 'draft',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (program_id, code)
);

create table topic_pathways (
  id uuid primary key default gen_random_uuid(),
  topic_id uuid not null references roadmap_topics(id),
  format_key text,
  target_level_key text,
  recommended_lms_refs jsonb not null default '[]'::jsonb,
  recommended_task_refs jsonb not null default '[]'::jsonb,
  product_refs jsonb not null default '[]'::jsonb,
  product_run_refs jsonb not null default '[]'::jsonb,
  group_refs jsonb not null default '[]'::jsonb,
  session_refs jsonb not null default '[]'::jsonb,
  track_ref jsonb,
  availability_state pathway_availability_state not null default 'not_available',
  roadmap_layer roadmap_layer not null default 'core',
  completion_contribution pathway_completion_contribution not null default 'evidence_only',
  is_recommended boolean not null default false,
  status text not null default 'draft',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table mini_group_tracks (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  program_id uuid not null references roadmap_programs(id),
  format_key text not null,
  starts_at timestamptz,
  ends_at timestamptz,
  status text not null default 'draft',
  default_schedule_rule_id uuid,
  topic_sequence jsonb not null default '[]'::jsonb,
  module_sequence jsonb not null default '[]'::jsonb,
  display_priority int,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

Content blocks​

create table lms_content_blocks (
  id uuid primary key default gen_random_uuid(),
  node_id uuid not null references lms_nodes(id),
  type lms_block_type not null,
  title text,
  body jsonb not null default '{}'::jsonb,
  position int not null,
  required boolean not null default false,
  activity_kind text,
  task_bank_problem_ref jsonb,
  max_score numeric(10, 2),
  estimated_minutes int,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (node_id, position)
);

create index lms_content_blocks_node_idx on lms_content_blocks(node_id);
create index lms_content_blocks_type_idx on lms_content_blocks(type);
create index lms_content_blocks_task_ref_idx
  on lms_content_blocks using gin (task_bank_problem_ref);

Learning Workspace​

create table learning_groups (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null,
  owner_teacher_user_id uuid not null,
  title text not null,
  description text,
  status learning_group_status not null default 'active',
  created_by_user_id uuid not null,
  archived_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index learning_groups_teacher_org_idx
  on learning_groups(owner_teacher_user_id, organization_id, status);

create table learning_group_participants (
  id uuid primary key default gen_random_uuid(),
  group_id uuid not null references learning_groups(id),
  student_profile_id uuid not null,
  status learning_group_participant_status not null default 'active',
  source text not null default 'manual',
  added_by_user_id uuid not null,
  joined_at timestamptz,
  removed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (group_id, student_profile_id)
);

create index learning_group_participants_student_idx
  on learning_group_participants(student_profile_id, status);

create table learning_group_invites (
  id uuid primary key default gen_random_uuid(),
  group_id uuid not null references learning_groups(id),
  target_type text not null check (target_type in ('student', 'parent', 'family_adult')),
  student_profile_id uuid,
  identity_invite_ref jsonb not null default '{}'::jsonb,
  status learning_group_invite_status not null default 'created',
  expires_at timestamptz,
  accepted_at timestamptz,
  created_by_user_id uuid not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index learning_group_invites_group_status_idx
  on learning_group_invites(group_id, status);

create table learning_group_assignments (
  id uuid primary key default gen_random_uuid(),
  group_id uuid not null references learning_groups(id),
  created_by_user_id uuid not null,
  target_domain text not null check (target_domain in ('lms', 'task-bank', 'competitions')),
  target_type text not null,
  target_ref jsonb not null,
  participant_filter jsonb not null default '{"kind":"all"}'::jsonb,
  status learning_group_assignment_status not null default 'assigned',
  due_at timestamptz,
  cancelled_at timestamptz,
  cancellation_reason text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index learning_group_assignments_group_status_idx
  on learning_group_assignments(group_id, status);

create index learning_group_assignments_target_idx
  on learning_group_assignments(target_domain, target_type);

learning_groups хранят рабочий список преподавателя. Они не являются canonical student registry, LMS live groups, lms_enrollments или competition groups.

Enrollments​

create table lms_enrollments (
  id uuid primary key default gen_random_uuid(),
  student_profile_id uuid not null,
  course_id uuid not null references lms_courses(id),
  course_version_id uuid not null references lms_course_versions(id),
  source text not null,
  source_ref jsonb not null default '{}'::jsonb,
  status lms_enrollment_status not null default 'pending',
  started_at timestamptz,
  completed_at timestamptz,
  paused_at timestamptz,
  revoked_at timestamptz,
  revoke_reason text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index lms_enrollments_user_idx on lms_enrollments(student_profile_id);
create index lms_enrollments_course_idx on lms_enrollments(course_id);
create index lms_enrollments_version_idx on lms_enrollments(course_version_id);
create index lms_enrollments_status_idx on lms_enrollments(status);

Для MVP допускается уникальность (student_profile_id, course_id) по active-like статусам, если бизнес запрещает параллельные enrollments:

create unique index lms_enrollments_one_active_course_idx
  on lms_enrollments(student_profile_id, course_id)
  where status in ('pending', 'active', 'paused');

Teacher assignments​

create table lms_teacher_assignments (
  id uuid primary key default gen_random_uuid(),
  teacher_user_id uuid not null,
  scope_type text not null,
  scope_id uuid not null,
  role text not null,
  status text not null default 'active',
  starts_at timestamptz,
  ends_at timestamptz,
  created_by_user_id uuid not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index lms_teacher_assignments_teacher_idx
  on lms_teacher_assignments(teacher_user_id, status);

create index lms_teacher_assignments_scope_idx
  on lms_teacher_assignments(scope_type, scope_id, status);

Для Learning Workspace scope_type = 'learning_group' даёт доступ к рабочему списку. Чтение LMS progress по участникам требует отдельного scope на course, node, enrollment или явно разрешённый group/cohort context.

Attempts and submissions​

create table lms_activity_attempts (
  id uuid primary key default gen_random_uuid(),
  enrollment_id uuid not null references lms_enrollments(id),
  node_id uuid not null references lms_nodes(id),
  content_block_id uuid references lms_content_blocks(id),
  attempt_no int not null,
  status lms_attempt_status not null default 'started',
  answer jsonb,
  score numeric(10, 2),
  max_score numeric(10, 2),
  checker_source text,
  checker_ref jsonb not null default '{}'::jsonb,
  started_at timestamptz not null default now(),
  submitted_at timestamptz,
  checked_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (enrollment_id, content_block_id, attempt_no)
);

create index lms_activity_attempts_enrollment_idx on lms_activity_attempts(enrollment_id);
create index lms_activity_attempts_status_idx on lms_activity_attempts(status);

create table lms_submissions (
  id uuid primary key default gen_random_uuid(),
  enrollment_id uuid not null references lms_enrollments(id),
  attempt_id uuid references lms_activity_attempts(id),
  source_type text not null,
  source_id uuid not null,
  status lms_submission_status not null default 'draft',
  payload jsonb not null default '{}'::jsonb,
  submitted_at timestamptz,
  reopened_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index lms_submissions_enrollment_idx on lms_submissions(enrollment_id);
create index lms_submissions_source_idx on lms_submissions(source_type, source_id);
create index lms_submissions_status_idx on lms_submissions(status);

Feedback​

create table lms_feedback (
  id uuid primary key default gen_random_uuid(),
  submission_id uuid not null references lms_submissions(id),
  author_user_id uuid,
  author_type text not null,
  status_decision text,
  score numeric(10, 2),
  rubric jsonb not null default '{}'::jsonb,
  comment text,
  visible_to_student boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index lms_feedback_submission_idx on lms_feedback(submission_id);
create index lms_feedback_author_idx on lms_feedback(author_user_id);

Progress​

create table lms_progress_snapshots (
  id uuid primary key default gen_random_uuid(),
  enrollment_id uuid not null references lms_enrollments(id),
  course_version_id uuid not null references lms_course_versions(id),
  node_id uuid references lms_nodes(id),
  status lms_progress_status not null default 'not_started',
  completion_percent numeric(5, 2) not null default 0,
  score_summary jsonb not null default '{}'::jsonb,
  evidence_summary jsonb not null default '{}'::jsonb,
  last_activity_at timestamptz,
  completed_at timestamptz,
  calculated_at timestamptz not null default now(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (enrollment_id, node_id)
);

create index lms_progress_snapshots_enrollment_idx
  on lms_progress_snapshots(enrollment_id);

create index lms_progress_snapshots_node_idx
  on lms_progress_snapshots(node_id);

create table lms_learning_evidence (
  id uuid primary key default gen_random_uuid(),
  enrollment_id uuid not null references lms_enrollments(id),
  node_id uuid references lms_nodes(id),
  content_block_id uuid references lms_content_blocks(id),
  evidence_type text not null,
  source_type text not null,
  source_id uuid,
  payload jsonb not null default '{}'::jsonb,
  occurred_at timestamptz not null,
  created_at timestamptz not null default now()
);

create table topic_progress (
  id uuid primary key default gen_random_uuid(),
  student_profile_id uuid not null,
  topic_id uuid not null references roadmap_topics(id),
  status lms_progress_status not null default 'not_started',
  mastery_score numeric(5,2),
  evidence_summary jsonb not null default '{}'::jsonb,
  calculated_at timestamptz not null default now(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (student_profile_id, topic_id)
);

create table topic_completions (
  id uuid primary key default gen_random_uuid(),
  topic_progress_id uuid not null references topic_progress(id),
  completed_at timestamptz not null,
  rule_version int not null,
  evidence_refs jsonb not null default '[]'::jsonb,
  approved_by_user_id uuid,
  source text not null default 'rule',
  override_reason text,
  audit_ref jsonb,
  created_at timestamptz not null default now()
);

create table topic_enrichments (
  id uuid primary key default gen_random_uuid(),
  topic_id uuid not null references roadmap_topics(id),
  locale text not null default 'ru',
  public_description text,
  hints jsonb not null default '[]'::jsonb,
  recommended_refs jsonb not null default '[]'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (topic_id, locale)
);

create index lms_learning_evidence_enrollment_idx
  on lms_learning_evidence(enrollment_id, occurred_at desc);

Workbooks and projects​

create table lms_workbooks (
  id uuid primary key default gen_random_uuid(),
  enrollment_id uuid not null references lms_enrollments(id),
  node_id uuid references lms_nodes(id),
  title text not null,
  status text not null default 'active',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table lms_workbook_revisions (
  id uuid primary key default gen_random_uuid(),
  workbook_id uuid not null references lms_workbooks(id),
  revision_no int not null,
  body jsonb not null default '{}'::jsonb,
  author_user_id uuid not null,
  submitted boolean not null default false,
  created_at timestamptz not null default now(),
  unique (workbook_id, revision_no)
);

create table lms_projects (
  id uuid primary key default gen_random_uuid(),
  enrollment_id uuid not null references lms_enrollments(id),
  course_version_id uuid not null references lms_course_versions(id),
  title text not null,
  status text not null default 'active',
  current_milestone_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table lms_project_milestones (
  id uuid primary key default gen_random_uuid(),
  project_id uuid not null references lms_projects(id),
  title text not null,
  position int not null,
  status text not null default 'not_started',
  due_at timestamptz,
  completed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (project_id, position)
);

Booking​

create table rooms (
  id uuid primary key default gen_random_uuid(),
  type text not null check (type in ('physical','virtual','hybrid')),
  title text not null,
  location jsonb not null default '{}'::jsonb,
  capacity int,
  meeting_provider text,
  status text not null default 'active',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table schedule_rules (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  recurrence jsonb not null,
  timezone text not null default 'Europe/Moscow',
  duration_minutes int not null,
  room_policy jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table groups (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  format_key text not null,
  roadmap_program_id uuid references roadmap_programs(id),
  mini_group_track_id uuid references mini_group_tracks(id),
  status text not null default 'planned',
  starts_at timestamptz,
  ends_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table sessions (
  id uuid primary key default gen_random_uuid(),
  group_id uuid references groups(id),
  starts_at timestamptz not null,
  ends_at timestamptz not null,
  room_id uuid references rooms(id),
  teacher_user_id uuid,
  status lms_session_status not null default 'planned',
  source_schedule_rule_id uuid references schedule_rules(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table attendance (
  id uuid primary key default gen_random_uuid(),
  session_id uuid not null references sessions(id),
  student_profile_id uuid not null,
  status lms_attendance_status not null default 'expected',
  marked_by_user_id uuid,
  marked_at timestamptz not null default now(),
  evidence_policy jsonb not null default '{}'::jsonb,
  entitlement_consumption_ref jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (session_id, student_profile_id)
);

create table session_topic_links (
  id uuid primary key default gen_random_uuid(),
  session_id uuid not null references sessions(id),
  topic_id uuid not null references roadmap_topics(id),
  role text not null default 'covered',
  created_at timestamptz not null default now(),
  unique (session_id, topic_id, role)
);

create table session_lesson_links (
  id uuid primary key default gen_random_uuid(),
  session_id uuid not null references sessions(id),
  node_id uuid not null references lms_nodes(id),
  role text not null default 'related',
  created_at timestamptz not null default now(),
  unique (session_id, node_id, role)
);

create table session_changes (
  id uuid primary key default gen_random_uuid(),
  session_id uuid not null references sessions(id),
  actor_user_id uuid,
  change_type text not null,
  before jsonb,
  after jsonb,
  reason text,
  created_at timestamptz not null default now()
);

create table makeup_assignments (
  id uuid primary key default gen_random_uuid(),
  original_session_id uuid references sessions(id),
  replacement_session_id uuid references sessions(id),
  student_profile_id uuid not null,
  status text not null default 'assigned',
  reason text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table lms_booking_slots (
  id uuid primary key default gen_random_uuid(),
  course_id uuid references lms_courses(id),
  course_version_id uuid references lms_course_versions(id),
  node_id uuid references lms_nodes(id),
  teacher_user_id uuid,
  starts_at timestamptz not null,
  ends_at timestamptz not null,
  capacity int not null default 1,
  status lms_booking_status not null default 'available',
  external_calendar_ref jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table lms_bookings (
  id uuid primary key default gen_random_uuid(),
  slot_id uuid not null references lms_booking_slots(id),
  enrollment_id uuid not null references lms_enrollments(id),
  status lms_booking_status not null default 'booked',
  booked_by_user_id uuid not null,
  cancelled_at timestamptz,
  cancellation_reason text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (slot_id, enrollment_id)
);

create index lms_booking_slots_time_idx on lms_booking_slots(starts_at, ends_at);
create index lms_bookings_enrollment_idx on lms_bookings(enrollment_id);

Capacity must be enforced transactionally with row lock or optimistic concurrency.

Gamification​

create table xp (
  id uuid primary key default gen_random_uuid(),
  student_profile_id uuid not null,
  scope_type text not null,
  scope_id uuid,
  amount int not null default 0,
  level int not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (student_profile_id, scope_type, scope_id)
);

create table xp_events (
  id uuid primary key default gen_random_uuid(),
  student_profile_id uuid not null,
  amount int not null,
  reason text not null,
  source_type text not null,
  source_id text not null,
  idempotency_key text not null unique,
  created_at timestamptz not null default now()
);

create table badges (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  title text not null,
  description text,
  scope_type text,
  criteria jsonb not null default '{}'::jsonb,
  status text not null default 'draft',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table badge_awards (
  id uuid primary key default gen_random_uuid(),
  badge_id uuid not null references badges(id),
  student_profile_id uuid not null,
  source_type text,
  source_id text,
  awarded_at timestamptz not null default now(),
  revoked_at timestamptz,
  unique (badge_id, student_profile_id, source_type, source_id)
);

create table streaks (
  id uuid primary key default gen_random_uuid(),
  student_profile_id uuid not null,
  scope_type text not null,
  current_count int not null default 0,
  best_count int not null default 0,
  last_activity_at timestamptz,
  status text not null default 'active',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (student_profile_id, scope_type)
);

Chat​

create table lms_chat_threads (
  id uuid primary key default gen_random_uuid(),
  context_type text not null,
  context_id uuid not null,
  status lms_chat_status not null default 'open',
  created_by_user_id uuid not null,
  closed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table lms_chat_participants (
  id uuid primary key default gen_random_uuid(),
  thread_id uuid not null references lms_chat_threads(id),
  participant_user_id uuid not null,
  role text not null,
  status text not null default 'active',
  joined_at timestamptz not null default now(),
  left_at timestamptz,
  unique (thread_id, participant_user_id)
);

create table lms_chat_messages (
  id uuid primary key default gen_random_uuid(),
  thread_id uuid not null references lms_chat_threads(id),
  sender_user_id uuid not null,
  body text,
  attachments jsonb not null default '[]'::jsonb,
  status text not null default 'visible',
  created_at timestamptz not null default now(),
  edited_at timestamptz,
  deleted_at timestamptz
);

create index lms_chat_threads_context_idx on lms_chat_threads(context_type, context_id);
create index lms_chat_messages_thread_created_idx on lms_chat_messages(thread_id, created_at);

External sync and audit​

create table lms_external_sync_records (
  id uuid primary key default gen_random_uuid(),
  external_system text not null,
  external_id text not null,
  entity_type text not null,
  entity_id uuid,
  sync_status text not null,
  payload jsonb not null default '{}'::jsonb,
  synced_at timestamptz,
  error_message text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (external_system, external_id, entity_type)
);

create table lms_audit_logs (
  id uuid primary key default gen_random_uuid(),
  actor_user_id uuid,
  actor_type text not null,
  action text not null,
  target_type text not null,
  target_id uuid not null,
  old_value jsonb,
  new_value jsonb,
  reason text,
  request_id text,
  created_at timestamptz not null default now()
);

create index lms_audit_logs_target_idx on lms_audit_logs(target_type, target_id, created_at desc);
create index lms_audit_logs_actor_idx on lms_audit_logs(actor_user_id, created_at desc);

Обязательные транзакции​

Транзакционно выполняются:

  • публикация course version;
  • создание enrollment из CRM entitlement;
  • старт attempt с вычислением next attempt_no;
  • submit attempt и создание evidence;
  • проверка submission, feedback, progress recalculation и events;
  • booking slot hold/book/cancel;
  • создание Learning Group, invite и assignment;
  • перенос teacher assignment;
  • импорт внешнего progress с reconciliation.