[BE] My VA Forms Status: Get benefits-intake-id from correct table

[tpharrison]

Background

The backend query that retrieves form submission statuses on My VA currently uses the benefits_intake_uuid from the form_submissions table to get the status of each form via the Lighthouse Benefits Intake API.

For forms with multiple submission attempts, a record is created for each attempt in the form_submission_attempts table. In these cases, the query should retrieve the benefits_intake_uuid from the latest record in the form_submission_attempts table. If there is only one form submission record, the benefits_intake_uuid should be taken from the form_submissions table.

flowchart TD;
    A[Start] --> B{Multiple form submissions?}
    B -- Yes --> C[Retrieve latest record from form_submission_attempts]
    B -- No --> D[Retrieve record from form_submissions]
    C --> E[Get benefits_intake_uuid from latest attempt]
    D --> E[Get benefits_intake_uuid from record]
    E --> F[Use benefits_intake_uuid to retrieve status from Lighthouse API]
    F --> G[End]

Tasks

• [x] Modify query to handle multiple submission attempts
• [x] Ensure query handles single submission attempt
• [ ] Walk Daniel through what needed to be done here

Acceptance Criteria

• [x] Unit tests cover both scenarios: multiple submission attempts and single submission.
• [x] The benefits_intake_uuid is correctly retrieved from the form_submissions table for forms with only one submission.
• [x] The benefits_intake_uuid is correctly retrieved from the form_submission_attempts table for forms with multiple submissions.

[ACParker89]

@mtcA6 FYA Tom will be working on this in 45

[tpharrison]

Some questions have arisen regarding this ticket. I'll be discussing the form submission table design with Eric Tillberg on 9/23.