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": "string", "format": "uuid"},
    {"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`	UUID	PRIMARY KEY, DEFAULT gen_random_uuid()	Unique edge identifier
`from_id`	UUID	NOT NULL, FOREIGN KEY	Source node ID (matches main table PK type)
`to_id`	UUID	NOT NULL, FOREIGN KEY	Target node ID (matches main table PK type)
`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:

REST API
Python
JavaScript

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

# Descendants
result = auth_client.database.from_("employees").filter("id", "eq", 1).include("descendants").execute()
# Ancestors
result = auth_client.database.from_("employees").filter("id", "eq", 4).include("ancestors").execute()
# Both
result = auth_client.database.from_("employees").filter("id", "eq", 2).include("both").execute()

// Descendants
const result = await database.from("employees").get(1, { include: "descendants" })
// Ancestors
const result = await database.from("employees").get(4, { include: "ancestors" })
// Both
const result = await database.from("employees").get(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:

REST API
Python
JavaScript

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)

# Direct children only
result = auth_client.database.from_("employees").filter("id", "eq", 1).include("descendants").depth(1).execute()
# 2 levels deep
result = auth_client.database.from_("employees").filter("id", "eq", 1).include("descendants").depth(2).execute()
# No traversal
result = auth_client.database.from_("employees").filter("id", "eq", 1).include("descendants").depth(0).execute()

// Direct children only
const result = await database.from("employees").get(1, { include: "descendants", depth: 1 })
// 2 levels deep
const result = await database.from("employees").get(1, { include: "descendants", depth: 2 })
// No traversal
const result = await database.from("employees").get(1, { include: "descendants", depth: 0 })

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:

REST API
Python
JavaScript

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

# Tree format
result = auth_client.database.from_("employees").filter("id", "eq", 1).format("tree").execute()
# Graph format
result = auth_client.database.from_("employees").format("graph").execute()
# Default format
result = auth_client.database.from_("employees").filter("id", "eq", 1).include("descendants").execute()

// Tree format
const result = await database.from("employees").get(1, { format: "tree" })
// Graph format
const result = await database.from("employees").execute({ format: "graph" })
// Default format
const result = await database.from("employees").get(1, { include: "descendants" })

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

relationship_type

Type: string (repeatable query parameter)

Format: relationship_type=type1&relationship_type=type2

Examples:

REST API
Python
JavaScript

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

# Single type
result = auth_client.database.from_("employees").format("graph").relationship_types(["manager"]).execute()
# Multiple types
result = auth_client.database.from_("employees").format("graph").relationship_types(["manager", "dotted_line"]).execute()
# Filter ancestors by type
result = auth_client.database.from_("employees").filter("id", "eq", 5).include("ancestors").relationship_types(["mentor"]).execute()

// Single type
const result = await database.from("employees").format("graph").relationshipType("manager").execute()
// Multiple types
const result = await database.from("employees").format("graph").relationshipType("manager").relationshipType("dotted_line").execute()
// Filter ancestors by type
const result = await database.from("employees").get(5, { include: "ancestors", relationshipType: "mentor" })

Behavior:

Filters edges by relationship type
Only returns edges matching specified types
Specify multiple types by repeating the parameter

Validation:

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

Deprecation

The graph_types parameter (comma-separated format) is deprecated but still accepted for backward compatibility. Use relationship_type instead.

Example IDs

Response examples in this reference use short integer IDs (1, 2, 3) for readability. In practice, single-PK tables use UUID primary keys that are auto-generated.

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)

Single Record Tree Behavior

When requesting a single record with format=tree (e.g., GET /data/{id}/?format=tree), if the record is a child node (not a natural root), the API returns the full tree starting from the natural root. This provides complete hierarchy context rather than just the requested node with empty children.

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`	string (UUID)	Unique edge identifier
`from`	string (UUID)	Source node ID (matches main table PK type)
`to`	string (UUID)	Target node ID (matches main table PK type)
`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 relationship_type 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 relationship_type 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": "relationship_type 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'"
}

Note: Record not found with format=tree or format=graph returns a proper 404 response (not an empty array).

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)

REST API
Python
JavaScript

curl -X PATCH "https://your-site.taruvi.cloud/api/apps/my-app/datatables/employees/" \
  -H "Authorization: Bearer YOUR_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"json_schema": { "...updated schema..." }}'

import requests

response = requests.patch(
    "https://your-site.taruvi.cloud/api/apps/my-app/datatables/employees/",
    headers={"Authorization": "Bearer YOUR_TOKEN"},
    json={"json_schema": {"...updated schema..."}}
)

const response = await fetch(
  "https://your-site.taruvi.cloud/api/apps/my-app/datatables/employees/",
  {
    method: "PATCH",
    headers: {
      "Authorization": "Bearer YOUR_TOKEN",
      "Content-Type": "application/json",
    },
    body: JSON.stringify({ 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": "string", "format": "uuid"},
    {"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:

REST API
Python
JavaScript

# 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

# Get Alice's reports
reports = auth_client.database.from_("employees").filter("id", "eq", 1).include("descendants").execute()

# Get Bob's manager
managers = auth_client.database.from_("employees").filter("id", "eq", 2).include("ancestors").execute()

# Get org tree
tree = auth_client.database.from_("employees").filter("id", "eq", 1).format("tree").execute()

// Get Alice's reports
const reports = await database.from("employees").get(1, { include: "descendants" })

// Get Bob's manager
const managers = await database.from("employees").get(2, { include: "ancestors" })

// Get org tree
const tree = await database.from("employees").get(1, { format: "tree" })

Example 2: Matrix Organization

Schema:

{
  "fields": [
    {"name": "id", "type": "string", "format": "uuid"},
    {"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:

REST API
Python
JavaScript

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

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

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

# Get all Emma's managers (both types)
all_managers = auth_client.database.from_("employees").filter("id", "eq", 5).include("ancestors").execute()

# Get only primary reporting line
primary = auth_client.database.from_("employees").filter("id", "eq", 5).include("ancestors").relationship_types(["manager"]).execute()

# Get matrix relationships
matrix = auth_client.database.from_("employees").format("graph").relationship_types(["manager", "dotted_line"]).execute()

// Get all Emma's managers (both types)
const allManagers = await database.from("employees").get(5, { include: "ancestors" })

// Get only primary reporting line
const primary = await database.from("employees").get(5, { include: "ancestors", relationshipType: "manager" })

// Get matrix relationships
const matrix = await database.from("employees")
  .format("graph")
  .relationshipType("manager")
  .relationshipType("dotted_line")
  .execute()

Schema Configuration​

Hierarchy Field​

Graph Configuration​

Edge Table Structure​

Query Parameters​

include​

depth​

format​

relationship_type​

Response Formats​

Standard Response (Include Format)​

Tree Response Format​

Graph Response Format​

Metadata Fields​

_depth​

_relationship_type​

Error Responses​

400 Bad Request​

404 Not Found​

500 Internal Server Error​

Performance Characteristics​

Query Complexity​

Index Strategy​

Scalability Limits​

Edge Semantics​

Edge Direction​

Traversal Direction​

Configuration Settings​

DATA_SERVICE_GRAPH_MAX_DEPTH​

Migration Guide​

Adding Hierarchy to Existing Table​

Schema Migration Example​

Complete Examples​

Example 1: Simple Org Chart​

Example 2: Matrix Organization​

See Also​

Schema Configuration

Hierarchy Field

Graph Configuration

Edge Table Structure

Query Parameters

include

depth

format

relationship_type

Response Formats

Standard Response (Include Format)

Tree Response Format

Graph Response Format

Metadata Fields

_depth

_relationship_type

Error Responses

400 Bad Request

404 Not Found

500 Internal Server Error

Performance Characteristics

Query Complexity

Index Strategy

Scalability Limits

Edge Semantics

Edge Direction

Traversal Direction

Configuration Settings

DATA_SERVICE_GRAPH_MAX_DEPTH

Migration Guide

Adding Hierarchy to Existing Table

Schema Migration Example

Complete Examples

Example 1: Simple Org Chart

Example 2: Matrix Organization

See Also