As you say, ground water level is difficult to visualize (although possible with some vegetation hints), so I would make it simple - uniform or a basic gradient, so player intuitively knows what to expect.
From there, I see two ways to add 'well optimality' - well depth based on ground height (affects construction time, or perhaps depth is limited by technology), or digging cost based on ground material (if you have seasons or biomes, cold terrain could be harder or even impossible to dig).
Both approaches require visualization, but both should be useful for more than just wells (ground height could be visualized with vegetation, height contours, terrain slope, maybe just mark hills with special trees and let player sample absolute height with mouse).
The height based approach means wells get cheaper near water bodies (lower terrain) - this is important for balance, since otherwise it cant compete. Wells also get more expensive further inland (assuming higher terrain), which allows carrying water from shores to compete (since that gets more expensive with distance). Obviously both keep their home ground advantage, but this dynamic keeps it small enough to not constrain the possibility space (external factors can make wells optimal near a stream, or a distant stream optimal for sourcing water for an inland settlement).
Another effect is that players are discouraged from building wells on hills. Useful if you want to encourage scenarios where water is distant (like making player deal with difficult to put out fires early game). Would also encourage building water storage. A well on a hill could be useful for irrigation if you can use gravity (aqueducts/dug out channels). Fertile ground on high terrain would also discourage farming there until later on (if you want to ensure player has these untouched pockets of fertile land available later on).
For gameplay longevity, I would absolutely make fertility go down over time, so keep the player moving their settlement (expand, move, create new ones - many approaches). This could also encourage eventual transition to inland farming if you decide to make shores optimal early game (also makes sense given that the shores might fill up with all kinds of buildings over time). You could even make some land lose fertility faster than other (so two variables, fertility, and uhh... longevity?) for long term strategical decisions. Of course it could be restored over time or player could use specific farming techniques to keep the land forever fertile, but not really necessary (worst case, player can just trade for food end game - probably bored with basic farming by then anyways).
Groundwater could have a similar effect (either deplete or pollute) on a more global scale (so the gameplay goes a bit like shore->inland->shore with no good farmland). Feels more like a driver of endgame chaos, leading to collapse of the city - maybe at this point a few settlers can leave in search for better land, or you can allow the player to eventually bounce back and rebuild (the remains of the old city can seed the new one, so its a whole new experience, but for it to work you need proper decay systems and resources have to renew or be infinite)
If you use streams instead of lakes, making those dry up or widen over time could add challenge (and again add strategical decision, since wells might be more reliable). Even changing the water level map-wide could work (no need for fluid simulation). Heh, make former riverbed super fertile, just so you can trick the player into building there and then hundred years later flood the thing (if you have proper fluid simulation, this could be one source of fertile land, if player can build dams or drain high altitude lakes?).
Bodies of water that appear/disappear with rain could be a really high-risk high-reward inland water source (if there is some huge benefit to building inland). Could also save the player on a really dry map.
