Multi Tenant Single Database Architecture

Architecture Overview

1. Azure SQL Server (Single Database, Multi-Tenant)

   • Tenant Isolation: Utilize row-based data partitioning with a TenantId field in all tables to ensure each tenant's data is separated.
   • Elastic Pool: For scalability, consider Azure SQL Elastic Pools, which help optimize cost and performance.
   • Security: Implement role-based access control (RBAC) and encryption (Azure Transparent Data Encryption - TDE) to ensure data protection.

2. .NET Middleware (Microservices or Web API)

   • ASP.NET Core Web API: Use ASP.NET Core to build RESTful services.
   • Multi-Tenancy: Implement middleware in the API to handle tenant context, which extracts tenant information from the request (e.g., from headers, subdomain, or JWT token).
     • Use a TenantResolver service to identify tenant context.
     • Apply filters for tenant-based data isolation in data queries.
   • Authentication & Authorization: Use Azure AD B2C or IdentityServer4 for user authentication and JWT tokens to secure API endpoints.
   • Order Management: The API should handle stock order creation, updates, cancellations, and queries.
   • Microservices (optional): Consider breaking down the system into microservices (e.g., OrderService, TradeService, UserService) to increase scalability.

3. Redis Cache

   • Caching Strategy: Use Redis for caching frequently accessed data like tenant metadata, stock prices, order histories, and trade status.
   • Session Management: Store session data (if needed) in Redis for quick retrieval, minimizing database round-trips.
   • Caching API Responses: Cache expensive database queries (e.g., stock prices or order histories) to improve performance.
   • Tenant Isolation in Cache: Use a prefix for each tenant in Redis keys to ensure separation.

4. Azure Components

   • Azure App Service: Deploy the .NET API on Azure App Service for easy scaling and management.
   • Azure Key Vault: Securely store credentials, API keys, and connection strings.
   • Azure Blob Storage: For storing non-relational data like trade receipts, logs, etc.
   • Azure Monitor & Application Insights: Track application health, performance metrics, and logging.

Data Flow Example

1. User Authentication: The client (mobile/web app) sends a login request; the .NET Middleware validates the credentials using Azure AD B2C.
2. Tenant Context Setup: After successful authentication, a JWT token containing the TenantId is issued. All subsequent API calls include this token.
3. Order Processing:
   • Client sends a stock trade order request (buy/sell).
   • The API validates the request, ensures the user has permission (based on TenantId), and checks stock availability.
   • The order is processed and stored in the SQL Server with the TenantId.
   • A success response is returned, and the result is cached in Redis for fast retrieval.
4. Caching:
   • Redis stores stock prices, order statuses, and user session data for faster access.
   • When a stock price or order status is updated, the Redis cache is invalidated and refreshed.

Scaling Considerations

• Horizontal Scaling: Use Azure App Service Autoscaling to handle traffic spikes.
• SQL Performance: Regularly optimize SQL queries and use indexes to maintain performance at scale.
• Redis Scaling: Use Azure Redis Cache for distributed caching across instances.
• Multi-Tenant Strategy: If needed, evolve the system to shard tenants across multiple databases as the user base grows.