Iain Vigurs opened the scoring for County after 10 minutes and despite Thompson's leveller on 39 minutes, Grant Munro restored the visitors' lead on the stroke of half-time.
Three St Mirren goals in the space of 10 minutes turned the game in the favour of the home side as Lewis Guy levelled from the penalty spot, Sam Parkin beat Michael Fraser to net the third and Kenny McLean drilled a low shot from outside the area into the bottom right-hand corner.
County refused to give up, however, and Rocco Quinn netted with 10 minutes remaining and again 90 seconds later to level the game at 4-4.
But as the clock approached the 90-minute mark, Thompson got on the end of Graham Carey's precise cross to beat Fraser in stunning style and earn St Mirren all three points.
The visitors enjoyed the better of the possession during the opening 10 minutes and they opened the scoring with their first opportunity of the match, Martin Scott found Vigurs who rifled an unstoppable effort beyond Craig Samson from 18 yards.
Two minutes after grabbing the opener, County went close again as Fotheringham's dangerous cutback was cleared behind by Marc McAusland with Sam Morrow ready to pounce inside the area.
St Mirren were struggling to demonstrate any sort of an attacking threat and it took them until 10 minutes from the break to create their first chance of the match - Thompson heading over Carey's free-kick.
Thompson was to take the next opportunity that came his way just three minutes later, rising at the front post to nod Goodwin's corner beyond Fraser.
Referee Steven McLean showed yellow cards to Goodwin and Stuart Kettlewell after the pair clashed off the ball.
Fraser did well to parry away Thompson's powerful header two minutes before half-time and the former Motherwell keeper's stop was to prove even more vital as the Highlanders restored their lead in first-half injury-time.
The St Mirren defence failed to defend Vigurs' free-kick and Munro bulleted a header into the net at the back post.
Morrow flashed a header inches over the bar as the visitors started the second half brightly.
St Mirren were handed the perfect opportunity to level the match on 54 minutes when Guy was brought down in the box by Marc Fitzpatrick, the striker took the resulting kick and despite the best efforts of Fraser the keeper could not keep the ball out.
Guy and Jon Robertson were replaced by Kenny McLean and Parkin during the aftermath of the goal and it took little time for the changes to pay off.
David Van Zanten swung over a deep cross and Parkin out-jumped Fraser to head the ball into the empty net.
A wonderful stop from Samson kept out Vigurs' 25-yard free-kick as the visitors reacted to falling behind for the first time in the match.
St Mirren were playing with a new-found confidence and they increased their lead when McLean drilled an excellent low effort past Fraser on 64 minutes.
Derek Adams introduced Colin McMenamin and Gary Glen for Kettlewell and Morrow in an effort to bring his side back into the match, but it was the hosts who continued to look the more likely.
Thompson's diving header from McLean's cross crept just wide of the target and McAusland headed over from Jim Goodwin's corner.
Quinn restored some hope for the visitors when he blasted home from the edge of the box after seeing his initial effort blocked by McAusland.
In what was an astonishing turnaround Quinn was on the scoresheet again 90 seconds later when he drilled an angled shot low beyond Samson.
But St Mirren threw men forward in search of a winner and in the final minute, Thompson met Carey's cross with an overhead kick to leave Fraser with no chance and send the home support wild.