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'  -- illustrative only; see note below
)
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;

The status = 'Active' filter above is a simplification. The real permission-resolution view (v_user_permissions, consumed by caller_accessible_project_ids and caller_has_permission) admits status Active OR Invited — an invited user resolves permissions before accepting — and additionally filters on expiry (expires_at in the future or NULL) and, where a project_relationship_id is present, the relationship being active (access_status in Active/Invited). This is the "permission-resolution" contract; it is deliberately looser than the sensitive-data clearance contract (get_user_clearance), which requires status Active only and also checks revoked_at IS NULL. See "Access primitives & semantic contracts" below.

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 organisation 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

Access primitives & semantic contracts

RLS policies and masking views compose a small set of access-primitive functions. Each carries a semantic contract (named in its DB COMMENT) that must not be confused with the others:

  1. Permission-resolution (caller identity). caller_accessible_project_ids(text[], boolean) — set-based, returns the projects the caller has access to (bounded-capable in policies, see below). caller_has_permission(text[], uuid, boolean) — per-project probe (per-row when handed a row column). Both resolve via v_user_permissions: status Active OR Invited, not expired, relationship (when present) active.
  2. Sensitive-data clearance. get_user_clearance(scope, id) (caller) and get_user_clearance_for_user(user_id, scope, id) (explicit user, for service_role paths). Stricter than permission resolution: status Active only, not expired, revoked_at IS NULL, role is_active. Never interchangeable with the permission-resolution contract — do not substitute Active+Invited permission access where Active-only clearance is required, or vice versa.
  3. Scope. Project-only vs organisation→project cascade is always explicit, never implied. The permission-resolution helpers take a boolean arg (p_check_org_cascade); the clearance functions take an explicit scope_type + scope_id pair; the visibility helpers resolve each sensitive_rules row at that rule’s own scope.
  4. Permission requirement (permission-resolution helpers only). An empty key array means "any project access"; a non-empty array means "hold at least one of these specific keys".
  5. Identity. Caller (auth.uid()) vs an explicit user (…_for_user, or the 4th arg of row_is_visible_to_caller) — the explicit-user variants exist for service_role paths where auth.uid() is NULL.
  6. Project membership (caller identity). caller_member_project_ids() (DEV-781) — set-based, the projects where the caller holds ANY user_accesses row with scope_type='project', status='Active' (never Invited), not expired. A third contract: looser than clearance (no revoked_at / role checks) and stricter than permission resolution (no Invited) with no relationship-status or grant-join filters. It mirrors the former project_relationships SELECT-policy EXISTS exactly. Do not substitute it for either of the other contracts.
  7. Sensitive-data restriction set (caller identity, own-row rules only). private.caller_restricted_project_relationship_ids() (DEV-781) — the set-based sensitivity primitive Story 3 deferred: the project_relationships ids whose own sensitive_rules row the caller's Active-only clearance does not satisfy (<@, all keys). Bounded by rule count; ancestor-cascade rules stay with per-row row_is_visible_to_caller. Lives in the non-API private schema — its raw output enumerates cross-tenant restricted-record ids, so it must never be PostgREST-exposed.

Permission-vs-clearance asymmetries (known divergences — follow-up ticket recommended). Two ways a grant can be admitted by permission resolution while being rejected by clearance, both pinned by tests/07_access_primitive_semantics.sql:

  1. revoked_at: v_user_permissions does not check revoked_at; it relies on the grant's status being flipped to Revoked. The clearance functions do check revoked_at IS NULL. A row left status = 'Active' with a non-null revoked_at still grants permissions but not clearance.
  2. Role is_active: v_user_permissions resolves role-derived keys through permission_role_links without joining permission_roles, so a grant referencing a deactivated role still yields its permissions. The clearance functions join permission_roles and filter is_active = true, so the same grant yields no clearance.

Close with a follow-up ticket (align v_user_permissions with the stricter checks, or guarantee status/role lifecycle flips keep the fields in lockstep) — do not change either side inside performance work.

Bounded evaluation in policies. In SELECT policies scoping many rows, the set-based primitives (caller_accessible_project_ids(...), caller_member_project_ids(), private.caller_restricted_project_relationship_ids()) must be called in the bounded = ANY (SELECT unnest(fn(...))) form so its evaluation is independent of row count (once per executor process; parallel workers may each build the subplan) rather than re-executed per row (STABLE alone does not guarantee this). See .claude/rules/rls-policies.md § "Bounded Access-Primitive Evaluation".

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)

Least-privilege grant invariant (invite / grant-access flow)

Granting access to a relationship (createProjectRelationshipWithPermissions, grantAccessToExistingRelationship, updateRelationshipPermissions) runs under a service-role client with RLS bypassed, so the action layer — not RLS — is the enforcement point. Two checks gate every write, both before the service-role escalation in the withRelationshipPermissionGate / withResolvedRelationshipPermissionGate wrappers (services/project/actions.ts):

  1. Membership: the caller must hold project_relationship:can_invite on the target project (assertPermission).
  2. Subset: the caller may only grant roles/permissions that are a subset of what they themselves hold on that project. A holder of {view, can_invite} cannot assign project:admin, payment:process, etc. — so inviting a colleague can never escalate any account (including the caller's own) above the inviter's level. A grant that exceeds the caller's set is rejected atomically (nothing written) with the non-leaky message "You can only grant permissions you hold on this project".

Key rules of the subset check:

  • The caller's held set is resolved via getUserProjectPermissions(ctx().userId, projectId) — the caller's verified id from the request context (never a client-supplied id), consistent with worker-safe gated flows.
  • The comparison is project-scope keys only (ScopeType.PROJECT). A project owner legitimately does not hold some organisation-scope keys that assignable roles carry (e.g. the project:admin role links organisation:edit), so an all-scope comparison would falsely block owners. Scoping to project keys keeps owners/full-holders behaviourally unchanged while still blocking real escalation. All these grants are written with scope_type: PROJECT anyway.
  • The auto-appended organisation:viewer role is exempt by construction: it is added server-side (includeOrgViewerRole in createUserAccessRecordsForRelationship), separate from the caller-supplied payload, so it never enters the checked set.
  • The pure predicate lives in utils/permissions.ts (assertGrantWithinGranterPermissions + the scope-resolution helpers) and is unit-tested in isolation. The UI mirrors the same rule for UX — the picker disables (with a tooltip) roles/permissions the caller can't assign (isRoleAssignable / isPermissionAssignable) — but the server check is the real gate.

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:organisation:view Organisation See sensitive entity rows in that organisation and everything derived from them. Required-permission check resolves against organisation scope via get_user_clearance('organisation', org_id).
sensitive_data:organisation:mark Organisation Create / update / delete sensitive_rules rows for entity records. Implies :organisation:view. Enforced inline by the sensitive_rules INSERT/UPDATE/DELETE policies via get_user_clearance('organisation', organisation_id).
sensitive_data:view_pii Scope-agnostic See PII fields (phone, email) on visible sensitive rows. Implied by both :project:view and :organisation: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
  • :organisation:mark → :organisation:view, :view_pii, :view_payment_details
  • :project:view → :view_pii, :view_payment_details
  • :organisation: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 organisation trio (:organisation:mark + :organisation:view + the two field keys) is bound to organisation: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.organisation_id / project_id and consults get_user_clearance('organisation' | '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 organisation level, in addition to role-based permissions.

Time-Based Permissions (Implemented)

Permissions that expire after a certain date (useful for temporary crew) are implemented and enforced. user_accesses.expires_at holds the optional expiry; both the permission-resolution path (v_user_permissions → caller_accessible_project_ids / caller_has_permission) and the sensitive-data clearance functions (get_user_clearance / get_user_clearance_for_user) exclude grants whose expires_at is in the past. An expired grant stops conferring both permissions and clearance without any status change.

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'