# How Chat2DB's SQL Operation Log Tracks Query History

> Discover how Chat2DB's SQL operation log tracks query history. Learn about its asynchronous logging, metadata storage, and robust failure handling to ensure uninterrupted query execution.

- Repository: [OtterMind/Chat2DB](https://github.com/OtterMind/Chat2DB)
- Tags: internals
- Published: 2026-07-26

---

**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)

```java
// 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:

```java
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`](https://github.com/OtterMind/Chat2DB/blob/main/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:

```java
Collections.reverse(list);

```

Storage path: [`chat2db-community-server/chat2db-community-storage/src/main/java/ai/chat2db/community/storage/large/OperationLogStorage.java`](https://github.com/OtterMind/Chat2DB/blob/main/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:

```java
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:

```typescript
// 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.