Skip to content

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.

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

Sensitive Data Permissions

Six permission keys gate visibility and mutation of sensitive records and fields. The four scope-aligned :view/:mark keys gate whole-row visibility on entities, project_relationships, budget_headers, budget_items (and everything derived from them — transactions, payments, approvals, attachments). The two scope-agnostic field-category keys (:view_pii, :view_payment_details) gate column-level masking on rows the caller can already see. Sensitivity is enforced primarily at the RLS layer; the keys here govern both the read-side carve-outs and the write-side toggle.

Key Scope What it controls
sensitive_data:project:view Project See sensitive project_relationships, budget_headers, budget_items, and everything derived from them within that project (transactions, payments, approvals, attachments). Sensitivity is recorded as sensitive_rules rows whose required_permissions must be a subset of the caller's project clearance.
sensitive_data:project:mark Project Create / update / delete sensitive_rules rows for project-scoped records (project_relationships, transactions, payments, budget_headers, budget_items). Implies :project:view. Enforced inline by sensitive_rules policies via get_user_clearance('project', project_id).
sensitive_data:organization:view Organization See sensitive entity rows in that organization and everything derived from them. Required-permission check resolves against organization scope via get_user_clearance('organization', org_id).
sensitive_data:organization:mark Organization Create / update / delete sensitive_rules rows for entity records. Implies :organization:view. Enforced inline by the sensitive_rules INSERT/UPDATE/DELETE policies via get_user_clearance('organization', organization_id).
sensitive_data:view_pii Scope-agnostic See PII fields (phone, email) on visible sensitive rows. Implied by both :project:view and :organization:view. Used by the masking views (v_entities, v_project_relationships) — when the caller's clearance contains this key, the CASE block returns the unmasked column value.
sensitive_data:view_payment_details Scope-agnostic See payment-detail fields (IBAN, bank account, SWIFT, bank name) on visible sensitive rows. Same shape as :view_pii, different field set.

Implies chain (statically expanded in the role-binding seed):

  • :project:mark → :project:view, :view_pii, :view_payment_details
  • :organization:mark → :organization:view, :view_pii, :view_payment_details
  • :project:view → :view_pii, :view_payment_details
  • :organization:view → :view_pii, :view_payment_details

The expansion is done at grant time by the Phase 1 migration with an inline INSERT INTO permission_role_links ... ON CONFLICT DO NOTHING covering the seed roles. RLS reads a flat permission set — no runtime closure walk.

Why :mark implies :view: A user who can hide rows must also be able to verify the hide happened (and unhide later). Splitting the read/write gates would create a footgun where someone marks a row sensitive and immediately loses access to confirm it.

Why :view implies the field-category keys: A user who can see the whole sensitive row can already read every field on it. Splitting the field-category gates from row-level view would be operationally meaningless — there's no scenario where a user has full row visibility but can't see a specific column. The field-category keys exist to support refined grants in the future (e.g., a future role that can see sensitive rows but with PII masked), but today they're always granted together with :view.

Default role assignments: The project trio (:project:mark + :project:view + the two field keys) is bound to project:owner and project:admin. The organization trio (:organization:mark + :organization:view + the two field keys) is bound to organization:owner. None of these are granted to other built-in roles — sensitive data is opt-in.

Where they are checked:

  • RLS policies on every gateable table call row_is_visible_to_caller(<own_type>, <pk>, <ancestors_jsonb>) (DEFINER, STABLE). The helper resolves the rule's scope from sensitive_rules.organization_id / project_id and consults get_user_clearance('organization' | 'project', ...) accordingly. When no sensitive_rules row matches the record or any ancestor, the predicate is vacuously TRUE.
  • Masking views (v_entities, v_project_relationships, v_transactions, v_payments, v_budget_headers, v_budget_items, the 3 approval views) embed CASE blocks that check whether the field's required key from sensitive_rules.field_required_permissions is in the caller's clearance: WHEN field_required_permissions ? '<col>' AND NOT (key = ANY(get_user_clearance(...))) THEN NULL ELSE column END AS column.
  • Server-side gates (approval routing, digest cron, AI processing) call row_is_visible_to_caller(record_type, record_id, '[]'::jsonb, recipient_user_id) — the 4-arg form switches the internal lookup to get_user_clearance_for_user(recipient_user_id, ...) because service_role has NULL auth.uid().
  • Approval router (services/approval/base.ts) reads the record's effective sensitive_required_permissions via getRecordRequiredPermissions(recordType, recordId) and filters tier approvers by getUserClearanceForUser(user_id, 'project', projectId) at submission time. Tiers with zero cleared approvers are stamped Skipped with a sensitivity reason; if no tier is routable the request falls through to soft approval (also clearance-filtered) and ultimately auto-approves with the routing failure recorded in the request's reason. When a tier completes, the router re-evaluates the upcoming tiers' clearance before activating them — instances whose approver lost clearance since submission get Skipped, and a fully-uncleared upcoming chain auto-approves.
  • UI reads the keys from usePermissions().hasPermission(PERMISSIONS.SENSITIVE_DATA_*) to decide whether to render the Manage Access button or per-field switches in the Manage Access dialog. Hiding controls is a UX convenience — the DB enforces the real gate inline in the sensitive_rules INSERT/UPDATE/DELETE policies, which call get_user_clearance(scope, scope_id) and assert that the resulting array contains sensitive_data:<scope>:mark.

To extend the sensitive-data system (new sensitive-eligible table, new restrictable field), see ADDING_SENSITIVE_DATA.md.

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'