Terraform can be used to manage TXT records too. Where does it store metadata for them? Or is it TXT all the way down?

You can have multiple TXT records for a given domain name, so it would be possible to store an arbitrary amount of metadata for whatever systems you desire, and just loop through the TXT records to figure out which ones are for the current system's purpose.

Exposed to the world?

I guess that's not ideal, though I'm not clear what attack surface area is increased by storing creation/ deletion metadata in public.

I guess it lets an attacker know that you're using Terraform, which might help them target their attacks.

Terraform stores secrets in state. Generated database password, etc.

Oh, gotcha. I've not used Terraform yet.

Yes, if that's the case, then TXT records could easily be unsuitable. Depends exactly what metadata needs to be attached to your DNS records.