IBD Database Sanitisation - Draft

What to do about GP Accounts?
Will PV break if I delete accounts that are referenced via created_by, updated_by etc.
Are we deleting the Renal Units etc. or will SSG do that?

IDs come from a SEQUENCE called hibernate_sequence. This should be checked to make sure it is not reset as part of the process as otherwise existing IDs may be re-used.

Identify IBD Patients

SELECT COUNT(DISTINCT X.id) AS pat_count
FROM
	pv_user X
INNER JOIN pv_user_group_role Y ON X.id = Y.user_id
INNER JOIN pv_group Z ON Y.group_id = Z.id
WHERE
	-- Patient
	Y.role_id = 1 AND
	-- Unit or Specialty
	Z.type_id IN (1, 2) AND
    -- IBD
    Z.code IN ('IBD', 'SALIBD')

Identify IBD Staff

SELECT COUNT(DISTINCT X.id) AS staff_count
FROM
	pv_user X
INNER JOIN pv_user_group_role Y ON X.id = Y.user_id
INNER JOIN pv_group Z ON Y.group_id = Z.id
WHERE
	-- Staff (Staff, Admin, Speciality Admin)
	Y.role_id IN (2, 3, 4) AND
	-- Unit or Speciality
	Z.type_id IN (1, 2) AND
    -- IBD
    Z.code IN ('IBD', 'SALIBD')

Delete Users who are not IBD Staff or Patients

DELETE FROM pv_user
WHERE
  ID NOT IN
    [USER QUERY]
  AND
  ID NOT IN
    [STAFF QUERY]

The FOREIGN KEYS in the PV database are not set to DELETE CASCADE so rows in other tables belonging to these users will not be automatically deleted.

PATIENTVIEW_PRODUCTION Tables with No Patient Data

Table Name	Description	Needs Cleaning?

Table Name	Description	Needs Cleaning?
pv_code	Diagnosis Codes (for My Diagnosis)	How did PV get rights to NHS Choices data?
pv_contact_point	Contact details for Units etc.
pv_feature	List of Optional Features
pv_feature_feature_type	Feature Metadata
pv_feature_group	Which Features belong to which Group
pv_feature_user	Which Features belong to which User
pv_group	List of Groups
pv_group_relationship	Group Hierarchy
pv_group_statistics	Historic Stats
pv_link	URLs for Patient Information via pv_codes
pv_location	Satellite Unit Information
pv_lookup_type	Metadata
pv_lookup_value	Metadata

PATIENTVIEW_PRODUCTION Tables with Patient Data

(A Direct Route to User ID is where a user_id field is present in the table)

Table Name	Description	TRUNCATE?	Route to User ID

Table Name	Description	TRUNCATE?	Route to User ID
pv_alert	Patient Alerts for New Results etc.		Direct
pv_audit	Event Logs	Yes
pv_conversation	Part of Messaging		via pv_conversation_user
pv_conversation_user	Part of Messaging		Direct
pv_feature_user	Which features belong to which user.		Direct
pv_fhir_link	Link to FHIR Database		Direct
pv_identifier	Patient NHS Nos etc.		Direct
pv_log	Empty?		Direct