Codex
189 lines
11 KiB
SQL
189 lines
11 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,
|
|
skill_tag text not null,
|
|
chapter_ref text,
|
|
source_event_type text,
|
|
source_event_id bigint,
|
|
source_event_refs jsonb not null default '[]'::jsonb,
|
|
start_ts timestamptz not null,
|
|
decision_ts timestamptz not null,
|
|
end_ts timestamptz not null,
|
|
difficulty text not null default 'medium',
|
|
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(),
|
|
check (start_ts <= decision_ts),
|
|
check (decision_ts <= end_ts)
|
|
);
|
|
|
|
alter table if exists training_cases add column if not exists instrument_id bigint;
|
|
alter table if exists training_cases add column if not exists timeframe text not null default '1m';
|
|
alter table if exists training_cases add column if not exists case_type text not null default 'unknown';
|
|
alter table if exists training_cases add column if not exists skill_tag text not null default 'general';
|
|
alter table if exists training_cases add column if not exists chapter_ref text;
|
|
alter table if exists training_cases add column if not exists source_event_type text;
|
|
alter table if exists training_cases add column if not exists source_event_id bigint;
|
|
alter table if exists training_cases add column if not exists source_event_refs jsonb not null default '[]'::jsonb;
|
|
alter table if exists training_cases add column if not exists start_ts timestamptz not null default now();
|
|
alter table if exists training_cases add column if not exists decision_ts timestamptz not null default now();
|
|
alter table if exists training_cases add column if not exists end_ts timestamptz not null default now();
|
|
alter table if exists training_cases add column if not exists difficulty text not null default 'medium';
|
|
alter table if exists training_cases add column if not exists answer_key jsonb not null default '{}'::jsonb;
|
|
alter table if exists training_cases add column if not exists status text not null default 'draft';
|
|
alter table if exists training_cases add column if not exists meta jsonb not null default '{}'::jsonb;
|
|
alter table if exists training_cases add column if not exists created_at timestamptz not null default now();
|
|
alter table if exists training_cases add column if not exists updated_at timestamptz not null default now();
|
|
|
|
create index if not exists idx_training_cases_instr_tf_decision_desc
|
|
on training_cases (instrument_id, timeframe, decision_ts desc);
|
|
|
|
create index if not exists idx_training_cases_skill_status_created_desc
|
|
on training_cases (skill_tag, status, created_at desc);
|
|
|
|
create index if not exists idx_training_cases_type_status_created_desc
|
|
on training_cases (case_type, status, created_at desc);
|
|
|
|
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,
|
|
skill_tag text not null,
|
|
prompt text not null,
|
|
required_annotation_types jsonb not null default '[]'::jsonb,
|
|
max_score numeric(8,2) not null default 0,
|
|
sort_order integer not null default 0,
|
|
status text not null default 'active',
|
|
meta jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
alter table if exists training_case_tasks add column if not exists case_id bigint;
|
|
alter table if exists training_case_tasks add column if not exists task_type text not null default 'annotation';
|
|
alter table if exists training_case_tasks add column if not exists skill_tag text not null default 'general';
|
|
alter table if exists training_case_tasks add column if not exists prompt text not null default '';
|
|
alter table if exists training_case_tasks add column if not exists required_annotation_types jsonb not null default '[]'::jsonb;
|
|
alter table if exists training_case_tasks add column if not exists max_score numeric(8,2) not null default 0;
|
|
alter table if exists training_case_tasks add column if not exists sort_order integer not null default 0;
|
|
alter table if exists training_case_tasks add column if not exists status text not null default 'active';
|
|
alter table if exists training_case_tasks add column if not exists meta jsonb not null default '{}'::jsonb;
|
|
alter table if exists training_case_tasks add column if not exists created_at timestamptz not null default now();
|
|
alter table if exists training_case_tasks add column if not exists updated_at timestamptz not null default now();
|
|
|
|
create index if not exists idx_training_case_tasks_case_sort
|
|
on training_case_tasks (case_id, sort_order, id);
|
|
|
|
create index if not exists idx_training_case_tasks_skill_type
|
|
on training_case_tasks (skill_tag, task_type);
|
|
|
|
create table if not exists training_attempts (
|
|
id bigserial primary key,
|
|
case_id bigint not null references training_cases(id),
|
|
user_id text not null default 'local',
|
|
status text not null default 'started',
|
|
current_frame_index integer not null default 0,
|
|
started_at timestamptz not null default now(),
|
|
submitted_at timestamptz,
|
|
total_score numeric(8,2),
|
|
meta jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
alter table if exists training_attempts add column if not exists case_id bigint;
|
|
alter table if exists training_attempts add column if not exists user_id text not null default 'local';
|
|
alter table if exists training_attempts add column if not exists status text not null default 'started';
|
|
alter table if exists training_attempts add column if not exists current_frame_index integer not null default 0;
|
|
alter table if exists training_attempts add column if not exists started_at timestamptz not null default now();
|
|
alter table if exists training_attempts add column if not exists submitted_at timestamptz;
|
|
alter table if exists training_attempts add column if not exists total_score numeric(8,2);
|
|
alter table if exists training_attempts add column if not exists meta jsonb not null default '{}'::jsonb;
|
|
alter table if exists training_attempts add column if not exists created_at timestamptz not null default now();
|
|
alter table if exists training_attempts add column if not exists updated_at timestamptz not null default now();
|
|
|
|
create index if not exists idx_training_attempts_user_status_created_desc
|
|
on training_attempts (user_id, status, created_at desc);
|
|
|
|
create index if not exists idx_training_attempts_case_user_created_desc
|
|
on training_attempts (case_id, user_id, created_at desc);
|
|
|
|
create table if not exists training_annotations (
|
|
id bigserial primary key,
|
|
attempt_id bigint not null references training_attempts(id) on delete cascade,
|
|
task_id bigint references training_case_tasks(id),
|
|
user_id text not null default 'local',
|
|
annotation_type text not null,
|
|
payload jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
alter table if exists training_annotations add column if not exists attempt_id bigint;
|
|
alter table if exists training_annotations add column if not exists task_id bigint;
|
|
alter table if exists training_annotations add column if not exists user_id text not null default 'local';
|
|
alter table if exists training_annotations add column if not exists annotation_type text not null default 'note';
|
|
alter table if exists training_annotations add column if not exists payload jsonb not null default '{}'::jsonb;
|
|
alter table if exists training_annotations add column if not exists created_at timestamptz not null default now();
|
|
alter table if exists training_annotations add column if not exists updated_at timestamptz not null default now();
|
|
|
|
create index if not exists idx_training_annotations_attempt_type_created
|
|
on training_annotations (attempt_id, annotation_type, created_at);
|
|
|
|
create table if not exists training_scores (
|
|
id bigserial primary key,
|
|
attempt_id bigint not null references training_attempts(id) on delete cascade,
|
|
task_id bigint references training_case_tasks(id),
|
|
user_id text not null default 'local',
|
|
category text not null,
|
|
score numeric(8,2) not null,
|
|
max_score numeric(8,2) not null,
|
|
feedback jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
alter table if exists training_scores add column if not exists attempt_id bigint;
|
|
alter table if exists training_scores add column if not exists task_id bigint;
|
|
alter table if exists training_scores add column if not exists user_id text not null default 'local';
|
|
alter table if exists training_scores add column if not exists category text not null default 'general';
|
|
alter table if exists training_scores add column if not exists score numeric(8,2) not null default 0;
|
|
alter table if exists training_scores add column if not exists max_score numeric(8,2) not null default 0;
|
|
alter table if exists training_scores add column if not exists feedback jsonb not null default '{}'::jsonb;
|
|
alter table if exists training_scores add column if not exists created_at timestamptz not null default now();
|
|
alter table if exists training_scores add column if not exists updated_at timestamptz not null default now();
|
|
|
|
create index if not exists idx_training_scores_attempt_category
|
|
on training_scores (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(8,4),
|
|
last_attempt_at timestamptz,
|
|
weakness_tags jsonb not null default '[]'::jsonb,
|
|
meta jsonb not null default '{}'::jsonb,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
unique (user_id, skill_tag)
|
|
);
|
|
|
|
alter table if exists training_progress add column if not exists user_id text not null default 'local';
|
|
alter table if exists training_progress add column if not exists skill_tag text not null default 'general';
|
|
alter table if exists training_progress add column if not exists attempt_count integer not null default 0;
|
|
alter table if exists training_progress add column if not exists avg_score numeric(8,4);
|
|
alter table if exists training_progress add column if not exists last_attempt_at timestamptz;
|
|
alter table if exists training_progress add column if not exists weakness_tags jsonb not null default '[]'::jsonb;
|
|
alter table if exists training_progress add column if not exists meta jsonb not null default '{}'::jsonb;
|
|
alter table if exists training_progress add column if not exists created_at timestamptz not null default now();
|
|
alter table if exists training_progress add column if not exists updated_at timestamptz not null default now();
|
|
|
|
create index if not exists idx_training_progress_user_skill
|
|
on training_progress (user_id, skill_tag);
|