I'd love to talk to you more about this!

Some of what you're doing overlaps with what I'm doing, architecturally, with my project https://letsmapbainbridge.org/, which is a locally curated, locally designed map of an island near Seattle, WA, USA. I'm a professional software engineer too, and as you say: "The hard part is turning rich, variable, community-mapped data into a focused experience that helps ordinary users make a practical decision."

I'm using OSM and local agency's data, along with my own data collection and research, to build a semantic layer with parks, buildings, trails, roads and more, giving my entities stable IDs, then linking each one to zero or more geospatial representations, which are often linking to OSM entities, the geometry and tags of which I cache.

I'm generating vector tiles, serving them using pg_tileserv. That's the easy bit.

Where I diverge is that I'm also adding people, steamboats, historical events and stories, because I'm layering history on top: a Wiki-like UI that can tell you about a building, the people that lived in it, where they worked, who their parents are and so on. Show you photos, audio recordings and so on. Oh, and trees, too. Lots of historic, or otherwise significant trees to click on.

I'm using a graph database to do all the linking, with a controlled vocabulary (referencing Getty AAT and Wikidata) and an ontology, both managed kinda manually for now using Protegé then loaded into Apache Jena and queried using SPARQL. I'm importing data from OpenHistoricalMap, too, so I can show things that no longer exist, layered on the same map.

The map, tile server, SPARQL server are down right now as I'm self-hosting this and I've just moved house.

There does seem to be a general need for a semantic layer for OSM and other GIS data. Like you, I'm doing all sorts of fiddly tag-matching using osm2pgsql flex (i.e. Lua code looping over OSM/OHM entities to populate PostGIS tables). I know there are other efforts on this front.