I can't speak to the early history of *_vendor
packages in ROS 2 but my understanding is that vendor packages exist to provide software that ROS 2 needs on platforms where it may not be available, or where a different version than what is available is required.
Most vendor packages first check to see if the vendored software is available, and if not build and install it within the vendor package directory using CMake's ExternalProject features.
Prior to the homespun Chocolatey packages tinyxml_vendor was the best example of a case where a library wasn't available on all platforms. Ubuntu had libtinyxml-dev via apt and MacOS had tinyxml via homebrew but there was no such package available for Windows. On a correctly set up Ubuntu or MacOS system, tinyxml_vendor acts as a thin shim for the system package. But on Windows it takes care of fetching, building, and installing the software and making that installation available to the rest of the ROS 2 workspace.
the *_cmake_module
packages exist to provide CMake glue for system packages that either don't provide it or provide it in an inconsistent fashion. In standard projects each project would just embed its own FindWhatever.cmake but since many packages in the ROS ecosystem are built together we want to make those modules available for any packages that need them rather than duplicating effort.