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.

Strategy	Isolation	Cost	Complexity
Database-per-tenant (ERP)	Strongest	Higher	Medium
Schema-per-tenant	Medium	Medium	Low
Row-level (shared table)	Weakest	Lowest	High

Tenant Resolution Flow

Key interfaces:

// 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

Property	Value
Host	192.168.120.95 (SSH: sqladmin)
Engine	SQL Server (exact version tracked in infra docs)
Model	Multiple databases on one VM, one DB per tenant
Access	Via Azure Key Vault secret per tenant
Admin SSH	ssh -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.

Environment	Backend
Development	MongoDB 7.0 on VM (ports 27017–27020)
Stage	MongoDB 7.0 on VM
Pre-Production	MongoDB 7.0 on VM
Production	Azure 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

Property	Value
Type	Azure Cache for Redis (Managed)
Use Cases	Session cache, distributed cache, session limiting
SSL	Required (ssl=True)
Stage Host	mic-erp-be-stage-redis.uksouth.redis.azure.net:10000
Stage Secret	KV mic-erp-stg-kv → RedisConfiguration--Password

Redis Usage Matrix

Feature	Key Pattern	TTL
Session limiting	session:{userId}	Sliding
Tenant context cache	tenant:{subdomain}	5 min
Keycloak token validation	kc:token:{hash}	60 s
Distributed lock	lock:{resource}	30 s

Configuration

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

---

EF Core

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

Quick Reference

Pattern	Detail
ORM	Entity Framework Core 8
Change Tracking	Disabled for reads (AsNoTracking())
Soft Deletes	Global query filter IsDeleted == false
Multi-Tenancy	Global query filter TenantId == currentTenantId
Auditing	AuditInterceptor auto-populates audit fields
Time Source	IClockService (never DateTime.Now)
Transactions	IUnitOfWork<TContext> with BeginTransactionAsync()
Decimal Precision	18,4 via ConfigureDecimalPrecision()

---

Data Flow Summary

---

Related Sections

• EF Core Patterns — repository pattern, transactions, interceptors
• MongoDB — VM setup, SCRAM gotcha, intentional typo
• Networking — 11 — VNet, SQL VM connectivity
• Environments — 10 — per-env connection string management