File size: 3,865 Bytes
ad68e43 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 | -- Apply this migration after database/schema.sql
CREATE TABLE IF NOT EXISTS public.task_dependencies (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID NOT NULL REFERENCES public.projects ON DELETE CASCADE,
task_id UUID NOT NULL REFERENCES public.tasks ON DELETE CASCADE,
depends_on_task_id UUID NOT NULL REFERENCES public.tasks ON DELETE CASCADE,
created_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT task_dependencies_unique UNIQUE (project_id, task_id, depends_on_task_id),
CONSTRAINT task_dependencies_not_self CHECK (task_id <> depends_on_task_id)
);
CREATE INDEX IF NOT EXISTS idx_task_dependencies_project_id ON public.task_dependencies(project_id);
CREATE INDEX IF NOT EXISTS idx_task_dependencies_task_id ON public.task_dependencies(task_id);
CREATE INDEX IF NOT EXISTS idx_task_dependencies_depends_on_task_id ON public.task_dependencies(depends_on_task_id);
ALTER TABLE public.task_dependencies ENABLE ROW LEVEL SECURITY;
DO $$
BEGIN
IF NOT EXISTS (
SELECT 1
FROM pg_policies
WHERE schemaname = 'public'
AND tablename = 'task_dependencies'
AND policyname = 'Task dependencies visibility'
) THEN
CREATE POLICY "Task dependencies visibility" ON public.task_dependencies
FOR SELECT
USING (
EXISTS (
SELECT 1
FROM public.projects
WHERE projects.id = task_dependencies.project_id
AND (projects.owner_id = auth.uid() OR projects.is_public = true)
)
);
END IF;
IF NOT EXISTS (
SELECT 1
FROM pg_policies
WHERE schemaname = 'public'
AND tablename = 'task_dependencies'
AND policyname = 'Project owners can create task dependencies'
) THEN
CREATE POLICY "Project owners can create task dependencies" ON public.task_dependencies
FOR INSERT TO authenticated
WITH CHECK (
EXISTS (
SELECT 1
FROM public.projects
WHERE projects.id = task_dependencies.project_id
AND projects.owner_id = auth.uid()
)
);
END IF;
IF NOT EXISTS (
SELECT 1
FROM pg_policies
WHERE schemaname = 'public'
AND tablename = 'task_dependencies'
AND policyname = 'Project owners can update task dependencies'
) THEN
CREATE POLICY "Project owners can update task dependencies" ON public.task_dependencies
FOR UPDATE TO authenticated
USING (
EXISTS (
SELECT 1
FROM public.projects
WHERE projects.id = task_dependencies.project_id
AND projects.owner_id = auth.uid()
)
)
WITH CHECK (
EXISTS (
SELECT 1
FROM public.projects
WHERE projects.id = task_dependencies.project_id
AND projects.owner_id = auth.uid()
)
);
END IF;
IF NOT EXISTS (
SELECT 1
FROM pg_policies
WHERE schemaname = 'public'
AND tablename = 'task_dependencies'
AND policyname = 'Project owners can delete task dependencies'
) THEN
CREATE POLICY "Project owners can delete task dependencies" ON public.task_dependencies
FOR DELETE TO authenticated
USING (
EXISTS (
SELECT 1
FROM public.projects
WHERE projects.id = task_dependencies.project_id
AND projects.owner_id = auth.uid()
)
);
END IF;
END $$;
|