Why can't my users find their own data?

Because the form you indexed and the form they typed are different strings. Across 297 realistic query variants against the same 47 documents, an un-normalised SQLite FTS5 index returned the right document for 51.5% of them; adding NFC, case folding, whitespace collapse and punctuation folding took that to 74.7%, and folding diacritics as well took it to 84.2%. Under an exact-key lookup rather than a search index, the raw number is 15.8%. The ranking function never entered into it.

Everything below was run on an Apple M3, 16 GB, macOS 26.4.1. Retrieval is SQLite FTS5 through Python 3.14.6's sqlite3 module, SQLite 3.53.4. The case-folding comparison also uses Node v23.5.0 (ICU 76.1, Unicode 16.0). Node 23's built-in node:sqlite reports SQLite 3.47.2 with no FTS5 — no such module: fts5 — so none of the retrieval numbers came from it. No network, no libraries.

The corpus is synthetic and written by hand for this article. The absolute percentages belong to it and nothing else. What transfers is the relative pattern by variant class: which normalisation step recovers which failure, and which step quietly destroys meaning while it does so.

The short answer

• Case folding is the step everyone applies and it bought 0.4 points, because FTS5's unicode61 tokenizer already folds case. The steps that moved recall were NFC (+8.1), punctuation folding (+14.8) and diacritic folding (+9.5).
• 24 of our 47 documents are a different string in NFD than in NFC — including 19 of 19 Vietnamese ones. Un-normalised, an NFD query against an NFC index matched 0.0%; one normalize('NFC') call made it 100.0%.
• Vietnamese đ is not a combining mark, so decomposition never removes it. NFD + strip marks recovered only 59.3% of accent-free queries, and all 11 misses contained đ or Đ. It needs an explicit đ → d rule.
• The Vietnamese trade: on 20 minimal-pair documents, folding tone marks at index time moved recall on accent-free queries from 15.0% to 100.0% and precision on accented queries from 100.0% to 20.0%. A folded duplicate column got both, for a 2.46x larger index.
• toLowerCase() is not case folding, and neither is a round trip. 'Kadıköy'.upper().casefold() is 'kadiköy' — the dotless ı is gone.
• Normalisation cannot fix typos. That class scored 0.0% under every pipeline. It is a different tool.

How was this measured?

47 documents, written for this article: English and Vietnamese product names, personal names, addresses and support phrases, plus three Chinese, three Thai, and one each in German, Turkish and Greek, all stored in NFC. From each, nine generators produce the string a user would plausibly type. A variant is kept only when it actually differs from the stored document, which is why the class sizes differ:

That is 297 queries. Each is normalised by the pipeline under test, wrapped as an FTS5 phrase, and counted a hit if its source document comes back at all. The tokenizer is pinned to unicode61 remove_diacritics 0 so the measurement is about your pipeline and not a hidden one.

Which variants actually break search?

This table is the article. Read the columns against each other.

Four things in there surprised us.

Whitespace collapse changed nothing — 51.5% before and after. A tokenizing index has already thrown whitespace away; a doubled space is not a token. It is pure ceremony here, and worth 16.5 points in the exact-match model below.

Punctuation folding was the single biggest win at 14.8 points, entirely from the full-width class going from 4.3% to 100.0%. Full-width characters are what a CJK input method emits when the user forgets to switch modes, and Ｐｒｏ ２ is as far from Pro 2 as a different alphabet. Nobody puts this step in their pipeline. It outscored case folding by 37 to 1.

Case folding bought 0.4 points, because unicode61 folds case itself. What is left is the residue the tokenizer's own fold misses.

The typo class stayed at 0.0%. Normalisation is exact matching on a canonical form; it has nothing to say about a transposed letter. The lone 2.1% under the mark-stripping pipelines is instructive: our Thai address ถนนสุขุมวิท and its transposed variant strip to the same mark-free string, so removing marks repaired a typo by destroying the distinction that made it one. That is the whole trade-off in a single cell.

Run the same 297 queries against a plain dictionary lookup — the model your "find customer by exact name" endpoint actually uses — and it collapses (columns abridged; the overall column still covers all 297 queries):

15.8%. Five in six realistic query variants fail an un-normalised key lookup. When your support tool says "no customer found", this table is the reason far more often than the customer not existing.

Should you strip Vietnamese diacritics when indexing?

A genuine trade, not a best practice. Vietnamese tone and vowel marks are not decoration: hoa (flower), hòa (peace), hóa (chemistry), hỏa (fire) and họa (disaster) are five words that fold to one string — and Vietnamese users type without marks routinely, because it is faster. We built 20 short documents around four minimal-pair groups (hoa/hòa/hóa/hỏa/họa, ma/má/mà/mả/mã/mạ, bò/bó/bỏ/bọ, chi/chí/chì/chỉ/chị), one distinct sense each, and measured both directions.

Stripping marks at index time bought 85 points of recall on accent-free queries and cost 80 points of precision on accented ones. On the folded index an accented query returned 5.10 documents on average where exactly one was right; on the accent-preserving index, 1.00. A user who took the trouble to type hòa bình gets four wrong senses for their effort.

The row worth staring at is the third. "Search the accented column, fall back to the folded one if empty" is the obvious design and it does not work — 35.0%, barely better than doing nothing. The fallback never fires, because an accent-free Vietnamese query is usually itself a valid word: hoa matches the flower document, the result is not empty, and the four documents the user might have wanted are never looked for. Union the two columns and rank accented hits first instead: same recall as destructive folding, five times the precision.

There is a second Vietnamese trap in the pipeline itself. NFD + strip marks recovered only 59.3% of accent-free queries, and every one of the 11 misses contained đ or Đ — Đỗ Thị Kim Oanh, Trần Văn Đức, Đường Nguyễn Huệ. U+0111 LATIN SMALL LETTER D WITH STROKE has no canonical decomposition, so NFD leaves it alone, and so does every "strip the combining marks" snippet on the internet. It needs its own two-entry translation table.

The tokenizer will not save you either. FTS5's unicode61 defaults to remove_diacritics 1, which with no application-level normalisation answered 25.9% of accent-free queries and 29.2% of NFD ones. remove_diacritics 2 reaches 44.4% and 95.8% — still not 100%, because of đ again. A half-done fold is the worst option: you lose the precision without gaining the recall.

Is NFC or NFD the right form to store?

Store NFC, normalise at every edge, and do it before the string is used as a key or compared.

24 of our 47 documents (51.1%) are not byte-identical in NFC and NFD — and that is 19 of 19 Vietnamese documents against 0 of 6 Chinese and Thai ones, so "we have not seen this bug" mostly means "we have not shipped to Vietnam yet". Nguyễn is 6 characters and 8 bytes in NFC, 8 characters and 10 bytes in NFD. They render identically. n == d is False, a dict keyed on one misses the other, and new Set([n, d]).size is 2. Over the corpus NFD costs 8.8% more bytes for the same text. One normalize('NFC') on both sides makes them equal.

We expected to blame the filesystem here, and could not. The received wisdom is that macOS hands back NFD — true of HFS+, and still the usual explanation. On APFS under macOS 26.4.1, writing Nguyễn.txt in NFC and reading the directory back gave the identical NFC bytes, and opening the NFD spelling of the same name also succeeded: APFS is normalisation-preserving and normalisation-insensitive. The filesystem is no longer where NFD enters your data. Clipboards, keyboards, old archives and anything that touched HFS+ still are — and the moment those strings reach a database the insensitivity stops. When the bytes are wrong rather than the form, the damage is different in kind: why your CSV turns into question marks.

Why doesn't lowercasing work outside ASCII?

Because lowercasing is a rendering operation and case folding is a matching operation, and the two disagree.

'Straße'.lower() is 'straße', six characters; .casefold() is 'strasse', seven. Only the fold makes the pair equal, because folding may change length. Greek is the same story: 'ΟΔΟΣ'.lower() gives final sigma ς, .casefold() gives σ, and only the second matches what a user typed mid-word.

İstanbul goes the other way. Both .lower() and .casefold() turn İ into i + U+0307 COMBINING DOT ABOVE — nine characters, not eight — so it is the mark-stripping step, not the case step, that rescues it. Kadıköy is beyond all of them: .upper() maps dotless ı to I, .casefold() maps I to dotted i, and the round trip lands on kadiköy. That one string is the entire residual miss in our case class at 97.6%.

JavaScript has no case folding at all. String.prototype.toLowerCase is the only tool, and it fails all four pairs. The closest substitute is new Intl.Collator('en', {sensitivity: 'base'}), which reports equality for three of the four — but a collator is a comparator, not a key function. You cannot put its verdict in an index. If your normalisation runs in Node, either build the fold table yourself or do the normalising where the index is.

What does normalisation cost?

Nothing you will notice, which is worth saying because "we skipped it for performance" is a common excuse. Over 20,000 synthetic Vietnamese records (653,761 characters, 32.7 each), best of five passes:

Query side, 5,000 short queries: 0.589 µs accent-keeping, 1.801 µs with mark stripping — under 2% of a 0.106 ms FTS5 lookup. Storage is the real bill:

The duplicate column costs 2.46x, not 2x — FTS5 pays a per-column price on every posting. Querying both columns with OR took 0.204 ms against 0.106 ms, 1.92x. At 20,000 records that is 4 MB and a fifth of a millisecond, a trade almost everyone should take. At a hundred million it is a real decision.

Check it yourself

Stdlib Python, no network, about a second. It needs a sqlite3 module with FTS5 — the python.org and Homebrew builds have it, Node 23's node:sqlite does not.

#!/usr/bin/env python3
# normcheck.py — recall by query-variant class, per normalisation pipeline
import sqlite3, unicodedata as ud
from collections import defaultdict

DOCS = ["Titan X9 Cordless Drill", "O'Brien's Coffee Roaster 250g",
        "Ultra-Light Camping Tent", "Nồi cơm điện Hoà Bình 1.8 lít",
        "Nguyễn Thị Hồng Nhung", "Trần Văn Đức", "Đỗ Thị Kim Oanh",
        "123 Đường Nguyễn Huệ, Quận 1, TP. Hồ Chí Minh",
        "Không đăng nhập được vào tài khoản", "Siobhán O'Connor",
        "北京市朝阳区建国路88号", "ถนนสุขุมวิท ซอย 11 กรุงเทพ",
        "Straße des 17. Juni 135", "İstanbul Kadıköy Şubesi"]
DOCS = [ud.normalize("NFC", d) for d in DOCS]

FOLD = str.maketrans({**{c: "'" for c in "‘’‚‛´′"}, **{c: '"' for c in "“”„‟"},
                      **{c: "-" for c in "‐‑‒–—―−"},
                      **{chr(c): chr(c - 0xFEE0) for c in range(0xFF01, 0xFF5F)},
                      "　": " ", " ": " ", "\t": " "})
WIDE = str.maketrans({**{chr(c - 0xFEE0): chr(c) for c in range(0xFF01, 0xFF5F)},
                      " ": "　"})
DSTROKE = str.maketrans({"đ": "d", "Đ": "D"})          # NFD will not do this
strip = lambda s: ud.normalize("NFC", "".join(
    c for c in ud.normalize("NFD", s) if not ud.combining(c)))
ws = lambda s: " ".join(s.split())

PIPELINES = [
    ("raw", lambda s: s),
    ("casefold", str.casefold),
    ("NFC", lambda s: ud.normalize("NFC", s)),
    ("NFD+strip marks", strip),
    ("whitespace", ws),
    ("punct fold", lambda s: s.translate(FOLD)),
    ("combined", lambda s: ws(ud.normalize("NFC", s).casefold().translate(FOLD))),
    ("combined+strip", lambda s: ws(strip(ud.normalize("NFC", s))
                                    .translate(DSTROKE).casefold().translate(FOLD))),
]
VARIANTS = [
    ("exact", lambda s: s),
    ("case", str.upper),
    ("NFD query", lambda s: ud.normalize("NFD", s)),
    ("no diacritics", lambda s: strip(s).translate(DSTROKE)),
    ("whitespace", lambda s: "  " + s.replace(" ", "   ", 1) + "\t"),
    ("quotes/dashes", lambda s: s.replace("'", "’").replace("-", "–")),
    ("full-width", lambda s: s.translate(WIDE)),
]
Q = [(i, cls, v(d)) for i, d in enumerate(DOCS) for cls, v in VARIANTS
     if cls == "exact" or v(d) != d]
CLS = [c for c, _ in VARIANTS]

print(f"sqlite {sqlite3.sqlite_version}  docs {len(DOCS)}  queries {len(Q)}")
print("| pipeline | " + " | ".join(CLS) + " | overall |")
print("|" + "---|" * (len(CLS) + 2))
for name, norm in PIPELINES:
    db = sqlite3.connect(":memory:")
    db.execute("CREATE VIRTUAL TABLE d USING fts5(body,"
               " tokenize = 'unicode61 remove_diacritics 0')")
    db.executemany("INSERT INTO d(rowid, body) VALUES (?,?)",
                   [(i, norm(d)) for i, d in enumerate(DOCS)])
    hit, tot = defaultdict(int), defaultdict(int)
    for did, cls, q in Q:
        tot[cls] += 1
        try:
            r = [x[0] for x in db.execute("SELECT rowid FROM d WHERE d MATCH ?",
                                          ('"' + norm(q).replace('"', '""') + '"',))]
        except sqlite3.OperationalError:
            r = []
        hit[cls] += did in r
    print(f"| {name} | " + " | ".join(f"{100*hit[c]/tot[c]:.1f}" if tot[c] else "-"
                                      for c in CLS) +
          f" | {100*sum(hit.values())/sum(tot.values()):.1f} |")

python3 normcheck.py

On this machine, the 14-document subset:

sqlite 3.53.4  docs 14  queries 74
| pipeline        | exact | case | NFD query | no diacritics | whitespace | quotes/dashes | full-width | overall |
| raw             | 100.0 | 83.3 |       0.0 |           0.0 |      100.0 |         100.0 |        0.0 |    55.4 |
| casefold        | 100.0 | 91.7 |       0.0 |           0.0 |      100.0 |         100.0 |        0.0 |    56.8 |
| NFC             | 100.0 | 83.3 |     100.0 |           0.0 |      100.0 |         100.0 |        0.0 |    66.2 |
| NFD+strip marks | 100.0 | 83.3 |     100.0 |          44.4 |      100.0 |         100.0 |        0.0 |    71.6 |
| whitespace      | 100.0 | 83.3 |       0.0 |           0.0 |      100.0 |         100.0 |        0.0 |    55.4 |
| punct fold      | 100.0 | 83.3 |       0.0 |           0.0 |      100.0 |         100.0 |      100.0 |    74.3 |
| combined        | 100.0 | 91.7 |     100.0 |           0.0 |      100.0 |         100.0 |      100.0 |    86.5 |
| combined+strip  | 100.0 | 91.7 |     100.0 |         100.0 |      100.0 |         100.0 |      100.0 |    98.6 |

Add your own documents to DOCS and your own generators to VARIANTS. The number that tells you what to build is the column, not the total.

Where this goes next

Fix the strings before you touch the ranker. When retrieval is still wrong afterwards the failure has moved a layer down — and each retriever fails a different class of query almost totally, which no fold repairs. Choosing the fold also means knowing what you hold: detecting the language of a short string decides whether đ → d is a repair or vandalism.

This is the index layer under MedSearch, where the query arrives from a phone keyboard in airplane mode and there is no second attempt.