Keeping patron records clean and consistent is essential when integrating third-party messaging systems with Koha. After connecting WhatsApp Business API to send important alerts, it became clear. Many patron profiles weren’t ready for WhatsApp delivery requirements. Specifically:

• Some patrons had their mobile numbers saved under the default phone field.
• Only a few had numbers in the dedicated SMS Alert Number field.
• None had a country code prefix, which WhatsApp requires for message delivery.

Manually correcting 5,000+ records through the Koha staff interface would have been painfully slow. So, I used a safe, direct approach via MySQL to standardize all phone numbers for WhatsApp, quickly and consistently. And here is exactly how I did it, along with important precautions and best practices you can follow.

Why This Cleanup Was Necessary

WhatsApp requires phone numbers in full international format (E.164-like), which includes the country code (e.g., 91 for India). Koha, however, often stores mobile numbers across multiple fields, commonly:

• phone (general phone)
• smsalertnumber (used by Koha for SMS/alerts)
• mobile (sometimes used depending on site practices)

For a reliable integration:

• All alert-targeted numbers should be centralized in smsalertnumber.
• Every number should be prefixed with the correct country code.
• Fields should be consistent across all patron records.

Prerequisites and Warnings

• Take a full database backup before running any updates.
• Verify the target instance and database name.
• Ensure there’s no ongoing batch job or cron task that might simultaneously alter borrower records.
• If your patrons include non-Indian numbers, add validation/filters to avoid wrongly applying 91 to all records.
• If you use additional number fields (e.g., mobile), consider merging/validating them as well.

The Exact Steps I Used

1. Switch to root (optional, depending on your environment):
   sudo su
2. Log into MySQL:
   mysql -uroot -p
3. Select your Koha database:
   use koha_library;
4. Clear previously entered SMS alert numbers (start clean):
   UPDATE borrowers SET smsalertnumber='';
5. Copy the general phone field into the SMS alert field:
   UPDATE borrowers SET smsalertnumber=phone;
6. Prefix the country code (India = 91) to all smsalertnumber entries:
   UPDATE borrowers SET smsalertnumber = CONCAT(91, smsalertnumber);
7. Exit MySQL:
   quit

That’s it. With these three updates, all patrons now have standardized mobile numbers in the right field and in the correct format for WhatsApp delivery.

Practical Enhancements and Safety Checks

The above works well if:

• All patron “phone” values are mobile numbers.
• All numbers are Indian.
• There are no blanks, special characters, or formatting variations.

In real datasets, you’ll often need more nuance. Consider these optional refinements:

• Skip empty/null values:
  UPDATE borrowers SET smsalertnumber=phone WHERE phone IS NOT NULL AND phone <> '';
• Remove spaces, dashes, or parentheses before prefixing:
  UPDATE borrowers SET smsalertnumber = REPLACE(REPLACE(REPLACE(smsalertnumber,' ',''),'-',''),'(','');
• Avoid double-prefixing numbers that already start with 91:
  UPDATE borrowers
SET smsalertnumber = CONCAT('91', smsalertnumber)
WHERE smsalertnumber NOT LIKE '91%';
• Handle numbers that already include a + sign:
  UPDATE borrowers
SET smsalertnumber = TRIM(LEADING '+' FROM smsalertnumber);
• Ensure only digits before adding the prefix (basic cleanup):
  UPDATE borrowers
SET smsalertnumber = REGEXP_REPLACE(smsalertnumber, '[^0-9]', '');
• Only prefix 91 when the number doesn’t already have a country code length:
  UPDATE borrowers
SET smsalertnumber = CONCAT('91', smsalertnumber)
WHERE CHAR_LENGTH(smsalertnumber) <= 10;

Note: REGEXP_REPLACE requires MySQL 8.0+. On older versions, you may need nested REPLACE calls.

Post-Update Verification

Run quick checks to validate the integrity of the changes:

• Count blanks (should be minimal/intentional):
  SELECT COUNT(*) FROM borrowers WHERE smsalertnumber IS NULL OR smsalertnumber = '';
• Spot entries without the 91 prefix:
  SELECT borrowernumber, smsalertnumber FROM borrowers WHERE smsalertnumber NOT LIKE '91%';
• Check for non-numeric characters:
  SELECT borrowernumber, smsalertnumber FROM borrowers WHERE smsalertnumber REGEXP '[^0-9]';
• Sample a few records:
  SELECT borrowernumber, surname, firstname, phone, smsalertnumber FROM borrowers LIMIT 20;

If issues appear, adjust with targeted UPDATE statements.

Koha and WhatsApp Integration Tips

• Koha + WhatsApp integration for my instance was done with help from Indranil Da. Who is a lovely human being and an active contributor in the koha community. A detailed blog post on this integration will follow.
• Ensure Koha is actually configured to use smsalertnumber when sending alerts.
• If a separate messaging gateway service is in play (e.g., msg91), confirm the exact number format they expect.
• Consider adding a validation rule at data entry time (via patron import scripts, SIP2 integrations, or staff training) so numbers are always stored in international format moving forward.
• For ongoing hygiene, schedule periodic reports that flag missing or malformed numbers.

Results

After these updates, all patron profiles now:

• Store the mobile number for alerts in smsalertnumber.
• Include the country code prefix required by WhatsApp (91 for India).
• They are consistent across the entire database, enabling reliable delivery of WhatsApp notifications for due-date reminders, fines, and other critical alerts.

Final Thoughts

Backend updates like these can save days of manual work while improving data quality. With a backup and a few well-crafted SQL statements, Koha patron records can be standardized quickly—and WhatsApp Business API integrations can run smoothly. If your environment uses additional number fields or supports international patrons, adapt the safety checks and conditional logic to match your data reality.