-what are the concerns related to patron identifiers in metadb?
-what problem will removing patron identfiers solve?
-does restricting user access to metadb help, or is the issue that the data resides in the database regardless of user access?
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.
(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 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.
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.
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.
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):
-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
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
| Step | Status | |
|---|---|---|
| 1 | As specified in the Metadb documentation, the hosting team will stop the server to prevent it from operating with an out-of-date cache. | |
| 2 | The hosting team will run the 5 anonymization scripts on the loan and requester tables in the Metadb reporting database. | |
| 3 | As specified in the Metadb documentation, the hosting team will restart the server | |
| 4 | The hosting team will inform Sharon when the Steps 2 and 3 are complete. | |
| 5 | Sharon and Joanne will verify that the loan anonymization process has been successful and that normal data synchronization is continuing through test scripts that capture data stamps in loan_id rows. |
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.
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
;-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
-need to determine whether or not BD and ILL requests need to be considered differently
-fields with patron identifiers:
request__ : requester_id, as well as the object (requester) barcode, lastname, firstname, middle name
request__t__ : requester_id has values
request__t : requester_id has values
These scripts will be reviewed by Nassib.
This query replaces the requester_id value with NULL on the request__t__ table if the request row is historical:
UPDATE folio_circulation.request__t__
SET requester_id = NULL
WHERE request__t__.__current = false
;
This query replaces barcode, firstName, lastName, and middleName fields inside the requester object with NULL and replaces the requesterId value with NULL if 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 request__.__current = false
;
-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
-Michelle gets report that show lost book charges never closed and needs to contact the patron, home address is no longer in FOLIO, so we now have a feed to get the addresses from CIT
-Michelle gets report on NYC Cornell Tech users - need to know more about why this is needed (requests for delivery of materials to Cornell Tech?)
-Next steps:
Patron Notices
-need to review tables that keep patron data in this area
-folio_email... etc.
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?
The backups are incremental, so taking a secondary snapshot backup is not needed.
How long can the backups be saved?
The backups are saved for 7 days.
History
have a clear idea of what we need to do, then get in touch with Nassib
Anonymization
It would
see data privacy documentation on LDP, you can see how anonymization works, it allows you to specify columns and tables that should be anonymized; data are never written to the database
Anonymization in Metadb will probably be similar, but not absolutely sure
1 - LDP + keeping an eye on new columns so specification as to what should be anonymized continues to be
2 - You allow data to come in, but you remove a person's specific data when they request it
(3) (Tom)Following anonymization processes in FOLIO
Anonymization should be done as part of the stream processing, so it never gets to the Metadb database
Anonymization will probably be similar to LDP anonymization
If FOLIO anonymizes a field, it will be anonymized
If we can make sure that FOLIO never writes the data to its database, it will not come over to Metadb
Not sure because it captures historical data; probably independent because metadb used beyond FOLIO
User Permissions
If there is a concern that just the data sitting there is a problem, 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
-we need to remove the data from the database, so historical rows keep us from keeping our promise with patrons
-is turning history off going to meet Cornell's needs?
-turning off history is not something that could be done right now (Nassib); he is not sure how involved it would be
Table Creation
Other
Risks
1st level risk - you may render the historical data unusable for queries you want to do, make it difficult to join
2nd level risk - could interfere with Metadb's ability to update the data
can send nassib queries to review each time
stay away from
in the tables, __id is the primary key; underlying data from FOLIO has its own primary key
primary key is a constraint, key cannot be null, must be unique; unique values are keys; primary key is a choice of one of those keys
candidate key, primary key - primary key is concretely a constraint; never nullify the primary keys
candidate key is the folio key, ID, this column should not be changed because metadb is using what folio thinks is the primary key to version the records
the database will not let you nullify a primary key, but the candidate key can be nullified (and should not be)
candidate keys, id keys are used for versioning
any column that might be a key in FOLIO should not be nullified
Pitfalls with NULLS
-in db theory, NULL = NULL is false , so you have to be careful if you do an equijoin on 2 columns being equal, cartisian product filtered by a condition, can get unexpected results if we join on NULL
Metadb Links
Metadb Project: https://github.com/metadb-project/metadb
Metadb Documentaton: https://metadb.dev/doc/