How Chat2DB Persists ER Diagram Table Positions: File-Based Local Storage Explained

Chat2DB persists ER diagram table positions by serializing coordinate data to a local JSON file (er_position.json) using the ERPositionStorage singleton, which stores x/y offsets keyed by datasource, database, and schema identifiers.

Chat2DB's ER diagram feature allows users to visually arrange database tables and maintain those layouts across application sessions. Understanding how Chat2DB persists ER diagram table positions reveals a lightweight, file-based architecture that avoids database dependencies while providing fast, local storage. This implementation leverages a simple POJO model and a generic storage helper to manage JSON serialization within the user's workspace directory.

The ERPosition Data Model

The persistence mechanism centers on the ERPosition class, a plain Java object defined in chat2db-community-server/chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/model/er/ERPosition.java.

This model captures four critical fields:

• dataSourceId – Identifies the specific database connection
• databaseName – The target database within the connection
• schemaName – The schema context for the table layout
• position – A JSON string encoding the x/y coordinates and dimensions of arranged tables

By combining the datasource, database, and schema identifiers into a composite key, Chat2DB ensures that table layouts remain isolated per environment while allowing multiple distinct arrangements within the same application instance.

Local Storage Architecture with ERPositionStorage

Actual file I/O operations are handled by ERPositionStorage, located at chat2db-community-server/chat2db-community-storage/src/main/java/ai/chat2db/community/storage/small/ERPositionStorage.java. This class extends SmallDataStorage<ERPosition>, a generic abstraction that manages JSON serialization for small, frequently-accessed configuration data.

The storage implementation follows a singleton pattern (ERPositionStorage.INSTANCE) and writes data to er_position.json within the user's local workspace directory, typically under ~/.chat2db/workspace/.../er_position.json. Because SmallDataStorage operates on plain JSON files rather than a database connection, position data survives application restarts without requiring server-side infrastructure.

Service Layer Abstractions

Chat2DB exposes position persistence through two primary service interfaces that decouple the UI from storage implementation details.

IDbErPositionService Interface

The IDbErPositionService interface (chat2db-community-server/chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/service/db/IDbErPositionService.java) declares the core persistence contract:

• getPositions(Long dataSourceId) – Retrieves stored layouts filtered by datasource
• savePosition(ERPosition param) – Persists a new or updated layout

IWorkspaceStorageFacade

The IWorkspaceStorageFacade (chat2db-community-server/chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/service/storage/IWorkspaceStorageFacade.java) provides a higher-level API that coordinates workspace-related storage operations, including ER position management. This facade forwards calls to ERPositionStorage while handling cross-cutting concerns like transaction coordination and error normalization.

The Persistence Workflow

The complete data flow involves distinct load and save operations triggered by user interactions in the ER diagram canvas.

Loading Positions on Component Mount

When the ER diagram component initializes (typically in chat2db-community-client/src/pages/main/workspace/components/WorkspaceTabs/index.tsx), the frontend invokes the backend API to fetch historical layouts:

1. The UI calls IWorkspaceStorageFacade.getErPositions(dataSourceId) or the equivalent IDbErPositionService method
2. ERPositionStorage loads the entire collection from er_position.json into memory
3. The service filters the list by matching dataSourceId, databaseName, and schemaName
4. The resulting position JSON strings are parsed and applied to the visual canvas

Saving Positions After User Interaction

When a user drags a table to a new location, the system persists the change immediately:

1. The frontend serializes the new coordinates into an ERPosition payload
2. It POSTs the data to /api/er/position, which routes to the service layer
3. The implementation retrieves the current list from ERPositionStorage.INSTANCE.getDataList()
4. It removes any existing entry for the same composite key (datasource + database + schema)
5. It adds the new position object and calls ERPositionStorage.INSTANCE.saveAll(list)
6. The storage layer writes the updated collection back to er_position.json

Implementation Examples

Backend Service Implementation

The following Java code demonstrates how the service layer filters positions by datasource and handles atomic updates:

// In IDbErPositionService implementation
public List<ERPosition> getPositions(Long dataSourceId) {
    return ERPositionStorage.INSTANCE.getDataList()
        .stream()
        .filter(p -> Objects.equals(p.getDataSourceId(), dataSourceId))
        .collect(Collectors.toList());
}

public ERPosition savePosition(ERPosition param) {
    // Retrieve current list
    List<ERPosition> list = ERPositionStorage.INSTANCE.getDataList();
    // Remove any existing entry for the same datasource/database/schema
    list.removeIf(p -> Objects.equals(p.getDataSourceId(), param.getDataSourceId())
                      && Objects.equals(p.getDatabaseName(), param.getDatabaseName())
                      && Objects.equals(p.getSchemaName(), param.getSchemaName()));
    // Add the new position and persist
    list.add(param);
    ERPositionStorage.INSTANCE.saveAll(list);   // writes back to er_position.json
    return param;
}

Frontend API Integration

The React/TypeScript client interacts with these endpoints to maintain UI state:

// Load positions when ER diagram opens
const loadPositions = async (dsId: number) => {
  const resp = await api.get<ERPosition[]>('/api/er/position', { params: { dataSourceId: dsId } });
  setPositions(resp.data);
};

// Persist after dragging a table
const persistPosition = async (pos: ERPosition) => {
  await api.post('/api/er/position', pos);
};

Summary

• Chat2DB stores ER diagram table positions in a local JSON file (er_position.json) managed by the ERPositionStorage singleton, ensuring data persists across application restarts without requiring a database.
• The ERPosition model uses a composite key of dataSourceId, databaseName, and schemaName to isolate layouts between different database environments.
• The SmallDataStorage generic base class handles JSON serialization and file I/O operations, providing a reusable pattern for small configuration datasets.
• Service interfaces (IDbErPositionService and IWorkspaceStorageFacade) abstract the storage implementation, allowing the frontend to interact with high-level APIs while the backend manages file-based persistence.
• Position updates are atomic—the service removes existing entries for the same context before inserting new coordinates, preventing duplicate layout data.

Frequently Asked Questions

Where does Chat2DB store ER diagram table positions?

Chat2DB stores ER diagram table positions in a local JSON file named er_position.json located within the user's workspace directory, typically under ~/.chat2db/workspace/.../er_position.json. This file-based approach, implemented in ERPositionStorage.java, ensures persistence without requiring a dedicated database server.

What data format does Chat2DB use for position persistence?

Chat2DB uses a JSON-based format where the ERPosition model stores metadata (datasource ID, database name, schema name) as plain fields and the actual coordinates as a serialized JSON string in the position field. The SmallDataStorage class manages the serialization of List<ERPosition> to and from the er_position.json file.

How does Chat2DB handle position updates when tables are moved?

When a user moves a table in the ER diagram, the frontend sends the new coordinates to the backend service, which calls ERPositionStorage.saveAll(). The implementation first removes any existing entry matching the same datasource, database, and schema combination, then appends the new position data before writing the entire collection back to disk, ensuring atomic updates without duplicate entries.

Is the ER diagram position data shared across different datasources?

No, position data is isolated per datasource. The ERPosition model includes dataSourceId, databaseName, and schemaName fields that form a composite unique key. When loading positions, the service filters the stored list by dataSourceId, ensuring that table layouts for one database connection do not affect another.

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