It would be stored as a blob in the extra_data field of the xf_user_alert table. It will look something like this:
Code:
a:3:{s:5:"title";s:32:"Hello! My useful thread is here!";s:4:"link";s:51:"index.php?threads/hello-my-useful-thread-is-here.3/";s:6:"reason";s:38:"I have moved your thread because I can";}
This contains the title of the thread, the link to the thread and the reason given via the author alert.