I wish we could be absolutely certain of this, then we wouldn't ever require more than 1 confirmation, or even PoW at all
For small amounts, 1 confirmation seems to be commonly accepted.
I realise that it's uncommon, thanks to your link and some more research I can confirm that the number of "extinct" blocks dropped from several per year to only 2 in 2019, and actually none since then.
Unfortunately I can't build my system based on hope alone. How do existing payment systems handle the situation when a transaction in a previously confirmed block actually disappears (perhaps because of a malicious actor trying to double spend)?
Once again it depends on the payment system and what you are doing.
Some examples :
With coinex after 1 conformation I can trade on their platform with the funds, but I can't withdraw anything I traded for or the original coin until 6 confirms.
With Bitrefill if I DO NOT use RBF and a high enough fee it's either 1 confirm or at times 0 if it's for a small enough card since I have been using their system for a while.
For larger amounts it's 1 confirm with no RBF and high enough fee. BUT, we are talking about $10 to $50 gift cards, not sure if that is going to change for a $1000 amazon card for a new user.
shoppy.gg is usually 2 or 3 confirms.
If you are going to be gaming the network to doublespend the time and effort involved are going to be a lot more then a few dollars.
-Dave