mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 01:40:01 +00:00
88 lines
3.8 KiB
SQL
88 lines
3.8 KiB
SQL
CREATE TABLE public.association_directory_entries (
|
|
id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
association_id UUID NOT NULL REFERENCES public.associations(id) ON DELETE CASCADE,
|
|
owner_id UUID REFERENCES public.owners(id) ON DELETE SET NULL,
|
|
created_by UUID,
|
|
display_name TEXT NOT NULL,
|
|
title TEXT,
|
|
company TEXT,
|
|
email TEXT,
|
|
phone TEXT,
|
|
address TEXT,
|
|
category TEXT NOT NULL DEFAULT 'Resident',
|
|
notes TEXT,
|
|
is_owner_opt_in BOOLEAN NOT NULL DEFAULT false,
|
|
is_published BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
|
|
CONSTRAINT association_directory_display_name_length CHECK (char_length(display_name) BETWEEN 1 AND 150),
|
|
CONSTRAINT association_directory_title_length CHECK (title IS NULL OR char_length(title) <= 150),
|
|
CONSTRAINT association_directory_company_length CHECK (company IS NULL OR char_length(company) <= 150),
|
|
CONSTRAINT association_directory_email_length CHECK (email IS NULL OR char_length(email) <= 255),
|
|
CONSTRAINT association_directory_phone_length CHECK (phone IS NULL OR char_length(phone) <= 50),
|
|
CONSTRAINT association_directory_address_length CHECK (address IS NULL OR char_length(address) <= 255),
|
|
CONSTRAINT association_directory_category_length CHECK (char_length(category) BETWEEN 1 AND 80),
|
|
CONSTRAINT association_directory_notes_length CHECK (notes IS NULL OR char_length(notes) <= 1000)
|
|
);
|
|
|
|
CREATE UNIQUE INDEX association_directory_one_owner_opt_in
|
|
ON public.association_directory_entries (association_id, owner_id)
|
|
WHERE owner_id IS NOT NULL AND is_owner_opt_in = true;
|
|
|
|
CREATE INDEX idx_association_directory_entries_association
|
|
ON public.association_directory_entries (association_id, is_published, category, display_name);
|
|
|
|
ALTER TABLE public.association_directory_entries ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Staff can manage directory entries"
|
|
ON public.association_directory_entries
|
|
FOR ALL
|
|
TO authenticated
|
|
USING (public.has_role(auth.uid(), 'admin'::public.app_role) OR public.has_role(auth.uid(), 'manager'::public.app_role))
|
|
WITH CHECK (public.has_role(auth.uid(), 'admin'::public.app_role) OR public.has_role(auth.uid(), 'manager'::public.app_role));
|
|
|
|
CREATE POLICY "Association members can view published directory entries"
|
|
ON public.association_directory_entries
|
|
FOR SELECT
|
|
TO authenticated
|
|
USING (
|
|
is_published = true
|
|
AND association_id IN (SELECT public.get_user_association_ids())
|
|
);
|
|
|
|
CREATE POLICY "Owners can create their own opt in directory entry"
|
|
ON public.association_directory_entries
|
|
FOR INSERT
|
|
TO authenticated
|
|
WITH CHECK (
|
|
is_owner_opt_in = true
|
|
AND is_published = true
|
|
AND owner_id IN (SELECT id FROM public.owners WHERE user_id = auth.uid() AND association_id = association_directory_entries.association_id)
|
|
);
|
|
|
|
CREATE POLICY "Owners can update their own opt in directory entry"
|
|
ON public.association_directory_entries
|
|
FOR UPDATE
|
|
TO authenticated
|
|
USING (
|
|
is_owner_opt_in = true
|
|
AND owner_id IN (SELECT id FROM public.owners WHERE user_id = auth.uid() AND association_id = association_directory_entries.association_id)
|
|
)
|
|
WITH CHECK (
|
|
is_owner_opt_in = true
|
|
AND owner_id IN (SELECT id FROM public.owners WHERE user_id = auth.uid() AND association_id = association_directory_entries.association_id)
|
|
);
|
|
|
|
CREATE POLICY "Owners can delete their own opt in directory entry"
|
|
ON public.association_directory_entries
|
|
FOR DELETE
|
|
TO authenticated
|
|
USING (
|
|
is_owner_opt_in = true
|
|
AND owner_id IN (SELECT id FROM public.owners WHERE user_id = auth.uid() AND association_id = association_directory_entries.association_id)
|
|
);
|
|
|
|
CREATE TRIGGER update_association_directory_entries_updated_at
|
|
BEFORE UPDATE ON public.association_directory_entries
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.update_updated_at_column(); |