Proper Database Segregation Architecture

Correct database separation for AI workloads - Final v2

Proper Database Segregation Architecture - Final v2

🔄 Latest Updates (v2)

Moved sessions and mcp_config to ai-mcp-dev - They belong with MCP-related data
New space AI database pattern - space-agents_{spaceId} (separate from main space DB)
Separation of concerns - backend-api → space data, backend-ai-api → AI data

✅ FINAL Architecture

MongoDB Databases (Properly Segregated)

┌─────────────────────────────────────────────────────────────────┐
│                    AI BACKEND DATABASE LAYOUT                    │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  ai-agents                 (Agent Orchestration ONLY)           │
│  ├── agent_tasks           Task lifecycle                       │
│  ├── agent_decisions       Task-level decisions                 │
│  ├── agent_patterns        Agent patterns                       │
│  ├── agent_metrics         Performance metrics                  │
│  ├── agent_calibrations    ML calibration                       │
│  ├── agent_knowledge       Knowledge base                       │
│  ├── agent_outcome_correlations  Learning correlations          │
│  ├── platform_agents       Platform agent metadata              │
│  ├── platform_patterns     Platform patterns                    │
│  └── training_jobs         Training job tracking                │
│                                                                 │
│  ai-mcp-dev                (ALL MCP & Learning System) 🔄       │
│  ├── sessions        🆕    MCP cursor sessions (MOVED!)         │
│  ├── mcp_configs     🆕    MCP configuration (MOVED!)           │
│  ├── decisions             MCP/Cursor decisions                 │
│  ├── core_rules            Learning pyramid - top tier          │
│  ├── learned_patterns      Learning pyramid - middle tier       │
│  └── trigger_states        Adaptive trigger states              │
│                                                                 │
│  ai-memory                 (Vector Embeddings - ISOLATED)       │
│  └── agent_embeddings      Vector search embeddings             │
│      ✨ Separate for performance - vector indexes are huge      │
│                                                                 │
│  ai-archive                (Cold Storage - SEPARATE)            │
│  ├── archived_decisions    Old decisions (post-learning)        │
│  ├── archived_embeddings   Old embeddings                       │
│  ├── archive_jobs          Archive task tracking                │
│  └── archive_policies      Archive configuration                │
│                                                                 │
│  ai-context                (Generation History)                 │
│  ├── ai_generations        AI generation logs                   │
│  ├── ai_mappings           Mappings data                        │
│  ├── ai_theme_context      Theme context                        │
│  └── aiusages              Usage tracking                       │
│                                                                 │
│  space_{spaceId}           (Per-Space CORE Data - backend-api)  │
│  ├── property_templates    Space templates                      │
│  ├── space_flows           Space workflows                      │
│  ├── space_metadata        Space config                         │
│  ├── space_modules         Space modules                        │
│  ├── space_pages           Space pages                          │
│  ├── space_widgets         Space widgets                        │
│  ├── theme_system          Space theme                          │
│  └── universes             Space universes                      │
│                                                                 │
│  space-agents_{spaceId} 🆕 (Per-Space AI Data - backend-ai-api) │
│  ├── decisions             Space-specific AI decisions          │
│  ├── user_profiles         User AI profiles for this space      │
│  ├── user_interactions     User AI interactions                 │
│  ├── patterns              Space-learned patterns               │
│  ├── preferences           User preferences                     │
│  └── embeddings            Space-specific embeddings            │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

Connection Configuration

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

export const AI_DATABASE_NAMES = {
  AGENTS: 'ai-agents',      // Agent orchestration ONLY
  CONTEXT: 'ai-context',    // Generation history
  MEMORY: 'ai-memory',      // Vector embeddings (SEPARATE for performance)
  MCP_DEV: 'ai-mcp-dev',    // MCP decisions, sessions, learning, config
  ARCHIVE: 'ai-archive',    // Cold storage
};

🆕 What Changed in v2

1. Moved to `ai-mcp-dev`

These collections moved from ai-agents to ai-mcp-dev:

sessions - MCP cursor sessions
mcp_configs - MCP configuration

Rationale: They're MCP-related data, not agent orchestration.

2. New Space AI Database Pattern

Separation of concerns between backend-api and backend-ai-api:

// apis/backend-ai-api/src/app/agents/utils/space-database.utils.ts

// Get AI database for a space (controlled by backend-ai-api)
getSpaceAIDatabaseName(spaceId);  // "space-agents_{spaceId}"

// Get core database for a space (controlled by backend-api)
getSpaceDatabaseName(spaceId);    // "space_{spaceId}"

// Get AI collection names
getSpaceAICollections(spaceId);   // { decisions, user_profiles, ... }

Benefits:

AI data doesn't pollute main space database
Different services control their own data
Independent scaling - AI can grow without affecting core
Clean deletion - drop AI database when space is deleted

✅ What You Can Safely Delete

Database/Collection	Safe to Delete?	Reason
`ai-agents/sessions`	✅ YES	Moved to ai-mcp-dev
`ai-agents/mcp_configs`	✅ YES	Moved to ai-mcp-dev
`ai-agents/tenant_user_profiles`	✅ YES	Replaced by space-agents_{spaceId}
`ai-agents/tenant_user_interactions`	✅ YES	Replaced by space-agents_{spaceId}

Migration endpoint: POST /ai-api/infrastructure/migrate/mcp-data-to-mcp-dev

📊 Performance Benefits of Segregation

Workload	Separate DB Benefit
Vector Embeddings	Vector indexes don't bloat other collections, faster similarity search
Archives	Cold storage with different backup frequency, doesn't slow hot paths
Space AI Data	Complete isolation, can drop entire space AI DB for GDPR compliance
MCP Learning	All MCP data in one place, easier to query and maintain

🔧 Module Mapping (v2)

Module	Database	Collections
`InfrastructureModule`	ai-agents + ai-mcp-dev	decisions, agent_knowledge (agents) / sessions, mcp_configs (mcp-dev)
`LearningModule`	ai-agents + ai-mcp-dev	decisions (agents) / core_rules, learned_patterns, trigger_states (mcp-dev)
`AgentsModule`	ai-agents + ai-memory	agent_* (agents) / embeddings (memory)
`ArchiveModule`	ai-agents + ai-archive	decisions (agents) / archived_* (archive)
`PlatformAIModule`	ai-agents	platform_*

✅ Changes Made (v2)

Moved sessions to ai-mcp-dev - Updated infrastructure.module.ts, session.service.ts
Moved mcp_config to ai-mcp-dev - Updated mcp-config.service.ts
Created migration endpoint - /ai-api/infrastructure/migrate/mcp-data-to-mcp-dev
Updated space-database.utils.ts - New space-agents_{spaceId} pattern
Added deprecation warnings - For old utility functions

🎯 Summary

Key Principle: Service Ownership

Service	Controls	Database(s)
`backend-api`	Core space data	`space_{spaceId}`
`backend-ai-api`	AI data	`ai-agents`, `ai-mcp-dev`, `ai-memory`, `ai-archive`, `space-agents_{spaceId}`

Each database serves a distinct purpose:

ai-agents: Agent orchestration (tasks, metrics, patterns)
ai-mcp-dev: MCP/Cursor data (sessions, config, learning)
ai-memory: Vector embeddings (isolated for performance)
ai-archive: Cold storage (different backup policy)
space-agents_{spaceId}: Per-space AI data (isolated per space)