Hierarchical Data & Org Charts

Build organizational charts, team structures, and hierarchical relationships with Taruvi's Graph API. Create parent-child relationships, traverse hierarchies, and visualize org structures with a few API calls.

Overview

The Hierarchy API enables you to:

Define Hierarchical Tables: Add hierarchy support to any DataTable
Create Relationships: Build org charts, reporting structures, and category trees
Query Hierarchies: Get direct reports, management chains, or full team structures
Multiple Formats: Return data as flat lists, nested trees, or graph structures
Depth Control: Limit traversal depth to prevent infinite loops
Multiple Relationship Types: Support matrix organizations with multiple reporting lines

Quick Start

1. Create a Hierarchy Table

Define a table schema with hierarchy: true:

{
  "fields": [
    {"name": "id", "type": "integer"},
    {"name": "employee_id", "type": "string"},
    {"name": "name", "type": "string"},
    {"name": "email", "type": "string", "format": "email"},
    {"name": "title", "type": "string"},
    {"name": "department", "type": "string"}
  ],
  "primaryKey": ["id"],
  "hierarchy": true,
  "graph": {
    "enabled": true,
    "types": [
      {
        "name": "manager",
        "inverse": "reports",
        "description": "Primary reporting line"
      }
    ]
  }
}

2. Add Employees

Create employee records via the Data API:

POST /api/apps/my-app/datatables/employees/data/
Content-Type: application/json

{
  "id": 1,
  "employee_id": "E001",
  "name": "Alice Chen",
  "email": "alice@company.com",
  "title": "CEO",
  "department": "Executive"
}

3. Create Relationships

Add manager relationships via the edges table:

POST /api/apps/my-app/datatables/employees/data/
Content-Type: application/json

# This creates an edge: Bob → Alice (Bob reports to Alice)

Or use the Graph API directly (see Graph Traversal Guide).

4. Query the Hierarchy

Get Alice's direct reports:

GET /api/apps/my-app/datatables/employees/data/1?include=descendants&depth=1

Response:

{
  "data": {
    "id": 1,
    "name": "Alice Chen",
    "title": "CEO"
  },
  "reports": [
    {
      "id": 2,
      "name": "Bob Smith",
      "title": "VP Engineering",
      "_depth": 1,
      "_relationship_type": "manager"
    },
    {
      "id": 3,
      "name": "Carol White",
      "title": "VP Sales",
      "_depth": 1,
      "_relationship_type": "manager"
    }
  ]
}

Working with Organizational Charts

Complete Org Chart Example

Let's build a tech company org chart:

Organization Structure:

Alice Chen (CEO)
├── Bob Smith (VP Engineering)
│   └── David Lee (Senior Engineer)
└── Carol White (VP Sales)
    ├── Frank Wilson (Sales Engineer)
    └── Henry Brown (Sales Manager)

Table Schema:

{
  "fields": [
    {"name": "id", "type": "integer"},
    {"name": "employee_id", "type": "string"},
    {"name": "name", "type": "string"},
    {"name": "email", "type": "string", "format": "email"},
    {"name": "title", "type": "string"},
    {"name": "department", "type": "string"},
    {"name": "level", "type": "integer"},
    {"name": "status", "type": "string"}
  ],
  "primaryKey": ["id"],
  "hierarchy": true,
  "graph": {
    "enabled": true,
    "types": [
      {
        "name": "manager",
        "inverse": "reports",
        "constraints": {
          "max_outgoing": 1
        },
        "description": "Primary reporting line"
      },
      {
        "name": "dotted_line",
        "inverse": "dotted_reports",
        "description": "Matrix reporting (cross-functional)"
      }
    ]
  }
}

Sample Data:

[
  {
    "id": 1,
    "employee_id": "E001",
    "name": "Alice Chen",
    "email": "alice@company.com",
    "title": "CEO",
    "department": "Executive",
    "level": 1,
    "status": "active"
  },
  {
    "id": 2,
    "employee_id": "E002",
    "name": "Bob Smith",
    "email": "bob@company.com",
    "title": "VP Engineering",
    "department": "Engineering",
    "level": 2,
    "status": "active"
  },
  {
    "id": 3,
    "employee_id": "E003",
    "name": "Carol White",
    "email": "carol@company.com",
    "title": "VP Sales",
    "department": "Sales",
    "level": 2,
    "status": "active"
  },
  {
    "id": 4,
    "employee_id": "E004",
    "name": "David Lee",
    "email": "david@company.com",
    "title": "Senior Engineer",
    "department": "Engineering",
    "level": 3,
    "status": "active"
  }
]

Relationships (Edges):

-- Edge semantics: from_id → to_id means "from's manager is to"
-- Bob (2) reports to Alice (1)
INSERT INTO employees_edges (from_id, to_id, type, metadata)
VALUES (2, 1, 'manager', '{"primary": true}'::jsonb);

-- Carol (3) reports to Alice (1)
INSERT INTO employees_edges (from_id, to_id, type, metadata)
VALUES (3, 1, 'manager', '{"primary": true}'::jsonb);

-- David (4) reports to Bob (2)
INSERT INTO employees_edges (from_id, to_id, type, metadata)
VALUES (4, 2, 'manager', '{"primary": true}'::jsonb);

Query Examples

Get Direct Reports

Get only Alice's immediate direct reports (depth=1):

GET /api/apps/my-app/datatables/employees/data/1?include=descendants&depth=1

Response:

{
  "data": {
    "id": 1,
    "name": "Alice Chen",
    "title": "CEO"
  },
  "reports": [
    {
      "id": 2,
      "name": "Bob Smith",
      "title": "VP Engineering",
      "_depth": 1,
      "_relationship_type": "manager"
    },
    {
      "id": 3,
      "name": "Carol White",
      "title": "VP Sales",
      "_depth": 1,
      "_relationship_type": "manager"
    }
  ]
}

Key Points:

✅ Returns only Bob and Carol (depth 1)
✅ Does NOT include David (depth 2)
✅ Uses inverse label "reports" (defined in schema)
✅ Includes _depth and _relationship_type metadata

Get Full Team Hierarchy

Get Alice's entire organization (all levels):

GET /api/apps/my-app/datatables/employees/data/1?include=descendants

Response:

{
  "data": {
    "id": 1,
    "name": "Alice Chen",
    "title": "CEO"
  },
  "reports": [
    {
      "id": 2,
      "name": "Bob Smith",
      "title": "VP Engineering",
      "_depth": 1,
      "_relationship_type": "manager"
    },
    {
      "id": 3,
      "name": "Carol White",
      "title": "VP Sales",
      "_depth": 1,
      "_relationship_type": "manager"
    },
    {
      "id": 4,
      "name": "David Lee",
      "title": "Senior Engineer",
      "_depth": 2,
      "_relationship_type": "manager"
    }
  ]
}

Key Points:

✅ Returns all descendants (no depth limit)
✅ Proper depth values (Bob/Carol: 1, David: 2)
✅ Flat array format (not nested)

Get Management Chain

Get David's management chain (who he reports to):

GET /api/apps/my-app/datatables/employees/data/4?include=ancestors

Response:

{
  "data": {
    "id": 4,
    "name": "David Lee",
    "title": "Senior Engineer"
  },
  "manager": [
    {
      "id": 2,
      "name": "Bob Smith",
      "title": "VP Engineering",
      "_depth": 1,
      "_relationship_type": "manager"
    },
    {
      "id": 1,
      "name": "Alice Chen",
      "title": "CEO",
      "_depth": 2,
      "_relationship_type": "manager"
    }
  ]
}

Key Points:

✅ Returns Bob (immediate manager) and Alice (CEO)
✅ Ordered by depth (closest first)
✅ Uses relationship label "manager" (not inverse)

Get Team Tree Structure

Get nested tree showing reporting structure:

GET /api/apps/my-app/datatables/employees/data/1?format=tree&depth=3

Response:

{
  "data": [
    {
      "id": 1,
      "name": "Alice Chen",
      "title": "CEO",
      "_depth": 0,
      "children": [
        {
          "id": 2,
          "name": "Bob Smith",
          "title": "VP Engineering",
          "_depth": 1,
          "_relationship_type": "manager",
          "children": [
            {
              "id": 4,
              "name": "David Lee",
              "title": "Senior Engineer",
              "_depth": 2,
              "_relationship_type": "manager",
              "children": []
            }
          ]
        },
        {
          "id": 3,
          "name": "Carol White",
          "title": "VP Sales",
          "_depth": 1,
          "_relationship_type": "manager",
          "children": []
        }
      ]
    }
  ],
  "total": 4
}

Key Points:

✅ Nested structure (children within children)
✅ Alice is root with _depth: 0
✅ Respects depth limit (stops at depth 3)
✅ Each node has children array

Get Full Org Graph

Get complete org chart as graph (nodes + edges):

GET /api/apps/my-app/datatables/employees/data/?format=graph

Response:

{
  "data": {
    "nodes": [
      {
        "id": 1,
        "name": "Alice Chen",
        "title": "CEO",
        "department": "Executive"
      },
      {
        "id": 2,
        "name": "Bob Smith",
        "title": "VP Engineering",
        "department": "Engineering"
      },
      {
        "id": 3,
        "name": "Carol White",
        "title": "VP Sales",
        "department": "Sales"
      },
      {
        "id": 4,
        "name": "David Lee",
        "title": "Senior Engineer",
        "department": "Engineering"
      }
    ],
    "edges": [
      {
        "id": 1,
        "from": 2,
        "to": 1,
        "type": "manager",
        "metadata": {"primary": true}
      },
      {
        "id": 2,
        "from": 3,
        "to": 1,
        "type": "manager",
        "metadata": {"primary": true}
      },
      {
        "id": 3,
        "from": 4,
        "to": 2,
        "type": "manager",
        "metadata": {"primary": true}
      }
    ]
  },
  "total": 4
}

Key Points:

✅ All employees returned as nodes
✅ All reporting relationships as edges
✅ Edge direction: from reports to to
✅ Includes edge metadata

Response Formats

The Hierarchy API supports three response formats:

Include Format (Default)

Returns base record with relationship arrays:

GET /data/1?include=descendants

Structure:

{
  "data": {...},           # Base record
  "reports": [...]         # Descendants (inverse label)
}

GET /data/4?include=ancestors

Structure:

{
  "data": {...},           # Base record
  "manager": [...]         # Ancestors (relationship label)
}

Tree Format

Returns nested tree structure:

GET /data/1?format=tree

Structure:

{
  "data": [{
    "id": 1,
    "_depth": 0,
    "children": [
      {
        "id": 2,
        "_depth": 1,
        "children": [...]
      }
    ]
  }],
  "total": 10
}

Graph Format

Returns nodes and edges separately:

GET /data/?format=graph

Structure:

{
  "data": {
    "nodes": [{...}, {...}],
    "edges": [
      {"from": 2, "to": 1, "type": "manager"}
    ]
  },
  "total": 10
}

Relationship Types

Define multiple relationship types for matrix organizations:

Manager Relationships

Primary reporting line:

{
  "graph": {
    "types": [
      {
        "name": "manager",
        "inverse": "reports",
        "constraints": {
          "max_outgoing": 1
        },
        "description": "Primary reporting line"
      }
    ]
  }
}

Matrix Organization

Multiple reporting lines:

{
  "graph": {
    "types": [
      {
        "name": "manager",
        "inverse": "reports",
        "description": "Primary reporting line"
      },
      {
        "name": "dotted_line",
        "inverse": "dotted_reports",
        "description": "Matrix reporting (cross-functional)"
      }
    ]
  }
}

Query specific type:

GET /data/4?include=both&graph_types=dotted_line

Mentorship

Non-hierarchical relationships:

{
  "graph": {
    "types": [
      {
        "name": "mentor",
        "inverse": "mentees",
        "description": "Mentorship relationship"
      }
    ]
  }
}

Query Parameters

include

Expand hierarchy in specified direction:

descendants: Get children, grandchildren, etc. (downward)
ancestors: Get parents, grandparents, etc. (upward)
both: Get both directions

Examples:

GET /data/1?include=descendants      # Get team
GET /data/4?include=ancestors        # Get management chain
GET /data/2?include=both             # Get manager and reports

depth

Limit traversal depth:

Default: 10 (configurable via DATA_SERVICE_GRAPH_MAX_DEPTH)
Minimum: 0 (no traversal, base record only)
Maximum: Enforced by server setting

Examples:

GET /data/1?include=descendants&depth=1   # Direct reports only
GET /data/1?include=descendants&depth=2   # 2 levels deep
GET /data/1?include=descendants&depth=0   # No traversal

format

Response structure:

null (default): Include format (flat arrays)
tree: Nested children structure
graph: Nodes + edges arrays

Examples:

GET /data/1?format=tree              # Nested tree
GET /data/?format=graph              # Full graph

graph_types

Filter by relationship type:

Examples:

GET /data/?format=graph&graph_types=manager
GET /data/?format=graph&graph_types=manager,dotted_line

Understanding Metadata

Every related record includes metadata fields:

_depth

Distance from base node:

{
  "id": 4,
  "name": "David Lee",
  "_depth": 2          # 2 hops from base node
}

Root node: _depth: 0 (in tree format)
Direct children/parents: _depth: 1
Grandchildren/grandparents: _depth: 2

_relationship_type

Type of relationship to parent:

{
  "id": 2,
  "name": "Bob Smith",
  "_relationship_type": "manager"
}

Matches the relationship type from schema
Useful when querying multiple types

Best Practices

When to Use Hierarchies

✅ Use hierarchies for:

Organizational charts
Reporting structures
Category trees
Permission inheritance
Team structures
Department hierarchies

❌ Don't use for:

Simple one-to-many relationships (use foreign keys)
Many-to-many relationships without direction
Temporary associations

Performance Considerations

Depth Limits:

# In settings.py
DATA_SERVICE_GRAPH_MAX_DEPTH = 10  # Prevent deep recursion

Indexing: The edges table is automatically indexed on:

(from_id, to_id, type) for fast lookups
type for filtering by relationship type

Query Optimization:

# ✅ Good: Limit depth
GET /data/1?include=descendants&depth=3

# ❌ Avoid: Unlimited depth on large hierarchies
GET /data/1?include=descendants

Schema Design Patterns

Single Parent (Tree):

{
  "graph": {
    "types": [{
      "name": "parent",
      "inverse": "children",
      "constraints": {"max_outgoing": 1}
    }]
  }
}

Multiple Parents (DAG):

{
  "graph": {
    "types": [{
      "name": "parent",
      "inverse": "children"
      # No max_outgoing constraint
    }]
  }
}

Common Use Cases

1. Company Org Chart

Goal: Display reporting structure for HR dashboard

Query:

GET /api/apps/hr/datatables/employees/data/1?format=tree&depth=5

Use case: CEO dashboard showing 5 levels of reports

2. Permission Inheritance

Goal: Find all users who inherit permissions from manager

Query:

GET /api/apps/hr/datatables/employees/data/10?include=descendants

Use case: Security check - who has access through this manager?

3. Department View

Goal: Show all employees in Engineering department tree

Query:

GET /api/apps/hr/datatables/employees/data/?department=Engineering&format=tree

Use case: Department-specific org chart

4. Reporting Chain

Goal: Show employee's full management chain to CEO

Query:

GET /api/apps/hr/datatables/employees/data/42?include=ancestors

Use case: "Report to" section on employee profile

Troubleshooting

Empty Results

Problem: Requesting descendants but getting empty array

Check:

Verify edges exist in {table}_edges table
Check edge direction (from_id → to_id means "from reports to to")
Confirm relationship type matches schema definition

Debug:

-- Check edges for employee 1
SELECT * FROM employees_edges WHERE from_id = 1 OR to_id = 1;

Circular References

Problem: Infinite loop or max depth error

Solution:

The API automatically prevents circular traversal
MAX_DEPTH setting limits recursion
Check for circular edges in database

Missing Metadata

Problem: _depth or _relationship_type missing

Check:

Only included in hierarchy queries (with include or format=tree/graph)
Not included in standard list queries

Next Steps

Graph Traversal Guide - Advanced graph operations
Hierarchy API Reference - Complete API specification
Data Service Querying - Standard query operations

Overview​

Quick Start​

1. Create a Hierarchy Table​

2. Add Employees​

3. Create Relationships​

4. Query the Hierarchy​

Working with Organizational Charts​

Complete Org Chart Example​

Query Examples​

Get Direct Reports​

Get Full Team Hierarchy​

Get Management Chain​

Get Team Tree Structure​

Get Full Org Graph​

Response Formats​

Include Format (Default)​

Tree Format​

Graph Format​

Relationship Types​

Manager Relationships​

Matrix Organization​

Mentorship​

Query Parameters​

include​

depth​

format​

graph_types​

Understanding Metadata​

_depth​

_relationship_type​

Best Practices​

When to Use Hierarchies​

Performance Considerations​

Schema Design Patterns​

Common Use Cases​

1. Company Org Chart​

2. Permission Inheritance​

3. Department View​

4. Reporting Chain​

Troubleshooting​

Empty Results​

Circular References​

Missing Metadata​

Next Steps​

Related Documentation​

Overview

Quick Start

1. Create a Hierarchy Table

2. Add Employees

3. Create Relationships

4. Query the Hierarchy

Working with Organizational Charts

Complete Org Chart Example

Query Examples

Get Direct Reports

Get Full Team Hierarchy

Get Management Chain

Get Team Tree Structure

Get Full Org Graph

Response Formats

Include Format (Default)

Tree Format

Graph Format

Relationship Types

Manager Relationships

Matrix Organization

Mentorship

Query Parameters

include

depth

format

graph_types

Understanding Metadata

_depth

_relationship_type

Best Practices

When to Use Hierarchies

Performance Considerations

Schema Design Patterns

Common Use Cases

1. Company Org Chart

2. Permission Inheritance

3. Department View

4. Reporting Chain

Troubleshooting

Empty Results

Circular References

Missing Metadata

Next Steps

Related Documentation