Permissions System Documentation

This document explains how the FarmCove Delta permissions system works, including role-based access control (RBAC) and project-specific permissions.

Overview

The permissions system is designed around the concept that users have different roles in different projects. A user might be a Producer in one project but only a Crew Member in another. This affects what they can see and do within each project context.

Core Concepts

1. Permissions

A permission is an atomic unit of access control. Each permission follows the pattern:

module:action:scope

Examples:

• budget:view:assigned - Can view budgets for assigned projects
• budget:edit:all - Can edit all budgets
• transaction:create - Can create new transactions

2. Project Roles

Roles define a set of permissions that are commonly grouped together. Standard roles include:

• Producer: Full project access
• Line Producer: Budget and schedule management
• Production Accountant: Financial management
• Coordinator: General support access
• Department Head: Department-specific access
• Crew Member: Basic read-only access

3. Project Context

Permissions are evaluated within the context of a specific project. A user's access changes when they switch between projects.

How It Works

User Journey

1. User logs in → Sees only projects they're assigned to
2. User selects a project → System loads their role for that project
3. System calculates permissions → Based on their role in that specific project
4. UI adapts → Menu items and features show/hide based on permissions

Data Flow

User Account
    ↓
Project Team Member (links user to project with a role)
    ↓
Project Role (e.g., Producer, Line Producer)
    ↓
Role Permissions (what that role can do)
    ↓
Menu/Feature Access

Database Schema

Key Tables

1. permissions: Defines all available permissions
2. permission_key: Unique identifier (e.g., 'budget:view:all')
3. description: Human-readable description

4. module: Feature area (Projects, Budgets, etc.)

5. permission_roles: Defines available roles

6. name: Role name (Producer, Line Producer, etc.)

7. description: What this role typically does

8. user_accesses: Links users to projects with permissions

9. user_id: The user
10. project_id: The project
11. grant_id: Their role or permission in this project

12. status: Active, Invited, or Revoked

13. permission_role_links: Maps permissions to roles

14. permission_role_id: The role

15. permission_id: The permission granted

16. menu_items: Navigation items

17. required_permission_key: Permission needed to see this menu

Permission Evaluation

The Query

When checking if a user can access a menu item in a project:

-- Function: get_user_project_menu
-- Returns all menu items with access status for a user in a project

WITH user_permissions AS (
    -- Get all permissions for user's role in this project
    SELECT DISTINCT p.permission_key
    FROM user_accesses ua
    JOIN permission_role_links prl
        ON ua.grant_id = prl.permission_role_id
    JOIN permissions p
        ON prl.permission_id = p.id
    WHERE ua.user_id = $user_id
    AND ua.scope_id = $project_id
    AND ua.scope_type = 'project'
    AND ua.grant_type = 'role'
    AND ua.status = 'Active'
)
SELECT
    mi.*,
    CASE
        WHEN mi.required_permission_key IS NULL THEN true  -- No permission required
        WHEN EXISTS (
            SELECT 1 FROM user_permissions up
            WHERE up.permission_key = mi.required_permission_key
        ) THEN true  -- User has required permission
        ELSE false   -- User lacks permission
    END as can_access
FROM menu_items mi
WHERE mi.is_active = true;

Example Scenarios

Scenario 1: Multi-Project User

• Sarah is a Producer in "Project Alpha"
• Sarah is a Crew Member in "Project Beta"

When Sarah is viewing Project Alpha:

• ✅ Can see Budgets menu (has budget:view:all)
• ✅ Can see Transactions menu (has transaction:view:all)
• ✅ Can edit project settings (has project:edit:all)

When Sarah switches to Project Beta:

• ❌ Cannot see Budgets menu (lacks budget:view:assigned)
• ❌ Cannot see Transactions menu (lacks transaction:view:assigned)
• ✅ Can see Schedule (has schedule:view)

Scenario 2: Permission Inheritance

The Line Producer role includes:

• All budget permissions
• All schedule permissions
• View-only transaction permissions

This is defined in permission_role_links table.

Menu System Integration

Menu Visibility

Each menu item has an optional required_permission_key. The menu is visible only if:

1. No permission is required (required_permission_key is NULL), OR
2. The user has the required permission in the current project or organization context

Dynamic Navigation

The app requests the user's menu for the current project:

// App code
const menuItems = await supabase.rpc('get_user_project_menu', {
  p_user_id: currentUser.id,
  p_project_id: currentProject.id,
});

// Only show accessible items
const visibleMenuItems = menuItems.filter((item) => item.can_access);

Security Considerations

Row Level Security (RLS)

All permission-related tables have RLS enabled with these best practices:

• Users can only see projects they're members of
• Permission definitions are read-only for regular users
• Service role bypasses RLS entirely (no need for explicit policies)
• Always use (SELECT auth.uid()) and (SELECT auth.role()) in RLS policies to prevent per-row re-evaluation
• Never use FOR ALL in RLS policies - create separate policies for SELECT, INSERT, UPDATE, DELETE
• Use EXISTS instead of IN for subqueries in RLS policies for better performance
• Always create indexes for foreign key columns used in RLS policies

Permission Checks

Always validate permissions server-side:

1. API Layer: Check permissions before processing requests
2. Database Layer: RLS policies enforce access control
3. UI Layer: Hide/show features (convenience, not security)

Common Permission Patterns

1. Hierarchical Permissions

• view:all implies view:assigned
• edit:all implies edit:assigned
• edit usually implies view

2. Module-Based Grouping

Permissions are grouped by feature area:

• Projects: Create, view, edit, delete projects
• Budgets: Create, view, edit, approve budgets
• Transactions: Create, view, edit, approve transactions
• Scripts: Upload, view, breakdown scripts
• Schedules: Create, view, edit schedules

3. Scope Modifiers

• :all - Access to all items in the module
• :assigned - Access only to items in assigned projects
• (no modifier) - Global permission

Best Practices

1. Principle of Least Privilege: Users get only the permissions they need
2. Role-Based Assignment: Use roles for common permission sets
3. Project Isolation: Permissions don't leak between projects
4. Audit Trail: Track who granted permissions and when
5. Regular Review: Periodically review user access

Future Enhancements

Direct Permissions (Implemented)

The system supports granting specific permissions directly to a user at the project or organization level, in addition to role-based permissions.

Time-Based Permissions (Planned)

Permissions that expire after a certain date (useful for temporary crew).

Delegation (Planned)

Allow users to delegate some of their permissions to others temporarily.

Troubleshooting

User Can't See Expected Menu Item

1. Check their role in the current project
2. Verify the role has the required permission
3. Ensure the menu item is active
4. Confirm they're viewing the correct project

Permission Changes Not Reflected

• App caches menu structure briefly
• Force refresh or switch projects and back
• Check that user_accesses status is 'Active'

Related Documentation

• Database Schema - Full database structure
• Menu System - Menu configuration details
• RLS Policies - Security implementation