Departments

Overview

Departments in AmbioSys represent organizational units used for user categorization and operational structuring. Every user must be assigned to a department during registration, and department assignments affect reporting, permissions, and operational workflows.

Department Structure

Database Schema

departments
├── department_id (PK)
├── department_name
├── description
├── created_at
└── updated_at

Key Characteristics

Required Assignment: Every user must belong to exactly one department
Organization-Wide: Departments are shared across the entire organization
Reporting Basis: Many reports and analytics are segmented by department
Permission Context: Some permissions may be department-scoped

Retrieving Departments

Get All Departments

Retrieve the complete list of departments available in the system.

GET /api/departamentos

From /Backend/web-ambiotec/src/routes/auth.js:503-521:

router.get('/departamentos', async (req, res) => {
  const client = await pgPool.connect();
  try {
    // Execute the stored function
    const { rows } = await pgPool.query(
      'SELECT * FROM db_ambiotec.fn_get_departments()'
    );
    
    // Return response with departments
    return res.status(200).json({
      success: true,
      departamentos: rows
    });
  } catch (error) {
    console.error('Error al obtener departamentos', error);
    res.status(500).json({ 
      error: 'Error al obtener los departamentos' 
    });
  } finally {
    client.release();
  }
});

Response:

{
  "success": true,
  "departamentos": [
    {
      "department_id": 1,
      "department_name": "Operaciones",
      "description": "Personal operativo de campo",
      "created_at": "2024-01-15T08:00:00Z"
    },
    {
      "department_id": 2,
      "department_name": "Administración",
      "description": "Personal administrativo y de gestión",
      "created_at": "2024-01-15T08:00:00Z"
    },
    {
      "department_id": 3,
      "department_name": "Mantenimiento",
      "description": "Mantenimiento de vehículos y equipos",
      "created_at": "2024-01-15T08:00:00Z"
    },
    {
      "department_id": 4,
      "department_name": "Logística",
      "description": "Coordinación logística y transporte",
      "created_at": "2024-01-15T08:00:00Z"
    }
  ]
}

The department list is retrieved through a database function (fn_get_departments) which may apply additional business logic such as filtering inactive departments or ordering by operational priority.

Department Usage in User Management

User Registration with Department

When registering a new user, a valid departamentoId must be provided. From /Backend/web-ambiotec/src/routes/auth.js:47-173:

router.post('/register',
  [
    check('usuarioLogin').notEmpty(),
    check('usuarioCorreo').isEmail(),
    check('usuarioPassword').isLength({ min: 6 }),
    check('usuarioNombre').notEmpty(),
    check('usuarioApellido').notEmpty(),
    check('departamentoId').isInt(), // Required department ID
  ],
  async (req, res) => {
    const {
      usuarioLogin,
      usuarioCorreo,
      usuarioPassword,
      usuarioNombre,
      usuarioApellido,
      departamentoId, // Department assignment
      // ... other fields
    } = req.body;

    // Pass department ID to registration function
    const { rows } = await pgPool.query(
      `SELECT * FROM db_ambiotec.fn_register_user(
        $1, $2, $3, $4, $5, $6, $7, ...
      )`,
      [
        usuarioLogin,
        usuarioCorreo,
        usuarioPassword,
        usuarioFechaNacimiento,
        usuarioNombre,
        usuarioApellido,
        departamentoId, // Position 7
        // ...
      ]
    );
  }
);

Registration Request Example:

{
  "usuarioLogin": "jdoe",
  "usuarioCorreo": "[email protected]",
  "usuarioPassword": "securepass123",
  "usuarioNombre": "John",
  "usuarioApellido": "Doe",
  "departamentoId": 1,
  "usuarioCelular": "+502 1234-5678"
}

The departamentoId must correspond to an existing, active department in the system. Providing an invalid department ID will cause the registration to fail with a database constraint violation.

Updating User Department

Users can be reassigned to different departments through the profile update endpoint. From /Backend/web-ambiotec/src/routes/auth.js:315-403:

router.post('/update-profile',
  [
    check('user_id').isInt(),
    check('username').notEmpty(),
    check('email').isEmail(),
    check('first_name').notEmpty(),
    check('last_name').notEmpty(),
    check('mobile_number').notEmpty(),
    check('department_id').isInt(), // Department validation
  ],
  async (req, res) => {
    const {
      user_id,
      username,
      email,
      first_name,
      last_name,
      department_id, // New department assignment
      // ... other fields
    } = req.body;

    const client = await pgPool.connect();
    try {
      await client.query('BEGIN');
      
      const { rows } = await client.query(
        `SELECT * FROM db_ambiotec.fn_update_user_profile(
          $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12
        )`,
        [
          user_id,
          username,
          email,
          first_name,
          last_name,
          date_born || null,
          normalizedLicenseNumber,
          normalizedLicenseTypeId,
          normalizedLicenseExpiration,
          mobile_number || '',
          Number(department_id), // Position 11
          (typeof public_name === 'string' ? public_name.trim() : null) || null
        ]
      );

      await client.query('COMMIT');
      return res.json({ 
        success: true, 
        message: 'Perfil actualizado con éxito', 
        user: rows[0] 
      });
    } catch (err) {
      await client.query('ROLLBACK');
      console.error('❌ Error actualizando perfil', err);
      return res.status(500).json({ 
        success: false, 
        error: err.message || 'Error interno' 
      });
    } finally {
      client.release();
    }
  }
);

Update Request Example:

{
  "user_id": 42,
  "username": "jdoe",
  "email": "[email protected]",
  "first_name": "John",
  "last_name": "Doe",
  "mobile_number": "+502 1234-5678",
  "department_id": 2
}

Viewing User Department

Get User Profile

The user profile includes department information.

POST /api/profile

Request:

{
  "user_id": 42
}

From /Backend/web-ambiotec/src/routes/auth.js:430-470:

router.post('/profile',[
  check('user_id').isInt()], 
  async (req, res) => {
    const userId = req.body.user_id;
    try {
      const { rows } = await pgPool.query(
        'SELECT * FROM db_ambiotec.fn_get_user_profile($1)',
        [userId]
      );

      if (rows.length === 0) {
        return res.status(404).json({ error: 'Usuario no encontrado' });
      }

      const user = rows[0]?.fn_get_user_profile;
      return res.json({
        success: true,
        user: {
          usuario_id: user.user_id,
          username: user.username,
          email: user.email,
          first_name: user.first_name,
          last_name: user.last_name,
          department_id: user.department_id,
          department_name: user.department_name, // Department details
          mobile_number: user.mobile_number,
          // ... other fields
        }
      });
    } catch (err) {
      console.error('Error al obtener perfil', err);
      return res.status(500).json({ 
        error: 'Error interno al obtener perfil' 
      });
    }
  }
);

Response:

{
  "success": true,
  "user": {
    "usuario_id": 42,
    "username": "jdoe",
    "email": "[email protected]",
    "first_name": "John",
    "last_name": "Doe",
    "department_id": 1,
    "department_name": "Operaciones",
    "mobile_number": "+502 1234-5678",
    "date_born": "1990-01-15",
    "license_number": "A-12345",
    "license_type_name": "Tipo A",
    "role_name": "Operator"
  }
}

The profile function returns both department_id and department_name through a SQL JOIN, providing complete department context without requiring an additional API call.

Department-Based Features

Reporting and Analytics

Departments serve as a primary dimension for reporting:

Performance Metrics: Operations completed per department
Resource Allocation: Vehicle and equipment assignments by department
User Activity: Access logs and actions segmented by department
License Management: Driver license tracking by department

Permission Scoping

While the current implementation uses role-based permissions, department context can be leveraged for:

Data Visibility: Users may only see data from their department
Operation Approval: Supervisors approve actions within their department
Resource Access: Equipment checkout restricted by department

Workflow Routing

Department assignments affect operational workflows:

┌─────────────────┐
│   Operation     │
│   Request       │
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│  Route to Dept  │
│   Supervisor    │
└────────┬────────┘
         │
         ▼
┌─────────────────┐
│  Assign to      │
│  Dept Staff     │
└─────────────────┘

Department Management Best Practices

Consistent Naming

Use clear, standardized department names that align with organizational structure (e.g., “Operaciones”, “Administración”, “Mantenimiento”).

Descriptive Details

Include meaningful descriptions that clarify department responsibilities and scope.

Validate on Assignment

Always validate that department IDs exist before assigning users to prevent orphaned references.

Plan for Reorganization

Design department transfer workflows that maintain historical records and audit trails.

Department-Scoped Permissions

Consider implementing department-level permission filtering for enhanced security and data isolation.

Common Department Patterns

Operational Departments

Field-focused departments for direct service delivery:

{
  "department_id": 1,
  "department_name": "Operaciones",
  "description": "Personal operativo de campo",
  "typical_roles": ["operator", "field-supervisor"]
}

Administrative Departments

Back-office and support functions:

{
  "department_id": 2,
  "department_name": "Administración",
  "description": "Personal administrativo y de gestión",
  "typical_roles": ["admin", "data-analyst", "coordinator"]
}

Technical Departments

Specialized technical functions:

{
  "department_id": 3,
  "department_name": "Mantenimiento",
  "description": "Mantenimiento de vehículos y equipos",
  "typical_roles": ["mechanic", "maintenance-supervisor"]
}

Integration Points

User Registration Flow

Fetch Departments

Call GET /api/departamentos to populate department selection dropdown.

User Selection

User selects appropriate department from the list.

Submit Registration

Include departamentoId in registration payload.

Validation

Backend validates department exists and creates user association.

Profile Management Flow

Load Current Profile

Call POST /api/profile to retrieve current user data including department.

Display Department Options

Fetch departments and show current assignment with option to change.

Update Profile

Submit profile update with new department_id if changed.

Confirm Update

Display updated department assignment to user.

Frontend Integration Example

import { useState, useEffect } from 'react';

function DepartmentSelector({ value, onChange }) {
  const [departments, setDepartments] = useState([]);
  const [loading, setLoading] = useState(true);

  useEffect(() => {
    async function fetchDepartments() {
      try {
        const response = await fetch('/api/departamentos');
        const data = await response.json();
        if (data.success) {
          setDepartments(data.departamentos);
        }
      } catch (error) {
        console.error('Error fetching departments:', error);
      } finally {
        setLoading(false);
      }
    }
    fetchDepartments();
  }, []);

  if (loading) return <div>Cargando departamentos...</div>;

  return (
    <select value={value} onChange={(e) => onChange(e.target.value)}>
      <option value="">Seleccione un departamento</option>
      {departments.map((dept) => (
        <option key={dept.department_id} value={dept.department_id}>
          {dept.department_name}
        </option>
      ))}
    </select>
  );
}

User Registration with Department

async function registerUser(userData) {
  try {
    const response = await fetch('/api/register', {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
      },
      body: JSON.stringify({
        usuarioLogin: userData.username,
        usuarioCorreo: userData.email,
        usuarioPassword: userData.password,
        usuarioNombre: userData.firstName,
        usuarioApellido: userData.lastName,
        departamentoId: userData.departmentId, // From department selector
        usuarioCelular: userData.phone,
        // ... other fields
      }),
    });

    const data = await response.json();
    
    if (data.token) {
      // Store token and user data
      localStorage.setItem('authToken', data.token);
      localStorage.setItem('user', JSON.stringify(data.user));
      return { success: true, user: data.user };
    }
    
    return { success: false, error: data.error };
  } catch (error) {
    console.error('Registration error:', error);
    return { success: false, error: 'Error de conexión' };
  }
}

Error Handling

Invalid Department ID

{
  "error": "Datos inválidos",
  "details": [
    {
      "msg": "Invalid value",
      "param": "departamentoId",
      "location": "body"
    }
  ]
}

Department Not Found

Database constraint violation when department ID doesn’t exist:

{
  "error": "Error interno al registrar usuario",
  "code": "23503"
}

Always validate department IDs against the current department list before submission to provide better user feedback than generic database constraint errors.

Future Enhancements

Potential improvements to department management:

Hierarchical Departments: Support parent-child department relationships
Department Admins: Designate department-level administrators
Department-Scoped Data: Implement row-level security based on department
Department Transfers: Formal workflow for transferring users between departments
Department Metrics: Dashboard showing department-level KPIs and performance
Inactive Departments: Soft-delete support for decommissioned departments

Get Started

Deployment

Core Features

User Management

Overview

Department Structure

Database Schema

Key Characteristics

Retrieving Departments

Get All Departments

Department Usage in User Management

User Registration with Department

Updating User Department

Viewing User Department

Get User Profile

Department-Based Features

Reporting and Analytics

Permission Scoping

Workflow Routing

Department Management Best Practices

Common Department Patterns

Operational Departments

Administrative Departments

Technical Departments

Integration Points

User Registration Flow

Profile Management Flow

Frontend Integration Example

User Registration with Department

Error Handling

Invalid Department ID

Department Not Found

Future Enhancements

Build docs developers (and LLMs) love

Get Started

Deployment

Core Features

User Management

​Overview

​Department Structure

​Database Schema

​Key Characteristics

​Retrieving Departments

​Get All Departments

​Department Usage in User Management

​User Registration with Department

​Updating User Department

​Viewing User Department

​Get User Profile

​Department-Based Features

​Reporting and Analytics

​Permission Scoping

​Workflow Routing

​Department Management Best Practices

​Common Department Patterns

​Operational Departments

​Administrative Departments

​Technical Departments

​Integration Points

​User Registration Flow

​Profile Management Flow

​Frontend Integration Example

​Fetching Departments for Dropdown

​User Registration with Department

​Error Handling

​Invalid Department ID

​Department Not Found

​Future Enhancements

Build docs developers (and LLMs) love

Overview

Department Structure

Database Schema

Key Characteristics

Retrieving Departments

Get All Departments

Department Usage in User Management

User Registration with Department

Updating User Department

Viewing User Department

Get User Profile

Department-Based Features

Reporting and Analytics

Permission Scoping

Workflow Routing

Department Management Best Practices

Common Department Patterns

Operational Departments

Administrative Departments

Technical Departments

Integration Points

User Registration Flow

Profile Management Flow

Frontend Integration Example

Fetching Departments for Dropdown

User Registration with Department

Error Handling

Invalid Department ID

Department Not Found

Future Enhancements