So the final decision was to go with foam in the roof and 3rd floor but batt insulation everywhere else. This was to eliminate the need for mechanical ventilation. Hopefully this compromise will find the sweet spot between being tight enough to hold the comfort of the house but not so tight that we need a dehumidifier or mechanical air exchange. Also, with the mechanical air exchange, the cost of foam was getting prohibitive.

As for the water, it gets pumped back into the well and is used like any other well water (eg., bathing, drinking, washing). The water isn't contaminated by the HVAC system. Still, the state performs a yearly test of the well to confirm that the water is safe.

Does all of this sound reasonable? Thanks for your input.