Store a hierarchy and return all children
Company: Atlassian
Role: Software Engineer
Category: System Design
Difficulty: medium
Interview Round: Onsite
Design a service to store a hierarchical tree of nodes and expose APIs.
Requirements:
- Persist a hierarchy (a forest with multiple roots is acceptable).
- Support:
1) Add a node under a parent.
2) Return **all descendants** (all children recursively) of a given node.
- Emphasis on REST API design, DB schema, and edge cases.
Deliverables:
- API endpoints and example requests/responses.
- Storage model choice (e.g., adjacency list, materialized path, nested sets, closure table) and justification.
- How to handle large subtrees (pagination) and prevent cycles.
Overview: This question evaluates understanding of hierarchical data modeling, REST API design, database schema choices (e.g., adjacency list vs alternatives), and systems-level concerns like scalability, pagination for large subtrees, and cycle prevention.
Read the full Atlassian Software Engineer interview experience this question came from
Community answers
Answer by freny07pethelis1996
name: Node hierarchy
overview: "One design for a forest of nodes. Closure table plus parent pointer. Paged descendants. Cycle checks under row locks."
todos:
id: model
content: nodes.parent_id plus node_closure, depth cap 32
status: pending
id: api
content: Create, children, descendants, move, delete
status: pending
id: page
content: Keyset pages on descendant id; maxDepth uses depth order
status: pending
id: cycles
content: Descendant check after ancestor locks, same transaction as the rewrite
status: pending
isProject: false
Node hierarchy
A tenant stores a forest. It adds a node under a parent, or as a new root, and it reads every descendant of a node as a page of rows. The parent lives on the node. Every ancestor of that node also has a closure row. Descendants are a range of that index. A cycle is a new parent that is already in those rows.
Interactive board: Node hierarchy (repo). Cursor live board: hierarchy-service.canvas.tsx. Row layouts for the four storage models: How a tree is stored. Repo copy: tree-storage-models.canvas.tsx.
Pattern (same as top posts): lock decisions → size the tree → name the components → APIs → schema and SQL → the page read → cycles and the move cap → scale, failure, observability.
Running tree used in every section. Tenant acme. Ids are shortened ULIDs, so sort order matches creation order. HR is a second root.
01J0001 Org
├── 01J0002 Engineering
│ ├── 01J0005 Platform
│ │ └── 01J0007 API
│ └── 01J0006 Product
└── 01J0003 Sales
01J0004 HR
C