TeamVis Release
234 lines
10 KiB
PL/PgSQL
234 lines
10 KiB
PL/PgSQL
-- Organigramm-Modul + Modul-Toggle.
|
|
-- ====================================================================
|
|
-- Generisches Datenmodell, das verschiedene Compliance-Regelwerke
|
|
-- (TSM, ISO 9001, DSGVO, …) abbilden kann. Stellen werden von Personen
|
|
-- getrennt, damit Vakanzen ("N.N.") und Personalwechsel ohne Layout-
|
|
-- Bruch funktionieren.
|
|
--
|
|
-- Tabellen:
|
|
-- org_units — Sparte/Bereich (Strom, Gas, Finanzen, …)
|
|
-- positions — Stelle in der Hierarchie
|
|
-- position_assignments — Person ⇄ Stelle, mit Zeitraum
|
|
-- deputies — Stelle ⇄ Stelle (Vertretung), mit Zeitraum
|
|
-- external_parties — Fremdfirma / Behörde / Dienstleister
|
|
-- service_relations — Externe ⇄ Pflichtbereich
|
|
--
|
|
-- Erweiterung an site_settings:
|
|
-- enabled_modules — text[]: aktivierte Produkt-Module
|
|
-- organigram_visibility — public | internal | trusted
|
|
--
|
|
-- RLS-Modell: Tabellen sind RLS-aktiv, KEINE anon-Policies. Reads
|
|
-- laufen ausschließlich über service_role aus der App. Die Sichtbarkeits-
|
|
-- Logik (public/internal/trusted) prüft der App-Code, nicht die DB.
|
|
|
|
-- --------------------------------------------------------------------
|
|
-- 1) Module-Toggle + Organigramm-Sichtbarkeit auf site_settings
|
|
-- --------------------------------------------------------------------
|
|
|
|
alter table public.site_settings
|
|
add column if not exists enabled_modules text[] not null
|
|
default array['business_cards']::text[];
|
|
|
|
alter table public.site_settings
|
|
add column if not exists organigram_visibility text not null
|
|
default 'internal';
|
|
|
|
-- Sichtbarkeitswerte einschränken (idempotent)
|
|
alter table public.site_settings
|
|
drop constraint if exists site_settings_organigram_visibility_check;
|
|
alter table public.site_settings
|
|
add constraint site_settings_organigram_visibility_check
|
|
check (organigram_visibility in ('public', 'internal', 'trusted'));
|
|
|
|
-- --------------------------------------------------------------------
|
|
-- 2) org_units — Sparte / Bereich (Hierarchie auch innerhalb möglich)
|
|
-- --------------------------------------------------------------------
|
|
|
|
create table if not exists public.org_units (
|
|
id uuid primary key default gen_random_uuid(),
|
|
parent_id uuid references public.org_units(id) on delete restrict,
|
|
slug text not null unique,
|
|
name text not null,
|
|
description text,
|
|
sort_order integer not null default 0,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
constraint org_units_no_self_parent check (parent_id is null or parent_id <> id)
|
|
);
|
|
|
|
create index if not exists org_units_parent_id_idx
|
|
on public.org_units (parent_id);
|
|
|
|
-- --------------------------------------------------------------------
|
|
-- 3) positions — Stelle (entkoppelt von Person)
|
|
-- --------------------------------------------------------------------
|
|
|
|
create table if not exists public.positions (
|
|
id uuid primary key default gen_random_uuid(),
|
|
parent_id uuid references public.positions(id) on delete restrict,
|
|
org_unit_id uuid references public.org_units(id) on delete set null,
|
|
name text not null,
|
|
short_name text,
|
|
description text,
|
|
-- Anzeigewert wenn keine Person zugewiesen ist (Default: 'N.N.').
|
|
vacant_label text not null default 'N.N.',
|
|
sort_order integer not null default 0,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
constraint positions_no_self_parent check (parent_id is null or parent_id <> id)
|
|
);
|
|
|
|
create index if not exists positions_parent_id_idx
|
|
on public.positions (parent_id);
|
|
create index if not exists positions_org_unit_id_idx
|
|
on public.positions (org_unit_id);
|
|
|
|
-- --------------------------------------------------------------------
|
|
-- 4) position_assignments — Person sitzt auf Stelle (mit Zeitraum)
|
|
-- --------------------------------------------------------------------
|
|
|
|
create table if not exists public.position_assignments (
|
|
id uuid primary key default gen_random_uuid(),
|
|
employee_id uuid not null references public.employees(id) on delete cascade,
|
|
position_id uuid not null references public.positions(id) on delete cascade,
|
|
-- Optionaler Zusatz, z.B. "kommissarisch", "i.V."
|
|
role_label text,
|
|
is_primary boolean not null default false,
|
|
valid_from date not null default current_date,
|
|
valid_to date,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
constraint position_assignments_valid_range
|
|
check (valid_to is null or valid_to >= valid_from)
|
|
);
|
|
|
|
create index if not exists position_assignments_employee_id_idx
|
|
on public.position_assignments (employee_id);
|
|
create index if not exists position_assignments_position_id_idx
|
|
on public.position_assignments (position_id);
|
|
-- Schnelle "wer ist gerade aktiv auf Stelle X"-Queries
|
|
create index if not exists position_assignments_active_idx
|
|
on public.position_assignments (position_id, valid_from, valid_to);
|
|
|
|
-- --------------------------------------------------------------------
|
|
-- 5) deputies — Stelle wird vertreten von Stelle (mit Zeitraum)
|
|
-- --------------------------------------------------------------------
|
|
|
|
create table if not exists public.deputies (
|
|
id uuid primary key default gen_random_uuid(),
|
|
position_id uuid not null references public.positions(id) on delete cascade,
|
|
deputy_position_id uuid not null references public.positions(id) on delete cascade,
|
|
-- Reihenfolge bei mehreren Vertretungen (1. Vertretung, 2. Vertretung, …)
|
|
sort_order integer not null default 0,
|
|
valid_from date not null default current_date,
|
|
valid_to date,
|
|
created_at timestamptz not null default now(),
|
|
constraint deputies_no_self check (position_id <> deputy_position_id),
|
|
constraint deputies_valid_range
|
|
check (valid_to is null or valid_to >= valid_from)
|
|
);
|
|
|
|
create index if not exists deputies_position_id_idx
|
|
on public.deputies (position_id);
|
|
create index if not exists deputies_deputy_position_id_idx
|
|
on public.deputies (deputy_position_id);
|
|
|
|
-- --------------------------------------------------------------------
|
|
-- 6) external_parties — Fremdfirma / Behörde / Dienstleister
|
|
-- --------------------------------------------------------------------
|
|
|
|
create table if not exists public.external_parties (
|
|
id uuid primary key default gen_random_uuid(),
|
|
name text not null,
|
|
-- Art der Partei: dienstleister, behoerde, kooperationspartner, sonstige
|
|
kind text not null default 'dienstleister',
|
|
contact_name text,
|
|
contact_email text,
|
|
contact_phone text,
|
|
website text,
|
|
address text,
|
|
notes text,
|
|
sort_order integer not null default 0,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
constraint external_parties_kind_check
|
|
check (kind in ('dienstleister', 'behoerde', 'kooperationspartner', 'sonstige'))
|
|
);
|
|
|
|
-- --------------------------------------------------------------------
|
|
-- 7) service_relations — Externe deckt Pflichtbereich ab
|
|
-- --------------------------------------------------------------------
|
|
|
|
create table if not exists public.service_relations (
|
|
id uuid primary key default gen_random_uuid(),
|
|
external_party_id uuid not null references public.external_parties(id) on delete cascade,
|
|
-- Polymorphe Referenz: scope_kind = 'org_unit' | 'position' | 'global'
|
|
scope_kind text not null,
|
|
scope_id uuid,
|
|
-- Klartext, z.B. "Brandschutzbeauftragter", "Betriebsarzt", "Personalwesen"
|
|
service_label text not null,
|
|
description text,
|
|
valid_from date not null default current_date,
|
|
valid_to date,
|
|
created_at timestamptz not null default now(),
|
|
constraint service_relations_scope_kind_check
|
|
check (scope_kind in ('org_unit', 'position', 'global')),
|
|
constraint service_relations_scope_id_consistency
|
|
check (
|
|
(scope_kind = 'global' and scope_id is null)
|
|
or (scope_kind in ('org_unit', 'position') and scope_id is not null)
|
|
),
|
|
constraint service_relations_valid_range
|
|
check (valid_to is null or valid_to >= valid_from)
|
|
);
|
|
|
|
create index if not exists service_relations_external_party_id_idx
|
|
on public.service_relations (external_party_id);
|
|
create index if not exists service_relations_scope_idx
|
|
on public.service_relations (scope_kind, scope_id);
|
|
|
|
-- --------------------------------------------------------------------
|
|
-- 8) Trigger: updated_at automatisch aktualisieren
|
|
-- --------------------------------------------------------------------
|
|
|
|
create or replace function public.set_updated_at()
|
|
returns trigger as $$
|
|
begin
|
|
new.updated_at = now();
|
|
return new;
|
|
end;
|
|
$$ language plpgsql;
|
|
|
|
drop trigger if exists trg_org_units_updated_at on public.org_units;
|
|
create trigger trg_org_units_updated_at
|
|
before update on public.org_units
|
|
for each row execute function public.set_updated_at();
|
|
|
|
drop trigger if exists trg_positions_updated_at on public.positions;
|
|
create trigger trg_positions_updated_at
|
|
before update on public.positions
|
|
for each row execute function public.set_updated_at();
|
|
|
|
drop trigger if exists trg_position_assignments_updated_at on public.position_assignments;
|
|
create trigger trg_position_assignments_updated_at
|
|
before update on public.position_assignments
|
|
for each row execute function public.set_updated_at();
|
|
|
|
drop trigger if exists trg_external_parties_updated_at on public.external_parties;
|
|
create trigger trg_external_parties_updated_at
|
|
before update on public.external_parties
|
|
for each row execute function public.set_updated_at();
|
|
|
|
-- --------------------------------------------------------------------
|
|
-- 9) RLS — alle neuen Tabellen sind defensive
|
|
-- --------------------------------------------------------------------
|
|
-- Reads laufen ausschließlich über service_role (Admin + öffentliche
|
|
-- Org-Page checken Visibility selbst). KEINE anon-Policies.
|
|
|
|
alter table public.org_units enable row level security;
|
|
alter table public.positions enable row level security;
|
|
alter table public.position_assignments enable row level security;
|
|
alter table public.deputies enable row level security;
|
|
alter table public.external_parties enable row level security;
|
|
alter table public.service_relations enable row level security;
|