[Bug]: Performance Optimization Needed for Hierarchical Database Queries

[asterixix]

Describe the bug
The current database structure and query patterns will likely cause significant performance issues when implementing hierarchical organization structures. Without proper indexing, query optimization, and potentially recursive Common Table Expressions (CTEs), traversing organization hierarchies will become exponentially slower as the depth and complexity of the hierarchies increase.

Steps to Reproduce

1. Simulate a hierarchical structure with multiple levels (e.g., 5+ levels of nested organizations)
2. Attempt to retrieve all descendants of a top-level organization
3. Attempt to retrieve all ancestors of a leaf organization
4. Observe query execution time increasing dramatically with hierarchy depth

Expected behavior
Hierarchical queries should perform efficiently regardless of depth, with response times remaining consistent even for deep organizational structures. Queries that traverse up or down the hierarchy should use optimized paths and appropriate indexing to maintain performance.

Screenshots
N/A - Database performance issue

Desktop (please complete the following information):

• N/A - System-wide database issue

Additional context
This performance issue needs to be addressed before implementing the hierarchical organization features to ensure the system remains responsive. Potential solutions include:

• Using recursive CTEs in PostgreSQL for efficient hierarchy traversal
• Implementing materialized paths or nested set models alongside the adjacency model
• Strategic indexing on parent-child relationship columns
• Caching common hierarchy paths
• Query optimization for common hierarchy traversal patterns
• Implementing database-level functions for common hierarchical operations
• Limiting recursion depth in API calls with pagination for very large hierarchies