Skip to content

Organizational Organogram Builder - Data Architecture Analysis

Executive Summary

This document provides a comprehensive analysis of the current Flowcode application's data architecture to identify opportunities and gaps for implementing an organizational organogram (organizational chart) builder system. The analysis reveals a solid foundation for basic organizational structures but identifies several key areas requiring enhancement to support complex hierarchical relationships and organogram functionality.

Current Data Architecture

Existing Models and Relationships

1. User Model (app/Models/User.php)

Current Implementation:

  • Primary entity representing individual users in the system
  • Contains basic user information: first_name, last_name, email, image, etc.
  • Well-established relationships with organizational structures

Existing Relationships:

  • organisations() - Many-to-many with Organizations
  • divisions() - Many-to-many with Divisions
  • departments() - Many-to-many with Departments
  • organisationRoles() - Many-to-many with OrganisationRoles
  • roles() - Many-to-many with system roles (different from org roles)

2. Organization Model (app/Models/Organisation.php)

Current Implementation:

  • Central organizational entity
  • Template-based organization creation system
  • Financial year and basic organizational metadata

Existing Relationships:

  • users() - Many-to-many with Users
  • divisions() - One-to-many with Divisions
  • departments() - One-to-many with Departments
  • roles() - One-to-many with OrganisationRoles

3. Division Model (app/Models/Division.php)

Current Implementation:

  • Basic division structure with name, description
  • Belongs to an organization
  • Many-to-many relationship with users

Database Schema:

CREATE TABLE divisions (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    organisation_id BIGINT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

4. Department Model (app/Models/Department.php)

Current Implementation:

  • Similar to Division model
  • Has an owner_id field (added via migration)
  • Many-to-many relationship with users

Database Schema:

CREATE TABLE departments (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    organisation_id BIGINT,
    owner_id BIGINT NULLABLE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

5. OrganisationRole Model (app/Models/OrganisationRole.php)

Current Implementation:

  • Organization-specific roles (different from system roles)
  • Global roles: CEO and OPAL (defined in app/Enums/OrganisationRoles.php)
  • Template-based role creation
  • Has slug field for identification

Database Schema:

CREATE TABLE organisation_roles (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    slug VARCHAR(255),
    organisation_id BIGINT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

Current System Strengths

1. Solid Foundation Models

  • User, Organization, Division, Department, and OrganisationRole models are well-established
  • Proper many-to-many relationships between users and organizational structures
  • Template-based organization creation system for consistency

2. Role Management System

  • Distinction between system roles and organizational roles
  • Global organizational roles (CEO, OPAL) with enum-based management
  • Flexible role assignment through pivot tables

3. Multi-tenancy Support

  • Tenant-aware structures
  • Organization-specific customization capabilities
  • Permission and licensing systems

4. Existing Organizational Features

  • OPTT (Objectives, Projects, Tasks, Targets) system with organizational visibility
  • Department and division-based filtering and access control
  • Ownership and creator tracking

Identified Gaps for Organogram Builder

1. Missing: Committee Model and Infrastructure

Current State: No committee structure exists Required Implementation:

// New Committee Model needed
class Committee extends Model {
    protected $fillable = ['name', 'description', 'organisation_id', 'is_default'];

    public function organisation() {
        return $this->belongsTo(Organisation::class);
    }

    public function users() {
        return $this->belongsToMany(User::class, 'committee_user')->withTimestamps();
    }
}

Database Schema Needed:

CREATE TABLE committees (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    organisation_id BIGINT,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE TABLE committee_user (
    id BIGINT PRIMARY KEY,
    committee_id BIGINT,
    user_id BIGINT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

Impact:

  • Replace OPAL role with default OPAL committee
  • Allow organizations to create custom committees
  • Enable committee-based access control and visibility

2. Missing: Function Model

Current State: No function structure exists Required Implementation:

// New Function Model needed
class OrganisationFunction extends Model {
    protected $fillable = ['name', 'description', 'organisation_id', 'color', 'abbreviation'];

    public function organisation() {
        return $this->belongsTo(Organisation::class);
    }

    public function users() {
        return $this->belongsToMany(User::class, 'function_user')->withTimestamps();
    }
}

Database Schema Needed:

CREATE TABLE organisation_functions (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    organisation_id BIGINT,
    color VARCHAR(7), -- hex color
    abbreviation VARCHAR(10),
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE TABLE function_user (
    id BIGINT PRIMARY KEY,
    organisation_function_id BIGINT,
    user_id BIGINT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

3. Missing: Hierarchical User Relationships

Current State: No direct user-to-user reporting relationships Critical Gap: No system for defining who reports to whom

Required Implementation:

// Addition to User Model
class User extends Model {
    public function directReports() {
        return $this->hasMany(User::class, 'reports_to_user_id');
    }

    public function manager() {
        return $this->belongsTo(User::class, 'reports_to_user_id');
    }

    // Recursive relationship helpers
    public function allSubordinates() {
        return $this->directReports()->with('allSubordinates');
    }
}

Database Schema Needed:

ALTER TABLE users ADD COLUMN reports_to_user_id BIGINT NULLABLE;
ALTER TABLE users ADD FOREIGN KEY (reports_to_user_id) REFERENCES users(id);

4. Missing: Organogram Structure Storage

Current State: No system for storing organogram layouts Required Implementation:

// New Organogram Model
class Organogram extends Model {
    protected $fillable = ['name', 'organisation_id', 'structure_data', 'is_active', 'created_by'];
    protected $casts = ['structure_data' => 'array'];

    public function organisation() {
        return $this->belongsTo(Organisation::class);
    }

    public function creator() {
        return $this->belongsTo(User::class, 'created_by');
    }
}

Database Schema Needed:

CREATE TABLE organograms (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255),
    organisation_id BIGINT,
    structure_data JSON, -- Store complex organogram layouts
    is_active BOOLEAN DEFAULT TRUE,
    created_by BIGINT,
    created_at TIMESTAMP,
    updated_at TIMESTAMP
);

JSON Structure Example:

{
    "nodes": [
        {
            "id": "user_1",
            "user_id": 1,
            "position": { "x": 100, "y": 50 },
            "department_ids": [1, 2],
            "division_ids": [1],
            "committee_ids": [1],
            "function_ids": [1]
        }
    ],
    "connections": [
        {
            "from": "user_1",
            "to": "user_2",
            "type": "reports_to"
        }
    ],
    "metadata": {
        "version": "1.0",
        "last_updated": "2024-01-01T00:00:00Z"
    }
}

5. Enhanced Attributes for Existing Models

Division Model Enhancements

Current Gaps:

  • No color coding for visual representation
  • No abbreviation for compact display
  • No hierarchy between divisions

Recommended Additions:

ALTER TABLE divisions ADD COLUMN color VARCHAR(7);
ALTER TABLE divisions ADD COLUMN abbreviation VARCHAR(10);
ALTER TABLE divisions ADD COLUMN parent_division_id BIGINT NULLABLE;
ALTER TABLE divisions ADD FOREIGN KEY (parent_division_id) REFERENCES divisions(id);

Department Model Enhancements

Current Gaps:

  • Limited visual customization options
  • No parent-child relationships

Recommended Additions:

ALTER TABLE departments ADD COLUMN color VARCHAR(7);
ALTER TABLE departments ADD COLUMN abbreviation VARCHAR(10);
ALTER TABLE departments ADD COLUMN parent_department_id BIGINT NULLABLE;
ALTER TABLE departments ADD FOREIGN KEY (parent_department_id) REFERENCES departments(id);

6. User Profile Enhancements for Organogram

Current Gaps:

  • No job title field
  • No start date tracking
  • Limited contact information

Recommended Additions:

ALTER TABLE users ADD COLUMN job_title VARCHAR(255);
ALTER TABLE users ADD COLUMN start_date DATE;
ALTER TABLE users ADD COLUMN office_location VARCHAR(255);
ALTER TABLE users ADD COLUMN employee_id VARCHAR(50);

Integration Points with Existing Systems

1. OPTT System Integration

The existing OPTT (Objectives, Projects, Tasks, Targets) system already has:

  • Visibility controls based on organizational structures
  • Department and division filtering
  • OPAL role integration

Organogram Integration Opportunities:

  • Visual representation of OPTT ownership
  • Committee-based OPTT visibility
  • Function-based OPTT categorization

2. Catalogue System Integration

The current catalogue assignment system uses:

  • Division, department, and role-based allocation
  • User-specific catalogue access

Organogram Enhancement:

  • Committee and function-based catalogue allocation
  • Visual representation of catalogue access hierarchy

3. Permission System Integration

Current organization permissions system can be enhanced with:

  • Committee-based permissions
  • Function-based access controls
  • Hierarchical permission inheritance

Technical Recommendations

1. Database Migration Strategy

Phase 1: Core Structure Implementation

  1. Create Committee model and tables
  2. Create Function model and tables
  3. Add user reporting relationships
  4. Create Organogram storage structure

Phase 2: Enhanced Attributes

  1. Add visual attributes to existing models
  2. Add hierarchical relationships to divisions/departments
  3. Add user profile enhancements

Phase 3: Integration Updates

  1. Update OPTT system for committee/function integration
  2. Enhance catalogue system with new structures
  3. Update permission system

2. API Design Considerations

Required API Endpoints:

// Organogram Management
GET /api/organisations/{org}/organograms
POST /api/organisations/{org}/organograms
PUT /api/organisations/{org}/organograms/{id}
DELETE /api/organisations/{org}/organograms/{id}

// Committee Management
GET /api/organisations/{org}/committees
POST /api/organisations/{org}/committees
PUT /api/organisations/{org}/committees/{id}

// Function Management
GET /api/organisations/{org}/functions
POST /api/organisations/{org}/functions

// Hierarchy Management
PUT /api/users/{id}/reporting-relationship
GET /api/users/{id}/hierarchy

3. Frontend Component Architecture

Required Components:

  • OrganogramBuilder.vue - Main drag-drop organogram builder
  • UserNode.vue - Individual user representation
  • CommitteePanel.vue - Committee management interface
  • FunctionPanel.vue - Function management interface
  • HierarchyControls.vue - Reporting relationship controls

4. Data Validation Considerations

Critical Validations:

  • Prevent circular reporting relationships
  • Ensure committee membership doesn't conflict with roles
  • Validate organogram JSON structure integrity
  • Check for orphaned nodes in organogram structures

Migration Impact Assessment

1. Low Impact Changes

  • Adding new models (Committee, Function, Organogram)
  • Adding optional fields to existing models
  • New API endpoints

2. Medium Impact Changes

  • User reporting relationship implementation
  • OPAL role to committee migration
  • Enhanced catalogue allocation logic

3. High Impact Changes

  • Major UI components for organogram builder
  • Complex JSON structure validation
  • Performance optimization for large organograms

Phase 1: Foundation (4-6 weeks)

  1. Implement Committee and Function models
  2. Add user reporting relationships
  3. Create basic organogram storage
  4. Migrate OPAL role to OPAL committee

Phase 2: Core Builder (6-8 weeks)

  1. Implement organogram builder UI
  2. Add visual attributes to existing models
  3. Create drag-drop functionality
  4. Implement basic hierarchy visualization

Phase 3: Advanced Features (4-6 weeks)

  1. Add hierarchical relationships to divisions/departments
  2. Implement advanced organogram layouts
  3. Add export/import functionality
  4. Performance optimization

Phase 4: Integration (2-4 weeks)

  1. Update OPTT system integration
  2. Enhance catalogue allocation system
  3. Update permission system
  4. Add reporting and analytics

Conclusion

The current Flowcode application provides a solid foundation for implementing an organizational organogram builder. The existing User, Organization, Division, Department, and OrganisationRole models create a strong base, but several key components are missing:

Critical Missing Components:

  1. Committee Model - Essential for replacing OPAL role and enabling custom committees
  2. Function Model - Required for function-based organizational grouping
  3. User Hierarchy System - Fundamental for reporting relationships
  4. Organogram Storage - Necessary for persisting complex organogram layouts

Key Strengths to Leverage:

  • Well-established user and organizational models
  • Robust permission and role systems
  • Template-based organization creation
  • Multi-tenancy support

The recommended phased approach allows for incremental implementation while maintaining system stability and minimizing disruption to existing functionality. The estimated total development time is 16-24 weeks, depending on team size and complexity requirements.