yuqei
105 lines
4.1 KiB
SQL
105 lines
4.1 KiB
SQL
create table if not exists training_cases (
|
|
id bigserial primary key,
|
|
instrument_id bigint not null references instruments(id),
|
|
timeframe text not null,
|
|
case_type text not null,
|
|
chapter_ref text,
|
|
skill_tags jsonb not null default '[]'::jsonb,
|
|
source_event_type text,
|
|
source_event_id bigint,
|
|
window_start_ts timestamptz not null,
|
|
decision_ts timestamptz not null,
|
|
window_end_ts timestamptz not null,
|
|
difficulty text not null default 'standard',
|
|
answer_key jsonb not null default '{}'::jsonb,
|
|
status text not null default 'draft',
|
|
meta jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
constraint training_cases_window_order check (window_start_ts <= decision_ts and decision_ts <= window_end_ts),
|
|
constraint training_cases_status_check check (status in ('draft', 'active', 'archived')),
|
|
unique (instrument_id, timeframe, case_type, source_event_type, source_event_id, decision_ts)
|
|
);
|
|
|
|
create table if not exists training_case_tasks (
|
|
id bigserial primary key,
|
|
case_id bigint not null references training_cases(id) on delete cascade,
|
|
task_type text not null,
|
|
prompt text not null,
|
|
required_annotation_types jsonb not null default '[]'::jsonb,
|
|
max_score numeric(10,2) not null default 0,
|
|
sort_order integer not null default 0,
|
|
meta jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create table if not exists training_attempts (
|
|
id bigserial primary key,
|
|
case_id bigint not null references training_cases(id) on delete cascade,
|
|
user_id text not null default 'local',
|
|
status text not null default 'started',
|
|
current_frame_index integer not null default 0,
|
|
started_ts timestamptz not null default now(),
|
|
submitted_ts timestamptz,
|
|
total_score numeric(10,2),
|
|
meta jsonb not null default '{}'::jsonb,
|
|
constraint training_attempts_status_check check (status in ('started', 'submitted', 'graded', 'abandoned')),
|
|
constraint training_attempts_frame_non_negative check (current_frame_index >= 0)
|
|
);
|
|
|
|
create table if not exists training_annotations (
|
|
id bigserial primary key,
|
|
attempt_id bigint not null references training_attempts(id) on delete cascade,
|
|
annotation_type text not null,
|
|
payload jsonb not null default '{}'::jsonb,
|
|
created_ts timestamptz not null default now()
|
|
);
|
|
|
|
create table if not exists training_scores (
|
|
id bigserial primary key,
|
|
attempt_id bigint not null references training_attempts(id) on delete cascade,
|
|
category text not null,
|
|
score numeric(10,2) not null default 0,
|
|
max_score numeric(10,2) not null default 0,
|
|
feedback jsonb not null default '{}'::jsonb,
|
|
created_ts timestamptz not null default now(),
|
|
unique (attempt_id, category)
|
|
);
|
|
|
|
create table if not exists training_progress (
|
|
id bigserial primary key,
|
|
user_id text not null default 'local',
|
|
skill_tag text not null,
|
|
attempt_count integer not null default 0,
|
|
avg_score numeric(10,2) not null default 0,
|
|
last_attempt_ts timestamptz,
|
|
weakness_tags jsonb not null default '[]'::jsonb,
|
|
meta jsonb not null default '{}'::jsonb,
|
|
updated_at timestamptz not null default now(),
|
|
unique (user_id, skill_tag)
|
|
);
|
|
|
|
create index if not exists idx_training_cases_type_status
|
|
on training_cases (case_type, status);
|
|
|
|
create index if not exists idx_training_cases_instrument_tf_decision
|
|
on training_cases (instrument_id, timeframe, decision_ts desc);
|
|
|
|
create index if not exists idx_training_cases_source_event
|
|
on training_cases (source_event_type, source_event_id);
|
|
|
|
create index if not exists idx_training_case_tasks_case_order
|
|
on training_case_tasks (case_id, sort_order, id);
|
|
|
|
create index if not exists idx_training_attempts_case_user
|
|
on training_attempts (case_id, user_id, started_ts desc);
|
|
|
|
create index if not exists idx_training_annotations_attempt_type
|
|
on training_annotations (attempt_id, annotation_type);
|
|
|
|
create index if not exists idx_training_scores_attempt
|
|
on training_scores (attempt_id);
|
|
|
|
create index if not exists idx_training_progress_user
|
|
on training_progress (user_id, avg_score);
|