How effective is a heat pump for AC and a heat pump/furnace for heating in New England?
You don't have to worry about a heat pump being inadequate for cooling tasks, they are available in a large range of sizes. For really large buildings, they can be ganged together. 800 sf is a walk in the park for residential sized units.

I don't know the specifics of what determines when the backup furnace kicks in. IIRC, it is the combination of a lower thermostat set point and the outside temperature. It kicks on when the pump is not keeping up or it's too cold for it to run efficiently. I can assure you you will not be sitting for hours waiting for backup heat. No customer would sit still for such a system.

While it is true that heat pump heat is free, you are just paying to move it around, if you've had heat wave cooling bills before, you know moving heat around is far from cheap. Only a careful analysis of the various options, considering both operational and installation costs, as well as life cycle replacement, can you make a proper decision about which system is most cost effective. The correct solution will vary by small changes in energy costs and climate.

In cold climates, a ground heat source system should at least be considered because it will reduce or eliminate the need for backup heat. If you are concerned about your carbon footprint, do not forget most electricity generated in the US is by burning fossil fuels. Your footprint from using a heat pump will vary greatly depending on where your local power is coming from. Heat pumps make a lot of sense for many people, especially if they are investing in cooling equipment anyway. But everyone's situation is unique, so the best solution can be arrived at only after careful rational analysis of all the factors.