Mempool descendants not removed with parents

[rnbrady]

When a mempool transaction is invalidated and removed from node_transaction because of a conflicting transaction, any descendant transactions that depend on it are left orphaned in the table.

Current behavior

Given a chain of unconfirmed transactions A → B → C in node_transaction:

1. A block is accepted containing transaction A' which conflicts with A
2. trigger_node_block_insert correctly deletes A and archives it to node_transaction_history
3. B and C remain in node_transaction despite being invalid (their parent outputs no longer exist in any valid context).

Impact

• node_transaction accumulates invalid rows over time, growing unboundedly
• queries for balances or UTXOs return invalid unconfirmed results
• affects chained unconfirmed transactions to arbitrary depth

For some example orphaned transactions, run the following GraphQL query on gql.chaingraph.pat.mn:

query MempoolOrphans {
  node_transaction(
    where: {
      transaction: {
        inputs: {
          outpoint: {
            transaction: {
              node_validation_timeline: { replaced_at: { _is_null: false } }
            }
          }
        }
      }
    }
  ) {
    transaction {
      hash
    }
    validated_at
  }
}

As of 2026-04-02 this returns 991 orphaned transactions.

Proposed solution

Add an AFTER INSERT trigger on node_transaction_history that cascades invalidation to descendants:

1. Check if NEW.replaced_at IS NOT NULL (if null, this is a block confirmation and descendants remain valid, so do nothing)
2. Look up the outputs created by the invalidated transaction
3. Find node_transaction rows (for the same node) whose transaction spends any of those outputs (via input.outpoint_transaction_hash / outpoint_index)
4. Delete those rows from node_transaction and insert them into node_transaction_history
5. The trigger fires recursively down the chain