CTHUB - Scenario 1: User uploads a one‑off .csv file with VINs

[danclancy85]

Title:
Upload and process a one‑off CSV file containing VINs for decoding

Description:

• "As a CTHUB user, I want to be able to upload a comma‑delimited CSV file containing a list of VINs so that VINs not yet present in the monthly ICBC dataset can still be decoded through the VIN decoding pipeline"

Wireframe:

Purpose and benefit to user:
Users may want to decode VINs that are not part of the ICBC files for two reasons:

1. VIN is not yet registered with ICBC
2. ICBC has not yet sent CTB the latest monthly .csv file containing the new VINs

Having this separate 'one off' functionality within CTHUB will enable the business area to have flexibility in servicing the CTB.

Acceptance Criteria:
Acceptance criteria is drafted here, but should be compared against the notes provided by the business area. They should also take into account some of the existing workflow in CTHUB that has been created by Tim"

• Given I am a CTHUB user, when I am uploading a file, the system only accepts CSV files that use commas (",") as delimiters and contain a column explicitly named vin
• Once the CSV is uploaded, then all VIN values are converted to uppercase for consistency with the icbc table
• Given the preprocessing has completed, the uploaded_vins_file is compared against the uploaded_vin_record and VINs that already exist in the uploaded record are filtered out.
• Given the filtering has completed, the VINs are sent for decoding via VINpower, and if Vinpower fails, then they are sent to VPIC for decoding.
• Given the decoding completes, then results are stored in the vinpower_decoded_vin_record or vpic_decoded_vin_record tables according to which decoding process was used.

Development Checklist:

• Perform file validation upon upload (inclusion of vin column)
• Preprocess VINs
• Write records to uploaded_vins_file and uploaded_vin_record tables.
• Use decoding pipeline for VINpower with a VPIC fallback
• Save decoded results to appropriate tables

Notes:
Notes from business area - link