TeamVis Release
62 lines
2.7 KiB
SQL
62 lines
2.7 KiB
SQL
-- Compliance-Modul.
|
|
-- ====================================================================
|
|
-- Frameworks (DVGW G 1000, ISO 9001, DSGVO, …) liegen als YAML im Repo
|
|
-- (compliance/frameworks/*.yaml). Die DB speichert nur:
|
|
--
|
|
-- 1) Welche Frameworks sind in dieser Instanz aktiv (site_settings).
|
|
-- 2) Welche Position erfuellt welche Framework-Rolle (bindings).
|
|
--
|
|
-- Die App rechnet daraus den Gap-Report (welche Rollen unbesetzt sind,
|
|
-- welche Qualifikationen fehlen, welche Bestellungen ablaufen).
|
|
|
|
-- --------------------------------------------------------------------
|
|
-- 1) Aktive Frameworks auf site_settings
|
|
-- --------------------------------------------------------------------
|
|
|
|
alter table public.site_settings
|
|
add column if not exists active_compliance_frameworks text[] not null
|
|
default array[]::text[];
|
|
|
|
-- --------------------------------------------------------------------
|
|
-- 2) compliance_role_bindings — Framework-Rolle ⇄ Stelle
|
|
-- --------------------------------------------------------------------
|
|
-- framework_id und role_id sind Strings (ID aus dem YAML), keine FKs:
|
|
-- die "Quelle" der Rollen liegt im Repo, nicht in der DB. Die App
|
|
-- validiert beim Schreiben, dass der Wert im aktuell geladenen
|
|
-- Framework existiert.
|
|
|
|
create table if not exists public.compliance_role_bindings (
|
|
id uuid primary key default gen_random_uuid(),
|
|
framework_id text not null,
|
|
role_id text not null,
|
|
position_id uuid not null references public.positions(id) on delete cascade,
|
|
appointed_on date,
|
|
appointment_valid_to date,
|
|
notes text,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
constraint compliance_role_bindings_appointment_range
|
|
check (appointment_valid_to is null
|
|
or appointed_on is null
|
|
or appointment_valid_to >= appointed_on),
|
|
constraint compliance_role_bindings_unique
|
|
unique (framework_id, role_id, position_id)
|
|
);
|
|
|
|
create index if not exists compliance_role_bindings_framework_idx
|
|
on public.compliance_role_bindings (framework_id, role_id);
|
|
create index if not exists compliance_role_bindings_position_idx
|
|
on public.compliance_role_bindings (position_id);
|
|
|
|
drop trigger if exists trg_compliance_role_bindings_updated_at
|
|
on public.compliance_role_bindings;
|
|
create trigger trg_compliance_role_bindings_updated_at
|
|
before update on public.compliance_role_bindings
|
|
for each row execute function public.set_updated_at();
|
|
|
|
-- --------------------------------------------------------------------
|
|
-- 3) RLS — service_role only, keine anon-Policies
|
|
-- --------------------------------------------------------------------
|
|
|
|
alter table public.compliance_role_bindings enable row level security;
|