This is how client apps use sqlite, yes. Single instance client apps. Litestream is one method of making sqlite work for server side apps. The hard part on the server is solving for multiple processes/vms/containers writing to one sqlite db.
I feel like if you have multiple apps writing to the database then you shouldn't be using SQLite. That's where Postgres etc completely earn their place in the stack. Where litestream is really valuable is when you have a single writer, but you want point-in-time backups like you can get with postgres.