PPR API - Create Scheduled Job to Remove Discharged/Expired Registration From the Account Registrations Table

[doug-lovett]

This update is intended to generally improve PPR database performance of queries that populate the PPR account registrations table.

Background:
Some PPR API accounts have a large number of registrations. For example:
ESC: 243,696
Teranet: 1,004,413

The queries that populate the UI registrations table for an account filter, sort, and limit over the entire dataset. For these accounts with a large dataset the query execution takes seconds instead of milliseconds. As a baseline, an account with 10,000 registrations takes 0.1 seconds to execute.

Registrations that have expired more than 30 days are excluded from search results and the registrations table.
Registrations that have been discharged more than 30 days are excluded from search results and the registrations table.

To improve the account registrations table query performance, reduce the number of registrations that belong to an account. Create a scheduled job that runs daily to:

• Modify the registration record account_id value to append "_HIS" when a base registration has been discharged more than 30 days.
• Modify the registration record account_id value to append "_HIS" when a base registration expiry date is more than 30 days in the past.
• Update the registration status from active to expired when the base registration expiry date is more than 30 days in the past.
• Remove records from the account extra registrations table when a base registration has become historical (expired/discharged) and more than 30 days have elapsed.

Also create an update SQL script to mark existing registration account_id values as historical.

SQL script run in DEV. Job deployed, configured, tested in DEV. Scheduled to run once a week in non-PROD: Wednesdays at 10:15 PM.

[doug-lovett]

Unit Test Notes:
Submit the API GET account registrations on an account that has existing registrations.
Run the database update script.
Submit the API GET registrations on the same account and verify the results are identical.
In the UI remove a base registration that was created by the account.
Verify the base registration is excluded from the account registrations.
In the UI add the base registration back to the account.
Verify the base registration is now included in the account registrations.

[doug-lovett]

Test Notes.
Verify in the UI registrations table that removing/restoring a registration created by the account behaves as expected.
Verify in the UI registrations table that adding/removing a registration created by another account behaves as expected.

[chdivyareddy]

Hey @doug-lovett , I'm using BCREG0057 (2753) in DEV and removed the following PPR registrations noted below and tried to add them back, but the "Registration Already Added" modal is displayed where the registrations are not present in the table. Can you please take a look, thanks!

100092Q, 100084Q - Expired ( base registration created around December 2023)
100318Q - Discharged ( base registration created on June 6)
100294Q - Active ( base registration created on June 4)

[doug-lovett]

@chdivyareddy thanks Divya, I'll take a look this morning. I only tested with the API, there is probably a flag not set correctly when the UI is checking an individual registration.

[doug-lovett]

@chdivyareddy please retest, restoring a registration should now work.

[chdivyareddy]

Hey @doug-lovett , restoring a registration is working now, but when I delete a registration, visually I'm seeing it is being deleted, and when I try to add the same registration back, I see "Registration already added" modal is displayed and when I filter by registration number I do see the registration is available in the table and is being completely deleted by filtering the registration number. Please follow the steps to reproduce noted below, thanks!

I'm using BCREG0057 (2753) account in DEV.

Steps to reproduce:

• Select a registration and delete from the table (eg: 100318Q)
• Manually add the registration by entering the registration number in "Retrieve an existing registration to add to your table:" field and search
• Registration already added modal is displayed
• Now, filter by registration number 100318Q
• Registration is displayed in the table
• Delete the registration, now it will be permanently removed
• Then, add the registration back manually by "Retrieve an existing registration to add to your table:" field and search
• Registration Found modal is displayed and can be added back to the table
• But, "Registered By" field displays 'N/A' in the table until the dashboard is refreshed or reloaded.

• When filtered by registration number and deleting it:

• Manually adding back the registration:

• When the registration is successfully restored in the table, "Registered By" is displayed as N/A.

• When the dashboard is reloaded, the the Registered By name is displayed back as expected.

[doug-lovett]

@chdivyareddy please re-test.

[chdivyareddy]

Hey @doug-lovett , quick question regarding other account registrations - When a PPR registration is added from another account, then is it a requirement to not display the "Registered By" filed in the table? I don't recall this (But, when a client account (BCSC) registrations are added to staff account, then the registered by filed is displayed as expected). Please take a look, thanks!
Rest everything is working as expected.

BCREG0057 account in DEV - Added 100409P from staff account

Verified in DEV for adding/removing registrations from the same account.

[doug-lovett]

Hi @chdivyareddy yes, non-staff only see registered by and reports for registrations that they created.

[chdivyareddy]

Thanks for confirming @doug-lovett !