Schema del database per organizzazioni e team B2B che rimane gestibile

Quale problema risolve questo pattern di schema

La maggior parte delle app B2B non sono davvero app di “account utente”. Sono spazi di lavoro condivisi dove le persone appartengono a un'organizzazione, si dividono in team e ottengono permessi diversi a seconda del ruolo. Sales, support, finance e amministratori hanno accessi diversi, e quegli accessi cambiano nel tempo.

Un modello troppo semplice si rompe in fretta. Se tieni una sola tabella users con una singola colonna role, non puoi esprimere “la stessa persona è Admin in un org ma Viewer in un altro.” Non puoi nemmeno gestire casi comuni come i contractor che dovrebbero vedere solo un team, o un dipendente che lascia un progetto ma resta nell'azienda.

Gli inviti sono un'altra fonte frequente di bug. Se un invito è solo una riga email, diventa poco chiaro se la persona è già “dentro” l'org, a quale team dovrebbe unirsi e cosa succede se si registra con un'email diversa. Piccole incongruenze qui tendono a trasformarsi in problemi di sicurezza.

Questo pattern persegue quattro obiettivi:

• Sicurezza: i permessi derivano da membership esplicite, non da assunzioni.
• Chiarezza: org, team e ruoli hanno ciascuno una fonte di verità.
• Coerenza: inviti e membership seguono un ciclo di vita prevedibile.
• Storia: puoi spiegare chi ha concesso accesso, cambiato ruoli o rimosso qualcuno.

La promessa è un unico modello relazionale che resta comprensibile man mano che le funzionalità crescono: più org per utente, più team per org, ereditarietà dei ruoli prevedibile e modifiche facili da auditare. È una struttura che puoi implementare oggi e estendere dopo senza riscrivere tutto.

Termini chiave: org, team, user e membership

Se vuoi uno schema leggibile anche tra sei mesi, inizia definendo poche parole. La maggior parte della confusione nasce dal mescolare “chi è qualcuno” con “cosa può fare”.

Un'Organization (org) è il confine tenant principale. Rappresenta il cliente o account business che possiede i dati. Se due utenti sono in org diversi, non dovrebbero vedere i dati l'uno dell'altro per default. Questa regola evita molti accessi accidentali cross-tenant.

Un Team è un gruppo più piccolo dentro un org. I team modellano unità di lavoro reali: Sales, Support, Finance o “Progetto A”. I team non sostituiscono il confine org; vivono al suo interno.

Un User è un'identità. È il login e il profilo della persona: email, nome, password o SSO ID e magari impostazioni MFA. Un user può esistere senza avere ancora accesso a nulla.

Una Membership è il record di accesso. Risponde a: “Questo user appartiene a questa org (e opzionalmente a questo team) con questo stato e questi ruoli.” Separare identità (User) e accesso (Membership) rende più semplice modellare contractor, offboarding e accesso multi-org.

Significati semplici che puoi usare in codice e UI:

• Member: un utente con una membership attiva in un org o in un team.
• Role: un insieme nominato di permessi (per esempio Org Admin, Team Manager).
• Permission: una singola azione permessa (per esempio “view invoices”).
• Tenant boundary: la regola che i dati sono in scope di un org.

Tratta la membership come una piccola macchina a stati, non come un booleano. Gli stati tipici sono invited, active, suspended e removed. Questo mantiene inviti, approvazioni e offboarding coerenti e auditabili.

Il modello relazionale unico: tabelle core e relazioni

Un buon schema multi-tenant parte da un'idea: memorizza “chi appartiene a cosa” in un posto, e tieni tutto il resto come tabelle di supporto. Così puoi rispondere a domande base (chi è nell'org, chi è in un team, cosa può fare) senza saltare tra modelli non correlati.

Tabelle core che solitamente servono:

• organizations: una riga per account cliente (tenant). Contiene nome, stato, campi billing e un id immutabile.
• teams: gruppi dentro un'organizzazione (Support, Sales, Admin). Appartengono sempre a un'organizzazione.
• users: una riga per persona. Questa è globale, non per organizzazione.
• memberships: il ponte che dice “questo user appartiene a questa organization” e opzionalmente “anche a questo team”.
• role_grants (o role_assignments): quali ruoli ha una membership, a livello org, team o entrambi.

Tieni chiavi e vincoli rigorosi. Usa primary key surrogate (UUID o bigint) per ogni tabella. Aggiungi foreign key come teams.organization_id -> organizations.id e memberships.user_id -> users.id. Poi aggiungi alcuni vincoli di unicità per bloccare duplicati prima che arrivino in produzione.

Regole che catturano la maggior parte dei dati sbagliati presto:

• Uno slug o chiave esterna per org: unique(organizations.slug)
• Nomi team per org: unique(teams.organization_id, teams.name)
• Nessuna membership org duplicata: unique(memberships.organization_id, memberships.user_id)
• Nessuna membership team duplicata (solo se modellate separatamente): unique(team_memberships.team_id, team_memberships.user_id)

Decidi cosa è append-only e cosa è aggiornabile. Organizations, teams e users sono aggiornabili. Le memberships spesso sono aggiornabili per lo stato corrente (active, suspended), ma i cambi dovrebbero anche scrivere in un registro append-only così gli audit sono semplici in seguito.

Inviti e stati di membership che restano coerenti

Il modo più semplice per mantenere pulito l'accesso è trattare un invito come un record a sé, non come una membership a metà. Una membership significa “questo user attualmente appartiene”. Un invito significa “abbiamo offerto accesso, ma non è ancora reale.” Tenerli separati evita membri fantasma, permessi parziali e misteri del tipo “chi ha invitato questa persona?”.

Un modello di stato semplice e affidabile

Per le membership usa un piccolo set di stati che puoi spiegare a chiunque:

• active: l'utente può accedere all'org (e ai team di cui è membro)
• suspended: bloccato temporaneamente, ma la cronologia resta intatta
• removed: non è più membro, conservato per audit e report

Molti team evitano uno stato di membership “invited” e mantengono “invited” strettamente nella tabella invitations. Questo risulta più pulito: le righe di membership esistono solo per utenti che hanno realmente accesso (active), o che lo avevano (suspended/removed).

Inviti via email prima che esista un account

Le app B2B spesso invitano via email quando non esiste ancora un account utente. Memorizza l'email nel record di invitation, insieme a dove si applica l'invito (org o team), il ruolo previsto e chi l'ha inviato. Se la persona poi si registra con quell'email, puoi trovare le invitations pendenti e permettere l'accettazione.

Quando un invito viene accettato, gestiscilo in una singola transazione: marca l'invito come accepted, crea la membership e scrivi una voce di audit (chi ha accettato, quando e quale email è stata usata).

Definisci stati finali per l'invito:

• expired: oltre la scadenza e non può essere accettato
• revoked: annullato da un admin e non può essere accettato
• accepted: convertito in una membership

Previeni inviti duplicati imponendo “al massimo un invito pendente per org o team per email.” Se supporti i re-invite, o estendi la scadenza sull'invito pendente esistente o revoca quello vecchio e emetti un nuovo token.

Ruoli ed ereditarietà senza rendere l'accesso confuso

La maggior parte delle app B2B ha due livelli di accesso: cosa qualcuno può fare nell'organizzazione in generale e cosa può fare dentro uno specifico team. Mischiarli in un unico campo role è il punto in cui le app iniziano a essere incoerenti.

I ruoli a livello org rispondono a domande come: questa persona può gestire la fatturazione, invitare persone o vedere tutti i team? I ruoli a livello team rispondono: può modificare elementi nel Team A, approvare richieste nel Team B o soltanto visualizzare?

L'ereditarietà dei ruoli è più gestibile quando segue una regola: un ruolo org si applica ovunque a meno che un team non dichiari esplicitamente il contrario. Questo mantiene il comportamento prevedibile e riduce dati duplicati.

Un modo pulito per modellarlo è memorizzare le assegnazioni di ruolo con uno scope:

• role_assignments: user_id, org_id, team_id opzionale (NULL significa org-wide), role_id, created_at, created_by

Se vuoi “un ruolo per scope”, aggiungi un vincolo di unicità su (user_id, org_id, team_id).

L'accesso effettivo per un team diventa:

1. Cerca un'assegnazione specifica del team (team_id = X). Se esiste, usala.

2. Altrimenti, ricadi sull'assegnazione org-wide (team_id IS NULL).

Per default least-privilege scegli un ruolo org minimale (spesso “Member”) e non dargli poteri amministrativi nascosti. I nuovi utenti non dovrebbero ricevere accesso implicito ai team a meno che il prodotto non lo richieda davvero. Se fai concessioni automatiche, fallo creando membership di team esplicite, non ampliando silenziosamente il ruolo org.

Gli override dovrebbero essere rari e ovvi. Esempio: Maria è “Manager” org-wide (può invitare, vedere report), ma nel team Finance deve essere “Viewer”. Conservi un'assegnazione org-wide per Maria e un override scoped per Finance. Niente copia di permessi, e l'eccezione è visibile.

I nomi dei ruoli funzionano bene per pattern comuni. Usa permessi espliciti solo quando hai veri casi singolari (come “può esportare ma non modificare”), o quando la compliance richiede una lista chiara di azioni consentite. Anche allora, mantieni lo stesso concetto di scope così il modello mentale resta coerente.

Modifiche pronte per audit: tracciare chi ha cambiato accesso

Se la tua app memorizza solo il ruolo corrente su una riga di membership, perdi la storia. Quando qualcuno chiede “Chi ha dato ad Alex i permessi admin martedì scorso?” non hai una risposta affidabile. Ti serve la cronologia dei cambi, non solo lo stato corrente.

L'approccio più semplice è una tabella di audit dedicata che registra gli eventi di accesso. Trattala come un diario append-only: non modifichi mai le righe vecchie dell'audit; ne aggiungi solo di nuove.

Una tabella di audit pratica di solito include:

• actor_user_id (chi ha fatto la modifica)
• subject_type e subject_id (membership, team, org)
• action (invite_sent, role_changed, membership_suspended, team_deleted)
• occurred_at (quando è successo)
• reason (testo libero opzionale come “contractor offboarding”)

Per catturare “prima” e “dopo”, memorizza un piccolo snapshot dei campi che ti interessano. Mantienilo limitato ai dati di controllo accessi, non ai profili utente completi. Per esempio: before_role, after_role, before_state, after_state, before_team_id, after_team_id. Se preferisci flessibilità, usa due colonne JSON (before, after), ma mantieni il payload piccolo e coerente.

Per membership e team, il soft delete è di solito preferibile al hard delete. Invece di rimuovere la riga, segna come disabilitata con campi come deleted_at e deleted_by. Questo mantiene intatte le foreign key e rende più semplice spiegare l'accesso passato. Il hard delete può avere senso per record veramente temporanei (come inviti scaduti), ma solo se sei sicuro di non averne più bisogno.

Con questo in atto puoi rispondere rapidamente a domande di compliance comuni:

• Chi ha concesso o rimosso accesso e quando?
• Che cosa è cambiato esattamente (ruolo, team, stato)?
• L'accesso è stato rimosso come parte di un normale offboarding?

Passo dopo passo: progettare lo schema in un database relazionale

Inizia semplice: un posto per dire chi appartiene a cosa e perché. Costruiscilo a piccoli passi e aggiungi regole man mano così i dati non degenerano in “quasi corretti”.

Un ordine pratico che funziona bene in PostgreSQL e altri DB relazionali:

1. Crea organizations e teams, ognuna con una PK stabile (UUID o bigint). Aggiungi teams.organization_id come FK e decidi presto se i nomi dei team devono essere unici all'interno di un org.

2. Tieni users separati dalla membership. Metti i campi di identità in users (email, status, created_at). Metti “appartiene a org/team” in una tabella memberships con user_id, organization_id, team_id opzionale (se la modelli così) e una colonna state (active, suspended, removed).

3. Aggiungi invitations come tabella a sé, non come colonna nella membership. Memorizza organization_id, team_id opzionale, email, token, expires_at e accepted_at. Applica unicità per “un invito aperto per org + email + team” così non crei duplicati.

4. Modella i ruoli con tabelle esplicite. Un approccio semplice è roles (admin, member, ecc.) più role_assignments che puntano a scope org (senza team_id) o team (team_id impostato). Mantieni le regole di ereditarietà coerenti e testabili.

5. Aggiungi una traccia di audit fin dal primo giorno. Usa una tabella access_events con actor_user_id, target_user_id (o email per gli inviti), action (invite_sent, role_changed, removed), scope (org/team) e created_at.

Dopo che queste tabelle esistono, esegui un paio di query admin di base per validare la realtà: “chi ha accesso org-wide?”, “quali team non hanno admin?” e “quali inviti sono scaduti ma ancora aperti?” Quelle domande tendono a rivelare vincoli mancanti presto.

Regole e vincoli che prevengono dati disordinati

Uno schema resta sano quando il database, non solo il codice, applica i confini tenant. La regola più semplice è: ogni tabella scoped al tenant porta org_id, e ogni lookup lo include. Anche se qualcuno dimentica un filtro nell'app, il database dovrebbe resistere a connessioni cross-org.

Guardrail che mantengono i dati puliti

Inizia con foreign key che puntano sempre “dentro lo stesso org.” Per esempio, se memorizzi team_memberships separatamente, una riga team_memberships dovrebbe referenziare team_id e user_id, ma anche portare org_id. Con chiavi composite puoi far rispettare che il team referenziato appartenga allo stesso org.

Vincoli che evitano i problemi più comuni:

• Una membership org attiva per utente per org: unique su (org_id, user_id) con condizione parziale per righe attive (dove supportato).
• Un invito pendente per email per org o team: unique su (org_id, team_id, email) dove state = 'pending'.
• I token degli inviti sono globalmente unici e mai riutilizzati: unique su invite_token.
• Team appartiene esattamente a un org: teams.org_id NOT NULL con FK a orgs(id).
• Termina le membership invece di eliminarle: memorizza ended_at (e opzionalmente ended_by) per proteggere la storia di audit.

Indicizzazione per le lookup che fai davvero

Indicizza le query che la tua app esegue più spesso:

• (org_id, user_id) per “in quali org è questo utente?”
• (org_id, team_id) per “elenca i membri di questo team”
• (invite_token) per “accetta invito”
• (org_id, state) per “inviti pendenti” e “membri attivi”

Tieni i nomi org modificabili. Usa un orgs.id immutabile ovunque e tratta orgs.name (e eventuale slug) come campi editabili. Rinominare tocca una sola riga.

Spostare un team tra org è di solito una decisione di policy. L'opzione più sicura è vietarlo (o clonare il team) perché membership, ruoli e storia di audit sono scoped per org. Se devi permetterlo, fallo in una singola transazione e aggiorna tutte le righe figlie che portano org_id.

Per evitare record orfani quando gli utenti se ne vanno, evita i delete fisici. Disabilita l'user, termina le sue membership e limita i delete sui parent row (ON DELETE RESTRICT) a meno che tu voglia veramente rimuovere in cascata.

Scenario d'esempio: un org, due team, cambiare accesso in sicurezza

Immagina un'azienda chiamata Northwind Co con un org e due team: Sales e Support. Assumono una contractor, Mia, per gestire i ticket di Support per un mese. Qui il modello dovrebbe restare prevedibile: una persona, una membership org, membership team opzionali e stati chiari.

Un admin org (Ava) invita Mia via email. Il sistema crea una riga invitation legata all'org, con stato pending e una data di scadenza. Niente cambia ancora, quindi non esiste un “utente a metà” con accesso ambiguo.

Quando Mia accetta, l'invito è marcato accepted e viene creata una riga di membership org con stato active. Ava assegna a Mia il ruolo org member (non admin). Poi Ava aggiunge la membership al team Support e assegna un ruolo team come support_agent.

Aggiungiamo una complicazione: Ben è un dipendente a tempo pieno con ruolo org admin, ma non dovrebbe vedere i dati di Support. Puoi gestirlo con un override a livello team che declassi esplicitamente il suo ruolo per Support mantenendo i suoi poteri admin per le impostazioni org-wide.

Una settimana dopo, Mia viola la policy e viene sospesa. Invece di cancellare righe, Ava imposta lo stato della membership org di Mia a suspended. Le membership di team possono restare ma diventano inefficaci perché la membership org non è attiva.

La cronologia di audit resta pulita perché ogni cambiamento è un evento:

• Ava ha invitato Mia (chi, cosa, quando)
• Mia ha accettato l'invito
• Ava ha aggiunto Mia a Support e assegnato support_agent
• Ava ha impostato l'override di Ben per Support
• Ava ha sospeso Mia

Con questo modello, la UI può mostrare un sommario accessi chiaro: stato org (active o suspended), ruolo org, lista team con ruoli e override, e un feed “Modifiche accesso recenti” che spiega perché qualcuno può o non può vedere Sales o Support.

Errori comuni e trappole da evitare

La maggior parte dei bug di accesso nasce da modelli “quasi giusti”. Lo schema sembra ok all'inizio, poi i casi limite si accumulano: reinviti, spostamenti di team, cambi di ruolo e offboarding.

Una trappola comune è mescolare inviti e membership in una sola riga. Se memorizzi “invited” e “active” nella stessa riga senza significato chiaro, ti ritrovi a chiedere cose impossibili come “Questa persona è membro se non ha mai accettato?” Tieni inviti e membership separati o rendi la macchina a stati esplicita e coerente.

Un altro errore frequente è mettere una singola colonna role nella tabella users e pensare che sia tutto risolto. I ruoli sono quasi sempre scoped (ruolo org, ruolo team, ruolo progetto). Un ruolo globale forza stratagemmi come “l'utente è admin per un cliente ma in sola lettura per un altro”, che rompe le aspettative multi-tenant e crea problemi di supporto.

Trappole che fanno più male col tempo:

• Permettere per sbaglio membership cross-org (team_id punta a org A, membership punta a org B).
• Eliminare fisicamente membership perdendo la traccia “chi aveva accesso la settimana scorsa?”.
• Vincoli di unicità mancanti così un utente ottiene accesso duplicato tramite righe identiche.
• Lasciare l'ereditarietà impilarsi silenziosamente (org admin + team member + override) così nessuno può spiegare perché esiste un accesso.
• Trattare “invito accettato” come evento UI e non come fatto nel database.

Un esempio veloce: un contractor è invitato a un org, si unisce al Team Sales, poi viene rimosso e reinvitato un mese dopo. Se sovrascrivi la riga vecchia perdi la storia. Se permetti duplicati, potrebbe finire con due membership attive. Stati chiari, ruoli scoped e vincoli giusti prevengono entrambi i problemi.

Controlli rapidi e passi successivi per integrarlo nella tua app

Prima di codare, fai una rapida revisione del modello e vedi se ha ancora senso su carta. Un buon modello di accesso multi-tenant dovrebbe sembrare noioso: le stesse regole si applicano ovunque e i “casi speciali” sono rari.

Una checklist rapida per trovare gap comuni:

• Ogni membership punta esattamente a un user e a un org, con un vincolo unico per prevenire duplicati.
• Stati di invitation, membership e rimozione sono espliciti (non implicati da null) e le transizioni sono limitate (per esempio, non si può accettare un invito scaduto).
• I ruoli sono memorizzati in un posto e l'accesso effettivo è calcolato in modo coerente (inclusa l'ereditarietà, se la usi).
• Cancellare org/team/user non cancella la storia (usa soft delete o campi di archiviazione dove serve traccia di audit).
• Ogni cambiamento di accesso emette un evento di audit con actor, target, scope, timestamp e motivo/sorgente.

Metti il design sotto stress con domande reali. Se non puoi rispondere a queste con una query e una regola chiara, probabilmente ti serve un vincolo o uno stato in più:

• Cosa succede se un utente viene invitato due volte e poi cambia email?
• Un admin di team può rimuovere un owner org da quel team?
• Se un ruolo org concede accesso a tutti i team, un team può sovrascriverlo?
• Se un invito viene accettato dopo che il ruolo è stato cambiato, quale ruolo si applica?
• Quando il support chiede “chi ha rimosso l'accesso”, puoi dimostrarlo rapidamente?

Scrivi cosa devono capire admin e support: stati di membership (e cosa li innesca), chi può invitare/rimuovere, cosa significa l'ereditarietà dei ruoli in linguaggio semplice e dove guardare per eventi di audit durante un incidente.

Implementa prima i vincoli (unicità, foreign key, transizioni permesse), poi costruisci la logica di business sopra di essi così il database ti aiuta a restare onesto. Mantieni decisioni di policy (ereditarietà on/off, ruoli di default, scadenza inviti) in tabelle di configurazione piuttosto che in costanti di codice.

Se vuoi costruire questo senza scrivere a mano ogni backend e schermo admin, AppMaster (appmaster.io) può aiutarti a modellare queste tabelle in PostgreSQL e a implementare transizioni di invito e membership come processi di business espliciti, pur generando codice reale per deploy di produzione.