I would like to emphasize that dumping untreated human sewage into bodies of water has ALWAYS been a common disposal method. Of course, when population densities get high enough, different methods need to be considered, especially if those bodies of water are also for human consumption and also if there is no access.
And following up on the cesspits and specialised cleaner people as part of a suggestion to the game:
Given the implementation of poop and assuming it has a 1) decay rate and 2) rate of production of noxious (sometimes harmful and even flammable) gases, and 3) spreads infection by contact (at least the poop... urine not so much), I suggest that wherever such waste (and anything else that can rot and can cause health problems) is dumped, and depending on environmental conditions, the aforementioned rates are affected as well as a rate of seepage into the surrounding blocks.
This goes into modeling cesspit construction and use in the game. I could envision cesspits sort of being constructed and designated much like a well is, except it doesn't need to be placed above water; it just needs to be placed over a hole into an empty area below. As the cesspit accumulates waste from those who use it, it allow stinky (possibly hazardous) gases to vent upward, and it will fill up eventually if waste is deposited faster than it decays (in the case of poop, which is a slow process) and seeps out/evaporates (in the case of pee) if not cleaned out somehow (by pooper scoopers!!!). Cesspits were often made to allow drainage of liquid waste into the soil and/or sewers.
If a cesspit that accumulates waste is too near to the watertable or other body of water, and if the walls and floor composing the cesspit are permeable enough (i.e. soil, stone bricks, etc.), then the waste should be able to contaminate the water in contact with the material outside the cesspit at a range that should be looked into (see below for how I would see it represented in the game). That contaminated water should have a greater chance of leading to disease of anything that consumes it. Also, the stuff inside the cesspit should generate gases, which could even be abstracted as miasma, though I think it should be important to note that SUPPOSEDLY there is evidence (of which I cannot find the primary sources of) of the fiery, and at times explosive results of methane build-up... though I would imagine this is exceedingly rare. That, and asphyxiation of people (like pooper scoopers) who find themselves inside the cesspit. Some sewer systems IRL did have ventilation of cesspits and other parts of the system to diffuse out the stink. This could be a design consideration at least in city sewers in the game maybe. ALSO, though it has been discussed elsewhere, piping ought to be a possible component of waste disposal (and other) systems. It should of course allow gases, waste and other materials (and even vermin!!!) through.
The way I would see these decomposition-related and water contamination features represented in the game:
Of course poop and urine would just disappear after a while. I would see urine as being treated as a liquid as mentioned before. This urine could evaporate and/or be absorbed out of existence (with absorption being dependent on what surface it is on, as I will detail below). Poop would be a solid item, which would decompose and dry out, perhaps abstracted out as the poop going from "wet" to "dry". "Dry" poop (or sludge or filth...) would be what could be used as fertilizer. More on that in a second.
Liquid (urine) and/or "wet" solid waste in enough quantity in one tile could cause whatever permeable material it is on to assume some sort of "contaminated" status, similar to how materials can assume a "damp" status when near enough water. The permeability of the material can be defined in the raws, and permeability should have an effect on other things besides contamination with waste (but that is for another post). Any water (or other suitable fluid) in contact with "contaminated" material should then itself be "contaminated". It might be a nightmare to track mixture of contaminated water with "contaminated" water or urine, but perhaps contamination could be represented by an object in the water that can float around... I don't know. This handling of liquids would be the tough part.
As for the decomposition rate of poop and what poop turns into; poop can dry out as ("contaminated") water seeps away from it and/or evaporates away if it is resting on a permeable surface, especially soil and/or is exposed to air long enough. In its dried/drier state, the poop would be less stinky (less gases produced), less infectious, and less able to contaminate whatever it is placed on (in terms of the contamination mechanic described above). Also, in this state, it can readily be used as fertilizer. So, if the cesspit's solid waste contents are allowed to mellow for long enough and have liquid seep away, it will become useful. If the "wet" poop waste is too fresh, it will have to be processed somehow first (composting, or drying out, or something... someone else more knowledgeable than I should chime in!) before being useful for fertilizer or what-have-you. Otherwise, "wet" poop can be used for crap-a-pults or whatever fiendish things people want if they so choose. In a cesspit, if the liquid and solid waste accumulates faster than the moisture drains out, the poop remains in a wet state, which is more stinky, more infectious, and more able to contaminate whatever it contacts. The urine and "wet" poop waste can also separate out, too, as it does in real life, into a liquid layer on top with poop settling out at the bottom. So, "wet" poop is basically what is called IRL "fecal sludge". This fecal sludge (which is the "wet" poop) is what would have to be processed or dried or whatever to make fertilizer. I think the liquid portion could be made into useful stuff, too, but I am not sure.
How construction of a cesspit would factor into this is the permeability of the walls and floor. With a permeable floor and/or walls, the above partial drying of the waste can happen (though there is risk of water contamination). If the cesspit is completely sealed, then the liquid portion (mainly pee) would not be able to seep out, so the cesspit would fill up much faster, though eventually the same thing could happen. The advantage of this design, though, is much less risk if any of water source contamination from seepage.
That's my idea, anyway! And I said poop too much.