Designing a Data Source Discovery App - Part 19: Generating a Role Based Hierarchy

In my last post I described the process for deploying the kDS data discovery PostgreSQL database to the cloud. Today I want to talk about how the app generates a role-based organizational hierarchy. This feature visualizes the complete structure of an organization from parent company down to individual roles, creating an interactive diagram that helps stakeholders understand reporting relationships and organizational design. Additionally, it is the heart and soul of the AI component of the app because role-business_unit-subsidiary data is the key to help the LLM consistently and clearly understand context and produce quality responses.

The Database Structure

The role based organizational hierarchy is built using four key tables, part of the client schema, shown highlighted in the image at the top of this blog:

1. client.parent - The top-level organization or company
2. client.subsidiary - Divisions or units that belong to the parent
3. client.business_unit - Functional areas within subsidiaries
4. client.role - Individual positions within business units

These tables are linked through foreign keys, creating a natural hierarchy that flows from parent → subsidiary → business unit → role.

The SQL View

To efficiently retrieve the complete hierarchy, we created a consolidated SQL view:

CREATE OR REPLACE VIEW client.vw_client_role_consolidated AS
SELECT 
    p.name_ AS parent_name,
    s.name_ AS subsidiary_name,
    bu.name_ AS business_unit_name,
    ro.description_ AS role_description
FROM client.role ro
INNER JOIN client.business_unit bu ON ro.unit_id = bu.unit_id
INNER JOIN client.subsidiary s ON bu.subsidiary_id = s.subsidiary_id
INNER JOIN client.parent p ON s.parent_id = p.parent_id
WHERE 
    p.name_ IS NOT NULL 
    AND s.name_ IS NOT NULL 
    AND bu.name_ IS NOT NULL 
    AND ro.description_ IS NOT NULL
ORDER BY
    s.name_,
    bu.name_,
    ro.description_;

This view joins all four tables and ensures we only get complete paths from parent to role. The WHERE clause filters out any incomplete hierarchy paths, making sure our visualization is accurate.

Generating the Diagram with Python and Mermaid

To visualize the hierarchy, we use Mermaid - a JavaScript-based diagramming and charting tool that renders Markdown-inspired text definitions to create diagrams. Our Python-Flask application generates the Mermaid code via an endpoint that:

1. Queries the consolidated view
2. Creates unique nodes for each entity
3. Establishes the relationships between them
4. Applies styling for visual clarity

Here's the Python function that handles this:

@app.route('/hierarchy')
def hierarchy():
    """
    Generate and return an organization hierarchy diagram using Mermaid syntax.
    """
    try:
        conn = psycopg2.connect(**db_params)
        cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
        
        # Initialize Mermaid diagram
        mermaid_code = """flowchart TD
"""
        
        try:
            # Get all hierarchy data in one query
            cursor.execute("SELECT * FROM client.vw_client_role_consolidated")
            data = cursor.fetchall()
            
            if not data:
                mermaid_code += "    no_data[\"No Complete Organization Hierarchy Data Found\"]\n"
                mermaid_code += "    style no_data fill:#f8d7da,stroke:#dc3545,stroke-width:2px,color:#842029\n"
                return render_template('view_hierarchy.html', mermaid_code=mermaid_code)
            
            # Track nodes and edges we've already created
            parent_nodes = {}
            sub_nodes = {}
            unit_nodes = {}
            role_nodes = {}
            parent_sub_edges = set()
            sub_unit_edges = set()
            unit_role_edges = set()
            
            # Process each row and create nodes and edges
            for row in data:
                parent_name = row['parent_name']
                subsidiary_name = row['subsidiary_name']
                unit_name = row['business_unit_name']
                role_description = row['role_description']
                
                # Create parent node if needed
                if parent_name not in parent_nodes:
                    parent_id = f"parent_{abs(hash(parent_name)) % 10000}"
                    parent_nodes[parent_name] = parent_id
                    mermaid_code += f"    {parent_id}[\"{parent_name} (P)\"]\n"
                    mermaid_code += f"    style {parent_id} fill:#cfe2ff,stroke:#0d6efd,stroke-width:2px,color:#084298\n"
                else:
                    parent_id = parent_nodes[parent_name]
                
                # Create subsidiary node if needed
                if subsidiary_name not in sub_nodes:
                    sub_id = f"sub_{abs(hash(subsidiary_name)) % 10000}"
                    sub_nodes[subsidiary_name] = sub_id
                    mermaid_code += f"    {sub_id}[\"{subsidiary_name} (S)\"]\n"
                    mermaid_code += f"    style {sub_id} fill:#d1e7dd,stroke:#198754,stroke-width:2px,color:#0f5132\n"
                else:
                    sub_id = sub_nodes[subsidiary_name]
                
                # Create parent-subsidiary edge if needed
                parent_sub_edge = f"{parent_id}->{sub_id}"
                if parent_sub_edge not in parent_sub_edges:
                    mermaid_code += f"    {parent_id} --> {sub_id}\n"
                    parent_sub_edges.add(parent_sub_edge)
                
                # Create business unit node if needed
                unit_key = f"{subsidiary_name}_{unit_name}"
                if unit_key not in unit_nodes:
                    unit_id = f"unit_{abs(hash(unit_key)) % 10000}"
                    unit_nodes[unit_key] = unit_id
                    mermaid_code += f"    {unit_id}[\"{unit_name} (BU)\"]\n"
                    mermaid_code += f"    style {unit_id} fill:#fff3cd,stroke:#ffc107,stroke-width:2px,color:#664d03\n"
                else:
                    unit_id = unit_nodes[unit_key]
                
                # Create subsidiary-unit edge if needed
                sub_unit_edge = f"{sub_id}->{unit_id}"
                if sub_unit_edge not in sub_unit_edges:
                    mermaid_code += f"    {sub_id} --> {unit_id}\n"
                    sub_unit_edges.add(sub_unit_edge)
                
                # Create role node
                role_key = f"{unit_key}_{role_description}"
                if role_key not in role_nodes:
                    # Truncate long role descriptions
                    shortened_role = role_description[:30] + "..." if len(role_description) > 30 else role_description
                    role_id = f"role_{abs(hash(role_key)) % 10000}"
                    role_nodes[role_key] = role_id
                    mermaid_code += f"    {role_id}[\"{shortened_role} (R)\"]\n"
                    mermaid_code += f"    style {role_id} fill:#e2d9f3,stroke:#6f42c1,stroke-width:2px,color:#3c096c\n"
                else:
                    role_id = role_nodes[role_key]
                
                # Create unit-role edge if needed
                unit_role_edge = f"{unit_id}->{role_id}"
                if unit_role_edge not in unit_role_edges:
                    mermaid_code += f"    {unit_id} --> {role_id}\n"
                    unit_role_edges.add(unit_role_edge)
            
            # Style all links
            mermaid_code += "    linkStyle default stroke:#6c757d,stroke-width:2px\n"
        
        except Exception as db_error:
            print(f"Database query error: {db_error}")
            return render_template('view_hierarchy.html', 
                                  mermaid_code=f"""flowchart TD
    db_error["Database Error: {db_error}"]
    style db_error fill:#f8d7da,stroke:#dc3545,stroke-width:2px,color:#842029
""")
        
        cursor.close()
        conn.close()
        
        return render_template('view_hierarchy.html', mermaid_code=mermaid_code)
        
    except Exception as e:
        print(f"Error generating hierarchy diagram: {e}")
        error_mermaid = """flowchart TD
    error["Error: Could not generate hierarchy diagram"]
    error_details["Error details: """ + str(e).replace('"', "'") + """"]
    error --> error_details
    style error fill:#f8d7da,stroke:#dc3545,stroke-width:2px,color:#842029
    style error_details fill:#f8d7da,stroke:#dc3545,stroke-width:1px,color:#842029
"""
        return render_template('view_hierarchy.html', mermaid_code=error_mermaid)

The User Experience

The hierarchy diagram is accessed from the parent form by clicking on "Diagram":

The hierarchy displays in a modal window:

Conclusion

The role-based organizational hierarchy visualization provides a powerful way to understand how an organization is structured. By combining SQL for data organization, Python for processing, and Mermaid for visualization, we've created a feature that:

1. Clearly shows reporting relationships from roles up to the parent organization
2. Provides an interactive diagram that can be explored directly in the browser
3. Offers export capabilities for documentation and sharing
4. Automatically stays up-to-date as the organizational structure changes

Most importantly, this hierarchical structure serves as the foundation for the AI component of our application. The role-business_unit-subsidiary data provides critical context that enables our LLM to consistently understand organizational relationships and produce high-quality, relevant responses. By mapping these relationships clearly, we ensure that AI-generated insights are properly contextualized within your specific organizational structure, leading to more accurate and useful recommendations.

Also, don't forget that kDS LLC is actively seeking organizations interested in adopting a beta version of the kDS Data Source Discovery App. If your organization is interested in exploring how our app can streamline your data source discovery process, please let us know. We'd love to collaborate. Tell us in the comments below or email us at info@keeshinds.com.

Until next time, thanks for stopping by.