Adding User Permissions in Old Projects

Migrate a Dynamic-mode project from role-only permissions to shared AuthPermission rows plus per-user overrides (1.7.0-rc+).

← Back

Written by

Uanela Como

Fullstack Developer@Mesquita Group & SuperM7.com Founder

Fri Jul 10 2026

Back up your database before running any step here. Step 3 deletes rows. Run this on staging first.

If your project predates 1.7.0-rc, AuthPermission is one row per role (roleId + @@unique([resource, action, roleId])). This guide moves you to the shared model (roles AuthPermission[] many-to-many + @@unique([resource, action])) and adds UserPermission for per-user overrides.

1. Drop the unique constraint entirely

prisma/schema.prisma

model AuthPermission {
  id        String   @id @default(uuid())
  resource  String
  action    String   @default("View")
  roleId    String
  role      AuthRole @relation(fields: [roleId], references: [id])

  // no @@unique here — removed for now
}

pnpm prisma db push

No constraint to violate, so this always succeeds regardless of existing duplicates.

2. Add the `roles` many-to-many relation

AuthPermission now has two relations to AuthRole — the old role (via roleId) and the new roles (many-to-many) — so both sides need named relations to disambiguate:

prisma/schema.prisma

model AuthPermission {
  id        String     @id @default(uuid())
  resource  String
  action    String     @default("View")
  roleId    String
  role      AuthRole   @relation("LegacyPermissionOwner", fields: [roleId], references: [id])
  roles     AuthRole[] @relation("PermissionRoles")

  @@unique([resource, action])
}

model AuthRole {
  id              String           @id @default(uuid())
  name            String           @unique
  permissions     AuthPermission[] @relation("LegacyPermissionOwner")
  rolePermissions AuthPermission[] @relation("PermissionRoles")
  users           UserRole[]
  createdAt       DateTime         @default(now())
  updatedAt       DateTime         @updatedAt
}

pnpm prisma generate

3. Run the migration script

This connects each old roleId into the new roles relation, then drops the legacy column entirely.

scripts/migrate-permissions.ts

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
  const permissions = await prisma.authPermission.findMany({
    select: { id: true, roleId: true },
  });

  for (const permission of permissions) {
    await prisma.authPermission.update({
      where: { id: permission.id },
      data: { roles: { connect: { id: permission.roleId } } },
    });
  }

  console.log(`Migrated ${permissions.length} permissions.`);
}

main()
  .catch((err) => {
    console.error(err);
    process.exit(1);
  })
  .finally(() => prisma.$disconnect());

npx tsx scripts/migrate-permissions.ts

Safe to re-run — connect on an already-connected relation is a no-op.

Now drop the legacy field and relation from the schema:

prisma/schema.prisma

model AuthPermission {
  id        String     @id @default(uuid())
  resource  String
  action    String     @default("View")
  roles     AuthRole[] @relation("PermissionRoles")

  @@unique([resource, action])
}

model AuthRole {
  id          String           @id @default(uuid())
  name        String           @unique
  permissions AuthPermission[] @relation("PermissionRoles")
  users       UserRole[]
  createdAt   DateTime         @default(now())
  updatedAt   DateTime         @updatedAt
}

You can drop the @relation("PermissionRoles") names on both sides at this point too — with the legacy relation gone there's no more ambiguity, and Prisma will infer the join table on its own.

4. Add the new unique constraint

Now that permissions are consolidated onto the many-to-many roles relation, (resource, action) duplicates across the old per-role rows are irrelevant — add it here:

prisma/schema.prisma

model AuthPermission {
  id        String     @id @default(uuid())
  resource  String
  action    String     @default("View")
  roles     AuthRole[]

  @@unique([resource, action])
}

pnpm prisma db push

This can still fail if step 3's connect loop left literal duplicate (resource, action) rows (e.g. two roles each had their own "post"/"Create" row, both now exist as separate AuthPermission records). If it fails here, merge those duplicate rows — reconnect any roles and UserPermission references from the duplicate onto one surviving row, then delete the duplicate — before re-running.

5. Add `UserPermission`

prisma/schema.prisma

model UserPermission {
  id           String               @id @default(uuid())
  createdAt    DateTime             @default(now())
  updatedAt    DateTime             @updatedAt
  effect       UserPermissionEffect @default(Allow)
  userId       String
  user         User                 @relation(fields: [userId], references: [id])
  permissionId String
  permission   AuthPermission       @relation(fields: [permissionId], references: [id])

  @@unique([userId, permissionId])
}

enum UserPermissionEffect {
  Allow
  Deny
}

5. Wire up `User` and `AuthPermission`

prisma/schema.prisma

model User {
  // ...everything else remains the same
  permissions UserPermission[]
}

model AuthPermission {
  id        String   @id @default(uuid())
  resource  String
  action    String   @default("View")
  roles     AuthRole[]
  users     UserPermission[]
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@unique([resource, action])
}

7. Push and restart

pnpm prisma db push
pnpm dev

At this point your project is fully on the 1.7.0-rc permission model — shared AuthPermission rows across roles, plus optional per-user overrides via UserPermission. See Dynamic — User-Level Permission Overrides for how resolution works.

Tags:

Adding User Permissions in Old Projects

1. Drop the unique constraint entirely

2. Add the roles many-to-many relation

3. Run the migration script

4. Add the new unique constraint

5. Add UserPermission

5. Wire up User and AuthPermission

7. Push and restart

2. Add the `roles` many-to-many relation

5. Add `UserPermission`

5. Wire up `User` and `AuthPermission`