Zero-Overhead Lakehouse Audit Reporting & Embedded DuckDB Analytics: Announcing OpenCrawling's Document Ledger

1. The Enterprise Governance & AI Token Cost Dilemma

In modern enterprise AI stacks, ingesting unstructured data into Retrieval-Augmented Generation (RAG) vector stores or migrating legacy archives into modern object storage is no longer just a pipeline task; it is a heavily regulated compliance workflow:

• Regulatory Audits (SOC 2, GDPR, HIPAA, ISO 27001): Enterprise compliance officers cannot rely on ephemeral log files. They demand immutable, Write-Once-Read-Many (WORM) audit manifests proving exactly when an asset was ingested, its bit-for-bit SHA-256 hash, and which ACL rules were applied.
• AI Economics & Token Budget Governance: With massive crawls generating tens of millions of vector embeddings across models like mxbai-embed-large or OpenAI text-embedding-3-small, finance and platform engineering teams need precise per-document and per-run accounting of chunks, tokens, and estimated dollar costs.
• Bit-for-Bit Migration Verification: In archive migration mode, safe-pruning source repositories requires mathematically verifiable proof that 100% of binary payloads landed uncorrupted in cloud object storage (such as Apache Ozone or Amazon S3).
• The Log File Trap: Parsing multi-gigabyte unindexed text logs with grep or awk is agonizingly slow, resource-heavy, and fragile. Organizations shouldn't be forced to deploy and pay for external data warehouses like Snowflake, BigQuery, or ClickHouse just to audit crawler job runs.

The new OpenCrawling Reporting Subsystem solves this holistically by integrating an enterprise Lakehouse audit architecture right into the crawler runtime, with zero database lock contention and zero runtime penalty.

2. Zero Main-Thread Overhead: The Asynchronous JobLedgerAccumulator

A core requirement of OpenCrawling is high-throughput parallelism: worker threads running on Java 25 Virtual Threads must never block on disk I/O, database locks, or external network logging calls while crawling and extracting content.

To achieve this, OpenCrawling introduces the JobLedgerAccumulator. As ingestion and migration workers process documents across parallel lanes, they emit lightweight, structured ledger events directly into a concurrent in-memory lock-free accumulator. No database queries, no row-level mutexes, and no serialization bottlenecks occur during the active crawl.

3. Hive Partitioning & Multi-Format Lakehouse Artifacts

Upon job completion, OpenCrawling automatically generates up to four complementary artifacts organized in standard Hive-partitioned directory layouts (mode={RAG|MIGRATION}/year=YYYY/month=MM/day=DD/):

${opencrawling.reporting.storage.local-dir}/
├── mode=RAG/
│   └── year=2026/
│       └── month=10/
│           └── day=17/
│               ├── job_1_exec_exec-6e03d06c.parquet
│               ├── manifest_exec-6e03d06c.csv
│               ├── summary_exec-6e03d06c.json
│               └── job_1_exec_exec-6e03d06c.avro       (optional)
└── mode=MIGRATION/
    └── year=2026/
        └── month=10/
            └── day=17/
                ├── job_2_exec_exec-9f82a11b.parquet
                ├── manifest_exec-9f82a11b.csv
                └── summary_exec-9f82a11b.json

1. Columnar Apache Parquet with Zstandard Compression

The primary audit ledger is written as a binary columnar Apache Parquet file compressed with Zstandard (ZSTD). Configured with 128 MB row groups and 1 MB data pages with dictionary encoding for repeating strings (connector types, MIME types, disposition statuses), Parquet achieves up to 90% storage savings while enabling sub-second vectorized scans over tens of millions of records.

2. RFC 4180 CSV Compliance Manifest

For external auditors, compliance officers, and legal teams, OpenCrawling produces an RFC 4180-compliant CSV manifest. It provides an immediate, tamper-evident record ready for spreadsheet inspection or regulatory discovery:

job_id,execution_id,document_id,source_uri,status,duration_ms,rag_chunk_count,rag_token_count,mig_sha256_checksum,timestamp_utc
1,exec-6e03d06c,doc-001,file:///data/contract.pdf,SUCCESS,142,8,2140,,2026-10-17T10:14:22Z
1,exec-6e03d06c,doc-002,file:///data/invoice.docx,SUCCESS,98,4,950,,2026-10-17T10:14:23Z
1,exec-6e03d06c,doc-003,file:///data/quarantine.bin,FAILED,12,0,0,,2026-10-17T10:14:24Z

3. Operational JSON Summary

A lightweight operational snapshot containing high-level run KPIs, document throughput, chunk counts, token totals, and estimated dollar costs:

{
  "executionId": "exec-6e03d06c",
  "jobId": "1",
  "jobName": "Default_RAG_Pipeline",
  "pipelineMode": "RAG",
  "status": "COMPLETED",
  "totalDocuments": 1540,
  "successfulDocuments": 1538,
  "failedDocuments": 2,
  "skippedDocuments": 0,
  "durationMs": 42180,
  "totalChunks": 6160,
  "totalTokens": 1848000,
  "estimatedEmbeddingCostUsd": 0.03696,
  "embeddingModel": "mxbai-embed-large",
  "reportPathParquet": "mode=RAG/year=2026/month=10/day=17/job_1_exec_exec-6e03d06c.parquet",
  "reportPathCsv": "mode=RAG/year=2026/month=10/day=17/manifest_exec-6e03d06c.csv",
  "reportPathJson": "mode=RAG/year=2026/month=10/day=17/summary_exec-6e03d06c.json"
}

4. Optional Canonical Avro Container

When enterprise streaming pipelines require binary schema enforcement, OpenCrawling can emit binary Avro container files using the canonical job_execution_report.avsc schema for direct ingestion into Kafka Lakehouse sinks or Apache Iceberg/Hadoop tables.

4. Serverless Vectorized OLAP: Embedded DuckDB Engine

Here is where things get truly exciting. Instead of requiring you to load Parquet files into a remote data warehouse or spin up external query infrastructure, OpenCrawling embeds DuckDB 1.2.x directly inside the Java runtime.

DuckDB executes vectorized, zero-copy SQL queries over local or cloud Parquet files with blazing speed. You can submit analytical SQL queries via the REST API (POST /api/reports/query), through the Admin UI, or using the CLI:

curl -X POST http://localhost:8080/api/reports/query \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "SELECT status, count(*) AS doc_count, sum(rag_token_count) AS tokens, avg(duration_ms) AS avg_ms FROM read_parquet(\"target/reports/mode=RAG/**/*.parquet\") GROUP BY status"
  }'

Essential Analytical Queries for Operations & Finance

• Top 5 Latency Bottlenecks:

  SELECT document_id, source_uri, duration_ms, rag_chunk_count 
  FROM read_parquet('target/reports/mode=RAG/**/*.parquet') 
  ORDER BY duration_ms DESC LIMIT 5;

• Error Root-Cause Analysis:

  SELECT error_code, error_message, count(*) AS occurrences 
  FROM read_parquet('target/reports/**/*.parquet') 
  WHERE status = 'FAILED' 
  GROUP BY error_code, error_message 
  ORDER BY occurrences DESC;

• AI Embedding Cost Accounting (e.g. $0.02 / 1M tokens):

  SELECT rag_embedding_model, 
         sum(rag_token_count) AS total_tokens, 
         (sum(rag_token_count) / 1000000.0) * 0.02 AS total_cost_usd 
  FROM read_parquet('target/reports/mode=RAG/**/*.parquet') 
  GROUP BY rag_embedding_model;

• Migration Cryptographic Integrity Verification:

  SELECT mig_target_bucket, 
         count(*) AS objects_migrated, 
         sum(mig_binary_size_bytes) AS total_bytes, 
         count(mig_sha256_checksum) AS checksums_verified 
  FROM read_parquet('target/reports/mode=MIGRATION/**/*.parquet') 
  GROUP BY mig_target_bucket;

5. Admin UI: The "Reports & Audit Ledger" Dashboard

The OpenCrawling Admin UI now features a dedicated Reports & Audit Ledger view (accessible at /reports):

• Executive KPI Cards: High-level metrics showing Total Reports Generated, Total Documents Audited (with unique job deduplication stats), Ingestion Success Rate, and Aggregate Tokens Consumed.
• Job Execution Table: Displays each crawl execution with pipeline mode badges (RAG vs MIGRATION), job name, duration, and OK vs Failed document counters.
• One-Click Artifact Downloads: Instant buttons to download manifest.csv, report.parquet, and summary.json for any execution.
• Interactive Document Ledger Modal: Drill into any completed job to search individual documents by URI or ID, filter by status (ALL, SUCCESS, FAILED, SKIPPED), and inspect latency, chunk counts, and error stacktraces.
• Embedded DuckDB Query Playground: An interactive in-browser SQL code editor equipped with one-click query templates (Token Costs, Latency Analysis, Migration Verification, Error Breakdown), instant query execution, and one-click Export to CSV.

6. Terminal-First Auditing with oc report CLI

For DevOps and automation pipelines, the OpenCrawling CLI introduces the oc report command suite:

1. List All Execution Reports

$ oc report list
+---------------------+--------+------+-----------+---------------------+----------+-----------------+--------+--------+-----------+
| EXECUTION ID        | JOB ID | MODE | STATUS    | START TIME          | DURATION | DOCS (OK/FAIL)  | CHUNKS | TOKENS | EST. COST |
+---------------------+--------+------+-----------+---------------------+----------+-----------------+--------+--------+-----------+
| exec-6e03d06c       | 1      | RAG  | COMPLETED | 2026-10-17 10:14:00 | 12s      | 9 (9/0)         | 42     | 11840  | $0.0024   |
| exec-9f82a11b       | 2      | MIG  | COMPLETED | 2026-10-17 11:20:00 | 45s      | 1500 (1500/0)   | -      | -      | -         |
+---------------------+--------+------+-----------+---------------------+----------+-----------------+--------+--------+-----------+

2. Inspect Granular Document Ledger

$ oc report ledger 1 exec-6e03d06c --status SUCCESS --page 0 --size 5
+------------------+------------------------------------+---------+--------+--------+----------+-------+
| DOCUMENT ID      | SOURCE URI                         | STATUS  | CHUNKS | TOKENS | DURATION | ERROR |
+------------------+------------------------------------+---------+--------+--------+----------+-------+
| doc-001          | file:///data/contracts/nda.pdf     | SUCCESS | 8      | 2140   | 142ms    | -     |
| doc-002          | file:///data/contracts/sla.pdf     | SUCCESS | 5      | 1200   | 98ms     | -     |
+------------------+------------------------------------+---------+--------+--------+----------+-------+
Showing 2 items | Page 0 | Total: 9

3. Export Artifacts & Run Direct DuckDB Queries

# Download Parquet columnar audit file
$ oc report export 1 exec-6e03d06c --format parquet --out ./audit-run.parquet

# Run analytical SQL query directly via CLI
$ oc report query --sql "SELECT count(*) AS total_docs, avg(duration_ms) AS avg_duration_ms FROM read_parquet('mode=RAG/**/*.parquet')"
+------------+-----------------+
| total_docs | avg_duration_ms |
+------------+-----------------+
| 9          | 124.44          |
+------------+-----------------+
✔ Query executed in 14 ms (1 rows returned)

7. Programmatic Governance with Java Client SDK (ReportClient)

Building custom governance bots or automated CI/CD compliance gates? The official OpenCrawling Java SDK provides full programmatic access through client.reports():

import org.opencrawling.sdk.OpenCrawlingClient;
import org.opencrawling.sdk.models.JobReportSummary;
import org.opencrawling.sdk.models.PaginatedLedgerResponse;
import org.opencrawling.sdk.models.QueryResult;
import java.nio.file.Path;

OpenCrawlingClient client = OpenCrawlingClient.builder()
        .baseUrl("http://localhost:8080")
        .build();

// 1. List all completed job execution summaries
for (JobReportSummary r : client.reports().list()) {
    System.out.printf("Exec: %s | Mode: %s | Docs: %d (Success: %d, Fail: %d) | Tokens: %d%n",
            r.executionId(), r.pipelineMode(), r.totalDocuments(), 
            r.successfulDocuments(), r.failedDocuments(), r.totalTokens());
}

// 2. Query document ledger for failures
PaginatedLedgerResponse ledger = client.reports().getDocumentLedger(
        "1", "exec-6e03d06c", "FAILED", "contracts", 0, 50);
ledger.items().forEach(entry -> {
    System.err.println("Document Failed: " + entry.documentId() + " Reason: " + entry.errorMessage());
});

// 3. Download Parquet file directly to disk
client.reports().downloadToFile("1", "exec-6e03d06c", "parquet", Path.of("./job1-report.parquet"));

// 4. Run embedded DuckDB query programmatically
QueryResult qr = client.reports().query(
        "SELECT status, count(*) AS total FROM read_parquet('target/reports/**/*.parquet') GROUP BY status");
System.out.println("Result: " + qr.rows());

8. Storage Targets & Configuration Reference

Lakehouse audit reporting is enabled by default in OpenCrawling 2.0+ and supports both local filesystems and enterprise cloud object stores (Apache Ozone, AWS S3, MinIO):

opencrawling:
  reporting:
    enabled: true                     # Enable Lakehouse audit reporting
    storage:
      type: LOCAL                     # LOCAL or S3
      local-dir: target/reports       # Local disk directory (Docker: /data/reports)
      s3:
        endpoint: http://localhost:9878
        bucket: opencrawling-audit-reports
        region: us-east-1
    formats:
      parquet:
        enabled: true                 # Generate columnar Parquet file
        compression: ZSTD             # ZSTD, SNAPPY, GZIP, UNCOMPRESSED
        row-group-size-bytes: 134217728 # 128 MB row groups
        page-size-bytes: 1048576       # 1 MB data pages
      csv: true                       # Generate CSV compliance manifest
      json: true                      # Generate operational JSON summary
      avro: false                     # Generate optional binary Avro file

9. Automated Verification & Testing

The Reporting Subsystem is backed by a comprehensive end-to-end integration test suite that runs against the decoupled multi-service containerized stack: