Моделирование оргструктур в PostgreSQL: списки смежности против таблицы замыканий

Что нужно поддерживать в оргструктуре

Оргструктура — это карта того, кто кому подчиняется, и как команды складываются в отделы. При моделировании оргструктур в PostgreSQL вы храните не просто manager_id для каждого человека. Вы поддерживаете реальные сценарии: просмотр оргструктуры, отчёты и правила доступа.

Большинство пользователей ожидают мгновенной работы в трёх вещах: изучение структуры, поиск людей и фильтрация по «моей области». Они также ждут, что обновления будут надёжными. Когда меняется менеджер, схема должна обновиться везде, не ломая отчёты и права доступа.

На практике хорошая модель должна отвечать на несколько типичных вопросов:

• Какова цепочка подчинения этого человека (до самого верха)?
• Кто под этим менеджером (прямые подчинённые и всё поддерево)?
• Как люди группируются в команды и отделы для дашбордов?
• Как происходят реорганизации без сбоев?
• Кто что может видеть на основании оргструктуры?

Задача усложняется по сравнению с простым деревом, потому что организации часто меняются. Команды переходят между отделами, менеджеры меняют группы, и некоторые представления не являются чисто «человек подчиняется человеку». Например: человек принадлежит команде, а команды принадлежат отделам. Права доступа добавляют ещё один слой: форма оргструктуры становится частью вашей модели безопасности, а не просто схемой.

Несколько терминов помогут держать дизайн в голове:

• Узел — один элемент иерархии (человек, команда или отдел).
• Родитель — узел прямо выше (менеджер или отдел, которому принадлежит команда).
• Предок — любой узел выше на любом расстоянии (менеджер менеджера и т. д.).
• Потомок — любой узел ниже на любом расстоянии (все подчинённые).

Например: если отдел Sales переносится под нового вице‑президента, два момента должны остаться верными сразу. Дашборды по‑прежнему фильтруют «всё в Sales», а права нового вице‑президента автоматически покрывают Sales.

Решения, которые нужно принять перед выбором таблицы

Прежде чем выбрать схему, чётко пропишите, что ваше приложение должно отвечать ежедневно. Вопрос «кто кому подчиняется?» — это только начало. Многие оргструктуры также должны показывать, кто руководит отделом, кто одобряет отпуск для команды и кто может видеть отчёт.

Запишите точные вопросы, которые будут задавать интерфейсы и проверки прав. Если вы не можете назвать вопросы, у вас получится схема, которая выглядит правильно, но с которой неудобно работать.

Решения, влияющие на всё:

• Какие запросы должны быть быстрыми: прямой менеджер, цепочка до CEO, всё поддерево за лидером или «все в этом отделе»?
• Это строгий дерево‑формат (один менеджер) или матричная организация (несколько менеджеров или лидов)?
• Являются ли отделы узлами в той же иерархии, что и люди, или это отдельный атрибут (например, department_id у каждого сотрудника)?
• Может ли кто‑то принадлежать нескольким командам (shared services, сквады)?
• Как текут права: вниз по дереву, вверх или в обе стороны?

Эти выборы определяют, как выглядят «правильные» данные. Если Алекс руководит и Support, и Onboarding, один manager_id или правило «один лидер на команду» могут не подойти. Возможно, потребуется таблица‑связка (лидер ↔ команда) или чёткая политика вроде «одна основная команда плюс пунктирные команды».

Отделы — ещё одна точка ветвления. Если отделы — это узлы, вы можете выразить «Отдел A содержит Команду B содержит Человека C». Если отделы — отдельный атрибут, вы будете фильтровать department_id = X, что проще, но может развалиться, когда команды пересекают отделы.

Наконец, опишите права простым языком. «Менеджер может просматривать зарплаты всех подчинённых, но не сверстников» — правило вниз по дереву. «Каждый может видеть свою цепочку управления» — правило вверх по дереву. Решите это заранее, потому что это меняет, какая модель иерархии будет естественной и какая приведёт к дорогим запросам позже.

Adjacency list: простая схема для менеджеров и команд

Если вам нужны минимальные компоненты, adjacency list — классическая отправная точка. Каждый сотрудник хранит указатель на своего прямого менеджера, и дерево строится следованием по этим указателям.

Минимальная настройка выглядит так:

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)
);

Вы также можете пропустить отдельные таблицы и держать department_name и team_name в колонках employees. Это быстрее на старте, но сложнее поддерживать (опечатки, переименования команд и неконсистентные записи). Отдельные таблицы упрощают фильтрацию и правила прав доступа.

Задайте ограничения сразу. Плохие данные иерархии трудно исправлять позже. Минимум — запретить самоподчинение (manager_id <> id). Также решите, может ли менеджер быть в другой команде или отделе и нужны ли мягкие удаления или история изменений (для аудита цепочек подчинения).

С adjacency list большинство изменений — простые записи: смена менеджера обновляет employees.manager_id, а перемещение команды — employees.team_id (часто вместе с менеджером). Но одна небольшая запись может иметь большие побочные эффекты: свёртки отчётов меняются, и любое правило «менеджер видит все отчёты» теперь должно следовать новой цепочке.

Эта простота — главная сила adjacency list. Слабость проявляется, когда вы часто фильтруете «все под этим менеджером», потому что обычно приходится каждый раз запускать рекурсивные запросы для обхода дерева.

Adjacency list: типичные запросы для фильтрации и отчётов

С adjacency list многие полезные вопросы оргструктуры превращаются в рекурсивные запросы. Если вы моделируете оргструктуру в PostgreSQL таким образом, это паттерны, которые вы будете использовать постоянно.

Прямые подчинённые (один уровень)

Простейший случай — команда менеджера на один уровень:

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

Это быстро и читаемо, но охватывает только один уровень.

Цепочка подчинения (вверх)

Чтобы показать, кому кто подчиняется (менеджер, менеджер менеджера и т. д.), используйте рекурсивный 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;

Это полезно для утверждений, путей эскалации и хлебных крошек менеджера.

Полное поддерево (вниз)

Чтобы получить всех под лидером (на всех уровнях), переверните рекурсию:

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;

Распространённый отчёт — «все в отделе X под лидером 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 могут быть рискованными для прав доступа, потому что проверки часто зависят от полного пути (является ли просматривающий предком этого человека?). Если один конечный пункт забывает рекурсию или применяет фильтры не в том месте, можно «протечь» строки. Также следите за проблемами данных: циклы и отсутствующие менеджеры. Одна плохая запись может сломать рекурсию или вернуть неожиданные результаты, поэтому проверки прав нуждаются в защитных механизмах и хороших ограничениях.

Closure‑таблица: как она хранит всю иерархию

Closure‑таблица хранит каждую пару предок‑потомок, а не только прямую ссылку менеджера. Вместо того чтобы идти шаг за шагом по дереву, вы можете сразу спросить: «Кто под этим лидером?» и получить полный ответ простым JOIN.

Обычно держат две таблицы: одну для узлов (люди или команды) и одну для путей иерархии.

-- 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)
)

Closure‑таблица хранит пары вроде (Alice, Bob), означающие «Alice — предок Bob». Она также хранит строку, где ancestor_id = descendant_id и depth = 0. Эта самосвязь сначала выглядит странно, но делает многие запросы проще.

depth показывает расстояние между двумя узлами: depth = 1 — прямой менеджер, depth = 2 — менеджер менеджера и т. д. Это важно, когда прямых подчинённых нужно отличать от косвенных.

Главная выгода — предсказуемое и быстрое чтение:

• Поиск всего поддерева выполняется быстро (все под директора).
• Цепочки подчинения просты (все менеджеры выше кого‑то).
• Можно разделять прямые и косвенные отношения по depth.

Цена — обслуживание при обновлениях. Если Боб меняет менеджера с Алисы на Даню, нужно пересчитать строки closure для Боба и всех под ним. Обычный подход: удалить старые пути предков для этого поддерева, затем вставить новые пути, комбинируя предков Даня с каждым узлом поддерева Боба и пересчитав depth.

Closure‑таблица: типичные запросы для быстрой фильтрации

Closure‑таблица заранее хранит каждую пару предок‑потомок (часто как org_closure(ancestor_id, descendant_id, depth)). Это делает фильтры по оргструктуре быстрыми, потому что большинство вопросов превращается в один JOIN.

Чтобы перечислить всех под менеджером, сделайте один JOIN и отфильтруйте по 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;

Для цепочки подчинения (все предки одного сотрудника) переверните 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;

Фильтрация становится предсказуемой. Пример: «все люди под лидером X, но только в отделе 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;

Поскольку иерархия предвычислена, подсчёты просты (без рекурсии). Это помогает дашбордам и итогам, ограниченным правами, и удобно для пагинации и поиска, так как вы можете применять ORDER BY, LIMIT/OFFSET и фильтры прямо к множеству потомков.

Как каждая модель влияет на права доступа и проверки

Распространённое правило: менеджер может просматривать (а иногда и изменять) всё, что под ним. Выбор схемы определяет, как часто вы платите за вычисление «кто под кем».

В adjacency list проверка прав обычно требует рекурсии. Если пользователь открывает страницу, где отображается 200 сотрудников, вы обычно строите множество потомков рекурсивным CTE и фильтруете целевые строки по нему.

С closure‑таблицей такое же правило можно часто проверять простым тестом существования: «является ли текущий пользователь предком этого сотрудника?» Если да — доступ разрешён.

-- 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;

Эта простота важна, когда вы вводите row‑level security (RLS), где каждый запрос автоматически включает правило «возвращать только строки, которые видит просматривающий». В adjacency list правило часто встраивает рекурсию и его труднее оптимизировать. С closure‑таблицей политика часто сводится к простому EXISTS (...).

Крайние случаи чаще всего ломают логику прав:

• Пунктирные подчинения (dotted‑line): человек фактически имеет двух менеджеров.
• Ассистенты и делегаты: доступ не основан на иерархии — храните явные гранты (часто с датой окончания).
• Временный доступ: временные права не стоит вплетать в структуру оргдерева.
• Кросс‑командные проекты: давайте доступ по членству в проекте, а не по цепочке управления.

Если вы строите это в AppMaster, closure‑таблица часто хорошо ложится на визуальную модель данных и делает проверку прав простой и одинаковой в веб‑ и мобильных интерфейсах.

Компромиссы: скорость, сложность и поддержка

Главный выбор — что вы оптимизируете: простые записи и небольшая схема или быстрые чтения для «кто под этим менеджером» и проверки прав.

Adjacency list держит таблицу маленькой и обновления простыми. Цена проявляется при чтении: полное поддерево обычно требует рекурсии. Это нормально, если ваша оргструктура невелика, интерфейс загружает лишь пару уровней или фильтры по иерархии используются редко.

Closure‑таблица меняет компромисс. Чтения становятся быстрыми, потому что «все потомки» — простые JOIN. Записи усложняются: перемещение или реорганизация требуют вставки и удаления множества строк отношений.

В реальной работе это обычно выглядит так:

• Производительность чтения: adjacency требует рекурсии; closure — в основном JOIN и остаётся быстрой по мере роста структуры.
• Сложность записи: adjacency обновляет один parent_id; closure обновляет много строк при перемещении.
• Объём данных: adjacency растёт с числом людей/команд; closure растёт с числом отношений (в худшем случае примерно N^2 для глубокого дерева).

Индексация важна в обеих моделях, но цели разные:

• Adjacency list: индексируйте указатель родителя (manager_id) и распространённые фильтры вроде флага active.
• Closure‑таблица: индексируйте (ancestor_id, descendant_id) и отдельно descendant_id для быстрых проверок.

Простое правило: если вы редко фильтруете по иерархии и проверки прав — лишь «менеджер видит прямых подчинённых», adjacency list зачастую достаточно. Если вы регулярно формируете отчёты «все под VP X», фильтруете по дереву отделов или применяете иерархические права по множеству экранов, closure‑таблица обычно окупит дополнительные затраты на поддержание.

Поэтапный путь: переход от adjacency list к closure‑таблице

Не обязательно выбирать модель в день запуска. Надёжный путь — сохранить adjacency list (manager_id или parent_id) и добавить closure‑таблицу рядом, затем мигрировать чтения постепенно. Это снижает риск и даёт время проверить, как новая иерархия работает в реальных запросах и проверках прав.

Начните с создания closure‑таблицы (обычно org_closure) с колонками ancestor_id, descendant_id и depth. Держите её отдельно от существующих employees или teams, чтобы можно было заполнить и проверить данные без остановки текущих фич.

Практический план внедрения:

• Создайте closure‑таблицу и индексы, при этом оставив adjacency list источником истины.
• Заполните closure‑строки из текущих отношений менеджеров, включая самосвязи (каждый узел — свой предок с depth 0).
• Проведите проверку: для нескольких менеджеров убедитесь, что подчинённые совпадают в обеих моделях.
• Сначала переключайте пути чтения: отчёты, фильтры и иерархические проверки прав должны читать из closure до изменения логики записи.
• Поддерживайте closure при каждой записи (смена родителя, приём, перемещение команды). Когда всё стабильно — откажитесь от рекурсивных запросов.

При проверке сосредоточьтесь на случаях, которые чаще всего ломают доступ: смены менеджера, топ‑уровневые лиды и пользователи без менеджера.

Если вы используете AppMaster, можно оставить старые endpoints работающими, параллельно добавить новые, читающие closure, и переключиться, когда результаты совпадут.

Частые ошибки, ломаюшие фильтрацию и права

Самый быстрый путь испортить оргфункции — допустить несогласованность иерархии. Данные могут выглядеть корректно построчно, но мелкие ошибки приведут к неверным фильтрам, медленным страницам или утечке прав.

Классическая проблема — случайное создание цикла: A управляет B, затем кто‑то ставит B менеджером A (или более длинный цикл через 3–4 человека). Рекурсивные запросы могут выполняться вечно, возвращать дубликаты или таймить‑аутиться. Даже с closure‑таблицей циклы могут «отравить» пары предок/потомок.

Ещё одна распространённая ошибка — дрейф closure: вы меняете менеджера, но обновляете только прямую связь и забываете перестроить строки closure для поддерева. Тогда фильтры «все под VP» вернут смесь старой и новой структуры. Это трудно заметить, потому что страницы профиля отдельных сотрудников могут выглядеть корректно.

Оргструктуры также путаются, когда отделы и линии подчинения смешаны без чётких правил. Отдел часто — административная группировка, а линии подчинения — про менеджмент. Если вы смешаете их в одном дереве, можно получить странное поведение: «перемещение отдела» неожиданно меняет доступ.

Права чаще всего ломаются, когда проверки смотрят только на прямого менеджера. Если вы даёте доступ по условию viewer is manager of employee, вы пропускаете полную цепочку. Результат — либо избыточная блокировка (пропускают skip‑level менеджеров), либо избыточное раскрытие (временный прямой менеджер даёт доступ).

Медленные страницы списков часто возникают от рекурсивной фильтрации на каждом запросе (входящие, списки тикетов, поиск сотрудников). Если один и тот же фильтр используется везде, лучше предвычислять пути (closure) или кэшировать набор допустимых employee_id.

Практические меры защиты:

• Блокируйте циклы валидацией перед сохранением изменений менеджера.
• Разделяйте понятие «отдел» и «линия подчинения».
• При использовании closure‑таблицы перестраивайте строки потомков после смен менеджера.
• Пишите правила прав для полной цепочки, а не только для прямого менеджера.
• Предвычисляйте области видимости для списков вместо пересчёта рекурсии на каждый запрос.

Если вы делаете админ‑панель в AppMaster, рассматривайте «сменить менеджера» как чувствительный сценарий: валидируйте, обновляйте связанные данные и только после этого допускайте влияние на фильтры и доступ.

Быстрые проверки перед релизом

Прежде чем считать оргструктуру «готовой», убедитесь, что вы можете объяснить доступ простыми словами. Если кто‑то спросит: «Кто может видеть сотрудника X и почему?», вы должны показать одно правило и один запрос (или view), который это доказывает.

Далее — реальность производительности. С adjacency list запрос «покажи всех под этим менеджером» — рекурсивный, и его скорость зависит от глубины и индексов. С closure‑таблицей чтение обычно быстрое, но вы должны доверять пути записи, который поддерживает таблицу верной после каждого изменения.

Короткий чек‑лист перед релизом:

• Возьмите одного сотрудника и проследите видимость от начала до конца: какая цепочка даёт доступ и какая роль его запрещает.
• Проверьте скорость запроса поддерева менеджера на ожидаемых данных (например, 5 уровней глубины и 50 000 сотрудников).
• Блокируйте плохие записи: предотвращайте циклы, самоподчинение и осиротевшие узлы с помощью ограничений и транзакционных проверок.
• Тестируйте безопасность реорганизаций: перемещения, слияния, смены менеджера и откат, если что‑то пошло не так.
• Напишите тесты прав, которые проверяют как разрешённый, так и запрещённый доступ для реалистичных ролей (HR, менеджер, тим‑лид, support).

Практический сценарий для проверки: агент поддержки видит только сотрудников своего отдела, а менеджер видит всё своё поддерево. Если вы можете смоделировать оргструктуру в PostgreSQL и доказать оба правила тестами — вы близки к релизу.

Если вы строите это как внутренний инструмент в AppMaster, держите эти проверки как автоматические тесты вокруг endpoints, возвращающих списки организованных данных и профили сотрудников, а не только на уровне запросов к базе.

Пример сценария и следующие шаги

Представьте компанию с тремя отделами: Sales, Support и Engineering. В каждом отделе по две команды, у каждой команды — лидер. Sales Lead A может одобрять скидки для своей команды, Support Lead B видит все тикеты отдела, а VP Engineering видит всё в Engineering.

Затем происходит реорганизация: одна команда Support переходит в Sales, и между директором Sales и двумя лидерами команд появляется новый менеджер. На следующий день кто‑то просит доступ: «Пусть Джейми (аналитик в Sales) видит все аккаунты клиентов отдела Sales, но не Engineering.»

Если вы моделируете оргструктуру в PostgreSQL с adjacency list, схема проста, но нагрузка по работе смещается в запросы и проверки прав. Фильтры вроде «все в Sales» обычно требуют рекурсии. Как только вы добавляете утверждения («только менеджеры в цепочке могут утверждать»), краевые случаи после реорганизаций начинают проявляться.

С closure‑таблицей реорганизации требуют больше работы записи (обновление строк предков/потомков), но чтение становится прямолинейным. Фильтрация и права часто сводятся к простым JOIN: «является ли этот пользователь предком этого сотрудника?» или «входит ли эта команда в поддерево отдела?».

Это напрямую отражается в интерфейсах: селекторы людей с фильтром по отделу, маршрутизация утверждений к ближайшему менеджеру над запросчиком, административные представления для дашбордов отдела и аудиты, объясняющие, почему доступ существовал в конкретную дату.

Следующие шаги:

1. Опишите правила доступа простым языком (кто что видит и почему).
2. Выберите модель, которая соответствует самым частым проверкам (быстрые чтения vs простые записи).
3. Постройте внутреннюю админку для тестирования реорганизаций, запросов доступа и процессов утверждения от начала до конца.

Если вы хотите быстро собрать такие админ‑панели и порталы, AppMaster (appmaster.io) может подойти: он позволяет моделировать данные, хранимые в PostgreSQL, реализовывать логику утверждений в визуальном Business Process и выдавать веб‑ и нативные мобильные приложения с единого бэкенда.