Design a Hierarchy Store with Subtree Queries

Clarified query patterns (subtree listing vs ancestor checks vs children-only) and mutation frequency before designing
Established scale assumptions (node count, depth, fanout) and consistency needs (especially for permission-inheritance use cases)
Explicitly scoped out multi-parent DAGs / cross-tree references unless asked

Chose adjacency list (or equivalent) as source of truth and justified why, rather than only using a denormalized structure
Proposed at least one denormalized read index (closure table, materialized path, or nested sets) to make subtree/ancestor queries fast
Designed API surface covering create, move, delete, get-children, get-subtree, get-ancestors with pagination
Defined schema with clear PK/SK and indexes for both primary store and denormalized index

Discussed the core tradeoff between adjacency list, materialized path, and closure table representations
Addressed how large subtree moves are handled without a single giant transaction (batching/async job)
Addressed consistency risk during async reindexing and proposed a fallback for correctness-critical queries
Addressed cycle prevention when moving a subtree under its own descendant
Addressed hot/large-subtree read scaling (caching, pagination, precomputed counts)

Structured the discussion within time budget, spending majority of time on high-level design and deep dives
Clearly articulated tradeoffs rather than presenting one design as the only option
Delivered a concise, confident opening/closing summary tying the design together

Problem