Database Path Normalization and Bypass Final Closure (2026-07-15, Round 3)

Step 3 Execution: Path Normalization Completion

Findings

Performed a full scan of all .db path constructions and driver instantiations in the server runtime directories (core/routes/services/digital-staff/utils/middleware/shared/boss-scheduler).

All real physical database names (appearing as parameters in createDatabase) are already covered in KNOWN_DBS:

rule_engine.db / sciot_import.db / core_runtime.db / enterprise_store.db / aml_store.db / aml.db / sccapp_process_data.db / plm_doc_brain.db / sciot-metadata.db / boss_analytics.db / boss_tenants.db / user_libraries(directory) / ai_tasks.db(added this round)

False Positive Explanation in Verification Script: The 19 'MISS' entries (e.g., config.db, store.db, svc.db, this.db, d.db) caught by regex in _verify_known_dbs.js are actually code variable names (property access like this.db, config.db), not actual filename parameters for createDatabase('xxx.db'), and thus are not within the scope of path normalization.

Modification

• server/sqlite-compat.js L36: Added ai_tasks.db to KNOWN_DBS → Normalizes both './server/data/ai_tasks.db' and 'server/data/ai_tasks.db' to server/data/ai_tasks.db, eliminating the risk of phantom paths during future database creation.

Final ruling on two driver-level bypasses (no forced changes)

Full scan revealed 3 types of driver instantiation bypassing the compat singleton:

1. sql.js in-memory copy: ai-inspector.js:49 (getSciotDb uses sql.js cache, TTL read-only snapshot), routes/scripts/*.js (build/migration scripts, not runtime)
2. sqlite3 native driver: services/ai-task-manager.js:28 (3rd driver, writes to ai_tasks.db)
3. mysql2: unified-asset-service.js:46 (external MySQL, outside sqlite normalization scope)

Ruling:

• The sql.js cache in ai-inspector.js = read-only snapshot semantics (TTL + read-only queries), does not conflict with the better-sqlite3 singleton data, only adds an extra in-memory copy (performance trade-off). Downstream is tightly coupled to the sql.js-specific API sdb.exec(...). Rewriting would require modifying the core inspection UI path, high risk with no data overwrite risk → Mark as tech-debt, do not block.
• The sqlite3 driver in ai-task-manager.js has API incompatibility (callback-style db.run(sql, cb) vs. compat synchronous style). The current ai_tasks.db disk file does not exist → it actually falls back to useMemory Map, with no risk of disk data loss. Rewriting it requires reworking init + 11 callback calls + caller await semantics, estimated 372 lines of work → schedule separately, do not force into this round (to avoid regression).
• mysql2 external library: not a sqlite normalization object, normal.

Final summary of the three-step implementation

| Step | Scope | Result |

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

| Step 1: Stop the bleeding | Log critical data-layer catches | ✅ Added error logging (no re-throw) at rule-engine lines 466/3040/3045 |

| Step 2: Block bypasses | Two real singleton bypasses | ✅ Modified self-evolving-pipeline(L32) + rule-engine metadata recovery(L313) to use compat singleton |

| Step 3: Path normalization | Complete KNOWN_DBS | ✅ Added ai_tasks.db, covering all real physical database names |

Not done (verified as should not be done):

• Do not modify 310 benign empty catches (fs fallback/JSON parse fallback/table not built expected) – changing them would crash normal operation.
• Do not rewrite ai-inspector sql.js read-only cache (API incompatible + no data risk)
• Do not forcibly modify ai-task-manager sqlite3 (API incompatible + current memory downgrade poses no risk) — scheduled as an independent task.
• Do not create a new db-registry.js (canonicalPath is already the single source of truth).
• Do not change the driver (better-sqlite3 is already in place).

Verification

• Service clean restart (clean up orphaned 9876): /api/health 200, /api/rule-engine/stats = {total:2413, active:2386}.
• Engine initialization logs: No dead/unsupported warnings, no FAIL.
• Singleton reuse (verified in the previous round): sccapp_process_data.db cache key=1, cross-wrapper read/write to the same underlying data successful.
• KNOWN_DBS coverage: All real physical database names are closed-loop.

Remaining tech-debt (independently scheduled, not urgent).

1. ai-task-manager.js: Convert sqlite3 callback style → compat synchronous style (current memory downgrade, no data risk).
2. ai-inspector.js: sql.js read-only cache → evaluate switching to compat singleton read (eliminate memory copy, but requires modifying the core path of the inspection UI).
3. Audit 310 benign empty catch blocks (for tidiness, not a data-layer root cause).
4. 14 .db files not physically merged (logical unification is complete; physical merging is an independent architectural debt).