apps/backend/src/infrastructure/database/migrations/0009_sprint_d_knowledge_training_today.sqlSQL
-- Sprint D — Knowledge, training, briefings and Employee Today.
create table if not exists knowledge_articles (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
slug text not null,
title text not null,
article_type text not null default 'sop',
status text not null default 'active',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint knowledge_articles_org_slug_unique unique (organization_id, slug),
constraint knowledge_articles_type_check check (article_type in ('sop','policy','howto','reference','safety','other')),
constraint knowledge_articles_status_check check (status in ('active','archived'))
);
create table if not exists knowledge_article_versions (
id uuid primary key default gen_random_uuid(),
article_id uuid not null references knowledge_articles(id) on delete restrict,
version_number integer not null,
status text not null default 'draft',
source_locale text not null default 'de-DE',
title text not null,
body_markdown text not null,
published_at timestamptz,
created_at timestamptz not null default now(),
created_by uuid references user_accounts(id) on delete set null,
constraint knowledge_article_version_unique unique (article_id, version_number),
constraint knowledge_article_version_status_check check (status in ('draft','published','retired'))
);
create unique index if not exists idx_knowledge_one_published_version
on knowledge_article_versions(article_id)
where status='published';
create table if not exists knowledge_article_version_translations (
article_version_id uuid not null references knowledge_article_versions(id) on delete cascade,
locale text not null,
title text not null,
body_markdown text not null,
translation_status text not null default 'translated',
source_version_updated_at timestamptz,
primary key (article_version_id, locale),
constraint knowledge_translation_status_check check (translation_status in ('draft','translated','reviewed','published','outdated'))
);
create table if not exists knowledge_article_acknowledgements (
id uuid primary key default gen_random_uuid(),
article_version_id uuid not null references knowledge_article_versions(id) on delete restrict,
employee_id uuid not null references employees(id) on delete restrict,
locale_shown text not null,
acknowledged_at timestamptz not null default now(),
constraint knowledge_ack_unique unique (article_version_id, employee_id)
);
create table if not exists training_modules (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
code text not null,
title text not null,
status text not null default 'active',
source_locale text not null default 'de-DE',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint training_modules_org_code_unique unique (organization_id, code),
constraint training_modules_status_check check (status in ('active','archived'))
);
create table if not exists training_module_translations (
training_module_id uuid not null references training_modules(id) on delete cascade,
locale text not null,
title text not null,
description text,
primary key (training_module_id, locale)
);
create table if not exists training_steps (
id uuid primary key default gen_random_uuid(),
training_module_id uuid not null references training_modules(id) on delete cascade,
sort_order integer not null,
step_type text not null,
title text not null,
body text,
knowledge_article_id uuid references knowledge_articles(id) on delete restrict,
requires_manager_signoff boolean not null default false,
created_at timestamptz not null default now(),
constraint training_steps_order_unique unique (training_module_id, sort_order),
constraint training_steps_type_check check (step_type in ('read','task','quiz','manager_signoff','video','other'))
);
create table if not exists training_step_translations (
training_step_id uuid not null references training_steps(id) on delete cascade,
locale text not null,
title text not null,
body text,
primary key (training_step_id, locale)
);
create table if not exists training_quiz_questions (
id uuid primary key default gen_random_uuid(),
training_step_id uuid not null references training_steps(id) on delete cascade,
prompt text not null,
options jsonb not null,
correct_option_key text not null,
created_at timestamptz not null default now()
);
create table if not exists training_quiz_question_translations (
question_id uuid not null references training_quiz_questions(id) on delete cascade,
locale text not null,
prompt text not null,
options jsonb not null,
primary key (question_id, locale)
);
create table if not exists training_assignments (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
employee_id uuid not null references employees(id) on delete restrict,
training_module_id uuid not null references training_modules(id) on delete restrict,
assigned_at timestamptz not null default now(),
due_at timestamptz,
status text not null default 'assigned',
assigned_by uuid references user_accounts(id) on delete set null,
completed_at timestamptz,
constraint training_assignment_status_check check (status in ('assigned','in_progress','completed','cancelled')),
constraint training_assignment_unique unique (employee_id, training_module_id, status)
);
create table if not exists training_step_progress (
id uuid primary key default gen_random_uuid(),
training_assignment_id uuid not null references training_assignments(id) on delete cascade,
training_step_id uuid not null references training_steps(id) on delete restrict,
status text not null default 'not_started',
started_at timestamptz,
completed_at timestamptz,
quiz_score numeric(8,4),
manager_signed_off_by uuid references user_accounts(id) on delete set null,
manager_signed_off_at timestamptz,
locale_shown text,
constraint training_step_progress_unique unique (training_assignment_id, training_step_id),
constraint training_step_progress_status_check check (status in ('not_started','in_progress','completed','failed','awaiting_signoff'))
);
create table if not exists daily_briefings (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
business_date date not null,
source_locale text not null default 'de-DE',
title text not null,
body text not null,
status text not null default 'draft',
published_at timestamptz,
created_at timestamptz not null default now(),
created_by uuid references user_accounts(id) on delete set null,
constraint daily_briefing_location_date_unique unique (location_id, business_date),
constraint daily_briefing_status_check check (status in ('draft','published','archived'))
);
create table if not exists daily_briefing_translations (
briefing_id uuid not null references daily_briefings(id) on delete cascade,
locale text not null,
title text not null,
body text not null,
primary key (briefing_id, locale)
);
create table if not exists daily_briefing_acknowledgements (
briefing_id uuid not null references daily_briefings(id) on delete restrict,
employee_id uuid not null references employees(id) on delete restrict,
locale_shown text not null,
acknowledged_at timestamptz not null default now(),
primary key (briefing_id, employee_id)
);
insert into permissions(permission_key, description) values
('knowledge.read', 'Read knowledge articles'),
('knowledge.manage', 'Manage knowledge article drafts/translations'),
('knowledge.publish', 'Publish knowledge article versions'),
('knowledge.acknowledge', 'Acknowledge required knowledge'),
('training.read', 'Read assigned training'),
('training.manage', 'Manage training modules and assignments'),
('training.progress', 'Progress own training'),
('training.signoff', 'Manager sign-off for training'),
('briefings.read', 'Read daily briefings'),
('briefings.manage', 'Create and manage daily briefings'),
('briefings.publish', 'Publish daily briefings'),
('briefings.acknowledge', 'Acknowledge daily briefings')
on conflict (permission_key) do nothing;