How to Build a User Permission Management with Lovable

1Create a complete RBAC schema in Supabase with these tables and function:
2
3Tables:
4
51. roles:
6   id uuid primary key default gen_random_uuid()
7   name text not null unique (e.g. 'admin', 'editor', 'viewer')
8   description text
9   is_system boolean default false (system roles cannot be deleted)
10   created_at timestamptz default now()
11
122. permissions:
13   id uuid primary key default gen_random_uuid()
14   name text not null unique (dot-notation: 'resource.action', e.g. 'reports.view', 'users.delete')
15   description text
16   resource text not null (the first part before the dot, for grouping)
17   created_at timestamptz default now()
18
193. role_permissions:
20   role_id uuid references roles(id) on delete cascade
21   permission_id uuid references permissions(id) on delete cascade
22   primary key (role_id, permission_id)
23   granted_by uuid references auth.users(id)
24   granted_at timestamptz default now()
25
264. user_roles:
27   user_id uuid references auth.users(id) on delete cascade
28   role_id uuid references roles(id) on delete cascade
29   primary key (user_id, role_id)
30   assigned_by uuid references auth.users(id)
31   assigned_at timestamptz default now()
32
335. permission_audit_log:
34   id uuid primary key default gen_random_uuid()
35   action text not null (e.g. 'role.permission.granted', 'user.role.assigned')
36   actor_id uuid references auth.users(id)
37   target_role_id uuid references roles(id)
38   target_user_id uuid references auth.users(id)
39   permission_id uuid references permissions(id)
40   metadata jsonb
41   created_at timestamptz default now()
42
43SQL Function:
44CREATE OR REPLACE FUNCTION has_permission(p_user_id uuid, p_permission text)
45RETURNS boolean
46LANGUAGE sql
47STABLE SECURITY DEFINER
48AS $$
49  SELECT EXISTS (
50    SELECT 1
51    FROM user_roles ur
52    JOIN role_permissions rp ON rp.role_id = ur.role_id
53    JOIN permissions p ON p.id = rp.permission_id
54    WHERE ur.user_id = p_user_id
55    AND p.name = p_permission
56  );
57$$;
58
59RLS:
60- roles: authenticated users can SELECT; only service role can INSERT/UPDATE/DELETE
61- permissions: authenticated users can SELECT; only service role can INSERT/UPDATE/DELETE
62- role_permissions: only users with 'admin.permissions.write' permission can INSERT/DELETE; authenticated SELECT
63- user_roles: only users with 'admin.users.write' permission can INSERT/DELETE; authenticated SELECT
64- permission_audit_log: INSERT allowed for authenticated; SELECT only for users with 'admin.audit.read'
65
66Seed data: Insert roles 'admin', 'editor', 'viewer'. Insert permissions: 'reports.view', 'reports.export', 'users.view', 'users.write', 'users.delete', 'admin.permissions.write', 'admin.users.write', 'admin.audit.read'. Grant all permissions to admin role.

1Build a permission matrix admin page at src/pages/PermissionMatrix.tsx.
2
3Data fetching:
4- Fetch all roles from the roles table
5- Fetch all permissions from the permissions table, ordered by resource then name
6- Fetch all role_permissions rows (just role_id and permission_id columns)
7
8Matrix layout:
9- Group permissions by their resource field into column groups (e.g. Reports, Users, Admin)
10- Render a sticky header row with grouped column labels
11- Each subsequent row is one role
12- Each cell is a shadcn/ui Checkbox
13- The Checkbox is checked if a role_permissions row exists for that (role_id, permission_id) pair
14- Checking the Checkbox inserts a new role_permissions row via supabase.from('role_permissions').insert()
15- Unchecking deletes the row via supabase.from('role_permissions').delete().match({role_id, permission_id})
16- System roles (is_system=true) show Checkboxes as disabled — cannot be modified
17
18UI details:
19- Show a loading Skeleton while data fetches
20- Show a toast notification on successful save: 'Permission updated'
21- Show a toast on error: 'Could not update permission. Check your access.'
22- Add a legend at the top explaining the dot notation format
23- Permission names display as Badges with the resource part in a muted color and the action part bold
24
25Access gate: if the current user does not have 'admin.permissions.write' permission (check by calling supabase.rpc('has_permission', {p_user_id: user.id, p_permission: 'admin.permissions.write'})), show an Alert with message 'You do not have permission to manage roles.'

1Create a Supabase Edge Function at supabase/functions/list-users/index.ts that:
21. Verifies the calling user has 'admin.users.write' permission by calling has_permission() via the service role client
32. If not authorized, return 403
43. Fetches all users from auth.users using the admin API: supabaseAdmin.auth.admin.listUsers()
54. For each user, fetches their current roles from user_roles joined to roles
65. Returns an array of { id, email, created_at, roles: [{id, name}][] }
7
8Then build src/pages/UserManagement.tsx:
9- DataTable with columns: Email, Roles (rendered as Badges), Joined (relative date), Actions
10- 'Assign Role' Button in the Actions column opens a Dialog
11- Dialog shows all available roles as a Checkbox group
12- Currently assigned roles are pre-checked
13- Saving the Dialog diff: insert new role assignments, delete removed ones
14- Each assignment/removal writes to permission_audit_log
15- Show a search Input above the table filtering by email
16- Access gate: only users with 'admin.users.write' can see this page

1// Example RLS policies using has_permission()
2// Run these in Supabase Dashboard → SQL Editor
3
4-- Reports table: only users with reports.view can read
5CREATE POLICY "reports_select_permission"
6ON reports
7FOR SELECT
8TO authenticated
9USING (has_permission(auth.uid(), 'reports.view'));
10
11-- Reports table: only users with reports.write can insert
12CREATE POLICY "reports_insert_permission"
13ON reports
14FOR INSERT
15TO authenticated
16WITH CHECK (has_permission(auth.uid(), 'reports.write'));
17
18-- Sensitive data table: viewer and above can read
19CREATE POLICY "data_select_permission"
20ON sensitive_data
21FOR SELECT
22TO authenticated
23USING (has_permission(auth.uid(), 'data.view'));
24
25-- Delete restricted to users with explicit delete permission
26CREATE POLICY "data_delete_permission"
27ON sensitive_data
28FOR DELETE
29TO authenticated
30USING (has_permission(auth.uid(), 'data.delete'));
31
32-- Tip: has_permission() is STABLE so Postgres can cache results within a transaction.
33-- For row-owner bypass: USING (owner_id = auth.uid() OR has_permission(auth.uid(), 'admin.all'))

1// src/hooks/usePermission.ts
2import { useState, useEffect } from 'react'
3import { supabase } from '@/integrations/supabase/client'
4import { useAuth } from '@/contexts/AuthContext'
5
6const permissionCache = new Map<string, boolean>()
7
8export function usePermission(permission: string): {
9  hasPermission: boolean
10  loading: boolean
11} {
12  const { user } = useAuth()
13  const cacheKey = `${user?.id}:${permission}`
14
15  const [hasPermission, setHasPermission] = useState(() => {
16    return permissionCache.get(cacheKey) ?? false
17  })
18  const [loading, setLoading] = useState(!permissionCache.has(cacheKey))
19
20  useEffect(() => {
21    if (!user?.id) {
22      setHasPermission(false)
23      setLoading(false)
24      return
25    }
26
27    if (permissionCache.has(cacheKey)) {
28      setHasPermission(permissionCache.get(cacheKey)!)
29      setLoading(false)
30      return
31    }
32
33    supabase
34      .rpc('has_permission', { p_user_id: user.id, p_permission: permission })
35      .then(({ data }) => {
36        const result = data as boolean
37        permissionCache.set(cacheKey, result)
38        setHasPermission(result)
39        setLoading(false)
40      })
41  }, [user?.id, permission, cacheKey])
42
43  return { hasPermission, loading }
44}
45
46// Usage in components:
47// const { hasPermission, loading } = usePermission('reports.export')
48// {hasPermission && <Button>Export</Button>}