I assuming that you are not using OOTB retrieval and save methods to retrieve and save data from active to archive tables. If that is indeed the case, Pega platform cannot track the changes happening to the tables and thus would not know that the index needs to be updated.
So the way I see it, once your data changes in the database (moving from active to archive table), you will have to trigger indexing of all Work if you don't want to do it for individual classes. This will be time consuming but there is currently no mechanism to automatically figure out that the data in the database has changed outside of Pega platform and thus the Pega platform must re-index the changed content. There has to be an event to notify so that indexing can catch up.
Alternatively, you can write an agent which can monitor the change to your archive table and trigger the indexing job from within Pega platform. The agent an poll the archive table(s) to see if the data has increased since it previously ran.
OOTB System Indexer looks for the queue items for all the work objects that needs to be indexed and adds the items to a list & requests the indexer to update the lucene index. So if we wanted to index the work then we need to an write an agent which replicates all this.
We have tried different methods to test whether the lucene index picks the newly imported cases but nine worked. (Even the OOTB archive & import didn't work for us, even though that is not an option for us)
We wanted to use much of OOTB features to achieve this feature because we are on 6.1 SP2 version of PRPC & we wanted to upgrade our system to 7(sooner than later) where our whole customization will not work because the lucene search has been replaced with Elastic search.
As of now, that is the only option because we cannot track changes to tables which are not going through our Obj-Save / Obj-Delete methods for the index to be updated.
With Elastic Search in 7.1.7 onward, the queue table and agent activity have changed calling different APIs now, so in that sense any custom changes that you do will need to be modified again upon upgrade.
Indexing external content is still an open area for the product along with how to search and index archived content. I will pass on the feedback to Product Management so that they can decide on best way forward for including (or not including) this in the product.
We cannot consider this as COMPLETLY external because, it is still within the Pega database & pointing to a Pega Work Class which has indexing enabled. If atleast there is an option for us to initiate the indexing on the work classes that we want to initiating the index manually or through an agent that will be great.