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

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 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 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, 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 performs the actual logging work. The service converts execution results to SqlOperationLogRecord instances using sqlOperationLogConverter, then submits write operations to an internal Executor:

// 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, 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 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:

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:

# 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:

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:

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:

@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.

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