I’ve been thinking about the PolarisTransactions database a lot recently, what it tracks and more specifically what it doesn’t track. There have been forum posts and discussions on the Discord along the lines of “I wish it tracked X,” or “It’d be nice if I could look up Z.” Most recently, @kleblanc (Katie LeBlanc) discovered that the 7100 TransactionTypeID (Phone Attendant Call Received) doesn’t log the PatronID. (I mean… what?)
Another example of something we’ve asked for, for the longest time, is a way to track who accessed/opened a patron registration or patron status. Why? Because there have been problems in the past with library staff improperly accessing information within Polaris. That, and some municipal governments want such functionality for oversight and safety purposes.
So I wanted to start a thread of requests for PolarisTransaction improvements. Rather than inundating the Idea Exchange with lots of requests, I figure it might be a good to send one, with all the things we want. If you have an enhancement idea for better or more thorough tracking through PolarisTransactions, could you please drop it below? Once we get a few together, I’ll submit all of them, with your name in the body of the idea to tie it to you and your library. If you want to stress the importance of a given idea below, do it. I’ll note that in the submission. Even if we only get five ideas, at least Innovative and the Polaris developers will know this is something we want, and in some cases, we might actually need it.
I’ll get started with a few. Feel free to join in!
Add the PatronID TransactionSubtypeID to the 7100 TransactionTypeID details
Track staff accessing and opening patron registrations and statuses
Track patron account renewals
Track SIP based logins
Track API based logins
Track patron password changes
To clarify that last one - I don’t want to know what the password was before or after, but goddess above I’d like to know if/when a patron’s password was changed.
In addition to tracking access and open of patron records, also track edits - including what field was edited. Even better if there is an option to track what the field edit actually was (option because of the privacy implications).
In addition to tracking patron password changes, I want to know who changed the password: the patron or a staff member and, if staff, who it was.
Track staff edits to item, bib, order, etc. records down to the field level. Including the option to track what the field edit actually was (option because of potential file sizes).
If I could only pick one thing, it would be those that deal with the patron record.
Here are a few more for consideration
This is currently incompletely logged in another table, but somewhere we’d like to see
A complete audit log of any admin changes that were made to the system.
These next ones may have changes since we originally submitted them, but I doubt it
Changing something to a missing status
When an item goes from In-Transit → In
When an item goes from In-Transit → In-Transit
Call number isn’t included in the CKO transaction (6001)
Deleted holds aren’t logged
Reactivating a hold doesn’t log anything
Handling situations when a code description changes:
This example more points to the fact that the TransactionDatabase is just a flawed way to store historical information:
Say you go from Collection Code ID 3 = Adult Books in 2022 to Collection Code ID 3 = Kids Books in 2023.
Because ONLY the collection code ID value of #3 is stored in the transaction details, now anytime you run a report, it is going to show ALL the stats as being for Kids Books
Whereas the stats for 2022 should have been for Adult Books because those transactions happened before the code description rename.
Or at least the sys admin UI should really warn you of this when you change the description of a code.
One of our consortium’s member libraries is going to switch to anonymizing patron checkout data a couple months after the item is checked in. This is done by a SQL job that replaces the patron ID (transaction subtype 6) with a 0 in the Transaction Details table. This means that we will no longer be able to link to the patron record for details that aren’t preserved in other lines of the Transaction Details. We’ve created a way to preserve what we need before the anonymization happens, but it would be great if the Transactions database included the following data for each circ transaction:
patron’s birth date
patron’s delivery option ID
patron’s original registration date
all UDFs from the patron’s record
patron’s city and county from their address (the postal code is recorded, but sometimes these cross multiple cities or even counties)
BTW - I’m sure Wes is aware, but in case anyone else isn’t, changes in an item status that aren’t captured in the Transactions database are preserved in the ItemRecordHistory table of the main Polaris DB, but only for the time period you specify in SA.
Aside from some of the other great ideas here, I think tracking Billed-to-Lost cases in the transactions would be helpful. The “Item claimed lost” (ID 6026) only tracks manually claimed items, if I remember correctly. Was an Idea Lab post that didn’t finish before the site closed down.
I also wonder if some of the more granular data suggested (like tracking specific patron field changes) would add enough rows to the detail tables that it makes querying more taxing? I’m curious if others share this concern. I wonder if it would be helpful to track these on a separate table, and/or have admin settings where we could configure which fields we want to capture?
I think the penalty would probably be inserting the row data rather than reading that data. That being said, this type of data (logging) is not well suited to SQL in general as you can tell by the complexity of the queries that are needed to find data out of this system. In fact, due to its complexity, there is even a whole book on the subject: Bricks Without Clay - The Book - Bricks Without Clay
The whole thing should likely to rearchitected into some sort of NoSQL / Key Value Pair / Elasticsearch database. However, what is more likely is that pieces of this get peeled away from the core ILS and get put into some sort of Vega analytics “data lake” that requires a cloud subscription.