B2B organizations and teams database schema that stays sane

What problem this schema pattern solves

Most B2B apps aren’t really “user accounts” apps. They’re shared workspaces where people belong to an organization, split into teams, and get different permissions depending on their job. Sales, support, finance, and admins need different access, and that access changes over time.

A too-simple model breaks quickly. If you keep one users table with a single role column, you can’t express “the same person is an Admin in one org, but a Viewer in another.” You also can’t handle common cases like contractors who should only see one team, or an employee who leaves a project but still belongs to the company.

Invites are another frequent source of bugs. If an invitation is just an email row, it becomes unclear whether the person is “in” the org yet, which team they’re supposed to join, and what happens if they sign up with a different email. Small inconsistencies here tend to turn into security problems.

This pattern aims for four goals:

• Security: permissions come from explicit membership, not assumptions.
• Clarity: orgs, teams, and roles each have one source of truth.
• Consistency: invitations and memberships follow a predictable lifecycle.
• History: you can explain who granted access, changed roles, or removed someone.

The promise is a single relational model that stays understandable as features grow: multiple orgs per user, multiple teams per org, predictable role inheritance, and audit-friendly changes. It’s a structure you can implement today and extend later without rewriting everything.

Key terms: orgs, teams, users, and memberships

If you want a schema that’s still readable after six months, start by agreeing on a few words. Most confusion comes from mixing “who someone is” with “what they can do.”

An Organization (org) is the top tenant boundary. It represents the customer or business account that owns data. If two users are in different orgs, they should not see each other’s data by default. That one rule prevents a lot of accidental cross-tenant access.

A Team is a smaller group inside an org. Teams model real working units: Sales, Support, Finance, or “Project A.” Teams don’t replace the org boundary; they live under it.

A User is an identity. It’s the person’s login and profile: email, name, password or SSO ID, and maybe MFA settings. A user can exist without access to anything yet.

A Membership is the access record. It answers: “This user belongs to this org (and optionally this team) with this status and these roles.” Keeping identity (User) separate from access (Membership) makes contractors, offboarding, and multi-org access much easier to model.

Plain meanings you can use in code and UI:

• Member: a user with an active membership in an org or team.
• Role: a named bundle of permissions (for example, Org Admin, Team Manager).
• Permission: a single allowed action (for example, “view invoices”).
• Tenant boundary: the rule that data is scoped to an org.

Treat membership as a small state machine, not a boolean. Typical states are invited, active, suspended, and removed. This keeps invitations, approvals, and offboarding consistent and auditable.

The single relational model: core tables and relationships

A good multi-tenant schema starts with one idea: store “who belongs where” in one place, and keep everything else as supporting tables. That way you can answer basic questions (who is in the org, who is in a team, what can they do) without hopping across unrelated models.

Core tables you usually need:

• organizations: one row per customer account (tenant). Holds name, status, billing fields, and an immutable id.
• teams: groups inside an organization (Support, Sales, Admin). Always belongs to one organization.
• users: one row per person. This is global, not per organization.
• memberships: the bridge that says “this user belongs to this organization” and optionally “also to this team.”
• role_grants (or role_assignments): what roles a membership has, at org level, team level, or both.

Keep keys and constraints strict. Use surrogate primary keys (UUIDs or bigints) for each table. Add foreign keys such as teams.organization_id -> organizations.id and memberships.user_id -> users.id. Then add a few unique constraints to stop duplicates before they show up in production.

Rules that catch most bad data early:

• One org slug or external key: unique(organizations.slug)
• Team names per org: unique(teams.organization_id, teams.name)
• No duplicate org membership: unique(memberships.organization_id, memberships.user_id)
• No duplicate team membership (only if you model team membership separately): unique(team_memberships.team_id, team_memberships.user_id)

Decide what’s append-only versus updatable. Organizations, teams, and users are updatable. Memberships are often updatable for current state (active, suspended), but changes should also write to an append-only access log so audits are straightforward later.

Invitations and membership states that stay consistent

The easiest way to keep access clean is to treat an invitation as its own record, not a half-made membership. A membership means “this user currently belongs.” An invitation means “we offered access, but it isn’t real yet.” Keeping them separate avoids ghost members, half-created permissions, and “who invited this person?” mysteries.

A simple, reliable state model

For memberships, use a small set of states you can explain to anyone:

• active: the user can access the org (and any teams they’re a member of)
• suspended: temporarily blocked, but history stays intact
• removed: no longer a member, kept for audit and reporting

Many teams avoid a membership state of “invited” and instead keep “invited” strictly in the invitations table. That tends to be cleaner: membership rows exist only for users who actually have access (active), or who used to have it (suspended/removed).

Email invites before an account exists

B2B apps often invite by email when no user account exists yet. Store the email on the invitation record, along with where the invite applies (org or team), the intended role, and who sent it. If the person later signs up with that email, you can match pending invitations and let them accept.

When an invite is accepted, handle it in one transaction: mark the invitation as accepted, create the membership, and write an audit entry (who accepted, when, and which email was used).

Define clear invitation end states:

• expired: past its deadline and cannot be accepted
• revoked: canceled by an admin and cannot be accepted
• accepted: converted into a membership

Prevent duplicate invites by enforcing “only one pending invite per org or team per email.” If you support re-invites, either extend the expiry on the existing pending invite or revoke the old one and issue a new token.

Roles and inheritance without making access confusing

Most B2B apps need two levels of access: what someone can do in the organization as a whole, and what they can do inside a specific team. Mixing these into one role field is where apps start to feel inconsistent.

Org-level roles answer questions like: can this person manage billing, invite people, or see all teams? Team-level roles answer: can they edit items in Team A, approve requests in Team B, or only view?

Role inheritance is easiest to live with when it follows one rule: an org role applies everywhere unless a team explicitly says otherwise. That keeps behavior predictable and reduces duplicate data.

A clean way to model this is to store role assignments with a scope:

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

If you want “one role per scope,” add a unique constraint on (user_id, org_id, team_id).

Then effective access for a team becomes:

1. Look for a team-specific assignment (team_id = X). If it exists, use it.

2. Otherwise, fall back to the org-wide assignment (team_id IS NULL).

For least-privilege defaults, pick a minimal org role (often “Member”) and don’t give it hidden admin powers. New users shouldn’t get implicit team access unless your product truly needs it. If you do auto-grant, do it by creating explicit team memberships, not by quietly widening the org role.

Overrides should be rare and obvious. Example: Maria is an org “Manager” (can invite, see reports), but in the Finance team she should be “Viewer.” You store one org-wide assignment for Maria, plus one team-scoped override for Finance. No permission copying, and the exception is visible.

Role names work well for common patterns. Use explicit permissions only when you have true one-offs (like “can export but cannot edit”), or when compliance needs a clear list of allowed actions. Even then, keep the same scope idea so the mental model stays consistent.

Audit-friendly changes: tracking who changed access

If your app stores only the current role on a membership row, you lose the story. When someone asks, “Who gave Alex admin access last Tuesday?” you have no reliable answer. You need change history, not just current state.

The simplest approach is a dedicated audit log table that records access events. Treat it as an append-only journal: you never edit old audit rows; you only add new ones.

A practical audit table usually includes:

• actor_user_id (who made the change)
• subject_type and subject_id (membership, team, org)
• action (invite_sent, role_changed, membership_suspended, team_deleted)
• occurred_at (when it happened)
• reason (optional free text like “contractor offboarding”)

To capture “before” and “after,” store a small snapshot of the fields you care about. Keep it limited to access-control data, not full user profiles. For example: before_role, after_role, before_state, after_state, before_team_id, after_team_id. If you prefer flexibility, use two JSON columns (before, after), but keep the payload small and consistent.

For memberships and teams, soft delete is usually better than hard delete. Instead of removing the row, mark it disabled with fields like deleted_at and deleted_by. This keeps foreign keys intact and makes it easier to explain past access. Hard delete can still make sense for truly temporary records (like expired invites), but only if you’re sure you won’t need them later.

With this in place, you can answer common compliance questions quickly:

• Who granted or removed access, and when?
• What exactly changed (role, team, status)?
• Was access removed as part of a normal offboarding flow?

Step by step: designing the schema in a relational database

Start simple: one place to say who belongs to what, and why. Build it in small steps, and add rules as you go so the data can’t drift into “almost correct.”

A practical order that works well in PostgreSQL and other relational databases:

1. Create organizations and teams, each with a stable primary key (UUID or bigint). Add teams.organization_id as a foreign key, and decide early whether team names must be unique inside an org.

2. Keep users separate from membership. Put identity fields in users (email, status, created_at). Put “belongs to org/team” in a memberships table with user_id, organization_id, optional team_id (if you model it that way), and a state column (active, suspended, removed).

3. Add invitations as its own table, not a column on membership. Store organization_id, optional team_id, email, token, expires_at, and accepted_at. Enforce uniqueness for “one open invite per org + email + team” so you don’t create duplicates.

4. Model roles with explicit tables. A simple approach is roles (admin, member, etc.) plus role_assignments that point to either org scope (no team_id) or team scope (team_id set). Keep inheritance rules consistent and testable.

5. Add an audit trail from day one. Use an access_events table with actor_user_id, target_user_id (or email for invites), action (invite_sent, role_changed, removed), scope (org/team), and created_at.

After these tables exist, run a couple of basic admin queries to validate reality: “who has org-wide access?”, “which teams have no admins?”, and “which invites are expired but still open?” Those questions tend to reveal missing constraints early.

Rules and constraints that prevent messy data

A schema stays sane when the database, not just your code, enforces tenant boundaries. The simplest rule is: every tenant-scoped table carries org_id, and every lookup includes it. Even if someone forgets a filter in the app, the database should resist cross-org connections.

Guardrails that keep data clean

Start with foreign keys that always point “within the same org.” For example, if you store team membership separately, a team_memberships row should reference a team_id and a user_id, but also carry org_id. With composite keys, you can enforce that the referenced team belongs to the same org.

Constraints that prevent the most common problems:

• One active org membership per user per org: unique on (org_id, user_id) with a partial condition for active rows (where supported).
• One pending invite per email per org or team: unique on (org_id, team_id, email) where state = 'pending'.
• Invite tokens are globally unique and never reused: unique on invite_token.
• Team belongs to exactly one org: teams.org_id NOT NULL with a foreign key to orgs(id).
• End memberships instead of deleting them: store ended_at (and optionally ended_by) to protect audit history.

Indexing for the lookups you actually do

Index the queries your app runs all the time:

• (org_id, user_id) for “what orgs is this user in?”
• (org_id, team_id) for “list members of this team”
• (invite_token) for “accept invite”
• (org_id, state) for “pending invites” and “active members”

Keep org names movable. Use an immutable orgs.id everywhere, and treat orgs.name (and any slug) as editable fields. Renaming then touches one row.

Moving a team between orgs is usually a policy decision. The safest option is to forbid it (or clone the team) because memberships, roles, and audit history are org-scoped. If you must allow moves, do it in a single transaction and update all child rows that carry org_id.

To prevent orphan records when users leave, avoid hard deletes. Disable the user, end their memberships, and restrict deletes on parent rows (ON DELETE RESTRICT) unless you truly want cascading removal.

Example scenario: one org, two teams, changing access safely

Imagine a company called Northwind Co with one org and two teams: Sales and Support. They hire a contractor, Mia, to help with Support tickets for one month. This is where the model should stay predictable: one person, one org membership, optional team memberships, and clear states.

An org admin (Ava) invites Mia by email. The system creates an invitation row tied to the org, with status pending and an expiry date. Nothing else changes yet, so there’s no “half user” with unclear access.

When Mia accepts, the invitation is marked accepted, and an org membership row is created with state active. Ava sets Mia’s org role to member (not admin). Then Ava adds a Support team membership and assigns a team role like support_agent.

Now add one twist: Ben is a full-time employee with an org role of admin, but he should not see Support data. You can handle that with a team-level override that explicitly downgrades his team role for Support while keeping his org-wide admin abilities for org settings.

A week later, Mia violates policy and is suspended. Instead of deleting rows, Ava sets Mia’s org membership state to suspended. Team memberships can stay in place but become ineffective because the org membership is not active.

Audit history stays clean because each change is an event:

• Ava invited Mia (who, what, when)
• Mia accepted the invite
• Ava added Mia to Support and assigned support_agent
• Ava set Ben’s Support override
• Ava suspended Mia

With this model, the UI can show a clear access summary: org status (active or suspended), org role, team list with roles and overrides, and a “Recent access changes” feed that explains why someone can or cannot see Sales or Support.

Common mistakes and traps to avoid

Most access bugs come from “almost right” data models. The schema looks fine at first, then edge cases pile up: re-invites, team moves, role changes, and offboarding.

A common trap is mixing invitations and memberships in one row. If you store “invited” and “active” in the same record without clear meaning, you end up asking impossible questions like “Is this person a member if they never accepted?” Keep invitations and memberships separate, or make the state machine explicit and consistent.

Another frequent mistake is putting a single role column on the user table and calling it done. Roles are almost always scoped (org role, team role, project role). A global role forces hacks like “user is admin for one customer, but read-only for another,” which breaks multi-tenant expectations and creates support headaches.

Traps that usually hurt later:

• Allowing cross-org team membership by accident (team_id points to org A, membership points to org B).
• Hard deleting memberships and losing the “who had access last week?” trail.
• Missing uniqueness rules so a user gets duplicate access via identical rows.
• Letting inheritance stack silently (org admin plus team member plus override) so nobody can explain why access exists.
• Treating “invite accepted” as a UI event, not a database fact.

A quick example: a contractor is invited to an org, joins Team Sales, then gets removed and re-invited a month later. If you overwrite the old row, you lose history. If you allow duplicates, they may end up with two active memberships. Clear states, scoped roles, and the right constraints prevent both.

Quick checks and next steps for building it into your app

Before you code, do a quick pass over your model and see if it still makes sense on paper. A good multi-tenant access model should feel boring: the same rules apply everywhere, and “special cases” are rare.

A fast checklist to catch common gaps:

• Every membership points to exactly one user and one org, with a unique constraint to prevent duplicates.
• Invitation, membership, and removal states are explicit (not implied by nulls), and transitions are limited (for example, you can’t accept an expired invite).
• Roles are stored in one place and effective access is computed consistently (including inheritance rules, if you use them).
• Deleting orgs/teams/users doesn’t erase history (use soft delete or archival fields where you need audit trails).
• Every access change emits an audit event with actor, target, scope, timestamp, and reason/source.

Pressure-test the design with real questions. If you can’t answer these with one query and a clear rule, you probably need a constraint or an extra state:

• What happens if a user is invited twice, then the email changes?
• Can a team admin remove an org owner from that team?
• If an org role grants access to all teams, can one team override it?
• If an invite is accepted after a role was changed, which role applies?
• When support asks “who removed access,” can you prove it quickly?

Write down what admins and support staff must understand: membership states (and what triggers them), who can invite/remove, what role inheritance means in plain language, and where to look for audit events during an incident.

Implement constraints first (uniques, foreign keys, allowed transitions), then build business logic around them so the database helps keep you honest. Keep policy decisions (inheritance on/off, default roles, invite expiry) in config tables rather than code constants.

If you want to build this without hand-writing every backend and admin screen, AppMaster (appmaster.io) can help you model these tables in PostgreSQL and implement invite and membership transitions as explicit business processes, while still generating real source code for production deployments.