alpenwerk-hr/db/migrations/20260730120000_app_users_and_session_context.sql

-- Schritt 1 auf dem Weg weg von Supabase: eigene Benutzertabelle und ein
-- eigener Sitzungskontext.
--
-- Ziel ist ein Schema, das auf jedem PostgreSQL ab 15 läuft — Azure Flexible
-- Server, RDS, Cloud SQL, eigenes Blech. Heute hängt genau eine Sache an
-- Supabase: `auth.uid()`, die Kennung der angemeldeten Person. Sie steckt in
-- 72 Zeilen SQL, aber für die Absicherung zählt nur eine Stelle —
-- is_hr_user(), das alle 58 RLS-Policies aufrufen.
--
-- Diese Migration ist bewusst **additiv und beidseitig lauffähig**: die
-- Anwendung läuft danach unverändert auf Supabase weiter, während die neue
-- Zugriffsschicht daneben entsteht. Ein Umbau, der beide Enden gleichzeitig
-- bewegt, lässt sich nicht testen.

-- ═══ 1. Sitzungskontext ══════════════════════════════════════════
-- Wer gerade angemeldet ist, kommt künftig aus einer Sitzungsvariablen, die
-- die Zugriffsschicht **transaktionslokal** setzt (siehe lib/db).
--
-- Warum plpgsql und nicht `language sql`: eine SQL-Funktion wird beim Anlegen
-- geparst, und `auth.uid()` existiert auf einem gewöhnlichen PostgreSQL
-- nicht — die Funktion liesse sich dort gar nicht erst erzeugen. plpgsql löst
-- den Aufruf erst zur Laufzeit auf, und der Ausnahmeblock fängt die fehlende
-- Funktion ab. Genau das macht diese Migration auf beiden Systemen anwendbar.
create or replace function app_current_user_id()
returns uuid
language plpgsql
stable
security definer
set search_path = public, pg_temp
as $$
declare
  v_id uuid;
begin
  -- Vorrang hat der eigene Kontext. `true` als zweites Argument heisst:
  -- fehlt die Variable, kommt null statt eines Fehlers.
  v_id := nullif(current_setting('app.user_id', true), '')::uuid;
  if v_id is not null then
    return v_id;
  end if;

  -- Übergangsweise: solange die Anmeldung noch über GoTrue läuft. Fällt in
  -- der Abschlussmigration weg, zusammen mit den Fremdschlüsseln auf
  -- auth.users.
  begin
    execute 'select auth.uid()' into v_id;
  exception
    when undefined_function or invalid_schema_name or undefined_table then
      v_id := null;
  end;
  return v_id;
end;
$$;

comment on function app_current_user_id() is
  'Kennung der angemeldeten Person: erst app.user_id aus der Sitzung, ersatzweise auth.uid(). Der zweite Zweig ist Übergang.';

-- Zugeteilt wird nur an Rollen, die es auch gibt. Auf einem gewöhnlichen
-- PostgreSQL existieren anon/authenticated/service_role nicht, und ein
-- `grant` auf eine unbekannte Rolle bricht die Migration ab — dieselbe Datei
-- liefe dort also nicht. Genau das soll sie aber.
do $$
declare r text;
begin
  foreach r in array array['anon', 'authenticated', 'service_role'] loop
    if exists (select 1 from pg_roles where rolname = r) then
      execute format('grant execute on function app_current_user_id() to %I', r);
    end if;
  end loop;
end;
$$;

-- ═══ 2. Benutzertabelle ══════════════════════════════════════════
-- Tritt an die Stelle von auth.users. Die neun Fremdschlüssel, die heute
-- dorthin zeigen, wandern in der Abschlussmigration hierher.
create table if not exists app_users (
  id uuid primary key default gen_random_uuid(),
  -- Die `oid` aus dem Entra-Token. Unveränderlich, anders als die E-Mail:
  -- eine Namensänderung darf nicht zu einem neuen Konto führen.
  external_id text not null unique,
  email text not null,
  full_name text,
  created_at timestamptz not null default now(),
  last_seen_at timestamptz
);

comment on table app_users is
  'Ersetzt auth.users. external_id ist die oid des Identitätsanbieters, nicht die E-Mail.';

create index if not exists app_users_email_idx on app_users (lower(email));

alter table app_users enable row level security;

-- Sich selbst sehen darf jede:r Angemeldete; alles andere ist HR-Sache.
-- Ohne diese Policy käme die Anmeldung nicht an die eigene Zeile.
drop policy if exists "app_users_select_own" on app_users;
create policy "app_users_select_own" on app_users
  for select using (id = app_current_user_id() or is_hr_user());

do $$
begin
  if exists (select 1 from pg_roles where rolname = 'anon') then
    execute 'grant select on table app_users to anon';
  end if;
  if exists (select 1 from pg_roles where rolname = 'authenticated') then
    execute 'grant select on table app_users to authenticated';
  end if;
  if exists (select 1 from pg_roles where rolname = 'service_role') then
    execute 'grant all on table app_users to service_role';
  end if;
end;
$$;

-- ═══ 3. Die eine Brücke umlegen ══════════════════════════════════
-- Ab hier fragt die Absicherung nicht mehr Supabase, sondern den eigenen
-- Kontext. Die 58 Policies bleiben Wort für Wort unverändert — sie rufen
-- weiterhin is_hr_user() auf und merken davon nichts.
create or replace function is_hr_user()
returns boolean
language sql
security definer
set search_path = public, pg_temp
stable
as $$
  select exists (
    select 1 from profiles p
    where p.id = app_current_user_id() and p.role = 'hr' and p.is_active = true
  );
$$;

create or replace function current_hr_user_id()
returns uuid
language sql
security definer
set search_path = public, pg_temp
stable
as $$
  select p.id from profiles p
  where p.id = app_current_user_id() and p.role = 'hr' and p.is_active = true;
$$;

create or replace function current_actor_name()
returns text
language sql
stable
set search_path = public, pg_temp
as $$
  select coalesce(p.full_name, p.email, 'Unbekannt')
  from profiles p where p.id = app_current_user_id();
$$;

-- ═══ 4. Die fünf Policies mit direktem auth.uid() ════════════════
-- Die übrigen 53 laufen über is_hr_user() und brauchen nichts.
drop policy if exists "profiles_select_own" on profiles;
create policy "profiles_select_own" on profiles
  for select using (app_current_user_id() = id);

-- Die Namen stammen aus 20260714120000_hr_only_access.sql: „_owner", nicht
-- „_own". Mit dem falschen Namen bricht die Migration bei create policy ab.
drop policy if exists "hire_drafts_owner" on hire_drafts;
create policy "hire_drafts_owner" on hire_drafts
  for all
  using (created_by = app_current_user_id() and is_hr_user())
  with check (created_by = app_current_user_id() and is_hr_user());

drop policy if exists "saved_reports_owner" on saved_reports;
create policy "saved_reports_owner" on saved_reports
  for all
  using (created_by = app_current_user_id() and is_hr_user())
  with check (created_by = app_current_user_id() and is_hr_user());

-- ═══ 5. Gegenprobe ═══════════════════════════════════════════════
-- Ohne Kontext und ohne Anmeldung darf is_hr_user() nicht wahr sein. Das
-- klingt selbstverständlich und ist genau der Fehler, der eine ganze
-- Datenbank öffnet.
do $$
begin
  perform set_config('app.user_id', '', true);
  if is_hr_user() then
    raise exception 'is_hr_user() liefert ohne Sitzungskontext true — Abbruch.';
  end if;

  perform set_config('app.user_id', gen_random_uuid()::text, true);
  if is_hr_user() then
    raise exception 'is_hr_user() liefert für eine unbekannte Kennung true — Abbruch.';
  end if;

  -- Aufräumen: die Einstellung gilt bis zum Ende dieser Transaktion, und
  -- was danach in derselben Sitzung läuft, soll sie nicht erben.
  perform set_config('app.user_id', '', true);
end;
$$;