[Bug 43616] New: Add core support for scheduled/automated patron import (RFC)
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43616 Bug ID: 43616 Summary: Add core support for scheduled/automated patron import (RFC) Initiative type: --- Sponsorship --- status: Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Patrons Assignee: koha-bugs@lists.koha-community.org Reporter: martin.renvoize@openfifth.co.uk QA Contact: testopia@bugs.koha-community.org CC: gmcharlt@gmail.com, kyle@bywatersolutions.com Target Milestone: --- This bug proposes adding native support to Koha core for scheduled, unattended patron imports -- something core currently lacks entirely. Today, Koha's patron import tooling (tools/import_borrowers.pl, misc/import_patrons.pl, Koha::Patrons::Import) is manual-trigger only. There is no way to configure a remote source and have Koha fetch and import an updated patron file on a schedule without a human clicking "import" or a hand-rolled external script. In practice, this gap has been filled by third-party plugins -- most notably koha-plugin-patrons-importer-advanced, which has real production customers depending on scheduled SFTP-sourced patron imports, including requests to run more than once per day. That plugin work highlighted exactly what's missing from core. This RFC proposes closing the gap with the same pattern Koha already uses for a very similar problem: bug 43603 (scheduled article request scan import), which polls a configured Koha::File::Transport (bug 35761) for new files and imports them, with a DB-backed idempotency log so repeated/frequent polling is safe. Proposed for core: - New tables patron_import_accounts and patron_import_log, mirroring the article_request_scan_accounts / article_request_scan_log pattern from bug 43603 (an account references a Koha::File::Transport, a matchpoint, defaults/preserve-fields, and an active flag; the log table records per-file processed/error status keyed by account and filename for idempotency). - A new admin config page, admin/patron_import_accounts.pl, modeled on admin/article_request_scan_accounts.pl, for configuring named import sources. - A new cronjob, misc/cronjobs/patron_imports.pl, modeled on misc/cronjobs/article_request_scans.pl, that polls active accounts, fetches new files via their configured file transport, and imports them via the existing Koha::Patrons::Import engine (no changes needed to the import engine itself -- this only adds scheduling/fetch infrastructure around it). This is Phase B of a small roadmap; a follow-up bug (Phase C) would apply the same pattern to scheduled patron photo import. This bug's scope is Phase B only: scheduled patron record import. No patch is attached yet. This bug is filed to open discussion on the design (in particular, whether the article-request-scan pattern from bug 43603 is the right one to reuse here) before implementation begins. Depends on: bug 35761 (Koha::File::Transport) See also: bug 43603 (article request scan import -- the precedent this design follows) -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43616 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |34632 Assignee|koha-bugs@lists.koha-commun |martin.renvoize@openfifth.c |ity.org |o.uk Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34632 [Bug 34632] Patron Importing should be a background job -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43616 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |ASSIGNED -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43616 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |andrew@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43616 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Summary|Add core support for |Add core support for |scheduled/automated patron |scheduled/automated patron |import (RFC) |import -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43616 --- Comment #1 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 206723 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=206723&action=edit Bug 43616: Add patron_import_accounts and patron_import_log tables Adds two new database tables to support scheduled patron imports: - patron_import_accounts: stores named import sources with the full configuration also available on the interactive Tools > Import patrons page (matchpoint, overwrite/preserve/default-value options, welcome email, patron list creation), plus the file transport used to fetch new files and an active flag - patron_import_log: tracks per-file processing for idempotency If you previously applied an earlier version of this atomicupdate that only created the original, narrower table shape, drop both tables first (DROP TABLE patron_import_log, patron_import_accounts;) before re-running updatedatabase.pl, since this version's CREATE TABLE has additional columns the TableExists guard won't add for you. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43616 --- Comment #2 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 206724 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=206724&action=edit Bug 43616: Automated Schema Update -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43616 --- Comment #3 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 206725 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=206725&action=edit Bug 43616: Add is_boolean flags to PatronImportAccount schema -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43616 --- Comment #4 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 206726 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=206726&action=edit Bug 43616: Add Koha::PatronImportAccount(s) Implements the Koha::Object and Koha::Objects layer on top of the PatronImportAccount schema, providing file_transport relationship resolution, CRUD functionality, and three helpers used by both the config page and the cronjob: - decoded_defaults / decoded_preserve_fields: JSON-decode the two longtext columns that store per-column default values and the list of fields preserved on overwrite - resolved_matchpoint: strips the "patron_attribute_" prefix the matchpoint dropdown uses for attribute-based matchpoints, so the stored value can still be used to reselect the right form option -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43616 --- Comment #5 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 206727 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=206727&action=edit Bug 43616: Add Koha::PatronImportLog(s) Implements the Koha Object classes for the patron_import_log table, following the same pattern as Koha::ArticleRequestScanLog(s). This provides the idempotency mechanism for the import cronjob via the (account_id, filename) unique constraint. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43616 --- Comment #6 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 206728 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=206728&action=edit Bug 43616: Add "Schedule patron imports" page under Tools Adds tools/schedule_patron_imports.pl and its template: CRUD for named scheduled patron import accounts, listed and linked directly beneath "Import patrons" in the Tools menu, permission-gated on the same tools > import_patrons permission as its sibling. The add/edit form exposes every option tools/import_borrowers.pl does (record-matching field, per-column default values including a patron_attributes default, fields preserved on overwrite, replace passwords, renew-on-overwrite behaviour, patron attribute replace-all/replace-included, welcome email, patron list creation) in addition to the file transport and active flag that make this a scheduled account rather than a one-off run. This is a separate page from the interactive tool rather than a mode added to it: the interactive tool is a single-shot action (upload, enqueue, see a result), while this is CRUD over a list of persistent, named configs - folding the two together would add a full list/edit/delete state machine to an already large, heavily-used page. Each page links to the other for discoverability. Test plan: 1. Apply the whole patchset, update the database, restart_all. 2. Go to Administration > File transports and add a Local file transport pointing at a directory you can drop CSV files into. 3. Log in as a user with the tools > import_patrons permission and go to Tools - confirm "Schedule patron imports" appears directly below "Import patrons", and that Tools > Import patrons now shows a note linking to the new page. 4. Go to Tools > Schedule patron imports and create a new account: - Name and File transport from step 2 - Matchpoint: cardnumber - Expand "Enter default values" and set a default branchcode - Expand "Preserve existing values" and check surname - Overwrite the existing one with this - Replace patron passwords - Renew existing patrons "from the current date" - Send email to new patrons: checked - Create patron list: checked Save, and confirm the account appears in the list. 5. Click Edit - confirm every field above, including both expandable sections' previous contents, the selected matchpoint, the overwrite radio and its nested renew/password controls, and both checkboxes, are correctly pre-filled. 6. Click Delete - confirm the confirmation page and the resulting removal from the list both work. 7. Confirm admin/patron_import_accounts.pl (the old location) no longer exists, and Administration no longer links to it. 8. Try reaching the page as a user without tools > import_patrons - confirm access is denied. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43616 --- Comment #7 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 206729 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=206729&action=edit Bug 43616: Add patron_imports.pl cron job Polls every active patron_import_accounts row's configured Koha::File::Transport for new .csv files and imports each one through the existing, unmodified Koha::Patrons::Import engine, passing through every option configurable on the account: matchpoint (resolved from its stored, possibly patron_attribute_-prefixed form), overwrite cardnumber/passwords, preserved fields, per-column default values, membership expiry renewal behaviour, welcome emails, and patron list creation from each run's imported patrons - the same options tools/import_borrowers.pl exposes interactively, since Koha::Patrons::Import already implements all of them. Structurally adapted from misc/cronjobs/article_request_scans.pl, minus the zip/mapping-file layer since patron import files are plain CSV imported directly. Processed and errored files are archived under processed/ and error/ subdirectories of the transport's configured directory, and each outcome is recorded in patron_import_log for per-(account, filename) idempotency. A few edge cases are handled explicitly: - A file that produces errors but not a single imported or overwritten patron (e.g. a CSV with a bad header row) is treated as a failure - it is logged with status 'error' and archived to error/ instead of being indistinguishable from a legitimately empty file. Partial successes (some rows imported, some errored) still archive to processed/, with the error count appended to the log details. - Any remote-server-supplied filename containing a path separator is rejected before it can be used to build a local download path or an archive destination, closing a path traversal hole against a malicious or misconfigured remote server. - Errors while storing the patron_import_log row (most likely a Koha::Exceptions::Object::DuplicateID from two overlapping cron runs racing on the same new file) are caught and logged as a warning rather than killing the whole run and abandoning every remaining file and account. Genuinely overlapping runs can still both import the same new file before either records having processed it, so the schedule interval should stay comfortably longer than a single run's worst-case duration - only the crash on that race is fixed here. - Patron list creation (the account's create_patron_list option) requires a real logged-in user to attribute ownership to. This cronjob runs unattended and never has one, so when create_patron_list is set, a warning is logged explaining why and no list is created - see the cronjob's own POD for this documented limitation. - A stored matchpoint can go stale if the patron attribute type it refers to is later deleted, or ExtendedPatronAttributes is switched off - Koha::Patrons::Import would then silently fail to match any existing row, mass-creating duplicate patrons on every unattended run. Each account's resolved matchpoint is validated up front ('cardnumber', 'userid', or a still-existing, unique-id-enabled attribute type) and the account is skipped with a logged error otherwise. Test plan: 1. Apply the whole patchset, update the database, restart_all. 2. As in the "Schedule patron imports" test plan, create a Local file transport and a scheduled account using it, this time also setting a default branchcode, preserving "surname" on overwrite, enabling "Replace patron passwords", "Renew existing patrons from the current date", "Send email to new patrons", and "Create patron list". 3. Prepare a CSV with a header row and a data row missing the branchcode column (to exercise the default value), matching an existing patron's cardnumber (to exercise overwrite/preserve/renew/ password-replace), and drop it into the transport's directory. 4. Dry run: `misc/cronjobs/patron_imports.pl -v` - confirm it reports "Test run only" and lists the file as "Processing: <filename>" without moving or importing anything. 5. Confirm: `misc/cronjobs/patron_imports.pl -c -v` - confirm: - the existing patron is overwritten, their surname is unchanged (preserved), their password is replaced, and their expiry date is now today - the row missing branchcode picked up the configured default - a WELCOME notice was queued for any newly created patron - a warning is logged that no patron list was created (no real user context is available when running from cron), and no patron list exists for this run - the file is moved into processed/, and a patron_import_log row exists with status 'processed' 6. Run the same command again - confirm the file is skipped ("Already processed: <filename>"). 7. Drop a CSV with a garbled header row and run with -c -v again - confirm it archives to error/ with log status 'error'. 8. Configure an account whose matchpoint is a patron_attribute_ code, then delete that patron attribute type (or disable ExtendedPatronAttributes) - confirm the account is skipped with a logged error and no files are processed for it, rather than mass-creating new patrons. 9. Confirm -a <account_id> restricts processing to that one account when more than one is configured. 10. `prove t/db_dependent/Koha/PatronImportAccounts.t t/db_dependent/Koha/PatronImportLogs.t` all pass. Co-Authored-By: Claude Sonnet 5 <noreply@anthropic.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43616 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43616 --- Comment #8 from Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> --- Created attachment 206730 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=206730&action=edit Bug 43616: Register patron_imports.pl in the default crontab templates Adds the new cronjob to both places Koha ships default cron scheduling from, so the feature actually runs out of the box once a librarian configures an account under Tools > Schedule patron imports: - debian/koha-common.cron.daily: added alongside the other nightly per-patron-account jobs (membership_expiry.pl, etc.), run via koha-foreach for every enabled instance, same as its neighbours. - misc/cronjobs/crontab.example: added as its own commented section for development/from-source installs, at 3am matching the cronjob's own POD-documented example schedule. With no accounts configured (the default), the script exits immediately after finding zero active patron_import_accounts rows - this entry is a no-op until a librarian opts in via the Tools page, matching the pattern already followed by every other conditional job in the same files (e.g. plugins_nightly.pl, archive_purchase_suggestions.pl). Test plan: 1. Apply the whole patchset. 2. Check debian/koha-common.cron.daily contains a "koha-foreach --chdir --enabled .../patron_imports.pl -c" line alongside the other koha-foreach entries. 3. Check misc/cronjobs/crontab.example contains a "SCHEDULED PATRON IMPORTS" section with a 0 3 * * * entry for patron_imports.pl -c. 4. With no patron_import_accounts configured, run `misc/cronjobs/patron_imports.pl -c -v` directly and confirm it exits cleanly with no output and no error (the no-op case these templates rely on). -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43616 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |37248 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=37248 [Bug 37248] [Omnibus] Power to the user -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43616 Martin Renvoize (ashimema) <martin.renvoize@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Sponsorship status|--- |Sponsored Target Milestone|--- |27.05 Initiative type|--- |Feature -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org