mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 01:40:01 +00:00
61 lines
2.6 KiB
SQL
61 lines
2.6 KiB
SQL
|
|
-- Add new roles to app_role enum for committee members
|
|
ALTER TYPE public.app_role ADD VALUE IF NOT EXISTS 'board_member';
|
|
ALTER TYPE public.app_role ADD VALUE IF NOT EXISTS 'arc_member';
|
|
ALTER TYPE public.app_role ADD VALUE IF NOT EXISTS 'fining_member';
|
|
|
|
-- Create role_permissions table for feature-area CRUD permissions
|
|
CREATE TABLE public.role_permissions (
|
|
id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
role TEXT NOT NULL,
|
|
feature_area TEXT NOT NULL,
|
|
can_read BOOLEAN NOT NULL DEFAULT false,
|
|
can_create BOOLEAN NOT NULL DEFAULT false,
|
|
can_edit BOOLEAN NOT NULL DEFAULT false,
|
|
can_delete BOOLEAN NOT NULL DEFAULT false,
|
|
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
|
|
UNIQUE(role, feature_area)
|
|
);
|
|
|
|
ALTER TABLE public.role_permissions ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Only admins can manage permissions
|
|
CREATE POLICY "Admins can manage role_permissions"
|
|
ON public.role_permissions
|
|
FOR ALL
|
|
TO authenticated
|
|
USING (public.has_role(auth.uid(), 'admin'::app_role))
|
|
WITH CHECK (public.has_role(auth.uid(), 'admin'::app_role));
|
|
|
|
-- All authenticated can read permissions (needed to enforce them client-side)
|
|
CREATE POLICY "Authenticated can read role_permissions"
|
|
ON public.role_permissions
|
|
FOR SELECT
|
|
TO authenticated
|
|
USING (true);
|
|
|
|
-- Seed default permissions for all roles and feature areas
|
|
INSERT INTO public.role_permissions (role, feature_area, can_read, can_create, can_edit, can_delete)
|
|
SELECT r.role, f.feature_area,
|
|
CASE WHEN r.role = 'homeowner' THEN
|
|
f.feature_area IN ('Owner Portal', 'Documents', 'Announcements', 'Calendar')
|
|
ELSE true END,
|
|
CASE WHEN r.role = 'homeowner' THEN false
|
|
WHEN r.role IN ('board_member','arc_member','fining_member') THEN false
|
|
ELSE true END,
|
|
CASE WHEN r.role = 'homeowner' THEN false
|
|
WHEN r.role IN ('board_member','arc_member','fining_member') THEN false
|
|
ELSE true END,
|
|
CASE WHEN r.role IN ('admin') THEN true ELSE false END
|
|
FROM
|
|
(VALUES ('admin'),('manager'),('staff'),('employee'),('homeowner'),('board_member'),('arc_member'),('fining_member')) AS r(role),
|
|
(VALUES ('Financial'),('Owners & Units'),('Violations'),('ARC Applications'),('Collections'),('Documents'),('Announcements'),('Calendar'),('Vendors & Bills'),('Projects'),('Email & Notifications'),('Legal Matters'),('Inspections'),('Owner Portal'),('Board Votes'),('Estoppels'),('Parking')) AS f(feature_area)
|
|
ON CONFLICT (role, feature_area) DO NOTHING;
|
|
|
|
-- Trigger for updated_at
|
|
CREATE TRIGGER update_role_permissions_updated_at
|
|
BEFORE UPDATE ON public.role_permissions
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.update_updated_at_column();
|