Escaping "Recursive Hell": Building ERP Hierarchies with ArangoDB Edges

This is the story of a mistake I kept making until I stopped fighting my database. When I started designing our ERP’s parts categorization and warehouse tracking, I reached for the "safe" choice: standard document references. It nearly broke the system.

I’m sharing this pattern not because it’s the only way, but because it’s the shift in thinking that finally made our complex relationships manageable.

---

The Problem: When parent_id Hits a Wall

Initially, I modeled our hierarchy using a simple parent_id field on each document. It worked for a week. Then the requirements got real:

1. Deep Category Inheritance: If "Electronics" is marked as hazardous, every sub-category three levels down needs to know that—unless an intermediate sub-category overrides it.
2. Dynamic Warehouse Layouts: We needed to map zones, racks, shelves, and bins. A user might ask, "What storage facility is this specific bin in?" requiring a climb up an unpredictable number of levels.

My first mistake: We tried to handle the recursion in TypeScript. We were writing complex loops to walk the tree manually, which was slow, error-prone, and a nightmare to debug.

"We were essentially reinventing a graph engine inside our API, and doing a poor job of it."

---

The Shift: Edges as First-Class Citizens

I realized that the relationship is the data. By moving to ArangoDB edge collections, we separated the "what" (the category) from the "where" (the hierarchy).

An edge is simply a document with _from and _to references. This allows for elegant traversals using AQL's OUTBOUND or INBOUND syntax.

The "Inheritance Trick"

The real win was solving the "property override" problem. By walking the graph inbound (up towards the root), reversing the results, and then collecting them, we let child properties naturally "clobber" parent ones.

/* Get inherited properties with overrides */
LET cat = CONCAT('part_cats/', @categoryKey)

LET props = FLATTEN(
  REVERSE(
    FOR doc IN 0..100 INBOUND cat part_cats_links
      FOR p IN part_cats_props
        FILTER p.cat == doc._key
        RETURN p
  )
)

RETURN (
  FOR p IN props
    COLLECT name = p.name INTO group = p
    RETURN LAST(group[*]) /* The most specific property wins */
)

---

Warehouse Hierarchies: Speed at Scale

We applied the same logic to the warehouse. Relationships between storage facilities, zones, racks, and shelves are stored in w_node_links.

• To find all bins in a Zone: One 1..100 OUTBOUND traversal.
• To find the Root Facility for a Shelf: One 1..100 INBOUND traversal filtered by the storage prefix.

Even with thousands of nodes, the performance remained snappy because ArangoDB’s engine handles the join-heavy lifting internally rather than shipping raw data to our API.

---

Expert Notes: The "Gotchas" & Trade-offs

While I’m a fan of this approach, it’s not a silver bullet. If you’re going to implement this, keep these expert-level constraints in mind:

1. Cycle Detection

In a graph, it is possible for a user to accidentally make "Category A" a parent of "Category B," while "Category B" is already a parent of "Category A."

• The Risk: Infinite loops in your traversal.
• The Fix: Use OPTIONS { uniqueVertices: 'path' } in your AQL and implement a check in your API to prevent circular links during POST or PATCH operations.

2. Why not Postgres CTEs?

You could do this in SQL using Recursive Common Table Expressions (CTEs). However, we found that as the logic grew—adding metadata to the links themselves or handling multiple parentage—the SQL syntax became significantly more difficult to maintain than AQL traversals.

3. When NOT to use this

• Flat Data: If your hierarchy is only ever one level deep, the overhead of an edge collection isn't worth it.
• Static Hierarchies: If your tree never changes, Materialized Paths (storing the path as a string like /electronics/parts/ics) might be faster for simple read-only lookups.

---

Lessons Learned

• Bounded Traversals: Always specify a depth (e.g., 1..100) to prevent accidental runaway queries.
• Indexing: _from and _to are indexed by default, but if you filter edges by metadata, ensure you add secondary indexes.
• Visual Documentation: My teammates didn't "get it" until I showed them Mermaid diagrams. In a graph-based system, the diagram is the documentation.

This pattern turned our most complex ERP features into our most stable ones. If you're struggling with deep hierarchies, stop writing loops and start thinking in edges.

---

Next Step: You can explore the full implementation in my public repo. I'd love to hear how you handle property overrides in your own systems!