Data Modelling for Multi tenancy apps.

When building multi-tenancy applications, the easiest and most common architecture pattern is sharing resources across all clients and ensuring each client gets access to the data they own. In this case, sharing a single database compute shared by all clients. This comes with extra caution, as we will see later. You have to model your data layer so that you can't expose data to the person who shouldn't see it in the first place.

In this example, we will be doing data modeling for an enterprise TODO app. The end goal of this app is that an enterprise "admin" can create an account for our to-do app, create a workspace as well as invite team members to join this workspace.

Before we get to the data modeling bit, let's look at the onboarding flow to get a higher level of understanding of things.

1. The user will sign up, and then after successful account creation, we direct them to the welcome screen

2. In the welcome screen, we check if the user is already a member of any workspace; if yes, we then redirect them to their dashboard sth like /dashboard/workspaceSlug/sthElse. If the user is not a member of any workspace, we then show the UI to create a workspace and then redirect them to the dashboard.

After getting the onboarding flow logic nailed, we move on to the data modeling bits. You will notice that in most apps, the concept of workspaces is used consistently. This is a common naming convention of the tables and it represents that all other tables are linked to this table. All of your other tables should be linked to this table by primary keys. It should contain basic information such as name, link to the user table, and a unique workspace ID. Below is an initial model of the workspace table linked to the user table. Note that we use auth.js for auth but the logic should apply with any other auth solution.

The below schema file uses Prisma ORM but the syntax to other ORMs or even raw SQL should be pretty much straight forward.

model User {
  id               String         @id @default(cuid())
  name             String?
  email            String?        @unique
  emailVerified    DateTime?
  image            String?
  accounts         Account[]
  source           String?
  defaultWorkspace String?
  createdAt        DateTime       @default(now())
  updatedAt        DateTime       @updatedAt
  WorkspaceUsers   WorkspaceUsers[]
  @@index(source)
  @@index(defaultWorkspace)
}

model Account {
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String?
  access_token      String?
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String?
  session_state     String?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@id([provider, providerAccountId])
}

model WorkspaceUsers {
  id        String   @id @default(cuid())
  role      Role     @default(member)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  userId    String
  workspace   Workspace  @relation(fields: [workspaceId], references: [id], onDelete: Cascade)
  workspaceId String

  @@unique([userId, workspaceId])
  @@index([workspaceId])
}

model Workspace {
  id                   String                 @id @default(cuid())
  name                 String
  slug                 String                 @unique
  logo                 String?
  plan                 String                 @default("free")
  inviteCode           String?                @unique
  createdAt            DateTime               @default(now())
  updatedAt            DateTime               @updatedAt
  users                WorkspaceUsers[]
  invites              WorkspaceInvite[]
}

model WorkspaceInvite {
  email     String
  expires   DateTime
  workspace  Workspace  @relation(fields: [workspaceId], references: [id], onDelete: Cascade)
  workspaceId String
  createdAt DateTime @default(now())

  @@unique([email, workspaceId])
  @@index([workspaceId])
}

enum Role {
  owner
  member
}

The User and Account tables are tables for authentication, and in this case, they are handled by the auth.js library; you can replace them with any other tables where you store your user data. The following relationship should be kept for the other tables.

• A user can belong to multiple workspaces. This will allow the user to switch between multiple workspaces without affecting the other people in the organization.

• Each workspace should have a unique workspace invite, which will ensure that no individual invite link belongs to more than one workspace.

• Each workspace slug should be unique, so check this before creating a new workspace.

• All other tables should be linked to the workspace table via a primary key.

Lastly, the todo table will be the table where we handle our todo lists.

model Todo {
  id          String   @id @default(cuid())
  title       String
  description String
  dueDate     DateTime
  createdAt   DateTime
  updatedAt   DateTime
}

model Workspace {
   ... other fields
   todo Todos[]
}

This will be the basis of the data model for the multi-tenancy todo app. We can improve the performance of our queries by adding indexes to the tables.

Advanced Use Cases

We can implement Row Level Security to ensure we don't expose our data to users who shouldn't access them. You can think of RLS policies are guard rails where you can define custom rules to access the database at the row level. They help you in cases where you might forget to do the checks in the app level. Furthermore, it reduces the database round trips when it comes to perform different CRUD operations. RLS should be handled via migations so that you can easily follow up the state of your database easily.

Although most time is spent on thinking of RLS as means of tightening the security of your database, it can cause massive performance downgrade to some of your queries. You can read more on how to improve your RLS policies here

Lastly, we can add indexes on the tables to improve the performance of our queries. The choice of your indexes depends on your use cases and the nature of your tables as well.