100 lines
3.9 KiB
PL/PgSQL
100 lines
3.9 KiB
PL/PgSQL
-- Event Tasks: kanban-style task management scoped to events
|
|
-- Uses the same KanbanBoard component but with event-specific tables
|
|
|
|
-- ============================================================
|
|
-- 1. Task columns: kanban columns per event
|
|
-- ============================================================
|
|
CREATE TABLE event_task_columns (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
position INT NOT NULL DEFAULT 0,
|
|
color TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_event_task_columns_event ON event_task_columns(event_id);
|
|
|
|
-- ============================================================
|
|
-- 2. Tasks: cards within columns
|
|
-- ============================================================
|
|
CREATE TABLE event_tasks (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
column_id UUID NOT NULL REFERENCES event_task_columns(id) ON DELETE CASCADE,
|
|
event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
position INT NOT NULL DEFAULT 0,
|
|
priority TEXT CHECK (priority IN ('low', 'medium', 'high', 'urgent')),
|
|
due_date DATE,
|
|
color TEXT,
|
|
assignee_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
created_at TIMESTAMPTZ DEFAULT now(),
|
|
updated_at TIMESTAMPTZ DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_event_tasks_column ON event_tasks(column_id);
|
|
CREATE INDEX idx_event_tasks_event ON event_tasks(event_id);
|
|
CREATE INDEX idx_event_tasks_assignee ON event_tasks(assignee_id);
|
|
|
|
-- ============================================================
|
|
-- 3. RLS policies
|
|
-- ============================================================
|
|
ALTER TABLE event_task_columns ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE event_tasks ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Task columns: org members can view, editors+ can manage
|
|
CREATE POLICY "Org members can view event task columns" ON event_task_columns FOR SELECT
|
|
USING (EXISTS (
|
|
SELECT 1 FROM events e
|
|
JOIN org_members om ON e.org_id = om.org_id
|
|
WHERE e.id = event_task_columns.event_id AND om.user_id = auth.uid()
|
|
));
|
|
|
|
CREATE POLICY "Editors can manage event task columns" ON event_task_columns FOR ALL
|
|
USING (EXISTS (
|
|
SELECT 1 FROM events e
|
|
JOIN org_members om ON e.org_id = om.org_id
|
|
WHERE e.id = event_task_columns.event_id AND om.user_id = auth.uid() AND om.role IN ('owner', 'admin', 'editor')
|
|
));
|
|
|
|
-- Tasks: org members can view, editors+ can manage
|
|
CREATE POLICY "Org members can view event tasks" ON event_tasks FOR SELECT
|
|
USING (EXISTS (
|
|
SELECT 1 FROM events e
|
|
JOIN org_members om ON e.org_id = om.org_id
|
|
WHERE e.id = event_tasks.event_id AND om.user_id = auth.uid()
|
|
));
|
|
|
|
CREATE POLICY "Editors can manage event tasks" ON event_tasks FOR ALL
|
|
USING (EXISTS (
|
|
SELECT 1 FROM events e
|
|
JOIN org_members om ON e.org_id = om.org_id
|
|
WHERE e.id = event_tasks.event_id AND om.user_id = auth.uid() AND om.role IN ('owner', 'admin', 'editor')
|
|
));
|
|
|
|
-- ============================================================
|
|
-- 4. Enable realtime
|
|
-- ============================================================
|
|
ALTER PUBLICATION supabase_realtime ADD TABLE event_task_columns;
|
|
ALTER PUBLICATION supabase_realtime ADD TABLE event_tasks;
|
|
|
|
-- ============================================================
|
|
-- 5. Auto-seed default columns when a new event is created
|
|
-- ============================================================
|
|
CREATE OR REPLACE FUNCTION public.seed_event_task_columns()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
INSERT INTO public.event_task_columns (event_id, name, position) VALUES
|
|
(NEW.id, 'To Do', 0),
|
|
(NEW.id, 'In Progress', 1),
|
|
(NEW.id, 'Done', 2);
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
CREATE TRIGGER on_event_created_seed_task_columns
|
|
AFTER INSERT ON events
|
|
FOR EACH ROW EXECUTE FUNCTION public.seed_event_task_columns();
|