Why not?

SQLite is more than enough for most apps, there is no infra to manage and no network calls overhead.

Well... in this case there is?

Lets you dump/query data into it without spinning up a complete database server + web frontend? I could have used something like this in my sysadmin days.

For many cases SQLite has better performance characteristics than a full blown db server.

If you can lock to a single-writer multi-reader model, SQLite is capable of handling very impressive loads.

This works especially well with horizontal scale apps where, for example, you can shard by client and want to have an isolated environment per-client.

Then you can do pg FDWs with a partitioned table and aggregate all of the SQLite dbs back together in a single view for super simple and fast cross-shard fan queries.

I think the hardest part for these sorts of projects is figuring out authorization.

For the two best-in-class (IMHO) examples of how to deal with this check directus and supabase. They have very different approaches to the problem, both very good.

thanks a lot ! do you have real world examples and whitepapers of systems designed around that architecture ?

It's new hot topic, everyone feels being architect and showing others brave new world:))

You can read more by googling for fly.io, sqlite https://fly.io/blog/all-in-on-sqlite-litestream/ and listen this episode https://podcasts.google.com/feed/aHR0cHM6Ly9jaGFuZ2Vsb2cuY29...