I found the the ON DELETE CASCADE is already there. I also found that the record in the DNN Modules table is not being deleted. The module instance is being removed from the page but the record is still there.

How do I track this down?

Since you're on the team you will have the joy of feeling the "collective pain" of seeing thousands of users (including the Core members) with failed scripts because a popular module placed foreign keys on Core tables :) But, what can we do except tell people that only the Core API is safe (mostly we keep breaking changes to a minimum). The Database has to be off limits.

but... I understand why people still go to the database so all we can do is warn and suggest alternatives.

How about this:

• A module developer that adds the foreign keys, must register them in a central repository related to the module he/she developed.
• On upgrades DNN checks for this keys and drops them before running the upgrade process, after it's finished it readds them and registers them again.