Modellare gli organigrammi in PostgreSQL: liste di adiacenza vs tabella di closure

Cosa deve supportare un organigramma

Un organigramma è una mappa di chi riporta a chi e di come i team si aggregano nei dipartimenti. Quando modelli gli organigrammi in PostgreSQL, non stai solo salvando un manager_id su ogni persona. Stai supportando lavoro reale: esplorazione dell’organizzazione, reportistica e regole di accesso.

La maggior parte degli utenti si aspetta tre cose per sentirla reattiva: esplorare l’organigramma, trovare persone e filtrare i risultati su “la mia area”. Si aspettano anche che gli aggiornamenti siano sicuri. Quando un manager cambia, il grafico dovrebbe aggiornarsi ovunque senza rompere report o permessi.

Nella pratica, un buon modello deve rispondere a qualche domanda ricorrente:

• Qual è la catena di comando di questa persona (fino all’alto)?
• Chi dipende da questo manager (report diretti e intero sottoalbero)?
• Come si raggruppano le persone in team e dipartimenti per le dashboard?
• Come avvengono le riorganizzazioni senza intoppi?
• Chi può vedere cosa, basandosi sulla struttura organizzativa?

Diventa più complesso di un semplice albero perché le organizzazioni cambiano spesso. I team si spostano tra dipartimenti, i manager si scambiano i gruppi e alcune viste non sono puramente “persone che riportano a persone”. Per esempio: una persona appartiene a un team e i team appartengono a dipartimenti. I permessi aggiungono un altro livello: la forma dell’organizzazione diventa parte del tuo modello di sicurezza, non solo un diagramma.

Alcuni termini aiutano a mantenere chiari i progetti:

• Un nodo è un elemento della gerarchia (una persona, un team o un dipartimento).
• Un parent è il nodo direttamente sopra (un manager, o il dipartimento che possiede un team).
• Un ancestor è qualsiasi nodo sopra ad una qualsiasi distanza (il manager del tuo manager).
• Un descendant è qualsiasi nodo sotto a qualsiasi distanza (tutti quelli sotto di te).

Esempio: se Sales si sposta sotto un nuovo VP, due cose dovrebbero rimanere vere immediatamente. Le dashboard continuano a filtrare “tutto Sales” e i permessi del nuovo VP coprono automaticamente Sales.

Decisioni da prendere prima di scegliere il design delle tabelle

Prima di stabilire uno schema, sii chiaro su cosa la tua app deve rispondere ogni giorno. “Chi riporta a chi?” è solo l’inizio. Molti organigrammi devono anche mostrare chi guida un dipartimento, chi approva le ferie per un team e chi può vedere un report.

Annota le domande esatte che le tue schermate e i tuoi controlli di permessi faranno. Se non riesci a nominare le domande, finirai con uno schema che sembra giusto ma è difficile da interrogare.

Le decisioni che determinano tutto:

• Quali query devono essere veloci: manager diretto, catena fino al CEO, sottoalbero completo sotto un leader o “tutti in questo dipartimento”?
• È un albero rigido (un manager) o una org a matrice (più di un manager o lead)?
• I dipartimenti sono nodi nella stessa gerarchia delle persone, o un attributo separato (come department_id su ogni persona)?
• Qualcuno può appartenere a più team (shared services, squad)?
• Come scorrono i permessi: giù per l’albero, su per l’albero, o entrambi?

Quelle scelte definiscono cosa significa avere dati “corretti”. Se Alex guida sia Support che Onboarding, un singolo manager_id o la regola “un lead per team” potrebbero non funzionare. Potresti aver bisogno di una tabella di join (leader-to-team) o di una policy chiara come “un team primario, più team in linea tratteggiata”.

I dipartimenti sono un altro bivio. Se i dipartimenti sono nodi, puoi esprimere “Dipartimento A contiene Team B che contiene Persona C”. Se i dipartimenti sono separati, filtrerai con department_id = X, che è più semplice ma può incepparsi quando i team attraversano i dipartimenti.

Infine, definisci i permessi in linguaggio semplice. “Un manager può vedere lo stipendio di tutti sotto di lui, ma non dei pari” è una regola verso il basso. “Chiunque può vedere la propria catena di management” è una regola verso l’alto. Decidi questo presto perché cambia quale modello di gerarchia risulterà naturale e quale costringerà a query costose in seguito.

Adjacency list: uno schema semplice per manager e team

Se vuoi il minor numero di parti in movimento, una adjacency list è il classico punto di partenza. Ogni persona conserva un puntatore al proprio manager diretto e l’albero si costruisce seguendo quei puntatori.

Una configurazione minima è così:

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

Puoi anche saltare le tabelle separate e mantenere department_name e team_name come colonne su employees. È più veloce per partire, ma più difficile da mantenere pulito (refusi, rinomina dei team e report incoerenti). Tabelle separate rendono i filtri e le regole di permesso più facili da esprimere in modo consistente.

Aggiungi dei vincoli precocemente. Dati gerarchici cattivi sono difficili da correggere più avanti. Al minimo, impedisci l’auto-gestione (manager_id <> id). Decidi anche se un manager può essere fuori dallo stesso team o dipartimento e se hai bisogno di soft delete o cambi storici (per audit delle linee di reporting).

Con le adjacency list, la maggior parte delle modifiche sono semplici scritture: cambiare un manager aggiorna employees.manager_id e spostare team aggiorna employees.team_id (spesso insieme al manager). La complicazione è che una piccola scrittura può avere grandi effetti a valle. I rollup dei report cambiano e qualsiasi regola “il manager può vedere tutti i report” deve ora seguire la nuova catena.

Questa semplicità è il maggiore punto di forza della adjacency list. La debolezza emerge quando filtri frequentemente per “tutti sotto questo manager”, perché di solito fai affidamento su query ricorsive per attraversare l’albero ogni volta.

Adjacency list: query comuni per filtraggio e reportistica

Con una adjacency list molte domande utili sull’organigramma diventano query ricorsive. Se modelli gli organigrammi in PostgreSQL in questo modo, questi sono i modelli che userai costantemente.

Report diretti (un livello)

Il caso più semplice è il team immediato di un manager:

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

Questo è veloce e leggibile, ma scende solo di un livello.

Catena di comando (verso l’alto)

Per mostrare a chi qualcuno riporta (manager, manager del manager e così via), usa una CTE ricorsiva:

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;

Questo supporta approvazioni, percorsi di escalation e breadcrumb del manager.

Sottoalbero completo (verso il basso)

Per ottenere tutti sotto un leader (a tutti i livelli), inverti la ricorsione:

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 report comune è “tutti in dipartimento X sotto il 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;

Le query su adjacency list possono diventare rischiose per i permessi perché i controlli di accesso spesso dipendono dal percorso completo (il viewer è un antenato di questa persona?). Se un endpoint dimentica la ricorsione o applica filtri nel posto sbagliato, puoi perdere righe. Attenzione anche ai problemi di dati come cicli e manager mancanti. Un record sbagliato può rompere la ricorsione o restituire risultati sorprendenti, quindi le query di permesso necessitano di salvaguardie e buoni vincoli.

Closure table: come memorizza l’intera gerarchia

Una closure table memorizza ogni relazione antenato-discendente, non solo il collegamento diretto al manager. Invece di camminare l’albero passo dopo passo, puoi chiedere: “Chi è sotto questo leader?” e ottenere la risposta completa con un semplice join.

Di solito mantieni due tabelle: una per i nodi (persone o team) e una per i percorsi della gerarchia.

-- 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 closure table memorizza coppie come (Alice, Bob) che significano “Alice è un antenato di Bob”. Memorizza anche una riga dove ancestor_id = descendant_id con depth = 0. Quella riga a se stessa può sembrare strana all’inizio, ma rende molte query più pulite.

depth indica quanto sono distanti due nodi: depth = 1 è un manager diretto, depth = 2 è il manager del manager, e così via. Questo è utile quando i report diretti devono essere trattati diversamente dagli indiretti.

Il vantaggio principale sono letture prevedibili e veloci:

• Lookup di sottoalberi interi sono rapidi (tutti sotto un direttore).
• Le catene di comando sono semplici (tutti i manager sopra qualcuno).
• Puoi separare relazioni dirette da indirette usando depth.

Il costo è la manutenzione sugli aggiornamenti. Se Bob cambia manager da Alice a Dana, devi ricostruire le righe di closure per Bob e per tutti sotto Bob. L’approccio tipico è: eliminare i vecchi percorsi di antenati per quel sottoalbero, poi inserire i nuovi percorsi combinando gli antenati di Dana con ogni nodo del sottoalbero di Bob e ricalcolando la profondità.

Closure table: query comuni per filtri veloci

Una closure table memorizza ogni coppia antenato-discendente in anticipo (spesso come org_closure(ancestor_id, descendant_id, depth)). Questo rende i filtri organizzativi veloci perché la maggior parte delle domande diventa un singolo join.

Per elencare tutti sotto un manager, fai un join una sola volta e filtra per 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;

Per la catena di comando (tutti gli antenati di un dipendente), inverti il 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;

Il filtraggio diventa prevedibile. Esempio: “tutte le persone sotto il leader X, ma solo nel dipartimento 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;

Poiché la gerarchia è precalcolata, anche i conteggi sono semplici (niente ricorsione). Questo aiuta dashboard e totali basati sui permessi, e funziona bene con paginazione e ricerca dato che puoi applicare ORDER BY, LIMIT/OFFSET e filtri direttamente sull’insieme dei discendenti.

Come ogni modello influenza permessi e controlli di accesso

Una regola comune è semplice: un manager può visualizzare (e talvolta modificare) tutto ciò che è sotto di lui. Lo schema scelto cambia quanto spesso paghi il costo di calcolare “chi è sotto chi”.

Con una adjacency list il controllo dei permessi di solito richiede ricorsione. Se un utente apre una pagina che elenca 200 dipendenti, normalmente costruisci l’insieme dei discendenti con una CTE ricorsiva e filtri le righe target rispetto a quell’insieme.

Con una closure table la stessa regola può spesso essere verificata con un semplice test di esistenza: “L’utente corrente è un antenato di questo dipendente?” Se sì, consenti.

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

Questa semplicità è importante quando introduci Row-Level Security (RLS), dove ogni query include automaticamente una regola come “restituisci solo le righe che il viewer può vedere”. Con le adjacency list la policy spesso incorpora ricorsione e può essere più difficile da ottimizzare. Con una closure table la policy è spesso un EXISTS (...) semplice.

I casi limite sono dove la logica dei permessi si rompe più spesso:

• Reporting a tratto puntinato: una persona ha effettivamente due manager.
• Assistenti e delegati: l’accesso non si basa sulla gerarchia, quindi conserva concessioni esplicite (spesso con una scadenza).
• Accesso temporaneo: i permessi legati al tempo non dovrebbero essere incorporati nella struttura org.
• Progetti cross-team: concedi accesso tramite appartenenza al progetto, non tramite catena di management.

Se stai costruendo questo in AppMaster, una closure table spesso mappa bene a un modello dati visuale e mantiene il controllo di accesso semplice e coerente tra web e mobile app.

Compromessi: velocità, complessità e manutenzione

La scelta principale è su cosa ottimizzare: scritture semplici e uno schema piccolo, oppure letture veloci per “chi è sotto questo manager” e controlli permessi.

Le adjacency list mantengono la tabella piccola e gli aggiornamenti facili. Il costo si manifesta sulle letture: un sottoalbero intero di solito significa ricorsione. Questo può andar bene se la tua org è piccola, l’interfaccia carica pochi livelli o i filtri basati sulla gerarchia sono usati in pochi punti.

Le closure table invertano il compromesso. Le letture diventano veloci perché puoi rispondere a “tutti i discendenti” con join regolari. Le scritture diventano più complesse perché una mossa o una riorganizzazione può richiedere di inserire ed eliminare molte righe di relazione.

Nel lavoro reale il compromesso spesso appare così:

• Prestazioni di lettura: adjacency richiede ricorsione; closure è per lo più join e resta veloce con la crescita.
• Complessità delle scritture: adjacency aggiorna un solo parent_id; closure aggiorna molte righe per una singola mossa.
• Dimensione dei dati: adjacency cresce con persone/team; closure cresce con le relazioni (nel peggior caso, approssimativamente N al quadrato per un albero profondo).

L’indicizzazione conta in entrambi i modelli, ma il bersaglio cambia:

• Adjacency list: indicizza il puntatore al parent (manager_id), più filtri comuni come un flag “active”.
• Closure table: indicizza (ancestor_id, descendant_id) e anche descendant_id singolarmente per lookup comuni.

Una regola semplice: se raramente filtri per gerarchia e i controlli permessi sono solo “il manager vede report diretti”, una adjacency list spesso basta. Se esegui regolarmente report “tutti sotto il VP X”, filtri per alberi di dipartimento o applichi permessi gerarchici su molte schermate, le closure table spesso ripagano la manutenzione extra.

Passo dopo passo: passare da adjacency list a closure table

Non devi scegliere tra i modelli il primo giorno. Una strada sicura è mantenere la adjacency list (manager_id o parent_id) e aggiungere una closure table accanto, poi migrare le letture gradualmente. Questo riduce il rischio mentre validi come il nuovo albero si comporta nelle query reali e nei controlli permessi.

Inizia creando una closure table (spesso chiamata org_closure) con colonne come ancestor_id, descendant_id e depth. Tienila separata dalla tua tabella employees o teams così puoi popolarla e validarla senza toccare le funzionalità correnti.

Un rollout pratico:

• Crea la closure table e gli indici mantenendo la adjacency list come fonte di verità.
• Backfilla le righe di closure dalle relazioni manager correnti, includendo la riga a se stante (ogni nodo è suo proprio antenato con depth 0).
• Valida con controlli puntuali: scegli alcuni manager e conferma che lo stesso insieme di subordinati appare in entrambi i modelli.
• Sposta prima i percorsi di lettura: report, filtri e permessi gerarchici leggono dalla closure table prima di cambiare le scritture.
• Mantieni la closure aggiornata a ogni scrittura (re-parent, assunzione, spostamento team). Quando stabile, dismetti le query basate sulla ricorsione.

Quando validi, concentrati sui casi che solitamente rompono i permessi: cambi di manager, leader di primo livello e utenti senza manager.

Se stai costruendo in AppMaster, puoi tenere gli endpoint vecchi attivi mentre aggiungi nuovi endpoint che leggono dalla closure table, poi commutare quando i risultati coincidono.

Errori comuni che rompono filtri o permessi

Il modo più veloce per rompere funzionalità org è lasciare che la gerarchia diventi incoerente. I dati possono sembrare corretti riga per riga, ma piccoli errori possono causare filtri sbagliati, pagine lente o una perdita di dati per i permessi.

Un problema classico è creare accidentalmente un ciclo: A gestisce B, e più tardi qualcuno imposta B che gestisce A (o un ciclo più lungo con 3–4 persone). Le query ricorsive possono girare all’infinito, restituire righe duplicate o andare in timeout. Anche con una closure table, i cicli possono contaminare le righe antenato/descendente.

Un altro problema comune è la deriva della closure: cambi il manager di qualcuno ma aggiorni solo la relazione diretta e dimentichi di ricostruire le righe di closure per il sottoalbero. Allora i filtri come “tutti sotto questo VP” restituiscono un mix della struttura vecchia e nuova. È difficile da individuare perché le pagine profilo individuali possono sembrare corrette.

Gli organigrammi si incasinano anche quando dipartimenti e linee di reporting sono mescolati senza regole chiare. Un dipartimento è spesso un raggruppamento amministrativo, mentre le linee di reporting riguardano i manager. Se li tratti come lo stesso albero, puoi avere comportamenti strani come uno “spostamento di dipartimento” che cambia inaspettatamente gli accessi.

I permessi falliscono più spesso quando i controlli guardano solo il manager diretto. Se permetti l’accesso quando viewer is manager of employee, perdi la catena completa. Il risultato è o un eccesso di blocchi (i manager a livello superiore non vedono la loro org) o un’eccessiva condivisione (qualcuno ottiene accesso impostandosi come manager diretto temporaneo).

Le pagine di elenco lente spesso derivano dall’esecuzione di filtri ricorsivi ad ogni richiesta (ogni inbox, ogni lista ticket, ogni ricerca dipendenti). Se lo stesso filtro è usato ovunque, conviene precomputare i percorsi (closure table) o mettere in cache l’insieme di ID dipendenti consentiti.

Alcune salvaguardie pratiche:

• Blocca i cicli con validazione prima di salvare i cambi di manager.
• Decidi cosa significa “dipartimento” e mantienilo separato dal reporting.
• Se usi una closure table, ricostruisci le righe discendenti al cambiamento del manager.
• Scrivi regole di permesso per l’intera catena, non solo per il manager diretto.
• Precalcola gli scope organizzativi usati dalle liste invece di ricalcolare la ricorsione ogni volta.

Se costruisci pannelli amministrativi in AppMaster, tratta “cambia manager” come un flusso sensibile: convalidalo, aggiorna i dati gerarchici correlati e solo dopo permetti che influenzi filtri e accessi.

Controlli rapidi prima del rilascio

Prima di dichiarare l’organigramma “finito”, assicurati di poter spiegare l’accesso in parole semplici. Se qualcuno chiede “Chi può vedere il dipendente X, e perché?”, dovresti poter indicare una regola e una query (o view) che lo dimostrino.

La prestazione è il prossimo controllo di realtà. Con una adjacency list “mostrami tutti sotto questo manager” diventa una query ricorsiva la cui velocità dipende da profondità e indicizzazione. Con una closure table le letture sono di solito veloci, ma devi fidarti del percorso di scrittura per mantenere la tabella corretta dopo ogni cambio.

Una breve checklist pre-rilascio:

• Scegli un dipendente e traccia la visibilità end-to-end: quale catena concede accesso e quale ruolo lo nega.
• Fai benchmark di una query su sottoalbero del manager usando la dimensione prevista (per esempio 5 livelli e 50.000 dipendenti).
• Blocca scritture errate: previeni cicli, auto-gestione e nodi orfani con vincoli e controlli in transazione.
• Testa la sicurezza delle riorganizzazioni: spostamenti, fusioni, cambi manager e rollback quando qualcosa fallisce a metà.
• Aggiungi test dei permessi che affermino sia l’accesso consentito che quello negato per ruoli realistici (HR, manager, team lead, support).

Uno scenario pratico da validare: un agente di supporto può vedere solo i dipendenti del proprio dipartimento, mentre un manager può vedere il suo sottoalbero completo. Se sai modellare gli organigrammi in PostgreSQL e dimostrare entrambe le regole con test, sei vicino al rilascio.

Se stai costruendo questo come strumento interno in AppMaster, mantieni questi controlli come test automatizzati attorno agli endpoint che restituiscono liste org e profili, non solo sulle query del database.

Scenario di esempio e prossimi passi

Immagina un’azienda con tre dipartimenti: Sales, Support ed Engineering. Ogni dipartimento ha due team e ogni team ha un lead. Il Sales Lead A può approvare sconti per il suo team, il Support Lead B può vedere tutti i ticket del suo dipartimento e il VP di Engineering può vedere tutto ciò che è sotto Engineering.

Poi avviene una riorganizzazione: un team di Support si sposta sotto Sales e viene aggiunto un nuovo manager tra il Direttore Sales e due team lead. Il giorno dopo, qualcuno richiede l’accesso: “Permetti a Jamie (analista Sales) di vedere tutti gli account clienti del dipartimento Sales, ma non Engineering.”

Se modelli gli organigrammi in PostgreSQL con una adjacency list, lo schema è semplice, ma il lavoro dell’app si sposta sulle query e sui controlli dei permessi. Filtri come “tutti sotto Sales” di solito richiedono ricorsione. Quando aggiungi le approvazioni (come “solo i manager nella catena possono approvare”), i casi limite dopo una riorganizzazione iniziano a contare.

Con una closure table le riorganizzazioni significano più lavoro di scrittura (aggiornare righe antenato/descendente), ma il lato lettura diventa diretto. Filtri e permessi spesso diventano join semplici: “questo utente è un antenato di quel dipendente?” o “questo team è dentro il sottoalbero del dipartimento?”.

Questo si vede direttamente nelle schermate che le persone costruiscono: selettori di persone limitati a un dipartimento, instradamenti di approvazione al manager più vicino sopra il richiedente, viste amministrative per dashboard dipartimentali e audit che spiegano perché l’accesso esisteva in una data.

Prossimi passi:

1. Scrivi le regole di permesso in linguaggio semplice (chi può vedere cosa e perché).
2. Scegli un modello che corrisponda ai controlli più comuni (letture veloci vs scritture più semplici).
3. Costruisci uno strumento amministrativo interno che ti permetta di testare riorganizzazioni, richieste di accesso e approvazioni end-to-end.

Se vuoi costruire quei pannelli e portali sensibili all’organizzazione rapidamente, AppMaster (appmaster.io) può essere una scelta pratica: ti permette di modellare dati su PostgreSQL, implementare logica di approvazione in un Business Process visuale e consegnare app web e native dallo stesso backend.