Slugs Carrying Curly-Quote Bytes: Fixed via HEX, Not Regex

Tonight I ran SELECT HEX(name) on the KotaPortal dev database, just to make sure no weird characters were left over from an old data import. The result showed the opposite: seven public venue slugs carried the bytes E2 80 99 in the middle of their URLs. Those bytes are not random noise; they are U+2019, the right single quotation mark, which should never reach a URL. Two other rows carried E2 80 93, an en dash.

My first guess: the column had the wrong charset. The suspicion felt fair, old columns look identical to three-byte utf8, which is routinely accused of truncating characters. If storage truncated, no wonder the data turned into soup.

That guess collapsed after reading the MySQL manual [2]: for BMP characters like U+2019 and U+2013, utf8mb3 and utf8mb4 have identical storage, same code values, same encoding, same length. Storage never truncated anything. The curly-quote bytes were written to the database intact, which means the import path wrote already-corrupted data in the first place. The column was innocent; the incoming data was not. And because UTF-8 guarantees the full ASCII range stays exactly as-is [1], ASCII slugs became the safest repair target.

Regex Rejected, Manual Mapping Chosen

At this point it is tempting to write one regex-based REPLACE function: detect every non-ASCII character in a slug, replace automatically. This commit explicitly rejects that path. Only seven rows were affected, and for a count that small, a manual per-row ASCII transliteration map is far more defensible: a human can read the diff, the blast radius is limited to the listed rows, and no "close enough" script touches anything else.

The mechanism lives in the WHERE clause. Old slugs are not matched as plain strings but byte-for-byte via HEX(slug). The reason is simple: a database connection with a different charset setting, or a SQL file passing through a different editor, can reshape curly quotes in transit. Hex representation is immune to all of that. A row whose id matches but whose HEX does not means something else already fixed it, so it is skipped, and that is exactly what makes this migration idempotent: run it twice and the end state is the same. A collision precheck for the new slugs also ran first on the dev database and found zero conflicting rows.

A Byte-Identical Way Back

The migration runs on the golang-migrate convention [3]: numbered up and down SQL files, executed in version order. What makes this migration worth copying is its symmetry down to the byte. Up: set the new slug with a HEX guard in WHERE. Down: restore the old bytes exactly via CONVERT(UNHEX(...) USING utf8mb4), matched again against the new slug. No "roughly back to how it was"; the rollback restores the same bytes the database held before the migration.

-- up: repair one slug, byte-exact guard
UPDATE entities SET slug = 'k-prima-futsal'
WHERE id = 336 AND HEX(slug) = '6BE280997072696D612D66757473616C';

-- down: restore the original bytes, byte-identical
UPDATE entities
SET slug = CONVERT(UNHEX('6BE280997072696D612D66757473616C') USING utf8mb4)
WHERE id = 336 AND slug = 'k-prima-futsal';

The public-page impact was visible immediately too: the module changelog notes that the old curly-quote URLs returned 404, so a QA re-run of the venue module was scheduled specifically to check the public links of these seven venues once the migration ran.

The same commit also closed two other holes on the infrastructure side. The db-push to staging now excludes the users table DATA: password hashes and dev accounts never overwrite staging accounts again, while the users schema is still created empty via a --no-data dump so the staging API does not crash on boot. The E2E config gained a load-time guard: if E2E_BASE_URL points anywhere but the machine the tests run on, the config refuses to run at all, because E2E tests write data and must never be pointed at a shared server.

The lesson I took home: repairing wrong data is precision work, not pattern work. A regex that roughly matches saves ten minutes and opens the door to invisible false positives. A byte-exact guard, a manual map, a collision precheck, and a byte-identical way back make this seven-slug repair explainable line by line to anyone who asks.

Sources

[1] RFC 3629: UTF-8, a transformation format of ISO 10646
[2] MySQL 8.0 Manual: The utf8mb4 Character Set
[3] golang-migrate/migrate