SQLite has been around for a very long time, it is easily the worlds most deployed SQL database. Litestream is just a way to do live sync/replication to make HA easier.

There are certainly use cases where SQLite is not a good fit. There are likewise use-cases where PostgreSQL is not a good fit either.

One is not better than the other, it just depends on your particular needs for that particular project.

My point is, SQLite is a totally sane and reasonable storage/DB solution for many server side applications as well.