Hierarchical Role Management System Architecture

Based on your hierarchical client structure and role requirements, I'll outline an approach to effectively manage user roles and access control.

Database Schema Design

Here's a proposed schema design that addresses your hierarchical client structure:

SQLCopied
-- Client table with hierarchical structure
CREATE TABLE Client (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    parent_id INT NULL,
    FOREIGN KEY (parent_id) REFERENCES Client(id)
);

-- Project table
CREATE TABLE Project (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    client_id INT NOT NULL,
    FOREIGN KEY (client_id) REFERENCES Client(id)
);

-- Role types (Admin, Viewer, Editor, etc.)
CREATE TABLE RoleType (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- User-Client-Role relationship
CREATE TABLE UserClientRole (
    id INT PRIMARY KEY,
    user_id INT NOT NULL,
    client_id INT NOT NULL,
    role_type_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES User(id),
    FOREIGN KEY (client_id) REFERENCES Client(id),
    FOREIGN KEY (role_type_id) REFERENCES RoleType(id),
    UNIQUE (user_id, client_id, role_type_id)
);

Role Management Approach

1. Handling Hierarchical Access

To implement the hierarchical access pattern where users at higher levels can access lower-level clients and projects:

PYTHONCopied
def get_accessible_clients(user_id):
    # Get direct client assignments for the user
    direct_client_roles = UserClientRole.objects.filter(user_id=user_id)
    
    accessible_clients = []
    
    for client_role in direct_client_roles:
        client_id = client_role.client_id
        # Add the directly assigned client
        accessible_clients.append(client_id)
        
        # Add all descendant clients (recursive function)
        descendant_clients = get_all_descendant_clients(client_id)
        accessible_clients.extend(descendant_clients)
    
    return list(set(accessible_clients))  # Remove duplicates

def get_all_descendant_clients(client_id):
    """Recursively get all descendant clients"""
    descendants = []
    direct_children = Client.objects.filter(parent_id=client_id)
    
    for child in direct_children:
        descendants.append(child.id)
        descendants.extend(get_all_descendant_clients(child.id))
    
    return descendants

2. Handling Multiple Client Assignments with Different Roles

For users assigned to multiple clients with different roles, you can implement a role resolution strategy:

PYTHONCopied
def get_effective_role(user_id, client_id):
    # Check direct role assignment
    direct_roles = UserClientRole.objects.filter(
        user_id=user_id, 
        client_id=client_id
    ).values_list('role_type_id', flat=True)
    
    if direct_roles:
        # Return highest privilege role if multiple exist
        return get_highest_privilege_role(direct_roles)
    
    # If no direct role, check parent clients (inheritance)
    client = Client.objects.get(id=client_id)
    if client.parent_id:
        return get_effective_role(user_id, client.parent_id)
    
    # No role found in hierarchy
    return None

3. Project Access Control

To determine if a user can access a specific project:

PYTHONCopied
def can_access_project(user_id, project_id):
    project = Project.objects.get(id=project_id)
    client_id = project.client_id
    
    # Get all clients the user has access to
    accessible_clients = get_accessible_clients(user_id)
    
    # Check if the project's client is in the accessible clients list
    if client_id in accessible_clients:
        # Get the effective role for this client
        role = get_effective_role(user_id, client_id)
        # Check if the role allows access to projects
        return role_allows_project_access(role)
    
    return False

API Implementation

For your Role API and Client API, you can implement endpoints like:

PYTHONCopied
# Role API
@api_view(['GET'])
def get_user_roles(request):
    user_id = request.user.id
    
    # Get direct role assignments
    direct_roles = UserClientRole.objects.filter(user_id=user_id)
    
    # Transform to appropriate response format
    roles_data = []
    for role in direct_roles:
        roles_data.append({
            'client_id': role.client_id,
            'client_name': role.client.name,
            'role_type': role.role_type.name
        })
    
    return Response(roles_data)

# Client API
@api_view(['GET'])
def get_accessible_clients_and_projects(request):
    user_id = request.user.id
    
    # Get all clients the user can access
    client_ids = get_accessible_clients(user_id)
    
    # Get all accessible clients with their projects
    result = []
    for client_id in client_ids:
        client = Client.objects.get(id=client_id)
        projects = Project.objects.filter(client_id=client_id)
        
        # Get effective role for this client
        role = get_effective_role(user_id, client_id)
        
        result.append({
            'client_id': client.id,
            'client_name': client.name,
            'role': role.name if role else None,
            'projects': [
                {'id': p.id, 'name': p.name} 
                for p in projects
            ]
        })
    
    return Response(result)

Performance Considerations

For better performance with large hierarchies:

1. Materialized Path: Store the full path of each client in the hierarchy
2. Caching: Cache the hierarchy and user permissions
3. Batch Processing: Precompute accessible clients and projects

This approach provides a flexible and scalable solution for managing roles in your hierarchical client structure while maintaining the ability to assign different roles to users across multiple clients.