Ultimately Cloudflare is just another provider you have to trust if you decide to rely on them, just like your web host and anything else you use. For any non-trivial site you should do a risk assessment of the service providers, external javascript and other software you are using to determine whether the benefits outweigh the risks, and for the most sensitive use cases they should be kept to an absolute minimum. As you say, for a static site HTTPS terminated by Cloudflare is more secure than plain HTTP so it makes the most sense in that situation.