Identity Data Modeling: The Decision You Cannot Cheaply Undo

The most expensive four bytes in B2B SaaS

The failure is not that the column is wrong on day one. It is usually right on day one. The failure is that a schema is a set of claims about the world, and this one claims something the world eventually contradicts. A contractor works for two of your customers. An agency manages five. A customer acquires another customer and wants the accounts merged. Somebody signs up with a personal address and later joins a corporate workspace.

Each of those is a support ticket in a one-org model and a row in a many-org model.

Four objects, and what each one owns

A workable B2B identity model has four entities. The discipline is keeping their responsibilities separate, because almost every design mistake in this area is an attribute stored on the wrong one.

The person. A globally unique human with credentials. Email, authentication factors, name, locale, and nothing organization-specific. If an attribute would have a different value in a different organization, it does not belong here.

The organization. The customer. Billing relationship, domain claims, identity-provider configuration, feature flags, data residency, and policy settings. Named organization, tenant, workspace, or account depending on your product's vocabulary, and worth naming once and consistently because it leaks into your API forever.

The membership. The join between a person and an organization, and the object almost nobody creates early enough. It owns the role, the status, the invitation metadata, the provisioning source, the join date, and any per-organization profile such as a job title or a display name. It is a first-class entity with its own lifecycle, not a link table.

The role or permission grant. What a membership is allowed to do. In simple products this is an enum on the membership. In products with resource-level sharing it becomes its own table, and eventually a relationship model along the lines described in RBAC vs ABAC vs ReBAC.

A minimal shape:

users (
  id, email, email_verified_at, created_at
)
 
organizations (
  id, name, slug, created_at
)
 
memberships (
  id,
  user_id        NULL,      -- null while an invitation is pending
  organization_id,
  invited_email  NULL,      -- set while pending, cleared on accept
  role,
  status,                   -- pending | active | suspended | removed
  source,                   -- manual | invitation | scim | jit | domain_claim
  external_id    NULL,      -- the IdP's identifier when source = scim
  invited_by, invited_at, accepted_at, removed_at,
  UNIQUE (user_id, organization_id)
)

The source column is the one teams add last and need most. Without it, you cannot tell a manually created membership from one the customer's identity provider owns, and that distinction determines whether your own admin UI is allowed to edit the row.

Status is what makes the model work

Membership status carries more weight than any other field, because it is what lets one table serve invitation, suspension, and removal without any of those becoming a special case.

Pending. The invitation. The row exists, user_id is null, invited_email holds the address, and the intended role is already decided. No user record is created. This is the detail that keeps invitations from polluting everything downstream: phantom users inflate seat counts, appear in admin lists, break "user must have a verified email" invariants, and get counted in billing. A pending membership is invisible to all of it while still being a real, queryable object with an expiry.

On acceptance, the invitee authenticates, a user record is created or matched, user_id is populated, invited_email is cleared, and status becomes active. One transaction.

Active. The normal state.

Suspended. Access revoked, relationship retained. This matters more than it looks: an organization that suspends a departing employee usually wants their history, comments, and ownership of records to survive. Deleting the membership orphans all of it.

Removed. Soft-deleted with a timestamp, for audit. Hard deletion is a data-subject-request path, not an offboarding path.

Where identity actually lives

The most important boundary decision in a B2B identity architecture is which system is authoritative for which object. Getting it wrong does not break anything immediately. It shows up later as a reporting query you cannot write, a permission check that fails during a vendor outage, and a migration with no export path. The split that holds up is narrower than most vendor documentation suggests.

The vendor is authoritative for authentication. Who this person is, what factors they used, when they last logged in, and whether the assertion from their employer's identity provider is valid. That is genuinely hard, genuinely commodity, and correctly outsourced.

You are authoritative for organization, membership, and role. These are business objects. They join against your data, they appear in your reports, they drive your billing, and they change on your product's schedule rather than your vendor's.

Storing roles and organization structure inside a vendor's user metadata feels efficient, and it produces three problems. You cannot join it against anything. You cannot query it in bulk without paginating an API. And it becomes the part of a migration that has no export path, which is the same asymmetry described in password hash migration.

The SCIM constraint on membership

Once an enterprise customer connects their identity provider, the ownership of membership changes for that organization, and your data model has to represent the change explicitly.

The provider becomes authoritative for who is in the organization. It creates memberships when IT adds someone to a group, deactivates them on offboarding, and reconciles on every sync. If a group mapping drives roles, it is authoritative for those too. Any edit made in your own admin UI is overwritten the next time the sync runs, which is the silent-failure version of the worst possible behavior: the admin sees their change succeed, and it disappears hours later with no notification.

Three requirements fall out of this.

1. Mark the source. Every membership records whether it came from SCIM, an invitation, just-in-time provisioning on SSO login, or a manual admin action.
2. Make provider-owned rows read-only in your UI. Not hidden. Visible, uneditable, and labelled with the reason and where to go instead.
3. Reconcile and alarm. Compare your membership set against the provider's periodically, and alert on divergence and on the absence of sync events. SCIM provisioning covers the protocol; the operational point is that a stalled sync produces no error at all.

Just-in-time provisioning needs the same treatment with an added subtlety. A JIT membership is created by the act of logging in, so it cannot be deprovisioned by the absence of logging in. A product with JIT and no SCIM has an onboarding mechanism and no offboarding mechanism, which is exactly the gap an enterprise security questionnaire is written to find.

The question to settle on day one

One decision has no cheap reversal, and it is worth making deliberately: is a person one global account with many memberships, or one account per organization?

Global account, many memberships. One credential, an organization switcher, and per-organization state on the membership. This is what most modern B2B products do, and it is what users expect after using Slack, Notion, and Figma. It requires you to answer what happens when the same human is invited under two different email addresses, and to accept that a person's authentication factors are shared across every organization they belong to.

Isolated account per organization. The same human authenticating separately in each tenant, with no shared credential. Correct when regulatory separation, per-tenant data residency, or hard isolation guarantees demand it, and painful for everyone else. It multiplies password resets, multiplies MFA enrollments, and makes the switcher impossible.

The hybrid that causes trouble is a global account with tenant-scoped credentials, where authentication is shared but factor enrollment is per-organization. It reads as flexible on a whiteboard and produces a recovery flow nobody can reason about.

Whichever you choose, write the decision down with its reasoning, because it will be questioned by someone who was not there, and the answer determines the shape of organizations and tenants, the multi-tenant architecture, and every authorization check you will ever write.

A migration path if the column is already there

Most teams read this with a tenant_id column already in production. It is recoverable, and the order matters.

1. Add the membership table and backfill it from the existing column, one row per user. Nothing changes behaviorally.
2. Add a dual-read path. New code reads membership. Old code keeps reading the column. Both are populated by writes.
3. Move the token. Stop putting a single organization in the session or access token as a permanent fact. Carry an active organization that the user can change, and re-derive permissions from membership on each request or from a cache keyed by user and organization together.
4. Convert call sites by module, with the authorization layer first, then reporting, then anything that renders a user's organization.
5. Make the column non-authoritative, then stop writing it, then drop it.
6. Only then ship multi-org membership in the UI. The schema change and the feature are two separate releases, and combining them is how a backfill error becomes a cross-tenant data exposure.

Step three is the one that takes the longest, because a tenant_id claim in a long-lived token tends to have been treated as immutable, and a surprising amount of code will have come to rely on that.