Project Role Hierarchy Expansion

Overview

The project role system has been expanded to include five new mid-tier roles that sit between Manager and Member in the permission hierarchy. This creates a more granular permission system that allows for better control over project access.

Role Hierarchy

The complete role hierarchy from highest to lowest permissions:

1. Owner (Level 100)

• Full Control: Complete authority over the project
• Unique: Only one owner per project
• Cannot be managed: No one can demote or remove the owner except themselves
• Special Powers: Can delete the project

2. Manager (Level 50)

• Administrative Control: Can do everything except delete the project
• Member Management: Can invite, remove, and change roles for all users below Manager level
• Project Settings: Full access to edit project settings, assets, and all content
• Limitations: Cannot delete the project

3. Mid-Tier Roles (Level 26-40)

These roles are positioned between Manager and Member with varying levels of permissions:

Executor (Level 40)

• Purpose: Project execution and implementation
• Badge Color: Cyan
• Common Use: Team leads, project coordinators

Investor (Level 35)

• Purpose: Funding and financial oversight
• Badge Color: Emerald
• Common Use: Financial stakeholders, investors

Auditor (Level 30)

• Purpose: Compliance and oversight
• Badge Color: Amber
• Common Use: Compliance officers, auditors

Technical (Level 27)

• Purpose: Content creation and technical work
• Badge Color: Green
• Common Use: Developers, content creators, technical staff

Executor, Investor, Auditor, Technical - Shared Permissions:

• ✅ View project
• ✅ Edit project details
• ✅ Create assets
• ✅ View assets
• ✅ Update assets
• ✅ Manage assets
• ❌ Cannot invite members
• ❌ Cannot remove members
• ❌ Cannot manage project settings
• ❌ Cannot delete project

Marketing (Level 26)

• Purpose: Campaign management (future feature)
• Badge Color: Pink
• Common Use: Marketing specialists, campaign managers
• Unique: Has specialized permissions for campaigns only

Marketing - Specialized Permissions:

• ✅ View project
• ✅ View campaigns
• ✅ Create campaigns
• ✅ Edit campaigns
• ✅ Delete campaigns
• ✅ Manage campaigns
• ❌ Cannot edit project details
• ❌ Cannot access general assets
• ❌ Cannot invite members
• ❌ Cannot remove members
• ❌ Cannot manage project settings
• ❌ Cannot delete project

4. Member (Level 25)

• View-Only Access: Can see project content but cannot edit
• Asset Viewing: Can view all project assets
• No Management: Cannot manage members or settings
• Limitations: Reduced from previous capabilities - now strictly read-only

5. Readonly (Level 10)

• Legacy Role: Maintained for backwards compatibility
• Equivalent to Member: Has the same permissions as Member
• Deprecated: Use Member role for new assignments

Permission Matrix

Member Management Permissions

Who can manage whom:

Owner

• ✅ Can manage: Manager, Executor, Investor, Auditor, Technical, Marketing, Member, Readonly
• ❌ Cannot manage: Other Owners

Manager

• ✅ Can manage: Executor, Investor, Auditor, Technical, Marketing, Member, Readonly
• ❌ Cannot manage: Owner, Other Managers

Mid-Tier (Executor, Investor, Auditor, Technical, Marketing)

• ❌ Cannot manage anyone

Member & Readonly

• ❌ Cannot manage anyone

Database Implementation

Permission Function

The core permission checking logic is implemented in user_has_project_permission():

create or replace function public.user_has_project_permission(
p_user_auth_id uuid,
p_project_id uuid,
p_action public.project_action
)
returns boolean
as $$
declare
v_role public.project_role;
begin
select role into v_role
from public.project_members
where project_id = p_project_id and account_id = p_user_auth_id;

if v_role is null then
return false;
end if;

case v_role
-- Owner: Full control (level 100)
when 'owner' then
return true;

-- Manager: Everything except delete project (level 50)
when 'manager', 'admin' then
return p_action != 'delete_project';

-- Executor, Investor, Auditor, Technical: Mid-tier with content CRUD (level 27-40)
when 'executor', 'investor', 'auditor', 'technical' then
return p_action in (
'view_project',
'edit_project',
'manage_asset',
'create_asset',
'view_asset',
'update_asset'
);

-- Marketing: Campaign-only permissions (level 26)
when 'marketing' then
return p_action in (
'view_project',
'view_campaign',
'create_campaign',
'edit_campaign',
'delete_campaign',
'manage_campaign'
);

-- Member & Readonly: View-only (level 10-25)
when 'member', 'readonly' then
return p_action in ('view_project', 'view_asset');
end case;

return false;
end;
$$;

Management Permissions

The current_user_can_manage_project_member() function implements the hierarchy:

create or replace function public.current_user_can_manage_project_member(
p_member_role varchar(50),
p_project_id uuid
)
returns boolean
as $$
declare
v_current_user_role varchar(50);
v_is_primary_owner boolean;
begin
-- Check for primary owner status
select exists (
select 1 from public.projects
where id = p_project_id
and primary_owner_user_id = auth.uid()
) into v_is_primary_owner;

if v_is_primary_owner then
return true; -- Primary owners can manage everyone
end if;

-- Get current user's role
select role into v_current_user_role
from public.project_members
where project_id = p_project_id
and account_id = auth.uid();

if v_current_user_role = 'owner' then
return p_member_role != 'owner';
elsif v_current_user_role in ('manager', 'admin') then
return p_member_role not in ('owner', 'manager', 'admin');
else
return false;
end if;
end;
$$;

UI Implementation

Role Badges

Each role has a distinct badge color for easy visual identification:

const roleStyles = {
owner: 'bg-purple-50 text-purple-600',
manager: 'bg-orange-50 text-orange-600',
executor: 'bg-cyan-50 text-cyan-600', // New
investor: 'bg-emerald-50 text-emerald-600', // New
auditor: 'bg-amber-50 text-amber-600', // New
technical: 'bg-green-50 text-green-600', // New
marketing: 'bg-pink-50 text-pink-600', // New
member: 'bg-blue-50 text-blue-500',
};

Role Selectors

All role selection components have been updated:

1. Invite Members Dialog - invite-project-members-dialog-container.tsx
2. Update Member Role Dialog - update-project-member-role-dialog.tsx
3. Role Badge Display - project-role-badge.tsx

Schema Validation

Zod schemas ensure type safety:

export const InviteProjectMembersSchema = z.object({
projectId: z.string().uuid(),
invitations: z.array(
z.object({
email: z.string().email(),
role: z.enum([
'owner',
'manager',
'executor', // New
'investor', // New
'auditor', // New
'technical',
'marketing',
'member',
]).default('member'),
})
).min(1).max(5),
});

Use Cases

When to Use Each Mid-Tier Role

Executor

Scenario: Construction project manager

• Needs to update project progress
• Manages project assets and documents
• Should not have access to invite/remove team members
• Perfect fit: Can edit and manage content, but not team

Example:

await service.inviteExternalUserToProject({
projectId: projectId,
email: 'project-manager@construction.com',
role: 'executor',
});

Investor

Scenario: Financial stakeholder

• Needs to see all project details
• Can update financial documents and reports
• Should not manage team membership
• Perfect fit: Can view and update without team management

Example:

await service.addProjectMember({
projectId: projectId,
userId: investorUserId,
role: 'investor',
});

Auditor

Scenario: Compliance officer

• Needs to review all project content
• Can add audit reports and findings
• Should not manage team or settings
• Perfect fit: Can document findings without administrative access

Example:

await service.updateProjectMemberRole({
projectId: projectId,
userId: auditUserId,
role: 'auditor',
});

Technical

Scenario: Software developer or content creator

• Needs to create and update technical content
• Can manage project documentation and assets
• Should not manage team membership
• Perfect fit: Can focus on technical work without administrative overhead

Example:

await service.inviteExternalUserToProject({
projectId: projectId,
email: 'developer@company.com',
role: 'technical',
});

Marketing

Scenario: Marketing campaign manager (future campaigns feature)

• Needs to manage marketing campaigns only
• Should not have access to general project content or assets
• Should not manage team membership or settings
• Perfect fit: Specialized role with campaign-only permissions

Important Note: The Marketing role is designed for a future campaigns feature that will be developed. The permissions are already configured in the database:

• Can view project (basic access)
• Can view, create, edit, delete, and manage campaigns
• Cannot access general content, assets, members, or settings

Example:

await service.inviteExternalUserToProject({
projectId: projectId,
email: 'campaign-manager@marketing.com',
role: 'marketing',
});

// When campaigns feature is implemented, Marketing role will be able to:
// - Create new marketing campaigns
// - Edit campaign details
// - Delete campaigns
// - Manage campaign settings
// But NOT access general project content or team management

Migration Guide

From Previous System

Old Member Role Behavior:

• ✅ Could view project
• ✅ Could edit project (in some cases)
• ✅ Had more permissions

New Member Role Behavior:

• ✅ Can view project
• ✅ Can view assets
• ❌ Cannot edit anything
• This is now strictly read-only

Migration Strategy:

1. Review existing Members: Check who has Member role currently
2. Assess needs: Determine if they need edit permissions
3. Upgrade if needed: Change to Executor, Investor, or Auditor as appropriate

-- Example: Upgrade members who need edit access to Executor
UPDATE project_members
SET role = 'executor',
updated_at = now()
WHERE role = 'member'
AND account_id IN (
-- List of users who need edit permissions
SELECT id FROM accounts WHERE ...
);

Testing

Test Permission Boundaries

// Test: Executor can edit project
const canEdit = await user_has_project_permission(
executorId,
projectId,
'edit_project'
); // Should return true

// Test: Executor cannot invite members
const canInvite = await user_has_project_permission(
executorId,
projectId,
'invite_member'
); // Should return false

// Test: Marketing can manage campaigns but not edit project
const canManageCampaign = await user_has_project_permission(
marketingId,
projectId,
'manage_campaign'
); // Should return true

const canEditProject = await user_has_project_permission(
marketingId,
projectId,
'edit_project'
); // Should return false

// Test: Manager can manage Executor and Marketing
const canManageExecutor = await current_user_can_manage_project_member(
'executor',
projectId
); // Should return true (if current user is Manager)

const canManageMarketing = await current_user_can_manage_project_member(
'marketing',
projectId
); // Should return true (if current user is Manager)

// Test: Member has view-only access
const canEditAsset = await user_has_project_permission(
memberId,
projectId,
'update_asset'
); // Should return false

Manual Testing Checklist

• [ ] Owner can manage all roles except other owners
• [ ] Manager can manage all roles below Manager
• [ ] Executor/Investor/Auditor/Technical can edit project and assets
• [ ] Executor/Investor/Auditor/Technical cannot invite or remove members
• [ ] Marketing can view project and manage campaigns
• [ ] Marketing cannot edit project or access assets
• [ ] Marketing cannot invite or remove members
• [ ] Member can only view project and assets
• [ ] Member cannot edit anything
• [ ] Role badges display with correct colors
• [ ] Role selectors show all 9 roles (owner, manager, executor, investor, auditor, technical, marketing, member, readonly)
• [ ] Invitations work with new roles

Troubleshooting

Common Issues

Issue: User with Executor role cannot edit project

Solution: Check RLS policies and ensure user_has_project_permission() is called correctly.

-- Verify function returns correct result
SELECT user_has_project_permission(
'user-uuid'::uuid,
'project-uuid'::uuid,
'edit_project'::project_action
);

Issue: Manager cannot change member to Executor

Solution: Verify the manager role is correctly set and the permission function is working.

-- Check current user's role
SELECT role FROM project_members
WHERE account_id = auth.uid()
AND project_id = 'project-uuid'::uuid;

-- Verify management permission
SELECT current_user_can_manage_project_member(
'executor'::varchar,
'project-uuid'::uuid
);

Issue: Role badge not displaying

Solution: Ensure translation key exists in common.json:

{
"roles": {
"executor": { "label": "Executor" },
"investor": { "label": "Investor" },
"auditor": { "label": "Auditor" }
}
}

Best Practices

1. Use Semantic Role Names

• Choose the role that best describes the user's function
• Executor for implementation
• Investor for financial involvement
• Auditor for compliance and oversight
• Technical for developers and content creators
• Marketing for campaign managers (when campaigns feature is available)

2. Use Specialized Roles for Feature-Specific Access

• Marketing role is designed for campaign-only access
• Prevents over-permissioning by limiting to specific features
• Consider creating similar specialized roles for other features in the future

3. Minimize Owner Count

• Keep only one Owner per project when possible
• Use Manager for administrative tasks

4. Regular Permission Audits

• Review role assignments quarterly
• Ensure users have appropriate access levels
• Downgrade or upgrade as responsibilities change

5. Member Role for External Stakeholders

• Use Member role for clients, observers
• Provides visibility without edit access
• Safe for external parties

6. Document Role Assignments

• Keep track of why each role was assigned
• Makes future audits easier
• Helps with compliance requirements

Security Considerations

Row-Level Security (RLS)

All permission checks flow through database functions with RLS policies:

-- Example RLS policy on projects table
create policy "Users can view projects they are members of"
on projects for select
using (
exists (
select 1 from project_members
where project_id = projects.id
and account_id = auth.uid()
)
);

Permission Isolation

• Mid-tier roles cannot escalate privileges
• No role can promote itself
• Member management requires Manager or Owner role
• All changes are audited through updated_at timestamps

API Security

All server actions use enhanceAction for validation:

export const updateProjectMemberRoleAction = enhanceAction(
async (data) => {
// Validates role is in allowed enum
// Checks current user has permission
// Applies change atomically
},
{
schema: UpdateProjectMemberRoleSchema,
},
);

API Reference

Server Actions

addProjectMember

type: (params: {
projectId: string;
userId: string;
role?: 'manager' | 'executor' | 'investor' | 'auditor' | 'technical' | 'marketing' | 'member';
}) => Promise<void>

inviteExternalUserToProject

type: (params: {
projectId: string;
email: string;
role?: 'owner' | 'manager' | 'executor' | 'investor' | 'auditor' | 'technical' | 'marketing' | 'member';
}) => Promise<Invitation>
  ```

  #### `updateProjectMemberRole`
  ```typescript
  type: (params: {
  projectId: string;
  userId: string;
  role: 'owner' | 'manager' | 'executor' | 'investor' | 'auditor' | 'technical' | 'marketing' | 'member';
  }) => Promise<boolean>
    ```

    ### Database Functions

    #### `user_has_project_permission()`
    ```sql
    user_has_project_permission(
    p_user_auth_id uuid,
    p_project_id uuid,
    p_action project_action
    ) returns boolean
    ```

    #### `current_user_can_manage_project_member()`
    ```sql
    current_user_can_manage_project_member(
    p_member_role varchar(50),
    p_project_id uuid
    ) returns boolean
    ```

    #### `is_project_admin()`
    ```sql
    is_project_admin(p_project_id uuid) returns boolean
    -- Returns true for Owner and Manager roles
    ```

    ---

    ## Related Documentation

    - [Project Settings Feature](./project-settings.mdoc) - Settings management with role checks
    - [Projects Overview](./projects-overview.mdoc) - Core project features
    - [Admin to Manager Migration](./role-admin-to-manager-migration.mdoc) - Role renaming guide

    ---

    ## Summary

    The expanded role hierarchy provides:

    ✅ **9 distinct roles** with clear permission boundaries
    ✅ **5 new mid-tier roles** (Executor, Investor, Auditor, Technical, Marketing)
    ✅ **Specialized role support** for feature-specific permissions (Marketing for campaigns)
    ✅ **Reduced Member permissions** to view-only
    ✅ **Granular control** over project access
    ✅ **Clear visual indicators** with colored badges
    ✅ **Type-safe implementation** across the stack
    ✅ **Comprehensive documentation** for developers
    ✅ **Future-ready** for campaigns feature development

    This system allows organizations to precisely control access to projects while maintaining security and clarity
    around permissions. The Marketing role demonstrates how specialized roles can be created for specific features,
    establishing a pattern for future feature-specific roles.