139 lines
4.6 KiB
SQL
139 lines
4.6 KiB
SQL
-- Add columns to support parent tasks (Etapes)
|
|
ALTER TABLE "public"."tasks"
|
|
ADD COLUMN "is_parent" boolean DEFAULT false NOT NULL;
|
|
|
|
ALTER TABLE "public"."tasks"
|
|
ADD COLUMN "parent_task_id" text;
|
|
|
|
ALTER TABLE "public"."tasks"
|
|
ADD CONSTRAINT "tasks_parent_task_id_fkey"
|
|
FOREIGN KEY ("parent_task_id") REFERENCES "public"."tasks"("id") ON DELETE SET NULL;
|
|
|
|
ALTER TABLE "public"."tasks"
|
|
ADD CONSTRAINT "tasks_parent_self_reference_check"
|
|
CHECK (NOT "is_parent" OR "parent_task_id" IS NULL);
|
|
|
|
COMMENT ON COLUMN "public"."tasks"."is_parent" IS 'When TRUE, the task represents an Etape (parent task) that can group other tasks';
|
|
COMMENT ON COLUMN "public"."tasks"."parent_task_id" IS 'Optional reference to the Etape (parent task) this task belongs to';
|
|
|
|
-- Index to quickly fetch Etapes per tablo and their ordering, plus children by parent
|
|
CREATE INDEX "tasks_parent_scope_idx" ON "public"."tasks" USING btree ("tablo_id", "is_parent", "position");
|
|
CREATE INDEX "tasks_parent_task_id_idx" ON "public"."tasks" USING btree ("parent_task_id");
|
|
|
|
-- Update tasks_with_assignee view to expose the new fields
|
|
CREATE OR REPLACE VIEW "public"."tasks_with_assignee" WITH ("security_invoker"='true') AS
|
|
SELECT
|
|
t.id,
|
|
t.tablo_id,
|
|
t.title,
|
|
t.description,
|
|
t.status,
|
|
t.assignee_id,
|
|
t.position,
|
|
t.created_at,
|
|
t.updated_at,
|
|
p.name AS assignee_name,
|
|
p.avatar_url AS assignee_avatar,
|
|
t.is_parent,
|
|
t.parent_task_id
|
|
FROM "public"."tasks" t
|
|
LEFT JOIN "public"."profiles" p ON t.assignee_id = p.id;
|
|
|
|
ALTER TABLE "public"."tasks_with_assignee" OWNER TO "postgres";
|
|
|
|
COMMENT ON VIEW "public"."tasks_with_assignee" IS 'View that returns tasks with assignee information from profiles';
|
|
|
|
-- Update RLS policies so only tablo owners can manipulate Etapes
|
|
DROP POLICY IF EXISTS "Users can view tasks from their tablos" ON "public"."tasks";
|
|
DROP POLICY IF EXISTS "Users can create tasks in their tablos" ON "public"."tasks";
|
|
DROP POLICY IF EXISTS "Users can update tasks in their tablos" ON "public"."tasks";
|
|
DROP POLICY IF EXISTS "Users can delete tasks in their tablos" ON "public"."tasks";
|
|
|
|
CREATE POLICY "Users can view tasks from their tablos"
|
|
ON "public"."tasks"
|
|
FOR SELECT
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM "public"."tablo_access" "ta"
|
|
WHERE "ta"."tablo_id" = "tasks"."tablo_id"
|
|
AND "ta"."user_id" = auth.uid()
|
|
AND "ta"."is_active" = true
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Users can create tasks in their tablos"
|
|
ON "public"."tasks"
|
|
FOR INSERT
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM "public"."tablo_access" "ta"
|
|
WHERE "ta"."tablo_id" = "tasks"."tablo_id"
|
|
AND "ta"."user_id" = auth.uid()
|
|
AND "ta"."is_active" = true
|
|
)
|
|
AND (
|
|
NOT "tasks"."is_parent"
|
|
OR EXISTS (
|
|
SELECT 1 FROM "public"."tablos" "t"
|
|
WHERE "t"."id" = "tasks"."tablo_id"
|
|
AND "t"."owner_id" = auth.uid()
|
|
)
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Users can update tasks in their tablos"
|
|
ON "public"."tasks"
|
|
FOR UPDATE
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM "public"."tablo_access" "ta"
|
|
WHERE "ta"."tablo_id" = "tasks"."tablo_id"
|
|
AND "ta"."user_id" = auth.uid()
|
|
AND "ta"."is_active" = true
|
|
)
|
|
AND (
|
|
NOT "tasks"."is_parent"
|
|
OR EXISTS (
|
|
SELECT 1 FROM "public"."tablos" "t"
|
|
WHERE "t"."id" = "tasks"."tablo_id"
|
|
AND "t"."owner_id" = auth.uid()
|
|
)
|
|
)
|
|
)
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM "public"."tablo_access" "ta"
|
|
WHERE "ta"."tablo_id" = "tasks"."tablo_id"
|
|
AND "ta"."user_id" = auth.uid()
|
|
AND "ta"."is_active" = true
|
|
)
|
|
AND (
|
|
NOT "tasks"."is_parent"
|
|
OR EXISTS (
|
|
SELECT 1 FROM "public"."tablos" "t"
|
|
WHERE "t"."id" = "tasks"."tablo_id"
|
|
AND "t"."owner_id" = auth.uid()
|
|
)
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Users can delete tasks in their tablos"
|
|
ON "public"."tasks"
|
|
FOR DELETE
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM "public"."tablo_access" "ta"
|
|
WHERE "ta"."tablo_id" = "tasks"."tablo_id"
|
|
AND "ta"."user_id" = auth.uid()
|
|
AND "ta"."is_active" = true
|
|
)
|
|
AND (
|
|
NOT "tasks"."is_parent"
|
|
OR EXISTS (
|
|
SELECT 1 FROM "public"."tablos" "t"
|
|
WHERE "t"."id" = "tasks"."tablo_id"
|
|
AND "t"."owner_id" = auth.uid()
|
|
)
|
|
)
|
|
);
|
|
|