Flexible Multi-Tenant Web App Prisma Schema

multitenant_prisma_schema.md

Flexible Multi-Tenant Web App Prisma Schema

Description

A Prisma schema for a multi-tenant web app, designed for flexibility during the MVP & scalup stages.

Features/Capabilities

• Individual user accounts
• Organization accounts
  • Users can belong to zero or more organizations
• Team support
  • Organizations can have zero or more teams
• Role-based access control that is flexible
  • Different roles for users in different organizations

Limitations/Gotchas

Limitation/Gotcha	Description
Organization Owner	Schema lacks enforcement of an organization "owner"
Unique Organization/Team Names	Schema lacks normalization of Organization.name, Team.name

Schema

model User {
    id          Int             @id @default(autoincrement())
    email       String          @unique
    createdAt   DateTime        @default(now())
    updatedAt   DateTime        @updatedAt

    team TeamMember[]
    userOrganizationRole UserOrganizationRole[]
  }

  model Organization {
    id          Int             @id @default(autoincrement())
    name        String          @unique
    createdAt   DateTime        @default(now())
    updatedAt   DateTime        @updatedAt

    teams Team[]
    userOrganizationRole UserOrganizationRole[]
  }

  model Team {
    id          Int           @id @default(autoincrement())
    name        String
    organization Organization @relation(fields: [organizationId], references: [id])
    organizationId Int
    createdAt   DateTime      @default(now())
    updatedAt   DateTime      @updatedAt

    teamMember TeamMember[]

    @@unique([name, organizationId])
  }

  model TeamMember {
    id          Int           @id @default(autoincrement())
    user        User          @relation(fields: [userId], references: [id])
    userId      Int
    team        Team          @relation(fields: [teamId], references: [id])
    teamId      Int
    createdAt   DateTime      @default(now())
    updatedAt   DateTime      @updatedAt

    @@unique([userId, teamId])
  }

  model UserOrganizationRole{
    id          Int             @id @default(autoincrement())
    user        User            @relation(fields: [userId], references: [id])
    userId      Int
    organization Organization @relation(fields: [organizationId], references: [id])
    organizationId Int
    role        Role            @relation(fields: [roleId], references: [id])
    roleId      Int
    createdAt   DateTime        @default(now())
    updatedAt   DateTime        @updatedAt

  @@unique([userId, organizationId, roleId])
  }

  model Role {
    id          Int             @id @default(autoincrement())
    name        String
    createdAt   DateTime        @default(now())
    updatedAt   DateTime        @updatedAt

    rolePermission RolePermission[]
    userOrganizationRole UserOrganizationRole[]
  }

  model Permission {
    id          Int             @id @default(autoincrement())
    name        String
    createdAt   DateTime        @default(now())
    updatedAt   DateTime        @updatedAt

    rolePermission RolePermission[]
  }

  model RolePermission {
    id          Int             @id @default(autoincrement())
    role        Role            @relation(fields: [roleId], references: [id])
    roleId      Int
    permission  Permission      @relation(fields: [permissionId], references: [id])
    permissionId Int
    createdAt   DateTime        @default(now())
    updatedAt   DateTime        @updatedAt

    @@unique([roleId, permissionId])
  }

Diagram

License

This project is licensed under the Creative Commons Attribution 4.0 International License.