Say I was building a scene like King's Landing. For each building, I'd like to have a file representing that building, that I can instance around a scene representing, say, a city block. I'll do a linked import of the building, so any changes I do to the building will take effect around the scene, and I'd like to make use of arrays and other modifiers for ad hoc instance changes. Again, my motivations for wanting a non destructive workflow are completely the same, regardless of whether that building is an object or a collection.
Now, as of now, my only option is for it to be an object. However, what if I want to use a collection of other objects in order to build the building? I want a cellar door that I'd like to have instanced. I'd like a ladder outside, and a few barrels on the second story. I want these instanced for all the same reasons. My preference, of course, would be for the building to be a collection of all these things, but if I want to then treat my building as one of these such objects at the next higher level of detail, I can't.
So you're dismissive of my intentions by insisting that the difference is meaningful, whereas I simply see it as a failure of abstraction. Objects and collections are ultimately both worldspace coordinates holding mesh data, the only difference is if they're nested at all. And like I said, collections CAN be nested. That relationship is already established. Collections CAN be instanced, that use case is already established. It's really just the modifiers that are missing. And from a programming perspective, the logic of "get the mesh data from the object I've been assigned to in order to apply this transformation" is the only thing that needs to be tweaked. Rather than going one level deep, it will say "and if this is mesh data, stop, else iterate."
What you want would be less of an issue in a tool that allowed for a more procedural approach where the assembly of complex objects is part of a modifiable construction history that is separate from the final scene graph. In such a context, merging objects is technically destructive, but repeatable. I've written such a modeler once.
So it's not actually about the modifiers being able to be used for what you want in one scenario and not being usable in in a slightly different scenario; the array modifier is not really the tool for the job here at all.
The tool to be used for both of the use cases here is instancing, not the modifiers. Setting up your scene with multiple levels of collections and using proper instancing (which the array modifier is not) certainly is possible, there's just a couple of pieces of the puzzle that you seem to be missing (or unobvious hoops you have to know about, depending how you look at the UX).
Instead of arguing about philosophy and good UX design, I hope you don't mind me elaborating in a more step by step way on how to actually set a scene such as your example up.
You can spawn a collection instance at the location an "empty" type object, by selecting "collection" as the instancing type at the instancing section of object properties. You can have many of these empties and position them manually if you were so inclined.
The other thing is that you can use a mesh to spawn instances of an object at each face of the mesh. So you can create a mesh object, and set up instancing to spawn aforementioned "empty" object, that in turn spawns the whole collection, at each face of the mesh. (You do this by parenting the empty to the mesh, and setting instancing type to "Face" in the instancing panel).
This mesh would typically be rather simple, for example just a couple of disjointed faces scattered where you want your collections to spawn. Or just a single face, and mesh modifiers applied to it, such as array. So instead of arraying the collection, we array a mesh consisting of a single face, and each face of the arrayed mesh spawns an empty that spawns the collection, but the end result is the same. (No idea why each face can't be set to spawn the collection directly and we need an intermediate "empty").
You can also set instances to appear from particle systems etc, for example if you want your houses to just be randomly scattered on a surface of an object (like mesh in shape of the city or so)
The point is, the fact that you can do instancing through the workarounds means that there isn't a technical barrier to doing any of this. You say that it would introduce footguns to the UX, but I consider the workarounds to be footguns.
But yes, I do see the use case for collection level modifier stacks, I just don't think implementing those is exactly as trivial as you figure it out to be. Not impossible either of course (for example by doing some sort of copy-on-write thing if the geometry is modified), but it'd be introducting a different kind of copying mechanism with significant performance differences, which smells a bit footgunny to me
Now, these objects can be combined into a higher level organizational container called a collection. These, too, have a position, scale, rotation, etc, and behave much the same way as objects. They can even be combined into parent collections.
Now, what I would love would be to have them gain much of the functionality of objects that they don't currently have, most importantly modifiers.
Say I was building a well. I make a single brick, an object. I can array this brick object to create a wall, and I can add a corner to the wall, and add it all to a collection. Now I have this wall "object," and maybe I want to array it 8x around a center point in order to create an octagonal well. I can't, because I can't place modifiers on collections. For all intents and purposes, it is just another object, and would love to see any push towards allowing it to behave as such.