In my game, I have a feature similar to building water pools. Can I use Tiled’s terrain or Automapping to achieve this kind of functionality, or do I need to re-implement something similar within the game engine itself?
Unless your game is based on Qt the terrain or Automapping code that is part of Tiled is unlikely to be of use in your game.
Fortunately, the problem of automatically choosing the right tiles is usually a lot easier in the game because you can restrict the data types and functionality to cover just what you need. For example you usually do not need to take into account existing tiles, but you may be able to go directly from a grid storing what area is filled with water to the right tile index for each tile in your water pool tileset, using bitwise logic based on either the corners or the edges, or both.
You can find plenty of examples of this online. I found this one quite illustrative, though for terrain I think usually the corners work better than the edges: