>What happens if, say, someone else adds that same CNAME to their own network?

Cloudflare network carries identity with traffic. If someone else adds the CNAME, they need an identity in the Zero Trust account that controls the tunnel. If you use the browser, Cloudflare IdP MITM the request and requires login to Cloudflare first. If you use Cloudflare Warp, then identity you use to login to Warp is injected.

>CNAME to a magic hostname causes traffic to get proxied and sent to a “Zero Trust” private network

That's also commonly called a load balancer.

From the Cloudflare UI, it works like:

- URL Normalization

- Redirect Rules

- URL Rewrites

- Page Rules

- Configuration Rules

- Origin Rules

- IP Access Rules

- DDoS protection

- Web Application Firewall

- Bots

- Rate Limiting

- Access

- Bulk Redirects

- Modify Request Header

- Cache Rules

- Snippets

- Cloud Connector

- Workers

- Custom Error Rules

- Modify Response Header

- Compression Rules

The "Access" step is key. Cloudflare acts like an authenticating reverse proxy. Once the request is authenticated, it continues processing and can route to the private backend over the Cloudflare tunnel.

Of course, you can make your app public. This is no different security wise than me adding a CNAME my-special-google.my-tld.com to google.com. Whether is works or not depends on the recipient server setup