How Chat2DB's SQL Operation Log Tracks Query History

Chat2DB records every SQL execution asynchronously through a dedicated service layer that converts execution results into persistent log entries, storing up to 1,000 records with full contextual metadata while ensuring logging failures never interrupt query execution.

Chat2DB is an open-source database management tool that provides comprehensive SQL execution capabilities across multiple data sources. Understanding how its SQL operation log tracks query history reveals a sophisticated asynchronous logging architecture designed to capture execution details without impacting performance. This article examines the source code implementation to show exactly how every query is recorded, stored, and exposed through REST APIs.

Execution Capture Flow

When a user runs a statement through the SQL editor, table browser, or JCEF interface, the execution flow passes through a chain of components designed to isolate logging from execution.

From User Action to Log Service

The execution pipeline begins at SqlExecutionManager, which delegates to SqlExecutionJob for actual processing. Upon completion, SqlExecutionLogConsumer hands each ExecuteResponse (or failure) to IOpsSqlOperationLogService.recordResultAsync. The implementation class OpsSqlOperationLogServiceImpl creates a transient SqlOperationLogRecord via SqlOperationLogConverter.executeResult2record, attaching:

• The raw SQL text, execution status, affected rows, and elapsed time
• The source enum (SQL_EDITOR_HTTP, TABLE_BROWSE, SQL_EDITOR_JCEF)
• Current connection profile and contextual metadata (user, organization, schema)

// Inside SqlExecutionJob after a successful execution
ExecuteResponse response = ...;
sqlOperationLogRecorder.recordResultAsync(response,
        SqlOperationLogSourceEnum.SQL_EDITOR_JCEF.name());

Handling Success and Failure

The service layer provides distinct async methods for different outcomes. For failed executions, the system calls recordFailureAsync with the exception details:

sqlOperationLogRecorder.recordFailureAsync(failedSql,
        SqlOperationLogSourceEnum.SQL_EDITOR_HTTP.name(),
        exception.getMessage());

Both paths guarantee that the original SQL execution completes before logging begins, preventing latency from impacting the user experience.

Data Transformation and Storage

Before persistence, execution data undergoes a conversion from transient DTO to persistent entity, ensuring only relevant fields are stored long-term.

The Conversion Pipeline

SqlOperationLogRecord is transformed into the persistent OperationLog POJO by SqlOperationLogConverter.sqlRecord2operationLog. According to the source code in OperationLog.java, each stored entry contains:

• id, gmtCreate (timestamps)
• name, dataSourceId, databaseName, schemaName (connection context)
• type, ddl (SQL content)
• status, operationRows, useTime (execution results)
• extendInfo, organizationId, userName (metadata)

Persistent Storage Implementation

The converted OperationLog is stored via IWorkspaceStorageFacade.createOperationLog, which delegates to OperationLogStorage. This class implements LargeDataStorage and maintains an in-memory cap of 1,000 entries. When retrieving logs, OperationLogStorage reverses the list so the newest logs appear first:

Collections.reverse(list);

Storage path: chat2db-community-server/chat2db-community-storage/src/main/java/ai/chat2db/community/storage/large/OperationLogStorage.java

Asynchronous Processing Guarantees

All log writes are queued to a dedicated ThreadPoolExecutor named chat2db-sql-operation-log-*. This isolation ensures that heavy logging workloads never block the main execution threads.

The implementation includes defensive error handling that protects the primary SQL execution flow. If logging fails for any reason, the system catches the exception and continues:

catch (RuntimeException e) { 
    log.warn(...); 
}

This best-effort approach means users always receive their query results even if the operation log is temporarily unavailable.

API Exposure and Frontend Access

The REST controller OpsOperationLogController exposes three primary endpoints for UI interaction:

1. POST /operationLog – Creates a new log entry
2. GET /operationLog/list – Retrieves paginated history
3. GET /operationLog – Fetches a single log by ID

Frontend components interact with these endpoints through WorkspaceStorageWebFacade, which provides methods like createOperationLog, operationLogList, and getOperationLog. A typical frontend request looks like:

// In the client UI
await fetch('/api/operationLog/list', {
  method: 'POST',
  body: JSON.stringify({ pageNo: 1, pageSize: 20 })
});

Summary

• Asynchronous architecture: Logging occurs via dedicated thread pools (chat2db-sql-operation-log-*) to prevent execution blocking
• DTO-to-Entity conversion: SqlOperationLogConverter transforms transient records into persistent OperationLog objects with comprehensive metadata
• Capped storage: OperationLogStorage retains the 1,000 most recent entries, displaying newest first via Collections.reverse
• Fail-safe design: Runtime exceptions during logging are caught and warned without affecting SQL execution results
• RESTful access: OpsOperationLogController provides full CRUD capabilities for frontend consumption

Frequently Asked Questions

How does Chat2DB ensure logging doesn't slow down SQL execution?

Chat2DB uses an asynchronous, fire-and-forget pattern where IOpsSqlOperationLogService.recordResultAsync queues log writes to a dedicated ThreadPoolExecutor. This separation means the main execution thread returns results to the user immediately while logging continues in the background. Additionally, all logging code is wrapped in try-catch blocks that swallow RuntimeException to guarantee that logging failures never propagate to the user.

What information is stored in each SQL operation log entry?

Each log entry stored in the OperationLog model includes the raw SQL text (ddl), execution status, affected row count (operationRows), elapsed time (useTime), source interface type (SQL_EDITOR_HTTP, TABLE_BROWSE, etc.), and contextual metadata including dataSourceId, databaseName, schemaName, organizationId, and userName. The extendInfo field captures additional execution-specific details.

How many query history entries does Chat2DB retain?

The OperationLogStorage implementation enforces a hard cap of 1,000 entries as a LargeDataStorage implementation. When the limit is reached, older entries are evicted. The storage layer automatically reverses the list on retrieval (Collections.reverse(list)) to ensure the most recent queries appear first in the UI.

Can I access the operation log through an API?

Yes. The OpsOperationLogController exposes REST endpoints for programmatic access. You can list history via POST /api/operationLog/list with pagination parameters (pageNo, pageSize), retrieve specific logs via GET /api/operationLog, or create entries via POST /api/operationLog. These endpoints are consumed by the frontend through WorkspaceStorageWebFacade but are available for any authenticated client.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}