ADR-067: Text Sanitization and Escaping for RAG Ingestion

<!-- MADR 3.0 Template - Markdown Any Decision Records --> <!-- Reference: https://adr.github.io/madr/ -->

Status

Proposed

Date

2025-12-26

Deciders

• Norbert Bede
• Development Team

Context and Problem Statement

The RAG ingestion pipeline stores text content from multiple sources (iDempiere database, wiki, K_Entry) into the cli_embeddings PostgreSQL table. This content is stored as-is without sanitization, leading to database backup/restore failures when using pg_dump:

1. Unclosed quotes issue: Text content containing unescaped single quotes (') causes pg_dump COPY format to produce malformed SQL that fails on import
2. URL-encoded content: Wiki content may contain URL-encoded characters (%20, %3D) that aren't decoded, reducing embedding quality
3. Control characters: Null bytes (\0), form feeds, and other control characters can corrupt PostgreSQL TEXT columns
4. Backslash sequences: Unescaped backslashes in text can be misinterpreted as escape sequences in COPY format

Observed failure: When running pg_dump on the vector database and attempting to restore, import fails with "unterminated quoted field" or similar errors due to special characters in text_segment column.

Decision Drivers

• Database Portability: pg_dump/pg_restore must work reliably for backups
• Embedding Quality: Clean, normalized text produces better semantic embeddings
• Data Integrity: Prevent corruption from control characters
• URL Readability: Decode URL-encoded content for human-readable embeddings
• Backward Compatibility: Existing data should be upgradable via re-ingestion
• Performance: Sanitization overhead must be minimal

Considered Options

1. Add TextSanitizer utility with comprehensive sanitization - Centralized sanitization before storage
2. Use pg_dump with --inserts flag - Avoid COPY format issues
3. Escape at query time only - Fix pg_dump output post-hoc
4. Store content as Base64 - Avoid escaping issues entirely

Decision Outcome

Chosen option: "Add TextSanitizer utility with comprehensive sanitization", because:

• Fixes the root cause at ingestion time
• Improves embedding quality with clean text
• Single point of control for all sanitization rules
• Transparent to downstream consumers (embeddings, search)

Confirmation

The decision is confirmed when:

• [ ] pg_dump of cli_embeddings table produces valid SQL
• [ ] pg_restore successfully imports the dump without errors
• [ ] URL-encoded characters are decoded in stored content
• [ ] No null bytes or control characters in stored text
• [ ] Search results show decoded, readable content
• [ ] Unit tests cover all sanitization cases

Pros and Cons of the Options

Option 1: TextSanitizer Utility (chosen)

Centralized utility class that sanitizes text before storing in PGVector.

• Good, because fixes root cause at ingestion
• Good, because improves embedding quality
• Good, because single point of control
• Good, because testable in isolation
• Good, because transparent to consumers
• Neutral, because requires re-ingestion of existing data
• Bad, because adds processing overhead (minimal)

Option 2: pg_dump with --inserts

Use INSERT statements instead of COPY format for dumps.

• Good, because no code changes required
• Good, because INSERT handles quoting automatically
• Bad, because much larger dump files
• Bad, because slower restore performance
• Bad, because doesn't improve embedding quality
• Bad, because URL-encoded content still present

Option 3: Post-hoc Escaping

Fix pg_dump output with sed/awk before importing.

• Good, because no code changes
• Bad, because fragile and error-prone
• Bad, because doesn't improve embeddings
• Bad, because requires manual intervention
• Bad, because may corrupt valid data

Option 4: Base64 Encoding

Store all text as Base64 encoded strings.

• Good, because eliminates all escaping issues
• Bad, because text not searchable
• Bad, because increases storage size 33%
• Bad, because breaks PGVector text similarity
• Bad, because metadata/debugging impossible

Architecture

┌─────────────────────────────────────────────────────────────────────────────┐
│ RAG INGESTION PIPELINE WITH TEXT SANITIZATION                               │
├─────────────────────────────────────────────────────────────────────────────┤
│                                                                              │
│ ┌─────────────────┐  ┌─────────────────┐  ┌─────────────────┐              │
│ │  WikiIngestor   │  │ KEntryIngestor  │  │ADMetadataIngestor│              │
│ │                 │  │                 │  │                  │              │
│ │ Wiki HTML/Text  │  │ BLK/GFM/HTM     │  │ AD_Table, etc.   │              │
│ └────────┬────────┘  └────────┬────────┘  └────────┬─────────┘              │
│          │                    │                    │                         │
│          └────────────────────┼────────────────────┘                         │
│                               │                                              │
│                               ▼                                              │
│          ┌─────────────────────────────────────────────────────┐            │
│          │                 TextSanitizer                        │            │
│          │                                                      │            │
│          │  1. URL Decode:  %20 → space, %3D → =               │            │
│          │  2. Remove Nulls: \0 → (removed)                    │            │
│          │  3. Normalize Whitespace: multiple spaces → single  │            │
│          │  4. Escape Backslashes: \ → \\                      │            │
│          │  5. Remove Control Chars: \x00-\x1F (except \n\t)   │            │
│          │  6. Normalize Line Endings: \r\n → \n               │            │
│          │  7. Trim: leading/trailing whitespace               │            │
│          │                                                      │            │
│          │  Optional (per-source):                             │            │
│          │  - HTML Entity Decode: & → &                    │            │
│          │  - Unicode Normalization: NFC form                  │            │
│          │                                                      │            │
│          └──────────────────────┬──────────────────────────────┘            │
│                                 │                                            │
│                                 ▼                                            │
│          ┌─────────────────────────────────────────────────────┐            │
│          │               TextSegment.from(sanitizedText)        │            │
│          │               + Metadata (includes sanitization_v)   │            │
│          └──────────────────────┬──────────────────────────────┘            │
│                                 │                                            │
│                                 ▼                                            │
│          ┌─────────────────────────────────────────────────────┐            │
│          │           EmbeddingStoreProvider (PGVector)          │            │
│          │                                                      │            │
│          │   cli_embeddings table:                             │            │
│          │   - text_segment TEXT (sanitized, pg_dump safe)     │            │
│          │   - embedding vector(1024)                          │            │
│          │   - metadata JSONB (includes sanitization_version)  │            │
│          └─────────────────────────────────────────────────────┘            │
│                                                                              │
└─────────────────────────────────────────────────────────────────────────────┘

Implementation

TextSanitizer Utility

File: src/main/java/org/idempiere/cli/rag/util/TextSanitizer.java

package org.idempiere.cli.rag.util;

import jakarta.enterprise.context.ApplicationScoped;
import org.jboss.logging.Logger;

import java.net.URLDecoder;
import java.nio.charset.StandardCharsets;
import java.text.Normalizer;
import java.util.regex.Pattern;

/**
 * Sanitizes text content before storing in RAG embedding store.
 *
 * <p>Addresses issues that cause pg_dump/pg_restore failures and
 * improves embedding quality by normalizing text.</p>
 *
 * @see <a href="../../../../../docs/adr/067-rag-text-sanitization.md">ADR-067</a>
 */
@ApplicationScoped
public class TextSanitizer {

    private static final Logger LOG = Logger.getLogger(TextSanitizer.class);

    /** Current sanitization version for tracking in metadata */
    public static final String SANITIZATION_VERSION = "1.0";

    // Pattern to match URL-encoded sequences
    private static final Pattern URL_ENCODED = Pattern.compile("%[0-9A-Fa-f]{2}");

    // Pattern to match control characters (except newline, tab, carriage return)
    private static final Pattern CONTROL_CHARS = Pattern.compile("[\\x00-\\x08\\x0B\\x0C\\x0E-\\x1F\\x7F]");

    // Pattern to match multiple whitespace
    private static final Pattern MULTIPLE_SPACES = Pattern.compile("[ \\t]{2,}");

    // Pattern to match multiple newlines
    private static final Pattern MULTIPLE_NEWLINES = Pattern.compile("\\n{3,}");

    /**
     * Sanitize text for safe storage and optimal embedding quality.
     *
     * @param text Raw text content
     * @return Sanitized text safe for pg_dump and optimized for embeddings
     */
    public String sanitize(String text) {
        if (text == null || text.isEmpty()) {
            return "";
        }

        String result = text;

        // 1. URL decode if contains encoded sequences
        if (URL_ENCODED.matcher(result).find()) {
            result = urlDecode(result);
        }

        // 2. Remove null bytes (critical for PostgreSQL)
        result = result.replace("\0", "");

        // 3. Remove other control characters (keep \n, \t, \r)
        result = CONTROL_CHARS.matcher(result).replaceAll("");

        // 4. Normalize line endings (Windows → Unix)
        result = result.replace("\r\n", "\n").replace("\r", "\n");

        // 5. Normalize multiple spaces to single
        result = MULTIPLE_SPACES.matcher(result).replaceAll(" ");

        // 6. Normalize multiple newlines to max 2
        result = MULTIPLE_NEWLINES.matcher(result).replaceAll("\n\n");

        // 7. Unicode normalization (NFC form)
        result = Normalizer.normalize(result, Normalizer.Form.NFC);

        // 8. Trim leading/trailing whitespace
        result = result.trim();

        return result;
    }

    /**
     * Sanitize with additional HTML entity decoding.
     * Use for content that may contain HTML entities outside of tags.
     *
     * @param text Raw text content
     * @return Sanitized text with HTML entities decoded
     */
    public String sanitizeWithHtmlDecode(String text) {
        if (text == null || text.isEmpty()) {
            return "";
        }

        String result = text;

        // Decode common HTML entities
        result = result
            .replace("&", "&")
            .replace("<", "<")
            .replace(">", ">")
            .replace(""", "\"")
            .replace("'", "'")
            .replace("'", "'")
            .replace(" ", " ")
            .replace(" ", " ");

        // Then apply standard sanitization
        return sanitize(result);
    }

    /**
     * URL decode text, handling malformed sequences gracefully.
     */
    private String urlDecode(String text) {
        try {
            return URLDecoder.decode(text, StandardCharsets.UTF_8);
        } catch (IllegalArgumentException e) {
            // Malformed URL encoding - try partial decoding
            LOG.debugf("Malformed URL encoding in text, attempting partial decode: %s",
                      e.getMessage());
            return partialUrlDecode(text);
        }
    }

    /**
     * Partial URL decode that handles malformed sequences.
     * Decodes valid sequences, leaves invalid ones as-is.
     */
    private String partialUrlDecode(String text) {
        StringBuilder result = new StringBuilder();
        int i = 0;

        while (i < text.length()) {
            char c = text.charAt(i);

            if (c == '%' && i + 2 < text.length()) {
                String hex = text.substring(i + 1, i + 3);
                try {
                    int code = Integer.parseInt(hex, 16);
                    result.append((char) code);
                    i += 3;
                    continue;
                } catch (NumberFormatException e) {
                    // Not valid hex, keep as-is
                }
            }

            result.append(c);
            i++;
        }

        return result.toString();
    }

    /**
     * Check if text contains potentially problematic content.
     * Useful for logging/monitoring.
     */
    public boolean containsProblematicContent(String text) {
        if (text == null) return false;

        return text.contains("\0") ||
               CONTROL_CHARS.matcher(text).find() ||
               URL_ENCODED.matcher(text).find();
    }
}

Integration Points

Update each ingestor to use TextSanitizer:

ADMetadataIngestor.java:

@Inject
TextSanitizer textSanitizer;

// In ingestion loop:
String content = buildContent(table); // existing method
String sanitizedContent = textSanitizer.sanitize(content);
TextSegment segment = TextSegment.from(sanitizedContent, metadata);

KEntryIngestor.java:

@Inject
TextSanitizer textSanitizer;

// After Markdown conversion:
String markdownContent = convertToMarkdown(textMsg, editMode, kEntryId);
String sanitizedContent = textSanitizer.sanitize(markdownContent);

WikiIngestor.java:

@Inject
TextSanitizer textSanitizer;

// After Tika parsing:
String parsedContent = parser.parse(html);
String sanitizedContent = textSanitizer.sanitizeWithHtmlDecode(parsedContent);

Metadata Tracking

Add sanitization version to metadata for upgrade tracking:

metadata.put("sanitization_version", TextSanitizer.SANITIZATION_VERSION);

This allows identifying which records need re-ingestion when sanitization rules change.

Testing

Unit Tests

File: src/test/java/org/idempiere/cli/rag/util/TextSanitizerTest.java

@QuarkusTest
class TextSanitizerTest {

    @Inject
    TextSanitizer sanitizer;

    @Test
    void testRemovesNullBytes() {
        String input = "Hello\0World";
        assertEquals("HelloWorld", sanitizer.sanitize(input));
    }

    @Test
    void testUrlDecoding() {
        String input = "Hello%20World%21";
        assertEquals("Hello World!", sanitizer.sanitize(input));
    }

    @Test
    void testNormalizesLineEndings() {
        String input = "Line1\r\nLine2\rLine3";
        assertEquals("Line1\nLine2\nLine3", sanitizer.sanitize(input));
    }

    @Test
    void testRemovesControlCharacters() {
        String input = "Hello\u0007World\u001B";
        assertEquals("HelloWorld", sanitizer.sanitize(input));
    }

    @Test
    void testPreservesNewlinesAndTabs() {
        String input = "Line1\n\tIndented";
        assertEquals("Line1\n\tIndented", sanitizer.sanitize(input));
    }

    @Test
    void testNormalizesMultipleSpaces() {
        String input = "Hello    World";
        assertEquals("Hello World", sanitizer.sanitize(input));
    }

    @Test
    void testHtmlEntityDecode() {
        String input = "Hello & World <test>";
        assertEquals("Hello & World <test>",
                    sanitizer.sanitizeWithHtmlDecode(input));
    }

    @Test
    void testMalformedUrlEncoding() {
        String input = "50% complete %ZZ invalid";
        String result = sanitizer.sanitize(input);
        // Should not throw, handles gracefully
        assertNotNull(result);
    }

    @Test
    void testQuotesPreserved() {
        String input = "It's a \"test\" with 'quotes'";
        assertEquals(input, sanitizer.sanitize(input));
    }

    @Test
    void testPgDumpCompatibility() {
        // Simulates content that would break pg_dump
        String problematic = "User's data\0with null\tand\ttabs";
        String sanitized = sanitizer.sanitize(problematic);

        assertFalse(sanitized.contains("\0"));
        assertTrue(sanitized.contains("'"));  // Quotes are valid
        assertTrue(sanitized.contains("\t")); // Tabs are valid
    }
}

Integration Test

@Test
void testPgDumpRestoreAfterIngestion() {
    // 1. Ingest test data with special characters
    // 2. Run pg_dump
    // 3. Restore to test database
    // 4. Verify data integrity
}

Configuration

# Text Sanitization
idempiere.cli.rag.sanitize.enabled=true
idempiere.cli.rag.sanitize.url-decode=true
idempiere.cli.rag.sanitize.html-decode=false  # Per-ingestor override

Migration Strategy

For Existing Data

1. Deploy new version with TextSanitizer
2. Clear and re-ingest:

   idempiere-cli knowledge clear --source all -y
   idempiere-cli knowledge ingest --source all
   

3. Verify pg_dump works:

   pg_dump -t cli_embeddings vector_db > embeddings_backup.sql
   

Rollback

If issues arise, disable sanitization temporarily:

idempiere.cli.rag.sanitize.enabled=false

Performance Impact

Overhead is acceptable for batch ingestion operations.

Related ADRs

• ADR-021 - RAG Architecture
• ADR-040 - K_Entry Content Format Normalization
• ADR-022 - Shared Embedding Infrastructure

References

• PostgreSQL COPY Format
• URL Encoding RFC 3986
• Unicode Normalization
• pg_dump Documentation