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

> Discover how Chat2DB persists ER diagram table positions using a local JSON file and the ERPositionStorage singleton for seamless session recall and easy data management.

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

---

**Chat2DB persists ER diagram table positions by serializing coordinate data to a local JSON file ([`er_position.json`](https://github.com/OtterMind/Chat2DB/blob/main/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`](https://github.com/OtterMind/Chat2DB/blob/main/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`](https://github.com/OtterMind/Chat2DB/blob/main/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`](https://github.com/OtterMind/Chat2DB/blob/main/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`](https://github.com/OtterMind/Chat2DB/blob/main/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`](https://github.com/OtterMind/Chat2DB/blob/main/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`](https://github.com/OtterMind/Chat2DB/blob/main/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`](https://github.com/OtterMind/Chat2DB/blob/main/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`](https://github.com/OtterMind/Chat2DB/blob/main/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:

```java
// 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:

```typescript
// 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`](https://github.com/OtterMind/Chat2DB/blob/main/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`](https://github.com/OtterMind/Chat2DB/blob/main/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`](https://github.com/OtterMind/Chat2DB/blob/main/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`](https://github.com/OtterMind/Chat2DB/blob/main/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.