Request - Data category migration to new dictionary

[joneubank]

Summary of request

We need a mechanism that will update the dictionary of a data category from one version of a dictionary to a new version. During this migration, we need to revalidate all records vs the new dictionary, and when errors are found we need to record in a separate table a list of migration errors that exist. This will allow us to show all records in their stored and unmodified state including which dictionary version they are valid for, but also look at each record showing what issues they have versus the latest dictionary.

Details

Database changes

• NEW TABLE - dictionary_migration

  • id PK
  • category_id reference -> category.id
  • from_dictionary_id reference -> dictionary.id
  • to_dictionary_id reference -> dictionary.id
  • submissionId reference -> submissions.id
  • status ENUM (IN-PROGRESS|COMPLETED|FAILED)
  • retries number
  • created_at date
  • created_by string
  • updated_at date
  • updated_by string

• MODIFY TABLE - audit_submitted_data

  • Add column error JSONB
  • Update enum action to add MIGRATION

Data validation

• Data validation should start on POST /dictionary/register.
• Should create a record in category_migration table with details.
• Should create an empty submission to link a migration with the audit table.
• Validation process occurs async, and it should block commit new submissions during the migration is running.
• Data validation should use ALL Submitted Data by category ID and validate against the registered dictionary (using function validateSchemas)
• If a record results invalid after running the validation, it should:
  • Update the submittedData record field isValid, lastValidSchemaId, updatedAt, updatedBy
  • Create a record in the audit_submitted_data table with action type MIGRATION, the migration error, and FK to the migration submission
  • Should use db transactions for updating/creation records
• When process is completed, update category_migration.completed_at field with current date

View historical submissions

New endpoints (Migration):

• An endpoint to list all migrations occurred by category.
  • GET /migration/category/{categoryId}:
  • Query params: pageSize, page
  • Should query the category_migration table to include in the response an array of general information of migrations.
• An Endpoint to view migration general information by it’s ID
  • GET /migration/{migrationId}
  • include count errors by entity
  • Should query the category_migration table to include in the response general information of migration
• An endpoint to list records updated by migration ID
  • GET /migration/{migrationId}/data
  • Query params: pageSize, page
  • Filter by entityName, records with errors and no errors
  • Should query category_migration table to get the submissionId and query audit_submitted_data to list all records in array of objects { systemId, error, entityName, oldDataIsValid, newDataIsValid }
• And endpoint to retry migration by migration ID
  • POST /migration/{migrationId}/retry
  • Should re-run data validation.
  • Should query the category_migration table to include in the response general information of migration

Update endpoint (Dictionary):

• Endpoint to register new dictionary
  • POST /dictionary/register:
  • Include in the response migrationId nullable property