Cleaning Up 30 Years of Text, One Row at a Time

July 17, 2026

by the Firefly Engineering Team

Every publisher with a long history eventually inherits the same headache: a database table that has been quietly collecting text since the days of dial-up. Some of it came in as Latin-1. Some came in as Windows-1252 with all its “smart” curly quotes. Some arrived already broken from a previous migration nobody wrote down. And some of it is perfectly fine and just wants to be left alone. Sorting that out by hand isn't realistic, so we built a small pipeline to do it for us carefully, and with a paper trail.

The Problem, In Plain Terms

We had a table that needed to move to utf8mb4 so new content can use the full range of modern Unicode going forward. Simple enough on paper. But you can't just flip the column charset and call it done. MariaDB will happily reinterpret decades-old bytes through the wrong lens, and suddenly your smart quotes are gibberish. The real job wasn't the schema change. It was making sure the text underneath it was actually readable first.

What We Actually Built

The setup has two halves. A Python-based sanitizer (wrapped in a small bash script) does the actual text surgery. A PHP migration script drives the whole process: it reads the table's real charset out of INFORMATION_SCHEMA instead of assuming one, pulls every row, runs each text field through the sanitizer, writes back only what changed, and converts the table's collation once everything is clean.

The sanitizer itself is deliberately boring and predictable. It:

  • Tries decoding as UTF-8 first, then cp1252, then plain Latin-1, in that order.
  • Unescapes HTML entities, including the annoying double-encoded kind like ’.
  • Looks for classic misdecoded-text patterns and repairs them when it's confident.
  • Swaps curly quotes, em dashes, and other “fancy” punctuation for their plain ASCII equivalents.
  • Strips leftover control characters, but leaves tabs and line breaks alone.
  • Replaces anything it truly can't map with a visible ? instead of quietly mangling it.

Why ASCII, and Why That's a Tradeoff Worth Naming

Here's the honest part: converting everything down to plain ASCII does lose some nuance. A true em dash becomes a hyphen. A curly quote becomes a straight one. Purists will wince. But for a decades-deep archive of classified-style copy, consistency wins over typographic perfection. You want every ad to render the same way in every system, forever, not just the ones written after 2010. We made that tradeoff on purpose, and we made sure the script says so out loud every time it runs.

What to Watch Out For

A few things bit us early, and they're worth flagging for anyone doing similar work:

  • The charset column can lie. A table labeled Latin-1 may still have rows that snuck in as UTF-8 from a different era of the app.
  • Not all corruption is fixable. Some encoding damage is genuinely lossy. You can guess, but you can't always recover the original character with certainty.
  • Entities can be nested. We ran into text that had been HTML-escaped more than once, so the unescape step needed to loop, with a hard limit so it doesn't run forever on garbage.
  • Spell-check flags are hints, not verdicts. Dictionaries don't know antique-trade jargon, brand names, or old abbreviations, so we treat a high miss rate as "someone should look," not "this is wrong."
  • The final schema conversion can lock the table. Run it in a quiet window, not the middle of the day.

Letting a Human Have the Last Word

We didn't want this to be a script that runs once and hopes for the best. So it also scores every sanitized field for suspicious signs: broken words, leftover garbled-encoding markers, strings of punctuation that look like placeholder junk, and word lists Aspell doesn't recognize. Anything that crosses a threshold gets flagged in the terminal output and written to a JSONL file afterward, so an editor can go straight to the rows that actually need a second look instead of re-reading the whole table.

The Python Modules Doing the Work

A quick rundown of what's under the hood, since none of it is exotic on purpose:

  • html: unescapes entities like ’ and numeric codes back into real characters.
  • re: spots garbled-encoding fingerprints and other suspicious patterns quickly.
  • unicodedata: handles the heavy lifting: decomposing accented characters, stripping diacritics, and naming characters for the report.
  • tempfile: writes output to a temp file first, so a crash mid-write never leaves a half-finished file behind.
  • pathlib.Path: keeps file handling readable instead of a pile of string concatenation.
  • collections.Counter: tallies every substitution so the report can say exactly what changed and how often.
  • json: formats replacement values cleanly in the log without us hand-rolling escaping.
  • os / sys: the plumbing for reading arguments, managing file descriptors, and exiting with a status code the shell script can check.

Our Actual Advice

Back up the table before you touch it. Run this against a copy first, not production. Skim the review report before you trust it. And don't be surprised if a handful of rows just need a person to look at them. That's not a bug in the script, that's thirty years of history doing what history does. Get the boring 95% cleaned up automatically, and spend your attention on the interesting 5%.

Glossary

ASCII
The original 128-character text standard. Covers plain English letters, numbers, and basic punctuation. Nothing fancy, but universally readable.
Unicode
The modern standard that covers virtually every character in every language, plus symbols and emoji. It's the destination; ASCII and Latin-1 are just older, narrower stops along the way.
UTF-8
The most common way of encoding Unicode as bytes. Backwards-compatible with plain ASCII, which is why it decodes cleanly most of the time.
utf8mb4
MariaDB and MySQL's name for “actually complete” UTF-8. The older utf8 charset in these databases is a historical trap that can't store every Unicode character; utf8mb4 can.
Latin-1 (ISO-8859-1)
An older single-byte encoding covering Western European characters. Common in older databases and web content from the '90s and 2000s.
cp1252 (Windows-1252)
Microsoft's variant of Latin-1, with curly quotes, em dashes, and a few extra symbols packed into the same byte range. The usual source of “smart punctuation” gone wrong.
Charset / encoding
The rulebook a system uses to turn raw bytes into readable characters. Read the same bytes with the wrong rulebook, and you get garbage.
Collation
The rules a database uses to sort and compare text in a given charset, including case sensitivity and accent handling.
Garbled encoding
The junk text you get when data encoded one way gets decoded another. The classic look is something like "it’s broken."
HTML entity
A text stand-in for a character, like ’ for a curly apostrophe. Common in old web content that stored formatted text as-is.
Sanitizer
Our term for the script that normalizes and cleans up text: decoding it correctly, fixing what it can, and flagging what it can't.
INFORMATION_SCHEMA
A built-in set of database tables that describe the database itself (table names, column types, charsets, and more). We use it to ask MariaDB what encoding a table actually claims to be, rather than guessing.
Aspell
An open-source spell-checking engine. We use it here as a quality signal, not a judge. A high rate of unrecognized words is a hint to have a human look, not proof something's wrong.
JSONL
“JSON Lines,” a file format with one JSON object per line. Easy to generate, easy to scan, easy to feed into another tool later.

← Back to Learn

Firefly Tree