Is LiteFS+SQLite suitable for something like a SaaS solution? For example - Fresh books, Trello or Craigslist - all three have different needs. So which kind of apps should NOT be built on top of LiteFS+SQlite combination?
There are also differences once you get to a large scale. Many databases support compression whereas SQLite does not so it might get expensive if you have terabytes of data. That's an extreme case though.
Ultimately, SQLite is just a database. It's more similar to Postgres & MySQL than it is different. There are some features that those client/server databases have like LATERAL joins but I feel like SQLite includes the 99% of what I typically use for application development.
For SQLite my guess would be areas of high concurrent write throughput - like a seasonal holiday/rush, a viral influx of users, or the onboarding of a large client.
Its not that SQLite can't handle these situations with careful architectural decisions. Its that out-of-the-box solutions, like the kind people depend on to solve business-issues in short time frames, won't support it as readily as more mainstream options.
If your SaaS is in the hundreds or thousands of customers then you could split each customer into their own database. That also provides nice tenant isolation. If you have more customers than that you may want to look at something like a consistent hash to distribute customers across multiple databases.
I think its less about proving sqlite is awesome for everything then it is about proving it can be awesome and practical for some projects.