Bug 41246

Summary: Deleting patron categories or libraries can leave orphaned rules in the overduerules table
Product: Koha Reporter: Lucas Gass (lukeg) <lucas>
Component: CirculationAssignee: Lucas Gass (lukeg) <lucas>
Status: Needs Signoff --- QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: andrew, gmcharlt, kyle
Version: Main   
Hardware: All   
OS: All   
GIT URL: Initiative type: ---
Sponsorship status: --- Comma delimited list of Sponsors:
Crowdfunding goal: 0 Patch complexity: Small patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:
Attachments: Bug 41246: Add foreign key constraints to overduerules table and cascade on delete
Bug 41246: Add foreign key constraints to overduerules table and cascade on delete
Bug 41246: DBIC changes [DO NOT PUSH]

Description Lucas Gass (lukeg) 2025-11-13 14:18:09 UTC
To recreate: 

1. Add or have a patron category.
2. Go to Tools >  Overdue notice/status triggers 
3. Set some rules for that category.
4. Delete the patron category 
5. Now do "select distinct categorycode from overduerules;"
6. The rule remains with a non-existent categorycode
Comment 1 Lucas Gass (lukeg) 2025-11-13 14:22:41 UTC
Just notices this is also true of branches. If I make a rule for a specific branch and then deleted the branch, the overduerule remains.
Comment 2 Lucas Gass (lukeg) 2025-11-13 14:59:30 UTC
Created attachment 189564 [details] [review]
Bug 41246: Add foreign key constraints to overduerules table and cascade on delete

To test:
0. APPLY PATCH, updatedatabase, restart_all
1. Add or have a patron category.
2. Go to Tools >  Overdue notice/status triggers
3. Set some rules for that category.
4. Delete the patron category
5. Now do "select distinct categorycode from overduerules;"
6. All rules should be gone.
Comment 3 Lucas Gass (lukeg) 2025-11-13 15:05:55 UTC
Created attachment 189565 [details] [review]
Bug 41246: Add foreign key constraints to overduerules table and cascade on delete

To test:
0. APPLY PATCH, updatedatabase, restart_all
1. Add or have a patron category.
2. Go to Tools >  Overdue notice/status triggers
3. Set some rules for that category.
4. Delete the patron category
5. Now do "select distinct categorycode from overduerules;"
6. All rules should be gone.
Comment 4 Lucas Gass (lukeg) 2025-11-13 15:05:58 UTC
Created attachment 189566 [details] [review]
Bug 41246: DBIC changes [DO NOT PUSH]
Comment 5 Andrew Fuerste-Henry 2025-11-13 15:27:48 UTC
It's worth noting explicitly that these orphaned rules cause the overdues cron to fail and not generate notices for anyone.