mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 09:50:01 +00:00
98 lines
3.1 KiB
PL/PgSQL
98 lines
3.1 KiB
PL/PgSQL
CREATE POLICY "Homeowners can create own tickets"
|
|
ON public.homeowner_requests
|
|
FOR INSERT
|
|
TO authenticated
|
|
WITH CHECK (
|
|
owner_id IN (
|
|
SELECT o.id
|
|
FROM public.owners o
|
|
WHERE o.user_id = auth.uid()
|
|
AND o.association_id = homeowner_requests.association_id
|
|
)
|
|
);
|
|
|
|
CREATE POLICY "Homeowners can view own tickets"
|
|
ON public.homeowner_requests
|
|
FOR SELECT
|
|
TO authenticated
|
|
USING (
|
|
owner_id IN (
|
|
SELECT o.id
|
|
FROM public.owners o
|
|
WHERE o.user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
CREATE OR REPLACE FUNCTION public.can_comment_on_entity(_user_id uuid, _entity_type text, _entity_id uuid)
|
|
RETURNS boolean
|
|
LANGUAGE sql
|
|
STABLE SECURITY DEFINER
|
|
SET search_path TO 'public'
|
|
AS $$
|
|
SELECT CASE _entity_type
|
|
WHEN 'board_vote' THEN EXISTS (
|
|
SELECT 1 FROM public.board_votes bv
|
|
WHERE bv.id = _entity_id
|
|
AND public.user_belongs_to_association(_user_id, bv.association_id)
|
|
)
|
|
WHEN 'legal_matter' THEN EXISTS (
|
|
SELECT 1 FROM public.legal_matters lm
|
|
WHERE lm.id = _entity_id
|
|
AND public.user_belongs_to_association(_user_id, lm.association_id)
|
|
)
|
|
WHEN 'bid_quote' THEN EXISTS (
|
|
SELECT 1 FROM public.bids_quotes bq
|
|
WHERE bq.id = _entity_id
|
|
AND public.user_belongs_to_association(_user_id, bq.association_id)
|
|
)
|
|
WHEN 'arc_application' THEN EXISTS (
|
|
SELECT 1 FROM public.arc_applications aa
|
|
WHERE aa.id = _entity_id
|
|
AND public.user_belongs_to_association(_user_id, aa.association_id)
|
|
)
|
|
WHEN 'client_request' THEN EXISTS (
|
|
SELECT 1 FROM public.client_requests cr
|
|
WHERE cr.id = _entity_id
|
|
AND public.user_belongs_to_association(_user_id, cr.association_id)
|
|
)
|
|
WHEN 'homeowner_request' THEN EXISTS (
|
|
SELECT 1
|
|
FROM public.homeowner_requests hr
|
|
LEFT JOIN public.owners o ON o.id = hr.owner_id
|
|
WHERE hr.id = _entity_id
|
|
AND (
|
|
public.user_belongs_to_association(_user_id, hr.association_id)
|
|
OR o.user_id = _user_id
|
|
)
|
|
)
|
|
WHEN 'status_update' THEN public.can_comment_on_status_update(_user_id, _entity_id)
|
|
ELSE false
|
|
END
|
|
$$;
|
|
|
|
CREATE POLICY "Participants can view ticket comments"
|
|
ON public.entity_comments
|
|
FOR SELECT
|
|
TO authenticated
|
|
USING (
|
|
entity_type = 'homeowner_request'
|
|
AND public.can_comment_on_entity(auth.uid(), entity_type, entity_id)
|
|
);
|
|
|
|
DROP TRIGGER IF EXISTS create_form_inbox_from_homeowner_request ON public.homeowner_requests;
|
|
CREATE TRIGGER create_form_inbox_from_homeowner_request
|
|
AFTER INSERT ON public.homeowner_requests
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.create_form_inbox_entry_from_homeowner_request();
|
|
|
|
DROP TRIGGER IF EXISTS notify_staff_on_homeowner_request ON public.homeowner_requests;
|
|
CREATE TRIGGER notify_staff_on_homeowner_request
|
|
AFTER INSERT ON public.homeowner_requests
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.notify_staff_on_homeowner_request();
|
|
|
|
DROP TRIGGER IF EXISTS notify_homeowner_on_ticket_comment ON public.entity_comments;
|
|
CREATE TRIGGER notify_homeowner_on_ticket_comment
|
|
AFTER INSERT ON public.entity_comments
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.notify_homeowner_on_ticket_comment(); |