Skip to content

Data Layer Overview ​

Section: 13 — Data
Last Updated: 2026-05-30
Scope: SQL Server, MongoDB, Redis, EF Core, Multi-tenancy


Architecture at a Glance ​

Microtec ERP uses a polyglot persistence strategy: relational data lives in SQL Server (per-tenant databases), document data in MongoDB/Cosmos DB, and volatile/cache data in Redis. Every tenant is completely isolated at the database level.


Multi-Tenancy Strategy ​

Database-Per-Tenant ​

ERP uses database-per-tenant isolation — each tenant gets its own SQL Server database. This is the most secure isolation model but requires careful connection-string management.

StrategyIsolationCostComplexity
Database-per-tenant (ERP)StrongestHigherMedium
Schema-per-tenantMediumMediumLow
Row-level (shared table)WeakestLowestHigh

Tenant Resolution Flow ​

Key interfaces:

csharp
// Resolves current tenant from HTTP context
public interface ITenantProvider
{
    string TenantId { get; }
    string ConnectionString { get; }
}

// Sets/gets tenant context across the request lifetime
public interface ITenantContextManager
{
    void SetTenant(TenantContext context);
    TenantContext GetCurrentTenant();
}

Important: All DbContext registrations are scoped, not singleton. Each request creates a new context bound to the resolved tenant's connection string.


SQL Server ​

Shared VM ​

PropertyValue
Host192.168.120.95 (SSH: sqladmin)
EngineSQL Server (exact version tracked in infra docs)
ModelMultiple databases on one VM, one DB per tenant
AccessVia Azure Key Vault secret per tenant
Admin SSHssh -i ~/.ssh/mic-shared-sql sqladmin@20.50.120.95

Database Layout ​

SQL Server VM (192.168.120.95)
├── mic_admin          ← BusinessOwner / admin portal
├── tenant_acme        ← Tenant: Acme Corp
├── tenant_beta        ← Tenant: Beta Ltd
├── tenant_gamma       ← Tenant: Gamma Inc
└── ...

Connection String Pattern ​

Connection strings are stored in Azure Key Vault using the double-dash naming convention:

KV Secret name:  Tenants--acme--ConnectionString
Resolved value:  Server=192.168.120.95;Database=tenant_acme;User Id=...;Password=...

Shell gotcha: SA passwords containing ! must be passed via Python subprocess or a quoted heredoc — not direct sqlcmd shell expansion.


MongoDB ​

See mongodb.md for full details including the intentional MonogoDb typo.

EnvironmentBackend
DevelopmentMongoDB 7.0 on VM (ports 27017–27020)
StageMongoDB 7.0 on VM
Pre-ProductionMongoDB 7.0 on VM
ProductionAzure Cosmos DB (Mongo API)

The MongoDB Atlas / Cosmos DB API is used in production for SLA, global distribution, and managed backups.


Redis ​

Azure Managed Redis ​

PropertyValue
TypeAzure Cache for Redis (Managed)
Use CasesSession cache, distributed cache, session limiting
SSLRequired (ssl=True)
Stage Hostmic-erp-be-stage-redis.uksouth.redis.azure.net:10000
Stage SecretKV mic-erp-stg-kv → RedisConfiguration--Password

Redis Usage Matrix ​

FeatureKey PatternTTL
Session limitingsession:{userId}Sliding
Tenant context cachetenant:{subdomain}5 min
Keycloak token validationkc:token:{hash}60 s
Distributed locklock:{resource}30 s

Configuration ​

json
{
  "RedisConfiguration": {
    "ConnectionString": "<from KV>",
    "Password": "<from KV>",
    "Ssl": true,
    "DefaultDatabase": 0
  }
}

EF Core ​

See ef-core-patterns.md for complete patterns.

Quick Reference ​

PatternDetail
ORMEntity Framework Core 8
Change TrackingDisabled for reads (AsNoTracking())
Soft DeletesGlobal query filter IsDeleted == false
Multi-TenancyGlobal query filter TenantId == currentTenantId
AuditingAuditInterceptor auto-populates audit fields
Time SourceIClockService (never DateTime.Now)
TransactionsIUnitOfWork<TContext> with BeginTransactionAsync()
Decimal Precision18,4 via ConfigureDecimalPrecision()

Data Flow Summary ​


Internal Documentation — Microtec