CUL Requirements for Access to Metadb Data

Problem Statement

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, patron identifiers on historical transaction records should be replaced with NULL values in the Metadb reporting database.

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. 

-- 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. 

• Reporting databases involve complex relationships between tables, and removing records without considering these connections can disrupt referential integrity. For example, if a deleted record is referenced elsewhere, it may leave orphaned data or trigger unintended cascading deletions. This compromises the accuracy and trustworthiness of the data.
• Reporting databases also commonly store historical, audit, or compliance-related information. Unintended deletions could permanently remove critical data needed for analysis, regulatory compliance, or business intelligence. This can affect decision-making processes and may even expose the organization to legal risks. Additionally, ETL pipelines that feed or consume the reporting data could break or propagate errors downstream if the source data unexpectedly changes.
• Reports and dashboards that rely on specific datasets might stop working or produce inaccurate results. In addition, deletions can interfere with database performance, especially if the system isn't properly maintained afterward to remove leftover data fragments. If triggers or materialized views are tied to the deleted data, they might behave unpredictably or become outdated. Finally, deletions without proper logging or oversight may raise security concerns and create audit gaps.

It’s crucial to approach deletions cautiously—starting with a deep analysis of the schema, understanding dependencies, and always backing up the data. Deletions should ideally be tested in non-production environments to prevent irreversible mistakes.

Tables in Metadb with Patron Identifiers

For reporting, folio_circulation, folio_audit, folio_feesfines, folio_derived, folio_email, folio_orders, and folio_patron_blocks have lots of tables and transformed tables that contain user_ids, requester IDs, recipient IDs or free text indicators of users associated with specific loans, requests, circ “events” or library materials. Most of the tables are very complex in their density of data and links to other data. Here is a list of table sets preliminarily identified – this list may not be complete (a ‘table set’ is the full table, plus the various extracted tables with current and/or archive states):

• Folio_audit.circulation_logs
• Folio_circulation.audit_loan
• Folio_circulation.check_in (can be linked by item_id and date/time to audit_loan table, which has user_id)
• Folio_circulation.patron_action_session
• Folio_circulation.loan
• Folio_circulation.request
• Folio_circulation.scheduled_notice
• Folio_email.email_statistics
• Folio_feesfines.accounts
• Folio_feesfines.feefineactions
• Folio_orders.po_line__
• Folio_patron_blocks.fee_fine_balance_changed_event
• Folio_patron_blocks.item_aged_to_lost_event
• Folio_patron_blocks.item_checked_in_event
• Folio_patron_blocks.item_checked_out_event
• Folio_patron_blocks.item_claimed_return_event
• Folio_patron_blocks.item_declared_lost_event
• Folio_patron_blocks.loan_closed_event
• Folio_patron_blocks.loan_due_date_changed_event
• Folio_patron_blocks.user_summary

Historic Loan Rows

Current Loans are already purged of patron data, but Historical Loans are not

-need to research connection between loans and fine/fee transactions

-fines/fees need to be kept with loan data

-a job runs on a schedule to delete the user id on loans checked in (discharged) on the loan__t table. The user id is reset to NULL, which anonymizes the data. It also removes the user id in the circulation audit table.

-Loan anonymization is configured in FOLIO here:
https://cornell.folio.ebsco.com/settings/circulation/loan-anonymization

-folio_circulation.loans_t_ The FOLIO process does not remove user id on historical loan rows; we just need to replace user_id with NULL on all historical rows

-user patron group is retained and is used for reports about collection usage; no sensitve data is included in those reports

-script will be run manually initially, then library systems will run it as a chron job on Jenkins

-good to ask Alexander Kurash, lead developer for loans

-What about turning off historical data for loans

-our anonymization should be equal to FOLIO's anonymization

-need to determine whether or not removing userid from historical loan rows would negatively impact fine/fee data

-loan id stays the same throughout all loan trans

-need add replacing user_id with NULL in  audit_loan__ table 

-Sharon to ask Hosting Team if they can run the job to stop the server, run the purge scripts, then restart the server on the weekend

Historic Loan Rows Anonymization Process

Still to do: determine the time the weekly process will be run. We have asked for weekends, but the hosting team would prefer during the week. Sharon has asked that we avoid mornings because there are many automated reporting processes that use the Metadb database from 3am to 8am.

Here are the details of the process that was followed to replace the user_id field on historic loan rows with NULL on Friday, May 9

Loan Anonymization Scripts

This script replaces the user_id values with NULL in historic loan rows on the loan__ table:

UPDATE folio_circulation.loan__ 
SET jsonb = jsonb_set(jsonb, '{userId}', 'null'::jsonb, false) 
WHERE __current = FALSE 
;

This script replaces the user_id value with NULL in historic loan rows on the loan__t__ table:

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

Current and Historical Request data to be purged

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

-Request anonymization cannot currently be configured in FOLIO Settings (as current loans can) 

-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 rows

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

-fields with patron identifiers:

request__ : requester_id,  (requester) barcode, (requester) lastname, firstname ; are the additional fields needed to make a request for users who do not have a user id? (ask Tom)

request__t__ : requester_id has values

request__t : requester_id has values

Requester Anonymization Scripts

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

UPDATE local_protected.test_request__t__
SET requester_id = NULL
WHERE status LIKE 'Closed%'
OR test_request__t__.__current = false
;

This query replaces the requester_id value with NULL on the request__t table if the request status is Closed:

UPDATE local_protected.test_request__t
SET requester_id = NULL
WHERE status LIKE 'Closed%'
;

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 local_protected.test_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 __current = false)
;

Fines and Fees

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

-(from Ken via Simeon): If we choose to forgive a fine or fee (without any collection attempt) then there is no financial record, and we are not subject to any retention requirement. The “without collection attempt” means that if we sent it to the bursar, even if we later forgave it, we should keep the record.

When a bill is sent to collections, what status is it given in FOLIO. This is data that we need to keep. We need to ask Michelle about this. 

-need to determine what we need to anonymize

-Tom plans to put in a ticket to request patron identifiers be anonymized in fines and fees

-need to 

Patron Notices

-need to review tables that keep patron data in this area

-folio_email... etc.

Data Anonymization Processes on the Metadb Database

Backups to Metadb Database

How often are backups made, how are they made, are the historical data backups overwritten with each new backup, are backups incremental or full?

Backups are incremental and are made on a rolling basis - they are made by the AWS Managed Service that the hosting team uses for Postgres databases. 

Is it possible to keep the backup taken right before the anonymization process is run?

YES. This has been requested as part of the historic loan data anonymization procedure.

How long can the backups be saved?

The backups can be saved for 7 days.

Questions

Questions for Nassib

Anonymization

• Given increasing concerns about data privacy, is data anonymization on the metadb software development roadmap? If so, when do you expect it to be released?

• There is some loan anonymization functionality in FOLIO settings and there are plans to do the same for Requests
  • Is metadb anonymization being developed with that interdependence in mind, or is it going to be an independent feature?
• Will there be a feature to make patron identifiers (e.g., user_id, requester_id) NULL in historical rows?
• If we need to recover from our data anonymization processes, what will be required and what might we lose?

User Permissions

• Support for more granular permissions has been added in Metadb 1.4: see https://gist.github.com/nassibnassar/9e8e63383db1dac0634b0cf371254dec
• Are there ways that this new functionality can support our goal of removing access to patron identifiers in our Metadb reporting database? 
• Does this mean we can use postgres permissions, groups, etc.?
• Would it be possible to develop user permission functionality for Metadb that corresponds to user permissions in FOLIO?

Table Creation

• When we look at the sets of 3 tables in each application area (e.g., loan__ , loan__t , and loan__t__), we get different totals when we count the id rows. Why would this be? Is this happening because just first level of json fields are extracted into the __t__ table?
• Is there a relationship among tables during the ETL process? Specifically, does it work like this, or another way?
  • main table (loan__) the json source
  • current and historical table (loan__t__) is created from the main table  
  • current table (loan__t) is made from the current and historical table (loan__t__)?

History

• Will there be a feature to turn off the historical row creation for Metadb (as you could for LDP)?

Other

• What is in the zzz_ tables in Metadb?  e.g., zzz___loan__t___
• Should we anonymize the zzz_tables, and if so, how would we do that?

Metadb Links

Metadb Project: https://github.com/metadb-project/metadb

Metadb Documentaton: https://metadb.dev/doc/