Welcome! It's always great to hear new voices with new ideas.
It sounds like you've got a fun project on your hands. Your clay pots are definitely going to need to be waterproofed if you want to use them for holding water. I don't know how to do this, but others here might, and your local hardware store might have some suggestions. My experience with with the terracotta material is that seeping water encourages the growth of mold and mildew on the surface of the pot, and the seepage rate can really be quite high. Eventually, mineralization occurs as evaporating water on the outside leaves behind dissolved substances, and a crusty mess surrounds the pot. Although this is manageable when the pot holds moist soil, I think it would be very difficult to keep up with as a water feature.
Maybe you could come up with a liner of some sort, made from a bag or inner pot or ?
