As a CIF User, I want the list of CIF operators to be automatically imported from SWRS, so that I don't have to enter all the operators

[matthieu-foucault]

Description:

An import of the operators needs to be triggered every time the SWRS data is being transformed, and when the application is deployed.

To avoid the issues encountered with CIIP when the data is copied across databases with pg_dump/pg_restore, the connection to the SWRS database should instead be done with a foreign data wrapper. The FDW can be created and removed during the job that imports the list of operators.

Acceptance Criteria:

Given that I am installing or upgrading the cas-cif helm chart When the database is setup (after the sqitch plan is deployed) Then a cif_private.import_swrs_operators(host, dbname, port, user password) postgres function is run to import the operators from SWRS

Given that the cif_private.import_swrs_operators function exists When it is invoked Then it:

1. creates a server, user mapping and foreign tables (swrs.organisation, swrs.report) using the postgres_fdw extension
2. copies the operators from the swrs.organisation table into the cif.operator table
3. drops the server with cascade

Given that the swrs.organisation foreign table is setup When operators are imported from it Then only the latest record for each swrs_organisation_id is imported (most recent reporting year and report submission date) And only the swrs_organisation_id, business_legal_name and english_trade_name are imported

Given that an operator was previously imported When importing the operators Then fields that were edited in the CIF app should not be overwritten (i.e. we need to track editing of the legal and trade names)

Development Checklist:

• [x] The helm chart should lookup the GGIRCS DB readonly user credential and copy them into the cas-cif secret, which is updated on every helm upgrade
• [x] Add network policy in GGIRCS namespace to allow CIF db ingress
• [x] cif.operator has a swrs_organisation_id column
• [x] cif.operator has legal_name_updated_by_cif_user and trade_name_updated_by_cif_user boolean columns tracking whether the fields were edited via the CIF app. That could be done via a trigger that is disabled during the import
• [x] Meets the DOD

Notes:

-

Personas:

• Program Development Team
• Operations Team

[matthieu-foucault]

Hey team! Please add your planning poker estimate with ZenHub @AlexZorkin @dleard @pbastia

[LindsayMacfarlane]

Thank you for the walk through @dleard, much appreciated. I look forward to seeing this in TEST where the data will be more representative. Awesome work.