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.
You can pick literally almost anything, and current models will be able to write it. I don't know how you arrived at that statement.