Database schema for Roles and permissions - best approach

Your database schema for roles and permissions is a good start. However, there are a few improvements and considerations you might want to take into account:

1. Normalize the permissions table to avoid boolean columns for each action. Instead, you can have a permissions table that defines the different actions and a role_permissions table that links roles to these permissions.

2. Consider using a many-to-many relationship between roles and permissions since a role can have multiple permissions and a permission can belong to multiple roles.

3. Use foreign keys to ensure referential integrity.

Here's an updated schema using DBML:

Table users {
  id integer [pk]
  first_name varchar
  last_name varchar
}

Table roles {
  id integer [pk]
  name varchar
}

Table users_roles {
  user_id integer [ref: > users.id]
  role_id integer [ref: > roles.id]
}

Table permissions {
  id integer [pk]
  name varchar
}

Table role_permissions {
  role_id integer [ref: > roles.id]
  permission_id integer [ref: > permissions.id]
  space_id integer [ref: > spaces.id]
}

Table spaces {
  id integer [pk]
  name varchar
}

// Define a composite primary key for users_roles and role_permissions
Index users_roles (user_id, role_id) [pk]
Index role_permissions (role_id, permission_id, space_id) [pk]

With this schema, you can have permissions like "read", "write", "delete", etc., and assign them to roles for specific spaces. This way, you can easily add new permissions without altering the table structure.

To deliver the permissions with user data for SPA and mobile apps, you would query the database to get the permissions for the user's roles and format them as needed. Here's a pseudo-code example of how you might retrieve and format the permissions for a user:

// Pseudo-code to fetch and format permissions for a user
userPermissions = {}

// Fetch user roles
userRoles = query("SELECT role_id FROM users_roles WHERE user_id = ?", [userId])

// Fetch permissions for each role
foreach (userRoles as role) {
    rolePermissions = query("SELECT p.name, s.name as space_name FROM role_permissions rp JOIN permissions p ON rp.permission_id = p.id JOIN spaces s ON rp.space_id = s.id WHERE rp.role_id = ?", [role.role_id])
    
    foreach (rolePermissions as permission) {
        // Initialize space in userPermissions if not present
        if (!userPermissions[permission.space_name]) {
            userPermissions[permission.space_name] = {
                can_view: false,
                can_edit: false,
                can_delete: false
            }
        }
        
        // Set the permission to true based on the permission name
        switch (permission.name) {
            case 'read':
                userPermissions[permission.space_name].can_view = true
                break
            case 'write':
                userPermissions[permission.space_name].can_edit = true
                break
            case 'delete':
                userPermissions[permission.space_name].can_delete = true
                break
        }
    }
}

// userPermissions now contains the formatted permissions for the user

This pseudo-code is just an example to illustrate the concept. You would need to implement the actual queries and logic in your application's server-side language and framework.