That's true, but previously the PaaSs I've looked at didn't seem to have the same concept of persistent volumes, or maybe they were costly, I forget. In any case, I tried on Heroku before, and one other provider, and they didn't really support this use case, or pushed you to use their hosted PostgreSQL offerings, which were expensive (for my budget). Fly.io looks a lot simpler and cheaper!
Your backup script is basically what I do, but I use a little Python script that also uploads it to S3, keeps the last 10 days worth of backups, and so on.
Company offering compute and expensive hosted DBs, thus making SQLite difficult to use? Color me surprised :-)
For use cases similar to yours, I'd probably bite the bullet with a raw VM from somewhere and manually configure systemd and Caddy on it and manually upgrade Caddy on it from time to time. We would probably get a re-usable Caddyfile and systemd unit from this initial setup, making it even easier to change VMs later if needed.
It's so simple, it doesn't need a PaaS.
I really appreciate all your replies and sharing your experiences. It's helping me think through deploying my own side projects which, you guessed it, will use Caddy and systemd on a VM somewhere.
Your backup script is basically what I do, but I use a little Python script that also uploads it to S3, keeps the last 10 days worth of backups, and so on.