Modelar organigramas en PostgreSQL: listas de adyacencia vs tabla de cierre

Lo que necesita soportar un organigrama

Un organigrama es un mapa de quién reporta a quién y cómo los equipos se integran en los departamentos. Al modelar organigramas en PostgreSQL, no basta con guardar un manager_id en cada persona. Estás soportando trabajo real: navegación por la org, informes y reglas de acceso.

La mayoría de los usuarios esperan tres cosas para sentir que todo es instantáneo: explorar la org, encontrar personas y filtrar resultados a "mi área". También esperan que las actualizaciones sean seguras. Cuando cambia un manager, el gráfico debe actualizarse en todas partes sin romper informes ni permisos.

En la práctica, un buen modelo necesita responder algunas preguntas recurrentes:

• ¿Cuál es la cadena de mando de esta persona (hasta la cima)?
• ¿Quién está bajo este manager (reportes directos y todo el subárbol)?
• ¿Cómo se agrupan las personas en equipos y departamentos para los dashboards?
• ¿Cómo ocurren las reorganizaciones sin fallos?
• ¿Quién puede ver qué, basado en la estructura organizacional?

Es más complicado que un simple árbol porque las organizaciones cambian con frecuencia. Los equipos se mueven entre departamentos, los managers intercambian grupos y algunas vistas no son puramente "personas que reportan a personas". Por ejemplo: una persona pertenece a un equipo, y los equipos pertenecen a departamentos. Los permisos añaden otra capa: la forma de la org se convierte en parte de tu modelo de seguridad, no solo en un diagrama.

Algunos términos ayudan a mantener los diseños claros:

• Un nodo es un elemento en la jerarquía (una persona, un equipo o un departamento).
• Un padre es el nodo directamente por encima (un manager, o un departamento que posee un equipo).
• Un ancestro es cualquier nodo por encima a cualquier distancia (el manager de tu manager).
• Un descendiente es cualquier nodo por debajo a cualquier distancia (todos los que están bajo ti).

Ejemplo: si Ventas se mueve bajo un nuevo VP, dos cosas deben ser ciertas de inmediato. Los dashboards aún filtran "todo Ventas" y los permisos del nuevo VP cubren Ventas automáticamente.

Decisiones que tomar antes de elegir un diseño de tabla

Antes de decidir un esquema, ten claro qué debe responder tu app cada día. "¿Quién reporta a quién?" es solo el comienzo. Muchos organigramas también necesitan mostrar quién lidera un departamento, quién aprueba tiempo libre para un equipo y quién puede ver un informe.

Anota las preguntas exactas que pedirán tus pantallas y comprobaciones de permisos. Si no puedes nombrarlas, acabarás con un esquema que parece correcto pero difícil de consultar.

Las decisiones que lo definen todo:

• ¿Qué consultas deben ser rápidas: manager directo, cadena al CEO, subárbol completo bajo un líder o "todos en este departamento"?
• ¿Es un árbol estricto (un solo manager) o una org matriz (más de un manager o líder)?
• ¿Son los departamentos nodos en la misma jerarquía que las personas, o un atributo separado (como department_id en cada persona)?
• ¿Puede alguien pertenecer a múltiples equipos (servicios compartidos, squads)?
• ¿Cómo fluyen los permisos: hacia abajo por el árbol, hacia arriba, o ambos?

Esas elecciones definen cómo se ve la información “correcta”. Si Alex lidera tanto Soporte como Onboarding, un único manager_id o la regla de "un líder por equipo" podría no funcionar. Podrías necesitar una tabla de unión (líder a equipo) o una política clara como "un equipo primario más equipos de línea discontinua".

Los departamentos son otro punto de bifurcación. Si los departamentos son nodos, puedes expresar "Departamento A contiene Equipo B que contiene Persona C". Si los departamentos son separados, filtrarás con department_id = X, que es más simple pero puede romperse cuando los equipos abarcan departamentos.

Finalmente, define los permisos en lenguaje llano. "Un manager puede ver salario de todos bajo él, pero no pares" es una regla hacia abajo del árbol. "Cualquiera puede ver su cadena de mando" es una regla hacia arriba. Decide esto temprano porque cambia qué modelo de jerarquía se sentirá natural y cuál forzará consultas costosas después.

Lista de adyacencia: un esquema simple para managers y equipos

Si quieres la menor cantidad de piezas móviles, una lista de adyacencia es el punto de partida clásico. Cada persona almacena un puntero a su manager directo y el árbol se crea siguiendo esos punteros.

Un setup mínimo se ve así:

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

También puedes omitir las tablas separadas y mantener department_name y team_name como columnas en employees. Eso es más rápido para empezar, pero más difícil de mantener limpio (errores tipográficos, equipos renombrados e informes inconsistentes). Las tablas separadas facilitan expresar filtros y reglas de permisos de forma consistente.

Añade protecciones desde el inicio. Los datos jerárquicos malos son dolorosos de arreglar más tarde. Como mínimo, impide la autogestión (manager_id \u003c\u003e id). Decide también si un manager puede estar fuera del mismo equipo o departamento, y si necesitas borrados suaves o cambios históricos (para auditoría de líneas de reporte).

Con listas de adyacencia, la mayoría de cambios son escrituras simples: cambiar un manager actualiza employees.manager_id, y mover equipos actualiza employees.team_id (a menudo junto con el manager). El problema es que una pequeña escritura puede tener grandes efectos downstream. Los acumulados de reportes cambian, y cualquier regla de "el manager puede ver todos los reportes" ahora debe seguir la nueva cadena.

Esa simplicidad es la mayor fortaleza de la lista de adyacencia. Su debilidad aparece cuando filtras con frecuencia por "todos bajo este manager", porque normalmente dependes de consultas recursivas para recorrer el árbol cada vez.

Lista de adyacencia: consultas comunes para filtrado e informes

Con una lista de adyacencia, muchas preguntas útiles del organigrama se convierten en consultas recursivas. Si modelas organigramas en PostgreSQL de esta forma, estos son los patrones que usarás constantemente.

Reportes directos (un nivel)

El caso más simple es el equipo inmediato de un manager:

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

Esto es rápido y legible, pero solo baja un nivel.

Cadena de mando (hacia arriba)

Para mostrar a quién reporta alguien (manager, manager del manager, etc.), usa un CTE recursivo:

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;

Esto soporta aprobaciones, rutas de escalación y breadcrumbs de managers.

Subárbol completo (hacia abajo)

Para obtener a todos bajo un líder (todos los niveles), invierte la recursión:

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;

Un informe común es "todos en el departamento X bajo el líder 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;

Las consultas con listas de adyacencia pueden ser riesgosas para permisos porque las comprobaciones de acceso a menudo dependen de la ruta completa (¿es el visualizador un ancestro de esta persona?). Si un endpoint olvida la recursión o aplica filtros en el lugar equivocado, puedes filtrar filas accidentalmente. También vigila problemas de datos como ciclos y managers faltantes. Un mal registro puede romper la recursión o devolver resultados sorprendentes, así que las consultas de permisos necesitan salvaguardas y buenas restricciones.

Tabla de cierre: cómo almacena toda la jerarquía

Una tabla de cierre almacena cada relación ancestro-descendiente, no solo el enlace directo al manager. En lugar de recorrer el árbol un paso a la vez, puedes preguntar: "¿Quién está bajo este líder?" y obtener la respuesta completa con un join simple.

Normalmente mantienes dos tablas: una para nodos (personas o equipos) y otra para rutas de jerarquía.

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

La tabla de cierre almacena pares como (Alice, Bob) que significan "Alice es un ancestro de Bob". También almacena una fila donde ancestor_id = descendant_id con depth = 0. Esa fila reflexiva puede parecer extraña al principio, pero hace muchas consultas más limpias.

depth indica cuán separados están dos nodos: depth = 1 es manager directo, depth = 2 es el manager del manager, y así sucesivamente. Esto importa cuando los reportes directos deben tratarse diferente de los indirectos.

El principal beneficio son lecturas previsibles y rápidas:

• Las búsquedas de subárbol completo son rápidas (todos los bajo un director).
• Las cadenas de mando son simples (todos los managers por encima de alguien).
• Puedes separar relaciones directas e indirectas usando depth.

El coste es el mantenimiento en las actualizaciones. Si Bob cambia de manager de Alice a Dana, debes reconstruir las filas de cierre para Bob y todos los que estén bajo Bob. El enfoque típico es: eliminar las rutas antiguas de ancestros para ese subárbol y luego insertar nuevas rutas combinando los ancestros de Dana con cada nodo del subárbol de Bob y recalculando la profundidad.

Tabla de cierre: consultas comunes para filtrado rápido

Una tabla de cierre almacena cada par ancestro-descendiente por adelantado (a menudo como org_closure(ancestor_id, descendant_id, depth)). Eso hace que los filtros de org sean rápidos porque la mayoría de preguntas se resuelven con un único join.

Para listar a todos bajo un manager, haz un join y filtra por 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 \u003e 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;

Para la cadena de mando (todos los ancestros de un empleado), invierte el 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 \u003e 0
ORDER BY c.depth;

El filtrado se vuelve predecible. Ejemplo: "todas las personas bajo el líder X, pero solo en el departamento 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;

Debido a que la jerarquía está precomputada, los conteos son directos también (sin recursión). Esto ayuda a dashboards y totales acotados por permisos, y funciona bien con paginación y búsqueda porque puedes aplicar ORDER BY, LIMIT/OFFSET y filtros directamente sobre el conjunto de descendientes.

Cómo cada modelo afecta permisos y comprobaciones de acceso

Una regla común es simple: un manager puede ver (y a veces editar) todo lo que está bajo él. El esquema que elijas cambia con qué frecuencia pagas el coste de averiguar "quién está bajo quién".

Con una lista de adyacencia, la comprobación de permisos suele necesitar recursión. Si un usuario abre una página que lista 200 empleados, normalmente construyes el conjunto de descendientes con un CTE recursivo y filtras las filas objetivo contra él.

Con una tabla de cierre, la misma regla a menudo puede comprobarse con una simple prueba de existencia: "¿Es el usuario actual un ancestro de este empleado?" Si sí, permitir.

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

Esa simplicidad importa cuando introduces row-level security (RLS), donde cada consulta incluye automáticamente una regla como "solo devuelve filas que el visualizador puede ver". Con listas de adyacencia, la política a menudo incrusta recursión y puede ser más difícil de ajustar. Con una tabla de cierre, la política suele ser un EXISTS (...) directo.

Los casos límite son donde la lógica de permisos más se rompe:

• Reportes en línea discontinua: una persona tiene efectivamente dos managers.
• Asistentes y delegados: el acceso no siempre se basa en la jerarquía, así que guarda concesiones explícitas (a menudo con expiración).
• Acceso temporal: permisos con límite de tiempo no deben hornearse en la estructura org.
• Proyectos entre equipos: concede acceso por membresía de proyecto, no por la cadena de mando.

Si construyes esto en AppMaster, una tabla de cierre a menudo mapea bien a un modelo de datos visual y mantiene la comprobación de acceso simple en web y aplicaciones móviles.

Compensaciones: velocidad, complejidad y mantenimiento

La mayor elección es qué optimizas: escrituras simples y un esquema pequeño, o lecturas rápidas para "quién está bajo este manager" y comprobaciones de permisos.

Las listas de adyacencia mantienen la tabla pequeña y las actualizaciones fáciles. El coste aparece en las lecturas: un subárbol completo normalmente significa recursión. Eso puede estar bien si tu org es pequeña, tu UI solo carga unos pocos niveles o los filtros basados en la jerarquía se usan en pocos lugares.

Las tablas de cierre invierten la compensación. Las lecturas se vuelven rápidas porque puedes responder "todos los descendientes" con joins regulares. Las escrituras se vuelven más complejas porque un movimiento o reorg puede requerir insertar y borrar muchas filas de relaciones.

En el trabajo real, la compensación suele lucir así:

• Rendimiento de lectura: la adyacencia necesita recursión; la tabla de cierre son mayormente joins y se mantiene rápida a medida que la organización crece.
• Complejidad de escritura: la adyacencia actualiza un solo parent_id; la tabla de cierre actualiza muchas filas por un solo movimiento.
• Tamaño de datos: la adyacencia crece con personas/equipos; la tabla de cierre crece con relaciones (en el peor caso, aproximadamente N al cuadrado para un árbol profundo).

El indexado importa en ambos modelos, pero el objetivo difiere:

• Lista de adyacencia: indexa el puntero al padre (manager_id), además de filtros comunes como una bandera de "activo".
• Tabla de cierre: indexa (ancestor_id, descendant_id) y también descendant_id por separado para búsquedas comunes.

Una regla simple: si rara vez filtras por jerarquía y las comprobaciones de permisos son solo "el manager ve reportes directos", una lista de adyacencia suele ser suficiente. Si ejecutas con regularidad informes "todos bajo el VP X", filtras por árboles de departamento o aplicas permisos jerárquicos en muchas pantallas, las tablas de cierre suelen compensar el mantenimiento extra.

Paso a paso: pasar de lista de adyacencia a tabla de cierre

No tienes que elegir un modelo el primer día. Un camino seguro es mantener tu lista de adyacencia (manager_id o parent_id) y añadir una tabla de cierre junto a ella, luego migrar lecturas con el tiempo. Esto reduce el riesgo mientras validas cómo se comporta la nueva jerarquía en consultas reales y comprobaciones de permisos.

Comienza creando una tabla de cierre (a menudo llamada org_closure) con columnas como ancestor_id, descendant_id y depth. Manténla separada de employees o teams para poder rellenarla y validar sin tocar las funciones actuales.

Un despliegue práctico:

• Crea la tabla de cierre y los índices mientras mantienes la lista de adyacencia como fuente de la verdad.
• Rellena las filas de cierre a partir de las relaciones de managers actuales, incluyendo la fila reflexiva (cada nodo es su propio ancestro con depth 0).
• Valida con comprobaciones puntuales: elige algunos managers y confirma que el conjunto de subordinados coincide en ambos modelos.
• Cambia primero las rutas de lectura: informes, filtros y permisos jerárquicos deben leer de la tabla de cierre antes de que cambies las escrituras.
• Mantén la tabla de cierre actualizada en cada escritura (re-parent, contratación, movimiento de equipo). Una vez estable, retira las consultas basadas en recursión.

Al validar, enfócate en los casos que suelen romper reglas de acceso: cambios de manager, líderes de nivel superior y usuarios sin manager.

Si construyes esto en AppMaster, puedes mantener los endpoints antiguos funcionando mientras añades nuevos que lean desde la tabla de cierre, y luego cambiar cuando los resultados coincidan.

Errores comunes que rompen filtros u permisos de org

La forma más rápida de romper funciones organizacionales es permitir que la jerarquía se vuelva inconsistente. Los datos pueden parecer correctos fila por fila, pero pequeños errores pueden causar filtros equivocados, páginas lentas o fugas de permisos.

Un problema clásico es crear accidentalmente un ciclo: A gestiona a B y luego alguien pone a B como manager de A (o un bucle más largo entre 3-4 personas). Las consultas recursivas pueden ejecutarse indefinidamente, devolver filas duplicadas o agotar el tiempo. Incluso con una tabla de cierre, los ciclos pueden envenenar filas de ancestro/descendiente.

Otro problema común es la deriva de la tabla de cierre: cambias el manager de alguien, pero solo actualizas la relación directa y olvidas reconstruir las filas de cierre del subárbol. Entonces filtros como "todos bajo este VP" devuelven una mezcla de la estructura antigua y la nueva. Es difícil de detectar porque las páginas de perfil individuales siguen pareciendo correctas.

Los organigramas también se vuelven enmarañados cuando departamentos y líneas de reporte se mezclan sin reglas claras. Un departamento suele ser una agrupación administrativa, mientras que las líneas de reporte tratan sobre managers. Si los tratas como el mismo árbol, puedes acabar con comportamientos extraños como que un "movimiento de departamento" cambie inesperadamente el acceso.

Los permisos fallan con más frecuencia cuando las comprobaciones solo miran al manager directo. Si permites acceso cuando viewer is manager of employee, te pierdes la cadena completa. El resultado es bloquear en exceso (managers a niveles superiores no pueden ver su org) o compartir en exceso (alguien gana acceso al ser asignado temporalmente como manager directo).

Las páginas de listas lentas suelen venir de ejecutar filtrado recursivo en cada petición (cada inbox, cada lista de tickets, cada búsqueda de empleados). Si el mismo filtro se usa en todas partes, quieres una ruta precomputada (tabla de cierre) o un conjunto cacheado de IDs de empleados permitidos.

Algunas salvaguardas prácticas:

• Bloquea ciclos con validación antes de guardar cambios de manager.
• Decide qué significa "departamento" y mantenlo separado de las líneas de reporte.
• Si usas una tabla de cierre, reconstruye las filas de descendientes en cambios de manager.
• Escribe reglas de permisos para la cadena completa, no solo para el manager directo.
• Precalcula ámbitos organizacionales usados por páginas de listas en vez de recalcular la recursión cada vez.

Si construyes paneles de administración en AppMaster, trata "cambiar manager" como un flujo sensible: valídalo, actualiza datos jerárquicos relacionados y solo entonces permite que afecte filtros y accesos.

Comprobaciones rápidas antes del lanzamiento

Antes de declarar tu organigrama como "listo", asegúrate de poder explicar el acceso en palabras sencillas. Si alguien pregunta, "¿Quién puede ver al empleado X y por qué?", deberías poder apuntar a una regla y una consulta (o vista) que lo pruebe.

El rendimiento es la siguiente comprobación de realidad. Con una lista de adyacencia, "muéstrame todos bajo este manager" se convierte en una consulta recursiva cuya velocidad depende de la profundidad y el indexado. Con una tabla de cierre, las lecturas suelen ser rápidas, pero debes confiar en tu ruta de escritura para mantener la tabla correcta tras cada cambio.

Una breve lista antes de lanzar:

• Elige un empleado y traza la visibilidad de extremo a extremo: qué cadena concede acceso y qué rol lo niega.
• Mide una consulta de subárbol de manager usando el tamaño esperado (por ejemplo, 5 niveles y 50,000 empleados).
• Bloquea escrituras malas: evita ciclos, autogestión y nodos huérfanos con restricciones y comprobaciones en transacciones.
• Prueba la seguridad de reorganizaciones: movimientos, fusiones, cambios de manager y rollback cuando algo falle a mitad de camino.
• Añade pruebas de permisos que afirmen tanto acceso permitido como denegado para roles realistas (HR, manager, team lead, soporte).

Un escenario práctico para validar: un agente de soporte solo puede ver empleados en su departamento asignado, mientras que un manager puede ver su subárbol completo. Si puedes modelar organigramas en PostgreSQL y demostrar ambas reglas con tests, estás cerca de lanzar.

Si lo construyes como una herramienta interna en AppMaster, mantén estas comprobaciones como tests automatizados alrededor de los endpoints que devuelven listas organizacionales y perfiles de empleados, no solo consultas a la base de datos.

Escenario de ejemplo y próximos pasos

Imagina una empresa con tres departamentos: Ventas, Soporte e Ingeniería. Cada departamento tiene dos equipos, y cada equipo tiene un líder. El líder de Ventas A puede aprobar descuentos para su equipo, el líder de Soporte B puede ver todos los tickets de su departamento y el VP de Ingeniería puede ver todo lo que está bajo Ingeniería.

Luego ocurre una reorganización: un equipo de Soporte se mueve bajo Ventas y se añade un nuevo manager entre el Director de Ventas y dos líderes de equipo. Al día siguiente, alguien solicita acceso: "Que Jamie (un analista de Ventas) pueda ver todas las cuentas de clientes del departamento de Ventas, pero no Ingeniería."

Si modelas organigramas en PostgreSQL con una lista de adyacencia, el esquema es simple, pero el trabajo de la app se desplaza a tus consultas y comprobaciones de permisos. Filtros como "todos bajo Ventas" suelen necesitar recursión. Una vez que añades aprobaciones (como "solo managers en la cadena pueden aprobar"), los casos límite después de una reorganización empiezan a importar.

Con una tabla de cierre, las reorganizaciones significan más trabajo de escritura (actualizar filas de ancestro/descendiente), pero el lado de lectura se vuelve directo. Los filtros y permisos a menudo se convierten en joins simples: "¿es este usuario un ancestro de ese empleado?" o "¿está este equipo dentro del subárbol de este departamento?".

Esto se refleja directamente en las pantallas que la gente construye: selectores de personas acotados a un departamento, enrutamiento de aprobaciones al manager más cercano por encima de un solicitante, vistas administrativas para dashboards de departamento y auditorías que expliquen por qué existía un acceso en una fecha dada.

Próximos pasos:

1. Escribe las reglas de permisos en lenguaje natural (quién puede ver qué y por qué).
2. Elige un modelo que coincida con las comprobaciones más comunes (lecturas rápidas vs escrituras simples).
3. Construye una herramienta administrativa interna que te permita probar reorganizaciones, solicitudes de acceso y aprobaciones de extremo a extremo.

Si quieres construir esos paneles y portales conscientes de la org rápidamente, AppMaster (appmaster.io) puede ser una opción práctica: te permite modelar datos respaldados por PostgreSQL, implementar lógica de aprobaciones en un Business Process visual y entregar apps web y móviles nativas desde el mismo backend.