Hierarchy API Reference

Complete API specification for hierarchy and graph operations. Reference for all query parameters, response formats, metadata fields, and error codes.

Schema Configuration

Hierarchy Field

Enable hierarchy support by setting hierarchy: true in table schema:

{
  "fields": [
    {"name": "id", "type": "integer"},
    {"name": "name", "type": "string"}
  ],
  "primaryKey": ["id"],
  "hierarchy": true
}

Effect:

• Creates {table_name}_edges table
• Enables hierarchy query parameters
• Supports basic parent-child relationships

Graph Configuration

For advanced features (multiple relationship types, constraints):

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

Fields:

Field	Type	Required	Description
enabled	boolean	No	Enable graph features (default: true if hierarchy: true)
types	array	No	Relationship type definitions
types[].name	string	Yes	Relationship type identifier
types[].inverse	string	Yes	Label for reverse direction
types[].constraints	object	No	Validation rules
types[].constraints.max_outgoing	integer	No	Max edges FROM a node
types[].constraints.max_incoming	integer	No	Max edges TO a node
types[].description	string	No	Human-readable documentation

Edge Table Structure

Automatically created table: {table_name}_edges

Columns:

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Unique edge identifier
from_id	INTEGER	NOT NULL, FOREIGN KEY	Source node ID
to_id	INTEGER	NOT NULL, FOREIGN KEY	Target node ID
type	VARCHAR(50)	NOT NULL	Relationship type
metadata	JSONB	NULL	Additional edge data
created_at	TIMESTAMP	DEFAULT NOW()	Creation timestamp

Indexes:

CREATE INDEX idx_edges_from_type ON {table}_edges(from_id, type);
CREATE INDEX idx_edges_to_type ON {table}_edges(to_id, type);
CREATE INDEX idx_edges_type ON {table}_edges(type);

Foreign Key Behavior:

• ON DELETE CASCADE: When node is deleted, all related edges are deleted
• Both from_id and to_id reference the main table's primary key

Query Parameters

include

Type: string

Values:

• descendants: Expand children/reports (follow incoming edges)
• ancestors: Expand parents/managers (follow outgoing edges)
• both: Expand in both directions

Default: null (no expansion)

Examples:

GET /data/1?include=descendants
GET /data/4?include=ancestors
GET /data/2?include=both

Behavior:

• Triggers graph traversal from specified node
• Returns additional relationship arrays in response
• Can be combined with depth parameter

Validation:

• Must be one of: descendants, ancestors, both
• Invalid value returns 400 error

depth

Type: integer

Range: 0 to DATA_SERVICE_GRAPH_MAX_DEPTH

Default: 10 (or server configured value)

Examples:

GET /data/1?include=descendants&depth=1    # Direct children only
GET /data/1?include=descendants&depth=2    # 2 levels deep
GET /data/1?include=descendants&depth=0    # No traversal (base record only)

Behavior:

• Limits maximum traversal depth
• 0 = no traversal (base record only)
• 1 = immediate relationships only
• N = up to N hops from start node

Validation:

• Must be non-negative integer
• Cannot exceed DATA_SERVICE_GRAPH_MAX_DEPTH
• Exceeding max returns 400 error

Performance:

• Lower depth = faster queries
• Depth 1-3 typically sufficient for most use cases

format

Type: string

Values:

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

Examples:

GET /data/1?format=tree              # Nested children
GET /data/?format=graph              # Nodes + edges
GET /data/1?include=descendants      # Default format

Behavior by Format:

Format	Structure	Use Case
Include (default)	Flat arrays (reports, manager)	Standard queries, simple displays
Tree	Nested children arrays	Org chart UI, hierarchical menus
Graph	{nodes: [], edges: []}	Network visualization, graph analysis

Validation:

• Must be one of: null, tree, graph
• Invalid value returns 400 error

graph_types

Type: string (comma-separated)

Format: "type1,type2,type3"

Examples:

GET /data/?format=graph&graph_types=manager
GET /data/?format=graph&graph_types=manager,dotted_line
GET /data/5?include=ancestors&graph_types=mentor

Behavior:

• Filters edges by relationship type
• Only returns edges matching specified types
• Can specify multiple types (comma-separated)

Validation:

• Types must be defined in schema's graph.types
• Unknown type returns 400 error
• Empty value returns all types

Response Formats

Standard Response (Include Format)

Used when include parameter is specified without format.

Structure:

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

Response Keys:

Key	Type	When Included	Description
data	object	Always	Base record data
{inverse}	array	include=descendants	Descendants array (uses inverse label)
{name}	array	include=ancestors	Ancestors array (uses relationship name)
Both arrays	arrays	include=both	Both directions included

Examples:

Descendants:

{
  "data": {...},
  "reports": [...]  // Inverse label for "manager" type
}

Ancestors:

{
  "data": {...},
  "manager": [...]  // Relationship name
}

Both:

{
  "data": {...},
  "reports": [...],   // Descendants
  "manager": [...]    // Ancestors
}

Tree Response Format

Used when format=tree.

Structure:

{
  "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": []
            }
          ]
        }
      ]
    }
  ],
  "total": 4
}

Response Keys:

Key	Type	Description
data	array	List of root nodes with nested children
total	integer	Total count of unique nodes in tree

Node Structure:

Field	Type	When Included	Description
All table fields	various	Always	Original record data
children	array	Always	Nested child nodes
_depth	integer	Always	Distance from root (0-based)
_relationship_type	string	If not root	Relationship type to parent

Key Points:

• Root nodes have _depth: 0
• Empty children: [] for leaf nodes
• Respects depth parameter
• Returns full tree context (includes ancestors for complete picture)

Graph Response Format

Used when format=graph.

Structure:

{
  "data": {
    "nodes": [
      {
        "id": 1,
        "name": "Alice Chen",
        "title": "CEO",
        "department": "Executive"
      },
      {
        "id": 2,
        "name": "Bob Smith",
        "title": "VP Engineering",
        "department": "Engineering"
      }
    ],
    "edges": [
      {
        "id": 9,
        "from": 2,
        "to": 1,
        "type": "manager",
        "metadata": {"primary": true}
      }
    ]
  },
  "total": 4
}

Response Keys:

Key	Type	Description
data	object	Contains nodes and edges
data.nodes	array	All nodes in result set
data.edges	array	All edges connecting nodes
total	integer	Total count of nodes

Node Structure:

• Contains all table fields
• NO _depth or _relationship_type (use edges for relationships)

Edge Structure:

Field	Type	Description
id	integer	Unique edge identifier
from	integer	Source node ID
to	integer	Target node ID
type	string	Relationship type
metadata	object	Additional edge data

Key Points:

• Nodes are de-duplicated (each node appears once)
• Edges show all relationships between nodes in result
• Can filter edges by type using graph_types parameter

Metadata Fields

_depth

Type: integer

Range: 0 to traversal depth

Included When:

• include parameter is used
• format=tree

Not Included When:

• Standard list queries
• format=graph (use edges instead)

Meaning:

• Distance from base node in traversal
• 0 = root node (only in tree format)
• 1 = immediate child/parent
• N = N hops away

Example:

{
  "id": 4,
  "name": "David Lee",
  "_depth": 2
}

This means David is 2 hops from the base node.

_relationship_type

Type: string

Included When:

• include parameter is used
• format=tree (except for root nodes)

Not Included When:

• Standard list queries
• format=graph (relationship type in edges instead)
• Root nodes in tree format

Meaning:

• Type of relationship to parent node
• Matches graph.types[].name from schema
• Useful when querying multiple relationship types

Example:

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

This means Bob's relationship to his parent is "manager" type.

Error Responses

400 Bad Request

Causes:

• Invalid include value
• Invalid format value
• Invalid depth value
• Depth exceeds DATA_SERVICE_GRAPH_MAX_DEPTH
• Unknown graph_types value
• Validation errors

Examples:

Invalid include:

{
  "error": "Validation failed",
  "detail": "include must be one of: descendants, ancestors, both"
}

Depth exceeded:

{
  "error": "Validation failed",
  "detail": "depth exceeds maximum allowed (10)"
}

Unknown type:

{
  "error": "Validation failed",
  "detail": "graph_types contains unknown type: 'foo'. Valid types: manager, dotted_line"
}

404 Not Found

Causes:

• Record ID not found
• Table not found
• App not found

Example:

{
  "error": "Not found",
  "detail": "Record with id=999 not found in table 'employees'"
}

500 Internal Server Error

Causes:

• Edges table missing (shouldn't happen)
• Database connection errors
• Unexpected errors

Example:

{
  "error": "Internal server error",
  "detail": "An unexpected error occurred while querying data"
}

Performance Characteristics

Query Complexity

Time Complexity:

Operation	Complexity	Description
Direct relationships (depth=1)	O(1)	Single indexed lookup
BFS traversal	O(V + E)	V=vertices, E=edges in subgraph
Depth-limited	O(k × b)	k=depth, b=branching factor
Full graph	O(V + E)	All nodes and edges

Space Complexity:

Format	Complexity	Description
Include	O(V)	Flat list of nodes
Tree	O(V)	Nested structure (same nodes)
Graph	O(V + E)	Nodes plus edges

Index Strategy

Automatically Created Indexes:

-- For descendant queries (incoming edges)
CREATE INDEX idx_{table}_edges_to_type ON {table}_edges(to_id, type);

-- For ancestor queries (outgoing edges)
CREATE INDEX idx_{table}_edges_from_type ON {table}_edges(from_id, type);

-- For type filtering
CREATE INDEX idx_{table}_edges_type ON {table}_edges(type);

Query Performance:

• Indexed lookups: ~1ms per hop
• 3-level traversal: ~3-5ms
• Full org (500 employees): ~20-50ms

Scalability Limits

Recommended Limits:

Organization Size	Max Depth	Performance
Small (<100)	10	Excellent
Medium (100-1,000)	7	Good
Large (1,000-10,000)	5	Acceptable
Very Large (>10,000)	3	Use caching

Settings:

# settings.py
DATA_SERVICE_GRAPH_MAX_DEPTH = 10  # Default

Environment Variable:

DATA_SERVICE_GRAPH_MAX_DEPTH=15  # Override default

Edge Semantics

Edge Direction

Format: from_id → to_id (type: "relationship_name")

Meaning: "from_id's relationship_name is to_id"

Examples:

Edge: 2 → 1 (type: "manager")
Means: Employee 2's manager is Employee 1
Or: Employee 2 reports to Employee 1

Edge: 5 → 3 (type: "mentor")
Means: Person 5's mentor is Person 3
Or: Person 3 mentors Person 5

Traversal Direction

Outgoing Traversal:

• SQL: SELECT to_id WHERE from_id = start_node
• Meaning: Follow edges FORWARD (who this node points to)
• Use case: Finding ancestors/parents/managers

Incoming Traversal:

• SQL: SELECT from_id WHERE to_id = start_node
• Meaning: Follow edges BACKWARD (who points to this node)
• Use case: Finding descendants/children/reports

Quick Reference:

Query	Direction	Finds
?include=descendants	Incoming	Who reports to this node
?include=ancestors	Outgoing	Who this node reports to

Configuration Settings

DATA_SERVICE_GRAPH_MAX_DEPTH

Type: integer

Default: 10

Purpose: Prevent infinite loops and excessive recursion

Location: settings.py

DATA_SERVICE_GRAPH_MAX_DEPTH = env.int("DATA_SERVICE_GRAPH_MAX_DEPTH", default=10)

Environment Variable:

export DATA_SERVICE_GRAPH_MAX_DEPTH=15

Behavior:

• Requests with depth > MAX_DEPTH return 400 error
• Applies to all hierarchy queries
• Can be overridden per deployment

Recommendations:

• Development: 10-15 (for testing deep hierarchies)
• Production (small org): 10
• Production (large org): 5-7

Migration Guide

Adding Hierarchy to Existing Table

Step 1: Update schema to enable hierarchy

{
  "fields": [...],  // Existing fields
  "hierarchy": true,
  "graph": {
    "enabled": true,
    "types": [
      {
        "name": "manager",
        "inverse": "reports"
      }
    ]
  }
}

Step 2: Save schema (edges table created automatically)

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

{
  "json_schema": { ... updated schema ... }
}

Step 3: Populate edges table

-- Migrate from foreign key column
INSERT INTO employees_edges (from_id, to_id, type, metadata)
SELECT id, manager_id, 'manager', '{}'::jsonb
FROM employees
WHERE manager_id IS NOT NULL;

Schema Migration Example

Before (Foreign Key):

{
  "fields": [
    {"name": "id", "type": "integer"},
    {"name": "name", "type": "string"},
    {"name": "manager_id", "type": "integer"}
  ]
}

After (Hierarchy):

{
  "fields": [
    {"name": "id", "type": "integer"},
    {"name": "name", "type": "string"}
    // manager_id field removed
  ],
  "hierarchy": true,
  "graph": {
    "enabled": true,
    "types": [{
      "name": "manager",
      "inverse": "reports"
    }]
  }
}

Data Migration:

-- Step 1: Create edges from manager_id column
INSERT INTO employees_edges (from_id, to_id, type, created_at)
SELECT id, manager_id, 'manager', NOW()
FROM employees
WHERE manager_id IS NOT NULL;

-- Step 2: (Optional) Drop manager_id column
-- ALTER TABLE employees DROP COLUMN manager_id;

Complete Examples

Example 1: Simple Org Chart

Schema:

{
  "fields": [
    {"name": "id", "type": "integer"},
    {"name": "name", "type": "string"},
    {"name": "title", "type": "string"}
  ],
  "hierarchy": true,
  "graph": {
    "types": [{
      "name": "manager",
      "inverse": "reports"
    }]
  }
}

Data:

INSERT INTO employees (id, name, title) VALUES
  (1, 'Alice', 'CEO'),
  (2, 'Bob', 'VP Eng'),
  (3, 'Carol', 'VP Sales');

INSERT INTO employees_edges (from_id, to_id, type) VALUES
  (2, 1, 'manager'),
  (3, 1, 'manager');

Queries:

# Get Alice's reports
GET /data/1?include=descendants

# Get Bob's manager
GET /data/2?include=ancestors

# Get org tree
GET /data/1?format=tree

Example 2: Matrix Organization

Schema:

{
  "fields": [
    {"name": "id", "type": "integer"},
    {"name": "name", "type": "string"}
  ],
  "hierarchy": true,
  "graph": {
    "types": [
      {"name": "manager", "inverse": "reports"},
      {"name": "dotted_line", "inverse": "dotted_reports"}
    ]
  }
}

Data:

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

-- Emma also reports to Carol (dotted line)
INSERT INTO employees_edges (from_id, to_id, type, metadata)
VALUES (5, 3, 'dotted_line', '{"percentage": 30}'::jsonb);

Queries:

# Get all Emma's managers (both types)
GET /data/5?include=ancestors

# Get only primary reporting line
GET /data/5?include=ancestors&graph_types=manager

# Get matrix relationships
GET /data/?format=graph&graph_types=manager,dotted_line

See Also

• Hierarchical Data & Org Charts - User guide with examples
• Graph Traversal & Relationships - Advanced concepts
• Data Service Overview - General API documentation
• Schema Definition - Table schema specification