mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 01:40:01 +00:00
49 lines
2.2 KiB
SQL
49 lines
2.2 KiB
SQL
CREATE TABLE public.committees (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
association_id UUID NOT NULL REFERENCES public.associations(id) ON DELETE CASCADE,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
created_by UUID REFERENCES auth.users(id),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX idx_committees_assoc ON public.committees(association_id);
|
|
|
|
ALTER TABLE public.committees ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Members view committees"
|
|
ON public.committees FOR SELECT
|
|
USING (public.user_belongs_to_association(auth.uid(), association_id));
|
|
CREATE POLICY "Admins manage committees - insert"
|
|
ON public.committees FOR INSERT
|
|
WITH CHECK (public.has_role(auth.uid(), 'admin'::public.app_role) OR public.has_role(auth.uid(), 'manager'::public.app_role));
|
|
CREATE POLICY "Admins manage committees - update"
|
|
ON public.committees FOR UPDATE
|
|
USING (public.has_role(auth.uid(), 'admin'::public.app_role) OR public.has_role(auth.uid(), 'manager'::public.app_role));
|
|
CREATE POLICY "Admins manage committees - delete"
|
|
ON public.committees FOR DELETE
|
|
USING (public.has_role(auth.uid(), 'admin'::public.app_role) OR public.has_role(auth.uid(), 'manager'::public.app_role));
|
|
|
|
CREATE TRIGGER update_committees_updated_at
|
|
BEFORE UPDATE ON public.committees
|
|
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();
|
|
|
|
ALTER TABLE public.arc_committee_members ADD COLUMN committee_id UUID REFERENCES public.committees(id) ON DELETE CASCADE;
|
|
ALTER TABLE public.arc_committee_members ADD COLUMN role TEXT;
|
|
CREATE INDEX idx_arc_committee_members_committee ON public.arc_committee_members(committee_id);
|
|
|
|
-- Backfill: create a default ARC committee per association that has existing members
|
|
INSERT INTO public.committees (association_id, name, description)
|
|
SELECT DISTINCT association_id, 'Architectural Review Committee', 'Default ARC committee'
|
|
FROM public.arc_committee_members
|
|
WHERE association_id IS NOT NULL
|
|
ON CONFLICT DO NOTHING;
|
|
|
|
UPDATE public.arc_committee_members m
|
|
SET committee_id = c.id
|
|
FROM public.committees c
|
|
WHERE m.committee_id IS NULL
|
|
AND c.association_id = m.association_id
|
|
AND c.name = 'Architectural Review Committee'; |