Page History

...

The Metadb reporting database serves as a platform for generating reports for library staff on data from all functional areas of the FOLIO system. The Metadb software extracts, transforms, and loads data from the FOLIO application server continuously to the reporting database through its process of data synchronization. Both current and historical records are saved in tables in the reporting database. Some of these tables contain fields with patron identifiers, such as "user_id" and "requester_id." Patron identifiers are needed for current transactions – such as loans, requests, and fines that are open – to enable library staff to identify and communicate with those patrons. Patron identifiers for historical transactions – such as loans, requests, and fines that are closed – are not needed. To uphold the Cornell Library's Commitment to Privacy policies policies, patron identifiers on historical transaction records should be replaced with NULL values in the Metadb reporting databasethe Metadb reporting database.

(from Jenn) Our promise to our patrons:
The Library respects the privacy of all borrowers of library materials. We will not reveal the names of individual borrowers nor reveal what books are, or have been, charged to any individual except as required by law. Only staff members who have a functional need to view circulation data can view who has borrowed a book. The Library seeks to protect user privacy by purging borrowing records as soon as possible. In general, the link connecting a patron with a borrowed item is broken once the item is returned. The exception is when a bill for the item is generated.

FOLIO Data vs Metadb Data Access

FOLIO provides the functionality to create granualar roles for user account access that are limited to the staff member's more narrow data access needs. Metadb does not yet have role based access functionality for user accounts, so all users see all data. Support for more granular permissions has been added in Metadb 1.4: see https://gist.github.com/nassibnassar/9e8e63383db1dac0634b0cf371254dec . If there is a concern that just having the patron-identifying data residing in the database is a problem, however, support for more granular user permisions will not help to address data privacy issues. 

-- add info on granular user permissions feature

Risks

Like many reporting databases, the data fields and tables in Metadb are woven together with a complex set of interdependencies, connections, and data relationships. Deleting data in a PostgreSQL reporting database without a thorough understanding of the impacts on its infrastructure and data integrity poses several significant risks. 

...

Loan Anonymization Scripts

These scripts were reviewed and approved by Nassib. They were run by the hosting team for the first time on 5/9/25. They are being automated by the hosting team to run every Saturday at 9am ET going forward starting 5/17/25.

...

UPDATE folio_circulation.loan__t__ 
SET user_id = NULL 
WHERE __current = FALSE 
AND user_id IS NOT NULL 
;

...

Historical Request data to be purged

-request data is not attached to fines, fees, or loans

...

-need to update requester id value to NULL on request rows that are not current or request status is Closed for both historic and current rowscurrent 

-need to determine whether or not BD and ILL requests need to be considered differently

...

request__t : requester_id has values

Requester Anonymization ScriptsScripts

These scripts will be reviewed by NassibThese scripts are being automated by the hosting team to run every Saturday at 9AM ET going forward starting 5/17/25.

This query replaces the requester_id value with NULL on the request__t__ table if the the request row is historical:

...

This query replaces barcode, firstName, lastName, and middleName fields inside the requester object with NULL and replaces the requesterId value with NULL if request status is Closed or request row is historical:

UPDATE folio_circulation.request__
SET jsonb = jsonb_set(
               jsonb_set(
               jsonb_set(
               jsonb_set(
               jsonb_set(
               jsonb, '{requester,barcode}', 'null'::jsonb, false),
                 '{requester,firstName}', 'null'::jsonb, false),
                 '{requester,lastName}', 'null'::jsonb, false),
                 '{requester,middleName}', 'null'::jsonb, false),
                 '{requesterId}', 'null'::jsonb, false)
                
WHERE 
(jsonb#>>'{status}' LIKE 'Closed%' OR WHERE request__.__current = false)
;

Fines and Fees

-Sharon, Joanne, Ann, Tom, Susan Bristol, and Michelle to review what is needed for purging patron data here

...

If there is a concern that just the data sitting there is a problem, support support for more granular user permisions does not help to address the issue.

What do you need from us for a feature to turn off history

...