I used to add pending transactions (< 6 confirmations) to one table, and then looked up each txid to see if confirmations changed. You need to have it tolerate conflicted transactions, which are the results of malleated txids. Sometimes, even after confirming in the blockchain (usually only on or two confirmations) your client might realize there is a longer proof of work chain which may or may not have your transaction in it. Because of this, your client will track the longer chain, so you need to have a way for your app to realize that a confirmed transaction is no longer valid to avoid duplicates. These are some of the reasons we use 6 confirmations instead of one.
The problem is two-fold:
1. When I broadcast a transaction to you, the p2p network will relay it to everyone. If there's any malleability bots running (that was the issue before) they'd try to change the sig so it's still valid, but would the effect of altering the TxID. TXID's are just a hash of the entire transaction. If any detail at all changes you get a new txid.
The txid you hear about before confirmation might be a malleated copy, and maybe a miner has the original or another copy. So while your client thinks it has txid A when unconfirmed, a block could be published with a completely different TxID.
2. Before it's confirmed in the blockchain, there could be multiple competing copies floating around the network. Your app needs to tolerate this because as mentioned above, even if one transaction makes it into the blockchain with TxID A, if the longest proof of work chain changes the effects could be:
- Your txid isn't confirmed in this new longer set of blocks whatsoever. Maybe.
- Your txid is included in the chain, but it could be in a different block height.
- Your txid is included in the chain, but it could have a completely different TxID!
Back when the bot was running, there were visible effects of all of this. I'd receive two payments instead of one.. If I tried spending them, obviously only one output is spendable not two.
So, you really need to pay attention to what's in the chain. You need to accept that chain reorgs (your client noticing there is a better chain available) happen, and so does malleability. While malleability won't always happen, chain reorgs are frequent. Not too deep, but frequent. Scanning backwards with each new block you hear about until you find a common ancestor would probably be a good idea, so you know if it's a new block, or a reorg.
This just means - your application needs to be able to delete, and replace transactions it learned about, but realizes later weren't confirmed properly. Once confirmed you should store the txid, the vout, and the scriptPubKey. This is all you need to create new transactions in future (even raw transactions purely in code).
I think the safest way to track inputs is this:
Each blocknotify, your app should look at the block hash, and keep asking the node for the 'previousblockhash' until you find a block hash you've already seen before. Normally the previousblockhash will already be known to your app, but if not, then your client has realized you were on the wrong fork and switched.
Once it finds this common ancestor block, you know how many blocks are between the leading block and the ancestor (subtract two heights) - n. If it's > 1, you need to remove all transactions that were confirmed in the last n blocks, and scan from the common ancestor all the way up to the most recent block in the chain.
I haven't said how to deal with transactions:
Decode each tx in a block, and analyze its outputs. If any pay to you, you need to store that txid, the vout, and the scriptPubKey. Usually other data like the blockhash, blockheight are useful to have around to assist with the pruning of no-longer-valid transactions. This will be used to maintain a list of all incoming transactions.
While you're looking at the transaction, you need to see if any of the inputs being spent are already in your table of stored incoming transactions. That way you know to remove it, or mark it as spent and keep it for historical purposes.
The important thing here is to stress that the top of the blockchain can be in turmoil, but know that older blocks are harder to replace. So this is why 6 confirmations is important - it buys you enough time so that you know it should be nearly impossible for blocks that deep to be replaced. Since blocks can be replaced, and malleability can change txids, you can't expect txs confirmed in one side of the fork to be in the other.
Bearing these in mind, you're able to continue writing your application. Have fun