RBAC System

The MLM CMMS implements a comprehensive Role-Based Access Control (RBAC) system with permissions enforced at both the frontend (UI guards) and backend (Row-Level Security policies) layers.

Overview

The RBAC system provides:

Fine-grained permissions based on resource:action pairs (e.g., tickets:create, users:read)
Role-based assignment where users are assigned to roles, and roles have permissions
Multi-role support where a single user can have multiple roles
Frontend route protection to hide inaccessible UI elements
Backend RLS enforcement to secure data at the database level
Dynamic permission synchronization between frontend and backend

Security Philosophy: Never trust client-side checks alone. All authorization boundaries are enforced in the database through RLS policies.

Architecture

┌─────────────┐       ┌─────────────┐       ┌─────────────────┐
│    User     │ ───→  │    Roles    │ ───→  │  Permissions    │
└─────────────┘       └─────────────┘       └─────────────────┘
      1:N                    N:M                   Resource +
                                                    Action pairs

Example:
User "Alice" → Roles: ["Technician", "Viewer"]
  → Permissions: [
      "work_orders:read",
      "work_orders:update",
      "tickets:create",
      "home:read"
    ]

Database Schema

Core Tables

roles
permissions
role_permissions
user_roles

Purpose: Define available roles in the system

CREATE TABLE public.roles (
  id serial PRIMARY KEY,
  name text NOT NULL UNIQUE,
  description varchar NULL,
  is_system boolean NOT NULL DEFAULT false,
  created_at timestamptz DEFAULT now()
);

Key Fields:

is_system: System roles cannot be deleted (e.g., “Admin”, “Super Admin”)

Source: sql/modules/core_cmms/03_tables.sql:18

Purpose: Registry of all available permissions

CREATE TABLE public.permissions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  resource text NOT NULL,
  action permission_action NOT NULL,
  code text NOT NULL UNIQUE,
  label text NOT NULL,
  description text,
  is_active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

Key Fields:

resource: The entity being accessed (e.g., 'work_orders', 'users', 'inventory')
action: The operation (e.g., 'create', 'read', 'update', 'delete', 'full_access')
code: Unique identifier as resource:action (e.g., 'work_orders:read')
label: Human-readable name for UI

Source: sql/modules/core_cmms/03_tables.sql:45

Purpose: Many-to-many mapping of roles to permissions

CREATE TABLE public.role_permissions (
  role_id int NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  permission_id uuid NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
  PRIMARY KEY (role_id, permission_id)
);

Cascade Behavior: Deleting a role or permission automatically removes the mapping.Source: sql/modules/core_cmms/03_tables.sql:56

Purpose: Many-to-many mapping of users to roles

CREATE TABLE public.user_roles (
  user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role_id int NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  PRIMARY KEY (user_id, role_id)
);

Note: Links to auth.users (Supabase Auth) not public.users.Source: sql/modules/core_cmms/03_tables.sql:62

Permission Model

Permission Actions

Defined in the permission_action enum (sql/modules/core_cmms/02_permission_action.sql):

Action	Description	Common Use
`create`	Create new records	Creating tickets, users, assets
`read`	View all records	Viewing lists and details
`read_own`	View only user’s own records	”My Tickets” view
`update`	Modify existing records	Editing tickets, user profiles
`delete`	Remove records	Deleting tickets, users
`full_access`	Combined create + update + special permissions	Admin-level access
`approve`	Approve/publish records	Publishing inventory documents
`cancel`	Cancel or deactivate	Canceling tickets, disabling users
`disable`	Toggle active status	Enable/disable catalog items
`assign`	Assign resources	Assigning technicians to tickets
`work`	Reserve/use resources	Reserving inventory parts for work orders
`import`	Bulk import data	Importing inventory
`export`	Export data	Exporting reports
`manage_roles`	Manage RBAC roles	Creating/editing roles
`manage_permissions`	Sync permission registry	Updating permission definitions

The frontend PermissionAction type in src/rbac/permissionRegistry.ts:1-16 must match this enum exactly.

Resources

Resources represent entities in the system:

Core

home
work_orders
work_requests
users
assignees

Admin

rbac
special_incidents
announcements
society
locations

Operations

assets
inventory
reports

See src/rbac/permissionRegistry.ts:39-53 for the complete list.

Permission Codes

Permission codes follow the format: {resource}:{action} Examples:

work_orders:read - View work orders
work_orders:create - Create new work orders
work_orders:full_access - Full control over work orders
users:read - View user list
users:full_access - Create and modify users
inventory:approve - Publish inventory documents
rbac:manage_roles - Manage roles and permissions

Frontend Implementation

Permission Registry

The frontend defines all permissions in src/rbac/permissionRegistry.ts:

// src/rbac/permissionRegistry.ts:60
export const PERMISSIONS: PermissionDef[] = [
  // RBAC / Admin
  p(RESOURCES.rbac, 'manage_permissions', 'Sincronizar permisos'),
  p(RESOURCES.rbac, 'manage_roles', 'Gestionar roles'),

  // Home / Inicio
  p(RESOURCES.home, 'read', 'Ver inicio'),

  // Tickets / WorkOrders
  p(RESOURCES.work_orders, 'read', 'Ver OT'),
  p(RESOURCES.work_orders, 'read_own', 'Ver mis OT'),
  p(RESOURCES.work_orders, 'create', 'Crear OT'),
  p(RESOURCES.work_orders, 'full_access', 'Acceso total OT (crear/modificar)'),
  p(RESOURCES.work_orders, 'cancel', 'Cancelar OT'),
  p(RESOURCES.work_orders, 'delete', 'Eliminar OT'),
  // ...
];

Critical: Permission codes in permissionRegistry.ts must match the code column in the permissions table. Mismatches will cause authorization failures.

Permissions Context

The PermissionsContext provides permission state to the entire app:

// src/rbac/PermissionsContext.tsx:14
type PermsState = {
  set: Set<string>;           // Fast lookup set
  list: string[];             // Array of permission codes
  has: (code: string | string[]) => boolean;
  roles: string[];            // User's role names
  ready: boolean;             // Loading state
  refresh: (opts?: { silent?: boolean }) => Promise<void>;
};

Usage in Components:

import { usePermissions } from '../rbac/PermissionsContext';

function MyComponent() {
  const { has, roles } = usePermissions();

  if (!has('work_orders:read')) {
    return <p>Access denied</p>;
  }

  return (
    <div>
      {has('work_orders:create') && (
        <button onClick={createTicket}>Create Ticket</button>
      )}
    </div>
  );
}

Route Protection

Routes are protected using the allowPerms field:

// src/Routes/appRoutes.tsx:252
export const APP_ROUTES: AppRoute[] = [
  {
    path: '/ordenes_trabajo',
    element: <WorkOrdersPage />,
    allowPerms: [
      'work_orders:read',
      'work_orders:full_access',
      'work_orders:cancel',
      'work_orders:delete',
    ],
    name: 'Órdenes de Trabajo',
    showInSidebar: true,
  },
  // ...
];

The ProtectedRoute component checks if the user has at least one of the allowPerms:

function ProtectedRoute({ allowPerms, children }) {
  const { has, ready } = usePermissions();

  if (!ready) return <LoadingSpinner />;
  if (!has(allowPerms)) return <Navigate to="/403" />;

  return children;
}

Permission Caching

Permissions are cached with a 5-minute TTL to reduce database queries:

// src/rbac/PermissionsContext.tsx:79
const CACHE_TTL_MS = 5 * 60_000; // 5 min

Cache Invalidation:

Manual: refresh() method
Automatic: On navigation or data invalidation events
Storage: LocalStorage (mlm:perms, mlm:roles) for hydration

Backend Implementation

Permission Checking Function

The me_has_permission(code) function is the core authorization primitive:

CREATE OR REPLACE FUNCTION public.me_has_permission(perm_code text)
RETURNS boolean
LANGUAGE sql
SECURITY DEFINER
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM public.user_roles ur
    JOIN public.role_permissions rp ON rp.role_id = ur.role_id
    JOIN public.permissions p ON p.id = rp.permission_id
    WHERE ur.user_id = auth.uid()
      AND p.code = perm_code
      AND p.is_active = true
  );
$$;

How it works:

Gets current user ID via auth.uid() (Supabase helper)
Joins through user_roles → role_permissions → permissions
Checks if any of the user’s roles have the specified permission code
Returns true if found and active, false otherwise

Row-Level Security Policies

All tables have RLS enabled and policies that use me_has_permission():

Work Orders (Read)

CREATE POLICY tickets_select_work_orders ON public.tickets
  FOR SELECT
  USING (
    is_accepted = true
    AND (
      me_has_permission('work_orders:read')
      OR me_has_permission('work_orders:full_access')
    )
  );

Work Requests (Read)

CREATE POLICY tickets_select_requests ON public.tickets
  FOR SELECT
  USING (
    is_accepted = false
    AND (
      me_has_permission('work_requests:read')
      OR me_has_permission('work_requests:full_access')
    )
  );

Separate policies for work requests vs work orders based on is_accepted flag.

CREATE POLICY tickets_insert_rbac ON public.tickets
  FOR INSERT
  WITH CHECK (
    me_has_permission('work_orders:create')
    OR me_has_permission('work_orders:full_access')
  );

Note: INSERT policies use WITH CHECK instead of USING.

Work Orders (Update)

CREATE POLICY tickets_update_work_orders ON public.tickets
  FOR UPDATE
  USING (
    is_accepted = true
    AND (
      me_has_permission('work_orders:full_access')
      OR me_has_permission('work_orders:cancel')
    )
  );

Work Requests (Update)

CREATE POLICY tickets_update_requests ON public.tickets
  FOR UPDATE
  USING (
    is_accepted = false
    AND (
      me_has_permission('work_requests:full_access')
      OR me_has_permission('work_requests:cancel')
    )
  );

Work Orders (Delete)

CREATE POLICY tickets_delete_work_orders ON public.tickets
  FOR DELETE
  USING (
    is_accepted = true
    AND me_has_permission('work_orders:delete')
  );

Work Requests (Delete)

CREATE POLICY tickets_delete_requests ON public.tickets
  FOR DELETE
  USING (
    is_accepted = false
    AND me_has_permission('work_requests:delete')
  );

-- View users
CREATE POLICY users_select_rbac ON public.users
  FOR SELECT
  USING (
    me_has_permission('users:read')
    OR me_has_permission('users:full_access')
  );

-- Create users
CREATE POLICY users_insert_rbac ON public.users
  FOR INSERT
  WITH CHECK (
    me_has_permission('users:create')
    OR me_has_permission('users:full_access')
  );

-- Update users
CREATE POLICY users_update_rbac ON public.users
  FOR UPDATE
  USING (
    me_has_permission('users:update')
    OR me_has_permission('users:full_access')
  );

-- Delete users
CREATE POLICY users_delete_rbac ON public.users
  FOR DELETE
  USING (me_has_permission('users:delete'));

-- View assignees
CREATE POLICY assignees_select_rbac ON public.assignees
  FOR SELECT
  USING (
    me_has_permission('assignees:read')
    OR me_has_permission('assignees:full_access')
  );

-- Create assignees
CREATE POLICY assignees_insert_rbac ON public.assignees
  FOR INSERT
  WITH CHECK (me_has_permission('assignees:full_access'));

-- Update assignees
CREATE POLICY assignees_update_rbac ON public.assignees
  FOR UPDATE
  USING (me_has_permission('assignees:full_access'));

-- Delete assignees
CREATE POLICY assignees_delete_rbac ON public.assignees
  FOR DELETE
  USING (me_has_permission('assignees:delete'));

See sql/modules/core_cmms/09_policies.sql for all policy definitions.

RLS Grants

Grants are configured in sql/modules/core_cmms/15_grants_auth.sql:

-- Schema access
GRANT USAGE ON SCHEMA public TO anon, authenticated;
GRANT USAGE ON SCHEMA storage TO anon, authenticated;

-- Function execution
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO anon, authenticated;

-- Specific revocations for security
REVOKE ALL ON FUNCTION public.me_has_permission(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.me_has_permission(text) TO authenticated;

The me_has_permission() function uses SECURITY DEFINER which executes with the permissions of the function owner (usually the database owner). This allows it to bypass RLS when querying the permission tables.

Permission Synchronization

The frontend permission registry should be synced to the database periodically:

Manual Sync (Admin UI)

Navigate to /admin/permisos (Role Management page)
Click “Sincronizar Permisos” button
Calls rbac:manage_permissions permission check
Executes an RPC or service to upsert permissions from PERMISSIONS array

Seed Script

The sql/modules/core_cmms/10_seed_admin_permissions.sql script inserts the initial permission registry. This should be updated whenever new permissions are added.

Best Practice: Keep permissionRegistry.ts and 10_seed_admin_permissions.sql in sync. Use version control to track changes.

Common Permission Patterns

Full Access Pattern

Many resources have a full_access permission that combines multiple actions:

// Frontend route allows any of these permissions
allowPerms: [
  'users:read',
  'users:full_access',
]

// Backend policy checks for full_access OR specific permission
USING (
  me_has_permission('users:read')
  OR me_has_permission('users:full_access')
)

Read Own Pattern

Some resources support viewing only the current user’s data:

CREATE POLICY tickets_select_own ON public.tickets
  FOR SELECT
  USING (
    me_has_permission('work_orders:read_own')
    AND created_by = auth.uid()
  );

Hierarchical Permissions

Example: Inventory permissions cascade:

inventory:read - View inventory
inventory:create - Create draft documents
inventory:approve - Publish documents (implies create + read)
inventory:full_access - All inventory operations

Role Management

Creating Roles

Insert into roles table:

INSERT INTO public.roles (name, description, is_system)
VALUES ('Warehouse Manager', 'Manages inventory and parts', false);

Assign permissions:

INSERT INTO public.role_permissions (role_id, permission_id)
SELECT 1, id FROM public.permissions
WHERE code IN (
  'inventory:read',
  'inventory:create',
  'inventory:approve',
  'inventory:full_access'
);

Assign to users:

INSERT INTO public.user_roles (user_id, role_id)
VALUES ('user-uuid-here', 1);

System Roles

Roles with is_system = true cannot be deleted and should not be modified:

Super Admin: All permissions
Admin: Most administrative permissions

These are seeded in sql/modules/core_cmms/11_seed_bootstrap.sql.

Security Best Practices

Never Trust the Client

Always enforce permissions at the database level. Frontend checks are for UX only.

Use Least Privilege

Grant only the minimum permissions needed for a role.

Audit Permission Changes

Track who creates/modifies roles and permissions using created_by/updated_by fields.

Test RLS Policies

Verify policies work by testing with users who have different roles.

Keep Frontend and Backend in Sync

Regularly sync permissionRegistry.ts with the database permissions table.

Troubleshooting

User Can’t Access a Resource

Check user’s roles:

SELECT r.name
FROM public.user_roles ur
JOIN public.roles r ON r.id = ur.role_id
WHERE ur.user_id = 'user-uuid';

Check role’s permissions:

SELECT p.code, p.label
FROM public.role_permissions rp
JOIN public.permissions p ON p.id = rp.permission_id
WHERE rp.role_id = 1;

Test permission function:

-- Run as the affected user
SELECT public.me_has_permission('work_orders:read');

Check RLS is enabled:

SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public' AND tablename = 'tickets';

Check policy exists:

SELECT policyname, cmd, qual
FROM pg_policies
WHERE tablename = 'tickets';

Frontend Shows Route but Backend Denies Access

This indicates a mismatch between allowPerms in appRoutes.tsx and the actual RLS policy. Fix: Update allowPerms to match the policy or vice versa.

Permission Not Found

If me_has_permission('some:code') always returns false:

Check if permission exists:

SELECT * FROM public.permissions WHERE code = 'some:code';

If missing, sync from frontend or insert manually:

INSERT INTO public.permissions (resource, action, code, label, is_active)
VALUES ('some', 'code', 'some:code', 'Some Action', true);

Next Steps

Database Schema

Explore the RBAC table structures in detail

Architecture

Understand the overall system design

User Management

Learn how to create and manage users

Roles & Permissions

Guide to creating and configuring roles

​RBAC System

​Overview

​Architecture

​Database Schema

​Core Tables

​Permission Model

​Permission Actions

​Resources

Core

Admin

Operations

​Permission Codes

​Frontend Implementation

​Permission Registry

​Permissions Context

​Route Protection

​Permission Caching

​Backend Implementation

​Permission Checking Function

​Row-Level Security Policies

​RLS Grants

​Permission Synchronization

​Manual Sync (Admin UI)

​Seed Script

​Common Permission Patterns

​Full Access Pattern

​Read Own Pattern

​Hierarchical Permissions

​Role Management

​Creating Roles

​System Roles

​Security Best Practices

​Troubleshooting

​User Can’t Access a Resource

​Frontend Shows Route but Backend Denies Access

​Permission Not Found

​Next Steps

Database Schema

Architecture

User Management

Roles & Permissions

RBAC System

Overview

Architecture

Database Schema

Core Tables

Permission Model

Permission Actions

Resources

Permission Codes

Frontend Implementation

Permission Registry

Permissions Context

Route Protection

Permission Caching

Backend Implementation

Permission Checking Function

Row-Level Security Policies

RLS Grants

Permission Synchronization

Manual Sync (Admin UI)

Seed Script

Common Permission Patterns

Full Access Pattern

Read Own Pattern

Hierarchical Permissions

Role Management

Creating Roles

System Roles

Security Best Practices

Troubleshooting

User Can’t Access a Resource

Frontend Shows Route but Backend Denies Access

Permission Not Found

Next Steps