db-bridge — 表格配置桥接器 / Table Configuration Bridge

概述 / Overview

（中文）db-bridge 是表格配置的解析层/桥接层，读取 table.json 获取表格元数据，调用 sql-linker 执行 SELECT / INSERT / UPDATE / DELETE 等数据库操作，本身不直接连接数据库，确保字段白名单安全可控。

（English）db-bridge is the parsing/bridging layer for table configurations. It reads table.json for table metadata, then calls sql-linker to execute SELECT / INSERT / UPDATE / DELETE operations — without connecting to the database directly, ensuring field whitelist security and control.

工作流程 / Workflow

（中文）用户请求 → 读取 table.json → 解析表格名/字段 → 调用 sql-linker → 返回结果

（English）Request → Read table.json → Parse table/fields → Call sql-linker → Return result

高效工作流 / Efficient Workflow

（中文）经验教训：从本次10分钟→1分钟的优化中总结而来。新设备首次使用时请严格遵循。 （English）Lesson learned: derived from a 10min→1min optimization. Follow on any new device.

标准流程（三步曲）/ Standard Three-Step Flow

Step 1 — 查现场，不脑补 / Inspect first, don't guess

# （中文）查询当前表结构（已知命令直接跑，不要先读文档）
# （English）Query current table structure (run known commands directly, don't read docs first)
python scripts/sql_linker.py query "SHOW TABLES"          # （中文）查看有哪些表 / （English）List tables
python scripts/sql_linker.py query "DESC \x3Ctable_name>"     # （中文）查看表字段 / （English）View table fields
python scripts/sql_linker.py query "SELECT * FROM \x3Ctable> LIMIT 3"  # （中文）看现有数据 / （English）View existing data

Step 2 — 直接执行（含真实身份上下文）/ Execute directly with real identity

# （中文）INSERT / UPDATE / DELETE — 用双引号包裹 JSON，PowerShell 不丢转义
# （中文）必须传入 --user-label 和 --session-id，确保审计日志真实可溯源
# （English）INSERT / UPDATE / DELETE — wrap JSON in double quotes, PowerShell won't lose escapes
# （English）Always pass --user-label and --session-id to ensure audit log traceability

# （中文）从 OpenClaw 消息 metadata 获取：label → --user-label，id → --session-id
# （English）From OpenClaw message metadata: label → --user-label, id → --session-id
python scripts/sql_linker.py --user-label "\x3Clabel>" --session-id "\x3Csession_id>" \
  insert "\x3Ctable>" "{\"field\":\"value\"}"

python scripts/sql_linker.py --user-label "\x3Clabel>" --session-id "\x3Csession_id>" \
  update "\x3Ctable>" "{\"field\":\"new_value\"}" "\x3Cwhere>"

python scripts/sql_linker.py --user-label "\x3Clabel>" --session-id "\x3Csession_id>" \
  delete "\x3Ctable>" "\x3Cwhere>"

Step 3 — 确认结果 / Confirm result

python scripts/sql_linker.py query "SELECT * FROM \x3Ctable>"  # （中文）回查验证 / （English）Verify result

避免的错误 / Mistakes to Avoid

核心原则 / Core Principle

（中文）CLI 优先，查现场，按需读文档。

（English）CLI-first, inspect before acting, read docs on-demand.

身份上下文注入 / Identity Context Injection

⚠️ （中文）重要：审计日志中的 ip_address 和 session_id 必须真实，否则失去溯源意义。
⚠️ IMPORTANT: ip_address and session_id in audit logs must be real — otherwise audit loses its value.

方式一：CLI 参数（推荐）/ Option 1: CLI flags (recommended)

# （中文）从 OpenClaw 消息 metadata 提取，传入 CLI
# （English）Extract from OpenClaw message metadata, pass to CLI
--user-label   # → metadata.label
--session-id   # → OpenClaw runtime session key

# （中文）完整示例
# （English）Full example
python scripts/sql_linker.py \
  --user-label "openclaw-control-ui" \
  --session-id "agent:hr:dashboard:2454324d-1bd5-4e04-9746-1e5df62bb9a7" \
  insert "supplier_table" "{\"supplier_name\":\"华为\"}"

方式二：环境变量（备选）/ Option 2: Environment variables (fallback)

# （中文）sql-linker 的 set_user_context_auto() 会读取这些环境变量
# （English）sql-linker's set_user_context_auto() reads these env vars
export OPENCLAW_LABEL="openclaw-control-ui"
export OPENCLAW_SESSION="agent:hr:dashboard:2454324d-..."
python scripts/sql_linker.py insert "supplier_table" "{\"supplier_name\":\"华为\"}"

**（中文）**OpenClaw 消息 metadata 示例：{"label": "openclaw-control-ui", "id": "openclaw-control-ui"}
**（English）**OpenClaw message metadata example: {"label": "openclaw-control-ui", "id": "openclaw-control-ui"}

table.json 结构 / table.json Structure

{
  "tables": [
    {
      "table_name": "supplier_table",
      "comment": "供应商信息表",
      "fields": [
        { "name": "id",           "type": "BIGINT",      "pk": true,  "auto": true  },
        { "name": "supplier_code","type": "VARCHAR(32)",  "pk": false, "auto": false },
        { "name": "supplier_name","type": "VARCHAR(128)", "pk": false, "auto": false },
        { "name": "short_name",   "type": "VARCHAR(64)",  "pk": false, "auto": false },
        { "name": "supplier_level","type": "VARCHAR(16)", "pk": false, "auto": false },
        { "name": "contact_person","type": "VARCHAR(64)", "pk": false, "auto": false },
        { "name": "contact_phone", "type": "VARCHAR(32)", "pk": false, "auto": false },
        { "name": "contact_email", "type": "VARCHAR(128)","pk": false, "auto": false },
        { "name": "status",        "type": "VARCHAR(16)", "pk": false, "auto": false },
        { "name": "created_at",   "type": "DATETIME",    "pk": false, "auto": false },
        { "name": "updated_at",   "type": "DATETIME",    "pk": false, "auto": false }
      ]
    }
  ]
}

字段类型说明 / Field Type Reference

sql-linker 调用方式 / sql-linker Invocation

查询 SELECT

linker.query("SELECT id, supplier_code, supplier_name FROM supplier_table WHERE status = %s", ("active",))

插入 INSERT

data = {
    "supplier_code": "SUP001",
    "supplier_name": "示例供应商",
    "contact_person": "张三",
    "status": "active"
}
linker.insert("supplier_table", data)

更新 UPDATE

linker.update(
    "supplier_table",
    {"supplier_name": "新名称", "updated_at": "2026-05-18 15:00:00"},
    "id = %s",
    (1,)
)

删除 DELETE

linker.delete("supplier_table", "id = %s AND status = %s", (1, "inactive"))

CLI 命令速查 / CLI Quick Reference

# （中文）查询
# （English）Query
python scripts/sql_linker.py query  "SELECT * FROM \x3Ctable> LIMIT 10"
python scripts/sql_linker.py query  "SHOW TABLES"
python scripts/sql_linker.py query  "DESC \x3Ctable_name>"

# （中文）插入（JSON 双引号包裹，附身份上下文）
# （English）Insert (JSON in double quotes, with identity context)
python scripts/sql_linker.py --user-label "\x3Clabel>" --session-id "\x3Csession_id>" \
  insert "\x3Ctable>" "{\"field\":\"value\",\"field2\":123}"

# （中文）更新
# （English）Update
python scripts/sql_linker.py --user-label "\x3Clabel>" --session-id "\x3Csession_id>" \
  update "\x3Ctable>" "{\"field\":\"new_value\"}" "\x3Cwhere_clause>"

# （中文）删除
# （English）Delete
python scripts/sql_linker.py --user-label "\x3Clabel>" --session-id "\x3Csession_id>" \
  delete "\x3Ctable>" "\x3Cwhere_clause>"

（中文）注意： Windows PowerShell 下 JSON 参数必须用双引号，单引号会导致转义丢失。
（English）Note: On Windows PowerShell, JSON arguments MUST use double quotes — single quotes cause escape loss.

权限控制 / Access Control

**（中文）**具体权限由 sql-linker 的 read_only 配置和业务规则共同控制。
**（English）**Actual permissions governed by sql-linker's read_only config and business rules.

安全原则 / Security Principles

1. （中文）字段白名单： 只允许 table.json 中定义的字段才能写入
   （English）Field whitelist: only fields defined in table.json are writable

2. （中文）参数化查询： 全部使用 %s + tuple 防止 SQL 注入
   （English）Parameterized queries: always use %s + tuple to prevent SQL injection

3. （中文）审计日志： 由 sql-linker 自动记录操作人、IP、SQL 语句
   （English）Audit log: sql-linker automatically records operator, IP, and SQL

4. （中文）敏感字段脱敏： 脱敏规则由 sql-linker 的 mask_values 配置控制
   （English）Sensitive field masking: rules controlled by sql-linker's mask_values config

快速上手示例 / Quick Start Example

# （中文）1. 查看现有表 / （English）1. View existing tables
python scripts/sql_linker.py query "SHOW TABLES"

# （中文）2. 插入一条数据（带真实身份）
# （English）2. Insert a record (with real identity)
python scripts/sql_linker.py \
  --user-label "openclaw-control-ui" \
  --session-id "agent:hr:dashboard:2454324d-..." \
  insert "supplier_table" "{\"supplier_code\":\"HW001\",\"supplier_name\":\"华为技术有限公司\",\"short_name\":\"华为\",\"supplier_level\":\"A\",\"contact_person\":\"张明\",\"contact_phone\":\"13800138000\",\"contact_email\":\"[email protected]\",\"status\":\"active\"}"

# （中文）3. 验证插入成功 / （English）3. Verify insert success
python scripts/sql_linker.py query "SELECT * FROM supplier_table"

# （中文）4. 更新数据 / （English）4. Update data
python scripts/sql_linker.py --user-label "openclaw-control-ui" --session-id "agent:hr:dashboard:..." \
  update "supplier_table" "{\"status\":\"inactive\"}" "supplier_code = 'HW001'"

# （中文）5. 确认更新结果 / （English）5. Confirm update result
python scripts/sql_linker.py query "SELECT * FROM supplier_table WHERE supplier_code = 'HW001'"