This is the part where people get excited about AI. I personally think they're dead wrong on the process, but strongly empathize with that end goal.

Giving people the power to make the interfaces they need is the most enduring solution to this issue. We had attempts like HyperCard or Delphi, or Access forms. We still get Excel forms, Google forms etc.

Having tools to incrementaly try stuff without having to ask the IT department is IMHO the best way forward, and we could look at those as prototypes for more robust applications to create from there.

Now, if we could find a way to aggregate these ad hoc apps in an OSS way...

Nor can the design world, for that matter. They think that making slightly darker gray text on gray background using a tiny font and leaving loads of empty space is peak design. Meanwhile my father cannot use most websites because of this.

I think it's because they are not using the product they are designing. A lot of problems you typically see in modern UIs would have been fixed before release if the people writing it were forced to use it daily for their job.

For example, dropdown menus with 95 elements and no search/filter function that are too small and only allow you to see 3 lines at a time.

Are there any good resources for developing good UX for necessarily complex use cases?

Not at all. Talented human artists still impress me as doing the same level of deep "wizardry" that programmers are stereotyped with.