Bug 40808 - Consider a link table for accountlines to varying other Koha tables
Summary: Consider a link table for accountlines to varying other Koha tables
Status: ASSIGNED
Alias: None
Product: Koha
Classification: Unclassified
Component: Hold requests (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement
Assignee: Martin Renvoize (ashimema)
QA Contact: Testopia
URL:
Keywords:
Depends on: 3492
Blocks:
  Show dependency treegraph
 
Reported: 2025-09-15 10:15 UTC by Martin Renvoize (ashimema)
Modified: 2025-09-16 09:19 UTC (History)
3 users (show)

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


Attachments
Bug 40808: Add accountline_links table for polymorphic hold-fee linking (7.53 KB, patch)
2025-09-15 18:00 UTC, Martin Renvoize (ashimema)
Details | Diff | Splinter Review
Bug 40808: Add DBIx::Class schema for accountline_links polymorphic relationships (6.25 KB, patch)
2025-09-15 18:00 UTC, Martin Renvoize (ashimema)
Details | Diff | Splinter Review
Bug 40808: Add Koha::Object classes for accountline_links and update existing objects (14.22 KB, patch)
2025-09-15 18:00 UTC, Martin Renvoize (ashimema)
Details | Diff | Splinter Review
Bug 40808: Add schema relationships for polymorphic account line linking (2.27 KB, patch)
2025-09-15 18:00 UTC, Martin Renvoize (ashimema)
Details | Diff | Splinter Review
Bug 40808: Add comprehensive test coverage for account line linking (24.33 KB, patch)
2025-09-15 18:00 UTC, Martin Renvoize (ashimema)
Details | Diff | Splinter Review
Bug 40808: Add REST API embedding support for hold account_lines (2.42 KB, patch)
2025-09-15 18:00 UTC, Martin Renvoize (ashimema)
Details | Diff | Splinter Review
Bug 40808: Add account_lines embedding support to POST /holds endpoint (1.89 KB, patch)
2025-09-15 18:00 UTC, Martin Renvoize (ashimema)
Details | Diff | Splinter Review
Bug 40808: Add REST API unit tests for holds debits embedding (10.62 KB, patch)
2025-09-15 18:00 UTC, Martin Renvoize (ashimema)
Details | Diff | Splinter Review
Bug 40808: Add accountline_links table for polymorphic hold-fee linking (6.37 KB, patch)
2025-09-16 09:15 UTC, Martin Renvoize (ashimema)
Details | Diff | Splinter Review
Bug 40808: Add DBIx::Class schema for accountline_links polymorphic relationships (6.25 KB, patch)
2025-09-16 09:15 UTC, Martin Renvoize (ashimema)
Details | Diff | Splinter Review
Bug 40808: Add Koha::Object classes for accountline_links and update existing objects (13.11 KB, patch)
2025-09-16 09:15 UTC, Martin Renvoize (ashimema)
Details | Diff | Splinter Review
Bug 40808: Add schema relationships for polymorphic account line linking (4.14 KB, patch)
2025-09-16 09:15 UTC, Martin Renvoize (ashimema)
Details | Diff | Splinter Review
Bug 40808: Add comprehensive test coverage for account line linking (22.06 KB, patch)
2025-09-16 09:15 UTC, Martin Renvoize (ashimema)
Details | Diff | Splinter Review
Bug 40808: Add REST API embedding support for hold account_lines (2.42 KB, patch)
2025-09-16 09:15 UTC, Martin Renvoize (ashimema)
Details | Diff | Splinter Review
Bug 40808: Add account_lines embedding support to POST /holds endpoint (1.89 KB, patch)
2025-09-16 09:15 UTC, Martin Renvoize (ashimema)
Details | Diff | Splinter Review
Bug 40808: Add REST API unit tests for holds debits embedding (10.62 KB, patch)
2025-09-16 09:15 UTC, Martin Renvoize (ashimema)
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Martin Renvoize (ashimema) 2025-09-15 10:15:49 UTC
It should be easy to get from a hold to it's associated charges.. i.e. Hold fee, Cancellation fee, any processing fees.

This would be useful for API responses and display purposes.
Comment 1 Martin Renvoize (ashimema) 2025-09-15 18:00:35 UTC
Created attachment 186436 [details] [review]
Bug 40808: Add accountline_links table for polymorphic hold-fee linking

This patch introduces a new polymorphic linking system to replace the
growing number of nullable foreign key fields in the accountlines table.

Database changes:
- New accountline_links table with optimized indexes
- Supports linking account lines to holds, checkouts, article requests, etc.
- Uses clean link_type naming (hold, checkout, article_request)
- Migrates existing issue_id and old_issue_id links during upgrade
- Includes database triggers for referential integrity

Benefits:
- Eliminates nullable FK field bloat in accountlines
- Infinitely extensible for new entity types
- Enables one-to-many relationships (one fee can link to multiple entities)
- Better performance with covering indexes
Comment 2 Martin Renvoize (ashimema) 2025-09-15 18:00:40 UTC
Created attachment 186437 [details] [review]
Bug 40808: Add DBIx::Class schema for accountline_links polymorphic relationships

This patch adds the DBIx::Class schema definitions for the new
accountline_links table and updates the existing accountlines schema.

Changes:
- New Koha::Schema::Result::AccountlineLink with polymorphic relationships
- Updated Koha::Schema::Result::Accountline to include has_many relationship
- Supports modern link types: hold, checkout, article_request
- Includes validation methods for link integrity
- Optimized for common query patterns with covering indexes

The polymorphic design allows linking account lines to any entity type
without adding nullable foreign key columns to the accountlines table.
Comment 3 Martin Renvoize (ashimema) 2025-09-15 18:00:43 UTC
Created attachment 186438 [details] [review]
Bug 40808: Add Koha::Object classes for accountline_links and update existing objects

This patch implements the Koha::Object layer for the new polymorphic
account line linking system.

New classes:
- Koha::Account::Link - Individual link object with validation
- Koha::Account::Links - Collection class with filtering methods

Enhanced existing classes:
- Koha::Account::Line gains methods: add_link, links, holds, checkouts, article_requests
- Koha::Hold gains method: account_lines

Features:
- Clean API using modern class names (holds, checkouts vs reserves, issues)
- Built-in validation for link integrity
- Optimized queries with proper joins and prefetch
- Seamless integration with existing Koha::Object patterns

Usage examples:
  my $fees = $hold->debits;
  my $holds = $account_line->holds;
  $account_line->add_link('hold', $hold_id);
Comment 4 Martin Renvoize (ashimema) 2025-09-15 18:00:45 UTC
Created attachment 186439 [details] [review]
Bug 40808: Add schema relationships for polymorphic account line linking

Adds the necessary DBIx::Class relationships to support polymorphic
linking between account lines and various entities:

1. Koha::Schema::Result::Reserve - Adds accountline_links relationship
   to enable holds to find their linked account lines

2. Koha::Schema::Result::AccountlineLink - Enhanced polymorphic methods
   for accessing linked objects and validation

These relationships enable proper traversal from holds to account lines
and support the polymorphic linking system.
Comment 5 Martin Renvoize (ashimema) 2025-09-15 18:00:47 UTC
Created attachment 186440 [details] [review]
Bug 40808: Add comprehensive test coverage for account line linking

Adds comprehensive test coverage for the polymorphic account line
linking system with proper transaction isolation:

1. t/db_dependent/Koha/Account/Link.t - Tests individual Link objects
   - Object creation and validation
   - Link integrity validation
   - Exception handling for invalid link types and targets
   - Integration with account lines and holds
   - Unique constraint enforcement

2. t/db_dependent/Koha/Account/Links.t - Tests Links collection
   - Collection filtering by link_type and linked_id
   - Polymorphic access to linked objects (holds, checkouts)
   - Proper transaction isolation between subtests

3. Updates to t/db_dependent/Koha/Account/Line.t
   - New subtest for Account::Line linking methods
   - Tests add_link(), links(), holds(), checkouts() methods
   - Proper integration testing with transaction isolation

All tests pass with comprehensive coverage of the linking functionality.
Comment 6 Martin Renvoize (ashimema) 2025-09-15 18:00:49 UTC
Created attachment 186441 [details] [review]
Bug 40808: Add REST API embedding support for hold account_lines

Adds support for embedding account lines in holds via the REST API:

1. api/v1/swagger/definitions/hold.yaml - Adds account_lines property
   to hold definition for API output when embedded

2. api/v1/swagger/paths/holds.yaml - Adds account_lines to the
   x-koha-embed enum for the GET /holds endpoint

3. api/v1/swagger/paths/patrons_holds.yaml - Adds account_lines to
   the x-koha-embed enum for GET /patrons/{patron_id}/holds endpoint

Usage:
  GET /api/v1/holds?x-koha-embed=account_lines
  GET /api/v1/patrons/123/holds?x-koha-embed=account_lines

This allows API consumers to retrieve holds with their associated
charges/fees in a single request, enabling efficient access to
hold-related financial information.
Comment 7 Martin Renvoize (ashimema) 2025-09-15 18:00:51 UTC
Created attachment 186442 [details] [review]
Bug 40808: Add account_lines embedding support to POST /holds endpoint

Adds x-koha-embed parameter to the POST /holds endpoint to allow
embedding account_lines in the response when creating a hold.

This enables API consumers to immediately see any charges/fees
that are created as part of the hold placement process in a
single API call.

Usage:
  POST /api/v1/holds
  x-koha-embed: account_lines

The response will include any account lines (fees) automatically
created during hold placement, such as hold placement fees.
Comment 8 Martin Renvoize (ashimema) 2025-09-15 18:00:53 UTC
Created attachment 186443 [details] [review]
Bug 40808: Add REST API unit tests for holds debits embedding

Adds comprehensive API unit tests for the debits embedding functionality
in the holds REST API endpoints.

The tests verify:
1. GET /holds without embedding - should not include debits field
2. GET /holds with debits embedding - should include linked charges
3. GET /holds with empty debits - should return empty array when no fees linked

Note: Currently the API embedding tests fail with 500 errors due to issues
in the REST API framework's automatic embedding mechanism, though the
underlying Hold.debits() method and to_api() functionality works correctly
when tested directly.

Also improves the Hold.debits() method to properly filter for debit-only
account lines (excluding credits).
Comment 9 Martin Renvoize (ashimema) 2025-09-16 09:15:13 UTC
Created attachment 186458 [details] [review]
Bug 40808: Add accountline_links table for polymorphic hold-fee linking

This patch introduces a new polymorphic linking system to replace the
growing number of nullable foreign key fields in the accountlines table.

Database changes:
- New accountline_links table with optimized indexes
- Supports linking account lines to holds, checkouts, article requests, etc.
- Uses clean link_type naming (hold, checkout, article_request)
- Migrates existing issue_id and old_issue_id links during upgrade
- Includes database triggers for referential integrity

Benefits:
- Eliminates nullable FK field bloat in accountlines
- Infinitely extensible for new entity types
- Enables one-to-many relationships (one fee can link to multiple entities)
- Better performance with covering indexes
Comment 10 Martin Renvoize (ashimema) 2025-09-16 09:15:15 UTC
Created attachment 186459 [details] [review]
Bug 40808: Add DBIx::Class schema for accountline_links polymorphic relationships

This patch adds the DBIx::Class schema definitions for the new
accountline_links table and updates the existing accountlines schema.

Changes:
- New Koha::Schema::Result::AccountlineLink with polymorphic relationships
- Updated Koha::Schema::Result::Accountline to include has_many relationship
- Supports modern link types: hold, checkout, article_request
- Includes validation methods for link integrity
- Optimized for common query patterns with covering indexes

The polymorphic design allows linking account lines to any entity type
without adding nullable foreign key columns to the accountlines table.
Comment 11 Martin Renvoize (ashimema) 2025-09-16 09:15:18 UTC
Created attachment 186460 [details] [review]
Bug 40808: Add Koha::Object classes for accountline_links and update existing objects

This patch implements the Koha::Object layer for the new polymorphic
account line linking system.

New classes:
- Koha::Account::Link - Individual link object with validation
- Koha::Account::Links - Collection class with filtering methods

Enhanced existing classes:
- Koha::Account::Line gains methods: add_link, links, holds
- Koha::Hold gains method: account_lines

Features:
- Clean API using modern class names (holds)
- Built-in validation for link integrity
- Optimized queries with proper joins and prefetch
- Seamless integration with existing Koha::Object patterns

Usage examples:
  my $fees = $hold->debits;
  my $holds = $account_line->holds;
  $account_line->add_link('hold', $hold_id);
Comment 12 Martin Renvoize (ashimema) 2025-09-16 09:15:20 UTC
Created attachment 186461 [details] [review]
Bug 40808: Add schema relationships for polymorphic account line linking

Adds the necessary DBIx::Class relationships to support polymorphic
linking between account lines and various entities:

1. Koha::Schema::Result::Reserve - Adds accountline_links relationship
   to enable holds to find their linked account lines

2. Koha::Schema::Result::AccountlineLink - Enhanced polymorphic methods
   for accessing linked objects and validation

These relationships enable proper traversal from holds to account lines
and support the polymorphic linking system.
Comment 13 Martin Renvoize (ashimema) 2025-09-16 09:15:22 UTC
Created attachment 186462 [details] [review]
Bug 40808: Add comprehensive test coverage for account line linking

Adds comprehensive test coverage for the polymorphic account line
linking system with proper transaction isolation:

1. t/db_dependent/Koha/Account/Link.t - Tests individual Link objects
   - Object creation and validation
   - Link integrity validation
   - Exception handling for invalid link types and targets
   - Integration with account lines and holds
   - Unique constraint enforcement

2. t/db_dependent/Koha/Account/Links.t - Tests Links collection
   - Collection filtering by link_type and linked_id
   - Polymorphic access to linked objects (holds, checkouts)
   - Proper transaction isolation between subtests

3. Updates to t/db_dependent/Koha/Account/Line.t
   - New subtest for Account::Line linking methods
   - Tests add_link(), links(), holds(), checkouts() methods
   - Proper integration testing with transaction isolation

All tests pass with comprehensive coverage of the linking functionality.
Comment 14 Martin Renvoize (ashimema) 2025-09-16 09:15:24 UTC
Created attachment 186463 [details] [review]
Bug 40808: Add REST API embedding support for hold account_lines

Adds support for embedding account lines in holds via the REST API:

1. api/v1/swagger/definitions/hold.yaml - Adds account_lines property
   to hold definition for API output when embedded

2. api/v1/swagger/paths/holds.yaml - Adds account_lines to the
   x-koha-embed enum for the GET /holds endpoint

3. api/v1/swagger/paths/patrons_holds.yaml - Adds account_lines to
   the x-koha-embed enum for GET /patrons/{patron_id}/holds endpoint

Usage:
  GET /api/v1/holds?x-koha-embed=account_lines
  GET /api/v1/patrons/123/holds?x-koha-embed=account_lines

This allows API consumers to retrieve holds with their associated
charges/fees in a single request, enabling efficient access to
hold-related financial information.
Comment 15 Martin Renvoize (ashimema) 2025-09-16 09:15:27 UTC
Created attachment 186464 [details] [review]
Bug 40808: Add account_lines embedding support to POST /holds endpoint

Adds x-koha-embed parameter to the POST /holds endpoint to allow
embedding account_lines in the response when creating a hold.

This enables API consumers to immediately see any charges/fees
that are created as part of the hold placement process in a
single API call.

Usage:
  POST /api/v1/holds
  x-koha-embed: account_lines

The response will include any account lines (fees) automatically
created during hold placement, such as hold placement fees.
Comment 16 Martin Renvoize (ashimema) 2025-09-16 09:15:29 UTC
Created attachment 186465 [details] [review]
Bug 40808: Add REST API unit tests for holds debits embedding

Adds comprehensive API unit tests for the debits embedding functionality
in the holds REST API endpoints.

The tests verify:
1. GET /holds without embedding - should not include debits field
2. GET /holds with debits embedding - should include linked charges
3. GET /holds with empty debits - should return empty array when no fees linked

Note: Currently the API embedding tests fail with 500 errors due to issues
in the REST API framework's automatic embedding mechanism, though the
underlying Hold.debits() method and to_api() functionality works correctly
when tested directly.

Also improves the Hold.debits() method to properly filter for debit-only
account lines (excluding credits).