Context

We recently identified a bug in the processing of events for pay in advance charges that led to some duplicated fees in the database. See #4220 for the fix of the initial bug.

On the fees table, an index named idx_on_pay_in_advance_event_transaction_id_charge_i_16302ca167 was supposed to prevent duplicated in advance fees:

CREATE UNIQUE INDEX idx_on_pay_in_advance_event_transaction_id_charge_i_16302ca167 ON public.fees 
USING btree (pay_in_advance_event_transaction_id, charge_id, charge_filter_id) 
WHERE ((created_at > '2025-01-21 00:00:00'::timestamp without time zone) 
  AND (pay_in_advance_event_transaction_id IS NOT NULL) 
  AND (pay_in_advance = true));

The issue reside in the way Postgres handles null values in indexes. Each null value is considered a distinct value meaning the index is useless in its current form as since charge_filter_id is null most of the time...

Goal

The main goal of the fix is to add a new set of index to really prevent duplication in the database.
The difficulty will reside in the fact that we already have duplicated values in DB and we cannot remove them as they have already impacted the customers.

The complete fix will follow these steps:

• Phase 1:
  • Add a new duplicated_in_advance flag on the fees table with default value to false
  • Set this field to true for all existing duplicated fees
• Phase 2:
  • Add new indexes making preventing the duplication of fees making sure that null values are not considered distinct and ignoring pre-existing duplication
  • Remove the existing index

Description

This PR is the phase 1.
It adds two migrations to:

• Add the new duplicated_in_advance field to the fees table
• Fill the field for all existing duplicated fees