I don’t use SQLite as my main data store for any project that runs on a server because a dedicated DB server has advantages. But that doesn’t mean that I don’t use it for production use. Here is an example from a few years ago:

I needed to pull in a large dataset from CSV (several dozen gigabytes), do a bunch of transforms on it, extract a small subset of data that I actually was interested in, and load that subset into the main DB. I needed to do this in a way where it wouldn’t bog down the main DB server (which stored less data than what was in the CSVs), and also in a way where I could potentially load diffs of the CSV files as updates. So the solution was that a dedicated job would run on a server where the SQLite database was used to load the CSVs and to manipulate the data with SQL because that was way more convenient than doing it with ad hoc objects in Python. Once done, extract rows from SQLite and load into Postgres. The local DB would stick around to use the diffs for incremental updates, but if it was lost it was easy to recreate it from the last full dataset. Not highly available but highly understandably, cheap, fast, and dead simple.