It is almost always simpler to create a central server.
Normally the argument here focuses on security, that a central server makes it possible to implement fine-grained access control without having to spray full database credentials all across your client deployments. That does not seem to be a concern for you.
SQLite doesn't mesh well with your intended approach
But here the crucial issue is that you're intending to publish a SQLite database over a network file share. That might technically work, but it's really not what SQLite is intended for and you are likely to run into technical problems or performance limitations. From the SQLite document Appropriate Uses For SQLite:
Situations Where A Client/Server RDBMS May Work Better
If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.
A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network.
So the authors of SQLite recommend that you choose solution #1, that your central collator completely owns the database and provides an interface through which clients can interact. As the same document summarizes the intended use of SQLite succinctly:
SQLite does not compete with client/server databases. SQLite competes with fopen().
If you were to use a client/server RDBMS such as Postgres, there might be an argument that client could access it directly. That would definitely work on a technical level. But that would just be a central server solution, albeit using the Postgres wire protocol instead of a HTTP web API.
Development effort seems pretty balanced.
Now lets ignore technical details of SQLite and talk about the effort of developing this solution. You raise a couple of concerns with a centralized solution.
Before we discuss those, I'd like to point out that your project features some “essential complexity” that has to be stuffed somewhere into the architecture. In particular, I see the following aspects:
- the clients need the results of queries to render the dashboards/control panels
- queries are likely to change during development, but will stay fixed afterwards
- the structure of queries is tightly coupled to the structure of the database
- server–client communication involves significant boilerplate
Indeed, this suggests that solution #2 will be simpler, just because it has fewer components: if the clients communicate directly with the DB, then you don't have to spend the effort for creating a server–client API. Not infrequently, wrappers, anti-corruption layers, interfaces, and other extra layers are just a waste of time.
However, I suspect that you're making that effort sound larger than it is. The clients don't have to make custom queries, they need the results of queries. An API that lobs a bunch of JSON back and forth is comparatively easy to write, especially in languages like C# that have good tooling for serialization etc. I suspect that the structure of the JSON (or other interchange format) will stay fairly stable even if the user interface and the queries evolve, since the purpose of a query or API endpoint won't change wildly.
This is not an argument for solution #1, but I think there wouldn't be an immense difference in development effort. Either way, you'll have to implement the difficult parts, like rendering the dashboards and making the queries. These parts do not get simpler if you make the queries in the client.
It's worth pointing out that I could be entirely wrong here.
I am assuming that your queries will eventually be fairly static.
However, if your user interface allows very complex and custom user-defined query operations that would have to be integrated in the database queries, then it would likely take excessive effort to describe that via a web API. Such extremely dynamic queries would be a good argument in favour of doing as much as possible in the client.
Discussion of your concerns
You voiced a couple of concerns with regards to having a central server.
The "Central Collator" is becoming monolithic - the data collation and control should in my opinion already be split apart as they are genuinely separate - adding a "database server" element to it makes this worse.
If the Central Collator owns the database, then it should typically broker any access to the data source. Sharing databases between services is generally considered to be an anti-pattern.
Instead of thinking about something being “monolithic”, I think it's better to talk about coupling. Which software components are forced to change together?
For example, when using a shared database, that database schema becomes an interface. All components that use this database must evolve their use of the database together. Since your Central Collector component writes to the database, and queries are made to the same database, whoever makes these queries is already coupled with the Central Collector. It's then worth a thought whether these queries should also be made by the Central Collector, or whether coupled code should be spread out across all clients.
In contrast, I mentioned a hunch above that a web API might be a fairly stable interface, allowing the Central Collector and the data sources to evolve independently from the user interface in the clients. Such independence may or may not simplify development.
The faff of the to-ing and fro-ing whilst we develop - since we need lots of different API methods (or a more generic one with the consequent "language" to drive it which would follow) which will evolve as the GUI evolves - given we need to create "client access code" for each API call, WebAPI Controllers to serve the calls etc etc.
Yes, there is some development overhead here – but perhaps it's not that much.
MY wish is to use the lighter approach whilst we allow the "shape" of the data queries to evolve. Then when that is pretty much settled, switch to 1 by simply taking the "QueryManager" code that gets created in the client and shift that into a stand-alone "Query API Application"
If you want to migrate to approach #1 anyway, then using that architecture right from the start might be simpler.
While you're probably right that approach #2 simplifies development, the limitations of SQLite probably mean that approach #1 is the only sensible solution. If you're going from the server component, you should probably develop that architecture from the start, instead of fixing things later. Introducing a web API might not be that much extra development overhead, especially since it is likely to serve as a fairly stable interface that allows the UI and queries to evolve somewhat independently.