Implementation of Soft Deletion for Gitcoin Passport Stamps

[erichfi]

User Story:

As a Gitcoin administrator, I want to implement soft deletion for Passport Stamps, So that deleted and expired stamps are kept in the read replica of the Passport Scorer DB.

Acceptance Criteria

GIVEN a Passport Holder deletes a stamp WHEN the deletion process is initiated THEN the stamp should be marked as 'deleted' or 'expired' but not removed from the read replica of the Passport Scorer DB

AND

GIVEN a Passport Holder's stamp has expired WHEN the system checks for stamp validity THEN the expired stamp should be marked as 'expired' but not removed from the read replica of the Passport Scorer DB

Product & Design Links:

Tech Details:

• Implementation of soft deletion requires changes in the database schema to include a status attribute for stamps (active, deleted, expired).
• The deletion process should update this status attribute instead of removing the record from the database.
• The same approach should be taken when a stamp expires.

Open Questions:

• How will this change affect the system's performance, given that the database will grow in size more quickly due to retention of deleted and expired stamps?
• Are there any potential issues with data privacy when retaining deleted data?

Notes/Assumptions:

• We are assuming that retaining deleted and expired stamps in the read replica DB will provide valuable data for the Passport Scorer DB without negatively affecting the user experience.
• We assume that proper data handling measures are in place to respect user privacy while implementing this feature.

[nutrina]

After a discussion with the engineering team, we came to the conclusion that the soft-delete is not the best solution because:

• when the user updates a stamp in the UI, we do not delete the stamp in the DB, but only update the issuance date
• hence stamp refreshes cannot be tracked
• so we will not be able to verify if a user does have a consistent behaviour (streak) of refreshing his passport stamp

Alternatively we propose to creates an Events table, that will track all the DB events (for the passport stamps - the CeramicCache table - for now):

• create
• update
• delete

This would allow recording all events, so the users pattern of refreshing stamps could also be evaluated over time We think that this approach is technically feasible, we do not expect this to result in an overflow of data events that we cannot handle. And in the future we might offload the events table or switch to another external storage more suitable for such use-cases (event queue, event stream, etc ...)