# Chat2DB Database Schema: Internal Metadata Tables and Structure

> Explore the Chat2DB database schema. Understand how Chat2DB stores internal metadata like users, data sources, and logs using its relational database structure and SQL scripts.

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

---

**Chat2DB stores its application metadata—including users, data sources, workspaces, and audit logs—in a relational database schema defined by SQL scripts bundled with each database-specific plugin.**

The open-source database client Chat2DB, maintained by OtterMind, persists its own state in a structured relational schema rather than relying solely on file-based configuration. Understanding the Chat2DB database schema is essential for administrators deploying the application on external PostgreSQL, MySQL, SQL Server, or Oracle instances, as well as for developers extending the platform. This article examines the core tables, initialization scripts, and MyBatis mapping layer that constitute the internal data model.

## Core Tables in the Chat2DB Database Schema

The internal schema defines seven primary tables that manage application state, authentication, and user preferences. These tables are created identically across all supported database backends, with syntax adapted for each platform.

### User and Authentication Storage

The `user` table maintains application-level accounts and profile information. It stores usernames, password hashes, email addresses, and timestamps for account creation and updates.

### Data Source Registry

The `datasource` table contains connection parameters for every database registered within the application. Columns include the connection URL, JDBC driver class, credentials, and foreign key references to the creating user.

### Workspace and Audit Management

- **workspace**: Persists user-defined workspace configurations, including editor states and opened tab collections.
- **operation_log**: Records an audit trail of executed SQL statements, timestamps, execution results, and associated metadata for compliance and debugging.
- **namespace**: Provides logical grouping for database objects such as schemas and catalogs within a user's context.
- **favorite**: Stores user-marked favorite tables, columns, or other database objects for quick access.
- **connection_history**: Maintains a history of recent connections to facilitate rapid reconnection.
- **plugin_info**: Tracks metadata about currently loaded database plugins and their versions.

## DDL Scripts and Schema Definition Locations

Chat2DB does not generate schema dynamically; instead, it ships static DDL scripts within each database plugin's resources. These scripts execute during application startup to initialize embedded H2 databases (Community mode) or verify schema compatibility against external instances.

The schema definitions reside in the following locations:

- **SQL Server**: [`chat2db-community-server/chat2db-community-plugins/chat2db-community-sqlserver/src/main/resources/dbo.sql`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-plugins/chat2db-community-sqlserver/src/main/resources/dbo.sql)
- **PostgreSQL**: [`chat2db-community-server/chat2db-community-plugins/chat2db-community-postgresql/src/main/resources/script.sql`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-plugins/chat2db-community-postgresql/src/main/resources/script.sql)
- **MySQL**: [`chat2db-community-server/chat2db-community-plugins/chat2db-community-mysql/src/main/resources/test.sql`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-plugins/chat2db-community-mysql/src/main/resources/test.sql)
- **Oracle**: [`chat2db-community-server/chat2db-community-plugins/chat2db-community-oracle/src/main/resources/TEST_USER_1.sql`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-server/chat2db-community-plugins/chat2db-community-oracle/src/main/resources/TEST_USER_1.sql)

Each file contains `CREATE TABLE` statements defining the core schema. For example, the SQL Server script defines the user and datasource tables as follows:

```sql
CREATE TABLE [dbo].[User] (
    id BIGINT IDENTITY(1,1) PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    created_at DATETIME2 NOT NULL,
    updated_at DATETIME2
);

CREATE TABLE [dbo].[Datasource] (
    id BIGINT IDENTITY(1,1) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    url VARCHAR(1024) NOT NULL,
    driver VARCHAR(255) NOT NULL,
    user_name VARCHAR(255),
    password VARCHAR(255),
    created_by BIGINT,
    created_at DATETIME2 NOT NULL
);

```

## Java Implementation and MyBatis Mapping

The application layer interacts with these tables through MyBatis mappers located in the `chat2db-community-domain` module. Java entity classes in `chat2db-community-domain-core` mirror the database schema, while mapper interfaces in `chat2db-community-domain-api` handle the SQL execution.

Entity classes reside under:
`chat2db-community-server/chat2db-community-domain/chat2db-community-domain-core/src/main/java/ai/chat2db/community/domain/core/model/`

Key entities include `ai.chat2db.community.domain.core.model.User`, `Datasource`, and `OperationLog`, annotated with MyBatis mapping directives such as `@TableId` and `@TableField`.

Mapper interfaces are located at:
`chat2db-community-server/chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/mapper/`

### Retrieving Data Sources (Java Example)

The following code demonstrates fetching a datasource from the internal store using the MyBatis mapper:

```java
@Autowired
private DatasourceMapper datasourceMapper;

public Datasource getDatasource(Long id) {
    return datasourceMapper.selectById(id);
}

```

*(See: [`chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/mapper/DatasourceMapper.java`](https://github.com/OtterMind/Chat2DB/blob/main/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/mapper/DatasourceMapper.java))*

### Inserting Users (SQL Example)

When interacting directly with the underlying database, inserting a new user requires populating the mandatory audit columns:

```sql
INSERT INTO [dbo].[User] (username, password_hash, email, created_at, updated_at)
VALUES ('alice', 'hashedpwd', 'alice@example.com', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

```

This corresponds to the `UserServiceImpl` class in `chat2db-community-domain-core`, which handles business logic before persistence.

## Schema Initialization and Runtime Behavior

During application startup, Spring Boot auto-configuration loads the appropriate DDL script from the active database plugin's resources. In Community editions running with an embedded H2 database, these scripts execute automatically to create the schema from scratch. When connecting to external PostgreSQL, MySQL, SQL Server, or Oracle instances, the application verifies the existence of required tables (such as `user` and `datasource`) and may apply the scripts if the schema is missing, depending on configuration properties.

This design allows the Chat2DB database schema to remain consistent across heterogeneous environments while leveraging native SQL syntax for each target platform.

## Summary

- **Chat2DB** maintains internal metadata in tables including `user`, `datasource`, `workspace`, `operation_log`, `favorite`, `connection_history`, `namespace`, and `plugin_info`.
- **DDL scripts** for each supported database are located in the respective plugin resource folders: [`dbo.sql`](https://github.com/OtterMind/Chat2DB/blob/main/dbo.sql) (SQL Server), [`script.sql`](https://github.com/OtterMind/Chat2DB/blob/main/script.sql) (PostgreSQL), [`test.sql`](https://github.com/OtterMind/Chat2DB/blob/main/test.sql) (MySQL), and [`TEST_USER_1.sql`](https://github.com/OtterMind/Chat2DB/blob/main/TEST_USER_1.sql) (Oracle).
- **MyBatis mappers** in `chat2db-community-domain-api` provide the data access layer, while entity classes in `chat2db-community-domain-core` represent the relational model in Java.
- The schema initializes automatically via Spring Boot, supporting both embedded H2 (Community mode) and external database deployments.

## Frequently Asked Questions

### What tables does Chat2DB create in its internal database?

Chat2DB creates eight core tables: `user` for authentication, `datasource` for connection details, `workspace` for UI state, `operation_log` for SQL execution history, `namespace` for object grouping, `favorite` for bookmarked items, `connection_history` for recent connections, and `plugin_info` for plugin metadata. These tables are defined in database-specific DDL scripts located in each plugin's resources directory.

### Can I deploy Chat2DB using my existing PostgreSQL or MySQL instance?

Yes. Chat2DB supports external database deployment by connecting to existing PostgreSQL, MySQL, SQL Server, or Oracle instances. The application verifies that the required schema exists at startup and can execute the bundled DDL scripts if the tables are missing. Administrators should review the specific SQL script for their database type (located in the plugin resources) to ensure compatibility with existing security policies.

### Where are the Java entity classes for the Chat2DB schema located?

The Java entity classes reside in the `chat2db-community-domain-core` module under `chat2db-community-server/chat2db-community-domain/chat2db-community-domain-core/src/main/java/ai/chat2db/community/domain/core/model/`. Classes such as `User`, `Datasource`, and `OperationLog` map directly to the database tables and are used by the service layer to persist application state.

### How does Chat2DB map Java objects to its internal database tables?

Chat2DB uses MyBatis for object-relational mapping. Mapper interfaces in `chat2db-community-domain-api` (located at `chat2db-community-server/chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/mapper/`) define SQL operations, while entity classes use MyBatis annotations or XML mappings to link Java fields to database columns. This architecture separates the domain model from the physical schema defined in the plugin DDL scripts.