Model org charts in PostgreSQL: adjacency lists vs closure

What an org chart needs to support

An org chart is a map of who reports to whom, and how teams roll up into departments. When you model org charts in PostgreSQL, you are not just saving a manager_id on each person. You are supporting real work: org browsing, reporting, and access rules.

Most users expect three things to feel instant: exploring the org, finding people, and filtering results to "my area". They also expect updates to be safe. When a manager changes, the chart should update everywhere without breaking reports or permissions.

In practice, a good model needs to answer a few recurring questions:

• What is this person’s chain of command (up to the top)?
• Who is under this manager (direct reports and the full subtree)?
• How do people group into teams and departments for dashboards?
• How do reorganizations happen without glitches?
• Who can see what, based on the org structure?

It gets harder than a simple tree because organizations change often. Teams move between departments, managers swap groups, and some views are not purely "people report to people". For example: a person belongs to a team, and teams belong to departments. Permissions add another layer: the org’s shape becomes part of your security model, not just a diagram.

A few terms help keep designs clear:

• A node is one item in the hierarchy (a person, a team, or a department).
• A parent is the node directly above it (a manager, or a department that owns a team).
• An ancestor is any node above it at any distance (your manager’s manager).
• A descendant is any node below it at any distance (everyone under you).

Example: if Sales moves under a new VP, two things should stay true immediately. Dashboards still filter "all of Sales", and the new VP’s permissions cover Sales automatically.

Decisions to make before choosing a table design

Before you settle on a schema, be clear about what your app must answer every day. "Who reports to whom?" is only the start. Many org charts also need to show who leads a department, who approves time off for a team, and who can see a report.

Write down the exact questions your screens and permission checks will ask. If you can’t name the questions, you’ll end up with a schema that looks right but is hard to query.

The decisions that shape everything:

• Which queries must be fast: direct manager, chain to the CEO, full subtree under a leader, or "everyone in this department"?
• Is it a strict tree (one manager) or a matrix org (more than one manager or lead)?
• Are departments nodes in the same hierarchy as people, or a separate attribute (like department_id on each person)?
• Can someone belong to multiple teams (shared services, squads)?
• How do permissions flow: down the tree, up the tree, or both?

Those choices define what "correct" data looks like. If Alex leads both Support and Onboarding, a single manager_id or "one lead per team" rule might not work. You may need a join table (leader to team) or a clear policy like "one primary team, plus dotted-line teams".

Departments are another fork. If departments are nodes, you can express "Department A contains Team B contains Person C". If departments are separate, you will filter with department_id = X, which is simpler but can fall apart when teams span departments.

Finally, define permissions in plain language. "A manager can view salary for everyone under them, but not peers" is a down-the-tree rule. "Anyone can see their management chain" is an up-the-tree rule. Decide this early because it changes which hierarchy model will feel natural and which one will force expensive queries later.

Adjacency list: a simple schema for managers and teams

If you want the least moving parts, an adjacency list is the classic starting point. Each person stores a pointer to their direct manager, and the tree is created by following those pointers.

A minimal setup looks like this:

create table departments (
  id bigserial primary key,
  name text not null unique
);

create table teams (
  id bigserial primary key,
  department_id bigint not null references departments(id),
  name text not null,
  unique (department_id, name)
);

create table employees (
  id bigserial primary key,
  full_name text not null,
  team_id bigint references teams(id),
  manager_id bigint references employees(id)
);

You can also skip the separate tables and keep department_name and team_name as columns on employees. That is faster to start, but harder to keep clean (typos, renamed teams, and inconsistent reporting). Separate tables make filtering and permission rules easier to express consistently.

Add guardrails early. Bad hierarchy data is painful to fix later. At a minimum, prevent self-management (manager_id <> id). Also decide whether a manager can be outside the same team or department, and whether you need soft deletes or historical changes (for auditing reporting lines).

With adjacency lists, most changes are simple writes: changing a manager updates employees.manager_id, and moving teams updates employees.team_id (often along with the manager). The catch is that a small write can have big downstream effects. Reporting rollups change, and any "manager can see all reports" rule must now follow the new chain.

This simplicity is the adjacency list’s biggest strength. Its weakness shows up when you frequently filter by "everyone under this manager", because you usually rely on recursive queries to walk the tree each time.

Adjacency list: common queries for filtering and reporting

With an adjacency list, many useful org chart questions turn into recursive queries. If you model org charts in PostgreSQL this way, these are the patterns you’ll run constantly.

Direct reports (one level)

The simplest case is a manager’s immediate team:

SELECT id, full_name, title
FROM employees
WHERE manager_id = $1
ORDER BY full_name;

This is fast and readable, but it only goes one level down.

Chain of command (upwards)

To show who someone reports to (manager, manager’s manager, and so on), use a recursive CTE:

WITH RECURSIVE chain AS (
  SELECT id, full_name, manager_id, 0 AS depth
  FROM employees
  WHERE id = $1

  UNION ALL

  SELECT e.id, e.full_name, e.manager_id, c.depth + 1
  FROM employees e
  JOIN chain c ON e.id = c.manager_id
)
SELECT *
FROM chain
ORDER BY depth;

This supports approvals, escalation paths, and manager breadcrumbs.

Full subtree (downwards)

To get everyone under a leader (all levels), flip the recursion:

WITH RECURSIVE subtree AS (
  SELECT id, full_name, manager_id, department_id, 0 AS depth
  FROM employees
  WHERE id = $1

  UNION ALL

  SELECT e.id, e.full_name, e.manager_id, e.department_id, s.depth + 1
  FROM employees e
  JOIN subtree s ON e.manager_id = s.id
)
SELECT *
FROM subtree
ORDER BY depth, full_name;

A common report is "everyone in department X under leader Y":

WITH RECURSIVE subtree AS (
  SELECT id, department_id
  FROM employees
  WHERE id = $1
  UNION ALL
  SELECT e.id, e.department_id
  FROM employees e
  JOIN subtree s ON e.manager_id = s.id
)
SELECT e.*
FROM employees e
JOIN subtree s ON s.id = e.id
WHERE e.department_id = $2;

Adjacency list queries can get risky for permissions because access checks often depend on the full path (is the viewer an ancestor of this person?). If one endpoint forgets the recursion or applies filters in the wrong place, you can leak rows. Also watch for data issues like cycles and missing managers. One bad record can break recursion or return surprising results, so permission queries need safeguards and good constraints.

Closure table: how it stores the whole hierarchy

A closure table stores every ancestor-descendant relationship, not just the direct manager link. Instead of walking the tree one step at a time, you can ask: "Who is under this leader?" and get the full answer with a simple join.

You usually keep two tables: one for nodes (people or teams) and one for hierarchy paths.

-- nodes
employees (
  id bigserial primary key,
  name text not null,
  manager_id bigint null references employees(id)
)

-- closure
employee_closure (
  ancestor_id bigint not null references employees(id),
  descendant_id bigint not null references employees(id),
  depth int not null,
  primary key (ancestor_id, descendant_id)
)

The closure table stores pairs like (Alice, Bob) meaning "Alice is an ancestor of Bob". It also stores a row where ancestor_id = descendant_id with depth = 0. That self-row looks odd at first, but it makes many queries cleaner.

depth tells you how far apart two nodes are: depth = 1 is a direct manager, depth = 2 is a manager’s manager, and so on. This matters when direct reports should be treated differently from indirect ones.

The main benefit is predictable, fast reads:

• Whole subtree lookups are quick (everyone under a director).
• Chains of command are simple (all managers above someone).
• You can separate direct vs indirect relationships using depth.

The cost is maintenance on updates. If Bob changes manager from Alice to Dana, you must rebuild closure rows for Bob and everyone below Bob. The typical approach is: delete old ancestor paths for that subtree, then insert new paths by combining Dana’s ancestors with every node in Bob’s subtree and recalculating depth.

Closure table: common queries for fast filtering

A closure table stores every ancestor-descendant pair ahead of time (often as org_closure(ancestor_id, descendant_id, depth)). That makes org filters fast because most questions become a single join.

To list everyone under a manager, join once and filter by depth:

-- Descendants (everyone in the subtree)
SELECT e.*
FROM employees e
JOIN org_closure c
  ON c.descendant_id = e.id
WHERE c.ancestor_id = :manager_id
  AND c.depth > 0;

-- Direct reports only
SELECT e.*
FROM employees e
JOIN org_closure c
  ON c.descendant_id = e.id
WHERE c.ancestor_id = :manager_id
  AND c.depth = 1;

For the chain of command (all ancestors of one employee), flip the join:

SELECT m.*
FROM employees m
JOIN org_closure c
  ON c.ancestor_id = m.id
WHERE c.descendant_id = :employee_id
  AND c.depth > 0
ORDER BY c.depth;

Filtering becomes predictable. Example: "all people under leader X, but only in department Y":

SELECT e.*
FROM employees e
JOIN org_closure c ON c.descendant_id = e.id
WHERE c.ancestor_id = :leader_id
  AND e.department_id = :department_id;

Because the hierarchy is precomputed, counts are straightforward too (no recursion). This helps dashboards and permission-scoped totals, and it plays nicely with pagination and search since you can apply ORDER BY, LIMIT/OFFSET, and filters directly on the descendant set.

How each model affects permissions and access checks

A common org rule is simple: a manager can view (and sometimes edit) everything under them. Access is based on descendants in the org tree. The schema you choose changes how often you pay the cost of figuring out "who is under who".

With an adjacency list, the permission check usually needs recursion. If a user opens a page that lists 200 employees, you typically build the descendant set with a recursive CTE and filter target rows against it.

With a closure table, the same rule can often be checked with a simple existence test: "Is the current user an ancestor of this employee?" If yes, allow.

-- Closure table permission check (conceptual)
SELECT 1
FROM org_closure c
WHERE c.ancestor_id = :viewer_id
  AND c.descendant_id = :employee_id
LIMIT 1;

This simplicity matters when you introduce row-level security (RLS), where every query automatically includes a rule like "only return rows the viewer can see". With adjacency lists, the rule often embeds recursion and can be harder to tune. With a closure table, the policy is often a straightforward EXISTS (...) check.

Edge cases are where permission logic breaks most often:

• Dotted-line reporting: one person effectively has two managers.
• Assistants and delegates: access is not based on hierarchy, so store explicit grants (often with an expiry).
• Temporary access: time-bound permissions should not be baked into the org structure.
• Cross-team projects: grant access by project membership, not by management chain.

If you’re building this in AppMaster, a closure table often maps cleanly to a visual data model and keeps the access check itself simple across web and mobile apps.

Tradeoffs: speed, complexity, and maintenance

The biggest choice is what you optimize for: simple writes and a small schema, or fast reads for "who’s under this manager" and permission checks.

Adjacency lists keep the table small and updates easy. The cost shows up on reads: a whole subtree usually means recursion. That can be fine if your org is small, your UI only loads a few levels, or hierarchy-based filters are used in only a handful of places.

Closure tables flip the tradeoff. Reads become fast because you can answer "all descendants" with regular joins. Writes get more complex because a move or reorg can require inserting and deleting many relationship rows.

In real work, the tradeoff usually looks like this:

• Read performance: adjacency needs recursion; closure is mostly joins and stays fast as the org grows.
• Write complexity: adjacency updates one parent_id; closure updates many rows for a single move.
• Data size: adjacency grows with people/teams; closure grows with relationships (in the worst case, roughly N squared for a deep tree).

Indexing matters in both models, but the target differs:

• Adjacency list: index the parent pointer (manager_id), plus common filters like an "active" flag.
• Closure table: index (ancestor_id, descendant_id) and also descendant_id alone for common lookups.

A simple rule: if you rarely filter by hierarchy and permission checks are only "manager sees direct reports", an adjacency list is often enough. If you regularly run "everyone under VP X" reports, filter by department trees, or enforce hierarchical permissions across many screens, closure tables often pay for the extra maintenance.

Step by step: moving from adjacency list to closure table

You don’t have to choose between models on day one. A safe path is to keep your adjacency list (manager_id or parent_id) and add a closure table next to it, then migrate reads over time. This lowers risk while you validate how the new hierarchy behaves in real queries and permission checks.

Start by creating a closure table (often called org_closure) with columns like ancestor_id, descendant_id, and depth. Keep it separate from your existing employees or teams table so you can backfill and validate without touching current features.

A practical rollout:

• Create the closure table and indexes while keeping the adjacency list as the source of truth.
• Backfill closure rows from current manager relationships, including the self row (each node is its own ancestor at depth 0).
• Validate with spot checks: pick a few managers and confirm the same set of subordinates appears in both models.
• Switch read paths first: reports, filters, and hierarchical permissions should read from the closure table before you change writes.
• Keep closure updated on every write (re-parent, hire, move team). Once stable, retire recursion-based queries.

When you validate, focus on the cases that usually break access rules: manager changes, top-level leaders, and users with no manager.

If you’re building in AppMaster, you can keep the old endpoints running while you add new ones that read from the closure table, then flip over once the results match.

Common mistakes that break org filtering or permissions

The fastest way to break org features is to let the hierarchy become inconsistent. The data might look fine row by row, but small mistakes can cause wrong filters, slow pages, or a permission leak.

A classic problem is accidentally creating a cycle: A manages B, and later someone sets B to manage A (or a longer loop through 3-4 people). Recursive queries may run forever, return duplicate rows, or time out. Even with a closure table, cycles can poison ancestor/descendant rows.

Another common issue is closure drift: you change someone’s manager, but you only update the direct relationship and forget to rebuild the closure rows for the subtree. Then filters like "everyone under this VP" return a mix of the old and new structure. It’s hard to spot because individual profile pages still look correct.

Org charts also get messy when departments and reporting lines are mixed without clear rules. A department is often an administrative grouping, while reporting lines are about managers. If you treat them as the same tree, you can end up with weird behavior like a "department move" unexpectedly changing access.

Permissions fail most often when checks only look at the direct manager. If you allow access when viewer is manager of employee, you miss the full chain. The result is either over-blocking (skip-level managers can’t see their org) or over-sharing (someone gains access by being set as a temporary direct manager).

Slow list pages often come from running recursive filtering on every request (every inbox, every ticket list, every employee search). If the same filter is used everywhere, you either want a precomputed path (closure table) or a cached set of allowed employee IDs.

A few practical safeguards:

• Block cycles with validation before saving manager changes.
• Decide what "department" means, and keep it separate from reporting.
• If you use a closure table, rebuild descendant rows on manager changes.
• Write permission rules for the full chain, not only the direct manager.
• Precompute org scopes used by list pages instead of recalculating recursion each time.

If you build admin panels in AppMaster, treat "change manager" as a sensitive workflow: validate it, update related hierarchy data, and only then let it affect filters and access.

Quick checks before you ship

Before you call your org chart "done", make sure you can explain access in plain words. If someone asks, "Who can see employee X, and why?", you should be able to point to one rule and one query (or view) that proves it.

Performance is the next reality check. With an adjacency list, "show me everyone under this manager" becomes a recursive query whose speed depends on depth and indexing. With a closure table, reads are usually fast, but you must trust your write path to keep the table correct after every change.

A short pre-ship checklist:

• Pick one employee and trace visibility end to end: which chain grants access, and which role denies it.
• Benchmark a manager subtree query using your expected size (for example, 5 levels deep and 50,000 employees).
• Block bad writes: prevent cycles, self-management, and orphan nodes with constraints and transaction checks.
• Test reorg safety: moves, merges, manager changes, and rollback when something fails mid-way.
• Add permission tests that assert both allowed and denied access for realistic roles (HR, manager, team lead, support).

A practical scenario to validate: a support agent can view only employees in their assigned department, while a manager can view their full subtree. If you can model org charts in PostgreSQL and prove both rules with tests, you’re close to shipping.

If you’re building this as an internal tool in AppMaster, keep these checks as automated tests around the endpoints that return org lists and employee profiles, not just database queries.

Example scenario and next steps

Imagine a company with three departments: Sales, Support, and Engineering. Each department has two teams, and each team has a lead. Sales Lead A can approve discounts for their team, Support Lead B can view all tickets for their department, and the VP of Engineering can see everything under Engineering.

Then a reorg happens: one Support team moves under Sales, and a new manager is added between the Sales Director and two team leads. The next day, someone requests access: "Let Jamie (a Sales analyst) see all customer accounts for the Sales department, but not Engineering."

If you model org charts in PostgreSQL with an adjacency list, the schema is simple, but the app work shifts into your queries and permission checks. Filters like "everyone under Sales" usually need recursion. Once you add approvals (like "only managers in the chain can approve"), edge cases after a reorg start to matter.

With a closure table, reorgs mean more write work (updating ancestor/descendant rows), but the read side becomes straightforward. Filtering and permissions often become simple joins: "is this user an ancestor of that employee?" or "is this team inside this department subtree?".

This shows up directly in the screens people build: people pickers scoped to a department, approval routing to the nearest manager above a requester, admin views for department dashboards, and audits that explain why access existed on a given date.

Next steps:

1. Write the permission rules in plain language (who can see what, and why).
2. Pick a model that matches the most common checks (fast reads vs simpler writes).
3. Build an internal admin tool that lets you test reorgs, access requests, and approvals end to end.

If you want to build those org-aware admin panels and portals quickly, AppMaster (appmaster.io) can be a practical fit: it lets you model PostgreSQL-backed data, implement approval logic in a visual Business Process, and deliver web and native mobile apps from the same backend.