Chat2DB Database Schema: Internal Metadata Tables and Structure

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
• PostgreSQL: 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
• Oracle: 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:

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:

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

Inserting Users (SQL Example)

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

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 (SQL Server), script.sql (PostgreSQL), test.sql (MySQL), and 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.

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