Not quite sure understanding SQL your cloud (using their AWS account?) but from what I guess by the consideration of D1: I'd wager you want something like Neon but self-hosted. I am building a similar product where I give each customer a full-blown Postgres (without individual extensions) and that is the solution I landed on with the most flexibility and security after testing out SQLite vm per user (via Fly.io), SQLite on the edge (Worker + S3, before D1 existed) and Postgres cluster per user (via Stackgres)
The nice part is the decoupling. User not using their DB for 5 minutes? Time to idle them and use that compute for another customer.
And it's also really nice that storage becomes way way way easier. Storage is the biggest hurdle when your database count scales with your user count so you can instead build out the compute system once, fix a few issues along the way and from then on just babysit a single big storage cluster.
And if that becomes too big at some point, replicate it, shard the tenants by region or whatever and you "only" have to manage storage a dozen times or something. It's obviously always annoying, but at least the setup is similar enough that you can park on FTE per Storage cluster and serve enough customers to make it worth it
The nice part is the decoupling. User not using their DB for 5 minutes? Time to idle them and use that compute for another customer.
And it's also really nice that storage becomes way way way easier. Storage is the biggest hurdle when your database count scales with your user count so you can instead build out the compute system once, fix a few issues along the way and from then on just babysit a single big storage cluster.
And if that becomes too big at some point, replicate it, shard the tenants by region or whatever and you "only" have to manage storage a dozen times or something. It's obviously always annoying, but at least the setup is similar enough that you can park on FTE per Storage cluster and serve enough customers to make it worth it