71 lines
No EOL
2.4 KiB
PL/PgSQL
71 lines
No EOL
2.4 KiB
PL/PgSQL
-- Migration pour créer la fonction is_member_of_org et les policies pour les notes
|
|
|
|
-- Créer la fonction is_member_of_org
|
|
create or replace function public.is_member_of_org(org_id uuid)
|
|
returns boolean
|
|
language sql
|
|
stable
|
|
security definer
|
|
as $$
|
|
select exists (
|
|
select 1 from public.organization_members m
|
|
where m.user_id = auth.uid()
|
|
and m.org_id = is_member_of_org.org_id
|
|
and m.revoked = false
|
|
);
|
|
$$;
|
|
|
|
-- Ajouter aussi la possibilité pour le staff d'accéder aux notes (comme pour les tickets)
|
|
create or replace function public.is_staff()
|
|
returns boolean
|
|
language sql
|
|
stable
|
|
as $$
|
|
select exists (
|
|
select 1 from public.staff_users s
|
|
where s.user_id = auth.uid() and coalesce(s.is_staff, false) = true
|
|
);
|
|
$$;
|
|
|
|
-- Créer la table notes si elle n'existe pas déjà
|
|
create table if not exists public.notes (
|
|
id uuid primary key default gen_random_uuid(),
|
|
contract_id varchar not null,
|
|
content text not null,
|
|
author varchar null,
|
|
source varchar default 'Espace Paie',
|
|
organization_id uuid null references public.organizations(id) on delete cascade,
|
|
created_at timestamptz not null default now(),
|
|
source_created_at timestamptz null
|
|
);
|
|
|
|
-- Activer RLS sur la table notes
|
|
alter table public.notes enable row level security;
|
|
|
|
-- Policies de lecture
|
|
drop policy if exists notes_member_read on public.notes;
|
|
create policy notes_member_read on public.notes
|
|
for select using (
|
|
organization_id is null or is_member_of_org(organization_id)
|
|
);
|
|
|
|
drop policy if exists notes_staff_read on public.notes;
|
|
create policy notes_staff_read on public.notes
|
|
for select using (public.is_staff());
|
|
|
|
-- Policies d'insertion
|
|
drop policy if exists notes_member_insert on public.notes;
|
|
create policy notes_member_insert on public.notes
|
|
for insert with check (
|
|
organization_id is null or is_member_of_org(organization_id)
|
|
);
|
|
|
|
drop policy if exists notes_staff_insert on public.notes;
|
|
create policy notes_staff_insert on public.notes
|
|
for insert with check (public.is_staff());
|
|
|
|
-- Index pour les performances
|
|
create index if not exists notes_contract_id_idx on public.notes(contract_id);
|
|
create index if not exists notes_organization_id_idx on public.notes(organization_id);
|
|
create index if not exists notes_created_at_idx on public.notes(created_at desc);
|
|
create index if not exists notes_source_created_at_idx on public.notes(source_created_at desc) where source_created_at is not null; |