Hierarchy and ltree

Purpose

The hierarchy models structural containment.

It answers:

Where does this entity live in the real-world or operational structure?

Examples:

Customer -> Site -> Building -> Asset
Customer -> Site -> Building -> Room -> Asset
Organisation -> Region -> Site -> Asset

Design

Each entity stores:

parent_entity_id uuid null
path ltree not null
path_label text unique not null

This is a hybrid of:

• adjacency list: parent_entity_id,
• materialized path: path.

Why Both Parent and Path?

parent_entity_id gives:

• direct parent lookup,
• fast child lookup,
• foreign key integrity,
• simple mutation logic,
• easy immediate tree rendering.

path gives:

• fast subtree queries,
• fast ancestor queries,
• depth calculation,
• root extraction,
• future RLS and tenant scoping.

Path Label Strategy

Do not build paths from display names.

Bad:

acme.london.main-building.router-01

Names change, contain unsafe characters, and are not stable.

Recommended:

e_01JZABC123.e_01JZABC456.e_01JZABC789

or another compact, immutable, ltree-safe label.

Rules:

• labels are generated once,
• labels are never changed,
• labels are unique,
• labels are not user-facing,
• labels are safe for ltree.

PostgreSQL 16's ltree extension supports hyphens in labels, but compact path labels are still preferred for readability, storage, and future portability.

Creating A Root Entity

Root entity:

parent_entity_id = null
path = path_label

Example:

INSERT INTO entities (id, entity_type_id, parent_entity_id, path_label, path)
VALUES ($id, $typeId, null, $label, $label::ltree);

Creating A Child Entity

Child entity:

parent_entity_id = parent.id
path = parent.path || child.path_label

Example:

INSERT INTO entities (id, entity_type_id, parent_entity_id, path_label, path)
SELECT
  $id,
  $typeId,
  parent.id,
  $label,
  parent.path || $label::ltree
FROM entities parent
WHERE parent.id = $parentId
  AND parent.deleted_at IS NULL;

Query Patterns

Get Parent

SELECT parent.*
FROM entities child
JOIN entities parent ON parent.id = child.parent_entity_id
WHERE child.id = $1
  AND child.deleted_at IS NULL
  AND parent.deleted_at IS NULL;

Get Children

SELECT *
FROM entities
WHERE parent_entity_id = $1
  AND deleted_at IS NULL
ORDER BY created_at ASC;

Get Descendants Including Self

SELECT descendant.*
FROM entities current
JOIN entities descendant ON descendant.path <@ current.path
WHERE current.id = $1
  AND current.deleted_at IS NULL
  AND descendant.deleted_at IS NULL
ORDER BY descendant.path;

Get Descendants Excluding Self

SELECT descendant.*
FROM entities current
JOIN entities descendant ON descendant.path <@ current.path
WHERE current.id = $1
  AND descendant.id <> current.id
  AND current.deleted_at IS NULL
  AND descendant.deleted_at IS NULL
ORDER BY descendant.path;

Get Ancestors Including Self

SELECT ancestor.*
FROM entities current
JOIN entities ancestor ON ancestor.path @> current.path
WHERE current.id = $1
  AND current.deleted_at IS NULL
  AND ancestor.deleted_at IS NULL
ORDER BY nlevel(ancestor.path) ASC;

Get Ancestors Excluding Self

SELECT ancestor.*
FROM entities current
JOIN entities ancestor ON ancestor.path @> current.path
WHERE current.id = $1
  AND ancestor.id <> current.id
  AND current.deleted_at IS NULL
  AND ancestor.deleted_at IS NULL
ORDER BY nlevel(ancestor.path) ASC;

Get Root

SELECT root.*
FROM entities current
JOIN entities root ON root.path = subpath(current.path, 0, 1)
WHERE current.id = $1;

Get Depth

SELECT nlevel(path) AS depth
FROM entities
WHERE id = $1;

Reparenting

Reparenting moves an entity and all descendants under a new parent.

Example:

Move Building A from Site X to Site Y

Affected rows:

Building A
All descendants of Building A

Reparenting Invariants

Before moving target under new_parent, validate:

1. target exists and is active,
2. new parent exists and is active,
3. target is not the root if root movement is disallowed,
4. new parent is not inside target subtree,
5. new parent type is allowed to contain target type,
6. actor has permission for both source and destination roots,
7. move does not cross tenant/root boundary unless explicitly allowed.

Cycle Prevention

Reject move when:

new_parent.path <@ target.path

This means the new parent is already a descendant of the target.

Reparent Update

Pseudo-SQL:

WITH target AS (
  SELECT id, path, path_label
  FROM entities
  WHERE id = $target_id
),
new_parent AS (
  SELECT id, path
  FROM entities
  WHERE id = $new_parent_id
),
updated_target AS (
  UPDATE entities e
  SET
    parent_entity_id = (SELECT id FROM new_parent),
    updated_at = now()
  WHERE e.id = (SELECT id FROM target)
  RETURNING e.*
)
UPDATE entities e
SET
  path = (SELECT path FROM new_parent) || subpath(e.path, nlevel((SELECT path FROM target)) - 1),
  updated_at = now()
WHERE e.path <@ (SELECT path FROM target);

The exact expression should be tested carefully. The goal is to replace the old prefix with the new parent path while preserving the target and descendant suffix.

Cross-Root Moves

Moving entities across root boundaries is dangerous because it may affect:

• authorization,
• tenant scoping,
• Vault access,
• encryption boundaries,
• audit reporting,
• external integrations.

Default rule:

Disallow cross-root moves unless an explicit privileged workflow handles them.

Immediate Children vs Descendants

Use parent_entity_id for immediate children.

Use path <@ for descendants.

Do not use descendants queries just to fetch immediate children unless the UI genuinely needs nested results.

Indexes

Minimum:

CREATE INDEX idx_entities_parent
ON entities(parent_entity_id)
WHERE deleted_at IS NULL;

CREATE INDEX idx_entities_path_gist
ON entities USING GIST(path);

Optional:

CREATE INDEX idx_entities_path_btree
ON entities(path);

Use EXPLAIN ANALYZE for large datasets.

Common Mistakes

Mistake: Storing containment in relationships

Bad:

entity_relationships.relationship_type = contains

Containment belongs to parent_entity_id and path.

Mistake: Building path from names

Bad:

acme.london.router_01

Names change. Use immutable labels.

Mistake: Reparenting outside a transaction

Always perform parent and path updates in one transaction.

Mistake: Ignoring deleted ancestors

If a parent is soft-deleted, children should normally be treated as inaccessible unless explicitly restoring or purging.