Making a cistern that's kept full from a heavy aquifer is easy, provided you have gotten beneath the aquifer already (either though aquifer piercing techniques or by having your staircase where there isn't an aquifer to deal with).
- Dig out your cisterns, at least 2 Z levels deep where you're going to place the well.
- Prepare the cistern inlet by digging a depressurizing horizontal tunnel at the cistern top (water) level under the floor/ around the cistern. This is done by making a diagonal only sideways shift of the tunnel.
- Build a raising drawbridge (and test it) in the tunnel. Optional, for the off chance that you'd want to empty the cistern some time in the future, e.g. to recover a body.
- Empty the cistern of any rocks you want to recover, as you can't do that when it's full of water. I usually mark them all for dumping and make a dump zone close to the cistern for regular hauling later.
- Paint all levels of your cisterns as high cost passage, or you may get dorfs drowning as they take a shortcut through the cistern as it's filling up (and dorfs are rather creative when it comes to finding reasons to go where you thought there would be no reason to venture).
- Order the digging of a vertical staircase starting at the end of the tunnel you dug, and have it end above the aquifer, but not connected to anything (to ensure it's dug from below).
- Once a miner approaches the top of the staircase, order the digging of a horizontal continuation to connect to the rest of the fortress. Digging though an aquifer from below is safe because the water drains downwards, but you need to ensure the dorf doesn't try to leave by going down, as that has a big risk or resulting in a drowned miner.
It can be noted that this design should work with a light aquifer as well, provided the cistern isn't so large it acts as an evaporation chamber.
I'd check whether grates are compatible with wells. I think they're not (try lowering a bucket though one...), but I'm not completely sure. I'd never leave an access to the fortress from the caverns open and vulnerable like that, although a fortress that didn't have an aquifer might get a well to a cavern lake once the cavern has been secured (i.e. walls built so critters can't get into the secured area, which, in my case, is the whole cavern except a small pocket on each edge).