1. Does CB activity supports custom activity types via custom plugins?
Yes, CB GroupJive and CB Gallery activity are entirely handled by those plugins and logged by CB Auto Actions.
2. Is it possible to modify the activities load logic via a plugin (i.e. any event to modify the SQL)?
To an extent, yes. There's events for the database queries to make some adjustments like adding to the WHERE statement to filter out activity. CB GroupJive does this to hide activity for groups a user doesn't have access to.