Universal Relational Ingestion & Tabular RAG: Announcing the JDBC Repository Connector

1. The Impedance Mismatch: Relational Data vs. Vector RAG

Building Retrieval-Augmented Generation (RAG) systems over relational databases has historically presented an awkward engineering trade-off. Naive approaches often flatten tables into dense comma-separated values (CSVs) or dump raw JSON structures into text splitters.

This approach causes two critical issues:

• Loss of Semantic Density: Dense vector models (such as mxbai-embed-large, text-embedding-3-small, or bge-large-en) perform poorly on unstructured key-value dumps. Embedding models expect cohesive prose, human syntax, and grammatical context to calculate accurate semantic distances.
• Security & Authorization Leakage: Tables often hold row-level permissions (e.g. owner_id, department_id, tenant_id) that are stripped during scraping, resulting in unauthorized retrieval when querying large language models.

The JDBC Repository Connector bridges this divide. It introduces native Dual-Path Ingestion: transforming relational records into rich natural-language narratives for Tabular RAG while routing binary attachments (PDFs, images, office files) directly through Apache Tika via decoupled Claim-Check streaming.

2. Universal SQL Compatibility Across Major Database Engines

Built on standard JDBC 4.2+ specifications and enterprise-grade HikariCP connection pooling, oc-jdbc-repository-connector runs out-of-the-box against all major enterprise database engines:

• PostgreSQL 14–17+ (including pgvector, Citus, and Supabase)
• MySQL 8.x & MariaDB 10/11
• Oracle Database 19c / 21c / 23ai
• Microsoft SQL Server 2019 / 2022 / Azure SQL
• IBM DB2 & Informix
• SQLite 3 & DuckDB (ideal for local testing and embedded analytics)
• Cloud Data Warehouses (Snowflake, Databricks via JDBC)

3. Dual Ingestion Strategies: Table Mode vs. Custom SQL Query Mode

The connector offers two operational strategies configurable via the Admin UI, Spring Boot YAML, or dynamic SCAN_PATH parameters:

A. Table / View Ingestion Mode

Specify a target table or view (e.g. support_tickets or crm.customers). The connector inspects table metadata via DatabaseMetaData.getColumns(), identifies primary keys, and projects all columns into Open Ingestion Standard (OIS) attributes.

B. Custom SQL Query Mode

For complex domain models involving normalized schemas, supply an arbitrary SQL query joining multiple tables:

SELECT t.id AS ticket_id, t.title, t.description, t.status, t.created_at, t.updated_at,
       c.name AS customer_name, c.tier AS customer_tier,
       t.owner_username, t.department_id, t.is_deleted, t.tenant_id
FROM support_tickets t
LEFT JOIN customers c ON t.customer_id = c.id
WHERE (:lastCrawledTime IS NULL OR t.updated_at >= :lastCrawledTime)

C. Multi-Table Concurrent Scanning

Need to ingest an entire database catalog at once? The connector accepts a comma-separated list of tables (e.g. customers,orders,invoices). It forks parallel scanning tasks across Java 25 StructuredTaskScope subtasks, maximizing I/O throughput across database connections.

4. Tabular RAG & Auto-Narrativization Copilot

OpenCrawling's RepositoryConnector.getSchema(basePath) SPI inspects SQL columns, data types, and remarks at runtime. This feeds directly into the Admin UI and the Spring AI TemplateGenerationCopilot, which automatically synthesizes natural-language Mustache templates.

For example, given a support ticket row, the narrativizer produces:

# Support Ticket {{id}}: {{title}}
Customer: {{customer_name}} (Tier: {{customer_tier}})
Status: {{status}} | Assigned to: {{owner_username}}
Created: {{created_at}} | Last Updated: {{updated_at}}

## Description
{{description}}

When narrativization is disabled, the connector generates a clean, deterministic Markdown key-value fallback representation, eliminating the flat CSV anti-pattern and maximizing vector retrieval relevance.

5. Binary BLOB / CLOB Streaming with Magic Byte Sniffing

Many enterprise databases store binary files directly in database columns (such as BLOB, BYTEA, RAW, or LONGVARBINARY). Loading these binaries into memory during database cursor scans is a notorious cause of JVM OutOfMemoryErrors.

The JDBC connector solves this via OpenCrawling's decoupled Claim Check Pattern:

• Automatic Detection: Auto-detects binary columns from SQL metadata or maps explicitly configured column names (blob-column-name).
• Magic Byte Sniffing: Automatically inspects leading file bytes to resolve MIME types:
  • \x89PNG\r\n\x1a\n → image/png
  • \xFF\xD8\xFF → image/jpeg
  • %PDF- → application/pdf
  • PK\x03\x04 → application/zip (DOCX / XLSX / ZIP)
  • GIF8 → image/gif, RIFF....WEBP → image/webp
• Claim-Check Offloading: Streams binary payloads directly to local storage, Apache Ozone, or AWS S3, passing lightweight URI references to Kafka while routing content to Apache Tika 4.1.0 for process-isolated text extraction.

6. Incremental Delta Crawling & Soft Deletes

Enterprise databases change continuously. The JDBC connector provides full change data capture (CDC) mechanisms:

• High-Water Mark (HWM): Tracks either a timestamp column (e.g. updated_at >= :lastCrawledTime) or an auto-incrementing integer primary key (e.g. id > :lastCrawledId).
• Soft-Delete Tombstone Generation: If a row has a soft-delete indicator (e.g. is_deleted = TRUE or status = 'PURGED'), the connector creates an OIS DocumentAction.DELETE tombstone via RepositoryDocument.createTombstone(...). Downstream consumers immediately purge stale vectors from target vector stores!

7. Zero-Trust Security & Column-Based ACL Mapping

Security is a first-class citizen in OpenCrawling. Using JdbcSecurityMapper, database security columns are mapped directly into standard OIS SecurityConfig and PermissionRule objects:

• User Columns: e.g. owner_id, assignee → PermissionRule(user, "user", user, "read")
• Group Columns: e.g. department_id, role_group → PermissionRule(group, "group", group, "read")
• Tenant Column: e.g. tenant_id → Stamped into metadata for multi-tenant isolation

Downstream, OpenCrawling's Secure Model Context Protocol (MCP) Server enforces strict principal filtering against vector store indices, guaranteeing that users and LLM agents only retrieve information they have explicit row-level authority to view.

8. Quick Configuration Reference

Add the following configuration to your application.yml or environment variables:

9. Automated Verification & Testing

The JDBC connector is backed by comprehensive integration testing scripts: