34 lines
1.1 KiB
SQL
34 lines
1.1 KiB
SQL
-- Add soft-delete support for tasks
|
|
ALTER TABLE "public"."tasks"
|
|
ADD COLUMN IF NOT EXISTS "deleted_at" timestamp with time zone;
|
|
|
|
COMMENT ON COLUMN "public"."tasks"."deleted_at" IS 'Timestamp when the task was soft-deleted';
|
|
|
|
-- Index used by tablo-scoped cleanup and active-task reads
|
|
CREATE INDEX IF NOT EXISTS "tasks_tablo_deleted_at_idx"
|
|
ON "public"."tasks" USING btree ("tablo_id", "deleted_at");
|
|
|
|
-- Keep tasks_with_assignee limited to active (not soft-deleted) tasks
|
|
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,
|
|
t.due_date
|
|
FROM "public"."tasks" t
|
|
LEFT JOIN "public"."profiles" p ON t.assignee_id = p.id
|
|
WHERE t.deleted_at IS NULL;
|
|
|
|
ALTER TABLE "public"."tasks_with_assignee" OWNER TO "postgres";
|
|
|
|
COMMENT ON VIEW "public"."tasks_with_assignee" IS 'View that returns active (non-deleted) tasks with assignee information from profiles';
|