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

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

Python

# 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()

JavaScript

// 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

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)

Python

# 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()

JavaScript

// 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

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

Python

# 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()

JavaScript

// 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

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

Python

# 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()

JavaScript

// 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

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..." }}'

Python

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..."}}
)

JavaScript

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

# 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

Python

# 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()

JavaScript

// 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

# 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

Python

# 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()

JavaScript

// 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()

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