# Hierarchies in Backend Design — Resources

## Knowledge — storage models

- [Book: _SQL Antipatterns, Volume 1_ — Bill Karwin (Pragmatic Bookshelf), Ch. "Naive Trees"](https://pragprog.com/titles/bksap1/sql-antipatterns-volume-1/)
  The canonical write-up of the four tree models in SQL: adjacency list, path enumeration (materialised path),
  nested sets, closure table — including the operations each one makes cheap and each one makes miserable.
  Use for: choosing a storage model; arguing why the naive `parent_id` default is sometimes wrong.

- [Docs: PostgreSQL `ltree` — hierarchical tree-like data type](https://www.postgresql.org/docs/current/ltree.html)
  Native materialised-path type with GiST/B-tree index support and path-matching operators (`<@`, `~`, `lquery`).
  Use for: the concrete Postgres-native option; understanding what indexing a path buys you.

- [Article: "PostgreSQL: `ltree` vs. `WITH RECURSIVE`" — CYBERTEC](https://www.cybertec-postgresql.com/en/postgresql-ltree-vs-with-recursive/)
  Direct comparison of the dynamic (recursive CTE over `parent_id`) and materialised (`ltree`) approaches,
  with query plans. Use for: the read-performance side of the trade-off.

- [Article: "Speeding up recursive queries and hierarchical data" — CYBERTEC](https://www.cybertec-postgresql.com/en/postgresql-speeding-up-recursive-queries-and-hierarchic-data/)
  How `WITH RECURSIVE` actually executes, cycle detection, and where it degrades.
  Use for: reasoning about deep-tree read cost without hand-waving.

- [Article: "Modeling Hierarchical Tree Data in PostgreSQL" — Leonard Q. Marcq](https://leonardqmarcq.com/posts/modeling-hierarchical-tree-data)
  Practical walkthrough with schema and benchmarks across models. Use for: seeing a full worked schema per model.

## Knowledge — API surface

- [Spec: AIP-121 Resource-oriented design — Google API Improvement Proposals](https://google.aip.dev/121)
  The reference model for treating an API as a resource hierarchy of collections and resources, and when to break
  out of pure CRUD into custom methods (`:action`). Use for: deciding REST-vs-command shape.

- [Spec: AIP-122 Resource names](https://google.aip.dev/122)
  Hierarchical resource naming (`campaigns/{c}/adSets/{a}/ads/{id}`), when to nest, when to flatten.
  Use for: URL/identifier design for type hierarchies.

- [Spec: AIP-124 Resource association](https://google.aip.dev/124)
  How to model references between resources that are *not* strict parent-child. Use for: DAGs, cross-links,
  and deciding whether nesting is lying to the caller.

- [Guide: Cloud API Design Guide — Google](https://docs.cloud.google.com/apis/design)
  The long-form version of the AIPs, including collections, sub-resources, and standard methods.

- [Spec: AIP-136 Custom methods](https://google.aip.dev/136)
  When a hierarchy operation genuinely cannot be a standard method — `:move`, `:import`, `:publish` — and the rules for
  naming them. Use for: justifying an RPC-shaped endpoint inside an otherwise RESTful API.

- [Article: "Designing robust and predictable APIs with idempotency" — Stripe](https://stripe.com/blog/idempotency)
  The reference treatment of `Idempotency-Key`, retry semantics, and why the server stores the response.
  Use for: safe retries on tree-mutating commands.

- [Article: "Implementing Stripe-like Idempotency Keys in Postgres" — Brandur Leach](https://brandur.org/idempotency-keys)
  The same idea at the schema and transaction level, including atomic phases for multi-step operations.
  Use for: actually building it.

## Knowledge — identity

- [Spec: RFC 9562 — Universally Unique IDentifiers (UUIDs)](https://datatracker.ietf.org/doc/html/rfc9562)
  The 2024 standard that replaced RFC 4122 and introduced UUIDv7. Its own text is the citation for time-ordered UUIDs
  having better database-index locality. Use for: defending v7 as a primary key.

- [Article: "Fixing UUIDv7 (for database use-cases)" — Marc Brooker](https://brooker.co.za/blog/2025/10/22/uuidv7.html)
  The strongest critique: v7 leaks creation timestamps, reduces entropy, and correlates behaviour across datacentres;
  proposes hashing the timestamp with a keyed hash to keep locality while obscuring the clock.
  Use for: the case *against* reflexively choosing v7, and for arguing the trade-off rather than the default.

- [Article: "Realtime Editing of Ordered Sequences" — Figma](https://www.figma.com/blog/realtime-editing-of-ordered-sequences/)
  How Figma orders siblings within a tree node using fractional indexing, so a reorder writes one row.
  Use for: the sibling-order problem, which is a separate axis from the parent-child problem.

- [Reference: fractional-indexing schemes and implementations (gist)](https://gist.github.com/Venryx/8e1c26cce0959f201b2d2080587c112b)
  Survey of the concrete key schemes (LexoRank and relatives) with implementations. Use for: picking one.

## Knowledge — invariants, aggregates, consistency

- [Paper: "Effective Aggregate Design" Parts I–III — Vaughn Vernon (dddcommunity.org)](https://www.dddcommunity.org/library/vernon_2011/)
  The definitive short treatment of aggregate boundaries: what belongs inside one transaction, why big object
  graphs fail under concurrency, and referencing other aggregates by identity.
  Use for: deciding which nodes of a hierarchy are one consistency unit; justifying eventual consistency between them.
  Direct PDFs: [Part I](https://www.dddcommunity.org/wp-content/uploads/files/pdf_articles/Vernon_2011_1.pdf) ·
  [Part III](https://www.dddcommunity.org/wp-content/uploads/files/pdf_articles/Vernon_2011_3.pdf)

- [Pattern: Transactional outbox — Chris Richardson, microservices.io](https://microservices.io/patterns/data/transactional-outbox.html)
  The canonical catalogue entry: write state and event in one local transaction, publish asynchronously via a relay.
  Use for: enforcing cross-aggregate rules without widening the transaction.

- [Guidance: Transactional outbox pattern — AWS Prescriptive Guidance](https://docs.aws.amazon.com/prescriptive-guidance/latest/cloud-design-patterns/transactional-outbox.html)
  Same pattern with the dual-write failure modes spelled out, plus polling vs change-data-capture relays.

## Gaps

- **Partly filled:** client-driven tree construction now has Stripe/Brandur on idempotency and Figma on fractional
  indexing, but no single source covers building a whole hierarchy client-side. Candidate direction: Linear's sync engine
  write-ups, offline-first literature.
- No strong source yet on **cascading delete policy design** (soft delete + subtree, orphan reparenting, restore).
- No strong source yet on **permission inheritance over hierarchies** (out of scope for now, but will be needed).