346 lines
16 KiB
PL/PgSQL
346 lines
16 KiB
PL/PgSQL
-- =====================================================================
|
|
-- GameDev Tracker — Migration v10 « Refonte »
|
|
-- À coller dans : Supabase > SQL Editor > New query > Run
|
|
-- Idempotent : sans danger si déjà appliquée en tout ou partie.
|
|
--
|
|
-- Ce que fait cette migration :
|
|
-- 0. Colonnes « tableau » manquantes (dont tasks.tags) et non-NULL
|
|
-- 1. Commentaires sur les tâches et les bugs
|
|
-- 2. Pièces jointes (captures d'écran) sur les tâches et les bugs
|
|
-- 3. Parité tâches/bugs : plusieurs pôles par bug
|
|
-- 4. Suppression des colonnes « de compatibilité » devenues des pièges
|
|
-- (profiles.role, tasks.pole, bugs.assignee_id)
|
|
-- 5. Réordonnancement du Kanban en UNE requête transactionnelle
|
|
-- 6. Suppression d'un projet réservée à son créateur ou à un admin
|
|
-- 7. Temps réel sur les projets
|
|
-- =====================================================================
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 0) Colonnes « tableau » garanties — À EXÉCUTER EN PREMIER
|
|
--
|
|
-- `tasks.tags` n'existait que dans schema.sql (installations neuves) et
|
|
-- n'a jamais été ajoutée par les migrations : sur une base mise à jour
|
|
-- pas à pas, la colonne manquait et l'application plantait en tentant
|
|
-- d'afficher les étiquettes d'une tâche.
|
|
--
|
|
-- Tant qu'on y est, on ré-affirme l'invariant sur TOUTES les colonnes
|
|
-- tableau : jamais NULL, toujours un tableau vide par défaut.
|
|
-- ---------------------------------------------------------------------
|
|
alter table public.tasks add column if not exists tags text[] not null default '{}';
|
|
alter table public.tasks add column if not exists poles member_role[] not null default '{}';
|
|
alter table public.tasks add column if not exists assignee_ids uuid[] not null default '{}';
|
|
alter table public.tasks add column if not exists checklist jsonb not null default '[]'::jsonb;
|
|
alter table public.bugs add column if not exists assignee_ids uuid[] not null default '{}';
|
|
alter table public.profiles add column if not exists roles member_role[] not null default '{}';
|
|
|
|
-- Remet à vide les valeurs NULL héritées, puis verrouille la contrainte.
|
|
do $$
|
|
declare
|
|
r record;
|
|
begin
|
|
for r in
|
|
select * from (values
|
|
('tasks', 'tags', '''{}''::text[]'),
|
|
('tasks', 'poles', '''{}''::member_role[]'),
|
|
('tasks', 'assignee_ids', '''{}''::uuid[]'),
|
|
('tasks', 'checklist', '''[]''::jsonb'),
|
|
('bugs', 'poles', '''{}''::member_role[]'),
|
|
('bugs', 'assignee_ids', '''{}''::uuid[]'),
|
|
('profiles', 'roles', '''{}''::member_role[]')
|
|
) as t(tbl, col, empty)
|
|
loop
|
|
if exists (
|
|
select 1 from information_schema.columns
|
|
where table_schema = 'public' and table_name = r.tbl and column_name = r.col
|
|
) then
|
|
execute format('update public.%I set %I = %s where %I is null', r.tbl, r.col, r.empty, r.col);
|
|
execute format('alter table public.%I alter column %I set default %s', r.tbl, r.col, r.empty);
|
|
execute format('alter table public.%I alter column %I set not null', r.tbl, r.col);
|
|
end if;
|
|
end loop;
|
|
end $$;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 1) Parité bugs / tâches : plusieurs pôles par bug
|
|
-- ---------------------------------------------------------------------
|
|
alter table public.bugs
|
|
add column if not exists poles member_role[] not null default '{}';
|
|
|
|
do $$ begin
|
|
if exists (select 1 from information_schema.columns
|
|
where table_schema = 'public' and table_name = 'bugs' and column_name = 'pole') then
|
|
update public.bugs set poles = array[pole] where pole is not null and poles = '{}';
|
|
end if;
|
|
end $$;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 2) Commentaires (fil de discussion sur une tâche ou un bug)
|
|
-- ---------------------------------------------------------------------
|
|
create table if not exists public.comments (
|
|
id uuid primary key default gen_random_uuid(),
|
|
project_id uuid not null references public.projects(id) on delete cascade,
|
|
entity_kind text not null check (entity_kind in ('task', 'bug')),
|
|
entity_id uuid not null,
|
|
author_id uuid references public.profiles(id) on delete set null,
|
|
body text not null check (length(btrim(body)) > 0),
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create index if not exists idx_comments_entity on public.comments(entity_kind, entity_id);
|
|
create index if not exists idx_comments_project on public.comments(project_id);
|
|
|
|
alter table public.comments enable row level security;
|
|
|
|
drop policy if exists comments_select on public.comments;
|
|
drop policy if exists comments_insert on public.comments;
|
|
drop policy if exists comments_update on public.comments;
|
|
drop policy if exists comments_delete on public.comments;
|
|
|
|
-- Toute l'équipe lit la discussion ; on n'écrit qu'en son propre nom ;
|
|
-- on ne modifie/supprime que ses propres messages (ou n'importe lequel si admin).
|
|
create policy comments_select on public.comments
|
|
for select to authenticated using (true);
|
|
create policy comments_insert on public.comments
|
|
for insert to authenticated with check (author_id = auth.uid());
|
|
create policy comments_update on public.comments
|
|
for update to authenticated
|
|
using (author_id = auth.uid()) with check (author_id = auth.uid());
|
|
create policy comments_delete on public.comments
|
|
for delete to authenticated
|
|
using (
|
|
author_id = auth.uid()
|
|
or exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 3) Pièces jointes (captures d'écran, logs, fichiers)
|
|
-- ---------------------------------------------------------------------
|
|
create table if not exists public.attachments (
|
|
id uuid primary key default gen_random_uuid(),
|
|
project_id uuid not null references public.projects(id) on delete cascade,
|
|
entity_kind text not null check (entity_kind in ('task', 'bug')),
|
|
entity_id uuid not null,
|
|
uploader_id uuid references public.profiles(id) on delete set null,
|
|
name text not null,
|
|
path text not null, -- chemin dans le bucket "attachments"
|
|
size bigint not null default 0,
|
|
mime text,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create index if not exists idx_attachments_entity on public.attachments(entity_kind, entity_id);
|
|
create index if not exists idx_attachments_project on public.attachments(project_id);
|
|
|
|
alter table public.attachments enable row level security;
|
|
|
|
drop policy if exists attachments_select on public.attachments;
|
|
drop policy if exists attachments_insert on public.attachments;
|
|
drop policy if exists attachments_delete on public.attachments;
|
|
|
|
create policy attachments_select on public.attachments
|
|
for select to authenticated using (true);
|
|
create policy attachments_insert on public.attachments
|
|
for insert to authenticated with check (uploader_id = auth.uid());
|
|
create policy attachments_delete on public.attachments
|
|
for delete to authenticated
|
|
using (
|
|
uploader_id = auth.uid()
|
|
or exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
|
|
);
|
|
|
|
-- Bucket de stockage des fichiers joints.
|
|
insert into storage.buckets (id, name, public)
|
|
values ('attachments', 'attachments', true)
|
|
on conflict (id) do nothing;
|
|
|
|
drop policy if exists attachments_read on storage.objects;
|
|
drop policy if exists attachments_write on storage.objects;
|
|
drop policy if exists attachments_remove on storage.objects;
|
|
|
|
create policy attachments_read on storage.objects
|
|
for select using (bucket_id = 'attachments');
|
|
create policy attachments_write on storage.objects
|
|
for insert to authenticated with check (bucket_id = 'attachments');
|
|
create policy attachments_remove on storage.objects
|
|
for delete to authenticated using (bucket_id = 'attachments');
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 4) Notifications : sur QUOI pointe la notification
|
|
-- (une notif de commentaire ouvre la tâche ou le bug commenté)
|
|
-- ---------------------------------------------------------------------
|
|
alter table public.notifications
|
|
add column if not exists entity_kind text not null default 'task';
|
|
|
|
update public.notifications
|
|
set entity_kind = kind
|
|
where kind in ('task', 'bug') and entity_kind is distinct from kind;
|
|
|
|
create index if not exists idx_notifications_user_unread
|
|
on public.notifications(user_id, read, created_at desc);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 5) Réordonnancement du Kanban en UNE requête
|
|
-- Avant : une requête UPDATE par carte de la colonne (jusqu'à 30
|
|
-- allers-retours non transactionnels — un échec corrompait l'ordre).
|
|
-- ---------------------------------------------------------------------
|
|
create or replace function public.reorder_tasks(p_status task_status, p_ids uuid[])
|
|
returns void
|
|
language plpgsql
|
|
security invoker -- la RLS de l'appelant s'applique normalement
|
|
set search_path = public
|
|
as $$
|
|
begin
|
|
update public.tasks t
|
|
set status = p_status,
|
|
position = ordered.idx
|
|
from (
|
|
select id, (ord - 1)::double precision as idx
|
|
from unnest(p_ids) with ordinality as u(id, ord)
|
|
) ordered
|
|
where t.id = ordered.id;
|
|
end $$;
|
|
|
|
grant execute on function public.reorder_tasks(task_status, uuid[]) to authenticated;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 6) Suppression d'un projet : créateur ou administrateur seulement
|
|
-- (avant : n'importe quel membre pouvait effacer un projet entier
|
|
-- et tout son contenu en cascade)
|
|
-- ---------------------------------------------------------------------
|
|
drop policy if exists projects_all on public.projects;
|
|
drop policy if exists projects_select on public.projects;
|
|
drop policy if exists projects_insert on public.projects;
|
|
drop policy if exists projects_update on public.projects;
|
|
drop policy if exists projects_delete on public.projects;
|
|
|
|
create policy projects_select on public.projects
|
|
for select to authenticated using (true);
|
|
create policy projects_insert on public.projects
|
|
for insert to authenticated with check (true);
|
|
create policy projects_update on public.projects
|
|
for update to authenticated using (true) with check (true);
|
|
create policy projects_delete on public.projects
|
|
for delete to authenticated
|
|
using (
|
|
created_by = auth.uid()
|
|
or exists (select 1 from public.profiles p where p.id = auth.uid() and p.is_admin)
|
|
);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 7) Temps réel : projets, commentaires, pièces jointes
|
|
-- ---------------------------------------------------------------------
|
|
do $$ begin
|
|
alter publication supabase_realtime add table public.projects;
|
|
exception when duplicate_object then null; end $$;
|
|
do $$ begin
|
|
alter publication supabase_realtime add table public.comments;
|
|
exception when duplicate_object then null; end $$;
|
|
do $$ begin
|
|
alter publication supabase_realtime add table public.attachments;
|
|
exception when duplicate_object then null; end $$;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 8) Création de profil à l'inscription : couleur d'avatar variée
|
|
-- (avant, tout le monde héritait du même violet #7c5cff)
|
|
-- ---------------------------------------------------------------------
|
|
create or replace function public.handle_new_user()
|
|
returns trigger language plpgsql security definer set search_path = public as $$
|
|
declare
|
|
palette text[] := array['#7c5cff','#00d2b8','#ff5c8a','#ffa53c','#3ca8ff','#9bdb4d','#e15cff','#ff6b6b'];
|
|
r member_role;
|
|
begin
|
|
r := coalesce((new.raw_user_meta_data->>'role')::member_role, 'other');
|
|
insert into public.profiles (id, full_name, roles, avatar_color)
|
|
values (
|
|
new.id,
|
|
coalesce(new.raw_user_meta_data->>'full_name', split_part(new.email, '@', 1)),
|
|
array[r],
|
|
palette[1 + (abs(hashtext(new.id::text)) % array_length(palette, 1))]
|
|
)
|
|
on conflict (id) do nothing;
|
|
return new;
|
|
end $$;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 9) Suppression des colonnes « de compatibilité »
|
|
-- Elles doublonnaient les tableaux et devaient être re-synchronisées
|
|
-- à la main dans six endroits du code. On sauvegarde d'abord, puis
|
|
-- on supprime. À exécuter EN DERNIER (la fonction du point 8 ne les
|
|
-- utilise plus).
|
|
-- ---------------------------------------------------------------------
|
|
|
|
-- a) profiles.role → profiles.roles[1]
|
|
do $$ begin
|
|
if exists (select 1 from information_schema.columns
|
|
where table_schema = 'public' and table_name = 'profiles' and column_name = 'role') then
|
|
update public.profiles set roles = array[role] where roles = '{}' and role is not null;
|
|
alter table public.profiles drop column role;
|
|
end if;
|
|
end $$;
|
|
|
|
-- b) tasks.pole → tasks.poles[1]
|
|
do $$ begin
|
|
if exists (select 1 from information_schema.columns
|
|
where table_schema = 'public' and table_name = 'tasks' and column_name = 'pole') then
|
|
update public.tasks set poles = array[pole] where pole is not null and poles = '{}';
|
|
alter table public.tasks drop column pole;
|
|
end if;
|
|
end $$;
|
|
|
|
-- c) bugs.assignee_id → bugs.assignee_ids[1] · bugs.pole → bugs.poles[1]
|
|
do $$ begin
|
|
if exists (select 1 from information_schema.columns
|
|
where table_schema = 'public' and table_name = 'bugs' and column_name = 'assignee_id') then
|
|
update public.bugs set assignee_ids = array[assignee_id]
|
|
where assignee_id is not null and assignee_ids = '{}';
|
|
alter table public.bugs drop column assignee_id;
|
|
end if;
|
|
if exists (select 1 from information_schema.columns
|
|
where table_schema = 'public' and table_name = 'bugs' and column_name = 'pole') then
|
|
alter table public.bugs drop column pole;
|
|
end if;
|
|
end $$;
|
|
|
|
-- d) bugs.priority : plus exposée dans l'interface (la sévérité suffit).
|
|
-- La colonne reste en base, avec une valeur par défaut, pour ne rien
|
|
-- perdre — mais plus personne ne la remplit.
|
|
do $$ begin
|
|
if exists (select 1 from information_schema.columns
|
|
where table_schema = 'public' and table_name = 'bugs' and column_name = 'priority') then
|
|
alter table public.bugs alter column priority set default 'medium';
|
|
end if;
|
|
end $$;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 10) Index manquants sur les colonnes les plus filtrées
|
|
-- ---------------------------------------------------------------------
|
|
create index if not exists idx_tasks_project_status on public.tasks(project_id, status, position);
|
|
create index if not exists idx_bugs_project_status on public.bugs(project_id, status);
|
|
create index if not exists idx_time_logs_date on public.time_logs(project_id, logged_at);
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- 11) Temps passé sur un BUG (parité avec les tâches)
|
|
-- Une entrée de temps porte sur une tâche, OU sur un bug, OU sur
|
|
-- rien (activité libre : réunion, veille…). Jamais les deux.
|
|
-- ---------------------------------------------------------------------
|
|
alter table public.time_logs
|
|
add column if not exists bug_id uuid references public.bugs(id) on delete cascade;
|
|
|
|
create index if not exists idx_time_logs_bug on public.time_logs(bug_id);
|
|
|
|
do $$ begin
|
|
alter table public.time_logs
|
|
add constraint time_logs_one_target
|
|
check (not (task_id is not null and bug_id is not null));
|
|
exception when duplicate_object then null; end $$;
|
|
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Dernière ligne : forcer l'API à relire le schéma.
|
|
-- Sans ça, PostgREST peut continuer à servir l'ancienne structure et
|
|
-- renvoyer « Could not find the 'xxx' column ... in the schema cache »
|
|
-- pendant plusieurs minutes après la migration.
|
|
-- ---------------------------------------------------------------------
|
|
notify pgrst, 'reload schema';
|