Building Multi-Tenant Support in a User-Based SaaS App (case study)

This case study explains how a simple user-owned SaaS application was migrated to a multi-tenant architecture that supports organizations, teams, shared resource ownership, tenant isolation, and role-based permissions.

Table of Contents

• What Problem Were We Solving?
  • Original User-Based Model
  • Why It Worked in the Beginning
  • Why It Started to Fail
• Moving Ownership From Users to Accounts
  • Introducing Accounts
  • New Ownership Model
  • Users vs Accounts vs Resources
• Memberships and Roles
  • The account_user Pivot Table
  • Roles Belong to Memberships
  • Real-World Role Example
• Tenant Isolation
  • Why Tenant Isolation Matters
  • Unsafe Query Example
  • Safe Query Example
• Migration Strategy
  • Create One Account Per Existing User
  • Move Existing Resources to Accounts
  • Why We Removed user_id
• Authorization Refactor
  • Before: Identity-Based Ownership
  • After: Membership-Based Ownership
  • Checking Roles Inside Current Account
• Common Mistakes
  • Forgetting to Scope Queries
  • Treating Roles as Global
  • Keeping Two Ownership Sources
• Final Result
• Key Takeaway

What Problem Were We Solving?

Many SaaS applications start with a simple model: one user signs up, creates data, and owns that data. This is often the right decision for an MVP because the application is easier to build, easier to test, and easier to secure.

The problem appears when the product grows beyond individual usage. Teams want to collaborate, managers want visibility, organizations want separation, and enterprise customers expect role-based permissions. At that point, the application needs multi-tenant support.

Original User-Based Model

The original architecture had no concept of organizations, workspaces, teams, or accounts. There were only users.

users
id | email | password | created_at | ...

Every business resource belonged directly to a user.

projects
id | user_id | name | created_at | ...

The ownership model looked like this:

User
  │
  ▼
Project

This is simple. A project belongs to exactly one user, and that user is the only person who can access it.

Why It Worked in the Beginning

For a personal product, this model works very well. A note-taking app, a personal task manager, or a single-user dashboard can safely use direct user ownership for a long time.

For example, if a user creates a private project, the authorization check is straightforward:

if ($project->user_id === auth()->id()) {
    // allow access
}

The application only needs to answer one question:

Does this resource belong to the current user?

If yes, access is allowed. If no, access is denied.

Why It Started to Fail

The model started to fail when the application needed collaboration. Imagine a marketing agency with 20 employees managing campaigns for multiple clients. If every campaign belongs to one employee, what happens when another employee needs access? What happens when the original employee leaves the company? What happens when a manager needs visibility into all campaigns?

The user-based model cannot answer these questions cleanly because it assumes that ownership and identity are the same thing.

In real SaaS systems, ownership usually belongs to a higher-level entity:

• A Slack message belongs to a workspace.
• A GitHub repository belongs to a user or organization.
• A Jira project belongs to a company workspace.
• A Notion page belongs to a workspace.
• A billing invoice belongs to a customer account.

That is the shift we needed to make.

Moving Ownership From Users to Accounts

The most important architectural decision was simple:

Resources should belong to accounts, not users.

An account represents an organization, company, team, customer, or workspace. Users become members of accounts. Accounts own the data.

Introducing Accounts

The first new concept was the accounts table.

accounts
id | name | created_at | updated_at

An account becomes the tenant boundary. In a multi-tenant SaaS application, a tenant is the unit that separates one customer's data from another customer's data.

For example, if two companies use the same SaaS product, they may both have projects, users, reports, and settings. These records may live in the same database, but they must be separated by tenant ownership.

New Ownership Model

The ownership model changed from this:

User
  │
  ▼
Project

to this:

User
  │
  ▼
Account
  │
  ▼
Project

Now the project belongs to the account, not directly to the user.

projects
id | account_id | name | created_at | ...

This makes collaboration natural. If five users belong to the same account, all five can potentially access the same account-owned projects, depending on their permissions.

Users vs Accounts vs Resources

The new mental model is:

• Users are people who log in.
• Accounts are organizations, teams, customers, or workspaces.
• Resources are business objects owned by accounts.

For example, in a project management SaaS, users are employees, accounts are companies, and resources are projects, tasks, comments, files, and reports.

In an email marketing SaaS, users are marketers, accounts are agencies or customer organizations, and resources are campaigns, audiences, templates, and analytics reports.

In a billing SaaS, users are finance team members, accounts are customer organizations, and resources are invoices, subscriptions, payment methods, and tax settings.

Memberships and Roles

Once accounts exist, users need a way to belong to accounts. A user can belong to multiple accounts, and an account can have multiple users, so the relationship is many-to-many.

The account_user Pivot Table

The relationship can be modeled with a pivot table:

Schema::create('account_user', function (Blueprint $table) {
    $table->unsignedBigInteger('account_id');
    $table->unsignedBigInteger('user_id');
    $table->json('roles');
    $table->primary(['account_id', 'user_id']);
    $table->timestamps();
});

The composite primary key prevents the same user from being added to the same account twice. The roles column stores the user's permissions inside that account.

This table is not just a technical join table. It represents membership.

User
  │
  ▼
Membership
  │
  ▼
Account

Roles Belong to Memberships

A common mistake is thinking that roles belong directly to users. In a multi-tenant system, that is usually wrong.

A user may be an owner in one account and a regular member in another account.

User: Alex

Account A:
  roles: ["owner", "admin"]

Account B:
  roles: ["member"]

If roles were stored directly on the user record, this would not be possible. The system would only know that Alex is an admin globally, which is dangerous and inaccurate.

Real-World Role Example

Imagine a consultant who works with several clients. In their own company account, they may be an owner. In Client A's account, they may be an admin. In Client B's account, they may only be a viewer.

This is why permissions should be evaluated in the context of the current account.

Tenant Isolation

Tenant isolation is one of the most important parts of multi-tenant architecture. It ensures that one account cannot access another account's data.

Why Tenant Isolation Matters

In a single database multi-tenant system, different customers may share the same tables.

projects
id | account_id | name

1  | 10         | Client A Project
2  | 20         | Client B Project

The database can safely store both rows in the same table, but every query must respect account_id.

If the application forgets to filter by account, it may accidentally expose another customer's data.

Unsafe Query Example

This query looks normal, but it is unsafe in a multi-tenant system:

$project = Project::findOrFail($id);

The problem is that $id is usually user-controlled. A user may try to access /projects/123. If project 123 belongs to another account, the query still finds it.

This is an authorization bug.

Safe Query Example

The safer version scopes the query to the current account:

$project = Project::query()
    ->where('account_id', $currentAccount->id)
    ->findOrFail($id);

Now the application only searches inside the current tenant.

A good rule is:

Every business query should know which account it belongs to.

The same rule applies to updates and deletes:

Project::query()
    ->where('account_id', $currentAccount->id)
    ->where('id', $projectId)
    ->update([
        'name' => $newName,
    ]);

Without the account_id condition, a bug in the route or controller could update data outside the current tenant.

Migration Strategy

The migration had to preserve existing user data while introducing accounts as the new ownership boundary. The safest strategy was to create one account for every existing user, attach the user as the owner, move resources to the new account, and then remove direct user ownership.

Create One Account Per Existing User

Every existing user received a default account.

User 1
  │
  ▼
Account 1

User 2
  │
  ▼
Account 2

This preserved the old behavior. Existing users still saw their own data, but internally the data now belonged to an account.

The membership insert looked like this:

INSERT INTO account_user (account_id, user_id, roles)
VALUES (?, ?, '["owner"]');

Move Existing Resources to Accounts

After accounts were created, existing resources were moved from user_id ownership to account_id ownership.

UPDATE projects
SET account_id = ?
WHERE user_id = ?;

Before migration:

User 1
  ├── Project A
  └── Project B

After migration:

Account 1
  ├── Project A
  └── Project B

From the user's perspective, nothing changed. From the architecture perspective, everything changed.

Why We Removed user_id

A tempting option was to keep both columns:

projects
id | user_id | account_id | name

At first, this looks harmless. In practice, it creates ambiguity.

If user_id says one thing and account_id says another, which field is the source of truth? Which one should authorization use? Which one should reporting use?

Different developers may answer those questions differently, which can lead to inconsistent behavior and security bugs.

For this system, ownership needed one source of truth:

Projects belong to accounts.

If authorship is needed later, it should be modeled separately:

projects
id | account_id | created_by_user_id | name

In that model, account_id represents ownership, while created_by_user_id only represents who created the record.

Authorization Refactor

The authorization model changed from identity-based ownership to membership-based ownership.

Before: Identity-Based Ownership

Before multi-tenancy, authorization asked whether the resource belonged to the current user.

$project->user_id === auth()->id()

This works only when each resource has exactly one user owner.

After: Membership-Based Ownership

After multi-tenancy, authorization asks whether the resource belongs to an account where the user is a member.

in_array(
    $project->account_id,
    auth()->user()->accounts->pluck('id')->toArray()
);

The question changed from:

Does this project belong to this user?

to:

Does this project belong to an account this user is a member of?

That is the core idea behind the migration.

Checking Roles Inside Current Account

Membership proves that the user belongs to the account. Roles define what the user can do inside that account.

if (!in_array('admin', $accountUser->roles)) {
    abort(403);
}

For example, a viewer may be allowed to read reports but not edit projects. An admin may manage users and projects. An owner may manage billing and delete the account.

The important part is that these roles are scoped to the account membership, not to the user globally.

Common Mistakes

Forgetting to Scope Queries

The most dangerous mistake is querying resources by ID without scoping them to the current account.

$invoice = Invoice::findOrFail($invoiceId);

In a multi-tenant application, this should usually be:

$invoice = Invoice::query()
    ->where('account_id', $currentAccount->id)
    ->findOrFail($invoiceId);

Treating Roles as Global

Another mistake is storing roles directly on the user.

users
id | email | role

This works for simple admin panels, but it is usually wrong for multi-tenant SaaS. A user can have different roles in different accounts.

Keeping Two Ownership Sources

Keeping both user_id and account_id as ownership fields can create confusion.

If the business rule is that accounts own resources, then account_id should be the source of truth. If you need to track who created the resource, use a separate field like created_by_user_id.

Final Result

After the refactor, the application moved from a personal workspace model to an organization-based SaaS model.

• Users can belong to multiple accounts.
• Accounts can have multiple users.
• Accounts own business resources.
• Users access resources through memberships.
• Roles are scoped to account membership.
• Authorization is based on tenant ownership.
• Queries are scoped by account_id.

This made team collaboration, invitations, role management, enterprise permissions, and account-level billing much easier to support.

Key Takeaway

The biggest lesson is that multi-tenancy is not just a database change. It is a change in ownership thinking.

Before the migration, the system asked:

Does this resource belong to this user?

After the migration, the system asked:

Does this resource belong to an account this user is a member of?

Once that mental model is clear, the architecture becomes much easier to reason about. Accounts become tenant boundaries, resources belong to accounts, users access resources through memberships, and roles are evaluated inside the current account.

That is the foundation of scalable multi-tenant SaaS architecture.