Bug 40665

Summary: Add booking_id field to issues to link checkouts to bookings that were fulfilled by them
Product: Koha Reporter: Paul Derscheid <paul.derscheid>
Component: CirculationAssignee: Paul Derscheid <me>
Status: Pushed to main --- QA Contact: Martin Renvoize (ashimema) <martin.renvoize>
Severity: enhancement    
Priority: P5 - low CC: gmcharlt, jonathan.druart, kyle, lucas, martin.renvoize, paul.derscheid
Version: Main   
Hardware: All   
OS: All   
GIT URL: Change sponsored?: ---
Patch complexity: --- Documentation contact:
Documentation submission: Text to go in the release notes:
Version(s) released in:
25.11.00
Circulation function: Bookings
Bug Depends on: 40296    
Bug Blocks:    
Attachments: Bug 40665: Atomic update
[DO NOT PUSH] Bug 40665: DBIC
Bug 40665: Add tests
Bug 40665: API spec changes
Bug 40665: Link checkouts to bookings
Bug 40665: Database update
Bug 40665: Add tests
Bug 40665: API spec changes
Bug 40665: Link checkouts to bookings
Bug 40665: DBIC
Bug 40665: (RM follow-up) Adjust number of tests in t/db_dependent/Circulation.t
Bug 40665: Fix Cypress tests
Bug 40665: (follow-up) Sync comment for booking_id column

Description Paul Derscheid 2025-08-18 15:26:36 UTC
Following the work on Bug 40296, we should add a way to link checkouts back to the bookings that were fulfilled through them.
Comment 1 Paul Derscheid 2025-08-18 15:48:07 UTC
Created attachment 185516 [details] [review]
Bug 40665: Atomic update
Comment 2 Paul Derscheid 2025-08-18 15:48:09 UTC
Created attachment 185517 [details] [review]
[DO NOT PUSH] Bug 40665: DBIC
Comment 3 Paul Derscheid 2025-08-18 15:48:12 UTC
Created attachment 185518 [details] [review]
Bug 40665: Add tests
Comment 4 Paul Derscheid 2025-08-18 15:48:14 UTC
Created attachment 185519 [details] [review]
Bug 40665: API spec changes
Comment 5 Paul Derscheid 2025-08-18 15:48:17 UTC
Created attachment 185520 [details] [review]
Bug 40665: Link checkouts to bookings

This patch adds a booking_id field to the issues and old_issues tables
to create a linkage between checkouts and the bookings that generated
them. When a patron with a booking checks out the reserved item, the
checkout is linked to their booking and the booking status is updated
to 'completed'.

The booking_id is preserved during renewals and when checkouts are
moved to old_issues upon return.

Database changes:
- Add booking_id column to issues and old_issues tables
- Add foreign key constraints to maintain referential integrity

Test plan:
1. Apply the database update
2. Restart services
3. Create a booking for a patron
4. When the patron checks out the booked item:
 a. Verify the checkout links to the booking (booking_id is set)
 b. Verify the booking status changes to 'completed'
5. Renew the checkout
 a. Verify the booking_id is preserved after renewal
 b. Check the database: SELECT booking_id FROM issues WHERE issue_id = X
6. Return the item
 a. Verify the checkout moves to old_issues
 b. Verify the booking_id is preserved in old_issues
7. Test the new Koha::Booking methods:
 a. For a booking with a checkout, verify $booking->checkout returns
    the correct Koha::Checkout object
 b. For a booking with an old checkout, verify $booking->old_checkout
    returns the correct Koha::Old::Checkout object
8. Test the API:
 a. Verify checkout endpoints include booking_id in responses
 b. Verify the booking relationship can be embedded using
    x-koha-embed: booking
9. Run the test suite:
 prove t/db_dependent/Circulation.t
 prove t/db_dependent/Koha/Booking.t
 prove t/db_dependent/Koha/Checkout.t

NOTE: I also thought about the inverse: linking to a checkout from the
booking, but as Koha is a circulation centric system, this approach makes
more sense in my opinion. I also thought about bidirectional linking via
FKs but that is too much maintenance overhead (and potential error source)
that we don't need. The inverse lookups should still be performant through
the FKs.
Comment 6 Martin Renvoize (ashimema) 2025-08-29 10:32:13 UTC
Created attachment 185895 [details] [review]
Bug 40665: Database update

This patch updates the database schema to add booking_id foreign key
columns to both issues and old_issues tables, enabling the linkage
between checkouts and the bookings that generated them.

Schema changes:
- Add booking_id column to issues and old_issues tables
- Add foreign key constraints referencing bookings table
- Preserve referential integrity with appropriate CASCADE actions

Signed-off-by: Martin Renvoize <martin.renvoize@openfifth.co.uk>
Comment 7 Martin Renvoize (ashimema) 2025-08-29 10:32:16 UTC
Created attachment 185896 [details] [review]
Bug 40665: Add tests

Signed-off-by: Martin Renvoize <martin.renvoize@openfifth.co.uk>
Comment 8 Martin Renvoize (ashimema) 2025-08-29 10:32:18 UTC
Created attachment 185897 [details] [review]
Bug 40665: API spec changes

Signed-off-by: Martin Renvoize <martin.renvoize@openfifth.co.uk>
Comment 9 Martin Renvoize (ashimema) 2025-08-29 10:32:20 UTC
Created attachment 185898 [details] [review]
Bug 40665: Link checkouts to bookings

This patch adds a booking_id field to the issues and old_issues tables
to create a linkage between checkouts and the bookings that generated
them. When a patron with a booking checks out the reserved item, the
checkout is linked to their booking and the booking status is updated
to 'completed'.

The booking_id is preserved during renewals and when checkouts are
moved to old_issues upon return.

Database changes:
- Add booking_id column to issues and old_issues tables
- Add foreign key constraints to maintain referential integrity

Test plan:
1. Apply the database update
2. Restart services
3. Create a booking for a patron
4. When the patron checks out the booked item:
 a. Verify the checkout links to the booking (booking_id is set)
 b. Verify the booking status changes to 'completed'
5. Renew the checkout
 a. Verify the booking_id is preserved after renewal
 b. Check the database: SELECT booking_id FROM issues WHERE issue_id = X
6. Return the item
 a. Verify the checkout moves to old_issues
 b. Verify the booking_id is preserved in old_issues
7. Test the new Koha::Booking methods:
 a. For a booking with a checkout, verify $booking->checkout returns
    the correct Koha::Checkout object
 b. For a booking with an old checkout, verify $booking->old_checkout
    returns the correct Koha::Old::Checkout object
8. Test the API:
 a. Verify checkout endpoints include booking_id in responses
 b. Verify the booking relationship can be embedded using
    x-koha-embed: booking
9. Run the test suite:
 prove t/db_dependent/Circulation.t
 prove t/db_dependent/Koha/Booking.t
 prove t/db_dependent/Koha/Checkout.t

NOTE: I also thought about the inverse: linking to a checkout from the
booking, but as Koha is a circulation centric system, this approach makes
more sense in my opinion. I also thought about bidirectional linking via
FKs but that is too much maintenance overhead (and potential error source)
that we don't need. The inverse lookups should still be performant through
the FKs.

Signed-off-by: Martin Renvoize <martin.renvoize@openfifth.co.uk>
Comment 10 Martin Renvoize (ashimema) 2025-08-29 10:32:22 UTC
Created attachment 185899 [details] [review]
Bug 40665: DBIC

Signed-off-by: Martin Renvoize <martin.renvoize@openfifth.co.uk>
Comment 11 Lucas Gass (lukeg) 2025-09-05 20:59:08 UTC
Created attachment 186250 [details] [review]
Bug 40665: (RM follow-up) Adjust number of tests in t/db_dependent/Circulation.t
Comment 12 Lucas Gass (lukeg) 2025-09-05 21:19:11 UTC
Nice work everyone!

Pushed to main for 25.11
Comment 13 Jonathan Druart 2025-09-11 09:35:08 UTC
Created attachment 186369 [details] [review]
Bug 40665: Fix Cypress tests

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 14 Jonathan Druart 2025-09-11 09:36:21 UTC
(In reply to Jonathan Druart from comment #13)
> Created attachment 186369 [details] [review] [review]
> Bug 40665: Fix Cypress tests
> 
> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>

Pushed to main.
Comment 15 Jonathan Druart 2025-10-07 12:07:08 UTC
Koha_Main_MariaDB_update is failing:

https://jenkins.koha-community.org/job/Koha_Main_MariaDB_update/261/consoleFull

22:29:35 koha-1  | -foreign key linking this checkout to the booking it fulfills
22:29:35 koha-1  | +foreign key linking this checkout to the booking it fulfilled
Comment 16 Lucas Gass (lukeg) 2025-10-08 15:30:17 UTC
Created attachment 187589 [details] [review]
Bug 40665: (follow-up) Sync comment for booking_id column
Comment 17 Lucas Gass (lukeg) 2025-10-08 15:53:35 UTC
follow-up pushed to main