Nested Sets as tree implementation is good for hierarchical data that do not change often. However, a change in a single node’s position requires a recomputation of several other nodes which creates database locks and and slows down the system.
Adjacency List is good for Hierarchical data where may be changed (for example, project hierarchy, etc.) It is increasingly used a replacement for Nested Sets. The main advantage of Adjacency List is that updates and deletions occurs only in that single particular node. So, you don’t have to lock other records and update their affected positions (as in Nested Set).
I would like to suggest the addition of Adjacency List in the Doctype (Frappe), and use CTE queries to implement it.
Is this worth going into?