I think people generally are overly pessimistic about other people's interests. While it is true that the majority of people aren't very demanding in what they want to read, watch, play, listen to and so on, there is a solid percentage of people in, pretty much, every field that are interested in a higher quality mods. I don't know much about Foundry, but I don't think Foundry is the best example, since NWO is an MMO, and MMOs naturally rarely attract people interested in the story or deep gameplay, they are centered around endless and mindless grind. I have played WoW and SWTOR for a couple of months each for the story (KotoR and Warcraft RTS series are among my all time favorites), but the experience was less than satisfactory.
SCL is another story. It is a D&D game, with top view, without shiny AAA Witcher 3-like graphics... Far from a mainstream game, closer to the niche category. I'm pretty sure the player base left after a few months of "hot cake period" (I think I just devised a new English slang sentence, didn't I?) will, generally, be quite interested in playing high quality modules, not endless "OMG dragons lololol v. 1.04" cheap "campaigns". At least, there certainly will be a sub-category of players and modders interested in this, so you will always find something interesting to play and someone to play your interesting products.
So far reading the forums gives me a lot of hope. Most people seem to be really interested in D&D gameplay, in the lore, in campaign creation. There have been countless petitions to add scripting, branching dialogue, custom model import... Surely, there will be a lot of, as you call it, "junk", but it shouldn't be hard to find what you want to play and to ignore the junk.
The most modding experience I've had was in Warcraft 3 editor. In Warcraft 3, there are hundreds thousands custom maps uploaded all over the Internet. 99.9% or so of them aren't worth looking at. But those hundreds of gems people have made over the years are resting in "halls of fame", thematic compilations, or actively played online. Poor maps die out very fast, while high quality maps persist and, over the years, are noticed by many players, that then carry them in the masses. Again, pretty much like NWN: in NWN1, there is a lot of trash, but you can be sure that playing something from a Hall of Fame, or with a rating of over 8/10 on the old Vault, is probably not a waste of time.