Bug 37901

Summary: Add ILL pseudonymization
Product: Koha Reporter: Pedro Amorim <pedro.amorim>
Component: ILLAssignee: Pedro Amorim <pedro.amorim>
Status: Pushed to main --- QA Contact: Lisette Scheer <lisette>
Severity: enhancement    
Priority: P5 - low CC: andrew.auld, caroline.cyr-la-rose, edith.speller, jeremy.evans, lisette, lucas, magnus, martin.renvoize, pedro.amorim, tomascohen, victor
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=32630
GIT URL: Change sponsored?: ---
Patch complexity: Small patch Documentation contact:
Documentation submission: Text to go in the release notes:
This enhancement adds 2 new types of statistics related to Interlibrary loans: ILL request created, and ILL request completed. Additionally, if Pseudonymization is enabled, pseudonymized transactions will be created from these statistics. Technical changes: - Database table 'pseudonymized_borrower_attributes' renamed to 'pseudonymized_metadata_values'. Existing reports that reference this table should be updated accordingly on upgrade. - Adds 'illrequest_id' column to statistics table
Version(s) released in:
25.11.00
Circulation function:
Bug Depends on: 39652    
Bug Blocks: 40725    
Attachments: Bug 37901: Add ILL pseudonymization
Bug 37901: Preparation: [DONT PUSH] automated dbic
Bug 37901: Preparation: db changes
Bug 37901: Preparation: atomic update
Bug 37901: Preparation: Update borrower attribute types logic
Bug 37901: Preparation: Update tests
Bug 37901: Add 'illrequest_id' column to statistics
Bug 37901: [DONT PUSH] dbic
Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics
Bug 37901: Consider illrequest_id when creating
Bug 37901: Update Statistic.pm
Bug 37901: UpdateStats when new ILL request is created
Bug 37901: Add updatedatabase information
Bug 37901: Add Tests
Bug 37901: Preparation: [DONT PUSH] automated dbic
Bug 37901: Preparation: db changes
Bug 37901: Preparation: atomic update
Bug 37901: Preparation: Update borrower attribute types logic
Bug 37901: Preparation: Update tests
Bug 37901: Add 'illrequest_id' column to statistics
Bug 37901: [DONT PUSH] dbic
Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics
Bug 37901: Consider illrequest_id when creating
Bug 37901: Update Statistic.pm
Bug 37901: UpdateStats after new ILL request is created
Bug 37901: Add updatedatabase information
Bug 37901: Add Tests
Bug 37901: Preparation: [DONT PUSH] automated dbic
Bug 37901: Preparation: db changes
Bug 37901: Preparation: atomic update
Bug 37901: Preparation: Update borrower attribute types logic
Bug 37901: Preparation: Update tests
Bug 37901: Add 'illrequest_id' column to statistics
Bug 37901: [DONT PUSH] dbic
Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics
Bug 37901: Consider illrequest_id when creating or completing
Bug 37901: Update Statistic.pm
Bug 37901: UpdateStats after new ILL request is created or completed
Bug 37901: Add updatedatabase information
Bug 37901: Add Tests
Bug 37901: Preparation: [DONT PUSH] automated dbic
Bug 37901: Preparation: db changes
Bug 37901: Preparation: atomic update
Bug 37901: Preparation: Update borrower attribute types logic
Bug 37901: Preparation: Update tests
Bug 37901: Add 'illrequest_id' column to statistics
Bug 37901: [DONT PUSH] dbic
Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics
Bug 37901: Consider illrequest_id when creating or completing
Bug 37901: Update Statistic.pm
Bug 37901: UpdateStats after new ILL request is created or completed
Bug 37901: Add updatedatabase information
Bug 37901: Add Tests
Bug 37901: Preparation: [DONT PUSH] automated dbic
Bug 37901: Preparation: db changes
Bug 37901: Preparation: atomic update
Bug 37901: Preparation: Update borrower attribute types logic
Bug 37901: Preparation: Update tests
Bug 37901: Add 'illrequest_id' column to statistics
Bug 37901: [DONT PUSH] dbic
Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics
Bug 37901: Consider illrequest_id when creating or completing
Bug 37901: Update Statistic.pm
Bug 37901: UpdateStats after new ILL request is created or completed
Bug 37901: Add updatedatabase information
Bug 37901: Add Tests
Bug 37901: Preparation: [DONT PUSH] automated dbic
Bug 37901: Preparation: db changes
Bug 37901: Preparation: atomic update
Bug 37901: Preparation: Update borrower attribute types logic
Bug 37901: Preparation: Update tests
Bug 37901: Add 'illrequest_id' column to statistics
Bug 37901: [DONT PUSH] dbic
Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics
Bug 37901: Consider illrequest_id when creating or completing
Bug 37901: Update Statistic.pm
Bug 37901: UpdateStats after new ILL request is created or completed
Bug 37901: Add updatedatabase information
Bug 37901: Add Tests
Bug 37901: Preparation: [DONT PUSH] automated dbic
Bug 37901: Preparation: db changes
Bug 37901: Preparation: atomic update
Bug 37901: Preparation: Update borrower attribute types logic
Bug 37901: Preparation: Update tests
Bug 37901: Add 'illrequest_id' column to statistics
Bug 37901: [DONT PUSH] dbic
Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics
Bug 37901: Consider illrequest_id when creating or completing
Bug 37901: Update Statistic.pm
Bug 37901: UpdateStats after new ILL request is created or completed
Bug 37901: Add updatedatabase information
Bug 37901: Add Tests
Bug 37901: Fix schema files

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
Comment 43 Pedro Amorim 2025-04-30 08:33:15 UTC
Created attachment 181696 [details] [review]
Bug 37901: Preparation: [DONT PUSH] automated dbic

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 44 Pedro Amorim 2025-04-30 08:33:20 UTC
Created attachment 181697 [details] [review]
Bug 37901: Preparation: db changes

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 45 Pedro Amorim 2025-04-30 08:33:25 UTC
Created attachment 181698 [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.

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 46 Pedro Amorim 2025-04-30 08:33:31 UTC
Created attachment 181699 [details] [review]
Bug 37901: Preparation: Update borrower attribute types logic

This now conforms to the new pseudonymized_metadata_values instead of pseudonymized_borrower_attributes

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 47 Pedro Amorim 2025-04-30 08:33:38 UTC
Created attachment 181700 [details] [review]
Bug 37901: Preparation: Update tests

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 48 Pedro Amorim 2025-04-30 08:33:43 UTC
Created attachment 181701 [details] [review]
Bug 37901: Add 'illrequest_id' column to statistics

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 49 Pedro Amorim 2025-04-30 08:33:51 UTC
Created attachment 181702 [details] [review]
Bug 37901: [DONT PUSH] dbic

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 50 Pedro Amorim 2025-04-30 08:33:57 UTC
Created attachment 181703 [details] [review]
Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 51 Pedro Amorim 2025-04-30 08:34:05 UTC
Created attachment 181704 [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.

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 52 Pedro Amorim 2025-04-30 08:34:10 UTC
Created attachment 181705 [details] [review]
Bug 37901: Update Statistic.pm

Consider 'ill' as a category and 'illreq_created' and 'illreq_comp'  as allowed types

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 53 Pedro Amorim 2025-04-30 08:34:17 UTC
Created attachment 181706 [details] [review]
Bug 37901: UpdateStats after new ILL request is created or completed

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 54 Pedro Amorim 2025-04-30 08:34:23 UTC
Created attachment 181707 [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

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 55 Pedro Amorim 2025-04-30 08:34:30 UTC
Created attachment 181708 [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;

--ILL specific pseudonymized transactions report--

SELECT
  pt.hashed_borrowernumber as 'borrower',pt.transaction_type as 'Transaction type', pt.datetime as 'Datetime', pt.branchcode as 'Patron branchcode', pt.categorycode as 'Patron category',
  (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

Sponsored-by: UKHSA - UK Health Security Agency

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 56 Pedro Amorim 2025-07-28 08:40:25 UTC
Created attachment 184709 [details] [review]
Bug 37901: Preparation: [DONT PUSH] automated dbic

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 57 Pedro Amorim 2025-07-28 08:40:28 UTC
Created attachment 184710 [details] [review]
Bug 37901: Preparation: db changes

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 58 Pedro Amorim 2025-07-28 08:40:31 UTC
Created attachment 184711 [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.

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 59 Pedro Amorim 2025-07-28 08:40:34 UTC
Created attachment 184712 [details] [review]
Bug 37901: Preparation: Update borrower attribute types logic

This now conforms to the new pseudonymized_metadata_values instead of pseudonymized_borrower_attributes

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 60 Pedro Amorim 2025-07-28 08:40:37 UTC
Created attachment 184713 [details] [review]
Bug 37901: Preparation: Update tests

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 61 Pedro Amorim 2025-07-28 08:40:40 UTC
Created attachment 184714 [details] [review]
Bug 37901: Add 'illrequest_id' column to statistics

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 62 Pedro Amorim 2025-07-28 08:40:43 UTC
Created attachment 184715 [details] [review]
Bug 37901: [DONT PUSH] dbic

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 63 Pedro Amorim 2025-07-28 08:40:46 UTC
Created attachment 184716 [details] [review]
Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 64 Pedro Amorim 2025-07-28 08:40:49 UTC
Created attachment 184717 [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.

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 65 Pedro Amorim 2025-07-28 08:40:52 UTC
Created attachment 184718 [details] [review]
Bug 37901: Update Statistic.pm

Consider 'ill' as a category and 'illreq_created' and 'illreq_comp'  as allowed types

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 66 Pedro Amorim 2025-07-28 08:40:55 UTC
Created attachment 184719 [details] [review]
Bug 37901: UpdateStats after new ILL request is created or completed

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 67 Pedro Amorim 2025-07-28 08:40:58 UTC
Created attachment 184720 [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

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 68 Pedro Amorim 2025-07-28 08:41:01 UTC
Created attachment 184721 [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;

--ILL specific pseudonymized transactions report--

SELECT
  pt.hashed_borrowernumber as 'borrower',pt.transaction_type as 'Transaction type', pt.datetime as 'Datetime', pt.branchcode as 'Patron branchcode', pt.categorycode as 'Patron category',
  (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

Sponsored-by: UKHSA - UK Health Security Agency

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 69 Lisette Scheer 2025-08-15 14:39:58 UTC
Patch doesn't apply, but even with rebase:

[SKIP] Koha/Schema/Result/PseudonymizedTransaction.pm
   SKIP   pod_coverage
                No POD exists
[SKIP] Koha/Schema/Result/Statistic.pm
   SKIP   pod_coverage
                No POD exists

Needs POD coverage
Comment 70 Lisette Scheer 2025-08-15 14:40:13 UTC
It did work great!
Comment 71 Pedro Amorim 2025-08-18 11:29:07 UTC
Created attachment 185495 [details] [review]
Bug 37901: Preparation: [DONT PUSH] automated dbic

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 72 Pedro Amorim 2025-08-18 11:29:10 UTC
Created attachment 185496 [details] [review]
Bug 37901: Preparation: db changes

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 73 Pedro Amorim 2025-08-18 11:29:14 UTC
Created attachment 185497 [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.

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 74 Pedro Amorim 2025-08-18 11:29:17 UTC
Created attachment 185498 [details] [review]
Bug 37901: Preparation: Update borrower attribute types logic

This now conforms to the new pseudonymized_metadata_values instead of pseudonymized_borrower_attributes

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 75 Pedro Amorim 2025-08-18 11:29:20 UTC
Created attachment 185499 [details] [review]
Bug 37901: Preparation: Update tests

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 76 Pedro Amorim 2025-08-18 11:29:23 UTC
Created attachment 185500 [details] [review]
Bug 37901: Add 'illrequest_id' column to statistics

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 77 Pedro Amorim 2025-08-18 11:29:26 UTC
Created attachment 185501 [details] [review]
Bug 37901: [DONT PUSH] dbic

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 78 Pedro Amorim 2025-08-18 11:29:30 UTC
Created attachment 185502 [details] [review]
Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 79 Pedro Amorim 2025-08-18 11:29:33 UTC
Created attachment 185503 [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.

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 80 Pedro Amorim 2025-08-18 11:29:36 UTC
Created attachment 185504 [details] [review]
Bug 37901: Update Statistic.pm

Consider 'ill' as a category and 'illreq_created' and 'illreq_comp'  as allowed types

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 81 Pedro Amorim 2025-08-18 11:29:39 UTC
Created attachment 185505 [details] [review]
Bug 37901: UpdateStats after new ILL request is created or completed

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 82 Pedro Amorim 2025-08-18 11:29:42 UTC
Created attachment 185506 [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

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 83 Pedro Amorim 2025-08-18 11:29:45 UTC
Created attachment 185507 [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;

--ILL specific pseudonymized transactions report--

SELECT
  pt.hashed_borrowernumber as 'borrower',pt.transaction_type as 'Transaction type', pt.datetime as 'Datetime', pt.branchcode as 'Patron branchcode', pt.categorycode as 'Patron category',
  (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

Sponsored-by: UKHSA - UK Health Security Agency

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Comment 84 Pedro Amorim 2025-08-18 11:32:45 UTC
(In reply to Lisette Scheer from comment #69)
> Patch doesn't apply, but even with rebase:
> 
> [SKIP] Koha/Schema/Result/PseudonymizedTransaction.pm
>    SKIP   pod_coverage
>                 No POD exists
> [SKIP] Koha/Schema/Result/Statistic.pm
>    SKIP   pod_coverage
>                 No POD exists
> 
> Needs POD coverage

Thank you Lisette, I've rebased the patches.
I believe this 'Needs POD coverage' is a false positive that comes up every time for new Schema files and are safe to ignore though I'm happy to be corrected.
Comment 85 Lisette Scheer 2025-08-19 16:44:05 UTC
Created attachment 185544 [details] [review]
Bug 37901: Preparation: [DONT PUSH] automated dbic

Worked great. You're correct that you don't need the POD coverage. Thanks!

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Signed-off-by: Lisette Scheer <lisette@bywatersolutions.com>
Comment 86 Lisette Scheer 2025-08-19 16:44:08 UTC
Created attachment 185545 [details] [review]
Bug 37901: Preparation: db changes

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Signed-off-by: Lisette Scheer <lisette@bywatersolutions.com>
Comment 87 Lisette Scheer 2025-08-19 16:44:12 UTC
Created attachment 185546 [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.

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Signed-off-by: Lisette Scheer <lisette@bywatersolutions.com>
Comment 88 Lisette Scheer 2025-08-19 16:44:16 UTC
Created attachment 185547 [details] [review]
Bug 37901: Preparation: Update borrower attribute types logic

This now conforms to the new pseudonymized_metadata_values instead of pseudonymized_borrower_attributes

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Signed-off-by: Lisette Scheer <lisette@bywatersolutions.com>
Comment 89 Lisette Scheer 2025-08-19 16:44:19 UTC
Created attachment 185548 [details] [review]
Bug 37901: Preparation: Update tests

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Signed-off-by: Lisette Scheer <lisette@bywatersolutions.com>
Comment 90 Lisette Scheer 2025-08-19 16:44:23 UTC
Created attachment 185549 [details] [review]
Bug 37901: Add 'illrequest_id' column to statistics

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Signed-off-by: Lisette Scheer <lisette@bywatersolutions.com>
Comment 91 Lisette Scheer 2025-08-19 16:44:27 UTC
Created attachment 185550 [details] [review]
Bug 37901: [DONT PUSH] dbic

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Signed-off-by: Lisette Scheer <lisette@bywatersolutions.com>
Comment 92 Lisette Scheer 2025-08-19 16:44:31 UTC
Created attachment 185551 [details] [review]
Bug 37901: atomicupdate: Add 'illrequest_id' column to statistics

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Signed-off-by: Lisette Scheer <lisette@bywatersolutions.com>
Comment 93 Lisette Scheer 2025-08-19 16:44:34 UTC
Created attachment 185552 [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.

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Signed-off-by: Lisette Scheer <lisette@bywatersolutions.com>
Comment 94 Lisette Scheer 2025-08-19 16:44:38 UTC
Created attachment 185553 [details] [review]
Bug 37901: Update Statistic.pm

Consider 'ill' as a category and 'illreq_created' and 'illreq_comp'  as allowed types

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Signed-off-by: Lisette Scheer <lisette@bywatersolutions.com>
Comment 95 Lisette Scheer 2025-08-19 16:44:42 UTC
Created attachment 185554 [details] [review]
Bug 37901: UpdateStats after new ILL request is created or completed

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Signed-off-by: Lisette Scheer <lisette@bywatersolutions.com>
Comment 96 Lisette Scheer 2025-08-19 16:44:45 UTC
Created attachment 185555 [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

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Signed-off-by: Lisette Scheer <lisette@bywatersolutions.com>
Comment 97 Lisette Scheer 2025-08-19 16:44:49 UTC
Created attachment 185556 [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;

--ILL specific pseudonymized transactions report--

SELECT
  pt.hashed_borrowernumber as 'borrower',pt.transaction_type as 'Transaction type', pt.datetime as 'Datetime', pt.branchcode as 'Patron branchcode', pt.categorycode as 'Patron category',
  (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

Sponsored-by: UKHSA - UK Health Security Agency

Signed-off-by: Jeremy Evans <Jeremy.Evans@ukhsa.gov.uk>
Signed-off-by: Lisette Scheer <lisette@bywatersolutions.com>
Comment 98 Lucas Gass (lukeg) 2025-08-20 19:45:46 UTC
Nice work everyone!

Pushed to main for 25.11
Comment 99 Lucas Gass (lukeg) 2025-08-20 19:46:42 UTC
Please add release notes!
Comment 100 Victor Grousset/tuxayo 2025-08-21 15:40:43 UTC
If you run on main
dbic --force

You will get that file popping.
Koha/Schema/Result/PseudonymizedMetadataValue.pm

It's from here, right?
Comment 101 Pedro Amorim 2025-08-21 15:49:46 UTC
Created attachment 185629 [details] [review]
Bug 37901: Fix schema files

prove t/db_dependent/Koha/Pseudonymization.t
Comment 102 Lucas Gass (lukeg) 2025-08-21 17:46:21 UTC
follow-ups pushed to main
Comment 103 Lucas Gass (lukeg) 2025-08-25 17:11:03 UTC
We need an updated DBRev here, I think. It should check for the existence of the table before we do:

$dbh->do(q{ DROP TABLE pseudonymized_borrower_attributes; });
Comment 104 Lucas Gass (lukeg) 2025-08-25 17:13:44 UTC
I also pushed a commit to remove the now unnessesary schema file, PseudonymizedBorrowerAttribute.pm.
Comment 105 Lucas Gass (lukeg) 2025-08-25 17:25:08 UTC
(In reply to Lucas Gass (lukeg) from comment #103)
> We need an updated DBRev here, I think. It should check for the existence of
> the table before we do:
> 
> $dbh->do(q{ DROP TABLE pseudonymized_borrower_attributes; });

This is wrapped, so maybe the problem comes from Bug 36755?
Comment 106 Lucas Gass (lukeg) 2025-08-25 18:47:25 UTC
12:19:39 koha-1      | 	[31mERROR: C4::Installer::foreign_key_exists(): DBI Exception: DBD::mysql::db selectrow_array failed: Table 'koha_kohadev.pseudonymized_borrower_attributes' doesn't exist at /kohadevbox/koha/installer/data/mysql/db_revs/231200053.pl line 11
Comment 107 Lucas Gass (lukeg) 2025-08-28 21:33:51 UTC
(In reply to Lucas Gass (lukeg) from comment #106)
> 12:19:39 koha-1      | 	[31mERROR: C4::Installer::foreign_key_exists(): DBI
> Exception: DBD::mysql::db selectrow_array failed: Table
> 'koha_kohadev.pseudonymized_borrower_attributes' doesn't exist at
> /kohadevbox/koha/installer/data/mysql/db_revs/231200053.pl line 11

I followed-up here in a new bug report, Bug 40725.