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

Схема БД

Правила​

  • Все таблицы используют uuid primary key.
  • Published versions immutable на уровне application service и database guard.
  • JSONB используется для rich statement, answer schema, scoring rules и visibility.
  • Raw answer хранится отдельно от normalized check result.
  • Answer keys и solutions не возвращаются public API без permission.

Enum-значения​

create type problem_status as enum ('draft','review','published','archived','retired');
create type problem_version_status as enum ('draft','review','published','retired');
create type template_status as enum ('draft','review','approved','published','archived','retired');
create type export_snapshot_status as enum ('draft','exported','locked','active','retired','failed');
create type problem_relation_status as enum ('suggested','confirmed','rejected','hidden');
create type problem_attempt_status as enum ('started','submitted','checking','checked','cancelled','void');
create type answer_check_status as enum ('pending','checked','needs_manual_review','failed','overridden');
create type manual_review_status as enum ('open','assigned','checked','returned','cancelled');

Subjects and public publication​

create table task_bank_subject_refs (
  id uuid primary key default gen_random_uuid(),
  key text not null unique,
  title text not null,
  status text not null check (status in ('active','hidden','archived')),
  default_locale text not null default 'ru',
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

Problems and versions​

create table problems (
  id uuid primary key default gen_random_uuid(),
  code text not null unique,
  subject_key text not null,
  status problem_status not null default 'draft',
  default_difficulty text,
  source_ref jsonb not null default '{}'::jsonb,
  created_by_user_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table problem_versions (
  id uuid primary key default gen_random_uuid(),
  problem_id uuid not null references problems(id) on delete cascade,
  version int not null,
  status problem_version_status not null default 'draft',
  statement jsonb not null,
  answer_schema jsonb not null,
  metadata jsonb not null default '{}'::jsonb,
  visibility jsonb not null default '{}'::jsonb,
  published_at timestamptz,
  created_by_user_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (problem_id, version)
);

create table problem_assets (
  id uuid primary key default gen_random_uuid(),
  problem_version_id uuid not null references problem_versions(id) on delete cascade,
  type text not null check (type in ('image','pdf','video','interactive','source_file','diagram')),
  url text not null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table problem_publication_profiles (
  problem_id uuid primary key references problems(id) on delete cascade,
  public_status text not null default 'draft'
    check (public_status in ('draft','candidate','ready','published','hidden','embargoed')),
  public_after_date timestamptz,
  canonical_public_url text,
  primary_topic_id uuid,
  public_solution_policy text not null default 'hide'
    check (public_solution_policy in ('show','show_after_attempt','show_after_date','hide','teacher_only')),
  license_status text not null default 'unknown'
    check (license_status in ('allowed','restricted','unknown','forbidden')),
  seo_title_override text,
  seo_description_override text,
  quality_score numeric(5,2),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

Solutions, hints, checking rules​

create table problem_solutions (
  id uuid primary key default gen_random_uuid(),
  problem_version_id uuid not null references problem_versions(id) on delete cascade,
  type text not null check (type in ('short_answer','full_solution','step_by_step','teacher_notes','rubric_explanation')),
  body jsonb not null,
  visibility_rule jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table problem_hints (
  id uuid primary key default gen_random_uuid(),
  problem_version_id uuid not null references problem_versions(id) on delete cascade,
  level int not null,
  body jsonb not null,
  penalty jsonb not null default '{}'::jsonb,
  visibility_rule jsonb not null default '{}'::jsonb,
  unique (problem_version_id, level)
);

create table answer_keys (
  id uuid primary key default gen_random_uuid(),
  problem_version_id uuid not null references problem_versions(id) on delete cascade,
  key_data jsonb not null,
  visibility_rule jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table checking_rules (
  id uuid primary key default gen_random_uuid(),
  problem_version_id uuid not null references problem_versions(id) on delete cascade,
  method text not null check (method in ('auto','manual','hybrid','external')),
  rule jsonb not null,
  scoring_rule jsonb not null default '{}'::jsonb,
  rubric jsonb not null default '{}'::jsonb,
  version int not null default 1,
  created_at timestamptz not null default now()
);

Taxonomy​

create table taxonomy_nodes (
  id uuid primary key default gen_random_uuid(),
  type text not null check (type in ('topic','skill','problem_type','source','level','grade')),
  key text not null,
  subject_key text,
  title text not null,
  parent_id uuid references taxonomy_nodes(id),
  status text not null check (status in ('active','archived')),
  unique (type, subject_key, key)
);

create table problem_taxonomy_links (
  id uuid primary key default gen_random_uuid(),
  problem_id uuid not null references problems(id) on delete cascade,
  taxonomy_node_id uuid not null references taxonomy_nodes(id),
  weight numeric(5,2) not null default 1,
  created_at timestamptz not null default now(),
  unique (problem_id, taxonomy_node_id)
);

Constraints:

  • public/published problem must have one primary_topic_id;
  • problem_publication_profiles.primary_topic_id references taxonomy_nodes(id) after taxonomy table creation;
  • source hierarchy can use taxonomy_nodes(type='source') with parent_id;
  • taxonomy cycles are forbidden by application service or database guard.

Problem relations​

create table problem_relations (
  id uuid primary key default gen_random_uuid(),
  from_problem_id uuid not null references problems(id) on delete cascade,
  to_problem_id uuid not null references problems(id) on delete cascade,
  relation_type text not null check (
    relation_type in (
      'duplicate',
      'analog',
      'parameterized_variant',
      'set_variant',
      'similar',
      'prerequisite',
      'same_method'
    )
  ),
  status problem_relation_status not null default 'suggested',
  weight numeric(6,3) not null default 1,
  created_by_user_id uuid,
  reviewed_by_user_id uuid,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  reviewed_at timestamptz,
  check (from_problem_id <> to_problem_id),
  unique (from_problem_id, to_problem_id, relation_type)
);

create index problem_relations_to_problem_idx
  on problem_relations (to_problem_id, relation_type, status);

Constraints:

  • symmetric relation types are normalized by service layer;
  • prerequisite is directed and acyclic;
  • generated similar cannot become analog without moderation.

Sets, variants, usage​

create table problem_sets (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  title text not null,
  purpose text not null check (purpose in ('homework','lesson','competition','diagnostic','training','manual')),
  status text not null check (status in ('draft','review','published','archived')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table problem_set_items (
  id uuid primary key default gen_random_uuid(),
  problem_set_id uuid not null references problem_sets(id) on delete cascade,
  problem_id uuid not null references problems(id),
  problem_version_id uuid references problem_versions(id),
  position int not null,
  settings jsonb not null default '{}'::jsonb,
  unique (problem_set_id, position)
);

create table problem_variants (
  id uuid primary key default gen_random_uuid(),
  problem_id uuid not null references problems(id),
  key text not null,
  problem_version_id uuid not null references problem_versions(id),
  settings jsonb not null default '{}'::jsonb,
  unique (problem_id, key)
);

create table problem_usages (
  id uuid primary key default gen_random_uuid(),
  problem_version_id uuid not null references problem_versions(id),
  context_domain text not null check (context_domain in ('lms','competitions','management','storefront','manual')),
  context_type text not null,
  context_id text not null,
  status text not null default 'draft' check (status in ('draft', 'active', 'locked', 'suspended', 'archived')),
  position int,
  scoring_rule_snapshot jsonb not null default '{}'::jsonb,
  visibility_policy jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  unique (problem_version_id, context_domain, context_type, context_id, position)
);

Activity templates​

create table activity_templates (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  subject_key text not null,
  title text not null,
  activity_type text not null check (
    activity_type in ('lesson','homework','trainer','diagnostic','competition_round','review','intensive')
  ),
  status template_status not null default 'draft',
  version int not null default 1,
  age_group jsonb not null default '{}'::jsonb,
  difficulty_range jsonb not null default '{}'::jsonb,
  duration_minutes int,
  primary_topic_id uuid references taxonomy_nodes(id),
  objective text,
  teacher_notes jsonb not null default '{}'::jsonb,
  student_intro jsonb not null default '{}'::jsonb,
  visibility text not null default 'private' check (visibility in ('private','organization','public')),
  owner_user_id uuid,
  identity_organization_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table activity_template_sections (
  id uuid primary key default gen_random_uuid(),
  activity_template_id uuid not null references activity_templates(id) on delete cascade,
  title text not null,
  section_role text not null check (
    section_role in ('warmup','theory_bridge','main','practice','homework','challenge','reserve','reflection','diagnostic')
  ),
  position int not null,
  duration_minutes int,
  teacher_notes jsonb not null default '{}'::jsonb,
  student_instructions jsonb not null default '{}'::jsonb,
  unique (activity_template_id, position)
);

create table activity_template_items (
  id uuid primary key default gen_random_uuid(),
  activity_template_id uuid not null references activity_templates(id) on delete cascade,
  section_id uuid references activity_template_sections(id) on delete set null,
  problem_id uuid not null references problems(id),
  problem_version_id uuid references problem_versions(id),
  problem_set_item_id uuid references problem_set_items(id),
  position int not null,
  role text not null check (role in ('main','warmup','reserve','homework','challenge','diagnostic','example')),
  required boolean not null default true,
  recommended_time_minutes int,
  recommended_points numeric(8,2),
  checking_mode text not null default 'auto' check (checking_mode in ('auto','manual','hybrid','none')),
  solution_visibility_policy jsonb not null default '{}'::jsonb,
  hint_policy jsonb not null default '{}'::jsonb,
  teacher_comment jsonb not null default '{}'::jsonb,
  student_comment jsonb not null default '{}'::jsonb,
  unique (activity_template_id, section_id, position)
);

Program templates​

create table program_templates (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  subject_key text not null,
  title text not null,
  program_type text not null check (program_type in ('circle','course_content','intensive','competition_prep','trainer_path')),
  status template_status not null default 'draft',
  version int not null default 1,
  age_group jsonb not null default '{}'::jsonb,
  duration_units int,
  objective text,
  visibility text not null default 'private' check (visibility in ('private','organization','public')),
  owner_user_id uuid,
  identity_organization_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table program_tracks (
  id uuid primary key default gen_random_uuid(),
  program_template_id uuid not null references program_templates(id) on delete cascade,
  title text not null,
  level text,
  position int not null,
  description text,
  unique (program_template_id, position)
);

create table program_activity_links (
  id uuid primary key default gen_random_uuid(),
  program_template_id uuid not null references program_templates(id) on delete cascade,
  program_track_id uuid references program_tracks(id) on delete cascade,
  activity_template_id uuid not null references activity_templates(id),
  position int not null,
  module_label text,
  required boolean not null default true,
  replacement_group_id uuid,
  prerequisite_activity_id uuid references activity_templates(id),
  unique (program_template_id, program_track_id, position)
);

Content export snapshots​

create table content_export_snapshots (
  id uuid primary key default gen_random_uuid(),
  source_type text not null check (source_type in ('problem_set','activity_template','program_template')),
  source_id uuid not null,
  source_version int not null,
  target_domain text not null check (target_domain in ('lms','competitions','storefront','management','manual')),
  target_type text not null,
  target_id text,
  status export_snapshot_status not null default 'draft',
  payload jsonb not null,
  payload_hash text not null,
  exported_by_user_id uuid,
  locked_at timestamptz,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index content_export_snapshots_target_idx
  on content_export_snapshots (target_domain, target_type, target_id, source_type, source_id)
  where target_id is not null;

Constraints:

  • locked snapshots are immutable;
  • snapshot payload cannot include answer key, hidden solution, teacher notes or rubric unless target scope and visibility allow it;
  • changes to published templates create a new version and a new snapshot.

Attempts and checks​

create table problem_attempts (
  id uuid primary key default gen_random_uuid(),
  problem_usage_id uuid references problem_usages(id),
  problem_version_id uuid not null references problem_versions(id),
  identity_user_id uuid not null,
  status problem_attempt_status not null default 'started',
  answer jsonb,
  started_at timestamptz not null default now(),
  submitted_at timestamptz,
  checked_at timestamptz,
  idempotency_key text unique
);

create table answer_checks (
  id uuid primary key default gen_random_uuid(),
  attempt_id uuid not null references problem_attempts(id) on delete cascade,
  checking_rule_id uuid references checking_rules(id),
  status answer_check_status not null default 'pending',
  method text not null check (method in ('auto','manual','hybrid','external')),
  is_correct boolean,
  score numeric(8,2),
  max_score numeric(8,2),
  feedback jsonb not null default '{}'::jsonb,
  checked_by_user_id uuid,
  override_reason text,
  created_at timestamptz not null default now()
);

create table manual_reviews (
  id uuid primary key default gen_random_uuid(),
  attempt_id uuid not null references problem_attempts(id) on delete cascade,
  status manual_review_status not null default 'open',
  assigned_to_user_id uuid,
  rubric_snapshot jsonb not null default '{}'::jsonb,
  decision jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

Evidence and audit​

create table problem_evidence (
  id uuid primary key default gen_random_uuid(),
  attempt_id uuid not null references problem_attempts(id),
  problem_id uuid not null references problems(id),
  identity_user_id uuid not null,
  signal text not null check (signal in ('attempted','solved','partially_solved','failed','solved_with_hint','needs_review','repeated_difficulty')),
  taxonomy_snapshot jsonb not null default '{}'::jsonb,
  score numeric(8,2),
  created_at timestamptz not null default now()
);

create table task_bank_audit_logs (
  id uuid primary key default gen_random_uuid(),
  actor_user_id uuid,
  action text not null,
  entity_type text not null,
  entity_id uuid,
  before jsonb,
  after jsonb,
  reason text,
  request_id text,
  created_at timestamptz not null default now()
);

create index task_bank_audit_logs_entity_idx
  on task_bank_audit_logs (entity_type, entity_id, created_at desc);