User registrations, content posts, in-app purchase receipts etc. Up to hundreds of thousands of concurrent users it works just fine.
It saves me a tone of time and complexity from setup I don’t have to do for a dedicated database engine.
There is a good overview with general use cases here: https://sqlite.org/whentouse.html
Yes this is a heavy query that would not make it to a production system, still I am surprised that load placed on a sqlite db by "hundreds of thousands of concurrent users" would not surface problems due to this simple detail.
https://sqlite.org/pragma.html#pragma_journal_mode
This lets sqlite read w/o a write lock (among other things)
Since it stores the log to an adjacent file you have to make sure the process can write to the whole directory containing the db.
Specially on how to deal with concurrent writes on SQLite.
With this approach, services are usually separated (e.g. microservices or a monolith with several db clients) and use several sqlite db files. For example, users.db, receipts.db, posts.db etc.
For data which is frequently accessed, it really helps to consider caching (e.g. HTTP).
I realise of course this won't scale infinitely so I usually make use of ORMs (e.g. GORM for Go/Gin or Diesel for Rust/Actix) just in case the SQL engine would need to change (so I don't have to rewrite queries etc). I haven't had the need to do so yet.