Have you looked at the Generico plugin? Its actually a filter, but it adds custom html / css / js to wherever it acts upon. So you can use in a regular page activity, to embed youtube, or add lightboxes etc. It can do basic database lookups. If you look at the Generico showcase thread, you can see some of what other people have made.
Here are some links:
Generico Showcase Forum
many thanks for the pointer, this looks promising (Not sure why it did not come up in search). Will try to port a representative app via the filter and hopefully report back here with a positive story!
Generico worked out of the box for the first app I tried to port (Moodle 3.0 version). It is a periodic table type interactivity where the template involves a table and the custom js / css are grouped in single files. It displays as a popup to accommodate the required width.
A small glitch comes from the fact that styling elements inherit properties from the YUI library. Hence the css must be adapted to undo or fine-tune some of these choices (margins etc.)
Overall though it literally opens up an new window on how to customize Moodle