Due to our business requirements we are implementing a multiple process engine configuration (as opposed to multi-tenancy) for the build out of our current application suite. Our application will support 8+ applications that will start process instances, but one application that will query for user tasks and complete the user tasks.
Our reason for multi instance is we have to be able to enhance, upgrade, deploy each application individually without effecting the others, as well as data integrity across applications. So multi tenancy in that aspect does not work for us since any downtime for a specific application would effect the others.
So our design has a process instance for each individual application we support which each have their own DB schema. We then have our one user application that is to query for user tasks for specific users and then allow them to complete the tasks from the GUI application. This approach works fine for us for the simple use case since most users will only have access to the specific application(department) they are entitled to.
There is a small subset of users that will be allowed to approve tasks across multiple applications (departments) so this is where the multi instance becomes a bit tricky. Our approach is to use a CustomSqlExecution on one process Engine that will basically query each schema for tasks the user is entitled to. The DB user would have been granted read-only access to all of the schemas.
After the long ramble I have 2 main questions. We were planning on performing a UNION between the queries for each schema, would Tasks that have the same execution_ID be filtered out as duplicates or would they survive the UNION? Also is there a way to use a DB sequence that all of the the schemas would use to generate execution ID's that would guarantee uniqueness across all of the schemas?
Any answers/comments/suggestions would be appreciated.
