I talk a little about the weaponization of water sources in the Middle Ages in this earlier answer, from which I'll borrow for the beginning of this post. There are a few times when we hear about actual well poisoning as a war tactic, but scholars are frequently skeptical:
The perilous geographic placement of Svetigrad at the tip-top of a rocky hill [was] good for defense against a siege but very bad for access to plentiful water sources. Drawing out the themes of betrayal, [the author of the primary source record] condemns a local man for knowing exactly how to crush the citizens and soliders of the town psychologically as well as physically. This traitor, the story goes, cast the corpse of a dog into the local well. The town defenders had lost their source of water, and knew they had to give in.
Whether it really took a local traitor to poison a well or whether the Ottoman army was actually just very strategic at cutting off the defenders' access to nearby water sources is an open question...The second is more plausible, but makes the Ottomans look much more effective so would not work well for the author's polemical purposes.
The most infamous and tragic cases of medieval well-poisoning, of course, were not actually cases of well-poisoning at all. The Black Death in particular, but also other periods of violent economic/political upheaval, led to mass accusations of poison/murder against already oppressed groups--lepers in some cases, but more frequently Jews. Which, in turn, led to pogroms, massacres, and massacres-by-arrest-and-execution of the accused. But while these cases have a lot to tell us about medieval people, they don't tell us what people thought about wells and how they interacted with them in the aftermath of accusations.
But! As the posts in the other thread curve around to, we can get some insight into how medieval people thought about potentially poisoned wells by looking at their attitudes towards other poisoned/polluted/supposedly poisoned water sources. Namely, fountains and sewer conduits (including pipes).
In these situations, we see a deep and frequent concern for their pollution with animals, dead animals, feces, sludge, and disease--sometimes by accident, especially in the frustrations of suave urban sophisticates dealing with rustici (hicks); but sometimes deliberately, as malicious poisoning to target one person or a whole town. What does not happen is wholesale destruction of the fountain or system in the aftermath of a pollution event or poisoning case.
For example, by 1262, Siena already had a surprisingly advanced water distribution system of fountains and pools, for use in drinking, bathing, and daily life as well as firefighting. That year, a woman was accused and convicted of poisoning the all-important Campo fountain. We know about this case because its financial ramifications were meticulously recorded in the records of the Biccherna, the city chancellry. These recorded include point-by-point accounts of the costs of flaying the convicted poisoner alive and then burning her. They do not include ripping out a fountain or the entire system of fountains and rebuilding them.
So if we take Barleti's story of the Ottomans' poisoning the Albanian town's well as a dramatic story (true or not): the "narrative effect" of the poisoning is absolutely to shut down the use of the well in the short term. On the other hand, with a longer-term outlook and (surely not coincidentally) the economics and practical logistics of rebuilding an intricate sewer/fountain system, pollution/poisoning through excrement or dead animal bodies could lead to caution and delay rather than immediate destruction.
In closing, it's worth pointing out that medieval people absolutely understood the problems of "dirty water" from pollution, and that dirty water could be cleansed by boiling. A 14th century letter supposedly from a Spanish doctor to his sons at university in Toulouse rattles off the local water sources that are known to be bad (wells, a river, etc), and then reminds the reader(s) not to drink water from those sources unless it is boiled ("cooked"). The danger in a spontaneous pollution/poison case, considering the commonly cited pollutants/poisons of feces and corpses, was the lack of knowledge.
Further Reading:
So does a well become drinkable again in time or not?
I think it may be helpful to consider what a well is and the process of keeping modern-day wells (which plenty of people still use, even in developed countries) clean. Fundamentally, a well is a hole into which water seeps from the surrounding earth and rock. This water is generally free of microbial contamination, although it may contain hazardous inorganic chemicals dissolved out of the rock. Therefore, presuming that the well was good to start with, removing contaminated water will cause clean water to flow in.
Thus, if a modern-day well is contaminated by dangerous bacteria, it can be cleaned simply by pouring in bleach ("chlorinating the well") to sterilize the pipes and then draining water from it until the bleach is sufficiently diluted. Even without the bleach, as long as the source of contamination was removed, the well would eventually wash itself clean. So if you wanted to poison my well, you could toss a dead animal in there and prevent me from drinking from it for several days, but you'd have a hard time permanently ruining it.
Then, of course, boiling the water would render it safe to drink even if a corpse had been floating in it. And people wouldn't die of thirst rather than drink such water even without boiling. Therefore, I am personally skeptical about well-poisoning as a tool of medieval warfare (in other words, as something people actually did, as opposed to the subject of urban legends and propaganda).
What kinds of poisons might've been used? I imagine that if they were organic compounds they would eventually decompose and no longer cause any harm.
This is a great question, and would take an awful lot of consideration to fully answer. The posters above have already covered a bunch of good material, so I just wanted to add my two cents. In the course of looking for instances of attested water-supply poisoning in antiquity, I came across two examples (so far): The 6th c. BCE Greek siege of Kirrha, in which the attacking Ampictyonic League poisoned the water-supply of the city with large quantities of toxic hellebore before an assault, and the 2nd c. BCE Roman suppression of several of the cities of Pergamon after Aristonicus' revolt, according to Florus' account:
Aquilius finally brought the Asiatic war to a close by the wicked expedient of poisoning the springs in order to procure the surrender of certain cities. This, though it hastened his victory, brought shame upon it, for he had disgraced the Roman arms, which had hitherto been unsullied, by the use of foul drugs in violation of the laws of heaven and the practice of our forefathers.
Unfortunately that's all the detail he provides, so it's impossible to say what the duration and severity of the poisoning was without even knowing the substance, let alone the local response to the contamination. The siege of Kirrha isn't much more informative; there are multiple accounts relating the same event, but all with varying specifics concerning the nature of the water supply and how it was poisoned, so it's hard to draw any conclusions.
Not very helpful, I know, but I thought it was interesting!
I also found this book discussion about Adrienne Mayor's Greek Fire, Poison Arrows, and Scorpion Bombs which seems very interesting and may contain more pertinent info on this topic. I've read her book on Mithridates VI and thought it was excellent.
Follow up question, how well did this tactic work?
(Both serious question and I wanted to make the pun)