collective-lists/supabase/migrations/008_tasks.sql

-- Migration 008: task_lists and tasks
--
-- No trash for tasks (per analysis §4.4): deletion is immediate.
-- Completed tasks remain visible at the bottom of the list.

-- ── task_lists ───────────────────────────────────────────────────────────────

CREATE TABLE public.task_lists (
    id            uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    collective_id uuid        NOT NULL REFERENCES public.collectives(id) ON DELETE CASCADE,
    name          text        NOT NULL,
    created_by    uuid        NOT NULL REFERENCES public.users(id) ON DELETE RESTRICT,
    created_at    timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE public.task_lists ENABLE ROW LEVEL SECURITY;

CREATE INDEX task_lists_collective_idx ON public.task_lists (collective_id);

-- ── tasks ────────────────────────────────────────────────────────────────────

CREATE TABLE public.tasks (
    id           uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
    list_id      uuid        NOT NULL REFERENCES public.task_lists(id) ON DELETE CASCADE,
    title        text        NOT NULL,
    is_completed boolean     NOT NULL DEFAULT false,
    completed_by uuid        NULL REFERENCES public.users(id) ON DELETE SET NULL,
    completed_at timestamptz NULL,
    sort_order   integer     NOT NULL DEFAULT 0,
    created_by   uuid        NOT NULL REFERENCES public.users(id) ON DELETE RESTRICT,
    created_at   timestamptz NOT NULL DEFAULT now(),
    -- Invariant: completed_at NOT NULL ⇔ completed_by NOT NULL ⇔ is_completed = true
    CONSTRAINT tasks_completion_consistent CHECK (
        (is_completed = true  AND completed_at IS NOT NULL AND completed_by IS NOT NULL)
        OR
        (is_completed = false AND completed_at IS NULL     AND completed_by IS NULL)
    )
);

ALTER TABLE public.tasks ENABLE ROW LEVEL SECURITY;

CREATE INDEX tasks_list_order_idx ON public.tasks (list_id, sort_order);

-- ── Helper: resolve collective for a task list ───────────────────────────────

CREATE OR REPLACE FUNCTION public.task_list_collective_id(p_list_id uuid)
RETURNS uuid
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
    SELECT collective_id FROM public.task_lists WHERE id = p_list_id;
$$;

-- ── RLS: task_lists ──────────────────────────────────────────────────────────

CREATE POLICY task_lists_select
    ON public.task_lists FOR SELECT
    USING (public.is_member(collective_id));

CREATE POLICY task_lists_insert
    ON public.task_lists FOR INSERT
    WITH CHECK (
        public.is_active_member(collective_id)
        AND created_by = auth.uid()
    );

CREATE POLICY task_lists_update
    ON public.task_lists FOR UPDATE
    USING  (public.is_active_member(collective_id))
    WITH CHECK (public.is_active_member(collective_id));

CREATE POLICY task_lists_delete
    ON public.task_lists FOR DELETE
    USING (public.is_active_member(collective_id));

-- ── RLS: tasks ───────────────────────────────────────────────────────────────

CREATE POLICY tasks_select
    ON public.tasks FOR SELECT
    USING (public.is_member(public.task_list_collective_id(list_id)));

CREATE POLICY tasks_insert
    ON public.tasks FOR INSERT
    WITH CHECK (
        public.is_active_member(public.task_list_collective_id(list_id))
        AND created_by = auth.uid()
    );

CREATE POLICY tasks_update
    ON public.tasks FOR UPDATE
    USING  (public.is_active_member(public.task_list_collective_id(list_id)))
    WITH CHECK (public.is_active_member(public.task_list_collective_id(list_id)));

CREATE POLICY tasks_delete
    ON public.tasks FOR DELETE
    USING (public.is_active_member(public.task_list_collective_id(list_id)));