ADR-0002: Use Parent Pointer Plus ltree For Hierarchy

Status

Accepted

Context

The platform needs fast and reliable hierarchy operations:

• parent lookup,
• child lookup,
• descendant lookup,
• ancestor lookup,
• path display,
• subtree soft delete,
• future root-scope authorization and RLS.

A pure recursive CTE adjacency-list model is flexible but less read-optimized. A pure materialized-path model is fast for reads but weaker for direct parent/child integrity and mutation simplicity.

Decision

Store both:

parent_entity_id
path ltree

parent_entity_id provides adjacency-list semantics.

path provides materialized-path performance.

Containment is not stored in the relationship graph.

Consequences

Positive

• Direct parent lookup.
• Fast child lookup.
• Fast descendants and ancestors.
• Strong foreign key integrity.
• Natural root-scope checks.

Negative

• Reparenting requires updating descendant paths.
• Parent/path consistency must be maintained transactionally.

Trade-off

The design optimizes for read-heavy asset directory usage while accepting write amplification during subtree moves.