In contrast, CSS modules is far simpler: every class used in a CSS file is replaced with a random identifier (normally something deterministic), and any JS file that imports that CSS file will receive an object mapping the original class names to the new identifiers. Essentially, the only change from normal CSS is that class names are no longer global - everything else remains the same.
In my experience, I tend to run into far fewer surprising situations with CSS modules because it really is just CSS. If I target a class and all of its children, then I get what I expect, regardless of how the components are laid out in practice. In contrast, with scoped CSS, I tend to find that the scoping rules get in the way - my CSS is now tied to my components, as opposed to being able to be used and reused in multiple places. It's not a big issue (and I've used it plenty, and most of the time it's fine), but I always feel like it's an extra layer on top of CSS that I don't actually need.
Whereas, ironically, CSS-in-JS tends to feel closer to true CSS (and CSS modules) because there aren't any surprises. It's just CSS declarations (albeit often written in an unusual syntax), except that class names are locally scoped. In that sense, I can use my existing CSS knowledge and craft selectors however I want.
I understand the value of SFCs for people who don't feel as comfortable with the scripting side of things, but I do wonder if that's a bit of a false economy. At the end of the day, Vue is a tool for writing Javascript components. If you've got a team that doesn't want to write Javascript, then something like Alpine.js is probably a much better option - minimise the JS side of things completely and just concentrate on the HTML and CSS sides. But component-based web development is going to involve combining Javascript, CSS, and the DOM, and that involves understanding all three technologies, and not just specialising in one of them.
E: as a complete aside, I love the user name! I've just finished rereading the Watch books and I'm deciding which thread to go down next.