ADR-0003: Use Parent Pointer Plus ltree For Hierarchy

Date: 2026-06-06

Status: Accepted

Context

Eco Manager needs fast hierarchy operations:

• get parent,
• get children,
• get descendants,
• get ancestors,
• move subtrees,
• support future tenant isolation and RLS.

Recursive CTEs are flexible but can become expensive for frequent subtree and ancestor queries.

A pure materialized path is fast for reads but less convenient for immediate parent/child operations and foreign key integrity.

Decision

Each entity will store both:

parent_entity_id
path ltree

parent_entity_id provides simple adjacency-list behavior.

path provides materialized-path behavior for fast tree traversal.

Containment will not be stored in the generic relationship table.

Consequences

Positive

• O(1)-style parent lookup through primary key access.
• Fast child lookup through indexed parent column.
• Fast descendants and ancestors through ltree indexes.
• Strong foreign key integrity.
• Future RLS support using path scope.

Negative

• Moving a subtree requires updating descendant paths.
• Service-layer transactions must preserve parent/path consistency.