As someone really tempted to use SQLite in production, the one thing I keep bumping against is how to have a nice GUI to interact with the running database. With our current prod databases, I can connect dbeaver and the like to them and nicely browse the data, query, or even do the occasional fix. Seems like this would be much more of a head scratcher if the database is just a file on the same VPS the app runs on.

If your app is a web app you could create a rudimental page with SQL input and table output and authorize only admins. It's risky, if someone gets access to admin account they can drop everything. I have something like this for logs, app reads log files directly and they are accessible only via private domain (tailscale), if I connect to public domain I can't access logs. Additionally you could enable only SELECT statements via web.

Another downside is that it will take time to make it look nice.

sqlite-web, put it behind nginx with basic auth or configure a password.

DBeaver can also open/manage SQLite databases. I use it daily (although on a tiny page).