GDPR Deletion Stuff

# GDPR Deletion Stuff GDPR Deletion is the legal requirement for us to permanently delete specific customer data, within a certain amount of time. There is a Sproc `GDPR_FamilyTree_Deletion` in RelationDB that is used to perform this task on trees marked to be deleted. ## Stored procedures ### GDPRTreeDeletion - Create table GDPRDeletion_FamilyTreeIdsToTarget, if it is not there - Insert into this table: - the top 1000 trees marked with TreeStatus = 999 - where deleted is 1 - and datedeleted < 7 days (over 7 days ago). - and the number of persons associated to that tree is under 20000 - FMP.dbo.CheckTempDb is the percentage of usage of the `temp DB`, it is a variable stored in a "view". - if (CheckTempDb > 50) Insert familyTreeId into GDPRTreeDeletionLog table - Check if there is 1 or more record in GDPRDeletion_FamilyTreeIdsToTarget and @TempDB_Used_percentage is under 50% - Insert familyTreeId into GDPRTreeDeletion_Log - If tables tempdb.dbo.#AllAncestors or tempdb.dbo.#DescendentPerson are there, drop them (housekeeping) - create table #AllAncestors - update GDPRTreeDeletion_Log.TimeStartedRecursion for this familyTreeId with the current time - Insert in AllAncestors every single persons parent (mother or father), that means 2 records for each person who has 2 parents. - create index on AllAncestors(person) - Count the number of distinct parents that each person has - Select the person with the most parents - if the person has more than 10 parents. - update GDPRTreeDeletion_Log with StatusInfo = "parent count too large" - Goto: DeleteTreeIdFromList - Create Temporary Descendant List and: - populate with everyone from #AllAncestors born more than 110 years ago; and - populate with any of their parents, up to 10 generations before. - Select all persons from the above list with isLiving = null - Add in @PERSONS_TO_DELETE: - All Persons in this tree; and - Not born before 110 years ago - with isLiving = 0 - PLUS: All Persons listed in DescendantPerson - Update GDPRTreeDeletionLog.TimeEndedRecursion with current time (time we ended recursion) - Drop table #AllAncestors now that we have all persons we want to delete in @PERSONS_TO_DELETE - Add FamilyId in @MARRIAGES_WITH_DELETED_PERSONS where mother/father exists in @PERSONS_TO_DELETE - Add FamilyId in @FAMILYS_WITH_DELETED_CHILDREN where children's PersonId exists in families to delete. - Delete records from dbo.Place associated with this familyTreeId (2500 at a time) - Delete records from dbo.Source associated with this familyTreeId (2500 at a time) - Delete records from dbo.SourceRepo associated with this familyTreeId (2500 at a time) - Delete records from dbo.Contact associated with this familyTreeId (2500 at a time) - Delete records from dbo.Dna associated with this familyTreeId (2500 at a time) - Delete identifying information from the family records - Create empty table GDPR_DeletionFamily - Populate with FamilyIds from the list @MARRIAGES_WITH_DELETED_PERSONS - Update family with null or 0 values for identifying info (e.g. MarriageDate, MarriagePlace, GedComPtr...) - Drop table GDPRDeletionFamily - Delete all FamilyFact relating to people to be deleted , 2500 at a time. - Delete records (/2500) from dbo.FamilyFacts where familyid in @MARRIAGES_WITH_DELETED_PERSONS or @FAMILYS_WITH_DELETED_CHILDREN - Remove Places from FamilyFacts where dbo.Place has been deleted - Delete records from dbo.PersonFact for persons which have been deleted. - Update dbo.PersonFact.PlaceId with 0 where the Place records were deleted. - Update dbo.PersonName to remove identifying data for deleted persons - Delete all dbo.PersonSearchWords for deleted persons - Delete all dbo.Notes for deleted persons - Update Person to remove identifying data (/2500), ie populate names and dates with null or 0 or unknown - Drop table GDPRDeletionPerson - Delete all EntityFactShare associated with this tree - Update the FamilyTree to mark it as deleted, TreeStatusNode='Deleted. Living nodes have been deleted.' - DeleteTreeIdFromList: delete the top record GDPRDeletion_FamilyTreeIdsToTarget. ### Note - Genes does not need any changes made to it's deletion process, as it conforms to GDPR, already. ## Requirements ## How it works ### Tables Affected