This seems related to T329006

We can discuss this synchronously, but I was thinking that maybe we might reach out to DBAs in the planning phase. This could help us understand and evaluate all options properly.

Hi there, We are about to start planning for the DB structure of PII.
Actually, we had our first conversation about it on our Engineering meeting last week (14/03/2023).
I brought kind of a first version of how the tables could look like, we can start from there, and we will make adjustments until we get closer to what we think would fit us, then we can ask for a call with the DBAs to show them and get some advices.

So here are an example of how the tables could look like:
Tables: ce_questions, ce_question_options, ce_question_answers, ce_question_aggregation

#ce = Campaing Events
ce_questions

ceq_id | ceq_answer_type (1 - free text, 2 - one choice, 3 - multiple choice, etc...)
------------------------
1      | 2               ( E.g: What is your age? )

#ceq = ce_question
ce_question_options ( this table would save the options that the users can choose )

ceqo_id  | ceq_id
--------------------
1        | 1        ( E.g: I am 14 to 18 )
--------------------
2        | 1        ( E.g: I am 19 to 32 )

#ceqa = ce_question_answers
#Here we save the PII ( this will be anonymized/deleted after 90 days)
ce_question_answers

ceqa_id | ceqa_user_id | ceqa_event_id | ceqa_question_id | ceqa_answer_option | ceqa_answer_text
1       | 1            | 1             | 1                | 2                  | null

#In the example below, we stored in "ce_question_aggregation" table, that 9 users chose option ID 1 and 8 users chose option 2 when they answered the qustion ID 1, which is this example is "What is your age?", so we have that 9 users said "14 to 18" and 8 users said " 19 to 32"

ce_question_aggregation

ceqag_id | ceqag_question_id | ceqag_event_id | ceqag_qustion_option_id | ceqag_id_answers_amount | ceqag_answer
1          1                   1                1                         9                       | null
1          1                   1                2                         8                       | null

// this is an option of how to aggregate freetext answers
1          3                   1                null                      7                       | aaaaaaaa
1          3                   1                null                      5                       | bbbbbbbb

@Daimona , @MHorsey-WMF , @ldelench_wmf, @Iflorez , @VPuffetMichel , @MMoss_WMF

Discussed with @cmelo @VPuffetMichel : let's review this at our next Engineering/Design Sync (also to make sure this structure serves the initial wireframes in T322751); if there are no concerns within Campaigns we can go ahead and request a review/consult with DBA.

Tables: ce_questions, ce_question_options, ce_question_answers, ce_question_aggregation

#ce = Campaing Events
ce_questions

ceq_id | ceq_answer_type (1 - free text, 2 - one choice, 3 - multiple choice, etc...)
------------------------
1      | 2               ( E.g: What is your age? )

#ceq = ce_question
ce_question_options ( this table would save the options that the users can choose )

ceqo_id  | ceq_id
--------------------
1        | 1        ( E.g: I am 14 to 18 )
--------------------
2        | 1        ( E.g: I am 19 to 32 )

We decided that we don't need the 2 tables above, so tables will be only:
ce_question_answers, ce_question_aggregation

Hi @cmelo @VPuffetMichel @gonyeahialam @Iflorez , we decided that one of our sprint goals would be deciding on boundaries for PII retention before/after an event takes place. I think that fits into the scope of this spike, so I've added to the task description two options that were discussed this week, along with a visual if it's helpful. I'm eager to hear what you all think of these, if you have a preference, or if you have other approaches in mind. Thanks!

PS: if you're wondering where I came up with the 60-day-before-event-start-date boundary: for events created in January 2023, the maximum time between enabling registration and the event start date was 56 days.
This is open to debate, too!

As far as time boundaries prior to event-start:
@JStephenson noted that many grantee organizers register event participants in the month prior to the event (to ensure time to work out any account registration or IP issues).

My sense is that the time boundary seen in the data is in large part a product of what we've allowed up to now.

Have ambassadors shared their feedback? @Astinson might have feedback from his experience on when organizers begin registering for events.

@lauren thank you for the visual.

As far as data retention time periods overall, Option 2, is more conservative and thus my preference of the two options.

However, I'm not seeing why we need to hold data for 60 days prior to the event starting AND also 90 days after the event starting. Why not 60 days prior and 30 days post? Or 30 days prior and 60 days post? From a data retention perspective, keeping to the current time period max (90) seems best for keeping personal data "for the shortest possible time that is consistent with the maintenance, understanding, and improvement of the Wikimedia Sites, and our obligations under applicable U.S. law" - 90 day retention policy page

@cmelo Can you say more about the ce_question_aggregation buckets? Ideally, the buckets that we use coincide with the answer options. Also, what about threshholds? If there are fewer than x numbers can we bucket that as <x ?

Change 904513 had a related patch set uploaded (by Damilare Adedoyin; author: Damilare Adedoyin):

[wikimedia/fundraising/SmashPig@master] Handle REJECT and other failure responses with http response status code 200 in ApprovePayment and CancelPayment methods for Dlocal.

https://gerrit.wikimedia.org/r/904513

As for starting registration for events: 56 days is super generous. I can't think of a situation where folks are registering more than 30 days out (you have to remember most of the "actions" that organizers want people to do are easy to forget about if you don't do it in a timely manner).

In T328032#8737138, @Iflorez wrote:

@cmelo Can you say more about the ce_question_aggregation buckets? Ideally, the buckets that we use coincide with the answer options. Also, what about threshholds? If there are fewer than x numbers can we bucket that as <x ?

Hi @Iflorez, sure, We are still checking how we will aggregate the data, and we will probably save it on X1 as well, we will aggregate the data on going, by that I mean we will update the aggregated data when the users answer/edit their responses, and then after 90 days we anonymize or delete the PII.

We had a meeting with the DBA, and he told us that we should check with privacy engineering re anonymizing data (e.g., add noise / randomize responses for aggregation), I will add more context about this as soon as I know more about it.

Thank you!

We're still left with an open question: should we delete the “time of first answer” if the participant deletes all their answers? Meaning we would restart counting from the start if they choose to later answer the question again.

That's a good point, I think it makes sense to delete it.
what do you think @VPuffetMichel , @MHorsey-WMF , @Iflorez ?

@Daimona this looks good. I would start working on the design and reach out if I encounter any confusion.

The next steps above that refer to design work may not be designed as described above but I would reach out to you for such cases. An example is:

• We will add an error message to use when the organizer tries to change the end date of an event that has already ended. This will be shown upon form submission.

Since we don't want this action in the first place as described below, we don't need to wait till the user does it and submits the form before we inform them it is not allowed. That would be stressful. We tell them before hand either by an inline error message as soon as they change the date or disable editing the date after the event has ended with a note on why it is disabled. I would explore this further as I begin the design.

This implies that there's a new requirement: organizers cannot change the end date of the event if the event is in the past. Without this restriction, organizers would be able to move the end date back and forth to view the aggregated data, and potentially infer individualized data from it. For instance, if there are 20 participants, you could change the end date to be in the past, take note of the aggregated data, and set the end date in the future again. When a new person registers, you could repeat the same trick and infer their answers from the differences in aggregated data. This restriction will be explained to the organizers.

In T328032#8938654, @cmelo wrote:

We're still left with an open question: should we delete the “time of first answer” if the participant deletes all their answers? Meaning we would restart counting from the start if they choose to later answer the question again.

That's a good point, I think it makes sense to delete it.
what do you think @VPuffetMichel , @MHorsey-WMF , @Iflorez ?

I don't think we need it for anything or do we?

In T328032#8945974, @gonyeahialam wrote:

@Daimona this looks good. I would start working on the design and reach out if I encounter any confusion.

The next steps above that refer to design work may not be designed as described above but I would reach out to you for such cases.

Yup, absolutely! I included some potential design ideas just to clarify what I meant, but those are in no way finalized or required. I will leave that up to you.

An example is:

• We will add an error message to use when the organizer tries to change the end date of an event that has already ended. This will be shown upon form submission.

Since we don't want this action in the first place as described below, we don't need to wait till the user does it and submits the form before we inform them it is not allowed. That would be stressful. We tell them before hand either by an inline error message as soon as they change the date or disable editing the date after the event has ended with a note on why it is disabled. I would explore this further as I begin the design.

Makes sense! I think disabling the field is a good thing to do regardless of additional error messages. However, do note that we will still need an error message for API users.

In T328032#8946128, @gonyeahialam wrote:

In T328032#8938654, @cmelo wrote:

We're still left with an open question: should we delete the “time of first answer” if the participant deletes all their answers? Meaning we would restart counting from the start if they choose to later answer the question again.

That's a good point, I think it makes sense to delete it.
what do you think @VPuffetMichel , @MHorsey-WMF , @Iflorez ?

I don't think we need it for anything or do we?

Indeed. I also think it should just be deleted.

@Daimona I have begun designing and I have some additional questions

Question

Note that deletion and aggregation need not happen at the same time. What's really important is that all the data has been aggregated when the event ends, because the organizer would then be able to see the aggregates. Data may as well be deleted once it's been aggregated since we couldn't allow further changes anyway.

• Would deletion always happen 90 days after the first answer regardless of when the aggregation happens?

• As soon as a participant answers the first question, a 90-day timer starts. When the timer reaches 0, we will aggregate and delete the answers of that participant.

• Does the timer start counting when the participant answers their first question (I understand the first question to be any of the questions they answer first e.g What is your age?) or when the participant submits the registration form with at least one question answered?
• Also, since deletion and aggregation may not happen at the same time wouldn't the timer for aggregation and deletion may be different?

We will make sure that participants are not allowed to answer if their answers have already been aggregated. This should work whether the participant has unregistered and re-registered or not.

Please confirm my interpretation of the consequences of this.
For events that end before the 90 days limit (aggregation happens at event end). The consequences would be that after aggregation(which is also event end), the participant:

• Can’t edit their registration form responses,
• Can cancel their registration but can not register again;

If the event is longer than 90 days that means aggregation would happen while the event is still on, the consequences of this would be that after aggregation, the participant:

• Can’t edit registration form responses,
• Can cancel the registration,
• Can register again(if the event hasn't ended) but can not fill the registration form(But how would we know that a participant who canceled their registration previously is trying to register again, are we going to still store the fact they once registered even after cancellation)

In T328032#8946509, @gonyeahialam wrote:

Note that deletion and aggregation need not happen at the same time. What's really important is that all the data has been aggregated when the event ends, because the organizer would then be able to see the aggregates. Data may as well be deleted once it's been aggregated since we couldn't allow further changes anyway.

• Would deletion always happen 90 days after the first answer regardless of when the aggregation happens?

I guess this is more of an implementation detail, but I think we would always do aggregation and deletion at the same time.

• As soon as a participant answers the first question, a 90-day timer starts. When the timer reaches 0, we will aggregate and delete the answers of that participant.

• Does the timer start counting when the participant answers their first question (I understand the first question to be any of the questions they answer first e.g What is your age?) or when the participant submits the registration form with at least one question answered?

I'm not sure if I understand the difference between these. Participants would do both things at the same time, right?

• Also, since deletion and aggregation may not happen at the same time wouldn't the timer for aggregation and deletion may be different?

In theory, they could. But I think for simplicity we could just aggregate and delete the data at the same time, and with the same timer. This doesn't have to be the case, but it's the simplest option I believe.

We will make sure that participants are not allowed to answer if their answers have already been aggregated. This should work whether the participant has unregistered and re-registered or not.

Please confirm my interpretation of the consequences of this.
For events that end before the 90 days limit (aggregation happens at event end). The consequences would be that after aggregation(which is also event end), the participant:

• Can’t edit their registration form responses,

Correct.

• Can cancel their registration but can not register again;

Also correct, and this is already how it works.

If the event is longer than 90 days that means aggregation would happen while the event is still on, the consequences of this would be that after aggregation, the participant:

• Can’t edit registration form responses,

Aye.

• Can cancel the registration,

Yeah, same as current behaviour.

• Can register again(if the event hasn't ended) but can not fill the registration form

Yea.

(But how would we know that a participant who canceled their registration previously is trying to register again, are we going to still store the fact they once registered even after cancellation)

Yes, and this is already the case, although it's not exposed anywhere in the interface.

Thank you for the responses @Daimona

Thank you @Daimona

Quick answers here and I'll follow up on the appropriate follow-up tickets:

In T328032#8938654, @cmelo wrote:

We're still left with an open question: should we delete the “time of first answer” if the participant deletes all their answers? Meaning we would restart counting from the start if they choose to later answer the question again.

That's a good point, I think it makes sense to delete it.
what do you think @VPuffetMichel , @MHorsey-WMF , @Iflorez ?

1. I don't have a reason for keeping this data

2. Shall we aggregate and delete the data at the same time, and with the same timer? Maybe not...More on implementation of aggregation coming soon.