---
# System prepended metadata

title: 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

