# Chat2DB Database Schema and Data Diff Service Architecture Explained

> Explore the Chat2DB database schema and data diff service architecture. Discover how its layered Java design generates portable SQL diffs between JDBC databases efficiently.

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

---

**The Chat2DB diff service uses a layered Java architecture that converts REST requests into Liquibase operations, generating portable SQL diffs between any two JDBC-compliant databases while isolating temporary changelog tables from user schemas.**

The **OtterMind/Chat2DB** repository implements a robust database comparison capability through a clean separation between web API and domain logic. Understanding the **Chat2DB database schema and data diff service architecture** reveals how the application leverages Liquibase's introspection engine to support MySQL, PostgreSQL, Oracle, and SQL Server without custom diff logic. The implementation spans four logical stages, from HTTP request mapping to SQL filtering.

## Four-Stage Diff Pipeline

The service processes schema comparisons through distinct layers that transform user requests into executable SQL.

### Request Mapping and Conversion

The flow begins in `DbDiffController` at `/api/db/diff`, which receives a `StructureDiffRequest` payload containing source and target datasource IDs along with database and schema names. The `DiffConverter.structureInfo2param()` method transforms this HTTP DTO into two `DbConnectionDiffRequest` objects—one for the source connection and one for the target.

```java
@PostMapping("/diff")
public DataResult<String> diff(@RequestBody StructureDiffRequest request) {
    DbConnectionDiffRequest src = diffConverter.structureInfo2param(request.getSource());
    DbConnectionDiffRequest tgt = diffConverter.structureInfo2param(request.getTarget());
    return DataResult.ok(dbDiffService.diff(src, tgt));
}

```

### Connection Building

The `DbDiffServiceImpl.buildConnectInfo()` method resolves metadata for each datasource ID using `IDbWorkspaceDataSourceService.queryDisplayDataSourceById()`. It normalizes JDBC URLs via `JdbcUrlUtils.resetUrl()` and constructs a `ConnectInfo` object encapsulating the driver, connection URL, and schema context.

### Liquibase Diff Generation

The heavy lifting occurs in `DbDiffServiceImpl.generateDiff()` and `generateChangeLog()`. The implementation initializes Liquibase `Database` objects via `DatabaseFactory`, then invokes `DiffGeneratorFactory.getInstance().compare()` to produce a `DiffResult`. This result feeds into `DiffToChangeLog` to generate a changelog XML file representing the structural differences.

```java
DiffResult diffResult = generateDiff(initDb(source), initDb(target));
generateChangeLog(diffResult, diffFile);

```

### Execution and SQL Filtering

Finally, `DbDiffServiceImpl.executeLiquibaseUpdate()` runs the changelog against the target database using `Liquibase.update()`. To prevent contamination of user schemas, the service creates uniquely-named temporary tables prefixed with `chat2db_database_change_<id>`. The `filter()` method (lines 61-73 in [`DbDiffServiceImpl.java`](https://github.com/OtterMind/Chat2DB/blob/main/DbDiffServiceImpl.java)) parses the generated SQL via `SqlUtils.parse()` and strips any statements referencing these temporary changelog or lock tables before returning the clean DDL to the caller.

## Why Liquibase Powers the Engine

Chat2DB delegates database introspection to **Liquibase** rather than implementing custom comparison logic. This delegation enables immediate compatibility with any JDBC-compliant database that Liquibase supports, including exotic configurations without database-specific code in the Chat2DB repository. The `DiffGeneratorFactory` and `DatabaseFactory` classes from the Liquibase library handle catalog and schema snapshotting, while Chat2DB manages the orchestration and cleanup.

## Isolating Temporary Changelog Tables

To avoid polluting target databases, the architecture employs temporary changelog tables with generated IDs. During `executeLiquibaseUpdate()`, the service configures Liquibase to use these temporary table names instead of the default `DATABASECHANGELOG`. After the diff completes, the implementation deletes the entire temporary directory via `FileUtil.del(filePath)`, ensuring no metadata tables remain in the user's schema.

The filtering logic specifically removes `CREATE TABLE`, `DROP TABLE`, `INSERT`, and `DELETE` statements targeting these temporary tables, returning only the relevant schema migration SQL to the API consumer.

## Key Implementation Files

| Module | File Path | Purpose |
|--------|-----------|---------|
| Web API | [`chat2db-community-server/chat2db-community-web/.../DbDiffController.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-web/.../DbDiffController.java) | REST controller exposing the `/diff` endpoint. |
| Web API | [`chat2db-community-server/chat2db-community-web/.../StructureDiffRequest.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-web/.../StructureDiffRequest.java) | HTTP request DTO for source/target configurations. |
| Web API | [`chat2db-community-server/chat2db-community-web/.../DiffConverter.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-web/.../DiffConverter.java) | Transforms web DTOs into domain request objects. |
| Domain API | [`chat2db-community-server/chat2db-community-domain-api/.../IDbDiffService.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-domain-api/.../IDbDiffService.java) | Service interface defining the diff contract. |
| Domain Core | [`chat2db-community-server/chat2db-community-domain-core/.../DbDiffServiceImpl.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-domain-core/.../DbDiffServiceImpl.java) | Full implementation including Liquibase integration and filtering. |
| Domain Core | [`chat2db-community-server/chat2db-community-domain-api/.../DbConnectionDiffRequest.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-domain-api/.../DbConnectionDiffRequest.java) | POJO carrying datasource connection parameters. |
| Utilities | [`chat2db-community-server/chat2db-community-tools/.../JdbcUrlUtils.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-tools/.../JdbcUrlUtils.java) | Normalizes JDBC URLs across database types. |
| Utilities | [`chat2db-community-server/chat2db-community-spi/.../SqlUtils.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-spi/.../SqlUtils.java) | Parses SQL strings for the filtering process. |

## Practical Usage Examples

### Calling the REST API

Invoke the diff service via HTTP POST with datasource IDs and schema names:

```bash
curl -X POST http://localhost:10825/api/db/diff \
  -H "Content-Type: application/json" \
  -d '{
        "source": {
          "dataSourceId": 123,
          "databaseName": "prod_db",
          "schemaName": "public"
        },
        "target": {
          "dataSourceId": 124,
          "databaseName": "test_db",
          "schemaName": "public"
        }
      }'

```

**Response:**

```json
{
  "code": 0,
  "message": "OK",
  "data": "CREATE TABLE new_table (\n    id BIGINT PRIMARY KEY,\n    name VARCHAR(255)\n);\n\nALTER TABLE existing_table ADD COLUMN extra VARCHAR(100);\n"
}

```

### Programmatic Service Access

Inject `IDbDiffService` to compare databases within Java code:

```java
@Autowired
private IDbDiffService diffService;

public void compareSchemas() {
    DbConnectionDiffRequest src = new DbConnectionDiffRequest(123L, "prod_db", "public");
    DbConnectionDiffRequest tgt = new DbConnectionDiffRequest(124L, "test_db", "public");
    String sqlDiff = diffService.diff(src, tgt);
    System.out.println(sqlDiff);
}

```

### Customizing the Filter Logic

Extend `DbDiffServiceImpl` to modify SQL post-processing:

```java
@Service
public class CustomDiffService extends DbDiffServiceImpl {

    @Override
    protected String filter(String raw, String dbType, String tableName, String tableName2) {
        String base = super.filter(raw, dbType, tableName, tableName2);
        // Remove generator comments
        return base.replaceAll("(?m)^-- CHAT2DB.*\\n", "");
    }
}

```

## Summary

- **Layered architecture**: The web layer remains thin, delegating to domain services that orchestrate Liquibase operations.
- **Liquibase integration**: `DbDiffServiceImpl` uses `DiffGeneratorFactory` and `DiffToChangeLog` to generate portable diffs across database types.
- **Temporary table isolation**: Unique `chat2db_database_change_<id>` tables prevent schema pollution, with `filter()` removing them from final output.
- **Key classes**: `DbDiffController` handles HTTP mapping, `DiffConverter` transforms requests, and `DbDiffServiceImpl` manages the comparison lifecycle.
- **Extensibility**: Developers can subclass `DbDiffServiceImpl` to customize SQL filtering or extend connection building logic.

## Frequently Asked Questions

### How does Chat2DB prevent temporary Liquibase tables from appearing in the final diff output?

The `DbDiffServiceImpl.filter()` method parses the raw SQL generated by Liquibase using `SqlUtils.parse()` and programmatically removes any statements referencing the temporary changelog or lock tables. This ensures the returned SQL contains only relevant schema changes, not the internal tracking tables created during the comparison process.

### What databases are supported by Chat2DB's diff service?

Any JDBC-compliant database supported by Liquibase works with the Chat2DB architecture, including MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server, and others. The service relies on Liquibase's `DatabaseFactory` for introspection rather than database-specific comparison code, providing broad compatibility without custom implementations.

### Can I use the diff service programmatically without the REST API?

Yes, autowire the `IDbDiffService` interface and invoke `diff(DbConnectionDiffRequest source, DbConnectionDiffRequest target)` directly. This bypasses the web layer entirely, accepting domain request objects containing datasource IDs, database names, and schema names to generate the SQL diff string.

### Where does the actual database comparison logic reside?

The comparison logic resides in [`DbDiffServiceImpl.java`](https://github.com/OtterMind/Chat2DB/blob/main/DbDiffServiceImpl.java) within the domain core module. This class coordinates the entire pipeline: building `ConnectInfo` objects, initializing Liquibase `Database` instances, generating the `DiffResult`, executing the changelog, and filtering the output. The web layer (`DbDiffController`) merely handles HTTP request mapping and delegates to this service.