feat: Materialize workspace admin permissions for resource access

Summary

Workspace admins should be able to view/manage all resources within their workspace. Currently, resource access is limited to:

1. Resource owner (profile_id = auth.uid())
2. Explicit permissions via user_effective_permissions

There's no automatic "workspace admin sees everything" functionality.

Problem

Resources (subagents, sandcastles, tools, projects, connections, documents, etc.) use RLS policies that check user_effective_permissions. Workspace admin status is stored as:

user_effective_permissions (
  user_id = <admin_user_id>,
  object_type = 'workspace',
  object_id = <workspace_id>,
  effective_role = 'admin'
)

But there's no automatic propagation of this admin status to individual resources.

Proposed Solution: Materialized Workspace Admin Permissions

Option 1: Trigger-based materialization

Create triggers that materialize workspace admin access to resources:

A) When user becomes workspace admin:

CREATE OR REPLACE FUNCTION sync_workspace_admin_to_resources()
RETURNS TRIGGER AS $$
BEGIN
    -- When someone gains workspace admin
    IF TG_OP = 'INSERT' AND NEW.object_type = 'workspace' AND NEW.effective_role = 'admin' THEN
        -- Add view permission for all resources in that workspace
        INSERT INTO user_effective_permissions (user_id, object_type, object_id, effective_role, source_type, source_id)
        SELECT NEW.user_id, 'subagent', s.id, 'view', 'workspace_admin', NEW.id
        FROM subagents s
        JOIN profiles p ON p.id = s.profile_id
        WHERE p.workspace_id = NEW.object_id
        ON CONFLICT DO NOTHING;
        
        -- Repeat for other resource types...
    END IF;
    
    -- When someone loses workspace admin
    IF TG_OP = 'DELETE' AND OLD.object_type = 'workspace' AND OLD.effective_role = 'admin' THEN
        DELETE FROM user_effective_permissions
        WHERE user_id = OLD.user_id
          AND source_type = 'workspace_admin'
          AND source_id = OLD.id;
    END IF;
    
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

B) When resource is created:

-- In each resource's ownership trigger, also grant to workspace admins
INSERT INTO user_effective_permissions (user_id, object_type, object_id, effective_role, source_type, source_id)
SELECT uep.user_id, 'subagent', NEW.id, 'view', 'workspace_admin', uep.id
FROM user_effective_permissions uep
JOIN profiles p ON p.workspace_id = uep.object_id
WHERE uep.object_type = 'workspace'
  AND uep.effective_role = 'admin'
  AND p.id = NEW.profile_id
ON CONFLICT DO NOTHING;

Option 2: RLS policy with profiles join (has issues)

Add workspace admin check directly in RLS:

OR EXISTS (
    SELECT 1 FROM profiles p
    JOIN user_effective_permissions uep
        ON uep.user_id = auth.uid()
        AND uep.object_type = 'workspace'
        AND uep.object_id = p.workspace_id
        AND uep.effective_role = 'admin'
    WHERE p.id = resource.profile_id
)

Problem: This requires joining to profiles, but profiles has RLS that only allows users to see their own profile. The join fails silently.

Option 3: Add workspace_id to all resources

Add a workspace_id column to resources that currently only have profile_id:

• connections
• subagents
• etc.

Then RLS can directly check workspace admin without joining profiles.

Considerations

1. Permission level for workspace admins: Should they get view, edit, or admin on resources? Probably view by default, with ability to escalate.

1. Performance: Trigger-based materialization adds rows to user_effective_permissions. For large workspaces with many resources and admins, this could be significant.

1. Source tracking: Need a new source_type = 'workspace_admin' to track which permissions came from workspace admin status (for cleanup when admin is revoked).

1. Existing resources: Need backfill migration when this is implemented.

Resources affected

• subagents
• sandcastles
• tools
• projects
• connections
• documents
• document_containers
• chunks (inherit from document)

Current state

All resource RLS policies currently only check owner + direct permissions. No workspace admin access.