ðū Database Persistence & Envelope Encryption Pipeline â
This document details the multi-engine persistence architecture, relational data model, AES-256-GCM authenticated envelope encryption pipeline, and dynamic secret provider resolution in the Model Context Gateway (MCG).
ð Table of Contents â
1. Database & Persistence Architecture â
The gateway abstracts persistent storage behind IDbConnectionFactory and high-performance Dapper repositories. It natively supports SQLite, Microsoft SQL Server, and MySQL / MariaDB.
Unified Entity-Relationship Diagram (Mermaid ERD) â
For the standalone schema reference and column constraints catalog, see Canonical Data Model & Database ERD and Database Provider Support & Deployment Matrix.
Engine Dialect Strategies (SQLite, MS SQL Server, MySQL) â
| Persistence Dimension | ðŠķ SQLite (Default) | ðĒ Microsoft SQL Server | ðŽ MySQL / MariaDB |
|---|---|---|---|
| Provider Key | sqlite | mssql | mysql |
| Concurrency Mode | WAL (Write-Ahead Logging) | Row-Level Locking / Always On | InnoDB MVCC Transactions |
| Execution Paradigm | Direct SQL & Parameterized Dapper | T-SQL Stored Procedures (sp_*) | Stored Procedures (sp_*) |
| Upsert Syntax | ON CONFLICT(Id) DO UPDATE | IF EXISTS ... UPDATE ELSE INSERT | ON DUPLICATE KEY UPDATE |
| Parameter Prefix | @Param | @Param | Strict p_Param |
| Timestamp Generation | CURRENT_TIMESTAMP (ISO-8601) | SYSUTCDATETIME() (DATETIME2) | CURRENT_TIMESTAMP / NOW() |
| DDL Migrations | In-Process Automatic (DatabaseSeederService) | Scripted DDL (scripts/db/mssql/) | Scripted DDL (scripts/db/mysql/) |
2. Secret Provider & Envelope Encryption Pipeline â
AES-256-GCM Envelope Encryption Specification â
Sensitive database columns (e.g. AppKeys.EncryptedKey, SecretProviders.EncryptedConfigJson, AuthProviderConfigs.EncryptedConfigJson, Servers.ApiKey) are protected using AES-256-GCM authenticated envelope encryption via SymmetricEncryptionHelper:
- Nonce Generation: A cryptographically secure 12-byte random nonce is generated for every encryption operation using
RandomNumberGenerator.GetBytes(12). - Authenticated Tagging:
AesGcmcomputes a 16-byte authentication tag over the ciphertext, guaranteeing tamper detection and payload integrity. - Storage Encoding: The concatenated packet
[ Nonce (12B) || Tag (16B) || Ciphertext (NB) ]is Base64 encoded before persistence into relational database columns.
Master Key Derivation (PBKDF2) â
- Derives a 256-bit symmetric encryption key from the environment variables
MCG_SECRET,MCG_MASTER_KEY, orDB_ENCRYPTION_KEY. - Uses
Rfc2898DeriveBytes.Pbkdf2configured with 600,000 iterations of SHA-256 and a deployment-specific salt (_McpRouter_Salt_v2).
Pluggable Retrievers & Dynamic Reload Without Restart â
When an administrator updates a Secret Provider in the Settings UI:
- The frontend submits the updated credentials to
POST /api/providers/secret. ProviderConfigSecurityHelperencrypts the payload with AES-256-GCM and persists it to the database.- The in-memory cache in
CompositeSecretRetrieveris immediately invalidated. - Subsequent downstream requests fetch fresh tokens from HashiCorp Vault or the updated provider without requiring a container or service restart.
Secret Resolution & Encryption Pipeline Flowchart â
The following flowchart illustrates how secrets are dynamically resolved, decrypted, and injected for downstream MCP requests:
Related Specifications: