How ProOne uses Prisma Middleware for Custom Data Security

Here at ProOne, we leverage Prisma as our ORM solution to interact with our PostgreSQL database. Prisma has proven to be an optimal solution, providing convenient and type-safe ways to interact with our database while remaining performant and offering robust data security.

However, some might find that the Prisma documentation isn’t comprehensive enough to fully understand the assortment of tools Prisma offers and how they can be utilized effectively. This article aims to guide you through how to use the Prisma $extends method to add custom methods or extend already existing methods.

One key feature we developed using this middleware is what we internally call “Division Based Access Control” (DivBAC). At ProOne, a customer might have hundreds of users on our platform categorized into various divisions. Many customers requested a feature to limit data access to specific users, which led us to set these limits based on the divisions to which users are assigned.

For instance, consider a division of subcontractors we have invited to our tenant of ProOne, referred to as the SubCon team. SubCon contractors should not have access to every job on our tenant, but only those their division is set to access. Therefore, when they visit the Job list page, they only see jobs allocated to their division, and if someone shares a link to a job outside of their viewing rights, that job will not be accessible.

Here’s a simplified schema we’ll be using:

model Job {
    id        String   @id @default(cuid())
    createdAt DateTime @default(now())
    updatedAt DateTime @updatedAt

    title     String

    divisionId
    division               Division?               @relation(fields: [divisionId]
    @@index([divisionId])
}

model Division {
    id        String   @id @default(cuid())
    createdAt DateTime @default(now())
    updatedAt DateTime @updatedAt

    users    Users[]

    dataCanBeSeenBy DataScope @relation(“data_of_division_can_be_seen_by”)
    dataCanBeSeenById String @unique
}

model DataScope {
    id        String   @id @default(cuid())
    createdAt DateTime @default(now())
    updatedAt DateTime @updatedAt

    isEveryone    Boolean

    divisions Division[] /// Divisions that have

    targetDivision Division? @relation(“data_of_division_can_be_seen_by”)
}

The methodology operates as follows: A job will always be linked to a specific division. Each division will have an associated DataScope. The DataScope has two conditions: if isEveryone is true, then any data under this division is visible to everyone. If isEveryone is false, then the divisions array will have selections of divisions that can access this division’s data.

We ensure that jobs are returned with the appropriate data access requirements using two methods depending on the use-case scenarios. The first possible method is a custom Prisma setup.

class ExtendedPrismaClient {
    private prisma: PrismaClient;

    constructor() {
        this.prisma = new PrismaClient();
    }

    // Our custom where clause check, 
    // if either of the conditions are true then the data is allowed to be viewed
    private dataScopeCheck = (userId: string) => ({
        OR: [
        { isEveryone: true },
        { divisions: { some: { users: { some: { id: userId } } } } },
        ]
    })

    // A method to fetch jobs based on division access
    async getJobsBasedOnAccess(userId: string): Promise<Job[] | null> {
        const userDivisionId = await this.getUserDivisionId(userId);
        return this.prisma.job.findMany({
            where: this.userScopeCheck(userId),
     })

    // A simple query to find the user's divisionId
    private async getUserDivisionId(userId: string): Promise<string> {
            const user = await this.prisma.user.findUnique({
                where: { id: userId },
                select: { divisionId: true }
            });
            return user?.divisionId || '';
       }
}

This setup is a custom solution designed to extend from Prisma to provide a direct, callable function that handles everything the client requests in terms of data querying and manipulation.

Here is how you call this function:

const prisma = new ExtendedPrismaClient();

const jobs = await prisma.getJobsBasedOnAccess('user123')

The custom methodology would work great for one-off sort of queries to satisfy specific needs. At ProOne, however, we focused on a more global solution, where developers don’t need to memorize custom Prisma methods. So instead of a custom method, we have extended Prisma’s models within our database. This adjustment looks as follows:

class ExtendedPrismaClient extends PrismaClient {

    constructor() {

    super();

    // In this extends, we specify which model we'll extend functionality for
    return this.$extends({
        model: {
            job: {
                // If we specify a query like "findMany", 
                // we effectively overwrite it
                async findMany(userId: string, args: Prisma.JobFindManyArgs = {}) {
                    const divisionId = await this.getUserDivisionId(userId);
                    const newArgs = await this.processDivBAC(divisionId, args);
                    const context = Prisma.getExtensionContext(this);
                    // We need to remember to return the context 
                    // with the overwritten arguments for Prisma to function properly
                    return context.$parent.job.findMany(newArgs);
                    }
                }
            }
        });
    }

    private dataScopeCheck = (divisionId: string) => ({
        OR: [
        { isEveryone: true },
        { division: { id: divisionId } },
        ]
    });

    private async getUserDivisionId(userId: string): Promise<string> {
        const user = await this.user.findUnique({
            where: { id: userId },
            select: { divisionId: true }
        });
        return user?.divisionId || '';
    }

    // Here we layer our arguments into any where clause
    async processDivBAC(divisionId: string, args: Prisma.JobFindManyArgs) {
            args.where = {
                ...args.where,
                    AND: [
                    ...(args.where.AND || []),
                    this.dataScopeCheck(divisionId),
                    ]
                };
            return args;
        }
}

// To call the findMany method
const prismaClient = new ExtendedPrismaClient();

const jobs = await prismaClient.job.findMany(userId, {
        orderBy: {
             createdAt: 'desc',  
          }
    }
 )

With this methodology, developers can call the function as a normal Prisma query, and Prisma will process all included data to limit access if the DataScope has the appropriate options set. Of course, this is just a simplified version. If calling a Prisma query within a Prisma query seems heavy on your system, you use data you’ve already accessed through your authorization process and pass it into the Prisma query through arguments.

There are some good pros to this approach with Prisma $extends:

• Enhanced data security. By enforcing Prisma to check this based on the normal queries, the likelihood of the check not applying correctly or being forgotten is eliminated.
• Seamless integration. Once the middleware extends function is set and the Prisma service that all developers use is properly extended, then the changes are applied throughout the API.
• Efficiency and scalability. With proper application and tweaking to suit your API, this setup can offer a great way to apply simple changes that scale seamlessly into existing security frameworks.

However, there are some cons to the Prisma $extends functionality:

• Not RLS. For the examples we’ve shown here, it’s not full Row Level Security so any joins the developer makes to the Prisma query need to be considered because they might not be covered. Prisma doesn’t support RLS directly, meaning you’ll have to establish it on the SQL level instead, making it harder to implement and less secure of deployment overall.
• Performance overheads. Depending on the way you wish to implement data restrictions, the queries might create greater demand on your servers and not properly scale well.
• Tight coupling to Prisma. This approach is highly dependent on Prisma and the version of Prisma that allows this $extends middleware. If you wish to change over to a different ORM, this system would need to be translated to suit. Additionally, if Prisma decided to change how they do middleware methods, which they’ve done in the past, to upgrade to a newer version would require a reworking of this system.

In conclusion, ProOne’s use of Prisma’s $extends to handle our DivBAC system has allowed us to keep our operations smooth and data security well-structured. We prioritized a developer-first approach, ensuring that the system is not only easy and intuitive to use but also robust and adaptable enough to handle rigorous functionality. This approach has significantly streamlined development workflows, reducing overhead and enhancing productivity. Moreover, it has provided a solid foundation that scales effectively as we grow, while continuously reinforcing our commitment to maintaining high security and reliability standards.