Database & Storage Consolidation Analysis

🚨 Current Problems

1. Multiple Disconnected MongoDB Databases

We have 4 separate MongoDB databases with confusing overlap:

2. Critical Bug: Disconnected Decision Collections

The Problem:

MCP → writes to → ai-agents.decisions
Learning Module → reads from → ai-mcp-dev.decisions
❌ THEY NEVER TALK TO EACH OTHER!

Code Evidence:

• InfrastructureModule: Uses AI_CONNECTION_NAMES.AGENTS (ai-agents database)
• LearningModule: Uses AI_CONNECTION_NAMES.MCP_DEV (ai-mcp-dev database)
• Result: Learning pyramid can't see MCP decisions!

3. Local File Structure Doesn't Match docs/data

Current (Wrong):

.cursor/ai-memory/
├── current-session.json     # All session 9 decisions in one file
└── sessions/                # Empty!

docs/data Pattern (Correct):

docs/data/sessions-history/
├── session-1.json
├── session-2.json
├── session-9.json           # One file per session

Better Pattern (What We Need):

.cursor/ai-memory/
├── sessions/
│   ├── session-9/
│   │   ├── 9.1.json         # Decisions from subsession 9.1
│   │   ├── 9.2.json         # Decisions from subsession 9.2
│   │   ├── 9.3.json         # Decisions from subsession 9.3
│   │   └── metadata.json    # Session metadata
│   └── session-8/
│       └── ...
└── current.json             # Pointer to current subsession

4. Confusing Schema Structure

Current Decision Schema:

{
  "id": "dev_123",
  "timestamp": "...",
  "type": "architecture",
  "question": "...",
  "session": {
    "sessionNumber": 9,
    "sessionID": "9.1"    // ← String, inconsistent
  }
}

Problems:

• sessionID is sometimes string ("9.1"), sometimes number
• Each decision duplicates session info
• No hierarchical structure

Better Schema:

// File: sessions/session-9/metadata.json
{
  "session": 9,
  "subsessions": {
    "9.1": { "startedAt": "...", "decisions": 5, "file": "9.1.json" },
    "9.2": { "startedAt": "...", "decisions": 3, "file": "9.2.json" },
    "9.3": { "startedAt": "...", "decisions": 2, "file": "9.3.json" }
  }
}

// File: sessions/session-9/9.1.json
{
  "subsession": 9.1,
  "startedAt": "2025-12-16T19:01:45.769Z",
  "decisions": [
    {
      "id": "dev_123",
      "timestamp": "...",
      "type": "architecture",
      "question": "..."
      // No session field - it's implied by file location!
    }
  ]
}

✅ Proposed Consolidated Structure

MongoDB: ONE Database (ai-agents)

ai-agents (PRIMARY DATABASE)
├── Core Collections
│   ├── decisions              ← ALL decisions (MCP + Infrastructure)
│   ├── sessions               ← Session metadata
│   ├── mcp_config             ← MCP configuration
│   
├── Learning Collections
│   ├── core_rules             ← Move from ai-mcp-dev
│   ├── learned_patterns       ← Move from ai-mcp-dev
│   ├── trigger_states         ← Move from ai-mcp-dev
│   
├── Agent Orchestration
│   ├── agent_tasks
│   ├── agent_decisions        ← Task orchestration (different from decisions)
│   ├── agent_patterns
│   ├── agent_knowledge
│   ├── agent_metrics
│   ├── agent_calibrations
│   ├── agent_outcome_correlations
│   
├── Vector & Embeddings
│   ├── agent_embeddings       ← Move from ai-memory
│   
├── Tenant System
│   ├── tenant_user_profiles   ← Move from ai-tenants
│   ├── tenant_user_interactions ← Move from ai-tenants
│   
├── Platform Intelligence
│   ├── platform_agents
│   ├── platform_patterns
│   
├── Archive System
│   ├── archived_decisions     ← Keep
│   ├── archived_embeddings    ← Keep
│   ├── archive_jobs           ← Keep
│   ├── archive_policies       ← Keep
│   
└── Jobs
    └── training_jobs

Total: 25 collections in ONE database ✅

Local File Structure (Matches docs/data)

.cursor/ai-memory/
├── sessions/
│   ├── session-9/
│   │   ├── metadata.json      # Subsession tracking
│   │   ├── 9.1.json           # Decisions from subsession 9.1
│   │   ├── 9.2.json           # Decisions from subsession 9.2
│   │   └── 9.3.json           # Current subsession
│   │
│   ├── session-8/
│   │   ├── metadata.json
│   │   └── 8.1.json
│   │
│   └── session-7/
│       └── ... (archived, can be deleted locally)
│
├── current.json               # { "session": 9, "subsession": 9.3 }
├── conventions.json           # Learned conventions
└── README.md

Key Improvements:

1. Hierarchical: session/subsession/decision
2. Matches docs/data/sessions-history/ pattern
3. Easy to archive old sessions (delete folder)
4. Fast reads (only load current subsession)

🔧 Migration Plan

Phase 1: Database Consolidation (Backend)

⚠️ HIGH RISK - Must be done carefully

1. Create migration script to move data:

   ai-mcp-dev.decisions → ai-agents.decisions
   ai-mcp-dev.core_rules → ai-agents.core_rules
   ai-mcp-dev.learned_patterns → ai-agents.learned_patterns
   ai-mcp-dev.trigger_states → ai-agents.trigger_states
   ai-memory.agent_embeddings → ai-agents.agent_embeddings
   ai-tenants.* → ai-agents.*
   

2. Update all module imports:

   • LearningModule: Change from MCP_DEV to AGENTS
   • AgentVectorMemoryService: Change from MEMORY to AGENTS
   • TenantAIService: Change from TENANTS to AGENTS

3. Update libs-api/backend-core/src/lib/config/database-names.config.ts:

   export const AI_CONNECTION_NAMES = {
     AGENTS: 'ai-agents',  // Only this one!
     // Remove: MCP_DEV, MEMORY, TENANTS, CONTEXT
   };
   

4. Remove unused connections from app.module.ts

5. Test thoroughly - Run all backend tests

Phase 2: Local File Restructuring (MCP)

1. Update MCP file-storage.ts:

   • Create subsession-based structure
   • Save to sessions/session-X/{X.Y}.json instead of current-session.json
   • Update metadata.json with subsession tracking

2. Migrate existing files:

   • Split current-session.json by subsession (9.1, 9.2, 9.3)
   • Create metadata.json files

3. Update MCP tools to use new structure

Phase 3: Verification

1. Test MCP logging → MongoDB sync
2. Test Learning module reads correct decisions
3. Test dashboard displays correct data
4. Verify local files match MongoDB

📊 Database Size Comparison

Before (4 databases):

ai-agents:   20.48 kB (20 collections)
ai-mcp-dev:  40.96 kB (4 collections)  ← LARGEST!
ai-memory:    4.10 kB (1 collection)
ai-tenants:   8.19 kB (2 collections)
─────────────────────────────────────
TOTAL:       73.73 kB (27 collections)

After (1 database):

ai-agents:   73.73 kB (25 collections)  ← All in one place
─────────────────────────────────────
TOTAL:       73.73 kB (25 collections)

Same data, WAY better organization!

⚠️ Risks & Mitigation

Critical: Backup all databases before migration!

🎯 Benefits

1. Single source of truth - No more confusion about which database to use
2. Fixes Learning Module bug - Can now see MCP decisions
3. Easier maintenance - One database to monitor/backup
4. Better performance - No cross-database queries
5. Clearer architecture - Matches docs/data pattern
6. Easier subsession tracking - File per subsession

📝 Next Steps

1. Get user approval for this consolidation plan
2. Backup all MongoDB databases (critical!)
3. Create migration script with validation
4. Run migration in test environment first
5. Update backend modules one at a time
6. Update MCP file storage to new structure
7. Test everything thoroughly
8. Archive old databases (don't delete yet)

Status: Awaiting approval to proceed with Phase 1 (Database Consolidation)

Estimated Time:

• Phase 1 (Backend): 3-4 hours
• Phase 2 (MCP): 1-2 hours
• Phase 3 (Testing): 1-2 hours
• Total: 5-8 hours