Database Architecture Overview

Last Updated: 2026-07-06

Refactored according to PPS Process Specification Implementation Model: One independent library per domain, library name = domain name, with complete documentation and code comments

? Design Principles

? Database List (9 production databases + 5 auxiliary files)

Overview Table

Detailed Database Description

1️⃣ rule_engine.db — SCIOT Rule Engine + Metadata Cache ?

Path: data/rule_engine.db

Size: 2.5MB | Total Rows: 11,203

Core routing: getSciotDb() in server/routes/aml.js points to this database

Data source: Synchronized from generated/full-aml-extract.json on startup + manual import from JSON

#### Table List

| Table Name | Row Count | Purpose | Data Source |

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

| sciot_rules_v2 | 7,053 | Main rules table (validate/create/identify/inspect, etc.) | Imported from generated/sciot-rules-auto.json |

| sciot_properties | 2,821 | Property definitions | Synchronized from full-aml-extract.json on startup |

| sciot_item_types | 298 | Object class list | Synchronized from full-aml-extract.json on startup |

| sciot_templates | 298 | Templates (one per object class) | Synchronized from full-aml-extract.json on startup |

| sciot_relationships | 431 | Relationship Definitions | full-aml-extract.json Start Sync |

| prompt_templates | 298 | Object Prompts | generated/sciot-prompts-auto.json Import |

| sciot_lifecycle_maps | 0 | Lifecycle Maps | Not Imported |

| sciot_lifecycle_states | 0 | Lifecycle States | Not Imported |

| sciot_list_values | 0 | List Values | Not Imported |

| sciot_methods | 0 | ⚠️ Data missing | Originally in the 35.8MB full version |

| sciot_sequences | 0 | ⚠️ Data missing | Originally in the 35.8MB full version |

| sciot_prompts | 0 | Deprecated (now using prompt_templates) | — |

| pps_* tables | 0 | Residual empty tables (should belong to sccapp_process_data.db) | — |

#### Code References

| File | Line | Purpose |

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

| server.js | 259 | Startup synchronization |

| server.js | 2097 | GET /api/system/health |

| server.js | 2109 | GET /api/system/stats |

| server.js | 2119 | GET /api/aml/auto-all |

| server.js | 2133 | GET /api/aml/sciot/templates |

| server.js | 2352 | Rule engine |

| server.js | 2391 | Create log |

| server.js | 2434 | Correction record |

| server.js | 2607 | Prompt template |

| server.js | 2829 | Rule engine trigger |

| server.js | 2872 | Intent recognition |

| server.js | 3012 | Type list |

| server.js | 4371 | Publish article |

| server.js | 4393 | Publish article |

| server.js | 4447 | Product supplier |

| server.js | 6566 | Database Initialization |

| server/routes/aml.js | 597-656 | getSciotDb() Singleton Connection |

#### Recent Fixes

| Fix Item | Before | After |

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

| prompt_templates Table Structure | Missing 10 columns | Complete 18 columns (including system_prompt/item_type_name/status, etc.) |

| Prompt Data | 0 records | 298 records ✅ |

| Rule Data | 75 records | 7,053 records ✅ |

| getSciotDb() Comments | None | Complete comments + prompt_templates auto-validation |

2️⃣ sccapp_process_data.db — Process Specifications ✅ Successfully deployed per specification

Path: data/sccapp_process_data.db

Size: 303MB | Total Rows: 920,367

Documentation: data/PPS_DATABASE_README.md

#### Table List

| Table Name | Row Count | Purpose |

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

| sccapp_tech_data | 300,887 | Parameters |

| sccapp_steps | 280,045 | Steps |

| sccapp_procs | 163,456 | Processes |

| sccapp_tech_files | 112,564 | Technical Files (Valid 26,092 + Empty 86,472) |

| pps_techfile_reports | 54,272 | Technical File Reports |

| pps_techfile_resources | 8,138 | Technical File Resources |

| process_specs | 1,005 | Process Specifications |

| sccapp_sync_progress | 0 | Synchronization Progress Tracking |

| Others | 0 | Reserved Empty Tables |

#### Code References

| File | Line | Purpose |

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

| server.js | 3202 | /api/process-spec/ routes |

| server.js | 3879 | /api/pps/* query routes |

| server.js | 4168 | /api/sccapp-convert/* routes |

3️⃣ core_runtime.db — BossAgents Runtime Database

Path: server/data/core_runtime.db (Redirected via canonicalPath in server/sqlite-compat.js; createDatabase('core_runtime.db') in code lands here)

Size: 4.0MB | Total Rows: 531

Purpose: SCSAI sync cache / Document assets / AI action & reasoning logs / Quality KPI & SPC / BOM & change runtime cache (Not core business data; core data resides in SCSAI/PLM)

#### Tables with Data

| Table Name | Row Count | Purpose |

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

| SCSAI_sync_cache | 296 | SCSAI Synchronization Cache |

| SCSAI_sync_log | 9 | Synchronization Log |

| alignment_matrix | 18 | Alignment Matrix |

| alignment_batch_strategy | 6 | Batch Strategy |

| content_brand_config | 5 | Brand Configuration |

| content_calendar | 6 | Content Calendar |

| doc_generation_templates | 4 | Document Generation Templates |

| alignment_resource_budget | 1 | Resource Budget |

#### Empty Tables (80+ tables, all with 0 rows)

These table structures are created but contain no data; they are in a reserved state. Domains involved include:

BOM Management (bom_), Change Management (change_), Quality Management (quality_), Inventory (inventory_),

Documents (doc_), Project Management (project_), Orders (orders), Customers (customers), Vendors (vendors),

Process (pps_/process_), Employees (employees), Knowledge Base (knowledge_*), etc.

#### Referenced Code

Used globally, library name not explicitly specified (managed by db-adapter or database1.js)

4️⃣ sciot-metadata.db — Complete SCIOT metadata (backup)

Path: server/data/sciot-metadata.db

Size: 6.4MB | Total rows: 49,879

Purpose: The most complete metadata source for SCIOT (includes extended data not present in rule_engine.db)

#### Differences compared to rule_engine.db

| Table | rule_engine.db | sciot-metadata.db | Difference |

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

| sciot_item_types | 298 | 1,217 | +919 |

| sciot_properties | 2,821 | 41,240 | +38,419 |

| sciot_relationships | 431 | 587 | +156 |

| sciot_list_values | 0 | 6,290 | +6,290 |

| sciot_lifecycle_maps | 0 | 48 | +48 |

| sciot_lifecycle_states | 0 | 192 | +192 |

Note: This database contains more complete data, but getSciotDb() currently points to rule_engine.db. To use the full dataset, either change getSciotDb() to read from this database or implement regular synchronization.

5️⃣ sciot_import.db (Legacy) — Digital Employee + Residual Data

Path: server/data/sciot_import.db

Size: 868KB | Total Rows: 482

Purpose: Digital employee data + a small amount of legacy rules.

Status: ❌ Obsolete. The 35.8MB full version (containing 2,817 prompt words, 2,367 rules, 1,205 templates) mentioned in the design document is lost (0 bytes).

#### Table List

| Table Name | Row Count | Purpose | Migration Status |

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

| staff_execution_logs | 188 | Staff Execution Logs | Not Migrated |

| staff_execution_outcomes | 72 | Execution Outcomes | Not Migrated |

| staff_tasks | 82 | Staff Tasks | Not Migrated |

| sciot_rules_v2 | 75 | Rules (Legacy Data) | Migrated to rule_engine.db |

| digital_staff | 34 | Digital Staff Configuration | Not Migrated |

| staff_lessons | 17 | Lessons Learned | Not Migrated |

| user_assets | 9 | User Assets | Not Migrated |

| Others | 2 | test_cases(2), rule_simulations(1) | — |

6️⃣ aml_store.db — AML Object Storage

Path: server/data/aml_store.db

Size: 42MB | Total Rows: 8,236

Purpose: Stores intermediate objects generated by AML assembly

| Table Name | Row Count | Purpose |

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

| aml_objects | 8,235 | AML objects |

| sqlite_sequence | 1 | Auto-increment management |

7️⃣ boss_analytics.db — Audit Logs + Risk Alerts

Path: server/data/boss_analytics.db

Size: 5.2MB | Total Rows: 23,085

| Table Name | Row Count | Purpose |

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

| audit_logs | 12,119 | Audit logs |

| risk_alerts | 10,784 | Risk alerts |

| daily_snapshots | 175 | Daily snapshots |

| dispatch_history | 3 | Dispatch history |

8️⃣ enterprise_store.db — Enterprise Private Library

Path: server/data/enterprise_store.db

Size: 68KB | Total Rows: 24

| Table Name | Row Count | Purpose |

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

| enterprise_objects | 19 | Enterprise Objects |

| sciot_corrections | 5 | Correction Records |

Reference code: server/routes/private-library.js

? Data Flow

┌──────────────────────────────────────────────────────┐
│  SCSAI 服务端 (源端)                                   │
│  ItemType/Property/Relationship/Lifecycle/List/Method│
└──────────┬───────────────────────────────────────────┘
           │ template-sync-service.js (启动时同步)
           ▼
┌──────────────────────┐    ┌──────────────────────┐
│ rule_engine.db        │    │ sciot-metadata.db     │
│ (元数据缓存, 298类型)  │    │ (完整元数据, 1217类型) │
│ 2.5MB / 11,203行     │◄──►│ 6.4MB / 49,879行     │
└──────────┬───────────┘    └──────────────────────┘
           │ 手动导入 JSON
           ▼
┌──────────────────────┐
│ JSON 数据源           │
│ generated/            │
│ ├─ full-aml-extract   │
│ ├─ sciot-prompts-auto │
│ ├─ sciot-rules-auto   │
│ └─ sciot-templates-auto│
└──────────────────────┘

┌──────────────────────┐    ┌──────────────────────┐
│ core_runtime.db         │    │ sccapp_process_data.db  │
│ (业务数据, ~350行)    │    │ (工艺规程, 920K行)   │
│ BOM/变更/文档/订单等  │    │ ✅ 已按规范独立落地    │
└──────────────────────┘    └──────────────────────┘

┌──────────────────────┐    ┌──────────────────────┐
│ boss_analytics.db    │    │ aml_store.db          │
│ (审计/风险, 23K行)   │    │ (AML对象, 8.2K行)    │
└──────────────────────┘    └──────────────────────┘

┌──────────────────────┐
│ sciot_import.db(旧)  │
│ (Digital Staff, ~480行)   │
│ ❌ 废弃, 35.8MB完整  │
│    版已丢失(0字节)    │
└──────────────────────┘

✅ 本次修复清单（2026-07-06）

1. prompt_templates 表结构修复

• 问题: 表缺 system_prompt / item_type_name / operation_type / version / status / scope / is_builtin / parent_id / score / usage_count 共 10 列
• 表现: GET /api/aml/sciot/prompts 查不存在列 → SQLite 抛错 → undefined.c → 前端报 Cannot read properties of undefined (reading 'c')
• Repair: 加 10 列，重建表为 TEXT PRIMARY KEY 结构

2. 提示词数据导入（298 条）

• 来源: generated/sciot-prompts-auto.json
• 目标: rule_engine.db → prompt_templates 表
• 验证: 覆盖全部 298 个对象类（APQP CC Catalog, Change Request, Document 等）

3. 规则数据导入（7,053 条）

• 来源: generated/sciot-rules-auto.json (3,489 条) + 已有数据 (3,564 条)
• 目标: rule_engine.db → sciot_rules_v2 表
• 范围: identify / create / create_pre / validate / inspect 等

4. getSciotDb() 代码注释 + 验证

• 文件: server/routes/aml.js L585-656
• 修改: 加完整注释（数据库路径、表清单、数据来源）+ prompt_templates 表结构自动校验/重建逻辑

5. server.js 数据库引用路径更正

• 数量: 16 处
• 变更: server/data/sciot_import.db → data/rule_engine.db
• 加注释: 每处标注数据库用途

6. 缺失表创建

• 新建表: sciot_lists, sciot_forms, sciot_identities, sciot_permissions, sciot_sqls, sciot_variables
• 目标: 确保"系统对象管理"页面查询不崩

❌ 遗留问题

| # | 问题 | 影响 | 修复方案 |

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

| 1 | sciot_methods data loss (original 2,236 records) | "System Object Management" → Method(0) | Re-sync from SCSAI or restore from backup |

| 2 | sciot_sequences data loss | "System Object Management" → Sequence(0) | Re-sync from SCSAI or restore from backup |

| 3 | rule_engine.db missing 919 item_type (current 298, complete 1,217) | Some object classes are not visible in the rule engine | Migrate from sciot-metadata.db |

| 4 | rule_engine.db missing 38,419 properties (current 2,821, complete 41,240) | Incomplete property data | Migrate from sciot-metadata.db |

| 5 | rule_engine.db missing 6,290 list values | List value references invalid | Migrate from sciot-metadata.db |

| 6 | server/sciot_import.db 0 bytes (35.8MB full version lost) | Original design data no longer available | Search Git history or restore from filesystem |

| 7 | Two sciot_import.db files with naming confusion | Developers unsure which to use | Deprecate both, centralize on rule_engine.db |

? Naming conventions

data/<领域>_<类型>.db

| 命名 | 路径 | 职责 |

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

| rule_engine.db | data/ | SCIOT rules + templates + prompts + metadata cache |

| sccapp_process_data.db | data/ | ✅ Process specification data |

| core_runtime.db | server/data/ | BossAgents runtime library (SCSAI sync cache / document assets / AI logs / quality KPIs / BOM·change cache) |

| sciot-metadata.db | server/data/ | Complete SCIOT metadata |

Suggested Renaming (Phase 2)

? Development and Debugging Guide

How to check which library a specific API uses

# 方法 1: grep 库名
grep -n "data/rule_engine.db\|sciot_import.db" server.js

# 方法 2: grep 路由
grep -n "/api/aml/sciot/prompts" server/routes/aml.js

# 方法 3: 查表行数
node -e "
  const p=require('path');const c=require('./server/db-adapter');
  (async()=>{await c.init();const d=c.createDatabase(p.resolve('data/rule_engine.db'));
  console.log(d.prepare('SELECT COUNT(*) as c FROM prompt_templates').get().c);
  d.close();})();
"

启动时数据丢失

如果 rule_engine.db 数据丢失，从 JSON 重新导入：

node -e "
  // 参考 server.js:276 的同步逻辑
  // 从 generated/full-aml-extract.json 同步 item_types/properties/relationships/templates
  // 从 generated/sciot-prompts-auto.json 导入提示词
  // 从 generated/sciot-rules-auto.json 导入规则
"

? Appendix: Table Field Mapping

prompt_templates table (complete structure)

CREATE TABLE prompt_templates (
    id             TEXT  PRIMARY KEY,       -- 提示词 ID
    name           TEXT  NOT NULL,           -- 名称
    description    TEXT,                     -- 描述
    content        TEXT  NOT NULL,           -- 提示词正文
    variables      TEXT,                     -- 变量列表 JSON
    prompt_type    TEXT  DEFAULT 'creation', -- 类型 (creation/validate/identify)
    item_type_name TEXT,                     -- 适用 ItemType
    version        INTEGER DEFAULT 1,        -- 版本号
    status         TEXT  DEFAULT 'active',   -- 状态 (active/deprecated/testing)
    parent_id      TEXT,                     -- A/B 测试父级
    score          REAL  DEFAULT 0,          -- 质量评分
    usage_count    INTEGER DEFAULT 0,        -- 使用次数
    operation_type TEXT,                     -- 操作类型
    system_prompt  TEXT,                     -- 系统提示词
    scope          TEXT,                     -- 作用范围
    is_builtin     INTEGER DEFAULT 0,        -- 是否内置
    created_at     TEXT,                     -- 创建时间
    updated_at     TEXT                      -- 更新时间
);

sciot_rules_v2 table (complete structure)

CREATE TABLE sciot_rules_v2 (
    id              TEXT  PRIMARY KEY,       -- 规则 ID
    name            TEXT  NOT NULL,           -- 规则名称
    description     TEXT,                     -- 规则描述
    scope           TEXT  NOT NULL,           -- 作用范围 (validate/create_pre/create_post)
    item_type_name  TEXT,                     -- 适用 ItemType
    severity        TEXT  DEFAULT 'warning',  -- 严重级别
    priority        INTEGER DEFAULT 2,        -- 优先级
    condition       TEXT,                     -- 条件配置 JSON
    condition_script TEXT,                    -- 条件 JS 脚本
    action_type     TEXT,                     -- 动作类型
    action_config   TEXT,                     -- 动作配置 JSON
    action_script   TEXT,                     -- 自定义动作 JS
    prompt_template TEXT,                     -- 内联提示词模板
    prompt_template_id TEXT,                  -- 关联 prompt_templates.id
    is_active       INTEGER DEFAULT 1,        -- 是否启用
    is_builtin      INTEGER DEFAULT 0,        -- 是否内置
    source          TEXT DEFAULT 'manual',    -- 来源
    version         INTEGER DEFAULT 1,        -- 版本号
    tags            TEXT,                     -- 标签
    category        TEXT,                     -- Category
    created_at      TEXT,                     -- 创建时间
    updated_at      TEXT                      -- 更新时间
);