I’m working on implementing role-based access control (RBAC) in my Next.js for my API and I want to ensure that I’m following best practices for efficient database queries. My application uses Next.js along with a backend database to manage user roles and permissions.

CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Roles (
    role_id SERIAL PRIMARY KEY,
    role_name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE UserRoles (
    user_id INT REFERENCES Users(user_id),
    role_id INT REFERENCES Roles(role_id),
    PRIMARY KEY (user_id, role_id)
);

CREATE TABLE Permissions (
    permission_id SERIAL PRIMARY KEY,
    permission_name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE RolePermissions (
    role_id INT REFERENCES Roles(role_id),
    permission_id INT REFERENCES Permissions(permission_id),
    PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE Sessions (
    session_id UUID PRIMARY KEY,
    user_id INT REFERENCES Users(user_id),
    valid_until TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Currently, my approach involves fetching the roles and permissions assigned to a user from the database on every request to determine their access level. However, I’m concerned about the potential performance impact of this approach, especially as the application scales.

What are the best practices for implementing RBAC in Next.js while minimizing the number of database queries? Specifically, I’m interested in strategies for caching role and permission data, optimizing token-based authorization, and handling cache expiration and invalidation.