Database Management Component — Implementation Document

I. Overall Architecture

┌──────────────────────────────────────────────────────────────┐
│                    用户界面（Vue 3）                           │
│  DatabaseManager.vue                                         │
│  ┌────────────┐  ┌────────────┐                             │
│  │ 远程数据库  │  │ 本地数据库  │ ← 仅桌面端显示              │
│  └─────┬──────┘  └─────┬──────┘                             │
│        │               │                                     │
│   浏览器: fetch     桌面端: window.lragent.*                 │
│        │               │                                     │
├────────┼───────────────┼─────────────────────────────────────┤
│        ▼               ▼                                     │
│  ┌──────────┐   ┌──────────────┐                            │
│  │ 服务端API │   │ Electron IPC │                            │
│  │ /api/    │   │ ipcMain      │                            │
│  │ platform │   │ .handle()    │                            │
│  └────┬─────┘   └──────┬───────┘                            │
│       │                │                                     │
│       ▼                ▼                                     │
│  server/data/*.db    ~/AppData/UserData/databases/*.db       │
│  (远端服务器)         (桌面端本地存储)                         │
└──────────────────────────────────────────────────────────────┘

核心思路：同一个 Vue 组件根据运行环境（浏览器 vs Electron 桌面端）自动选择不同的数据通道：

• Browser: Directly calls server-side REST APIs.
• Desktop: Accesses local file system + remote HTTP via window.lragent.* (preload bridge → ipcMain handler).

II. Detailed Three‑Layer Implementation

2.1 Server (Node.js) — Remote Data Source

File: server/routes/platform-asset.js → handleDatabaseManagerRoutes()

Data directory: server/data/ (defined by DATA_DIR = path.join(__dirname, '..', 'data'))

Three API endpoints:

| Endpoint | Method | Function | Returns |

|------|------|------|------|

| /api/platform/databases | GET | List all .db files | { success, databases: [...], total } |

| /api/platform/databases/:name/download | GET | Download .db file | application/octet-stream stream |

| /api/platform/databases/:name/tables | GET | Query table structure | { success, tables: [{ name, sql, rowCount }] } |

How the List API Works:

1. Recursively scan the server/data/ directory and subdirectories
2. Find all files with the .db extension
3. Read file size and modification time
4. Match Chinese descriptions from the DB_DESCRIPTIONS dictionary
5. Return results sorted in descending order by file size

How the Download API Works:

1. Perform security filtering on filenames (remove .., /, \, prevent path traversal)
2. Concatenate DATA_DIR + safeName to get the absolute path
3. Verify file existence
4. Set Content-Type: application/octet-stream + Content-Disposition: attachment
5. Stream the file using fs.createReadStream().pipe(res)

How the Table Structure API Works:

1. Also perform path security filtering
2. Open the database in read-only mode using better-sqlite3 (or sqlite-compat)
3. Query sqlite_master to get all user tables
4. Execute SELECT COUNT(*) for each table to get row counts
5. Return results after closing the database connection

Key Security Measures:

const safeName = dbName.replace(/\.\./g, '').replace(/[\/\\]/g, '');

防止路径穿越攻击。下载和表结构两个端点都做了此过滤。

2.2 Desktop Client (Electron) — Local Management + Remote Sync

File: electron-app/main.js → registerIpcHandlers()

Local storage directory: ~/AppData/Roaming/lragent/databases/ (defined by app.getPath('userData') + '/databases')

6 IPC Handlers:

| Channel | Parameters | Function | Implementation |

|------|------|------|----------|

| db-list-local | None | List locally downloaded .db files | fs.readdirSync scanning dbStoreDir |

| db-list-remote | serverUrl? | List remote databases | HTTP GET → try ylxt.chat first, fallback to ylxt.chat |

| db-download | dbName, serverUrl? | Download remote .db to local | HTTP GET → fs.createWriteStream with progress notifications |

| db-delete-local | dbName | Delete local .db file | fs.unlinkSync |

| db-query | dbName, sql, params? | SQL query on local .db | better-sqlite3 readonly mode |

| db-tables | dbName | View local .db table structure | better-sqlite3 + sqlite_master |

Additional features (preload already exposed, not yet used by components):

| Channel | Function |

|------|------|

| db-export | Opens save dialog, copies local .db to user-specified location |

| db-import | Opens file selection dialog, copies external .db to local storage |

| db-store-path | Returns the local storage directory path |

Download progress notification:

mainWindow?.webContents.send('db-download-progress', {
  dbName, status: 'downloading', received, total, percent
})

前端可通过 window.lragent.onDbDownloadProgress(cb) 监听。

Remote connection fallback logic:

db-list-remote / db-download:
  1. 先尝试 https://ylxt.chat/api/platform/databases
  2. 失败则回退 https://ylxt.chat/api/platform/databases
  3. 都失败返回合并错误信息

2.3 前端（Vue 3）— 统一交互界面

文件：src/views/DatabaseManager.vue

Environment detection:

const isDesktop = !!(window.lragent && window.lragent.dbListRemote)

桌面端 preload.js 会注入 window.lragent 对象，浏览器环境不存在。

Dual Tab design:

• Remote Database tab: Displayed in both browser and desktop clients
• Local Database tab: Displayed only in desktop client (v-if="isDesktop")

Operation matrix:

| Operation | Browser・Remote | Desktop・Remote | Desktop・Local |

|------|:-----------:|:-----------:|:-----------:|

| Browse list | fetch API | IPC → HTTP | IPC → local fs |

| View table structure | fetch API | fetch API | IPC → better-sqlite3 |

| Download | Tag | IPC → HTTP → local file | — |

| SQL Query | — | — | IPC → better-sqlite3 |

| Delete | — | — | IPC → fs.unlink |

| Import | — | — | IPC → dialog (exposed in preload) |

| Export | — | — | IPC → dialog (preload exposed) |

III. What the remote server needs to do

3.1 Minimal solution: Just place files in the specified directory

Yes, simply place them in the specified directory. The server API will automatically scan.

Steps:

1. Place .db files in the server/data/ directory
2. If there are subdirectories for categorization (e.g., kb/, kb-snapshots/), the API will scan recursively
3. File extensions must be .db
4. Restart the server to take effect (no caching, real-time scanning)

Current databases already in server/data/ (tested 27+):

server/data/
├── bossagents.db          (6.3 MB)  主业务数据库
├── rule_engine.db         (353 MB)  规则引擎
├── boss_analytics.db      (62 MB)   审计日志
├── digital-staff.db       (1.5 MB)  Digital Staff
├── core_runtime.db        (26 MB)   运行时主库
├── sciot-metadata.db      (8 MB)    SCSAI元数据
├── aml.db                 (4 MB)    AML规则
├── inspection.db          (2 MB)    巡检记录
├── sccapp_process_data.db (600 MB)  工艺规程
├── sciot_import.db        (65 MB)   SCSAI导入
├── kb/
│   └── phos_chem.db       (247 MB)  磷化工知识库
├── kb-snapshots/
│   └── phos_chem_pre-convert_*.db  知识库快照
└── ... (共 27+ 个)

3.2 添加数据库说明

在 server/routes/platform-asset.js 的 DB_DESCRIPTIONS 字典中添加条目：

const DB_DESCRIPTIONS = {
  'rule_engine.db': '规则引擎（巡检规则、模板、提示词）',
  'bossagents.db': '主业务数据库',
  // 添加新数据库的说明：
  'your_new_db.db': '你的数据库说明',
};

不添加说明也能工作，说明列会显示 -。

3.3 Tasks for official website deployment (ylxt.chat)

The official website needs to run the LRAgent Node.js server and ensure:

Required items:

1. Deploy server.js: node server.js listening on a port (default 3006)
2. Place .db files: Put the database files to be shared in the server/data/ directory
3. HTTPS: The Electron desktop client defaults to connecting to https://ylxt.chat first, requiring an SSL certificate
4. CORS (if accessed cross-origin via browser): The server already has built-in CORS middleware, no additional configuration needed
5. Reverse proxy: If using Nginx, proxy the /api/platform/databases* path

Nginx configuration example:

server {
    listen 443 ssl;
    server_name ylxt.chat;

    ssl_certificate     /path/to/cert.pem;
    ssl_certificate_key /path/to/key.pem;

    # 数据库管理 API
    location /api/platform/databases {
        proxy_pass https://ylxt.chat;
        proxy_set_header Host $host;
        proxy_read_timeout 300s;  # 大文件下载需要长超时
        proxy_buffering off;       # 流式下载
        client_max_body_size 0;    # 不限制请求体
    }

    # 其他 API
    location /api/ {
        proxy_pass https://ylxt.chat;
        proxy_set_header Host $host;
    }

    # 前端静态文件
    location / {
        root /path/to/lragent/dist;
        try_files $uri $uri/ /index.html;
    }
}

大文件下载注意事项：

• sccapp_process_data.db is 600MB; Nginx default proxy_buffering on caches the entire response
• Must set proxy_buffering off + proxy_read_timeout 300s
• Or use the X-Accel-Buffering: no header to disable Nginx buffering.

3.4 Alternative Solutions Without Deploying a Server

If the official website does not want to run a Node.js server, a pure static file approach can be used:

Option A: Nginx directly hosts .db files

location /databases/ {
    alias /var/www/databases/;
    autoindex on;           # 开启目录列表
    autoindex_format json;  # JSON 格式目录列表
}

此方案需要修改桌面端 db-list-remote 的解析逻辑，因为返回格式不同。

Option B: Pre-generate a database list JSON

1. Write a script to periodically scan directories and generate databases.json
2. Place it at https://ylxt.chat/databases.json
3. Set download links to point to https://ylxt.chat/databases/xxx.db
4. Modify the db-list-remote handler to parse this JSON

Option C: Object Storage (OSS/S3)

1. Upload .db files to an OSS bucket
2. Generate a databases.json index file
3. Set download links to point to OSS signed URLs
4. Suitable for CDN acceleration of large file downloads

IV. Data Flow Details

4.1 Remote List Loading (Browser)

用户打开页面
  → onMounted() → loadRemoteDatabases()
  → get('/api/platform/databases')        ← src/utils/api.js (fetch)
  → Node.js server.js 路由匹配
  → handleDatabaseManagerRoutes()
  → scanDir('server/data/')               ← 递归扫描
  → 返回 JSON { databases: [...] }
  → Vue 组件渲染表格

4.2 远程列表加载（桌面端）

用户打开页面
  → onMounted() → loadRemoteDatabases()
  → window.lragent.dbListRemote()         ← preload.js 桥接
  → ipcMain.handle('db-list-remote')
  → httpGet('https://ylxt.chat/api/platform/databases')
  → 失败? → httpGet('https://ylxt.chat/api/platform/databases')
  → JSON.parse(response)
  → 返回给渲染进程
  → Vue 组件渲染表格

4.3 下载到本地（桌面端）

用户点击"Download"
  → downloadDb(db)
  → window.lragent.dbDownload(db.name)    ← preload.js 桥接
  → ipcMain.handle('db-download')
  → downloadFile('https://ylxt.chat/api/platform/databases/xxx.db/download', destPath)
  → HTTP 流式下载 → fs.createWriteStream
  → 进度通知: webContents.send('db-download-progress', {...})
  → 下载完成 → 返回 { success: true, path, size }
  → Vue 组件显示成功消息

4.4 本地 SQL 查询（桌面端）

用户点击"查询" → 输入 SQL → 点击"执行"
  → runQuery()
  → window.lragent.dbQuery(dbName, sql)   ← preload.js 桥接
  → ipcMain.handle('db-query')
  → require('better-sqlite3')
  → new Database(dbPath, { readonly: true })
  → db.prepare(sql).all(...params)
  → db.close()
  → 返回 { success: true, rows: [...] }
  → Vue 组件渲染结果表格

五、安全设计

| 层级 | 措施 |

|------|------|

| Server Path | dbName.replace(/\.\./g, '').replace(/[\/\\]/g, '') to prevent path traversal |

| Server Download | Return 404 if file does not exist, 500 for exceptions |

| Server Table Query | better-sqlite3 opens with readonly: true, closes immediately after query |

| Desktop Query | readonly: true mode, write operations disabled |

| Desktop Delete | Frontend confirm() secondary confirmation |

| Electron | contextIsolation: true + nodeIntegration: false, preload whitelist exposure |

VI. Extension Points

6.1 Implemented but Not Yet Frontend-Integrated Features

6.2 Future Extensions

• Incremental sync: Download only changed .db files based on mtime
• Compressed transfer: Server-side gzip compression of .db files (600MB → ~100MB)
• Access Control: Add API Key authentication to /api/platform/databases*
• Selective Synchronization: Users check the databases they need, instead of downloading all
• Scheduled Synchronization: The desktop client periodically checks for remote updates and syncs automatically in the background

VII. Quick Deployment Checklist

Official Website Server (ylxt.chat)

• [ ] Deploy the LRAgent server (node server.js)
• [ ] Place the .db files to be shared into server/data/
• [ ] Add Chinese descriptions in DB_DESCRIPTIONS
• [ ] Configure HTTPS certificate
• [ ] Configure Nginx reverse proxy (note proxy_buffering off and long timeout settings)
• [ ] Test: curl https://ylxt.chat/api/platform/databases returns JSON

Desktop Client Users

• [ ] Install the LRAgent desktop client
• [ ] Open the "Database Management" page (Sidebar → System Management → Database Management)
• [ ] Browse and download under the "Remote Databases" tab
• [ ] View, query, and export under the "Local Databases" tab

Browser Users

• [ ] Visit https://ylxt.chat → Sidebar → Database Management
• [ ] Browse the remote database list and view table structures
• [ ] Click the "Download" button to download the .db file locally