Schéma de base de données pour organisations et équipes B2B qui reste cohérent

Quel problème ce modèle de schéma résout

La plupart des applications B2B ne sont pas vraiment des apps « comptes utilisateurs ». Ce sont des espaces de travail partagés où les personnes appartiennent à une organisation, sont réparties en équipes, et ont des permissions différentes selon leur rôle. Les équipes ventes, support, finance et les admins ont des accès distincts, et ces accès évoluent dans le temps.

Un modèle trop simple casse vite. Si vous ne gardez qu'une table users avec une colonne role unique, vous ne pouvez pas exprimer « la même personne est Admin dans une org, mais Viewer dans une autre ». Vous ne gérez pas non plus des cas courants comme des prestataires qui ne doivent voir qu'une seule équipe, ou un employé qui quitte un projet mais reste dans la société.

Les invitations sont une autre source fréquente de bugs. Si une invitation n'est qu'une ligne email, il devient flou de savoir si la personne fait déjà partie de l'org, quelle équipe elle doit rejoindre, et ce qui se passe si elle s'inscrit avec un autre email. De petites incohérences ici finissent souvent en problèmes de sécurité.

Ce modèle vise quatre objectifs :

• Sécurité : les permissions viennent d'une adhésion explicite, pas d'hypothèses.
• Clarté : orgs, équipes et rôles ont chacun une source de vérité.
• Cohérence : invitations et memberships suivent un cycle de vie prévisible.
• Historique : vous pouvez expliquer qui a accordé l'accès, changé les rôles ou supprimé quelqu'un.

La promesse est un modèle relationnel unique qui reste compréhensible à mesure que les fonctionnalités s'ajoutent : plusieurs orgs par utilisateur, plusieurs équipes par org, héritage de rôles prévisible et changements faciles à auditer. C'est une structure que vous pouvez implémenter aujourd'hui et étendre plus tard sans tout réécrire.

Termes clés : orgs, équipes, utilisateurs et memberships

Si vous voulez un schéma encore lisible dans six mois, commencez par vous mettre d'accord sur quelques mots. La plupart des confusions viennent de mélanger « qui est quelqu'un » et « ce qu'il peut faire ».

Une Organization (org) est la frontière de locataire principale. Elle représente le client ou le compte business qui possède les données. Si deux utilisateurs appartiennent à des orgs différentes, ils ne devraient par défaut pas voir les données l'un de l'autre. Cette règle simple évite beaucoup d'accès inter-tenant accidentels.

Une Team est un groupe plus petit à l'intérieur d'une org. Les équipes modélisent des unités de travail réelles : Sales, Support, Finance ou « Projet A ». Les équipes vivent sous l'org et ne remplacent pas la frontière org.

Un User est une identité. C'est la connexion et le profil : email, nom, mot de passe ou identifiant SSO, et peut-être des paramètres MFA. Un user peut exister sans avoir accès à quoi que ce soit.

Une Membership est l'enregistrement d'accès. Elle répond à : « Cet utilisateur appartient à cette org (et éventuellement à cette équipe) avec ce statut et ces rôles. » Séparer l'identité (User) de l'accès (Membership) rend la modélisation des prestataires, de l'offboarding et de l'accès multi-org beaucoup plus simple.

Sens clairs à utiliser dans le code et l'UI :

• Member : un utilisateur avec une membership active dans une org ou une équipe.
• Role : un ensemble nommé de permissions (par exemple Org Admin, Team Manager).
• Permission : une action autorisée unique (par exemple « view invoices »).
• Frontière du tenant : la règle que les données sont sciatées par org.

Considérez la membership comme une petite machine d'états, pas un booléen. Les états typiques sont invited, active, suspended et removed. Cela rend invitations, validations et offboarding cohérents et auditables.

Le modèle relationnel unique : tables et relations principales

Un bon schéma multi-tenant commence par une idée : stocker « qui appartient où » en un seul endroit, et garder tout le reste comme tables de support. Ainsi vous pouvez répondre aux questions basiques (qui est dans l'org, qui est dans une équipe, que peuvent-ils faire) sans sauter dans des modèles non reliés.

Tables principales généralement nécessaires :

• organizations : une ligne par compte client (tenant). Contient le nom, le statut, les champs de facturation et un id immuable.
• teams : groupes à l'intérieur d'une organization (Support, Sales, Admin). Appartient toujours à une seule organization.
• users : une ligne par personne. C'est global, pas par organisation.
• memberships : le pont qui dit « cet utilisateur appartient à cette organization » et éventuellement « aussi à cette équipe ».
• role_grants (ou role_assignments) : quels rôles une membership a, au niveau org, au niveau équipe, ou les deux.

Gardez clés et contraintes strictes. Utilisez des clés primaires substituées (UUIDs ou bigints) pour chaque table. Ajoutez des clés étrangères comme teams.organization_id -> organizations.id et memberships.user_id -> users.id. Ensuite, ajoutez quelques contraintes uniques pour empêcher les doublons avant qu'ils n'arrivent en production.

Règles qui attrapent la plupart des données incorrectes tôt :

• Un slug ou clé externe unique pour l'org : unique(organizations.slug)
• Noms d'équipe uniques par org : unique(teams.organization_id, teams.name)
• Pas de double membership org : unique(memberships.organization_id, memberships.user_id)
• Pas de double membership d'équipe (si vous modélisez les memberships d'équipe séparément) : unique(team_memberships.team_id, team_memberships.user_id)

Décidez ce qui est append-only vs modifiable. Organizations, teams et users sont modifiables. Les memberships sont souvent modifiables pour l'état courant (active, suspended), mais chaque changement devrait aussi écrire dans un journal append-only pour faciliter les audits ultérieurs.

Invitations et états de membership qui restent cohérents

La façon la plus simple de garder l'accès propre est de traiter une invitation comme son propre enregistrement, pas comme une membership incomplète. Une membership signifie « cet utilisateur appartient actuellement ». Une invitation signifie « on a offert l'accès, mais ce n'est pas encore réel ». Les garder séparés évite les membres fantômes, les permissions à moitié créées et les mystères du type « qui a invité cette personne ? ».

Un modèle d'état simple et fiable

Pour les memberships, utilisez un petit ensemble d'états que vous pouvez expliquer à tout le monde :

• active : l'utilisateur peut accéder à l'org (et aux équipes dont il est membre)
• suspended : bloqué temporairement, mais l'historique reste intact
• removed : n'est plus membre, conservé pour l'audit et le reporting

Beaucoup d'équipes évitent d'avoir un état membership « invited » et gardent « invited » strictement dans la table invitations. C'est souvent plus propre : les lignes membership existent uniquement pour les utilisateurs qui ont réellement accès (active), ou qui en avaient auparavant (suspended/removed).

Invitations par email avant la création d'un compte

Les apps B2B invitent souvent par email quand aucun compte utilisateur n'existe encore. Stockez l'email dans l'enregistrement d'invitation, avec la portée (org ou équipe), le rôle prévu et qui l'a envoyée. Si la personne s'inscrit ensuite avec ce même email, vous pouvez faire correspondre les invitations en attente et lui permettre d'accepter.

Quand une invitation est acceptée, traitez-la dans une transaction unique : marquez l'invitation comme acceptée, créez la membership, et écrivez une entrée d'audit (qui a accepté, quand, et quel email a été utilisé).

Définissez des états de fin d'invitation clairs :

• expired : passée la date et ne peut plus être acceptée
• revoked : annulée par un admin et ne peut plus être acceptée
• accepted : convertie en membership

Empêchez les invitations en double en faisant respecter « une seule invitation en attente par org ou équipe par email ». Si vous supportez les ré-invitations, soit étendez l'expiration de l'invitation en attente existante, soit révoquez l'ancienne et émettez un nouveau token.

Rôles et héritage sans rendre l'accès confus

La plupart des apps B2B ont besoin de deux niveaux d'accès : ce que quelqu'un peut faire au niveau organisation, et ce qu'il peut faire dans une équipe spécifique. Mixer tout ça dans une seule colonne role est là où les apps commencent à devenir incohérentes.

Les rôles org répondent à des questions comme : cette personne peut-elle gérer la facturation, inviter des gens ou voir toutes les équipes ? Les rôles équipe répondent : peut-elle modifier les éléments de l'équipe A, approuver des demandes dans l'équipe B, ou seulement voir ?

L'héritage des rôles est plus simple à gérer quand il suit une règle : un rôle org s'applique partout sauf si une équipe dit explicitement le contraire. Cela rend le comportement prévisible et réduit les données dupliquées.

Une manière propre de modéliser cela est de stocker les assignations de rôle avec une portée :

• role_assignments : user_id, org_id, team_id optionnel (NULL signifie scope org-wide), role_id, created_at, created_by

Si vous voulez « un rôle par portée », ajoutez une contrainte unique sur (user_id, org_id, team_id).

L'accès effectif pour une équipe devient :

1. Cherchez une assignation spécifique à l'équipe (team_id = X). Si elle existe, utilisez-la.

2. Sinon, retombez sur l'assignation org-wide (team_id IS NULL).

Pour des valeurs par défaut en moindre privilège, choisissez un rôle org minimal (souvent « Member ») et ne lui donnez pas de pouvoirs admin cachés. Les nouveaux utilisateurs ne devraient pas obtenir d'accès d'équipe implicite, sauf si votre produit le nécessite vraiment. Si vous auto-attribuez, faites-le en créant des memberships d'équipe explicites, pas en élargissant silencieusement le rôle org.

Les overrides doivent être rares et évidents. Exemple : Maria est « Manager » org (peut inviter, voir les rapports), mais dans l'équipe Finance elle doit être « Viewer ». Vous stockez une assignation org-wide pour Maria, plus une override scope équipe pour Finance. Pas de copie de permissions, l'exception est visible.

Les noms de rôles fonctionnent bien pour les schémas courants. Utilisez des permissions explicites seulement pour de vrais cas uniques (comme « peut exporter mais pas modifier »), ou lorsque la conformité exige une liste claire d'actions autorisées. Même alors, conservez l'idée de portée pour maintenir un modèle mental cohérent.

Changements compatibles audit : tracer qui a modifié l'accès

Si votre app stocke seulement le rôle courant sur une ligne membership, vous perdez l'histoire. Quand quelqu'un demande « Qui a donné les droits admin à Alex mardi dernier ? », vous n'avez pas de réponse fiable. Vous avez besoin d'un historique des changements, pas seulement de l'état courant.

L'approche la plus simple est une table d'audit dédiée qui enregistre les événements d'accès. Traitez-la comme un journal append-only : n'éditez jamais les anciennes lignes d'audit, n'ajoutez que de nouvelles.

Une table d'audit pratique inclut généralement :

• actor_user_id (qui a fait le changement)
• subject_type et subject_id (membership, team, org)
• action (invite_sent, role_changed, membership_suspended, team_deleted)
• occurred_at (quand)
• reason (texte libre optionnel comme « contractor offboarding »)

Pour capturer le « avant » et « après », stockez un petit snapshot des champs qui vous intéressent. Limitez-le aux données de contrôle d'accès, pas au profil utilisateur complet. Par exemple : before_role, after_role, before_state, after_state, before_team_id, after_team_id. Si vous préférez la flexibilité, utilisez deux colonnes JSON (before, after), mais gardez la charge utile petite et cohérente.

Pour les memberships et les équipes, la suppression douce (soft delete) est généralement préférable à la suppression physique. Au lieu de supprimer la ligne, marquez-la comme désactivée avec des champs comme deleted_at et deleted_by. Cela préserve les clés étrangères et facilite l'explication des accès passés. La suppression physique peut encore avoir du sens pour des enregistrements vraiment temporaires (comme des invites expirées), mais seulement si vous êtes sûr de ne pas en avoir besoin plus tard.

Avec cela en place, vous pouvez répondre rapidement aux questions de conformité courantes :

• Qui a accordé ou retiré l'accès, et quand ?
• Qu'est-ce qui a exactement changé (rôle, équipe, état) ?
• L'accès a-t-il été retiré dans le cadre d'un offboarding normal ?

Étape par étape : concevoir le schéma dans une base relationnelle

Commencez simple : un seul endroit pour dire qui appartient à quoi, et pourquoi. Construisez par petites étapes, et ajoutez des règles au fur et à mesure pour que les données ne dérivent pas vers du « presque correct ».

Un ordre pratique qui fonctionne bien dans PostgreSQL et d'autres bases relationnelles :

1. Créez organizations et teams, chacun avec une clé primaire stable (UUID ou bigint). Ajoutez teams.organization_id comme clé étrangère, et décidez tôt si les noms d'équipe doivent être uniques dans une org.

2. Gardez users séparés des memberships. Mettez les champs d'identité dans users (email, status, created_at). Mettez « appartient à org/équipe » dans une table memberships avec user_id, organization_id, team_id optionnel (si vous le modélisez ainsi), et une colonne state (active, suspended, removed).

3. Ajoutez invitations comme table à part, pas une colonne sur membership. Stockez organization_id, team_id optionnel, email, token, expires_at et accepted_at. Faites respecter l'unicité pour « une invite ouverte par org + email + team » pour éviter les doublons.

4. Modélisez les rôles avec des tables explicites. Une approche simple est roles (admin, member, etc.) plus role_assignments qui pointent soit vers la portée org (pas de team_id) soit vers la portée équipe (team_id renseigné). Gardez les règles d'héritage consistantes et testables.

5. Ajoutez un fil d'audit dès le premier jour. Utilisez une table access_events avec actor_user_id, target_user_id (ou email pour les invites), action (invite_sent, role_changed, removed), scope (org/team) et created_at.

Après la création de ces tables, lancez quelques requêtes admin basiques pour valider la réalité : « qui a un accès org-wide ? », « quelles équipes n'ont pas d'admins ? », « quelles invitations sont expirées mais toujours ouvertes ? » Ces questions révèlent souvent des contraintes manquantes tôt.

Règles et contraintes qui évitent les données en désordre

Un schéma reste sain quand la base de données, pas seulement votre code, fait respecter les frontières du tenant. La règle la plus simple est : chaque table portée par un tenant porte org_id, et chaque lookup l'inclut. Même si quelqu'un oublie un filtre dans l'app, la base devrait résister aux connexions cross-org.

Garde-fous qui maintiennent les données propres

Commencez par des clés étrangères qui pointent toujours « au sein de la même org ». Par exemple, si vous stockez la membership d'équipe séparément, une ligne team_memberships devrait référencer un team_id et un user_id, mais aussi porter org_id. Avec des clés composites, vous pouvez faire respecter que l'équipe référencée appartient à la même org.

Contraintes qui évitent les problèmes les plus courants :

• Une membership org active par utilisateur et par org : unique sur (org_id, user_id) avec une condition partielle pour les lignes actives (là où supporté).
• Une invitation en attente par email par org ou équipe : unique sur (org_id, team_id, email) où state = 'pending'.
• Les tokens d'invite sont globalement uniques et jamais réutilisés : unique sur invite_token.
• Une équipe appartient à exactement une org : teams.org_id NOT NULL avec une clé étrangère vers orgs(id).
• Terminez les memberships au lieu de les supprimer : stockez ended_at (et optionnellement ended_by) pour protéger l'historique d'audit.

Indexation pour les recherches que vous faites vraiment

Indexez les requêtes que votre app exécute tout le temps :

• (org_id, user_id) pour « dans quelles orgs est cet utilisateur ? »
• (org_id, team_id) pour « lister les membres de cette équipe »
• (invite_token) pour « accepter une invitation »
• (org_id, state) pour « invitations en attente » et « membres actifs »

Gardez les noms d'org modifiables. Utilisez un orgs.id immuable partout, et traitez orgs.name (et tout slug) comme des champs éditables. Renommer touche alors une seule ligne.

Déplacer une équipe entre orgs est souvent une décision politique. L'option la plus sûre est de l'interdire (ou de cloner l'équipe) car les memberships, rôles et l'historique d'audit sont liés à l'org. Si vous devez autoriser les déplacements, faites-le dans une transaction unique et mettez à jour toutes les lignes enfants portant org_id.

Pour prévenir les enregistrements orphelins quand les utilisateurs partent, évitez les suppressions physiques. Désactivez l'utilisateur, terminez ses memberships, et restreignez les suppressions sur les lignes parentes (ON DELETE RESTRICT) à moins que vous souhaitiez vraiment une suppression en cascade.

Scénario exemple : une org, deux équipes, changement d'accès en sécurité

Imaginez une société appelée Northwind Co avec une org et deux équipes : Sales et Support. Ils engagent une prestataire, Mia, pour traiter les tickets de Support pendant un mois. Le modèle doit rester prévisible : une personne, une membership org, memberships d'équipe optionnelles, et des états clairs.

Un admin org (Ava) invite Mia par email. Le système crée une ligne d'invitation liée à l'org, avec le statut pending et une date d'expiration. Rien d'autre ne change encore, il n'y a donc pas de « demi-utilisateur » avec un accès flou.

Quand Mia accepte, l'invitation est marquée accepted et une ligne membership org est créée avec l'état active. Ava donne à Mia le rôle org member (pas admin). Ensuite Ava ajoute une membership à l'équipe Support et assigne un rôle d'équipe comme support_agent.

Ajoutons une torsion : Ben est employé à plein temps avec le rôle org admin, mais il ne doit pas voir les données de Support. Vous pouvez gérer cela avec une override d'équipe qui lui donne un rôle plus restreint pour Support tout en conservant ses capacités admin au niveau org.

Une semaine plus tard, Mia viole la politique et est suspendue. Au lieu de supprimer des lignes, Ava met l'état de la membership org de Mia à suspended. Les memberships d'équipe peuvent rester mais deviennent inopérantes tant que la membership org n'est pas active.

L'historique d'audit reste propre parce que chaque changement est un événement :

• Ava a invité Mia (qui, quoi, quand)
• Mia a accepté l'invitation
• Ava a ajouté Mia au Support et a assigné support_agent
• Ava a appliqué l'override de Ben pour Support
• Ava a suspendu Mia

Avec ce modèle, l'UI peut afficher un résumé d'accès clair : état org (active ou suspended), rôle org, liste d'équipes avec rôles et overrides, et un fil « changements d'accès récents » qui explique pourquoi quelqu'un voit ou non Sales ou Support.

Erreurs courantes et pièges à éviter

La plupart des bugs d'accès viennent de modèles « presque corrects ». Le schéma a l'air bien au début, puis les cas limites s'accumulent : réinvitations, déplacements d'équipe, changements de rôle et offboarding.

Un piège fréquent est de mélanger invitations et memberships sur une même ligne. Si vous stockez « invited » et « active » dans le même enregistrement sans signification claire, vous vous retrouvez avec des questions impossibles du type « cette personne est-elle membre si elle n'a jamais accepté ? ». Séparez invitations et memberships, ou rendez la machine d'états explicite et cohérente.

Autre erreur fréquente : mettre une colonne rôle unique sur la table user et croire que c'est suffisant. Les rôles sont presque toujours scopiés (rôle org, rôle équipe, rôle projet). Un rôle global force des contorsions du type « utilisateur admin pour un client, en lecture seule pour un autre », ce qui casse les attentes multi-tenant et crée des maux de tête pour le support.

Pièges qui font mal plus tard :

• Permettre par accident la membership cross-org (team_id pointe vers l'org A, membership vers l'org B).
• Supprimer physiquement des memberships et perdre la trace « qui avait accès la semaine dernière ? ».
• Absence de règles d'unicité permettant à un utilisateur d'obtenir un accès dupliqué via des lignes identiques.
• Laisser l'héritage s'empiler silencieusement (admin org + membre d'équipe + override) de sorte que personne ne puisse expliquer pourquoi l'accès existe.
• Traiter « invite accepted » comme un événement UI, pas comme un fait enregistré en base.

Un exemple rapide : un prestataire est invité dans une org, rejoint l'équipe Sales, puis est supprimé et ré-invité un mois plus tard. Si vous écrasez l'ancienne ligne, vous perdez l'historique. Si vous permettez les doublons, il peut se retrouver avec deux memberships actives. Des états clairs, des rôles scopiés et les bonnes contraintes évitent les deux.

Vérifications rapides et prochaines étapes pour l'intégrer à votre app

Avant de coder, passez rapidement votre modèle en revue et voyez s'il tient encore sur le papier. Un bon modèle d'accès multi-tenant doit sembler ennuyeux : les mêmes règles s'appliquent partout, et les « cas spéciaux » sont rares.

Une checklist rapide pour repérer les lacunes courantes :

• Chaque membership pointe vers exactement un user et une org, avec une contrainte unique pour éviter les duplicatas.
• États d'invitation, membership et suppression sont explicites (pas déduits d'un null), et les transitions sont limitées (par exemple, on ne peut pas accepter une invite expirée).
• Les rôles sont stockés en un seul endroit et l'accès effectif est calculé de manière cohérente (y compris les règles d'héritage si vous les utilisez).
• La suppression d'orgs/teams/users n'efface pas l'historique (utilisez soft delete ou champs d'archivage quand il faut garder une trace).
• Chaque changement d'accès émet un événement d'audit avec acteur, cible, portée, horodatage et raison/source.

Soumettez le design à des questions concrètes. Si vous ne pouvez pas répondre à celles-ci avec une requête et une règle claire, vous avez probablement besoin d'une contrainte ou d'un état supplémentaire :

• Que se passe-t-il si un utilisateur est invité deux fois, puis que l'email change ?
• Un admin d'équipe peut-il retirer un owner d'org de cette équipe ?
• Si un rôle org donne accès à toutes les équipes, une équipe peut-elle l'overrider ?
• Si une invite est acceptée après qu'un rôle a changé, quel rôle s'applique ?
• Si le support demande « qui a retiré l'accès », pouvez-vous le prouver rapidement ?

Rédigez ce que les admins et le support doivent comprendre : états de membership (et ce qui les déclenche), qui peut inviter/supprimer, ce que signifie l'héritage de rôles en langage simple, et où chercher les événements d'audit lors d'un incident.

Implémentez d'abord les contraintes (uniques, clés étrangères, transitions autorisées), puis construisez la logique métier autour pour que la base de données vous aide à rester honnête. Gardez les décisions de politique (héritage on/off, rôles par défaut, expiration d'invites) dans des tables de configuration plutôt que dans des constantes code.

Si vous voulez construire cela sans écrire à la main tous les backends et écrans d'administration, AppMaster (appmaster.io) peut vous aider à modéliser ces tables dans PostgreSQL et implémenter les transitions d'invitation et de membership comme des processus métier explicites, tout en générant du code source réel pour des déploiements en production.