# How to Enable Audit Trails with Chat2DB's SQL Operation Log

> Easily enable audit trails in Chat2DB by leveraging its SQL operation log. Discover how Chat2DB automatically records every SQL statement without impacting query performance.

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

---

**Chat2DB automatically records every SQL statement through an asynchronous, three-layer audit-logging pipeline that persists execution details to workspace storage without blocking query performance.**

The OtterMind/Chat2DB open-source database client provides built-in audit capabilities that capture SQL operations by default using a best-effort delivery system. This architecture ensures complete audit trails for compliance and debugging while maintaining zero impact on user query latency.

## How the Audit Logging Pipeline Works

Chat2DB implements audit trails through a coordinated stack that processes execution results asynchronously. When a user executes SQL, `SqlExecutionManager` located at [`chat2db-community-server/chat2db-community-web/src/main/java/ai/chat2db/community/web/api/adapter/db/execution/SqlExecutionManager.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-web/src/main/java/ai/chat2db/community/web/api/adapter/db/execution/SqlExecutionManager.java) triggers `IOpsSqlOperationLogService.recordResultAsync()` to initiate logging in a background thread.

### Web and API Layer

The `OpsOperationLogController` exposes REST endpoints under `/api/operation/log/*` for querying and manually creating operation-log records. This controller in [`chat2db-community-server/chat2db-community-web/src/main/java/ai/chat2db/community/web/api/controller/OpsOperationLogController.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-web/src/main/java/ai/chat2db/community/web/api/controller/OpsOperationLogController.java) handles paginated list requests and individual log retrieval via standard HTTP methods.

### Service Abstraction

The `IOpsSqlOperationLogService` interface defines the contract for recording SQL operations. Located in [`chat2db-community-server/chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/service/ops/IOpsSqlOperationLogService.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/service/ops/IOpsSqlOperationLogService.java), this layer receives `ExecuteResponse` objects and delegates persistence tasks to the implementation layer while maintaining strict separation between domain logic and storage mechanisms.

### Asynchronous Implementation

`OpsSqlOperationLogServiceImpl` in [`chat2db-community-server/chat2db-community-domain/chat2db-community-domain-core/src/main/java/ai/chat2db/community/domain/core/impl/operation/OpsSqlOperationLogServiceImpl.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-domain/chat2db-community-domain-core/src/main/java/ai/chat2db/community/domain/core/impl/operation/OpsSqlOperationLogServiceImpl.java) performs the actual logging work. The service converts execution results to `SqlOperationLogRecord` instances using `sqlOperationLogConverter`, then submits write operations to an internal `Executor`:

```java
// Best-effort async write inside OpsSqlOperationLogServiceImpl
executor.execute(() -> write(record));

```

This implementation catches all exceptions internally, ensuring that audit logging failures never bubble back to users or affect query execution time.

### Storage Facade

The `IWorkspaceStorageFacade` interface abstracts persistence details, allowing operation logs to be stored in local H2/SQLite databases, files, or custom plugin providers. Found in [`chat2db-community-server/chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/service/storage/IWorkspaceStorageFacade.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/service/storage/IWorkspaceStorageFacade.java), this facade receives `OperationLog` objects and handles physical storage in the configured workspace directory (default: `~/.chat2db`).

### Domain Model

The `OperationLog` POJO in [`chat2db-community-server/chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/model/operation/OperationLog.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/model/operation/OperationLog.java) captures audit-relevant fields including SQL text, execution duration, user ID, datasource ID, timestamps, and status codes.

## Verifying Audit Trail Configuration

Audit trails are **enabled by default** in Chat2DB distributions. To confirm the pipeline is active:

1. **Verify Spring bean instantiation** – Check application startup logs for `OpsSqlOperationLogServiceImpl` registration. No additional configuration files are required for standard deployments.

2. **Confirm workspace storage accessibility** – Ensure the application can write to the workspace directory. The default H2/SQLite provider works out-of-the-box, but custom implementations require a valid `IWorkspaceStorageProvider` plugin in the classpath.

3. **Validate execution logging** – Run any SQL query and verify entries appear via the REST API:

```bash
curl "http://localhost:10825/api/operation/log/list?pageNo=1&pageSize=20"

```

## Querying and Creating Audit Logs

### Retrieving Logs via REST

The `OpsOperationLogController` provides endpoints for audit log access:

```bash

# List logs with pagination

curl "http://localhost:10825/api/operation/log/list?pageNo=1&pageSize=20"

# Get specific log entry by ID

curl "http://localhost:10825/api/operation/log?id=123"

```

### Manual Log Creation

For administrative actions or custom events, create log entries directly through the API:

```bash
curl -X POST "http://localhost:10825/api/operation/log/create" \
     -H "Content-Type: application/json" \
     -d '{
           "sql":"SELECT * FROM user",
           "datasourceId":1,
           "userId":42,
           "durationMs":12,
           "status":"SUCCESS"
         }'

```

### Programmatic Access

Inject `IOpsSqlOperationLogService` to record operations from custom Java code:

```java
import ai.chat2db.community.domain.api.service.ops.IOpsSqlOperationLogService;
import ai.chat2db.community.domain.api.model.operation.OperationLog;
import org.springframework.beans.factory.annotation.Autowired;

@Service
public class AuditHelper {

    @Autowired
    private IOpsSqlOperationLogService operationLogService;

    public void auditCustomSql(String sql, Long datasourceId, Long userId) {
        OperationLog log = new OperationLog();
        log.setSql(sql);
        log.setDatasourceId(datasourceId);
        log.setUserId(userId);
        log.setStatus("SUCCESS");
        
        // Asynchronous persistence via the service
        operationLogService.recordResultAsync(
            new ExecuteResponse(sql, datasourceId, userId, 0L, "SUCCESS"),
            "custom-audit"
        );
    }
}

```

## Implementing Retention Policies

By default, `OperationLog` entries persist indefinitely. Implement automated cleanup using `IWorkspaceStorageFacade`:

```java
@Component
public class OperationLogRetention {

    @Autowired
    private IWorkspaceStorageFacade storageFacade;

    @Scheduled(cron = "0 0 2 * * *")  // Daily at 02:00
    public void pruneOldLogs() {
        long cutoff = System.currentTimeMillis() - (30L * 24 * 60 * 60 * 1000);
        storageFacade.deleteOperationLogBefore(cutoff);
    }
}

```

Note: `deleteOperationLogBefore` requires implementation in your storage provider if using custom plugins; the default provider supports direct deletion by ID via `deleteOperationLog(id)`.

## Summary

- Chat2DB enables SQL audit trails by default through `OpsSqlOperationLogServiceImpl`, which auto-wires as a Spring bean without requiring explicit configuration.
- The logging pipeline uses an asynchronous, best-effort strategy that executes in background threads via `executor.execute()` to prevent query latency impact.
- Audit data flows from `SqlExecutionManager` through the service layer to `IWorkspaceStorageFacade`, ultimately persisting as `OperationLog` entities in the workspace storage.
- REST endpoints under `/api/operation/log/*` allow manual querying and creation of audit entries using standard HTTP clients.
- Custom retention policies require implementing scheduled cleanup jobs that interact with the storage facade, as logs are retained indefinitely by default.

## Frequently Asked Questions

### Is SQL audit logging enabled by default in Chat2DB?

Yes. The `OpsSqlOperationLogServiceImpl` bean automatically instantiates during application startup and records every SQL execution through the asynchronous pipeline. No explicit configuration or property files are required to activate the feature.

### Where are SQL operation logs physically stored?

Logs are stored via the `IWorkspaceStorageFacade` abstraction, which defaults to the local workspace directory (`~/.chat2db`) using an embedded H2 or SQLite database. You can redirect storage to external systems by implementing a custom `IWorkspaceStorageProvider` plugin and registering it in the Spring context.

### Does audit logging impact query performance?

No. Chat2DB uses a best-effort asynchronous design where `OpsSqlOperationLogServiceImpl` submits persistence tasks to a background `Executor`. The implementation catches all exceptions internally, ensuring that logging failures or storage latency never block or slow down SQL execution.

### How can I export or query audit logs programmatically?

Use the `OpsOperationLogController` REST endpoints at `http://localhost:10825/api/operation/log/list` for paginated retrieval, or inject `IOpsSqlOperationLogService` in Java services to create custom audit entries. For bulk export, implement a custom query against the underlying workspace storage through the `IWorkspaceStorageFacade` interface.