Encoding Transformation During Data Migration

Data migrations are where encoding bugs hide. When a legacy CRM migrated 2.3 million customer records from an on-premise MySQL database to a cloud PostgreSQL instance, encoding inconsistencies caused 14,000 records to fail validation. The engineering team used Dev Tools ! to diagnose the root causes and build a transformation pipeline, reducing a projected 3-day debugging effort to 6 hours.

The Migration Problem

The legacy system had accumulated encoding debt over 12 years. Different parts of the system had written data using different encodings:

• Customer names from the original system: ISO-8859-1 (Latin-1)
• Names imported from a European acquisition: Windows-1252
• Names entered through the web form after 2019: UTF-8
• Profile images stored as: raw Base64 in a TEXT column (no data URI prefix)
• Phone numbers stored as: HTML entity-encoded strings (e.g., +1-555-0100)

The migration script assumed UTF-8 throughout. The result: corrupted characters in 14,000 records, broken image rendering for 8,200 profiles, and phone number search failures across the board.

Diagnosis with Dev Tools !

Step 1: Character Encoding Identification

The team copied suspect character sequences from the database dump into the Base64 Encoder to inspect raw byte values. For a customer named "André":

ISO-8859-1: 41 6E 64 72 E9
UTF-8:      41 6E 64 72 C3 A9

When ISO-8859-1 bytes (E9) are read as UTF-8:
E9 alone is an incomplete multi-byte sequence → replacement character (U+FFFD)
Result in database: "Andr<REPLACEMENT>"

This confirmed the root cause: the migration was reading ISO-8859-1 bytes as UTF-8.

Step 2: HTML Entity Decoding

The team pasted phone numbers into the HTML Entity Decoder to verify their hypothesis:

Stored value:  +1-555-0100+ext.7
Decoded:       +1-555-0100+ext.7

Stored value:  (555) 867‑5309
Decoded:       (555) 867‑5309  ← non-breaking hyphen U+2011, not ASCII -

This explained the search failures: the phone numbers contained Unicode non-breaking hyphens that didn't match simple ASCII hyphen queries.

Step 3: Base64 Image Validation

Profile images were stored as raw Base64 without the data URI prefix. The team pasted sample values into the Base64 Decoder to verify the format:

Stored: /9j/4AAQSkZJRgABAQEASABIAAD/...   ← JPEG header visible after decode
Fix needed: prepend "data:image/jpeg;base64," before serving

They also found ~200 records where the Base64 had been double-encoded (encoded twice), producing valid Base64 that decoded to more Base64:

Double-encoded: L3m0pIVFSUhNSGAEUAQDAVQIAA...
First decode:   /9j/4AAQSkZJRg...  (still Base64)
Second decode:  JPEG binary data (correct image)

The Fix

Armed with precise diagnoses, the team wrote a targeted transformation script:

// 1. Fix character encoding for legacy records
function fixEncoding(rawBuffer) {
  // Records before 2019 are Latin-1
  const latin1Text = Buffer.from(rawBuffer).toString("latin1");
  // Re-encode as UTF-8 for PostgreSQL
  return Buffer.from(latin1Text, "utf8").toString("utf8");
}

// 2. Decode HTML entities in phone numbers
import { decode } from "html-entities";
function normalizePhone(phone) {
  const decoded = decode(phone);
  // Normalize all dash variants to ASCII hyphen
  return decoded.replace(/[‐-―−-]/g, "-");
}

// 3. Fix base64 images
function normalizeImage(b64) {
  // Check for double-encoding
  try {
    const firstDecode = atob(b64);
    // If it's still valid base64, it was double-encoded
    atob(firstDecode);
    return `data:image/jpeg;base64,${firstDecode}`;
  } catch {
    return `data:image/jpeg;base64,${b64}`;
  }
}

Results

Issue	Records Affected	Time to Diagnose	Time to Fix
ISO-8859-1 / Latin-1 names	9,200	45 min	2 hours
HTML-entity phone numbers	3,800	30 min	1 hour
Base64 image prefix missing	8,200	20 min	30 min
Double-encoded images	210	20 min	30 min

Total: 14,000 records fixed in one day. Projected without diagnostic tooling: 3+ days.

Key Lessons

1. Sample before migrating: always inspect raw byte values of a sample before writing transformation code
2. Encoding accumulates: systems built over years accumulate encoding inconsistencies that a single migration exposes all at once
3. HTML entities in unexpected places: data entered through web forms can carry HTML entities even in fields like phone numbers
4. Double-encoding happens: when data passes through multiple serialization layers, check for multiple levels of encoding

Tools Used

• Base64 Encoder/Decoder — inspect and validate Base64 image data
• HTML Entity Decoder — decode HTML entities in data fields
• URL Encoder/Decoder — validate percent-encoded data in URLs