I don't consider it an Achilles' heel, more like the reason that you want postgres sometimes.
Similarly, I wouldn't say that postgres' Achilles' heel is that you can't just copy the database to a thumb drive.
Agreed that you have to treat SQLite as though you're writing to a single file with all conflict resolution essentially on-disk. Because you are.
You're not likely to run multiple threads in NodeJS. If you do need to scale up to multiple threads or processes, SQLite might not be the right choice, but then again it was never designed for that use case.
The other thing is setting the sync and journaling mode on the connection. You have to do that for every connection, if you do want to use WAL mode.
SQLite can be quite high performance if done right.