#Data Quality
Migrating an Old .aspx Site to a JSON Catalog and Filtering Images
The owner of an old .aspx site asked me to rebuild it, but more than a decade of content was in a database I could no longer access. I collected the pages from the site itself to build a JSON catalog that preserved every legacy_id, then wrote a three-layer image filter to keep stray QR codes from becoming article covers.
Reverse Engineering Spreadsheet Constants into Formulas
A sheet has a criteria table sitting beside the calculations, but no VLOOKUP connects them. People read the table and type values into cells themselves, and mistakes go unnoticed. This article covers separating formula cells from manually entered cells, then reconstructing a single formula that matches all 16 values in a multiplier column.
Making 20,000+ API Calls That Resume Without Leaving Corrupt Files
Generating 26,850 files through an API limited to 20 calls per minute requires three pieces—a system-wide throttle, resuming from what is already on disk, and a separate file validator—because files interrupted by network failures still have the right extension and a nonzero size.
The App Uses UTC, People Use Thai Time — 7 Hours Off, No Errors
Scheduled for three in the afternoon, published at ten at night. No errors, nothing wrong in the logs, because the app stores UTC while the person entering the time thinks in Thai time. This article traces the issue from an env setting that has no effect to the single line that writes Thai wall-clock time into a column everyone reads as UTC.
Moving JSON Time Series to Columns Without Changing the Dashboard
I stored entire device frames as JSON arrays at one row per second. After two weeks, the tables had grown to 183 MB at the endpoint and 625 MB on the server, with JSON_EXTRACT queries that could not use an index — here is how I moved the data into int columns and had the API rebuild a sparse array without changing a single line of the dashboard.
Secrets Set to 600, but 0644 Logs Go Offsite Every Night
The .env is encrypted and permissions are locked down on every secret file, but customer data still leaks off the machine every night through something nobody classifies as confidential: logs that every account on the machine can read, bundled into backups uploaded to object storage. Here is how I stop it at the source by logging only result codes.
News Stuck for Three Days Because the Feed Wasn't Sorted by Date
The news site's homepage showed the same day's stories for three days straight, even though scheduled jobs ran, translations finished, and every step reported success. The causes were an RSS feed sorted by relevance rather than date, string sorting of pubDate that actually sorted by weekday name, and requesting too few stories, leaving nothing after filtering before the rest even got a look.
Thai Data Migration Traps: VARCHAR, Idempotency, and OCR Vowels
Lessons from importing and migrating hundreds of thousands of Thai data rows — undersized VARCHAR columns silently truncating synthetic keys into collisions, idempotency requiring DELETE before INSERT to stop totals reaching 200%, collation treating su001 and SU001 as equal while storing both, and Thai vowels corrupted by OCR that need a normalization map before insert