SQLite is ubiquitous as an embedded database in client-side apps, especially iOS and Android. This ubiquity and simplicity make it a viable alternative to PostgreSQL during development. This is why all popular ORM/QueryBuilder frameworks support SQLite and fits with your appreciation of it.

SQLite as an embedded server-side database requires extra work and configuration to make it a viable alternative to PostgreSQL. It lacks good write concurrency and recoverability by default. It is, however, continually improving but gaps remain.

Since it does not have a wire protocol, SQLite is rarely connected to a data warehouse via ETL so it does not fit well as an alternative to TiDB.

> SQLite as an embedded server-side database requires extra work...

No, it depends on the server application. An all-read or read-mostly server requires nothing special. Same for any server that expects a low number of users or database per user(s).

Mozilla uses it on servers for documentation sites. You can also run your own Firefox sync server using SQLite.

SQLite is surprisingly good. You can even run a small site or intranet from it.

It has the advantage of requiring zero administration. All you need is a file: backups or copies are just a matter of copying the file.

Yep, I think too many people fall in to the trap of thinking they are Google or Netflix when the reality is their site could probably be run on a potato. I wouldn’t use sqlite for a commercial web app but even a good chunk of those would be just fine on it.

If you have already made your choice, you do not need a flow chart. If you are uncertain, use postgres.