Enhancing the flexibility of the auth schema

[kentcdodds]

This was brought up by @jacobparis and discussed by myself, @sergiodxa, @glitchassassin, and @DennisKraaijeveld

Here's an AI summary of the conversation (which started here):

Summary of Discord Conversation:

Participants:

• Jacob Paris
• Kent C. Dodds
• Sergio
• Jon Winsley
• DennisK

Key Points:

1. Error Boundary in Onboarding:

   • Jacob Paris identified that onboarding.tsx is missing a general error boundary and plans to submit a PR to address this. He's working on a Remix Vercel project and is heavily copying the auth setup from Epic Stack, making necessary adjustments like switching Resend for SendGrid.

2. Flexible Auth System:

   • Jacob Paris outlined a flexible auth system where users can access multiple workspaces and accounts, allowing for the ability to sign into multiple accounts and switch between them.
   • Kent C. Dodds expressed interest in adopting this model for the built-in auth setup and suggested using an example to demonstrate the implementation.

3. Terminology and Structure:

   • Sergio proposed using "organization," "membership," and "user" to describe the system components. Membership would represent a user's access to an organization, with roles and permissions managed at this level.
   • Jacob Paris preferred the terms "workspace" and "account," emphasizing the need for flexibility in user identities and access across multiple organizations.

4. Multi-Tenancy and Data Modeling:

   • Sergio shared insights on multi-tenancy data modeling, suggesting a structure where organizations, accounts, memberships, and users are distinct entities. He emphasized the importance of roles and permissions being managed at the membership level.
   • Jacob Paris and Sergio discussed the practical aspects of implementing this model, with Sergio noting his work on an IAM service for CF Workers and D1 as the database.

5. Implementation Details:

   • Sergio explained his approach to building an IAM service with a flexible API, while DennisK shared his existing setup where users can switch workspaces within a dashboard, and sessions track the current workspace.
   • The conversation concluded with a discussion on session management and whether sessions need to track user identities directly or just maintain an array of memberships.

Next Steps:

• Move the conversation to GitHub for a more permanent discussion platform.
• Review and consider the proposed changes to the auth system and data model.
• Collaborate on PRs and examples to refine and implement the discussed changes.

Let's continue this discussion on GitHub to further explore the implementation details and best practices for the flexible auth system and multi-tenancy data modeling.

[aderici]

Interesting conversation! I wonder how the SSO could be integrated into this equation where an organization would be given an option to mandate its users only logging in through a specific SAML or OIDC auth provider

[DennisKraaijeveld]

I'll participate by sharing my current setup more in detail, in order to deliver something to the discussion and learn why this approach may not be viable. And maybe it's a simple step up for people who want to implement something like this.

Since I have an App built for companies/agencies, I was going for a multi tenant setup. Many companies work with freelancers so in edge cases you would have this situation that a freelancer can work for both companies, and does need to have access to both workspaces.

So:

• One user account. Nothing special.
• A user can be part of multiple workspaces as either an admin or member.
• User A of Workspace A should be able to switch to Workspace B without logging out.
• The workspace is owner of all data. An user is just a model to store profile data and dashboard related information

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  username  String   @unique
  firstName String?
  lastName  String?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  image          UserImage?
  password       Password?
  roles          Role[]
  sessions       Session[]
  connections    Connection[]
  authenticators Authenticator[]

  currentWorkspaceId String?
  workspaces         UserWorkspace[]

  novuSubscriberId NovuSubscriberID[]

}

model UserWorkspace {
  userId      String
  workspaceId String
  user        User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  workspace   Workspace @relation(fields: [workspaceId], references: [id], onDelete: Cascade)

  @@id([userId, workspaceId])
}

model Workspace {
  id String @id @default(uuid())

  createdAt DateTime        @default(now())
  updatedAt DateTime        @updatedAt
  name      String
  users     UserWorkspace[]

  invitation      Invitation[]

  // Stripe Customer in this case is the workspace
  customerId       String?       @default("")
  // Stripe Subscription
  subscription     Subscription?
}

At every workspace related page, I check if:

1. User has a workspace
2. If user is part of a workspace, it returns me the currentWorkspaceId from the DB (!! Learned this should be in Session. Keen to learn why)
3. Continues to query data based on the currentWorkspaceId

Switching workspaces involves making a POST and updating the currentWorkspaceID

export async function loader({ request }: LoaderFunctionArgs) {
	const workspaceId = await requireUserWorkspaceId(request)

	if (!workspaceId) {
		return redirect('/create-workspace')
	}
	
	const totalContacts = await prisma.contact.count({
		where: { workspaceId },
	})

[sergiodxa]

If user is part of a workspace, it returns me the currentWorkspaceId from the DB (!! Learned this should be in Session. Keen to learn why)

Let's say the user is logged in in two browsers, if you save currentWorkspaceId on the user table and the user change to workspace 1 on one browser, and then navigate in the second one, it will be also on the workspace 1 because the workspace id was updated on the first browser.

By keeping it in the session you can have each browser use a different workspace. Something even better could be to keep the workspace id in the URL, this way you can even have two tabs on the same browser with different workspaces.

[sergiodxa]

You're setting the role in the User model, if you want to have different roles per workspace, it would be better to keep it in the UserWorkspace model, you can still have a global role for the whole app to identify the users vs admins of the app as a whole, useful to access some global access panel.

[aderici]

I remember reading for different opinions on where to keep the tenant/workspace id. subdomain vs cookie/session vs url. URL sounds like the most convenient one to me. What could go wrong with the url?

[DennisKraaijeveld]

You're setting the role in the User model, if you want to have different roles per workspace, it would be better to keep it in the UserWorkspace model, you can still have a global role for the whole app to identify the users vs admins of the app as a whole, useful to access some global access panel.

The Role inside the User model, is indeed for the app, it's not the role of user for the current workspace. I think I did work on something fishy by storing the workspace role in the Role table connecting the role, userId and workspaceId.

Storing it in the UserWorkspace model does sound better though

[glitchassassin]

Does it make sense to separate Organization, Workspace, and User?

For example, it seems to be pretty common for an Organization to have separate internal teams with a workspace per project, and multiple users per workspace.

[DennisKraaijeveld]

Does it make sense to separate Organization, Workspace, and User?

For example, it seems to be pretty common for an Organisation to have separate internal teams with a workspace per project, and multiple users per workspace.

Edit: never mind.

But I think adding an org, is really based on what you want with your app and for most cases it just adds a level of complexity which is not needed. But there should't be a big difference in setup though. I guess both Users and Workspaces are part of an org, where Users could belong to multiple Orgs and Workspaces, but Workspaces can only have one Org.

[itsmegood]

I made something similar here is the schema.

model User {
  id       String  @id @default(cuid())
  email    String  @unique
  username String  @unique
  name     String?

  // Optional Currently maybe add this later
  inviteKey          String
  canCreateCompanies Boolean @default(false)
  companiesLimit     Int     @default(3)

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

  password          Password?
  platformStatus    PlatformStatus @relation(fields: [platformStatusKey], references: [key])
  platformStatusKey String

  roles              Role[]
  sessions           Session[]
  connections        Connection[]
  userCompanies      UserCompany[]
  companyInvitations CompanyMemberInvitation[]

  @@unique([email, inviteKey])
}

model Permission {
  id          String @id @default(cuid())
  action      String // e.g. create, read, update, delete
  entity      String // e.g. note, user, etc.
  access      String // e.g. own or any
  description String @default("")

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

  roles         Role[]
  userCompanies UserCompany[]

  @@unique([action, entity, access])
}

model Role {
  id          String @id @default(cuid())
  name        String @unique
  description String @default("")

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

  users                   User[]
  permissions             Permission[]
  userCompanies           UserCompany[]
  companyMemberInvitation CompanyMemberInvitation[]
}

model PlatformStatus {
  id    Int     @id @default(autoincrement())
  key   String  @unique
  label String?
  color String?

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

  companies     Company[]
  users         User[]
  userCompanies UserCompany[]
}

// * Account specific models
// * This is for a simple personal finance app

// Company model represents a business entity or organization that the accounting system will handle. 
model Company {
  id      String @id @default(cuid())
  name    String // Name of the company or business entity.
  linkKey String

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

  platformStatus    PlatformStatus @relation(fields: [platformStatusKey], references: [key])
  platformStatusKey String

  invoiceCount Int @default(0) // Count of invoices generated by the company.

  users            UserCompany[] // Users associated with the company.
  accounts         Account[]                 @relation("OriginCompany") // Financial accounts belonging to the company.
  linkedAccounts   Account[]                 @relation("LinkedPlatformCompanyAccount")
  transactions     Transaction[] // Financial transactions recorded for the company.
  saleInvoices     SaleInvoice[] // Invoices generated by the company.
  saleItems        SaleInvoiceItem[] // Bills received by the company.
  purchaseBills    PurchaseBill[]
  inventory        InventoryItem[]
  requestInventory RequestInventory[]
  cart             Cart[]
  memberInvitation CompanyMemberInvitation[]

  @@unique([id, linkKey])
}

// UserCompany is a join table that allows users to be associated with multiple companies.
// It serves as a many-to-many relationship between User and Company models.
model UserCompany {
  id      String  @id @default(cuid())
  isOwner Boolean @default(false)

  user      User    @relation(fields: [userId], references: [id])
  userId    String
  company   Company @relation(fields: [companyId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  companyId String
  cart      Cart?

  roles       Role[]
  permissions Permission[]
  status      PlatformStatus @relation(fields: [statusKey], references: [key])
  statusKey   String

  transactions     Transaction[]
  saleInvoices     SaleInvoice[]
  purchaseBills    PurchaseBill[]
  accounts         Account[]
  inventory        InventoryItem[]
  requestInventory RequestInventory[]

  @@unique([userId, companyId])
}

model CompanyMemberInvitation {
  id String @id @default(cuid())

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

  email      String
  isAccepted Boolean @default(false)

  status String // e.g. pending, accepted, rejected

  company       Company @relation(fields: [companyId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  companyId     String
  role          Role?   @relation(fields: [roleId], references: [id])
  roleId        String?
  user          User?   @relation(fields: [userEmail, userInviteKey], references: [email, inviteKey])
  userEmail     String?
  userInviteKey String?
}

// model represents financial ledgers which are used to record transactions
// and keep track of the financial status of various aspects of the company such as assets, liabilities, revenue, etc.
model Account {
  id          String  @id @default(cuid())
  name        String // Name of the financial ledger/account.
  uniqueId    String?
  email       String?
  balance     Float? // Current balance of the account.
  // type    String // Type of the account, e.g., revenue, expense.
  phone       String?
  country     String?
  city        String?
  state       String?
  zip         String?
  address     String?
  description String?

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

  // Get approval of both the companies
  isLinkedCompanyAccount Boolean @default(false)
  innitiatorApproval     Boolean @default(false)
  linkedCompanyApproval  Boolean @default(false)

  company          Company     @relation("OriginCompany", fields: [companyId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  companyId        String
  linkedCompany    Company?    @relation("LinkedPlatformCompanyAccount", fields: [linkedCompanyId, linkedCompanyKey], references: [id, linkKey], onDelete: Cascade, onUpdate: Cascade)
  linkedCompanyId  String?
  linkedCompanyKey String?
  createdBy        UserCompany @relation(fields: [createdById], references: [id])
  createdById      String

  transactions Transaction[] // Transactions associated with this account.
  PurchaseBill PurchaseBill[] // Bills associated with this account.
  SaleInvoice  SaleInvoice[] // Invoices associated with this account.
}

[itsmegood]

One user can have multiple companies/ organizations, can invite users to join their company.

One user can join multiple companies with different roles/ permissions

[jacobparis]

The tentative schema I'm working with looks like this right now, but as I go through each use-case I might make some tweaks to handle it

• i should be able to use a single email and attach multiple workspaces
• i should be able to stay logged in with multiple emails and switch between workspaces
• use github login, then associate my work email
• when i log in i get a token for the account, if i login multiple times save multiple tokens
• a simple app should be able to use a default organization and a single user per account and not feel the complexity

model Organization {
  id        String   @id @default(uuid())
  name      String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  memberships Membership[]
}

model Membership {
  id             String  @id @default(uuid())
  userId         String?
  organizationId String
  invitedById    String?

  user         User?        @relation("MembershipUser", fields: [userId], references: [id])
  invitedBy    User?        @relation("InvitedByUser", fields: [invitedById], references: [id])
  organization Organization @relation(fields: [organizationId], references: [id])
  roles        Role[]

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

  @@index([userId])
  @@index([invitedById])
  @@index([organizationId])
}

model User {
  id                 String       @id @default(uuid())
  createdAt          DateTime     @default(now())
  updatedAt          DateTime     @updatedAt
  memberships        Membership[] @relation("MembershipUser")
  invitedMemberships Membership[] @relation("InvitedByUser")
  account            Account      @relation(fields: [accountId], references: [id])
  accountId          String

  // The following fields are app-specific and you might want to change them
  name     String
  username String
  email    String
  image    UserImage?
  notes    Note[]
}

model Session {
  id        String    @id @default(uuid())
  expiresAt DateTime
  accounts  Account[]

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

model Account {
  id    String @id @default(uuid())
  email String @unique

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

  password    Password?
  sessions    Session[]
  users       User[]
  connections Connection[]
}

model Password {
  accountId String @unique
  hash      String

  account Account @relation(fields: [accountId], references: [id])
}

model Connection {
  id           String @id @default(cuid())
  providerName String
  providerId   String

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

  account   Account? @relation(fields: [accountId], references: [id])
  accountId String?

  @@unique([providerName, providerId])
}

model Permission {
  id          String @id @default(cuid())
  action      String
  entity      String
  access      String
  description String @default("")

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

  roles Role[] @relation("RolePermissions")

  @@unique([action, entity, access])
}

model Role {
  id          String @id @default(cuid())
  name        String @unique
  description String @default("")

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

  permissions Permission[] @relation("RolePermissions")
  memberships Membership[]
}

[jacobparis]

The Account is the thing the person logs in with, so with Github for example you'd log in with your username/password, and that's gonna have at least one user with the same email but you could have an additional user with a company email that has access to different memberships

They would only have to log in once but still be able to act as each of those email addresses

Alternatively you could create separate work and personal accounts that are just linked to a single user each and then they'd have to log in on each of them. Since the Session accepts an array of accounts, they could remain logged in on both and have an account switcher to jump back and forth, but each device would require them to log in to all of them individually.

There's no many-to-many going on here right now so it looks like this

• one session to many accounts (this should be many sessions to many accounts actually)
• one account to many users
• one user to many memberships
• one membership to one user, one org, many roles
• one role to many permissions

[sergiodxa]

🤔 I think you're replicating Slack model?

[jacobparis]

Slack model is in-scope in that it should be implementable with the schema, though you could implement Discord model by having your app create them like

• one Account -> one User -> Membership for each server
• or one Account -> User for each server

It becomes an app concern to change the model and not require mucking with the auth schema

[jacobparis]

You could see User.name, User.username, and User.email all as app-level concerns and not related to auth at all, though they're useful if you want to implement a switcher

[sergiodxa]

You could see User.name, User.username, and User.email all as app-level concerns and not related to auth at all, though they're useful if you want to implement a switcher

Yeah that's why my implementation doesn't have users, only accounts, the user is part of the app, not the auth