Add a new backend task named ReviewAging

• Task will be inserted into existing table task
• The working name for the task is ReviewAging.
• The taskId will be 5
• The command will be review_aging()

Create a new table task_collection_config

• Columns are tcId (PK/auto integer), taskId (INT), collectionId (INT) and config (JSON)
• Composite Unique key on taskId and collectionId
• Foreign key constraint on both taskId and collectionId, with cascade delete

The config value for ReviewAging will be a JSON array

• Each array item will be an object with this pseudo-schema:

{
  triggerField: enum (ts, statusTs, touchTs)
  triggerBasis: enum (now, timestamp)
  triggerInterval: integer seconds
  triggerAction: enum (delete, update)
  updateField: enum (status, result)
  updateValue: enum (-, +, saved, submitted, approved, notReviewed, informational)
  updateFilter: { assetIds: [ ], labelIds: [ ], benchmarkIds: [ ] }
  updateUserId: integer (userId or zero for system)
  enabled: enum (true, false)
}

Alter table task_output

• Add integer column collectionId, default NULL

Create new utility stored procedure task_output_collection

• duplicates existing task_output sproc
• accept additional IN parameter collectionId
• update INSERT statement to support addition of collectionId

Create stored procedure review_aging which will integrate with the existing task architecture.

The Task will:

• Refer to the session variable @taskId for its taskId
• Retrieve the rule set from task_collection_config for each collection with a config for @taskId
• For each task rule:
  • identify the affected reviews
  • Modify the affected reviews, in batches of no more than 10,000 records (follow batching pattern in sprocs delete_disabled and delete_unmapped)
• Use both task_output and task_output_collection sprocs to provide operational output

Add tests to test/api/mocha/integration/job.test.js

• Verify affected reviews are correctly found based on the config
• Verify affected reviews are correctly modified based on the config

The feature will require updates to the API and the tests.