Comp and Pen - Add Fields to VA Profile Cache

[EvanParish]

User Story - Business Need

We want to update our VA Profile cache table to include a participant_id and has_duplicate_mappings fields so we can make the Comp and Pen pipeline significantly more efficient.

• Ticket is understood, and QA has been contacted (if the ticket has a QA label).

User Story(ies)

As a backend engineer
I want add the necessary fields to our VA Profile cache table
So that we have and efficient way of storing and retrieving this data

Additional Info and Resources

Acceptance Criteria

• The VA Profile Cache table has been updated to include a participant_id and a has_duplicate_mappings fields

[cris-oddball]

Do we want to consider adding in API endpoints for this table so that the lambdas can just use the endpoints instead of making direct db calls and so that we can troubleshoot rows in that cache without having to log into the DB and run queries?

[mjones-oddball]

Hey team! Please add your planning poker estimate with Zenhub @cris-oddball @EvanParish @k-macmillan @ldraney @MackHalliday @mchlwellman

[mjones-oddball]

I noted that while Perf data isn't real, Prod is so I wouldn't be as comfy with people accessing that data from their local machine via an API endpoint.

[mchlwellman]

Fields have been added, migration generated, testing in progress.

[cris-oddball]

PR approved/merged and sent through the pipeline up to Perf/Staging. Need to just jump on the DB and confirm the columns are there.

[cris-oddball]

QA PASSED

• No migration errors
• Verified that Perf table has_duplicate_mappings exists with default 'f'
• Verified that Perf table participant_id exists with default null

[cris-oddball]

@k-macmillan @mjones-oddball

I tested with @mchlwellman the opt-in/opt-out lambda using Dave's test script and a Lambda test body to update an item in the cache.

[DEBUG]	2024-05-30T17:20:24.579Z		Getting the database URI from SSM Parameter Store . . .
[DEBUG]	2024-05-30T17:20:24.934Z		. . . Retrieved the database URI from SSM Parameter Store.
[DEBUG]	2024-05-30T17:20:24.934Z		Making a request to ACM . . .
[DEBUG]	2024-05-30T17:20:25.202Z		. . . Finished the request to ACM.
[DEBUG]	2024-05-30T17:20:25.203Z		Getting the ALB private key from SSM Parameter Store . . .
[DEBUG]	2024-05-30T17:20:25.451Z		. . . Retrieved the ALB private key from SSM Parameter Store.
START RequestId: f8ad2faa-c4b5-481b-abf0-ba6ac40c20fe Version: $LATEST
[INFO]	2024-05-30T17:20:25.457Z	f8ad2faa-c4b5-481b-abf0-ba6ac40c20fe	POST request received.
[DEBUG]	2024-05-30T17:20:25.457Z	f8ad2faa-c4b5-481b-abf0-ba6ac40c20fe	POST event: {<REDACTED}
[DEBUG]	2024-05-30T17:20:25.457Z	f8ad2faa-c4b5-481b-abf0-ba6ac40c20fe	This request is an integration test.
[INFO]	2024-05-30T17:20:25.458Z	f8ad2faa-c4b5-481b-abf0-ba6ac40c20fe	The request is an opt-in.
[DEBUG]	2024-05-30T17:20:25.458Z	f8ad2faa-c4b5-481b-abf0-ba6ac40c20fe	Connecting to the database . . .
[DEBUG]	2024-05-30T17:20:25.899Z	f8ad2faa-c4b5-481b-abf0-ba6ac40c20fe	. . . Connected to the database.
[DEBUG]	2024-05-30T17:20:25.906Z	f8ad2faa-c4b5-481b-abf0-ba6ac40c20fe	Executed the stored function.
[DEBUG]	2024-05-30T17:20:25.907Z	f8ad2faa-c4b5-481b-abf0-ba6ac40c20fe	PUT request body: {'dateTime': '2023-04-23T19:14:35.435Z', 'status': 'COMPLETED_SUCCESS'}
[INFO]	2024-05-30T17:20:26.407Z	f8ad2faa-c4b5-481b-abf0-ba6ac40c20fe	VA Profile responded to the PUT request with HTTP status 200.
[INFO]	2024-05-30T17:20:26.407Z	f8ad2faa-c4b5-481b-abf0-ba6ac40c20fe	POST response: {'statusCode': 200, 'headers': {'Content-Type': 'application/json'}, 'body': '{"put_body": {"dateTime": "2023-04-23T19:14:35.435Z", "status": "COMPLETED_SUCCESS"}}'}
END RequestId: f8ad2faa-c4b5-481b-abf0-ba6ac40c20fe
REPORT RequestId: f8ad2faa-c4b5-481b-abf0-ba6ac40c20fe	Duration: 952.55 ms	Billed Duration: 953 ms	Memory Size: 128 MB	Max Memory Used: 87 MB	Init Duration: 1364.09 ms

Then logged into the DB and verified that the row had has_duplicate_mappings as False