Change Details

Currently we are storing the tracking information of donations in the serialized `data` property. This has the following drawbacks: - It's very hard to create analysis and reports on donations in the Fundraising Operation Center, leading to a lot of code that filters donations in PHP, which is a challenge for developer understanding and has a negative impact on performance of the FOC. - Each donation takes an additional 20-30 bytes to store. - We can't do ad-hoc queries for donations of specific campaigns. To prepare for improvements in the FOC we need to extract the tracking information. To keep the existing scripts running, we need to keep storing the tracking data in the `data` blob until the FOC is adapted. **Acceptance criteria** - All new donations store tracking data in a way that can be queried from SQL, targeting campaigns and keywords separately. This is *in addition* to the `tracking` string in the `data` blob (for backwards compatibility). - The database migration copies the existing tracking data of donations to the new tracking table. - The deprecated fields `source`, `color` and `layout` are removed from the `TrackingInfo` domain class. They are superseded by other software and are not exported since 2019. **Implementation details** - Store the `TrackingInfo` domain class as an entity in the donation bounded context. Backing table will probably have 3 columns: `id`, `campaign`,`keyword` (with a 2-column unique index on campaign and keyword). Use `donation_tracking` as the table name, because we will have tracking for memberships and subscriptions as well. - Store the tracking ID in donation (using the legacy converter classes), - Add a 1:n column relationship between Donation Doctrine entity and the Tracking entity - Create a migration that adds the new entity table and adds the `tracking_id` property to the donation (`spenden`) table - Create a script that reads existing donations and their tracking information, creating new entries in the tracking table as needed (each campaign/keyword combination should be unique) and updating the donation. Don't do this in the migration because you need to query and modify the 7Mio+ donations in batches. Test the script in the test environment with the full production data set.

Currently we are storing the tracking information of donations in the serialized `data` property. This has the following drawbacks: - It's very hard to create analysis and reports on donations in the Fundraising Operation Center, leading to a lot of code that filters donations in PHP, which is a challenge for developer understanding and has a negative impact on performance of the FOC. - Each donation takes an additional 20-30 bytes to store. - We can't do ad-hoc queries for donations of specific campaigns. To prepare for improvements in the FOC we need to extract the tracking information. To keep the existing scripts running, we need to keep storing the tracking data in the `data` blob until the FOC is adapted. **Acceptance criteria** - All new donations store tracking data in a way that can be queried from SQL, targeting campaigns and keywords separately. This is *in addition* to the `tracking` string in the `data` blob (for backwards compatibility). - The database migration copies the existing tracking data of donations to the new tracking table. - The deprecated fields `source`, `color` and `layout` are removed from the `TrackingInfo` domain class. They are superseded by other software and are not exported since 2019. **Implementation details** - Store the `TrackingInfo` domain class as an entity in the donation bounded context. Backing table will probably have 3 columns: `id`, `campaign`,`keyword` (with a 2-column unique index on campaign and keyword). Use `donation_tracking` as the table name, because we will have tracking for memberships and subscriptions as well. - Store the tracking ID in donation (using the legacy converter classes), - Add a 1:n column relationship between Donation Doctrine entity and the Tracking entity - Create a migration that adds the new entity table and adds the `tracking_id` property to the donation (`spenden`) table - Create a script that reads existing donations and their tracking information, creating new entries in the tracking table as needed (each campaign/keyword combination should be unique) and updating the donation. Don't do this in the migration because you need to query and modify the 7Mio+ donations in batches. Test the script in the test environment with the full production data set. - Three implementations of an interface that stores donation tracking data: - one that stores it in the table. - one that stores it in the blob. - a proxy that uses the two implementations above. - When the Fundraising Operation Center is ready to use the new tables, we can drop the latter two.

Currently we are storing the tracking information of donations in the serialized `data` property. This has the following drawbacks: - It's very hard to create analysis and reports on donations in the Fundraising Operation Center, leading to a lot of code that filters donations in PHP, which is a challenge for developer understanding and has a negative impact on performance of the FOC. - Each donation takes an additional 20-30 bytes to store. - We can't do ad-hoc queries for donations of specific campaigns. To prepare for improvements in the FOC we need to extract the tracking information. To keep the existing scripts running, we need to keep storing the tracking data in the `data` blob until the FOC is adapted. **Acceptance criteria** - All new donations store tracking data in a way that can be queried from SQL, targeting campaigns and keywords separately. This is *in addition* to the `tracking` string in the `data` blob (for backwards compatibility). - The database migration copies the existing tracking data of donations to the new tracking table. - The deprecated fields `source`, `color` and `layout` are removed from the `TrackingInfo` domain class. They are superseded by other software and are not exported since 2019. **Implementation details** - Store the `TrackingInfo` domain class as an entity in the donation bounded context. Backing table will probably have 3 columns: `id`, `campaign`,`keyword` (with a 2-column unique index on campaign and keyword). Use `donation_tracking` as the table name, because we will have tracking for memberships and subscriptions as well. - Store the tracking ID in donation (using the legacy converter classes), - Add a 1:n column relationship between Donation Doctrine entity and the Tracking entity - Create a migration that adds the new entity table and adds the `tracking_id` property to the donation (`spenden`) table - Create a script that reads existing donations and their tracking information, creating new entries in the tracking table as needed (each campaign/keyword combination should be unique) and updating the donation. Don't do this in the migration because you need to query and modify the 7Mio+ donations in batches. Test the script in the test environment with the full production data set. - Three implementations of an interface that stores donation tracking data: - one that stores it in the table. - one that stores it in the blob. - a proxy that uses the two implementations above. - When the Fundraising Operation Center is ready to use the new tables, we can drop the latter two.