It's a general approach for SQL-backed CRUD applications. If your application is (1) read-heavy (most are), especially if it's particularly read-heavy, and (2) benefits from snappy responses --- ie, if it's the kind of thing where you'd invest significant time in, like, serverside Javascript compilation --- and, especially, if (3) it wants to run in more than one geographical region at the same time, then LiteFS is an interesting way to slash database server round trip times out of your response budget.
The best way to think about SQLite in modern CRUD apps is by thinking about the N+1 query problem. N+1 is an issue largely because of the round-trips between the app and the database server (in an n-tier architecture, those are virtually always separate servers; in a geographically distributed n-tier architecture, they're also far apart from each other). Think of SQLite as a tool that would allow you to just randomly write N+1 query logic without worrying about it. You'd probably still not do that, but that's the kind of thing SQLite ostensibly lets you get away with.
Everything I listed at least claims to be a distributed database (also in the title of this article: "distributed sqlite").
However, Postgres is not and never claimed to be a distributed database, so you are erecting and attacking a strawman argument by choosing something that wasn't even in the list.
The best way to think about SQLite in modern CRUD apps is by thinking about the N+1 query problem. N+1 is an issue largely because of the round-trips between the app and the database server (in an n-tier architecture, those are virtually always separate servers; in a geographically distributed n-tier architecture, they're also far apart from each other). Think of SQLite as a tool that would allow you to just randomly write N+1 query logic without worrying about it. You'd probably still not do that, but that's the kind of thing SQLite ostensibly lets you get away with.