Database Unified Management Solution (Document Unification + PLM Classification + Management Functions)

Status: Proposal (To be implemented after approval)

Date: 2026-08-30

Basis: Code Research (Explore agent) + docs/DATABASE_GUIDE.md (Current Baseline) + User Instructions

Goal: Ensure Database Consistency — ① Unify and deduplicate document/table definitions ② Clarify PLM correspondences ③ "Manage" within System Management (View/Cloud Download/PLM Sync, can be unidirectional or bidirectional)

0. Current Baseline (Verified Hard Facts)

0.1 Existing Backend Framework (Reusable, not from scratch)

server/routes/platform-asset.js (already mounted with /api/platform, /api/db-datasource, /api/db-metadata, /api/db-sync, /api/db-mapping, /api/unified-assets) Already implemented:

• GET /api/platform/databases — Scan data/ to list all .db files (including size, description)
• GET /api/platform/databases/:name/tables — Read sqlite_master to return table names + CREATE TABLE SQL + row count
• GET /api/platform/databases/:name/download — Download .db as an attachment
• /api/db-datasource (data source CRUD + testing), /api/db-metadata (metadata/column collection), /api/db-sync (data synchronization), /api/db-mapping (table→object mapping)
• Supporting module: server/services/db-metadata-collector.js (~1050 lines, connector factory + metadata/row count collection + table→ItemType mapping)

Frontend: src/views/DatabaseManager.vue (route /database-manager, navigation group "Integration & Logs") already supports listing/viewing tables/downloading; src/views/DataCollectionView.vue (/metadata-collection) is the data source/metadata/mapping/sync wizard.

Existing centralized table creation entry (but only manages its own database): server/database1.js (DatabaseManager), server/services/db-schema-init.js, server/db/migrations/v2_incremental.js, server/db/dialect.js (SQLite/MySQL dialects).

0.2 Existing Gaps (i.e., those to be addressed now)

1. No single source of truth for table definitions: ~90 CREATE TABLE statements scattered across modules under the full server/ directory; core_runtime.db (140 tables) and rule_engine.db (64 tables) **duplicate a batch of sciot_ / inspection_ tables; leads to tables being created in the wrong database and missing columns (incidents occurred: risk_alerts missing columns, inspection_logs missing table).
2. No classification for PLM correspondences: Which tables must have PLM counterparts, which are local-only, and what the synchronization direction is—all exist only in project members' minds, with no documentation.
3. No "cloud download/restore" capability: The only "download" is for local files; no OSS/COS/remote snapshot retrieval.
4. PLM synchronization status is invisible: Synchronization logic is scattered (sccapp-sync-service, sync-SCSAI-meta, staff-persistence, etc.), with no unified scheduling/status dashboard; DatabaseManager.vue does not display "whether PLM mirror/last sync time/differences."
5. .gitignore uses *.db global ignore → Table definitions are not versioned with the .db files; definitions are lost when running on a different computer.

1. Unified Schema Registry (Unified Documentation + Deduplication)

1.1 Design Principle: The registry = single source of truth, with a "generation + curation" two-layer approach to prevent documentation from drifting from reality.

• Generation Layer (Automatic): Scripts scan sqlite_master / PRAGMA table_info from all operational databases, producing a machine-readable snapshot of the current state: server/db/schema-registry.generated.json (database→table→column name/type/constraints/row count/source file).
• Curation Layer (Manually Maintained): server/db/schema-classification.json overlays authoritative ownership and classification for each table: ownerDb (eliminates duplicates, keeping only one owner for duplicate tables), plmClass, syncDirection, category, description.
• The human-readable document docs/DATABASE_GUIDE.md is now generated by rendering these two JSON files**, no longer manually maintained → no drift forever.

1.2 Deduplication: Define a clear "Database → Responsibility" single mapping (Proposal, pending your confirmation)

| Database (Unique Responsibility) | Owned Tables (owner) | Duplicate Tables to be Removed from Other Databases |

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

| sciot-metadata.db | Meta-model Mirror: sciot_item_types/sciot_properties/sciot_relationships/sciot_templates/sciot_lifecycle_maps | Delete sciot_* copies in core_runtime/rule_engine |

| rule_engine.db | Rules + Digital Staff + Inspection: sciot_rules_v2, digital_staff, inspection_logs/inspection_issues, sccapp_ (Process Cache) | Delete inspection_ copies in core_runtime |

| core_runtime.db | Runtime/Business Cache + Local Business: SCSAI_items/aras_items/pps_*, Order/BOM/Quality/Document business tables | No longer store meta-model/inspection tables |

| sccapp_process_data.db | Process Master Data Mirror (Unique) | — |

| Local Business Databases (boss_tenants/bossagents/feedback/quality_/kb//enterprise_store/boss_analytics) | Their respective tables | — |

This layer's "who owns which duplicate table" is the key decision point, to be confirmed by you below.

1.3 Registry File Implementation

• Added scripts/gen-schema-registry.cjs: Scans server/data/*.db (excluding release8/mini-program/snapshot/temporary databases), writes to server/db/schema-registry.generated.json.
• Added server/db/schema-classification.json: Manually curated layer (first derived skeleton from 'generated' by script, manually filled ownerDb/plmClass/syncDirection).
• Added server/db/schema-registry.js: Reads two JSON files, provides getRegistry(), getTable(ownerDb,table), listByPlmClass(), diffAgainstLive() (compares runtime live state against registry at runtime, alerts on drift).
• Consolidated 'self-built tables' for all modules: Modules no longer CREATE TABLE; instead, declare in schema-classification.json, and tables are created by a unified ensureAllSchemas() (see §3.2).

2. PLM Correspondence Classification (Must have PLM / Local-only + Sync Direction)

2.1 Three-Level Classification Model

2.2 Classification Matrix (Proposal, by Database)

| Database / Table | Classification | Direction | Notes |

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

| sciot-metadata.db / sciot_* | PLM_SOURCE | PLM→Local | Full mirror of metamodel, pulled by sync-SCSAI-meta |

| sccapp_process_data.db / sccapp_* | PLM_SOURCE | PLM→Local | Process master data mirror |

| core_runtime / SCSAI_items, aras_items, pps_* | PLM_SOURCE | PLM→Local | Business object synchronization cache |

| core_runtime / ARTICLE, STAFF_TASK, EXECUTION_LOG, RULE_INTERPRETATION | PLM_SHADOW | Bidirectional | Content domain rectified, classes created in PLM and written back |

| core_runtime / DIGITAL_EMPLOYEE, AGENT | PLM_SHADOW | Bidirectional | staff-persistence already pushed to PLM |

| rule_engine / digital_staff, sciot_rules_v2 | PLM_SHADOW | Bidirectional | Digital employees/rules have corresponding entries in PLM |

| boss_tenants / bossagents / feedback / quality_ / kb/ / enterprise_store / boss_analytics | LOCAL_ONLY | None | Purely local business/tenant/knowledge/telemetry |

Note: ARTICLE, etc., are new PLM categories created in this round of rectification; DIGITAL_EMPLOYEE/AGENT were promoted in the previous round. The absence of PLM counterparts in other local databases is an established fact.

3. Database Management Function Design (Managed within "System Management")

3.1 Entry Integration

• Add a new independent navigation group "Data Management" under System Management (or integrate into the existing admin system), upgrade the current DatabaseManager.vue to a unified console, and add new tabs:

1. Overview: All database cards (size/table count/owner responsibility/PLM category badge/last sync time/health status)
2. Schema Browser: Select database → tree table → column definitions (from registry, annotated with owner/category/sync direction)
3. SQL Console (Read-only Sandbox): SELECT whitelist, prohibit DROP/DELETE/UPDATE/INSERT (to prevent accidental deletion)
4. Cloud Sync: Pull snapshot from cloud and restore (see 3.3)
5. PLM Sync: Each PLM-type table shows direction/last sync/diff, supports manual trigger of "pull/push" (see 3.4)

3.2 Backend additions/extensions (server/routes/database-manager.js, mounted at /api/db)

Reuse existing capabilities of platform-asset.js, add:

• GET /api/db/registry —— Returns unified registry (including classification/direction/owner), data source for frontend overview and Schema browsing
• GET /api/db/registry/diff —— Runtime comparison of actual state vs. registry, returns drift (missing tables/columns/extra duplicate tables)
• POST /api/db/ensure —— Triggers ensureAllSchemas(), creates/repairs tables in all databases according to registry (self-healing entry point, replaces scattered CREATE statements)
• GET /api/db/plm-status —— Aggregates last sync time/record count/diff for each PLM mirror database (reads status tables of respective sync services)
• POST /api/db/plm-sync {db,table,direction} —— Triggers single-item PLM pull/push (delegates to existing sccapp-sync / sync-SCSAI-meta / staff-persistence)
• POST /api/db/cloud-pull {source,target} —— Restore from a cloud source snapshot (see 3.3)
• Read-only SQL: POST /api/db/query {db,sql} (whitelist sandbox)

3.3 Cloud Download/Restore (currently zero, requires new adaptation layer)

• New server/services/cloud-snapshot.js: Pluggable "remote snapshot source" adapter (interface pull(snapshotId)→buffer, list()).
• Default adapter: HttpSnapshotSource (fetches from environment variables CLOUD_SNAPSHOT_URL/CLOUD_SNAPSHOT_TOKEN); extension points reserved for OssSnapshotSource/CosSnapshotSource/GitLfsSource.
• Security: Verify checksum after download + replace within the "backup current DB → rename → deploy new DB → PRAGMA wal_checkpoint" transaction, rollback on failure.
• Frontend "Cloud Sync" Tab: List available cloud snapshots, select target database, one-click pull and restore, display progress and verification results.

⚠️ Requires your decision: What exactly does "cloud" refer to? ① A specific HTTP backup service address (provide URL+token) ② Object storage OSS/COS (provide bucket/credentials) ③ Snapshots on Git LFS ④ Directly use PLM(114) as the "cloud". This determines the first adapter implementation for cloud-snapshot.js.

3.4 PLM Sync Engine (Unified Scheduling + Status)

• Added server/services/db-plm-sync.js: Driven by the registry's plmClass/syncDirection.
• PLM_SOURCE: Only supports "pull" (local read-only mirror), delegates pull(db,table) to existing sync-SCSAI-meta/sccapp-sync.
• PLM_SHADOW: Supports "pull + push", delegates push(db,table) to existing write-back logic like staff-persistence/SCSAI-process-sync/content-store; conflict strategy (PLM priority / latest timestamp wins) is configurable.
• Each synchronization writes to the sync_state table (database/table/direction/last successful time/record count/difference count) for /api/db/plm-status and frontend display.
• The management interface displays "which tables have never synced/sync failed/drifted," allowing manual or scheduled triggering.

3.5 Version Control and Loss Prevention (Root Cause Fix for .gitignore Issues)

• Table definitions into Git: server/db/schema-classification.json + schema-registry.js + ensureAllSchemas() under version control (these are the "single source of truth" for table definitions, not binaries).
• Revise .gitignore: Keep /.db ignored, but explicitly unignore schema scripts/JSON; continue ignoring very large binary libraries (sccapp_process_data.db/large kb/*.db) or use Git LFS.
• Switching computers: Pull code → ensureAllSchemas() automatically creates all table definitions → resolves "missing/inconsistent table definitions."

4. Implementation Phase (To be executed in order after approval)

5. Key Decisions Requiring Your Approval

1. Deduplication and ownership (§1.2): Confirm sciot_ belongs to sciot-metadata.db, inspection_ belongs to rule_engine.db, core_runtime retains only business caches—do you agree with this single mapping?
2. Cloud source definition (§3.3): What type of 'cloud' is the 'cloud' in 'download from cloud' (HTTP backup service / OSS·COS / Git LFS / PLM as cloud)? This determines the first adapter.
3. P0 start now: After confirmation, I will first implement the unified registry + ensureAllSchemas + .gitignore revision (P0), then proceed with P1.

6. Evolution: Model the database metadata itself as PLM object classes.

Trigger: User decision—'Since we're already at this point, why not also store SQLite database and table management as object classes in PLM for better unification.'

This is a natural extension of the §2 PLM classification model: Previously, we only modeled 'business objects' into PLM; now we also objectify 'the system's own metadata (database/table/column)', making PLM the single source of truth and a unified, linkable catalog for all object models (business + system metadata).

6.1 Value (Why is it better unified?)

1. Full object model visible in one place: Business objects (Part/BOM/Process/Article/Digital Worker...) and 'what the database looks like' (database/table/column) are all in PLM, enabling unified search, unified permissions, unified audit, and unified versioning.
2. Local table ↔ PLM object bidirectional traceability: Through the relationship R_DB_TABLE_ITEMTYPE, a local table can be directly linked to its corresponding PLM ItemType (e.g., ARTICLE table ↔ ARTICLE ItemType), answering 'Which PLM object does this local table correspond to?' and 'Which PLM objects still lack a local table to host them?'.
3. Cross-computer disaster recovery closed loop: Local .db files are excluded by .gitignore and not stored in the repository, risking loss of definitions. Now, a "Database Object Model Catalog" is permanently maintained in PLM; if the local library is lost, it can be rebuilt based on PLM + ensureAllSchemas().
4. Complies with the user's ironclad rule: "Basic data models reside in PLM, driven by object models and relationships."

6.2 Object Model (Reuses the existing sjzcpt_ data collection platform model from PLM, no new creation)

Verified: PLM(114) already contains a complete set of data collection platform object models (sjzcpt_ prefix), with a hierarchy that corresponds one-to-one to our "Database/Table/Column":

• sjzcpt_sjy (Data Source) ← Our Database: fdatabasename (database filename)/fdatabasetype (SQLite)/fserviceaddr (path)/floginaccount/fconfigpath/fgatherpath/fissynchronized/fsyncname…
• sjzcpt_ysj (metadata/source data) ← Our table: fname(table name)/fcode/fsjyobjid(→belongs to data source sjzcpt_sjy)/ftype/fdesc/forderid/fissynchronized
• sjzcpt_ysj_ysjsx (metadata attribute = data list item) ← Our column: name/ftype(type)/fiskey(primary key)/fisfkey(foreign key)/fismust(required)/max_length/sort_order/sjyid(→belongs to table sjzcpt_ysj)
• sjzcpt_sjytype (data source type dictionary): fcode/fname/fparent (need to confirm there is a "SQLite" type entry)
• sjzcpt_spq (collection/data packet): fgatherid/ftoolid

Relationships are built-in: RelationshipType sjzcpt_ysj_ysjsx connects metadata↔attribute; sjzcpt_ysj.fsjyobjid and sjzcpt_ysj_ysjsx.sjyid are field-level references. This model already has 3 sample instances and is a usable, live model.

Direction correction: The previous design in this section (old §6.2) to create new DB_INSTANCE/DB_TABLE ItemTypes was incorrect—PLM already has an equivalent and more complete sjzcpt_ model, which should be reused directly, avoiding reinventing the wheel. This also addresses the concern about "3300 column-style instance bloat": sjzcpt_ysj_ysjsx is inherently a field-level model, and writing column by column is within its design.

6.3 Field Mapping (Local registry → PLM sjzcpt_)

6.4 Existing code already has a foundation for reuse (no need to start from scratch)

• server/services/db-metadata-collector.js: Fully implements "collection → mapping (table→ItemType/column→Property/foreign key→RelationshipType) → objectification → local storage", and _getSCSAIDataSourceConfig (:993) can already read back data source configuration from PLM sjzcpt_sjy. Its local storage table structures db_datasources/db_metadata_tables/db_metadata_columns can be directly mapped.
• server/routes/platform-asset.js: Already has /api/db-datasource, /api/db-metadata, /api/db-mapping, /api/unified-assets routes.
• Gap: The existing code only "stores locally + reads back from PLM", lacking a reverse adapter to "write collection results into the PLM sjzcpt_ model"—this is what needs to be added.

6.5 Synchronization Direction

• Collection Source = Local schema-registry (generated live data + classification curation) = Single Source of Truth.
• Write Target = PLM sjzcpt_ model: Add a new script, scripts/push-schema-to-plm.cjs, which uses SCSAIClient.sendAML/applyItem to idempotently upsert database→sjy, table→ysj, and column→ysjsx (locate by fcode/name, update without delete to circumvent endpoint delete restrictions; orphaned items are cleaned via the Aras admin console).
• PLM becomes the "Database Object Model Catalog", residing in the same unified source of truth as business objects (ItemType).

6.6 Relationship with Existing System (Prevent Dual Sources of Truth)

• Local registry (generated + classification) = source of truth for collection/curation; sjzcpt_ model = PLM-side "objectified registry + linkable catalog".
• Process: gen-schema-registry generates live data → classification adds layers → push adapter writes to PLM sjzcpt_ → PLM unified catalog. Can be attached to startup/CI for automatic runs.

6.7 Implementation Steps (P1 Priority)

1. Verify if an item of type "SQLite" already exists in sjzcpt_sjytype; if not, createItem to add one.
2. Add new script scripts/push-schema-to-plm.cjs: getSharedClient() → read generated+classification data → create/update sjzcpt_sjy per database, create/update sjzcpt_ysj per table (linked via fsjyobjid), create/update sjzcpt_ysj_ysjsx per column (linked via sjyid). Use applyItem's where/keyed for idempotent positioning.
3. (Optional) Add the fplmitemtype attribute (smartRepairItemType) to sjzcpt_ysj, write it into classification.tableItemTypeMap, to implement the "local table ↔ PLM business object" mapping.
4. Validation: In PLM, verify sjzcpt_sjy ≥ 31, sjzcpt_ysj ≥ 332, and sjzcpt_ysj_ysjsx ≈ total column count; sample check that the reference chains ysj→sjy and ysjsx→ysj are complete for one database.

6.8 Risks and Constraints (Confirmed by actual testing, important)

• PLM sjzcpt_ family physical tables lack SOURCE_ID/RELATED_ID columns (systemic defect): Through item-by-item testing, the underlying physical tables for the three types—sjzcpt_sjy, sjzcpt_ysj, and sjzcpt_ysj_ysjsx—all lack SOURCE_ID and RELATED_ID columns, resulting in:
• Instance creation succeeds; however, all subsequent edit/delete operations fail—edit reports 'invalid column name "SOURCE_ID"', delete reports 'invalid column name "SOURCE_ID". invalid column name "RELATED_ID"', and relationship edit/delete reports MissingCriteriaException.
• Inference: Existing instances cannot be modified or deleted via API (orphaned instances can only be corrected through the Aras admin console or underlying database). Push scripts uniformly follow the idempotent strategy of "check for duplicates → skip if exists, create if not exists," never relying on update/delete.
• Columns are relationship types and cannot be independently created via createItem: sjzcpt_ysj_ysjsx is a RelationshipType (source=sjzcpt_ysj). Using SCSAIClient.createItem('sjzcpt_ysj_ysjsx', props) will throw The source item has to be specified in order to add a relationship.; the correct approach is to explicitly include =ysj.id within the relationship item via sendAML (verified OK). Use filtering for duplicate checking (custom fields can serve as query conditions).
• The sjzcpt_ field uses the prefix f (Chinese system attribute). When writing, strictly align with the field names (verified, see §6.2).

6.9 Finalized (Implementation Details)

• A. Write Trigger: Manual script scripts/push-schema-to-plm.cjs for a one-time push (implemented). Usage: SCOPE=db,table / SCOPE=column / SCOPE=all, with optional LIMIT=n for small-batch validation first.
• B. Column Granularity: Write column by column to sjzcpt_ysj_ysjsx (implemented, see §6.10).
• C. Table ↔ PLM Object Annotation (Partially Blocked): The fplmitemtype attribute has been added to the sjzcpt_ysj class. However, due to the edit limitation in §6.8, the fplmitemtype values for the 332 existing table instances cannot be backfilled (old instances cannot be modified/deleted). Actual handling: ① The plmClass+category+action info is already written into each table's fdesc text, so the "table ↔ PLM object" mapping remains text-searchable within PLM; ② classification.json is the single source of truth for this mapping; ③ Only 12 tables have non-empty plmClass (8 PLM_SHADOW tables like ARTICLE/STAFF_TASK/EXECUTION_LOG… + 4 PLM_SOURCE/LOCAL_ONLY). The remaining 320 tables are purely local schema metadata (sjzcpt_ysj is their representation in PLM), requiring no individual column mapping. → Conclusion: The structured fplmitemtype field is not essential for the other tables; mapping usability is ensured by fdesc + classification. To add structured annotation for those 12 tables, correction via the Aras admin console (physical table modification) or cleanup/re-push is required (but delete is also restricted, so adding a column is the only solution).

6.10 Actual Implementation Status (As of Now)

• ✅ sjzcpt_sjytype: Added the "SQLite" type entry (fcode=SQLITE/fname=SQLite).
• ✅ sjzcpt_sjy (database): All 31 local databases have been integrated into PLM (corresponding one-to-one with the 31 registry databases; PLM total 39 entries include 8 existing entries from other data sources, no conflict).
• ✅ sjzcpt_ysj (table): All 332 local tables have been integrated into PLM (verification: fcode=database::table for all 332/332 local tables can be found in PLM; PLM total 779 entries include 447 existing entries from other systems, isolated by fcode without overlap).
• ✅ fplmitemtype attribute: Successfully added to the sjzcpt_ysj class (exists at class level, pending future instance creation or backfilling historical values via admin console).
• ✅ sjzcpt_ysj_ysjsx (column): Full push of 3922 columns completed (relationship type + source_id created; SCOPE=column actual run ok=3916/fail=0, 6 columns from dry-run tables were skipped, total 3922; PLM actual test shows 3923 columns for our tables, with 1 extra debug junk column _reltest_a). PLM currently has 6329 columns, all from existing other system data; our 3922 columns are isolated by the source_id of their parent ysj table. End-to-end verification: 331/332 tables have exact column count match (only data/bossagents.db::changes has 7 vs 6 columns due to junk column); spot-check confirms column source_id == parent table ysjId, reference chain is complete.
• ⚠️ Known junk: A debugging session left 1 test column _reltest_a under data/bossagents.db::changes (cannot be cleared via API due to delete restrictions; the name is distinct, does not conflict with real columns, and is harmless; requires Aras admin console for removal if needed).

6.11 Management Console (Completed based on the aforementioned modeling results, 2026-08-30)

• Objective: Transform the fact that "libraries/tables/columns have been modeled into PLM" into a web-based management feature that is browsable, searchable, and allows real-time data viewing. This will be implemented under the "Metadata Registry" tab within the "Integration & Logs / Database Management" module.
• Backend server/routes/platform-asset.js added /api/platform/schema-registry*:
• GET /schema-registry → Three-level tree (library → table → column statistics) + statistical chips; inject role/plmClass/syncDirection/deprecated/plmSynced according to schema-classification.json (note: classification key uses server/data/..., registry db.path uses data/..., normalized via normKey).
• GET /schema-registry/columns?db=&table= → Single column-level metadata.
• GET /schema-registry/data?db=&table=&limit= → Real-time data preview (sqlite-compat read-only open of real database, table/db validated against registry to prevent injection/unauthorized reads).
• POST /schema-registry/sync (background child process re-pushes to PLM) + GET /schema-registry/sync (poll progress).
• Frontend src/views/SchemaRegistry.vue (self-contained): Library/Table/Column three-level drill-down, PLM classification tags, real-time data preview, filter by name/role/PLM classification, one-click PLM re-push. Mounted as the third tab in DatabaseManager.vue.
• Critical fix: In scripts/push-schema-to-plm.cjs, changed upsert to skip if exists (original updateItem would always fail for sjzcpt_ instances), ensuring re-push is idempotent and error-free.
• Verification: node --check passes; handler directly connects to real registry and runs tree(31/332/3922)/columns/data successfully; vite build succeeds. Running server.js requires restart to load new routes (frontend dist already rebuilt).