Roles & Permissions

Overview

AmbioSys implements a comprehensive Role-Based Access Control (RBAC) system that manages user access to system modules and actions. The permission model supports:

Role creation and management
User-to-role assignment
Module-level permissions with view and block controls
Bulk permission updates
Dynamic permission versioning for cache invalidation

RBAC Architecture

Components

Roles

Roles are named groups that define a set of permissions. Each role has a unique key, display name, and description.

Modules

Modules represent distinct areas or features of the application. Each module has a path and can have multiple actions.

Permissions

Permissions link roles to modules, defining what each role can access. Permissions include can_view and is_blocked flags.

User Assignments

Users can be assigned to multiple roles through the user_roles junction table.

Database Schema

roles
├── role_id (PK)
├── role_key (unique)
├── role_name
├── description
└── created_by

modules
├── module_id (PK)
├── module_name
├── module_path (unique)
├── module_description
└── is_blocked

role_module_permissions
├── id (PK)
├── role_id (FK)
├── module_id (FK)
├── can_view
└── is_blocked

user_roles
├── user_id (FK)
└── role_id (FK)

Role Management

List All Roles

Retrieve all roles in the system ordered by name.

GET /api/roles

From /Backend/web-ambiotec/src/routes/roles.js:6-17:

router.get('/', async (req, res) => {
  const client = await pgPool.connect();
  try {
    const { rows } = await client.query(
      'SELECT * FROM db_ambiotec.roles ORDER BY role_name'
    );
    return res.json({ success: true, roles: rows });
  } catch (error) {
    console.error('Error listing roles', error);
    return res.status(500).json({ 
      success: false, 
      error: 'Error listing roles' 
    });
  } finally {
    client.release();
  }
});

Response:

{
  "success": true,
  "roles": [
    {
      "role_id": 1,
      "role_key": "admin",
      "role_name": "Administrator",
      "description": "Full system access",
      "created_by": 1,
      "created_at": "2024-01-15T10:30:00Z"
    },
    {
      "role_id": 2,
      "role_key": "operator",
      "role_name": "Operator",
      "description": "Field operations access",
      "created_by": 1,
      "created_at": "2024-01-15T10:35:00Z"
    }
  ]
}

Create a Role

Create a new role with a unique key and name.

POST /api/roles

Request Body:

{
  "role_key": "supervisor",
  "role_name": "Supervisor",
  "description": "Supervises field operations and reviews reports"
}

From /Backend/web-ambiotec/src/routes/roles.js:19-35:

router.post('/', async (req, res) => {
  const client = await pgPool.connect();
  try {
    const { role_key, role_name, description } = req.body || {};
    
    if (!role_key || !role_name) {
      return res.status(400).json({ 
        success: false, 
        error: 'role_key and role_name required' 
      });
    }
    
    const createdBy = req.user?.usuario_id ?? req.user?.user_id ?? null;
    
    const { rows } = await client.query(
      `INSERT INTO db_ambiotec.roles 
       (role_key, role_name, description, created_by) 
       VALUES ($1,$2,$3,$4) RETURNING *`,
      [role_key, role_name, description, createdBy]
    );
    
    return res.status(201).json({ success: true, role: rows[0] });
  } catch (error) {
    console.error('Error creating role', error);
    return res.status(500).json({ 
      success: false, 
      error: 'Error creating role' 
    });
  } finally { 
    client.release(); 
  }
});

The role_key must be unique across the system. Use lowercase, hyphenated format for consistency (e.g., field-operator, data-analyst).

Response:

{
  "success": true,
  "role": {
    "role_id": 3,
    "role_key": "supervisor",
    "role_name": "Supervisor",
    "description": "Supervises field operations and reviews reports",
    "created_by": 1,
    "created_at": "2024-03-03T14:20:00Z"
  }
}

User-Role Assignment

Assign User to Role

Assign a specific user to a role.

POST /api/roles/:roleId/users/:userId

From /Backend/web-ambiotec/src/routes/roles.js:37-53:

router.post('/:roleId/users/:userId', async (req, res) => {
  const client = await pgPool.connect();
  const roleId = Number(req.params.roleId);
  const userId = Number(req.params.userId);
  
  if (!Number.isInteger(roleId) || !Number.isInteger(userId)) {
    return res.status(400).json({ 
      success: false, 
      error: 'Invalid ids' 
    });
  }
  
  try {
    const { rows } = await client.query(
      `INSERT INTO db_ambiotec.user_roles (user_id, role_id) 
       VALUES ($1,$2) ON CONFLICT DO NOTHING RETURNING *`,
      [userId, roleId]
    );
    return res.json({ success: true, assigned: rows[0] ?? null });
  } catch (error) {
    console.error('Error assigning role', error);
    return res.status(500).json({ 
      success: false, 
      error: 'Error assigning role' 
    });
  } finally { 
    client.release(); 
  }
});

Example:

curl -X POST https://api.ambiosys.com/api/roles/2/users/42

Response:

{
  "success": true,
  "assigned": {
    "user_id": 42,
    "role_id": 2
  }
}

The ON CONFLICT DO NOTHING clause ensures idempotent behavior. Assigning the same user to a role multiple times won’t create duplicates or throw errors.

Remove User from Role

Remove a user’s role assignment.

DELETE /api/roles/:roleId/users/:userId

From /Backend/web-ambiotec/src/routes/roles.js:55-68:

router.delete('/:roleId/users/:userId', async (req, res) => {
  const client = await pgPool.connect();
  const roleId = Number(req.params.roleId);
  const userId = Number(req.params.userId);
  
  if (!Number.isInteger(roleId) || !Number.isInteger(userId)) {
    return res.status(400).json({ 
      success: false, 
      error: 'Invalid ids' 
    });
  }
  
  try {
    const { rowCount } = await client.query(
      'DELETE FROM db_ambiotec.user_roles WHERE role_id=$1 AND user_id=$2', 
      [roleId, userId]
    );
    return res.json({ success: true, removed: rowCount > 0 });
  } catch (error) {
    console.error('Error removing role assignment', error);
    return res.status(500).json({ 
      success: false, 
      error: 'Error removing role assignment' 
    });
  } finally { 
    client.release(); 
  }
});

List Users for a Role

Get all users assigned to a specific role.

GET /api/roles/:roleId/users

From /Backend/web-ambiotec/src/routes/roles.js:70-88:

router.get('/:roleId/users', async (req, res) => {
  const client = await pgPool.connect();
  const roleId = Number(req.params.roleId);
  
  if (!Number.isInteger(roleId)) {
    return res.status(400).json({ 
      success: false, 
      error: 'Invalid role id' 
    });
  }
  
  try {
    const { rows } = await client.query(
      `SELECT u.user_id, u.email, u.first_name, u.last_name
       FROM db_ambiotec.user_roles ur
       JOIN db_ambiotec.users u ON u.user_id = ur.user_id
       WHERE ur.role_id = $1`,
      [roleId]
    );
    return res.json({ success: true, users: rows });
  } catch (error) {
    console.error('Error listing users for role', error);
    return res.status(500).json({ 
      success: false, 
      error: 'Error listing users for role' 
    });
  } finally { 
    client.release(); 
  }
});

Response:

{
  "success": true,
  "users": [
    {
      "user_id": 42,
      "email": "[email protected]",
      "first_name": "John",
      "last_name": "Doe"
    },
    {
      "user_id": 58,
      "email": "[email protected]",
      "first_name": "Alice",
      "first_name": "Smith"
    }
  ]
}

Module Permissions

Get Role Permissions

Retrieve all module permissions for a specific role.

GET /api/roles/:roleId/permissions

From /Backend/web-ambiotec/src/routes/roles.js:90-124:

router.get('/:roleId/permissions', async (req, res) => {
  const client = await pgPool.connect();
  const roleId = Number(req.params.roleId);
  
  if (!Number.isInteger(roleId)) {
    return res.status(400).json({ 
      success: false, 
      error: 'Invalid role id' 
    });
  }
  
  try {
    const { rows } = await client.query(
      `
      SELECT
        m.module_id,
        m.module_name,
        m.module_path,
        m.module_description,
        rmp.can_view,
        rmp.is_blocked,
        rmp.id AS role_module_permission_id
      FROM db_ambiotec.modules m
      LEFT JOIN db_ambiotec.role_module_permissions rmp
        ON rmp.module_id = m.module_id AND rmp.role_id = $1
      ORDER BY m.module_name
      `,
      [roleId]
    );
    
    const permissions = rows.map((row) => ({
      ...row,
      has_permission: row.role_module_permission_id != null,
    }));
    
    return res.json({ success: true, permissions });
  } catch (error) {
    console.error('Error listing role permissions', error);
    return res.status(500).json({ 
      success: false, 
      error: 'Error listing role permissions' 
    });
  } finally {
    client.release();
  }
});

Response:

{
  "success": true,
  "permissions": [
    {
      "module_id": 1,
      "module_name": "Dashboard",
      "module_path": "/dashboard",
      "module_description": "Main dashboard view",
      "can_view": true,
      "is_blocked": false,
      "role_module_permission_id": 15,
      "has_permission": true
    },
    {
      "module_id": 2,
      "module_name": "Reports",
      "module_path": "/reports",
      "module_description": "Report generation and viewing",
      "can_view": null,
      "is_blocked": null,
      "role_module_permission_id": null,
      "has_permission": false
    }
  ]
}

The LEFT JOIN ensures all modules are returned, even those without permissions for the role. The has_permission flag indicates whether the role has any permission record for that module.

Update Role Permissions (Bulk)

Update multiple module permissions for a role in a single transaction.

PUT /api/roles/:roleId/permissions

Request Body:

{
  "permissions": [
    {
      "module_id": 1,
      "enabled": true,
      "can_view": true,
      "is_blocked": false
    },
    {
      "module_id": 2,
      "enabled": true,
      "can_view": true,
      "is_blocked": false
    },
    {
      "module_id": 3,
      "enabled": false
    }
  ]
}

From /Backend/web-ambiotec/src/routes/roles.js:126-167:

router.put('/:roleId/permissions', async (req, res) => {
  const client = await pgPool.connect();
  const roleId = Number(req.params.roleId);
  const { permissions } = req.body || {};
  
  if (!Number.isInteger(roleId)) {
    return res.status(400).json({ 
      success: false, 
      error: 'Invalid role id' 
    });
  }
  if (!Array.isArray(permissions)) {
    return res.status(400).json({ 
      success: false, 
      error: 'permissions array required' 
    });
  }

  try {
    await client.query('BEGIN');
    
    for (const perm of permissions) {
      const moduleId = Number(perm?.module_id);
      if (!Number.isInteger(moduleId)) continue;
      
      const enabled = perm?.enabled !== false;
      
      if (!enabled) {
        // Remove permission if disabled
        await client.query(
          `DELETE FROM db_ambiotec.role_module_permissions 
           WHERE role_id = $1 AND module_id = $2`,
          [roleId, moduleId]
        );
        continue;
      }
      
      // Update or insert permission
      const canView = Boolean(perm?.can_view);
      const isBlocked = Boolean(perm?.is_blocked);
      
      // Delete existing then insert (upsert pattern)
      await client.query(
        `DELETE FROM db_ambiotec.role_module_permissions 
         WHERE role_id = $1 AND module_id = $2`,
        [roleId, moduleId]
      );
      await client.query(
        `INSERT INTO db_ambiotec.role_module_permissions 
         (role_id, module_id, can_view, is_blocked) 
         VALUES ($1, $2, $3, $4)`,
        [roleId, moduleId, canView, isBlocked]
      );
    }
    
    await client.query('COMMIT');
    return res.json({ success: true });
  } catch (error) {
    await client.query('ROLLBACK');
    console.error('Error updating role permissions', error);
    return res.status(500).json({ 
      success: false, 
      error: 'Error updating role permissions' 
    });
  } finally {
    client.release();
  }
});

This endpoint uses a transactional approach. If any permission update fails, all changes are rolled back. Ensure all module_id values are valid before submitting.

Delete a Module Permission

Remove a specific module permission from a role.

DELETE /api/roles/:roleId/permissions/:moduleId

From /Backend/web-ambiotec/src/routes/roles.js:169-189:

router.delete('/:roleId/permissions/:moduleId', async (req, res) => {
  const client = await pgPool.connect();
  const roleId = Number(req.params.roleId);
  const moduleId = Number(req.params.moduleId);
  
  if (!Number.isInteger(roleId) || !Number.isInteger(moduleId)) {
    return res.status(400).json({ 
      success: false, 
      error: 'Invalid ids' 
    });
  }
  
  try {
    const { rowCount } = await client.query(
      `DELETE FROM db_ambiotec.role_module_permissions 
       WHERE role_id = $1 AND module_id = $2`,
      [roleId, moduleId]
    );
    return res.json({ success: true, removed: rowCount > 0 });
  } catch (error) {
    console.error('Error deleting role permission', error);
    return res.status(500).json({ 
      success: false, 
      error: 'Error deleting role permission' 
    });
  } finally {
    client.release();
  }
});

User Permissions API

Get User Permissions Version

Retrieve the current permission version for cache invalidation.

GET /api/permissions/getPermissionsVersion/:id

From /Backend/web-ambiotec/src/routes/permissions.js:6-25:

router.get('/getPermissionsVersion/:id', async (req, res) => {
  const client = await pgPool.connect();
  const { id } = req.params;
  try {
    const { rows } = await client.query(
      'SELECT * FROM db_ambiotec.fn_get_user_permissions_version($1)', 
      [id]
    );
    return res.status(200).json({
      success: true,
      version: rows[0] 
        ? rows[0]?.fn_get_user_permissions_version 
        : null
    });
  } catch (error) {
    console.error('Error al obtener la versión de permisos', error);
    res.status(500).json({ 
      error: 'Error al obtener la versión de permisos' 
    });
  } finally {
    client.release();
  }
});

Response:

{
  "success": true,
  "version": "v1.2024-03-03T14:30:00Z"
}

The permissions version allows client applications to cache permissions locally and only refresh when the version changes, improving performance.

Get User Permissions

Retrieve complete permission set for a user including modules and actions.

GET /api/permissions/getPermission/:id

From /Backend/web-ambiotec/src/routes/permissions.js:27-44:

router.get('/getPermission/:id', async (req, res) => {
  const client = await pgPool.connect();
  const { id } = req.params;
  try {
    const { rows } = await client.query(
      'SELECT * FROM db_ambiotec.fn_get_user_permissions_modules_actions($1)', 
      [id]
    );
    return res.status(200).json({
      success: true,
      permissions: rows[0] 
        ? rows[0]?.fn_get_user_permissions_modules_actions 
        : null
    });
  } catch (error) {
    console.error('Error al obtener permisos', error);
    res.status(500).json({ 
      error: 'Error al obtener los permisos' 
    });
  } finally {
    client.release();
  }
});

Response:

{
  "success": true,
  "permissions": {
    "modules": [
      {
        "module_id": 1,
        "module_name": "Dashboard",
        "can_view": true,
        "is_blocked": false,
        "actions": [
          {
            "action_id": 1,
            "action_name": "view_reports",
            "can_execute": true
          }
        ]
      }
    ]
  }
}

Permission Flags

can_view

Type: Boolean
Purpose: Determines if the user can view the module
Default: false
Usage: Controls visibility in navigation and direct access to module routes

is_blocked

Type: Boolean
Purpose: Explicitly blocks access to a module regardless of other permissions
Default: false
Usage: Override permission for security or compliance reasons

When is_blocked is true, the module is completely inaccessible to the role, even if can_view is true. Block flags take precedence over view permissions.

Best Practices

Principle of Least Privilege

Grant users only the minimum permissions required to perform their job functions. Start with no permissions and add as needed.

Role Hierarchy

Design roles in a hierarchical structure (e.g., Viewer → Operator → Supervisor → Admin) and assign permissions accordingly.

Use Role Keys Consistently

Establish a naming convention for role keys (e.g., field-operator, data-analyst) and document role purposes.

Audit Permission Changes

Track who creates roles and when. The created_by field stores the user who created each role for accountability.

Bulk Updates for Consistency

Use the bulk permission update endpoint when configuring roles to ensure atomic, transactional updates.

Cache Permission Versions

Implement permission caching on the client side and use the version endpoint to determine when to refresh.

Common Permission Patterns

Read-Only Role

{
  "role_key": "viewer",
  "role_name": "Viewer",
  "permissions": [
    {
      "module_id": 1,
      "enabled": true,
      "can_view": true,
      "is_blocked": false
    }
  ]
}

Operator Role with Restrictions

{
  "role_key": "operator",
  "role_name": "Operator",
  "permissions": [
    {
      "module_id": 1,
      "enabled": true,
      "can_view": true,
      "is_blocked": false
    },
    {
      "module_id": 5,
      "enabled": true,
      "can_view": false,
      "is_blocked": true
    }
  ]
}

Administrator Role

{
  "role_key": "admin",
  "role_name": "Administrator",
  "permissions": [
    {
      "module_id": 1,
      "enabled": true,
      "can_view": true,
      "is_blocked": false
    },
    // ... all modules with can_view: true
  ]
}

Get Started

Deployment

Core Features

User Management

Roles & Permissions

Overview

RBAC Architecture

Components

Database Schema

Role Management

List All Roles

Create a Role

User-Role Assignment

Assign User to Role

Remove User from Role

List Users for a Role

Module Permissions

Get Role Permissions

Update Role Permissions (Bulk)

Delete a Module Permission

User Permissions API

Get User Permissions Version

Get User Permissions

Permission Flags

can_view

is_blocked

Best Practices

Common Permission Patterns

Read-Only Role

Operator Role with Restrictions

Administrator Role

Build docs developers (and LLMs) love

Get Started

Deployment

Core Features

User Management

​Overview

​RBAC Architecture

​Components

​Database Schema

​Role Management

​List All Roles

​Create a Role

​User-Role Assignment

​Assign User to Role

​Remove User from Role

​List Users for a Role

​Module Permissions

​Get Role Permissions

​Update Role Permissions (Bulk)

​Delete a Module Permission

​User Permissions API

​Get User Permissions Version

​Get User Permissions

​Permission Flags

​can_view

​is_blocked

​Best Practices

​Common Permission Patterns

​Read-Only Role

​Operator Role with Restrictions

​Administrator Role

Build docs developers (and LLMs) love

Overview

RBAC Architecture

Components

Database Schema

Role Management

List All Roles

Create a Role

User-Role Assignment

Assign User to Role

Remove User from Role

List Users for a Role

Module Permissions

Get Role Permissions

Update Role Permissions (Bulk)

Delete a Module Permission

User Permissions API

Get User Permissions Version

Get User Permissions

Permission Flags

can_view

is_blocked

Best Practices

Common Permission Patterns

Read-Only Role

Operator Role with Restrictions

Administrator Role