TFRS - Discrepancy in 'latest_report' & 'traversal' Fields in Compliance Reports

[AlexZorkin]

Describe the Bug An inconsistency was identified post-migration in the compliance_report tracking system. The 'latest_report' doesn't show the newest report ID, and the 'traversal' wasn't correctly incremented, incorrectly marking the second-to-last report as 'latest'. One example is with the organization Parkland Refining (B.C.) Ltd.

The following query displays an example of the issue:

SELECT cr.id, cr.organization_id, cr.compliance_period_id, cr.latest_report_id AS latest_report_id FROM compliance_report cr INNER JOIN compliance_report_workflow_state crws ON cr.status_id = crws.id WHERE NOT EXISTS ( SELECT 1 FROM (VALUES ('Deleted'), ('Draft')) AS status(status_id) WHERE status.status_id = crws.fuel_supplier_status_id ) AND crws.analyst_status_id != 'Deleted' AND crws.manager_status_id != 'Deleted' AND crws.director_status_id != 'Deleted' and cr.organization_id = 33 order by root_report_id

Private Zenhub Image

Expected Behaviour When a new compliance report is created, 'latest_report' should update across the chain to the newest ID, and 'traversal' should increment accordingly, recognizing the current report as the 'latest'.

Actual Behaviour The 'latest_report' doesn’t reflect the newest report in the chain, and 'traversal' isn’t updated. This leads to misidentification of the latest report and potential data inaccuracies.

Implications This disrupts report system integrity, leading to possible misinformation, audit difficulties, and challenges in data retrieval, affecting monitoring and compliance processes.

Additional Context/Comments Found during post-migration checks. Immediate action needed to correct tracking logic for accurate 'latest_report' and 'traversal' updates upon report creation/editing.

[justin-lepitzki]

Nothing for the PO to test