Have you tried Immich? It runs local and is open source. I haven’t yet but was planning to soon as I have the same struggle

Typically I’ve found it better to be combined both for process integration and ease of administration like you said.

It can make sense to separate it but it totally depends on the company.

I also think it’s always easier to start with everything in the cmdb and separate things later if it makes sense to do so vs the other way around.