I often have to generate ad-hoc recursive queries for bills of materials. Any one have good ideas to make something more self serve for end users? I guess just some sort of app where the user supplies the top level part works, but I wonder if it should be pre-exploded? Often I am given a list of many parts to explode.

"Something more self-serve" is essentially what graph databases provide.

If you don't have the time to use one of those or build your own (and you probably shouldn't) sort of thing on top of SQL, then you can instead define a bunch of VIEWs using recursive queries and GROUP BY and aggregation functions to provide something simple for your users to query.

If you do want to build something more general purpose... If you begin by modeling your schema in something other than SQL DDLs (like with an AST) and then model FKs as bi-directional relationships (because they are), and if you add a way to group those relationships, and further provide a way to define a subset of like columns from all the tables in a graph defined by a group of those relationships, then suddenly you can also come up with a simple(ish) language for expressing [sub-]graphs that you want to fetch. And there's your ad-hoc query language for dealing with recursive graphs in your relational data.