How do I catch the possible unconfirmed transactions, so the ones that
got saved in the db when were first posted, but in the process were
rejected/unconfirmed in the next 6 blocks.
There are two ways a transaction (TXID) is first introduced by
- 1) Acceptance in mempool
- 2) Confirmation in a block
A transaction in mempool has no guarantee of being mined, and a confirmed transaction may have never been previously seen in your local mempool. Therefore, you need to be able to differentiate between the two scenarios, and only store the transactions which actually receive confirmation.
Reorgs: Furthermore, there is no guarantee that a previously confirmed transaction receives confirmation from subsequent blocks. It is possible that subsequent blocks form a separate branch, so that your previously confirmed transaction is no longer in the strong chain. This is where incrementing confirmation with each new block as you describe will fail.
Better to verify the longest header chain (they must link together via prev header hash) and ensure that the transaction is included in this longest chain when computing the level of transaction confirmation. If this longest header chain reorganises, you can update the confirmations of all transactions accordingly.