The B2B SaaS data model: organizations, memberships, invites, and roles

The six tables

This is the model our three kits ship. It is small on purpose — everything else in a multi-tenant app is downstream of these six.

users          id, email (unique)
organizations  id, name, slug (unique)
memberships    id, org_id, user_id, role
invites        id, org_id, token_hash, role, expires_at_ms
sessions       id, user_id, expires_at_ms
audit_log      id, org_id, actor, action, target, metadata, at

users holds identity and nothing else. organizations holds the tenant. memberships is the join. invites is the onboarding path.sessions is authentication. audit_log is the record.

The decision that matters: the role belongs to the membership

The most common mistake in this schema is putting role on theusers table. It looks simpler and it is wrong, because a role is not a property of a person — it is a property of a person's membership in one specific organization.

Our kits declare role on memberships, constrained to'owner' | 'admin' | 'member'. Three roles is a deliberate ceiling, not a starting point — a permissions engine built before you have three real roles is a rewrite you pay for later.

Constrain the join in the database, not in the handler

"A user should have at most one membership per organization" is a rule that belongs to a unique index, not to a SELECT followed by an optimistic INSERT. Our schema has both directions covered:

unique (org_id, user_id)   -- one membership per user per org
index    (user_id)        -- "which orgs am I in?" runs on every page load

The second index is the one people skip. Without it, every authenticated request that needs to show an org switcher scans the memberships table, and the app gets slower exactly as it gets more popular.

Invites: four properties, and all four are required

An invite is a security feature wearing an onboarding costume. Our service treats it as one, and each property below is a specific bug it prevents.

The last one is worth being precise about, because it is the failure that survives code review. Accepting an invite is not one operation — it takes a row lock, checks the seat count, and inserts a membership. Run those separately and a concurrent accept interleaves between them. Our implementation runs all of it in one transaction, with the per-organization lock as its first statement, so a second accept waits rather than racing.

The same function returns typed states rather than a boolean —invalid_token, expired, revoked,already_accepted, already_member, bad_role. That list is the actual specification of invite behaviour, and it is the thing you want in a return type when you are debugging at 2am.

The audit log must outlive the rows it describes

A tempting design gives audit_log a foreign key toorganizations and to users. Then you delete a user, and the question "who removed the last production credential" has no answer, because the evidence was cascade-deleted along with the person who did it.

Our audit_log carries no foreign key precisely so history survives member and org removal. It is also append-only: if your application can UPDATE an audit row, it is not an audit log, it is a changelog someone can edit.

The operational questions the golden schema skips

These are the parts that no schema diagram shows and every team hits eventually.

• Ownership transfer. The last owner leaves the company. Either you block deletion of the final owner, or you make the last remaining admin an owner. Decide which, before an offboarding forces the decision for you.
• The solo admin. A one-person organization is a real case, not an edge case. Any rule of the form "an organization must have an admin" needs to tolerate one human who is also the owner.
• Invite rollback. You invited the wrong person, or the wrong address. If your invite is single-use and immutable, revocation has to be an explicit state (revoked) rather than a delete — otherwise your audit log has a hole in it.
• Seat changes mid-cycle. Decoupling "users in your database" from "seats you are paying for" is what makes downgrades and upgrades survivable. That is a billing problem, but it is decided here, in the data model.

What this looks like in a real framework

The schema above is Postgres and is framework-neutral. What you write around it — the handler that accepts an invite, the middleware that resolves the current organization — is where frameworks differ.

We ship SvelteKit, and the three kits are identical in this layer over SQLite, Supabase and Postgres, so the examples we publish are ones we actually run in CI. If you are building this in Django, Rails, Laravel, NestJS, Next.js or Go, the tables and the constraints transfer unchanged; the request plumbing is yours to write. How the same six tables map across stacks →