SuperOffice AI Support MCP Platform

An enterprise-grade, secure, modular Model Context Protocol (MCP) platform for AI-assisted SuperOffice CRM support triage, diagnostic telemetry, and cross-system incident investigation.

The platform bridges external LLM reasoning engines (such as Claude Desktop, Cursor, and autonomous support agents) with Onsite SuperOffice CRM REST APIs, Microsoft SQL Server database diagnostics, PostgreSQL/pgvector knowledge stores, and infrastructure boundaries under strict security, data minimization, and audit controls.

Architecture Overview

The platform strictly implements a Six-Layer Architecture. Observability is a cross-cutting concern spanning all operational layers:

┌─────────────────────────────────────────────────────────────────────────┐
│                           1. MCP Layer                                  │
│   (SuperOffice MCP, Diagnostics MCP, Knowledge MCP, Investigation MCP)  │
└────────────────────────────────────┬────────────────────────────────────┘
                                     │
┌────────────────────────────────────v────────────────────────────────────┐
│                         2. Security Layer                               │
│  (MCP Gateway, JWT Auth, YAML RBAC, Privilege Flags, PII/Sanitization) │
└────────────────────────────────────┬────────────────────────────────────┘
                                     │
┌────────────────────────────────────v────────────────────────────────────┐
│                      3. Investigation Layer                             │
│   (Investigation State Machine, Hypothesis Engine, Incident Correlator) │
└────────────────────────────────────┬────────────────────────────────────┘
                                     │
┌────────────────────────────────────v────────────────────────────────────┐
│                  4. Application Service Layer                           │
│  (Ticket Investigation Service, Knowledge Service, Diagnostics Service) │
└────────────────────────────────────┬────────────────────────────────────┘
                                     │
┌────────────────────────────────────v────────────────────────────────────┐
│                       5. Integration Layer                              │
│   (SuperOffice REST Client, MSSQL Async Client, Knowledge DB Adapter)  │
└────────────────────────────────────┬────────────────────────────────────┘
                                     │
┌────────────────────────────────────v────────────────────────────────────┐
│                     6. Infrastructure Layer                             │
│       (Host Boundaries, Process Models, OS Diagnostics Boundaries)      │
└─────────────────────────────────────────────────────────────────────────┘
                                     ▲
                                     │
                     ════════════════╪════════════════
                        CROSS-CUTTING OBSERVABILITY
                     (Structured Logs, Audit Sinks, Traces)
                     ═════════════════════════════════

Backend Ownership & Isolation Boundaries

Each backend is exclusively owned by a single service boundary. External AI models and LLMs have zero direct access to databases, filesystems, attachments, infrastructure shells, or enterprise credentials:

• SuperOffice Business Data $\rightarrow$ SuperOffice MCP (so-mcp) $\rightarrow$ SuperOffice REST API.

• MSSQL Database Diagnostics & Logs $\rightarrow$ Diagnostics MCP (diag-mcp) $\rightarrow$ Microsoft SQL Server & Log parsers.

• Technical Knowledge & Runbooks $\rightarrow$ Knowledge MCP (kb-mcp) $\rightarrow$ PostgreSQL + pgvector.

• Infrastructure Boundaries $\rightarrow$ Infrastructure MCP (infra-mcp, deferred under Decision D07).

• Perimeter Security $\rightarrow$ MCP Gateway (platform-gateway). The Gateway has no direct database access.

Related MCP server: superoffice-mcp-server

Canonical Public Tool Inventory (29 Tools)

The Gateway exposes exactly 29 registered public tools over Streamable HTTP (ADR 007). There are zero aliases and zero public ingestion tools.

Registered vs. Operational Status

• search_logs: IMPLEMENTED / CONFIGURATION-CONDITIONAL (operational when local log directories are configured).

• get_ticket_diagnostic_record: ACTIVE / OPERATIONAL (queries ticket_log and ticket_log_action with sanitized summaries).

• get_ticket_audit_trail: ACTIVE / OPERATIONAL (queries ticket_log, ticket_log_action, ticket_log_change, and ejuser).

• get_associate_details, get_ticket_metadata_lists, list_system_events_and_triggers: ACTIVE / OPERATIONAL (queries ASSOCIATE, EJUSER, EJ_CATEGORY, SCHEDULE, SCHEDULED_TASK).

• list_extra_tables, get_extra_table_schema, query_extra_table: ACTIVE / OPERATIONAL (user-defined y_* discovery).

• Knowledge tools: CONFIGURED / LOCAL OPERATIONAL (backed by local PostgreSQL + pgvector and FastEmbed).

• investigate_incident: Operational with 4 frozen subordinate operations (get_ticket, get_database_health, find_slow_queries, find_deadlocks). Subordinate application_logs returns BLOCKED and knowledge_base returns NOT_CONFIGURED.

• Infrastructure MCP: 0 public tools (retained as boundary; implementation deferred under Decision D07).

Gateway Prompts & AI Guardrails (3 Prompts)

The MCP Gateway exposes structured, versioned prompt templates (prompts/list, prompts/get) with embedded operational rules and anti-hallucination guardrails:

Security Model & Policy Invariants

• Deny-by-Default RBAC: Declarative tool-level policy defined in tool_permissions.yaml and enforced by YamlPolicyEngine. Unknown roles or unmapped tools are unconditionally rejected (DENY).

• Role Hierarchy: L1 (Basic Support Triage) $\subset$ L2 (Diagnostics) $\subset$ L3 (Cross-System Investigation).

• Attachment Protection (Decision D05): Deny-by-default. The public tool list_attachments returns strictly metadata. Raw attachment downloads and automatic ingestion to AI models are prohibited.

• External AI Boundary (Decision D08): Live production transmission of confidential/sensitive SuperOffice CRM data to external AI endpoints is NOT APPROVED. Knowledge ingestion of live customer CRM exports is DENIED.

• Database Safety (Decisions D01, D02, D09):

  • Statement timeout hard-capped at 5.0 seconds (D01).

  • Query result rows hard-capped at 50 rows (D02).

  • Mandatory SNAPSHOT isolation (D09). Zero WITH (NOLOCK) hints and zero dynamic/arbitrary SQL execution.

• Data Minimization & PII Scrubbing: All responses pass through recursive PII and secret redaction filters before delivery to external AI clients.

Knowledge Backend & Ingestion Engine

• Database: PostgreSQL with pgvector (0.8.6) in schema knowledge (documents, chunks, runbooks, known_issues).

• Embeddings: In-process FastEmbedEmbeddingProvider utilizing BAAI/bge-small-en-v1.5 (384 dimensions).

• Immutable Artifacts: Pre-redacted canonical Markdown artifacts stored at approved/<type>/<id>/<hash>.md on local filesystem outside Git.

• Concurrency Control: Transaction-scoped PostgreSQL advisory locking (pg_advisory_xact_lock) serializes ingest per document while computing embeddings outside the lock.

• Internal Operator CLI:

  # Execute dry-run admission and sanitization check
  python -m kb_mcp.ingestion.cli dry-run path/to/document.md
  
  # Ingest document into local knowledge base
  python -m kb_mcp.ingestion.cli ingest path/to/document.md

  Supported local formats: .md, .markdown, .txt, Runbook .json, Known-Issue .json. (PDF, DOCX, HTML, OCR, and YAML are deferred).

SuperOffice Codebase Mirror & Synchronization Engine

The platform includes an automated synchronization engine (so_mcp.sync) that mirrors custom scripts, screen definitions, and database schemas from an Onsite SuperOffice instance to a local filesystem directory (e.g., F:\CodeBase_SuperOffice) for offline static analysis, indexing, and AI assistance:

• Dual Extraction Modes:

  • http: Extracts scripts, screens, and schemas via authenticated SuperOffice CRMScript handler (scripts/customer.fcgi).

  • mssql: Directly queries database metadata (dbo.hierarchy, dbo.ejscript, dbo.screen_definition, custom y_ tables) with safety controls and zero cross-server dependencies.

• Security & Secret Scanner: Pre-scans every script body with regex pattern matchers for API keys, passwords, bearer tokens, connection strings, and base64 credentials before persisting to disk.

• Deterministic Mirroring & Manifest: Generates clean folder trees matching SuperOffice hierarchy paths, file sanitization for Windows/POSIX safety, and an immutable manifest.json.

• Operator Commands:

  # Sync entire codebase using PowerShell helper
  .\scripts\sync-so-codebase.ps1 -OutputDir "F:\CodeBase_SuperOffice" -Mode http
  
  # Dry-run evaluation (no disk writes)
  .\scripts\sync-so-codebase.ps1 -DryRun -Verbose
  
  # Direct Python CLI
  uv run python -m so_mcp.sync.cli -o "F:\CodeBase_SuperOffice" -m http -t "ejscript,screens,schema"

Client Integrations & Desktop Run Modes

• Streamable HTTP (Multi-Client Gateway): Run the platform services via .\scripts\start-local.ps1 and connect clients to the MCP Gateway at http://127.0.0.1:8000/mcp.

• Direct STDIO Transport (Claude Desktop / Cursor): SuperOffice MCP can run directly over standard input/output without starting HTTP daemons:

  uv run python -m so_mcp.stdio

• Stakeholder Presentation Deck: A complete executive presentation deck is provided at SuperOffice_AI_Support_MCP_Client_Deck.pptx covering architecture, security boundaries, diagnostic workflows, and the local-to-production roadmap.

Getting Started / Local Development

Prerequisites

• Python >= 3.12

• uv package manager (pip install uv or install via official installer)

• PowerShell 7+ (on Windows) or Bash (on Linux/macOS)

Setup Instructions

1. Clone and Install:

   uv sync

2. Configure Local Environment:

   cp .env.example .env

   Edit .env to configure your local test database endpoints and local development JWT secret.

3. Pre-Flight Diagnostic Probes & Connectivity Dashboard:

   # Terminal CLI Report (Fast, structured readiness check)
   powershell -File scripts/check-preflight.ps1
   
   # Visual Web Dashboard (Browser GUI with live re-run capability)
   powershell -File scripts/preflight-gui.ps1

Verification & Quality Baseline

The codebase maintains a 100% verified baseline with zero lint or type errors:

# Run full regression suite (1189 passed, 12 skipped live-DB tests)
uv run pytest

# Linting and style verification
uv run ruff check src tests
uv run ruff format --check src tests

# Strict type checking
uv run mypy src tests

Local-Development-Only Exceptions

The local development setup incorporates specific exceptions that must be replaced in production:

1. Self-Signed TLS Trust: SUPEROFFICE_ALLOW_SELF_SIGNED_CERT=true for local test servers.

2. MSSQL Certificate Trust: DIAGNOSTICS_MSSQL_TRUST_SERVER_CERTIFICATE=true for local development SQL Server.

3. Symmetric JWT Signing: Local tests use HS256 shared secrets; production requires asymmetric JWT validation / JWKS.

4. Local Database & Cache: Local PostgreSQL on 127.0.0.1:5432 and locally cached FastEmbed model weights.

5. Local Artifact Store: Knowledge artifacts stored in a local filesystem directory.

Documentation Index

Roadmap

• FLC.4: Local Development Release 1.0 Checkpoint & Git Tag (COMPLETE / APPROVED — v1.0.0-local.1).

• FLC.5: Repository Professionalization & Remote Publication (Git history audit, provider-neutral hygiene, private repo push).

• PR.0–PR.12: Production Readiness Program (Production deployment, containerization, PKI, network policies, HA/DR, and operational acceptance).