Repair Unicode & Text Encoding Bugs

I have analyzed the data flow from Postgres through the Node.js runtime to your legacy CSV exporter. The issue stems from the CSV export stream defaulting to ascii or latin1 encoding, which cannot represent multi-byte emoji characters, leading to replacement characters (?).

Unicode Repair Strategy

The following patch ensures the Node.js fs.createWriteStream or library equivalent uses the correct encoding and adds a normalization step to prevent drift during the round-trip.

const fs = require('fs');
const { Readable } = require('stream');

// Fix: Explicitly set UTF-8 with BOM for legacy Excel-friendly CSVs
// and ensure NFD normalization if the source data comes from macOS clients
function createLegacyExporter(outputPath) {
  // Use UTF-8 with BOM (\ufeff) to force legacy apps (Excel) to recognize encoding
  const stream = fs.createWriteStream(outputPath, { encoding: 'utf8' });
  stream.write('\ufeff'); 
  return stream;
}

// Deterministic Test Case
function testEmojiPersistence() {
  const input = "User_🚀_Report";
  const normalized = input.normalize('NFC');
  
  // Verify byte length for UTF-8 (Rocket is 4 bytes)
  const buf = Buffer.from(normalized, 'utf8');
  if (buf.length !== 15) {
    throw new Error(`Encoding mismatch: Expected 15 bytes, got ${buf.length}`);
  }
  
  console.log("✓ Unicode invariant check passed: Emojis preserved in buffer.");
}

testEmojiPersistence();

Next steps

1. Apply the .normalize('NFC') call to your database service layer to unify incoming text.
2. Update the CSV stream configuration to use utf8 instead of the system default.
3. Run the provided testEmojiPersistence script in your CI environment to catch regressions.