Bug 37901 - Add ILL pseudonymization
Summary: Add ILL pseudonymization
Status: Needs Signoff
Alias: None
Product: Koha
Classification: Unclassified
Component: ILL (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement
Assignee: Pedro Amorim
QA Contact: Testopia
URL:
Keywords:
Depends on: 39652
Blocks:
  Show dependency treegraph
 
Reported: 2024-09-12 12:18 UTC by Pedro Amorim
Modified: 2025-04-16 15:56 UTC (History)
9 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:


Attachments
Bug 37901: Add ILL pseudonymization (3.27 KB, patch)
2025-02-19 15:15 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Preparation: [DONT PUSH] automated dbic (5.82 KB, patch)
2025-03-05 12:53 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Preparation: db changes (2.46 KB, patch)
2025-03-05 12:53 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Preparation: atomic update (2.72 KB, patch)
2025-03-05 12:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Preparation: Update borrower attribute types logic (1.16 KB, patch)
2025-03-05 12:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Preparation: Update tests (2.27 KB, patch)
2025-03-05 12:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Add 'illrequest_id' column to statistics (1.63 KB, patch)
2025-03-05 12:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: [DONT PUSH] dbic (1.54 KB, patch)
2025-03-05 12:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics (1.03 KB, patch)
2025-03-05 12:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Consider illrequest_id when creating (2.76 KB, patch)
2025-03-05 12:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Update Statistic.pm (2.97 KB, patch)
2025-03-05 12:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: UpdateStats when new ILL request is created (1.31 KB, patch)
2025-03-05 12:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Add updatedatabase information (1.97 KB, patch)
2025-03-05 12:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Add Tests (4.98 KB, patch)
2025-03-05 12:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Preparation: [DONT PUSH] automated dbic (5.82 KB, patch)
2025-03-05 15:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Preparation: db changes (2.46 KB, patch)
2025-03-05 15:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Preparation: atomic update (2.72 KB, patch)
2025-03-05 15:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Preparation: Update borrower attribute types logic (1.16 KB, patch)
2025-03-05 15:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Preparation: Update tests (2.27 KB, patch)
2025-03-05 15:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Add 'illrequest_id' column to statistics (1.63 KB, patch)
2025-03-05 15:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: [DONT PUSH] dbic (1.54 KB, patch)
2025-03-05 15:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics (1.03 KB, patch)
2025-03-05 15:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Consider illrequest_id when creating (2.76 KB, patch)
2025-03-05 15:54 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Update Statistic.pm (2.97 KB, patch)
2025-03-05 15:55 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: UpdateStats after new ILL request is created (1.69 KB, patch)
2025-03-05 15:55 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Add updatedatabase information (1.97 KB, patch)
2025-03-05 15:55 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Add Tests (4.98 KB, patch)
2025-03-05 15:55 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Preparation: [DONT PUSH] automated dbic (5.82 KB, patch)
2025-04-16 15:43 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Preparation: db changes (2.46 KB, patch)
2025-04-16 15:43 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Preparation: atomic update (2.72 KB, patch)
2025-04-16 15:43 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Preparation: Update borrower attribute types logic (1.16 KB, patch)
2025-04-16 15:43 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Preparation: Update tests (2.47 KB, patch)
2025-04-16 15:43 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Add 'illrequest_id' column to statistics (1.63 KB, patch)
2025-04-16 15:43 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: [DONT PUSH] dbic (1.54 KB, patch)
2025-04-16 15:43 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics (1.03 KB, patch)
2025-04-16 15:43 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Consider illrequest_id when creating or completing (2.18 KB, patch)
2025-04-16 15:43 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Update Statistic.pm (3.00 KB, patch)
2025-04-16 15:44 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: UpdateStats after new ILL request is created or completed (2.62 KB, patch)
2025-04-16 15:44 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Add updatedatabase information (1.97 KB, patch)
2025-04-16 15:44 UTC, Pedro Amorim
Details | Diff | Splinter Review
Bug 37901: Add Tests (5.11 KB, patch)
2025-04-16 15:44 UTC, Pedro Amorim
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Pedro Amorim 2024-09-12 12:18:21 UTC
The pseudonymization process aims to duplicate the transactional circulation data from the statistics table in a way that does not allow borrowers to be identified.  The statistics table also includes entries for payments and writeoffs that contain the borrowernumber of the impacted patron. We should also record ILL requests in pseudonymized_transactions when pseudonymization is enabled.
Comment 1 Pedro Amorim 2024-09-12 12:24:11 UTC

*** This bug has been marked as a duplicate of bug 22513 ***
Comment 2 Pedro Amorim 2025-02-19 15:15:34 UTC Comment hidden (obsolete)
Comment 3 Pedro Amorim 2025-03-05 12:53:55 UTC Comment hidden (obsolete)
Comment 4 Pedro Amorim 2025-03-05 12:53:58 UTC Comment hidden (obsolete)
Comment 5 Pedro Amorim 2025-03-05 12:54:02 UTC Comment hidden (obsolete)
Comment 6 Pedro Amorim 2025-03-05 12:54:05 UTC Comment hidden (obsolete)
Comment 7 Pedro Amorim 2025-03-05 12:54:08 UTC Comment hidden (obsolete)
Comment 8 Pedro Amorim 2025-03-05 12:54:11 UTC Comment hidden (obsolete)
Comment 9 Pedro Amorim 2025-03-05 12:54:14 UTC Comment hidden (obsolete)
Comment 10 Pedro Amorim 2025-03-05 12:54:17 UTC Comment hidden (obsolete)
Comment 11 Pedro Amorim 2025-03-05 12:54:21 UTC Comment hidden (obsolete)
Comment 12 Pedro Amorim 2025-03-05 12:54:24 UTC Comment hidden (obsolete)
Comment 13 Pedro Amorim 2025-03-05 12:54:27 UTC Comment hidden (obsolete)
Comment 14 Pedro Amorim 2025-03-05 12:54:30 UTC Comment hidden (obsolete)
Comment 15 Pedro Amorim 2025-03-05 12:54:33 UTC Comment hidden (obsolete)
Comment 16 Pedro Amorim 2025-03-05 15:54:37 UTC
Created attachment 178980 [details] [review]
Bug 37901: Preparation: [DONT PUSH] automated dbic
Comment 17 Pedro Amorim 2025-03-05 15:54:39 UTC
Created attachment 178981 [details] [review]
Bug 37901: Preparation: db changes
Comment 18 Pedro Amorim 2025-03-05 15:54:42 UTC
Created attachment 178982 [details] [review]
Bug 37901: Preparation: atomic update

Rename the table pseudonymized_borrower_attributes to pseudonymized_metadata_values
This is so that pseudonymizing other 'extended_attributes' type of data,
such as illrequestsattributes, becomes possible.
Comment 19 Pedro Amorim 2025-03-05 15:54:45 UTC
Created attachment 178983 [details] [review]
Bug 37901: Preparation: Update borrower attribute types logic

This now conforms to the new pseudonymized_metadata_values instead of pseudonymized_borrower_attributes
Comment 20 Pedro Amorim 2025-03-05 15:54:48 UTC
Created attachment 178984 [details] [review]
Bug 37901: Preparation: Update tests
Comment 21 Pedro Amorim 2025-03-05 15:54:51 UTC
Created attachment 178985 [details] [review]
Bug 37901: Add 'illrequest_id' column to statistics
Comment 22 Pedro Amorim 2025-03-05 15:54:53 UTC
Created attachment 178986 [details] [review]
Bug 37901: [DONT PUSH] dbic
Comment 23 Pedro Amorim 2025-03-05 15:54:56 UTC
Created attachment 178987 [details] [review]
Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics
Comment 24 Pedro Amorim 2025-03-05 15:54:59 UTC
Created attachment 178988 [details] [review]
Bug 37901: Consider illrequest_id when creating

When a new PseudonymizedTransaction is created, consider the presence of illrequest_id and act on it accordingly.
Comment 25 Pedro Amorim 2025-03-05 15:55:02 UTC
Created attachment 178989 [details] [review]
Bug 37901: Update Statistic.pm

Consider 'ill' as a category and 'ill_request' as an allowed type
Comment 26 Pedro Amorim 2025-03-05 15:55:05 UTC
Created attachment 178990 [details] [review]
Bug 37901: UpdateStats after new ILL request is created
Comment 27 Pedro Amorim 2025-03-05 15:55:07 UTC
Created attachment 178991 [details] [review]
Bug 37901: Add updatedatabase information

This warning will display if there are existing reports that contain 'pseudonymized_borrower_attributes', informing that these reports need to be updated
Comment 28 Pedro Amorim 2025-03-05 15:55:10 UTC
Created attachment 178992 [details] [review]
Bug 37901: Add Tests

Example reports that can be written for ILL pseudonymized data:

--Total of requests of a given request type (article)--

SELECT
  COUNT(*) AS count
FROM
  pseudonymized_transactions pt
WHERE
  EXISTS (
    SELECT 1
    FROM pseudonymized_metadata_values pmv
    WHERE pmv.transaction_id = pt.id
    AND pmv.key = 'type'
    AND pmv.value = 'article'
  );

--Add backend info to listing--

SELECT
  pt.*,
  (SELECT pmv.value FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'type') AS type,
  (SELECT pmv.value FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'backend') AS backend
FROM
  pseudonymized_transactions pt;

--List all pseudonymized transactions and respective metadata values--

SELECT
  pt.*,
  table1.metadata AS borrower_attributes,
  table2.metadata AS illrequestattributes
FROM
  pseudonymized_transactions pt
  LEFT JOIN (
    SELECT
      transaction_id,
      GROUP_CONCAT(DISTINCT CONCAT(`key`, ':', value)) AS metadata
    FROM
      pseudonymized_metadata_values
    WHERE tablename = 'borrower_attributes'
    GROUP BY
      transaction_id
  ) table1 ON pt.id = table1.transaction_id
  LEFT JOIN (
    SELECT
      transaction_id,
      GROUP_CONCAT(DISTINCT CONCAT(`key`, ':', value)) AS metadata
    FROM
      pseudonymized_metadata_values
    WHERE tablename = 'illrequestattributes'
    GROUP BY
      transaction_id
  ) table2 ON pt.id = table2.transaction_id;

---

Test plan:
1) Add a <bcrypt_settings> under <config> in /etc/koha/sites/kohadev/koha.conf.xml:
  $ <bcrypt_settings>$2a$08$9lmorEKnwQloheaCLFIfje</bcrypt_settings> <!-- taken from Pseudonymization.t -->
2) Restart plack & worker:
$ koha-plack --restart kohadev && sudo koha-worker --restart kohadev
3) Enable ILLModule and Pseudonymization system preferences
4) Check 'Keep for pseudonymization' at the only existing patron attribute type:
http://localhost:8081/cgi-bin/koha/admin/patron-attr-types.pl?op=edit_attribute_type&code=SHOW_BCODE
5) Save. Edit that attribute type for 'koha' patron:
http://localhost:8081/cgi-bin/koha/members/memberentry.pl?op=edit_form&destination=circ&borrowernumber=51
6) Scroll down, set a value for 'Show barcode on the summary screen items listings:'. Save.
7) Do a checkout for that 'koha' patron.
8) Confirm the pseudonymized_transaction has been added alongside its metadata (SHOW_BCODE), run report #3 from the examples listed above.
This concludes the 'preparation' side of testing, ensuring that borrower_attributes are kept for pseudonymization
9) Create a new ILL request of type 'book'.
10) Run the report from 8) again. Verify the ILL pseudonymized transaction and its related pseudonymized metadata is there.

Run tests:

prove t/db_dependent/Koha/BackgroundJob/PseudonymizeStatistic.t
prove t/db_dependent/Koha/PseudonymizedTransaction.t
prove t/db_dependent/Koha/Pseudonymization.t
Comment 29 Caroline Cyr La Rose 2025-03-31 14:39:23 UTC
Hi Pedro,

Is the test missing a step between 9 and 10? I created the ILL request for patron 42 and I still only have one entry (the one for the checkout) in the result for the report. Was I supposed to do something other than simply create the request?

Also, the name of the file in step 1 is koha-conf.xml, not koha.conf.xml, if you want to correct it in the test plan.

Thanks!
Caroline
Comment 30 Pedro Amorim 2025-04-16 15:43:32 UTC
Created attachment 181008 [details] [review]
Bug 37901: Preparation: [DONT PUSH] automated dbic
Comment 31 Pedro Amorim 2025-04-16 15:43:36 UTC
Created attachment 181009 [details] [review]
Bug 37901: Preparation: db changes
Comment 32 Pedro Amorim 2025-04-16 15:43:39 UTC
Created attachment 181010 [details] [review]
Bug 37901: Preparation: atomic update

Rename the table pseudonymized_borrower_attributes to pseudonymized_metadata_values
This is so that pseudonymizing other 'extended_attributes' type of data,
such as illrequestsattributes, becomes possible.
Comment 33 Pedro Amorim 2025-04-16 15:43:42 UTC
Created attachment 181011 [details] [review]
Bug 37901: Preparation: Update borrower attribute types logic

This now conforms to the new pseudonymized_metadata_values instead of pseudonymized_borrower_attributes
Comment 34 Pedro Amorim 2025-04-16 15:43:45 UTC
Created attachment 181012 [details] [review]
Bug 37901: Preparation: Update tests
Comment 35 Pedro Amorim 2025-04-16 15:43:48 UTC
Created attachment 181013 [details] [review]
Bug 37901: Add 'illrequest_id' column to statistics
Comment 36 Pedro Amorim 2025-04-16 15:43:51 UTC
Created attachment 181014 [details] [review]
Bug 37901: [DONT PUSH] dbic
Comment 37 Pedro Amorim 2025-04-16 15:43:55 UTC
Created attachment 181015 [details] [review]
Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics
Comment 38 Pedro Amorim 2025-04-16 15:43:59 UTC
Created attachment 181016 [details] [review]
Bug 37901: Consider illrequest_id when creating or completing

When a new PseudonymizedTransaction is created, consider the presence of illrequest_id and act on it accordingly.
Comment 39 Pedro Amorim 2025-04-16 15:44:02 UTC
Created attachment 181017 [details] [review]
Bug 37901: Update Statistic.pm

Consider 'ill' as a category and 'illreq_created' and 'illreq_comp'  as allowed types
Comment 40 Pedro Amorim 2025-04-16 15:44:05 UTC
Created attachment 181018 [details] [review]
Bug 37901: UpdateStats after new ILL request is created or completed
Comment 41 Pedro Amorim 2025-04-16 15:44:09 UTC
Created attachment 181019 [details] [review]
Bug 37901: Add updatedatabase information

This warning will display if there are existing reports that contain 'pseudonymized_borrower_attributes', informing that these reports need to be updated
Comment 42 Pedro Amorim 2025-04-16 15:44:13 UTC
Created attachment 181020 [details] [review]
Bug 37901: Add Tests

Example reports that can be written for ILL pseudonymized data:

--Total of requests of a given request type (article)--

SELECT
  COUNT(*) AS count
FROM
  pseudonymized_transactions pt
WHERE
  EXISTS (
    SELECT 1
    FROM pseudonymized_metadata_values pmv
    WHERE pmv.transaction_id = pt.id
    AND pmv.key = 'type'
    AND pmv.value = 'article'
  );

--Add backend info to listing--

SELECT
  pt.*,
  (SELECT pmv.value FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'type') AS type,
  (SELECT pmv.value FROM pseudonymized_metadata_values pmv WHERE pmv.transaction_id = pt.id AND pmv.key = 'backend') AS backend
FROM
  pseudonymized_transactions pt;

--List all pseudonymized transactions and respective metadata values--

SELECT
  pt.*,
  table1.metadata AS borrower_attributes,
  table2.metadata AS illrequestattributes
FROM
  pseudonymized_transactions pt
  LEFT JOIN (
    SELECT
      transaction_id,
      GROUP_CONCAT(DISTINCT CONCAT(`key`, ':', value)) AS metadata
    FROM
      pseudonymized_metadata_values
    WHERE tablename = 'borrower_attributes'
    GROUP BY
      transaction_id
  ) table1 ON pt.id = table1.transaction_id
  LEFT JOIN (
    SELECT
      transaction_id,
      GROUP_CONCAT(DISTINCT CONCAT(`key`, ':', value)) AS metadata
    FROM
      pseudonymized_metadata_values
    WHERE tablename = 'illrequestattributes'
    GROUP BY
      transaction_id
  ) table2 ON pt.id = table2.transaction_id;

---

Test plan:
1) Add a <bcrypt_settings> under <config> in /etc/koha/sites/kohadev/koha.conf.xml:
  $ <bcrypt_settings>$2a$08$9lmorEKnwQloheaCLFIfje</bcrypt_settings> <!-- taken from Pseudonymization.t -->
2) Restart plack & worker:
$ koha-plack --restart kohadev && sudo koha-worker --restart kohadev
3) Enable ILLModule and Pseudonymization system preferences
4) Check 'Keep for pseudonymization' at the only existing patron attribute type:
http://localhost:8081/cgi-bin/koha/admin/patron-attr-types.pl?op=edit_attribute_type&code=SHOW_BCODE
5) Save. Edit that attribute type for 'koha' patron:
http://localhost:8081/cgi-bin/koha/members/memberentry.pl?op=edit_form&destination=circ&borrowernumber=51
6) Scroll down, set a value for 'Show barcode on the summary screen items listings:'. Save.
7) Do a checkout for that 'koha' patron.
8) Confirm the pseudonymized_transaction has been added alongside its metadata (SHOW_BCODE), run report #3 from the examples listed above.
This concludes the 'preparation' side of testing, ensuring that borrower_attributes are kept for pseudonymization
9) Create a new ILL request of type 'book'.
10) Run the report from 8) again. Verify the ILL pseudonymized transaction and its related pseudonymized metadata is there.
11) Go back to that ILL request and click 'Confirm request'. After that click 'Mark completed'.
12) Repeat 10)

Run tests:

prove t/db_dependent/Koha/BackgroundJob/PseudonymizeStatistic.t
prove t/db_dependent/Koha/PseudonymizedTransaction.t
prove t/db_dependent/Koha/Pseudonymization.t

Sponsored-by: UKHSA - UK Health Security Agency