According to this: Installation - Home Assistant if I want to use add-ons in Home Assistant, I can choose OS, or supervised modes. I have no interest in OS mode (I use the rpi for other things as well), but supervised seems over the top (don’t really think I need a full container for my purposes). Is there any way to enable add-ons in ‘core’ mode? I was running an older version a while ago, and I seem to recall being able to use add-ons in the straight python mode?
No.
If using core, just install the applications you need.
E.g. you want the MariaDB addon, just install MariaDB instead.
Perhaps I’m misunderstanding what add-ons do; I thought they were required to interact with things connected directly to the rpi (e.g. CEC devices, camera, sensors). Can HA work with such directly-attached without addons?
Add-on’s are all individual containers pre configured to interact with HA
Perhaps you are. See at the top of this page where it says “integrations”. Click that. Anything listed there is built in to home assistant and needs no addons or custom integrations.