We are working on a package where the data from salesforce needs to be exported to external database instantaneously on DML. The export can be configured such that the user can select the Standard/Custom objects and the fields to export. Our pain point is Triggers which makes the future callouts needs to be implemented on each object that needs to be synced. So, if a new custom object is created which needs to be synced, trigger needs to be created with a specific lines of code.
Question is, can Streaming API be used here? Based on the configuration, we can create PushTopics dynamically from Apex. But where do we place the code to make the callouts? If I understand Streaming API correctly, subscriber could be either be the VF Page or Java code. Java program needs to be hosted on Heroku or likewise. But can VF pages be used to make callouts as soon as it gets a notification. Also, does the VF page needs to be active all the time, as in does someone need to baby sit to make sure session doesnt expire?