Esquema de base de datos para organizaciones y equipos B2B que se mantiene coherente

Qué problema resuelve este patrón de esquema

La mayoría de apps B2B no son realmente apps de “cuentas de usuario”. Son espacios de trabajo compartidos donde la gente pertenece a una organización, se divide en equipos y recibe permisos distintos según su trabajo. Ventas, soporte, finanzas y admins necesitan accesos distintos, y esos accesos cambian con el tiempo.

Un modelo demasiado simple se rompe rápido. Si mantienes una sola tabla users con una columna role, no puedes expresar “la misma persona es Admin en una org y Viewer en otra.” Tampoco puedes manejar casos comunes como contratistas que solo deben ver un equipo o un empleado que deja un proyecto pero sigue perteneciendo a la empresa.

Las invitaciones son otra fuente frecuente de errores. Si una invitación es solo una fila de email, queda poco claro si la persona ya está “en” la org, a qué equipo debe unirse y qué pasa si se registra con otro correo. Pequeñas inconsistencias aquí tienden a convertirse en problemas de seguridad.

Este patrón persigue cuatro objetivos:

• Seguridad: los permisos vienen de membresías explícitas, no de suposiciones.
• Claridad: orgs, equipos y roles tienen cada uno una única fuente de verdad.
• Consistencia: invitaciones y membresías siguen un ciclo de vida predecible.
• Historial: puedes explicar quién concedió acceso, cambió roles o eliminó a alguien.

La promesa es un único modelo relacional que sigue siendo comprensible conforme crecen las funciones: múltiples orgs por usuario, múltiples equipos por org, herencia de roles predecible y cambios aptos para auditoría. Es una estructura que puedes implementar hoy y ampliar después sin reescribirlo todo.

Términos clave: orgs, equipos, usuarios y membresías

Si quieres un esquema que siga legible después de seis meses, empieza por acordar algunas palabras. La mayor parte de la confusión viene de mezclar “quién es alguien” con “qué puede hacer”.

Una Organization (org) es la frontera de inquilino superior. Representa el cliente o cuenta de negocio que posee los datos. Si dos usuarios están en orgs diferentes, no deberían ver por defecto los datos del otro. Esa regla evita mucho acceso cruzado accidental.

Un Team es un grupo más pequeño dentro de una org. Modelan unidades de trabajo reales: Sales, Support, Finance o “Proyecto A”. Los teams no reemplazan el límite de la org; viven bajo él.

Un User es una identidad. Es el inicio de sesión y el perfil: email, nombre, contraseña o ID SSO y quizá ajustes de MFA. Un usuario puede existir sin tener acceso a nada todavía.

Una Membership es el registro de acceso. Responde: “Este usuario pertenece a esta org (y opcionalmente a este equipo) con este estado y estos roles.” Mantener identidad (User) separada del acceso (Membership) facilita modelar contratistas, offboarding y acceso multi-org.

Significados claros que puedes usar en código y UI:

• Member: un usuario con una membresía activa en una org o equipo.
• Role: un conjunto nombrado de permisos (por ejemplo, Org Admin, Team Manager).
• Permission: una acción permitida única (por ejemplo, “ver facturas”).
• Tenant boundary: la regla de que los datos están limitados a una org.

Trata la membresía como una pequeña máquina de estados, no como un booleano. Estados típicos son invited, active, suspended y removed. Esto mantiene las invitaciones, aprobaciones y el offboarding consistentes y auditables.

El modelo relacional único: tablas y relaciones centrales

Un buen esquema multi-inquilino parte de una idea: almacenar “quién pertenece dónde” en un solo lugar y mantener todo lo demás como tablas auxiliares. Así puedes responder preguntas básicas (quién está en la org, quién está en un equipo, qué puede hacer) sin saltar por modelos no relacionados.

Tablas centrales que normalmente necesitas:

• organizations: una fila por cuenta cliente (tenant). Contiene nombre, estado, campos de facturación y un id inmutable.
• teams: grupos dentro de una organización (Support, Sales, Admin). Siempre pertenecen a una organización.
• users: una fila por persona. Esto es global, no por organización.
• memberships: el puente que dice “este usuario pertenece a esta organización” y opcionalmente “también a este equipo.”
• role_grants (o role_assignments): qué roles tiene una membresía, a nivel de org, equipo o ambos.

Mantén claves y restricciones estrictas. Usa claves primarias sustitutas (UUIDs o bigints) para cada tabla. Agrega foreign keys como teams.organization_id -> organizations.id y memberships.user_id -> users.id. Luego añade unas pocas restricciones únicas para evitar duplicados antes de que aparezcan en producción.

Reglas que capturan la mayor parte de datos malos tempranamente:

• Un slug o clave externa por org: unique(organizations.slug)
• Nombres de team por org: unique(teams.organization_id, teams.name)
• Sin membresía org duplicada: unique(memberships.organization_id, memberships.user_id)
• Sin membresía de equipo duplicada (solo si modelas membresía de equipo por separado): unique(team_memberships.team_id, team_memberships.user_id)

Decide qué es append-only y qué es actualizable. Organizations, teams y users son actualizables. Las memberships a menudo se actualizan para el estado actual (active, suspended), pero los cambios deberían también escribirse en un registro de acceso append-only para que las auditorías sean sencillas después.

Invitaciones y estados de membresía que se mantienen consistentes

La forma más sencilla de mantener el acceso limpio es tratar una invitación como su propio registro, no como una membresía a medio crear. Una membresía significa “este usuario pertenece ahora”. Una invitación significa “ofrecimos acceso, pero aún no es real.” Mantenerlas separadas evita miembros fantasma, permisos a medio crear y misterios de “quién invitó a esta persona”.

Un modelo de estados simple y fiable

Para las memberships, usa un pequeño conjunto de estados que puedas explicar a cualquiera:

• active: el usuario puede acceder a la org (y a cualquier equipo del que sea miembro)
• suspended: bloqueado temporalmente, pero la historia se mantiene intacta
• removed: ya no es miembro, se conserva para auditoría e informes

Muchos equipos evitan dejar el estado “invited” en la membership y en su lugar mantienen “invited” estrictamente en la tabla de invitations. Eso suele ser más limpio: las filas de membership existen solo para usuarios que realmente tienen acceso (active), o que lo tuvieron (suspended/removed).

Invitaciones por email antes de que exista una cuenta

Las apps B2B suelen invitar por email cuando no existe aún una cuenta de usuario. Guarda el email en el registro de invitación, junto con dónde aplica la invitación (org o team), el rol previsto y quién la envió. Si la persona luego se registra con ese email, puedes emparejar invitaciones pendientes y permitir que las acepten.

Cuando se acepta una invitación, manéjalo en una transacción: marca la invitación como accepted, crea la membership y escribe una entrada de auditoría (quién aceptó, cuándo y qué email se usó).

Define estados finales claros para la invitación:

• expired: pasada su fecha límite y no puede aceptarse
• revoked: cancelada por un admin y no puede aceptarse
• accepted: convertida en una membership

Evita invitaciones duplicadas imponiendo “solo una invitación pendiente por org o equipo por email.” Si soportas re-invites, o bien extiende la expiración en la invitación pendiente existente o revoca la vieja y emite un nuevo token.

Roles y herencia sin volver el acceso confuso

La mayoría de apps B2B necesitan dos niveles de acceso: qué puede hacer alguien en la organización en su conjunto y qué puede hacer dentro de un equipo específico. Mezclar esto en un solo campo role es donde las apps empiezan a sentirse inconsistentes.

Los roles a nivel org responden preguntas como: ¿esta persona puede gestionar facturación, invitar gente o ver todos los equipos? Los roles a nivel de equipo responden: ¿puede editar elementos en el Equipo A, aprobar solicitudes en el Equipo B o solo ver?

La herencia de roles es más fácil de aceptar cuando sigue una regla: un rol de org aplica en todas partes a menos que un equipo lo diga explícitamente. Eso mantiene el comportamiento predecible y reduce datos duplicados.

Una manera limpia de modelarlo es almacenar asignaciones de rol con un scope:

• role_assignments: user_id, org_id, team_id opcional (NULL significa a nivel org), role_id, created_at, created_by

Si quieres “un rol por scope”, añade una restricción única en (user_id, org_id, team_id).

Entonces el acceso efectivo para un equipo se calcula:

1. Busca una asignación específica de equipo (team_id = X). Si existe, úsala.

2. Si no existe, recurre a la asignación a nivel de org (team_id IS NULL).

Para comportamientos de mínimo privilegio, elige un rol org mínimo (a menudo “Member”) y no le des poderes administrativos ocultos. Los usuarios nuevos no deberían obtener acceso implícito a equipos a menos que el producto lo requiera; si haces concesiones automáticas, hazlo creando membresías de equipo explícitas.

Las excepciones deben ser raras y obvias. Ejemplo: María es “Manager” de la org (puede invitar y ver informes), pero en el equipo de Finanzas debe ser “Viewer”. Guardas una asignación org-wide para María y una anulación con scope de equipo para Finanzas. Sin copia de permisos y con la excepción visible.

Los nombres de roles funcionan bien para patrones comunes. Usa permisos explícitos solo cuando tengas casos únicos (como “puede exportar pero no editar”) o cuando cumplimiento requiere una lista clara de acciones permitidas. Aun así, mantén la misma idea de scope para no romper el modelo mental.

Cambios aptos para auditoría: registrar quién cambió accesos

Si tu app solo almacena el rol actual en una fila de membresía, pierdes la historia. Cuando alguien pregunta “¿Quién dio a Alex acceso de admin el martes pasado?” no tienes una respuesta fiable. Necesitas historial de cambios, no solo estado actual.

El enfoque más simple es una tabla de auditoría dedicada que registre eventos de acceso. Trátala como un diario append-only: nunca edites filas antiguas de auditoría; solo añade nuevas.

Una tabla práctica de auditoría suele incluir:

• actor_user_id (quién hizo el cambio)
• subject_type y subject_id (membership, team, org)
• action (invite_sent, role_changed, membership_suspended, team_deleted)
• occurred_at (cuándo ocurrió)
• reason (texto libre opcional como “offboarding de contratista”)

Para capturar “antes” y “después”, guarda una pequeña instantánea de los campos que te importan. Limítala a datos de control de acceso, no a perfiles completos. Por ejemplo: before_role, after_role, before_state, after_state, before_team_id, after_team_id. Si prefieres flexibilidad, usa dos columnas JSON (before, after), pero mantén la carga pequeña y consistente.

Para memberships y teams, el borrado suave suele ser mejor que el borrado físico. En lugar de eliminar la fila, márcala deshabilitada con campos como deleted_at y deleted_by. Esto mantiene las claves intactas y facilita explicar accesos pasados. El borrado físico puede tener sentido para registros realmente temporales (como invitaciones expiradas), pero solo si estás seguro de que no los necesitarás después.

Con esto en marcha, puedes responder preguntas de cumplimiento rápidamente:

• ¿Quién concedió o quitó acceso y cuándo?
• ¿Qué cambió exactamente (rol, equipo, estado)?
• ¿Se quitó el acceso como parte de un offboarding normal?

Paso a paso: diseñando el esquema en una BD relacional

Empieza simple: un lugar para decir quién pertenece a qué y por qué. Contrúyelo en pasos pequeños y añade reglas para que los datos no se desvíen hacia “casi correctos”.

Un orden práctico que funciona bien en PostgreSQL y otras BD relacionales:

1. Crea organizations y teams, cada una con una PK estable (UUID o bigint). Añade teams.organization_id como FK y decide pronto si los nombres de team deben ser únicos dentro de una org.

2. Mantén users separado de membership. Pon campos de identidad en users (email, status, created_at). Pon “pertenece a org/equipo” en una tabla memberships con user_id, organization_id, team_id opcional y una columna state (active, suspended, removed).

3. Añade invitations como su propia tabla, no como columna en membership. Guarda organization_id, team_id opcional, email, token, expires_at y accepted_at. Forza unicidad para “una invitación abierta por org + email + team” para no crear duplicados.

4. Modela roles con tablas explícitas. Una aproximación simple es roles (admin, member, etc.) más role_assignments que apunten a scope de org (sin team_id) o scope de team (team_id seteado). Mantén reglas de herencia consistentes y testeables.

5. Añade desde el día uno una traza de auditoría. Usa una tabla access_events con actor_user_id, target_user_id (o email para invitaciones), action (invite_sent, role_changed, removed), scope (org/team) y created_at.

Después de crear estas tablas, ejecuta un par de consultas administrativas básicas para validar la realidad: “¿quién tiene acceso a nivel org?”, “¿qué equipos no tienen admins?” y “¿qué invitaciones están expiradas pero aún abiertas?” Esas preguntas suelen revelar constraints faltantes temprano.

Reglas y restricciones que previenen datos desordenados

Un esquema se mantiene sano cuando la base de datos, no solo tu código, aplica las fronteras de tenant. La regla más simple: cada tabla con scope tenant lleva org_id y cada lookup lo incluye. Incluso si alguien olvida un filtro en la app, la base de datos debe resistir conexiones entre orgs.

Guardarraíles que mantienen los datos limpios

Empieza con foreign keys que siempre apunten “dentro de la misma org.” Por ejemplo, si almacenas membresía de equipo por separado, una fila team_memberships debería referenciar team_id y user_id, pero también llevar org_id. Con claves compuestas puedes forzar que el team referenciado pertenezca a la misma org.

Constraints que evitan los problemas más comunes:

• Una membresía org activa por usuario por org: unique en (org_id, user_id) con condición parcial para filas activas (cuando sea soportado).
• Una invitación pendiente por email por org o team: unique en (org_id, team_id, email) donde state = 'pending'.
• Tokens de invitación globalmente únicos y nunca reutilizados: unique en invite_token.
• Un team pertenece a exactamente una org: teams.org_id NOT NULL con FK a orgs(id).
• Termina membresías en lugar de borrarlas: guarda ended_at (y opcionalmente ended_by) para proteger el historial de auditoría.

Indexado para las consultas que realmente ejecutas

Indexa las consultas que tu app hace todo el tiempo:

• (org_id, user_id) para “¿en qué orgs está este usuario?”
• (org_id, team_id) para “listar miembros de este equipo”
• (invite_token) para “aceptar invitación”
• (org_id, state) para “invitaciones pendientes” y “miembros activos”

Mantén los nombres de org editables. Usa un orgs.id inmutable en todas partes y trata orgs.name (y cualquier slug) como campos editables. Renombrar toca una sola fila.

Mover un team entre orgs suele ser una decisión de política. La opción más segura es prohibirlo (o clonar el team) porque las membresías, roles e historial son scoped a la org. Si debes permitir movimientos, hazlo en una sola transacción y actualiza todas las filas hijas que llevan org_id.

Para evitar registros huérfanos cuando los usuarios se van, evita borrados duros. Deshabilita al usuario, termina sus membresías y restringe borrados en filas padre (ON DELETE RESTRICT) a menos que realmente quieras cascada.

Escenario de ejemplo: una org, dos equipos y cambios de acceso seguros

Imagina una empresa llamada Northwind Co con una org y dos teams: Sales y Support. Contratan a una contratista, Mia, para ayudar en tickets de Support por un mes. Aquí el modelo debe seguir siendo predecible: una persona, una membresía org, membresías de equipo opcionales y estados claros.

Un admin de org (Ava) invita a Mia por email. El sistema crea una fila de invitación ligada a la org, con estado pending y una fecha de expiración. Aún no cambia nada más, así que no hay un “usuario a medio crear” con acceso incierto.

Cuando Mia acepta, la invitación se marca accepted y se crea una fila de membership org con estado active. Ava le asigna a Mia el rol org member (no admin). Luego Ava añade la membresía al equipo Support y asigna un rol de equipo como support_agent.

Añadamos un giro: Ben es empleado a tiempo completo con rol org admin, pero no debe ver datos de Support. Puedes manejar eso con una anulación a nivel de equipo que degrade su rol en Support mientras mantiene sus capacidades de admin a nivel org.

Una semana después Mia incumple la política y la suspenden. En lugar de eliminar filas, Ava pone el estado de membership de Mia en suspended. Las membresías de equipo pueden permanecer pero se vuelven inefectivas porque la membership org no está activa.

El historial de auditoría se mantiene limpio porque cada cambio es un evento:

• Ava invitó a Mia (quién, qué, cuándo)
• Mia aceptó la invitación
• Ava añadió a Mia a Support y le asignó support_agent
• Ava puso la anulación de Ben en Support
• Ava suspendió a Mia

Con este modelo, la UI puede mostrar un resumen de acceso claro: estado org (active o suspended), rol org, lista de equipos con roles y anulaciones, y un feed de “Cambios de acceso recientes” que explique por qué alguien puede o no ver Sales o Support.

Errores comunes y trampas a evitar

La mayor parte de bugs de acceso proviene de modelos “casi correctos”. El esquema parece bien al principio y luego los casos borde se acumulan: re-invites, movimientos de equipos, cambios de rol y offboarding.

Una trampa común es mezclar invitaciones y membresías en la misma fila. Si guardas “invited” y “active” en el mismo registro sin un significado claro, acabas con preguntas imposibles como “¿es miembro si nunca aceptó?” Mantén invitaciones y membresías separadas o haz la máquina de estados explícita y consistente.

Otro error frecuente es poner una sola columna role en la tabla users y dar por terminado el tema. Los roles casi siempre están scopeados (rol de org, rol de equipo, rol de proyecto). Un rol global fuerza hacks como “usuario es admin para un cliente pero solo lectura para otro”, lo que rompe las expectativas multi-inquilino y causa dolores de soporte.

Trampas que suelen doler más tarde:

• Permitir por accidente membresía cruzada entre orgs (team_id apunta a la org A, membership apunta a la org B).
• Borrar físicamente memberships y perder “¿quién tenía acceso la semana pasada?”.
• No tener reglas de unicidad y que un usuario obtenga acceso duplicado mediante filas idénticas.
• Dejar que la herencia se acumule silenciosamente (org admin más miembro de equipo más anulación) hasta que nadie pueda explicar por qué existe un acceso.
• Tratar “aceptar invitación” como un evento de UI y no como un hecho de base de datos.

Un ejemplo rápido: un contratista es invitado a una org, se une al Team Sales, luego lo quitan y lo vuelven a invitar un mes después. Si sobrescribes la fila antigua, pierdes historial. Si permites duplicados, puede acabar con dos memberships activas. Estados claros, roles scopeados y restricciones adecuadas evitan ambos problemas.

Comprobaciones rápidas y siguientes pasos para integrarlo en tu app

Antes de codificar, haz un repaso rápido de tu modelo y mira si sigue teniendo sentido en papel. Un buen modelo de acceso multi-inquilino debería sentirse aburrido: las mismas reglas aplican en todas partes y los “casos especiales” son raros.

Una lista rápida para captar huecos comunes:

• Cada membership apunta exactamente a un user y una org, con una restricción única para evitar duplicados.
• Invitación, membresía y estados de baja son explícitos (no implícitos por nulos) y las transiciones están limitadas (por ejemplo, no puedes aceptar una invitación expirada).
• Los roles se guardan en un solo lugar y el acceso efectivo se calcula consistentemente (incluyendo reglas de herencia si las usas).
• Borrar orgs/teams/users no borra la historia (usa soft delete o campos de archivo cuando necesites trazas de auditoría).
• Cada cambio de acceso emite un evento de auditoría con actor, objetivo, scope, timestamp y razón/fuente.

Pon a prueba el diseño con preguntas reales. Si no puedes responderlas con una consulta y una regla clara, probablemente necesites una restricción o un estado extra:

• ¿Qué pasa si un usuario es invitado dos veces y luego cambia el email?
• ¿Puede un admin de equipo quitar a un owner de la org de ese equipo?
• Si un rol de org da acceso a todos los equipos, ¿puede un equipo anularlo?
• Si una invitación se acepta después de que el rol cambió, ¿qué rol aplica?
• Cuando soporte pregunta “quién quitó el acceso”, ¿puedes probarlo rápido?

Escribe qué deben entender admins y soporte: estados de membresía (y qué los dispara), quién puede invitar/quitar, qué significa la herencia de roles en lenguaje llano y dónde mirar eventos de auditoría durante un incidente.

Implementa las constraints primero (unicidad, foreign keys, transiciones permitidas) y luego construye la lógica de negocio alrededor para que la base de datos te ayude a mantener la corrección. Mantén decisiones de política (herencia activada/desactivada, roles por defecto, expiración de invitaciones) en tablas de configuración más que en constantes de código.

Si quieres construir esto sin escribir a mano cada backend y pantalla de administración, AppMaster (appmaster.io) puede ayudarte a modelar estas tablas en PostgreSQL e implementar transiciones de invitación y membresía como procesos de negocio explícitos, manteniendo la posibilidad de generar código real para despliegues en producción.