When we take over an existing system in Kuwait — an ERP, a CRM, a booking platform — there is a short list of faults we expect to find before opening the code. They are not the result of bad engineering. They are the result of software written and tested in English, then filled with Arabic names and Kuwaiti dinars. Each one is invisible in a demo and obvious to the accounts department in month nine. Here they are, with the mechanism behind each and the fix.
Why does searching for an Arabic name return nothing?
Because أ إ آ and ا are four different characters, not four renderings of one. They have distinct Unicode code points, and a database comparison treats them the way it treats "a" and "b".
In practice a receptionist types احمد into a search box and the system reports no such customer, while أحمد sits in the table. The same applies to ه and ة at the end of a word, and to ى and ي. Users learn to try two or three spellings, then conclude the search is unreliable and start scrolling the list instead. Nobody files a bug, because from the outside it looks like a slow day.
This is the single most common Arabic data fault we find in existing systems, and the one users are most likely to have quietly worked around.
The fix is normalisation at write time: store a folded copy of the name alongside the original — alef variants collapsed to ا, ة to ه, ى to ي, tashkeel stripped — and search against the folded column while displaying the original. Doing it at read time instead means every query has to transform every row, which is exactly the pattern that stops an index being usable.
Why do diacritics and tashkeel break matching too?
Tashkeel marks are separate combining characters rather than part of the letter they sit on. Text stored with them will not match a query typed without them, and almost nobody types them. Data pasted from a formal document, a government form or a Quranic quotation frequently carries them invisibly.
Strip them during the same normalisation pass that handles alef variants. The display copy keeps them; the search copy does not.
Why do Arabic names sort in the wrong order?
Collation. MySQL's utf8mb4_general_ci and utf8mb4_unicode_ci sort and compare Arabic differently from one another, and neither is a safe default chosen by accident. PostgreSQL needs an ICU collation to handle Arabic properly.
The symptom is a customer list that is alphabetical in a way no Arabic speaker recognises, and a search that misses records it should return — because collation governs comparison as well as ordering. It is worth checking the collation on the column, not just on the database: a table created before a settings change keeps the old one, so a single column can disagree with everything around it.
Why do totals drift by a few fils?
The Kuwaiti dinar has three decimal places, and float and double cannot represent 0.001 exactly. They are binary approximations, so every stored amount is very slightly wrong and the errors accumulate across rows.
Over a hundred invoices this is invisible. Over a year of transactions the report total and the sum of its own lines stop agreeing, and finance finds it at year end — usually by hand, usually while closing the books, and usually assuming someone entered something incorrectly.
Use numeric(12,3) or DECIMAL(12,3) for money. A currency column typed as float is a bug with a delay on it.
The correction on an existing system is a column type migration plus a reconciliation pass, and it gets more expensive the longer the wrong type has been accumulating rows. It is worth checking early even when nothing appears wrong.
How should Hijri dates and Kuwait time be stored?
Store timestamps as timestamptz in UTC and convert at the edge, when the value is displayed. Kuwait is UTC+3 with no daylight saving, which makes local-time storage look harmless for years — until a server moves, an integration arrives from another timezone, or a report needs to compare against anything external.
Keep Hijri as a derived display value, never as the stored key. Hijri dates depend on observation and the conversion is not fixed, so a stored Hijri date is a record of one particular conversion made on one particular day. Derive it when showing it and the underlying record stays unambiguous.
How do you check whether your own system has these faults?
None of this needs access to the source code. Five checks, each a few minutes:
- 1
Search for a customer without the hamza
Find a record stored as أحمد and search احمد. If nothing comes back, normalisation is missing.
- 2
Sort the customer list by Arabic name
Show it to an Arabic speaker and ask whether the order is right. Wrong order means the wrong collation.
- 3
Sum a report's own lines by hand
Take a report with a few hundred rows and compare its total against the sum of the lines shown. A discrepancy of a few fils is a float column.
- 4
Check a timestamp against a second source
Compare a recorded time against an email or payment gateway record of the same event. A consistent three-hour offset means local time is being stored as if it were UTC.
- 5
Paste a name from a government form
Copy a name out of an official PDF into the search box. If it fails while typing the same name works, tashkeel is being stored and not stripped.
If all five pass, the system was built by someone who had seen Arabic data before. That is rarer than it should be, and worth knowing about whoever built it.