Multitenancy is all about navigating data isolation with precision. The Particlesy approach to this challenge takes advantage of the capabilities of Azure SQL Hyperscale, complemented by a mechanism implemented within the infrastructure layer generic repository and the command level CQRS via MediatR. Let’s dissect this architecture for a granular understanding.

Introducing Azure SQL Hyperscale

Azure SQL Hyperscale offers highly scalable storage and computing resources that adapt on-demand to the workload’s requirements. Its ability to auto-scale with super-fast backup and restore capabilities makes it an optimal choice for Particlesy’s demanding multi-tenant architecture.

Understanding the Dual-Enforcement Mechanism

Before we delve into the code, let’s conceptualize our dual-enforcement strategy:

1. CQRS Level: Using MediatR, commands and queries are distinctly modeled. By attaching Tenant ID at this juncture, operations become inherently tenant-resolute.
2. Data Infrastructure Repository: Herein lies the final defense. If, for any reason, a command or query misses the Tenant ID, the repository ensures tenant-specific data retrieval and mutation.

Integrating Tenant ID in MediatR Commands and Queries

MediatR provides a mechanism to implement CQRS in .NET applications, allowing for clear separation of commands (write operations) and queries (read operations).

// Example MediatR Command with Tenant ID
public class UpdateUserDataCommand : IRequest<bool>
{
    public Guid TenantId { get; set; }
    public Guid UserId { get; set; }
    public UserData UserData { get; set; }
}

// Corresponding MediatR Query
public class GetUserDataQuery : IRequest<UserData>
{
    public Guid TenantId { get; set; }
    public Guid UserId { get; set; }
}

Notice the explicit use of TenantId in both commands and queries, ensuring tenant specificity right from the entry point of the operation.

Azure SQL Hyperscale in Action: Data Infrastructure Repository

The repository layer is pivotal in ensuring data integrity. It interacts directly with Azure SQL Hyperscale, leveraging its capabilities for efficient, tenant-aware operations.

public class UserRepository
{
    private readonly DbContext _context;

    public UserRepository(DbContext context)
    {
        _context = context;
    }

    public async Task<UserData> GetUserData(Guid tenantId, Guid userId)
    {
        return await _context.Users
                             .FirstOrDefaultAsync(u => u.TenantId == tenantId && u.UserId == userId);
    }

    public async Task<bool> UpdateUserData(UserData userData)
    {
        _context.Users.Update(userData);
        var affectedRows = await _context.SaveChangesAsync();
        return affectedRows > 0;
    }
}

Our repository ensures that all SQL commands executed against Azure SQL Hyperscale are tenant-aware. This tenant-filtering is essential for preserving data isolation.

Deriving Tenant ID from OAuth Tokens or API Keys

In the realm of multitenancy, establishing the correct tenant context for each operation is paramount. This is particularly crucial in systems where every request can potentially be associated with a different tenant. For many systems, the tenant context is derived from authentication and authorization tokens such as OAuth tokens or API keys.

OAuth Tokens:

OAuth tokens, especially JWTs (JSON Web Tokens), often contain claims that can be used to identify the tenant. For instance, an OAuth token might contain a tenant_id claim that specifies which tenant the token was issued for. When a user logs in, the authentication system determines the tenant based on the user’s credentials and includes the appropriate tenant_id in the token.

csharpCopy code// Example token payload
{
    "sub": "1234567890",
    "name": "John Doe",
    "tenant_id": "a1b2c3d4e5"
}

Upon receiving a request with an attached token, the system can decode the token, validate its signature, and extract the tenant_id claim to determine the tenant context for the operation.

API Keys:

API keys, on the other hand, are often generated per tenant. When a tenant is onboarded onto the system, they might be issued a unique API key. This key is then used in subsequent requests to authenticate the tenant.

To derive the Tenant ID from an API key, the system can have a mapping (like a dictionary or a database table) that relates each API key to its associated Tenant ID.

csharpCopy code// Example API Key to Tenant ID mapping
Dictionary<string, Guid> apiKeyToTenantMap = new Dictionary<string, Guid>
{
    { "APIKEY123456", new Guid("a1b2c3d4e5") }
};

When a request comes in with an API key, the system looks up the corresponding Tenant ID from the mapping, setting the tenant context accordingly.

Both these methods ensure that every operation is executed in the correct tenant context, bolstering the system’s security and ensuring data integrity and isolation.

Optimizing Query Performance in Hyperscale

Given the vast scaling capabilities of Hyperscale, it’s pivotal to optimize your SQL queries for tenant-specific operations. Utilizing Azure SQL Hyperscale’s native capabilities, such as adaptive query processing, ensures efficient data operations.

Additionally, indexing strategies play a significant role. For multi-tenant applications, composite indexes that include the TenantId are often more effective.

modelBuilder.Entity<User>()
            .HasIndex(u => new { u.TenantId, u.UserId })
            .HasName("idx_tenant_user");

By creating such indexes, query performance for tenant-specific operations is enhanced, especially in large-scale multi-tenant scenarios.

Conclusion

The collaboration of MediatR’s CQRS approach with Azure SQL Hyperscale’s scalability offers a robust solution for the multi-tenant challenges faced by platforms like Particlesy. The technical prowess of ensuring data isolation at multiple layers not only guarantees security but also optimizes performance. As the cloud architectural landscape continues to evolve, such integrations will be paramount for scalable, secure, and efficient solutions.