How Chat2DB Persists Favorite Tables: Inside the Table Pinning Architecture

Chat2DB persists favorite tables by writing records to a relational database table user_table_pin via REST endpoints, making pinned tables available across sessions and devices.

Chat2DB's table pinning feature allows users to mark frequently accessed tables as favorites, ensuring quick access across browser sessions. This functionality relies on a persistent storage mechanism that tracks user-specific table preferences in the application's backend database. Understanding how Chat2DB persists favorite tables reveals a clean separation between the React-based frontend and the Java Spring Boot backend.

The Table Pinning Architecture Overview

The persistence flow follows a standard three-tier pattern: the React frontend invokes REST endpoints, the Spring Boot controller delegates to a service layer, and MyBatis maps the data to a relational table. When a user clicks the pin icon, the system either inserts or deletes a row in the user_table_pin table based on the current state. This design ensures that pinned tables survive browser refreshes and remain synchronized across devices for the same user account.

Front-End Implementation: Triggering Pin Actions

The pinning interaction originates in the tree view component where users manage database objects.

Building the API Request in pinTable.ts

Located at chat2db-community-client/src/blocks/NewTree/functions/pinTable.ts, the pinning logic dynamically selects the appropriate API method based on the current pin state. If decorativeParams.pinned is true, it calls deleteTablePin; otherwise, it calls addTablePin. The function constructs a request containing the fully qualified table identifiers including dataSourceId, databaseName, schemaName, and tableName.

// chat2db-community-client/src/blocks/NewTree/functions/pinTable.ts
const api = treeNodeData.decorativeParams.pinned
    ? 'deleteTablePin'
    : 'addTablePin';
return mysqlService[api]({
    dataSourceId: treeNodeData.extraParams.dataSourceId,
    databaseName: treeNodeData.extraParams.databaseName,
    schemaName:   treeNodeData.extraParams.schemaName,
    tableName:    treeNodeData.originalTitle,
});

Back-End REST Controller and Service Layer

The frontend requests are handled by dedicated endpoints in the Java backend.

TablePinController Endpoints

The TablePinController class at chat2db-community-web/src/main/java/ai/chat2db/community/web/api/controller/TablePinController.java exposes two primary endpoints under /api/pin/table. The controller uses Spring's @RestController annotation and delegates all business logic to the ITablePinService interface.

// chat2db-community-web/src/main/java/ai/chat2db/community/web/api/controller/TablePinController.java
@RestController
@RequestMapping("/api/pin/table")
public class TablePinController {
    @PostMapping("/add")
    public Result<Void> add(@RequestBody TablePinCreateRequest request) {
        tablePinService.addPin(request);
        return Result.success();
    }

    @PostMapping("/delete")
    public Result<Void> delete(@RequestBody TablePinDeleteRequest request) {
        tablePinService.removePin(request);
        return Result.success();
    }
}

Service Interface and Implementation

The service layer is defined by ITablePinService in the domain API module, with the actual implementation residing in TablePinServiceImpl. This abstraction allows the controller to remain agnostic of the persistence mechanism while the service handles the conversion of request objects to data objects (DOs) and manages transaction boundaries.

// chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/service/db/ITablePinService.java
public interface ITablePinService {
    void addPin(TablePinCreateRequest request);
    void removePin(TablePinDeleteRequest request);
    List<TablePinDTO> listUserPins(Long userId);
}

Database Persistence with MyBatis

The actual storage happens in a dedicated relational table managed through MyBatis mappers.

The user_table_pin Table Schema

Records are stored in the user_table_pin table within the chat2db database schema. Each row contains five critical columns that uniquely identify a pinned table for a specific user: user_id, datasource_id, database_name, schema_name, and table_name. This composite identification ensures that pins are both user-scoped and precisely targeted to specific database objects.

MyBatis Mapper Operations

The TablePinMapper interface at chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/mapper/TablePinMapper.java defines the SQL operations using annotations. The insert operation uses a straightforward @Insert annotation to persist the pin record with the current user's ID obtained from the security context.

// chat2db-community-domain/chat2db-community-domain-api/src/main/java/ai/chat2db/community/domain/api/mapper/TablePinMapper.java
@Insert("INSERT INTO user_table_pin (user_id, datasource_id, database_name, schema_name, table_name) "
      + "VALUES (#{userId}, #{dataSourceId}, #{databaseName}, #{schemaName}, #{tableName})")
void insert(TablePinDO pin);

Retrieving Pinned Tables for UI Rendering

When the application initializes the database tree view, it must reconcile the current schema with the user's persisted pins.

Fetching Pins on Tree Load

The frontend calls mysqlService.getTablePinList() which maps to the GET /api/pin/table/list endpoint. The backend queries all pins for the authenticated user and returns a list of TablePinDTO objects. The frontend then merges this data into the tree node structure, setting decorativeParams.pinned = true for matching tables, which triggers the visual pin indicator in the UI.

// Example of consuming the pin list in the frontend
import mysqlService from '@/service/sql';

async function loadPinnedTables() {
  const pins = await mysqlService.getTablePinList();
  // Merge into tree nodes to set pinned status
  pins.forEach(pin => {
    const node = findTreeNode(pin);
    if (node) node.decorativeParams.pinned = true;
  });
}

Summary

• Chat2DB stores table pins in the relational user_table_pin table using a composite key of user ID and table identifiers.
• The frontend uses pinTable.ts to call either addTablePin or deleteTablePin based on the current pin state.
• REST endpoints at /api/pin/table/add and /api/pin/table/delete handle the create and delete operations through TablePinController.
• MyBatis mappers in TablePinMapper.java execute the raw SQL inserts and deletes against the database.
• Pinned tables persist across sessions because the storage is server-side and user-specific.

Frequently Asked Questions

Where does Chat2DB store table pin information?

Chat2DB stores table pin information in a relational database table named user_table_pin within the chat2db schema. This table contains columns for user_id, datasource_id, database_name, schema_name, and table_name, ensuring each pin is associated with a specific user and database object.

What REST endpoints handle table pinning in Chat2DB?

The application exposes three main endpoints under /api/pin/table: POST /add for creating pins, POST /delete for removing pins, and GET /list for retrieving all pinned tables for the current user. These endpoints are implemented in TablePinController.java and secured to ensure users can only access their own pins.

How does the Chat2DB frontend know which tables are pinned?

When loading the database tree, the frontend calls mysqlService.getTablePinList() to fetch all pinned tables for the authenticated user from the backend. It then merges this data into the tree node objects by setting decorativeParams.pinned = true, which triggers the visual pin indicator in the UI components.

Are pinned tables in Chat2DB shared between devices?

Yes, because the pin data is stored in the server-side database rather than browser local storage, pinned tables are available on any device where the user logs in. The user_id column in the user_table_pin table ensures that these preferences are tied to the user account, not the specific browser or session.

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