Migration Guide

Migrations are squashed

All migrations from before v16 have been squashed, so remove the migration history and add a single line for the new squashed migration.

DELETE FROM flyway_schema_history;
INSERT INTO flyway_schema_history (installed_rank, version, description, type, script, checksum, installed_by, installed_on, execution_time, success) VALUES (1, '16.0.99.00', 'squashed structure', 'SQL', 'database/init/V16.0.99_00__squashed_structure.sql', 353179779, 'root', current_timestamp(), 0, 1);

Questionnaire flow steps

In v17 we introduced a new flow step to display questionnaires. The existing questionnaire flow steps are deprecated and need to be replaced by the new flow step.

tip

Automatic migration available On the admin Organization configuration page, the Questionnaire flow steps migration (v17) card has a Migrate button that migrates all flow definitions of the organization. The result reports how many flow steps were migrated and lists the flow steps that could not be migrated automatically, each with the reason why. When anything goes wrong, nothing is changed.

Fixing skipped flow steps

A step is only skipped when there is no questionnaire section for the new flow step to point at — the deprecated step resolves its questions by subsegment, so a skipped step displays no questions today either. The report gives one of two reasons:

• No form definition linked — the flow definition has no flow configuration (typically an orphan/unused flow definition), so there is no form to take sections from. Link a form definition to its flow configuration, or remove the obsolete flow definition.
• No form section matches the subsegment — a form is linked but has no section for the step's subsegment. Add the missing section to the form definition, or remove the obsolete flow step.

After correcting the configuration, run the Migrate button again. There is no need to hand-edit the flow steps.

All changes applied by the migration

• Each deprecated flow step becomes one new flow step per form section matching its subsegment, keeping its identifier, position, module and other settings; coveredBy references follow split steps.
• MIFID steps with answerTarget: RESPONSIBLE: fixed responsibles + counterparty-individuals associatedPartyFilter, and submissionVisibility: PERSONAL on the responsibility (answers stay private in the PDF). Other answer targets on the MIFID responsibilities: the previously implicit submissionVisibility: SHARED is written explicitly (a configured visibility is kept).
• PARTY_IDENTITY_QUESTIONS: partyTypes: [INDIVIDUAL] on the filter (steps stay individual-only).
• UBO_QUESTIONS, and answerTarget: CLIENT outside the CLIENT responsibility: an explicit client filter (the client stays the one answering).
• Behaviour change: a MIFID_V2_GOALS step blocked the other counterparties' flow steps until the goals were confirmed or signed. This gate is tied to the old step type, so after migration the flow order is less strict — only the order changes, questions/answers/visibility are unaffected.

Form definition required on flow configurations

Prior to v16, the flow configuration of a dossier definition didn't require a valid form definition to be configured. Flow configurations without a form definition configured will result in a configuration exception upon entering the flow. Starting in v17 a form definition is always required to be configured on a flow configuration: the id_form_definition column is NOT NULL, so the upgrade will fail when flow configurations without a form definition exist.

danger

Before upgrading, verify that no flow configurations without a linked form definition exist on your instance:

SELECT id_flow_configuration as flow_configuration_with_missing_form_definition, id_dossier_definition as linked_to_dossier_definition
FROM dossier_definition_flow_configuration
WHERE id_form_definition IS NULL;

If any rows are returned, link a form definition to these flow configurations or remove the flow configurations before upgrading.

revision_information.timestamp is no longer nullable

To send correct timestamps to the data warehouse, we need timestamps on all revisions. Verify that all rows have a value:

select count(*) from revision_information where timestamp is null;

The column might be null due to manual migrations. To set the timestamp to the most recent timestamp, the query below can be used. The query is using batches of 100 and only looks at the 10 previous revisions to find a timestamp. Adjust the parameters as needed.

update revision_information ri1
join (
    select r1.revision as target_revision, max(r2.timestamp) as max_timestamp
    from revision_information r1
    join revision_information r2 on r2.revision < r1.revision and r2.revision >= r1.revision - 10 and r2.timestamp is not null
    where r1.timestamp is null
    group by r1.revision
    limit 100
) derived on derived.target_revision = ri1.revision
set ri1.timestamp = derived.max_timestamp
where ri1.timestamp is null;

Additional columns on financial_sp_dossier_archive

The following columns were added on the financial_sp_dossier_archive table:

• id_financial_sp
• front_office_time_updated
• back_office_time_updated

Filling in these columns during the Flyway migration is not possible due to the amount of data. The following queries can be used to backfill the data. Batching might be required.

-- Set id_financial_sp
-- Set time_updated based on the revision timestamp. It's not 100% correct, but it's better than having it empty.
UPDATE financial_sp_dossier_archive da
    JOIN financial_sp_dossier d USING (id_dossier)
    JOIN revision_information ri on da._revision = ri.revision
SET da.id_financial_sp = d.id_financial_sp,
    da.front_office_time_updated = FROM_UNIXTIME(ri.timestamp / 1000),
    da.back_office_time_updated = FROM_UNIXTIME(ri.timestamp / 1000);

-- For the most recent revision entry, we do have the correct timestamps on the non-archive table, so we can use those to update the most recent archive entry of each dossier
UPDATE financial_sp_dossier_archive da
    JOIN financial_sp_dossier d USING (id_dossier)
    JOIN (
        SELECT id_dossier, MAX(_revision) AS max_revision
        FROM financial_sp_dossier_archive
        GROUP BY id_dossier
    ) AS latest ON da.id_dossier = latest.id_dossier AND da._revision = latest.max_revision
SET da.front_office_time_updated = d.front_office_time_updated,
    da.back_office_time_updated = d.back_office_time_updated;

Organization required on configuration entities

Starting in v17, the id_organization column is NOT NULL on the following tables:

• dossier_definition
• flow_definition
• form_definition
• profile_definition
• question
• task_list_definition

Previously, these entities could exist without an organization (shared across the instance). This is no longer supported. Every configuration entity listed above must be linked to a specific organization.

Additionally, unique constraints on the identifier column of these tables (and dossier_status_transition_definition, financial_sp_definition, flow_option_definition, report_query) have been changed from instance-level uniqueness to organization-level uniqueness.

danger

Before upgrading, verify that no configuration entities exist without an organization on your instance. You can run the following query to check:

SELECT 'dossier_definition' AS entity, identifier FROM dossier_definition WHERE id_organization IS NULL
UNION ALL
SELECT 'flow_definition', identifier FROM flow_definition WHERE id_organization IS NULL
UNION ALL
SELECT 'form_definition', identifier FROM form_definition WHERE id_organization IS NULL
UNION ALL
SELECT 'profile_definition', identifier FROM profile_definition WHERE id_organization IS NULL
UNION ALL
SELECT 'question', identifier FROM question WHERE id_organization IS NULL
UNION ALL
SELECT 'task_list_definition', identifier FROM task_list_definition WHERE id_organization IS NULL;

If any rows are returned, assign an organization to these entities before upgrading. The migration will fail if any of these columns contain NULL values.