I've not used it for websites but have used it for state management in a flutter app, which seemed waaaaaay simpler than learning and then using one of the many state management systems they have (even if I managed to figure out which one to use, paradox of choice always trips me up).
And for data analysis stuff it's absolutely brilliant!
So yes absolutely it's underrated!
SQLite is used on device very commonly. It’s a great use fit for that sort of use case.
The practical problem is that, out of the box, SQLlite runs inside a single program.
If you use, say, PostgreSQL, any number of web server processes and batch processes can run at any time and connect to the database and the integrity of your data is ensured. If you want to do some administration or analysis just connect with psql or DBeaver.
SQLLite on the other hand is a library that runs inside a single program. If you want to add a table or do some analysis you have to shut your web server down!.
It is not the hits per day that matter so much as the analysis, administration and management requirements. If it is an application just a few people use you can shut it down to admin, but if it has 15-25k users you will certainly inconvenience some.
This is just plain wrong. SQLite supports concurrent read connections and concurrent write operations if you activate WAL mode (https://sqlite.org/wal.html). Admin and analysis tasks are probably even easier to do: Just ssh into the server and run the sqlite3 CLI command analyse / edit your databases (https://sqlite.org/cli.html).