Схема БД

Правила

• Все таблицы используют uuid primary key.
• Ссылки на identity, task-bank, CRM и storefront хранятся как external refs.
• Scores/results не удаляются физически после публикации.
• Все изменения submission/check/result/publication/document пишутся в audit.
• JSONB используется для policies, settings, snapshots и source refs.
• Внешние ссылки на identity organization layer называются явно: identity_organization_id, organization_student_id, organization_membership_id.
• Локальная олимпиадная проекция организации называется competition_organization_id.
• Внешние ссылки на Learning Workspace / Learning Group называются явно: learning_workspace_id, learning_group_id, learning_group_member_id.
• competition_group хранит снимок регистрации сезона, а не canonical список учеников преподавателя.
• Official data package для ГИР хранится в identity/protected attributes; competitions хранит requirement/status/ref/audit, но не сырой СНИЛС, дату рождения ребёнка, email или телефон родителя.

Enum-значения

create type competition_event_status as enum ('draft','active','archived');
create type competition_season_status as enum ('draft','registration_open','running','checking','results_review','published','archived','cancelled');
create type competition_tour_status as enum ('draft','scheduled','open','closed','checking','results_ready','published','archived','cancelled');
create type competition_registration_status as enum ('draft','pre_submitted','needs_completion','submitted','pending_review','approved','rejected','cancelled','duplicate_hold');
create type competition_official_data_package_status as enum ('not_required','not_filled','incomplete','needs_verification','confirmed','rejected');
create type competition_season_participation_status as enum ('pending','active','completed','withdrawn','disqualified');
create type competition_tour_participation_status as enum ('pending','admitted','active','completed','withdrawn','disqualified');
create type competition_submission_status as enum ('not_started','started','submitted','locked','checking','checked','void');
create type competition_publication_status as enum ('hidden','personal','public','archived');

Event, season, tour

create table competition_events (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique,
  title text not null,
  type text not null check (type in ('olympiad','contest','tournament')),
  subject_key text,
  status competition_event_status not null default 'draft',
  public_visibility text not null default 'hidden',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table competition_seasons (
  id uuid primary key default gen_random_uuid(),
  event_id uuid not null references competition_events(id),
  title text not null,
  status competition_season_status not null default 'draft',
  registration_policy jsonb not null default '{}'::jsonb,
  fee_policy jsonb not null default '{}'::jsonb,
  starts_at timestamptz,
  ends_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table competition_tours (
  id uuid primary key default gen_random_uuid(),
  season_id uuid not null references competition_seasons(id),
  title text not null,
  position int not null,
  format text not null check (format in ('test','written','scans','hybrid','chess','other')),
  status competition_tour_status not null default 'draft',
  starts_at timestamptz,
  ends_at timestamptz,
  settings jsonb not null default '{}'::jsonb,
  unique (season_id, position)
);

create table competition_grade_categories (
  id uuid primary key default gen_random_uuid(),
  season_id uuid not null references competition_seasons(id),
  key text not null,
  title text not null,
  grade_range_key text,
  created_at timestamptz not null default now(),
  unique (season_id, key)
);

create table competition_tracks (
  id uuid primary key default gen_random_uuid(),
  season_id uuid not null references competition_seasons(id),
  tour_id uuid references competition_tours(id),
  key text not null,
  title text not null,
  settings jsonb not null default '{}'::jsonb,
  unique (season_id, tour_id, key)
);

create table competition_award_policies (
  id uuid primary key default gen_random_uuid(),
  season_id uuid not null references competition_seasons(id),
  track_id uuid references competition_tracks(id),
  grade_category_id uuid references competition_grade_categories(id),
  rules jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table competition_award_statuses (
  key text primary key check (key in (
    'diploma_1',
    'diploma_2',
    'diploma_3',
    'honorable_mention_1',
    'honorable_mention_2',
    'participation_certificate',
    'no_award'
  )),
  title text not null,
  document_type text,
  sort_order int not null,
  active boolean not null default true
);

Participants and registration

create table competition_participants (
  id uuid primary key default gen_random_uuid(),
  season_id uuid not null references competition_seasons(id),
  student_profile_id uuid,
  organization_student_id uuid,
  identity_organization_id uuid,
  organization_membership_id uuid,
  organization_student_snapshot jsonb not null default '{}'::jsonb,
  identity_user_id uuid,
  display_name text not null,
  grade text,
  child_mode_required boolean not null default false,
  official_data_ref jsonb,
  identity_status text not null default 'unlinked',
  duplicate_status text not null default 'not_checked',
  created_by_user_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  check (organization_student_id is null or identity_organization_id is not null)
);

create unique index uq_competition_participants_season_identity
  on competition_participants(season_id, identity_user_id)
  where identity_user_id is not null and duplicate_status <> 'merged';

create unique index uq_competition_participants_season_student_profile
  on competition_participants(season_id, student_profile_id)
  where student_profile_id is not null and duplicate_status <> 'merged';

create unique index uq_competition_participants_season_org_student
  on competition_participants(season_id, organization_student_id)
  where organization_student_id is not null and duplicate_status <> 'merged';

-- Official data package fields are protected identity refs, not raw official values.
create table competition_registrations (
  id uuid primary key default gen_random_uuid(),
  season_id uuid not null references competition_seasons(id),
  participant_id uuid references competition_participants(id),
  group_id uuid,
  learning_workspace_id uuid,
  learning_group_id uuid,
  status competition_registration_status not null default 'draft',
  stage text not null default 'pre_registration'
    check (stage in ('pre_registration','details_required','ready_for_review','complete')),
  source text not null check (source in ('self','parent','teacher','organization','admin')),
  required_fields_status jsonb not null default '{}'::jsonb,
  delivery_preferences jsonb not null default '{}'::jsonb,
  teacher_name_text text,
  official_data_package_required boolean not null default false,
  official_data_package_status competition_official_data_package_status not null default 'not_required',
  official_data_ref jsonb,
  parent_contact_ref jsonb,
  parent_invite_ref jsonb,
  official_data_collected_by_user_id uuid,
  official_data_collected_at timestamptz,
  official_data_verified_by_user_id uuid,
  official_data_verified_at timestamptz,
  submitted_by_user_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  check (
    (official_data_package_required = false and official_data_package_status = 'not_required')
    or
    official_data_package_required = true
  )
);

create table competition_season_participations (
  id uuid primary key default gen_random_uuid(),
  season_id uuid not null references competition_seasons(id),
  participant_id uuid not null references competition_participants(id),
  registration_id uuid references competition_registrations(id),
  status competition_season_participation_status not null default 'pending',
  payment_status text not null default 'not_required',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (season_id, participant_id)
);

create table competition_tour_participations (
  id uuid primary key default gen_random_uuid(),
  tour_id uuid not null references competition_tours(id),
  season_participation_id uuid not null references competition_season_participations(id),
  delivery_mode text not null check (delivery_mode in ('online','teacher_led','venue','imported')),
  track_id uuid references competition_tracks(id),
  venue_id uuid,
  status competition_tour_participation_status not null default 'pending',
  admitted_at timestamptz,
  unique (tour_id, season_participation_id)
);

create table competition_access_claims (
  id uuid primary key default gen_random_uuid(),
  participant_id uuid not null references competition_participants(id),
  requester_user_id uuid not null,
  claim_type text not null check (claim_type in ('student','parent','family')),
  status text not null check (status in ('requested','approved','rejected','cancelled')),
  evidence jsonb not null default '{}'::jsonb,
  reviewed_by_user_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

External teachers

create table external_teacher_profiles (
  id uuid primary key default gen_random_uuid(),
  identity_user_id uuid not null,
  display_name_snapshot text not null,
  organization_name text,
  verification_status text not null check (verification_status in ('pending','verified','rejected','revoked')),
  verification_source text not null check (verification_source in ('self_declaration','organization','admin','migration')),
  allowed_season_refs jsonb not null default '[]'::jsonb,
  verified_by_user_id uuid,
  verified_at timestamptz,
  revoked_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (identity_user_id)
);

create table external_teacher_student_links (
  id uuid primary key default gen_random_uuid(),
  external_teacher_profile_id uuid not null references external_teacher_profiles(id),
  participant_id uuid not null references competition_participants(id),
  relation_status text not null check (relation_status in ('pending','active','revoked','rejected')),
  evidence jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  revoked_at timestamptz,
  unique (external_teacher_profile_id, participant_id)
);

create index idx_external_teacher_student_links_participant
  on external_teacher_student_links(participant_id, relation_status);

Groups, venues, tasks

create table competition_groups (
  id uuid primary key default gen_random_uuid(),
  season_id uuid not null references competition_seasons(id),
  competition_organization_id uuid,
  learning_workspace_id uuid not null,
  learning_group_id uuid not null,
  primary_teacher_user_id uuid,
  title_snapshot text not null,
  member_snapshot jsonb not null default '[]'::jsonb,
  snapshot_taken_at timestamptz not null default now(),
  status text not null check (status in ('active','archived')),
  created_at timestamptz not null default now(),
  unique (season_id, learning_group_id)
);

create table competition_organizations (
  id uuid primary key default gen_random_uuid(),
  season_id uuid not null references competition_seasons(id),
  identity_organization_id uuid not null,
  display_name_snapshot text not null,
  participation_status text not null default 'active',
  season_scope jsonb not null default '{}'::jsonb,
  snapshot_taken_at timestamptz not null default now(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (season_id, identity_organization_id)
);

alter table competition_groups
  add constraint fk_competition_groups_competition_organization
  foreign key (competition_organization_id)
  references competition_organizations(id);

create table competition_group_members (
  id uuid primary key default gen_random_uuid(),
  group_id uuid not null references competition_groups(id) on delete cascade,
  participant_id uuid not null references competition_participants(id),
  learning_group_member_id uuid,
  member_snapshot jsonb not null default '{}'::jsonb,
  status text not null check (status in ('active','removed')),
  unique (group_id, participant_id)
);

create table competition_group_helpers (
  id uuid primary key default gen_random_uuid(),
  group_id uuid not null references competition_groups(id) on delete cascade,
  helper_user_id uuid not null,
  role text not null check (role in ('answers_entry','file_upload','roster_view','venue_staff')),
  granted_by_user_id uuid not null,
  status text not null check (status in ('active','revoked')),
  created_at timestamptz not null default now(),
  revoked_at timestamptz,
  unique (group_id, helper_user_id, role)
);

create table competition_organization_roles (
  id uuid primary key default gen_random_uuid(),
  competition_organization_id uuid not null references competition_organizations(id),
  role text not null check (role in ('organizer','closed_group_host','open_venue')),
  status text not null default 'active',
  created_at timestamptz not null default now()
);

create table competition_venue_applications (
  id uuid primary key default gen_random_uuid(),
  season_id uuid not null references competition_seasons(id),
  competition_organization_id uuid not null references competition_organizations(id),
  status text not null check (status in ('draft','submitted','needs_changes','approved','rejected','cancelled')),
  title text not null,
  address jsonb not null default '{}'::jsonb,
  geo_point jsonb,
  capacity_total int,
  accepted_grade_categories jsonb not null default '[]'::jsonb,
  contacts jsonb not null default '[]'::jsonb,
  access_rules jsonb not null default '{}'::jsonb,
  required_documents jsonb not null default '[]'::jsonb,
  guardian_policy jsonb not null default '{}'::jsonb,
  facility_rules jsonb not null default '{}'::jsonb,
  technical_capabilities jsonb not null default '{}'::jsonb,
  participant_public_comment text,
  admin_internal_comment text,
  submitted_by_user_id uuid,
  reviewed_by_user_id uuid,
  reviewed_at timestamptz,
  review_comment text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table competition_venues (
  id uuid primary key default gen_random_uuid(),
  season_id uuid not null references competition_seasons(id),
  competition_organization_id uuid references competition_organizations(id),
  venue_application_id uuid references competition_venue_applications(id),
  title text not null,
  address jsonb not null default '{}'::jsonb,
  geo_point jsonb,
  capacity int,
  current_assignment_count int not null default 0,
  public_visibility text not null default 'hidden' check (public_visibility in ('hidden','list','map','list_and_map')),
  official_status text not null default 'not_official' check (official_status in ('official','not_official','mixed')),
  access_rules jsonb not null default '{}'::jsonb,
  required_documents jsonb not null default '[]'::jsonb,
  guardian_policy jsonb not null default '{}'::jsonb,
  facility_rules jsonb not null default '{}'::jsonb,
  technical_capabilities jsonb not null default '{}'::jsonb,
  participant_public_comment text,
  admin_internal_comment text,
  status text not null check (status in ('draft','approved','rejected','active','registration_open','closed_for_selection','running','suspended','cancelled','archived')),
  created_at timestamptz not null default now()
);

create table competition_teacher_conduct_applications (
  id uuid primary key default gen_random_uuid(),
  season_id uuid not null references competition_seasons(id),
  tour_id uuid not null references competition_tours(id),
  identity_organization_id uuid not null,
  competition_organization_id uuid references competition_organizations(id),
  submitted_by_user_id uuid not null,
  location_text text,
  expected_participant_count int,
  planned_date_from date,
  planned_date_to date,
  grade_categories jsonb not null default '[]'::jsonb,
  delivery_mode text not null check (delivery_mode in ('teacher_led','online_supervised','mixed')),
  contact_snapshot jsonb not null default '{}'::jsonb,
  photo_report_required boolean not null default true,
  status text not null check (status in ('draft','submitted','approved','needs_changes','rejected','cancelled','completed')),
  admin_comment text,
  submitted_at timestamptz,
  reviewed_by_user_id uuid,
  reviewed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table competition_venue_capacity_changes (
  id uuid primary key default gen_random_uuid(),
  venue_id uuid not null references competition_venues(id),
  old_capacity int,
  new_capacity int not null,
  current_assignment_count int not null,
  status text not null check (status in ('applied','requires_admin_process','rejected')),
  reason text,
  actor_user_id uuid,
  created_at timestamptz not null default now(),
  check (new_capacity >= current_assignment_count or status = 'requires_admin_process')
);

create table competition_venue_assignments (
  id uuid primary key default gen_random_uuid(),
  venue_id uuid not null references competition_venues(id),
  tour_participation_id uuid references competition_tour_participations(id),
  participant_id uuid references competition_participants(id),
  assigned_by_user_id uuid,
  status text not null default 'assigned'
    check (status in ('selected','assigned','confirmed','transferred','cancelled','attended','no_show')),
  created_at timestamptz not null default now(),
  check (tour_participation_id is not null or participant_id is not null)
);

create table competition_tour_activity_bindings (
  id uuid primary key default gen_random_uuid(),
  season_id uuid not null references competition_seasons(id),
  tour_id uuid not null references competition_tours(id),
  grade_category_id uuid references competition_grade_categories(id),
  track_id uuid references competition_tracks(id),
  mode text not null check (mode in ('lms_activity','temporary_adapter')),
  lms_activity_ref jsonb,
  adapter_ref jsonb,
  task_bank_source_ref jsonb,
  status text not null check (status in ('draft','review','locked','published','archived')),
  activity_snapshot_ref jsonb,
  locked_at timestamptz,
  created_at timestamptz not null default now(),
  check (
    (mode = 'lms_activity' and lms_activity_ref is not null)
    or
    (mode = 'temporary_adapter' and adapter_ref is not null and activity_snapshot_ref is not null)
  )
);

create table competition_tour_task_structures (
  id uuid primary key default gen_random_uuid(),
  activity_binding_id uuid not null references competition_tour_activity_bindings(id) on delete cascade,
  grade_category_id uuid references competition_grade_categories(id),
  task_count int not null check (task_count > 0),
  max_total_score numeric(8,2) not null,
  structure_snapshot jsonb not null default '{}'::jsonb,
  locked_at timestamptz,
  created_at timestamptz not null default now(),
  unique (activity_binding_id, grade_category_id)
);

create table competition_tour_task_items (
  id uuid primary key default gen_random_uuid(),
  activity_binding_id uuid not null references competition_tour_activity_bindings(id) on delete cascade,
  task_structure_id uuid references competition_tour_task_structures(id) on delete cascade,
  task_number int not null,
  display_title text,
  max_score numeric(8,2) not null,
  lms_activity_item_ref jsonb,
  task_bank_problem_version_ref jsonb,
  scoring_snapshot jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  unique (activity_binding_id, task_number)
);

create table competition_training_publications (
  id uuid primary key default gen_random_uuid(),
  source_activity_binding_id uuid not null references competition_tour_activity_bindings(id),
  source_tour_task_structure_id uuid references competition_tour_task_structures(id),
  lms_ref jsonb not null,
  storefront_ref jsonb,
  training_mode text not null check (training_mode in ('practice','timed_practice','review_mode','mock_contest')),
  visibility text not null check (visibility in ('public','registered','participants','teacher','admin')),
  status text not null check (status in ('draft','scheduled','published','paused','archived')),
  scheduled_at timestamptz,
  published_at timestamptz,
  created_at timestamptz not null default now()
);

competition_tour_activity_bindings.mode = 'temporary_adapter' is a competitions-first exception. It allows the first olympiad launch before LMS activity runtime is ready, while keeping a locked activity snapshot for historical reproducibility. The adapter follows the future LMS activity contract, does not become task-bank or a full LMS, and must have a migration trigger to LMS activity runtime.

competition_tour_task_items.task_bank_problem_version_ref is a content/source ref only. Task-bank is not the runtime for a competition attempt.

competition_training_publications.lms_ref is the source link to LMS practice/review/mock activity. storefront_ref is only a public projection and does not replace LMS ownership.

Submissions, results, documents

create table competition_submissions (
  id uuid primary key default gen_random_uuid(),
  tour_participation_id uuid not null references competition_tour_participations(id),
  activity_binding_id uuid references competition_tour_activity_bindings(id),
  lms_activity_attempt_ref jsonb,
  adapter_attempt_ref jsonb,
  status competition_submission_status not null default 'not_started',
  attempt_status_snapshot jsonb not null default '{}'::jsonb,
  submitted_at timestamptz,
  locked_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (tour_participation_id)
);

create table competition_submission_files (
  id uuid primary key default gen_random_uuid(),
  submission_id uuid not null references competition_submissions(id) on delete cascade,
  type text not null check (type in ('file','photo','scan','pdf','image','zip')),
  url text not null,
  uploaded_by_user_id uuid,
  uploaded_for_participant_id uuid references competition_participants(id),
  is_late boolean not null default false,
  late_reason text,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table competition_photo_reports (
  id uuid primary key default gen_random_uuid(),
  tour_id uuid not null references competition_tours(id),
  venue_id uuid references competition_venues(id),
  competition_group_id uuid references competition_groups(id),
  teacher_conduct_application_id uuid references competition_teacher_conduct_applications(id),
  competition_organization_id uuid references competition_organizations(id),
  submitted_by_user_id uuid not null,
  status text not null check (status in ('submitted','confirmed','unconfirmed')),
  file_refs jsonb not null default '[]'::jsonb,
  comment text,
  reviewed_by_user_id uuid,
  reviewed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table competition_answer_items (
  id uuid primary key default gen_random_uuid(),
  submission_id uuid not null references competition_submissions(id) on delete cascade,
  tour_task_item_id uuid references competition_tour_task_items(id),
  lms_activity_item_ref jsonb,
  item_result_snapshot jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table competition_checks (
  id uuid primary key default gen_random_uuid(),
  submission_id uuid not null references competition_submissions(id),
  method text not null check (method in ('lms_runtime','temporary_adapter','auto','manual','hybrid','imported')),
  status text not null check (status in ('pending','checked','needs_review','failed','overridden')),
  score numeric(8,2),
  checked_by_user_id uuid,
  reason text,
  created_at timestamptz not null default now()
);

create table competition_scores (
  id uuid primary key default gen_random_uuid(),
  submission_id uuid not null references competition_submissions(id),
  tour_task_item_id uuid references competition_tour_task_items(id),
  lms_activity_item_ref jsonb,
  score_version int not null default 1,
  is_current boolean not null default true,
  score numeric(8,2) not null,
  max_score numeric(8,2),
  source_check_id uuid references competition_checks(id),
  source_runtime_ref jsonb,
  score_snapshot jsonb not null default '{}'::jsonb,
  recalculation_reason text,
  voided_at timestamptz,
  created_at timestamptz not null default now(),
  unique (submission_id, tour_task_item_id, score_version)
);

create unique index competition_scores_current_unique
  on competition_scores (submission_id, tour_task_item_id)
  where is_current = true;

create table competition_results (
  id uuid primary key default gen_random_uuid(),
  tour_id uuid references competition_tours(id),
  season_id uuid not null references competition_seasons(id),
  participant_id uuid not null references competition_participants(id),
  track_id uuid references competition_tracks(id),
  score numeric(8,2) not null,
  rank int,
  award_status_key text not null default 'no_award' references competition_award_statuses(key),
  status text not null check (status in ('draft','calculated','review','finalized','void')),
  publication_status competition_publication_status not null default 'hidden',
  threshold_policy_snapshot jsonb not null default '{}'::jsonb,
  approved_by_user_id uuid,
  approved_at timestamptz,
  is_withheld boolean not null default false,
  withheld_reason text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table competition_appeals (
  id uuid primary key default gen_random_uuid(),
  result_id uuid references competition_results(id),
  submission_id uuid references competition_submissions(id),
  participant_id uuid not null references competition_participants(id),
  status text not null check (status in ('draft','submitted','in_review','accepted','rejected','cancelled')),
  reason text not null,
  decision_comment text,
  submitted_by_user_id uuid not null,
  reviewed_by_user_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table competition_arbitration_cases (
  id uuid primary key default gen_random_uuid(),
  submission_id uuid references competition_submissions(id),
  participant_id uuid references competition_participants(id),
  status text not null check (status in ('opened','in_review','cleared','sanctioned','closed')),
  suspicion_type text not null,
  evidence_refs jsonb not null default '[]'::jsonb,
  decision_comment text,
  opened_by_user_id uuid,
  decided_by_user_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table competition_rankings (
  id uuid primary key default gen_random_uuid(),
  season_id uuid not null references competition_seasons(id),
  tour_id uuid references competition_tours(id),
  track_id uuid references competition_tracks(id),
  participant_id uuid not null references competition_participants(id),
  rank int not null,
  score numeric(8,2) not null,
  calculated_at timestamptz not null default now()
);

create table competition_publications (
  id uuid primary key default gen_random_uuid(),
  season_id uuid not null references competition_seasons(id),
  type text not null check (type in ('personal_results','public_results','works','materials','training','venues')),
  status text not null check (status in ('draft','scheduled','published','hidden','archived')),
  grade_category_id uuid references competition_grade_categories(id),
  track_id uuid references competition_tracks(id),
  settings jsonb not null default '{}'::jsonb,
  published_at timestamptz
);

create table competition_teacher_materials (
  id uuid primary key default gen_random_uuid(),
  season_id uuid not null references competition_seasons(id),
  tour_id uuid references competition_tours(id),
  teacher_conduct_application_id uuid references competition_teacher_conduct_applications(id),
  material_type text not null check (material_type in ('poster','banner','announcement_text','school_news_text','teacher_instruction','student_instruction','parent_instruction','tour_material','participant_list','report_template','photo_report','results_news','gratitude')),
  title text not null,
  description text,
  file_ref jsonb,
  text_content text,
  visibility text not null check (visibility in ('teacher','teacher_and_helpers','organization','public','admin')),
  available_from timestamptz,
  available_until timestamptz,
  status text not null check (status in ('draft','published','archived')),
  generated boolean not null default false,
  source_ref jsonb,
  created_by_user_id uuid,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table competition_award_documents (
  id uuid primary key default gen_random_uuid(),
  result_id uuid references competition_results(id),
  type text not null check (type in ('diploma','certificate','gratitude','participation','teacher_gratitude','team_gratitude','organization_gratitude','venue_gratitude')),
  recipient_type text not null default 'participant'
    check (recipient_type in ('participant','teacher','team_member','organization','venue')),
  recipient_snapshot jsonb not null default '{}'::jsonb,
  status text not null check (status in ('pending','generated','failed','revoked')),
  document_url text,
  verification_code text unique,
  generated_at timestamptz,
  check (result_id is not null or recipient_type <> 'participant')
);

create table competition_document_generation_jobs (
  id uuid primary key default gen_random_uuid(),
  publication_id uuid references competition_publications(id),
  status text not null check (status in ('queued','running','succeeded','failed','cancelled')),
  payload jsonb not null default '{}'::jsonb,
  error_message text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

Audit

create table competition_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 competition_audit_logs_entity_idx
  on competition_audit_logs (entity_type, entity_id, created_at desc);