A category tree, an org chart, a threaded comment section: these are all the same shape, a hierarchy, and a database table with a flat list of rows is not naturally built for it. This lesson covers ltree, Postgres's own extension for tree-shaped data, then a set of concurrency-safety decisions that show up the moment you combine a tree with role-based permissions and real, simultaneous writers. This is the kind of "how did you actually handle X" follow-up that separates someone who has read about a topic from someone who has shipped it.

Storing a tree: ltree and the alternatives

ltree is a Postgres extension: a data type for tree-shaped data stored as dot-separated label paths, with real index support so you don't need a recursive query to walk it.

Read that first query as: "is this row's path a descendant of Top.Science?" Postgres checks each stored path and keeps only the ones nested somewhere underneath it. An index type called GiST supports every one of these tree operators, while a plain B-tree index only helps with equality and sorting, not "is this an ancestor of that":

ltree is Postgres-only. Without it, three other approaches exist, each with a different trade-off:

The interview answer: ltree stores label-based tree paths and gives fast ancestor/descendant queries through a GiST index, avoiding a hand-written recursive query. Without it, you're choosing between recursion cost (adjacency list) and write cost (nested sets), so the right pick depends on your read/write ratio.

Remember: ltree is a Postgres extension for tree paths with GiST-indexed ancestor/descendant queries. An adjacency list is easy to write but slow to read deep; nested sets are fast to read but expensive to write.

Advisory checks vs authoritative triggers

This is the pattern that repeats through everything else in this lesson: application-level validation is advisory. A database constraint that runs inside the same transaction as the write is authoritative.

Python code cannot make a check-then-write sequence safe under concurrency without external locking, because two requests can both pass the check before either one commits.

Here's the race the Python-only version misses: two concurrent requests can each call clean(), each find no cycle because neither has committed yet, and both proceed to save. Together they create a cycle that neither individual check ever saw. A Postgres trigger runs inside the same transaction as the write itself, so it's the only place this check is actually race-free.

Keep the Python check anyway. It gives a fast, friendly error in the common case, while the trigger is the backstop for the case where two requests land at nearly the same instant.

The same split shows up anywhere you need a guarantee that holds no matter what wrote the data. Blocking UPDATE/DELETE inside a model's save()/delete() methods only works if every write goes through the ORM; a raw SQL client bypasses it completely. An audit log that must truly never change needs a Postgres trigger that rejects any UPDATE/DELETE outright, on top of the Django-layer guard:

Remember: an app-level check and a database trigger are not doing the same job. The check is a fast, friendly error for the common case; the trigger is the only thing that's actually race-free.

Scoped roles over a hierarchy: additive, not precedence

When a role can be granted at any node of a tree, for example a role at /company/sales and a different role at /company/sales/india, you have to decide: do overlapping grants combine, or does the more specific one win?

Decision: additive (union) semantics, no precedence. A user with a role at /company/sales and a different role at /company/sales/india gets the union of both roles' permissions inside /company/sales/india. This is simpler to reason about and to audit: "why can this user do X" is always "some ancestor scope granted it," never "which of two conflicting grants wins." Precedence can be added later through a priority field without breaking any existing grant, so leaving it out now is a safe thing to defer, not a corner you're cutting.

Remember: overlapping scoped roles add up; they don't override each other. That keeps "why can this user do X" answerable with one sentence instead of a precedence table.

Locking a subtree: lock the root, not every row

Moving or soft-deleting a subtree needs to stop two concurrent operations from touching the same subtree at once. Locking the entire descendant set with select_for_update() can hold thousands of row locks for the whole transaction, blocking unrelated reads on nodes that were never actually in conflict.

The fix: lock only the subtree root. The bulk UPDATE that follows acquires its own row locks as it goes, which is all it needs, since you don't have to pre-lock what the update will lock anyway.

Two concrete bugs hide in that one line if you're not careful. First, a select_for_update() queryset that's never evaluated, never iterated or checked with .exists(), sends no lock request to Postgres at all: the lock silently does nothing. Second, locking every descendant instead of just the root is far more expensive than it needs to be for the same correctness.

Remember: lock the subtree root, not every descendant. A select_for_update() queryset does nothing until something forces it to actually run, like .exists().

Soft delete, expiring grants, and the partial index Postgres won't let you write

A naive "currently active" query on a scoped role filters only deleted_at IS NULL. But a role assignment with a past expires_at looks soft-deleted in every way that matters, without actually being soft-deleted, and it still passes that filter, silently granting access that should have lapsed.

The fix is one canonical queryset method that every permission check goes through, instead of trusting every call site to remember both conditions:

This is the same "one blessed path, not a convention every caller has to remember" principle as a shared response helper or a single auth-check function. A permission leak from a forgotten .filter() is a security bug, not a style nit.

A related trap: ActiveManager.get_queryset() returning a plain models.QuerySet looks fine until someone chains .filter(...).delete(). The type of the returned queryset decides whether that .delete() does a soft delete or a real SQL DELETE. Building the manager from a custom queryset subclass keeps soft-delete semantics all the way down any chain:

Django's auto_now=True only fires on .save(). A bulk .update(), which any queryset-level soft delete has to use, silently skips it. Any custom .delete() override on a queryset has to set updated_at explicitly, or every soft-deleted row ends up with a stale timestamp.

Now the indexing question: a composite index on (user_id, deleted_at) still leaves expires_at as a check done after the index scan. The instinct is a partial index with WHERE expires_at > now(). Postgres rejects this, because a partial index's condition is evaluated once, at the moment the index is defined, and now() changes every time it's called, so it can never be an immutable condition. The workable partial index instead targets the permanent grants, which have no time-based condition at all:

Grants that do expire fall back to the regular composite index. Being able to explain why they can't get the same partial-index treatment is the actually interview-worthy part of this answer, not just knowing partial indexes exist.

Remember: a partial index's WHERE clause must be immutable. expires_at > now() fails that test, since now() changes every call; only a condition on fixed columns like expires_at IS NULL works.