Multi-Tenancy in EASE

Multi-tenancy is a core architectural pattern in EASE that allows a single application instance to serve multiple customers (tenants) while keeping their data completely isolated.

Why Multi-Tenancy?

Benefits:

✅ Cost efficiency (shared infrastructure)
✅ Simplified maintenance (one codebase)
✅ Centralized upgrades (all tenants updated together)
✅ Resource optimization (better utilization)

Challenges:

❌ Data isolation (prevent data leakage)
❌ Performance (noisy neighbor problem)
❌ Customization (per-tenant requirements)
❌ Scalability (growing tenant demands)

EASE addresses all these challenges!

Three Strategies

Choose based on tenant size and isolation requirements:

Strategy 1: Pluggable Database (PDB)

Best for: Fortune 500 clients, >10,000 users

How it works:

Each tenant gets their own Oracle Pluggable Database
Complete database isolation
Independent backups, restore, and versions
Can be moved to dedicated hardware if needed

Oracle Configuration:

-- Create CDB (Container Database)
CREATE DATABASE EASECDB;

-- Create PDB for tenant
CREATE PLUGGABLE DATABASE tenant_acme
  ADMIN USER pdb_admin IDENTIFIED BY password
  FILE_NAME_CONVERT = ('/pdbseed/', '/acme/');

-- Connect and configure
ALTER PLUGGABLE DATABASE tenant_acme OPEN;
ALTER PLUGGABLE DATABASE tenant_acme SAVE STATE;

Application Code:

public class TenantDbContextFactory
{
    public DbContext Create(string tenantId)
    {
        var connectionString = _pdbResolver.GetConnectionString(tenantId);
        return new ApplicationDbContext(connectionString);
    }
}

Pros:

Maximum isolation
Independent scaling
Tenant-specific configurations
Easy to archive/move tenants

Cons:

Higher cost per tenant
More complex management
Resource overhead

Strategy 2: Schema Per Tenant

Best for: Mid-market, 100-10,000 users

How it works:

Single database with separate schema per tenant
Shared infrastructure, isolated data
Good balance of cost and isolation

Oracle Configuration:

-- Create schema for tenant
CREATE USER tenant_acme IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO tenant_acme;

-- Grant tablespace quota
ALTER USER tenant_acme QUOTA UNLIMITED ON USERS;

-- Create tables in tenant schema
CREATE TABLE tenant_acme.bookings (
    id NUMBER PRIMARY KEY,
    -- ... columns
);

Application Code:

public class SchemaPerTenantRepository<T> : IRepository<T>
{
    public async Task<List<T>> GetAllAsync()
    {
        var schema = _tenantContext.CurrentSchema; // e.g., "tenant_acme"
        var query = $"SELECT * FROM {schema}.{typeof(T).Name}";
        return await _db.QueryAsync<T>(query);
    }
}

Pros:

Good isolation
Moderate cost
Easier than PDB to manage
Can still customize per tenant

Cons:

Schema migration complexity
More schemas = more objects to manage
Cross-tenant queries harder

Strategy 3: Shared Schema + VPD

Best for: Small businesses, <100 users

How it works:

Single schema shared by all tenants
Row-level security via Virtual Private Database (VPD)
Most cost-effective approach

Database Design:

-- All tables include tenant_id
CREATE TABLE bookings (
    id NUMBER PRIMARY KEY,
    tenant_id NUMBER NOT NULL,
    user_id NUMBER NOT NULL,
    activity_id NUMBER NOT NULL,
    status VARCHAR2(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);

-- Create index on tenant_id for performance
CREATE INDEX idx_bookings_tenant ON bookings(tenant_id);

VPD Policy:

-- Create VPD policy function
CREATE OR REPLACE FUNCTION tenant_security_policy(
    schema_name IN VARCHAR2,
    table_name IN VARCHAR2
)
RETURN VARCHAR2
IS
    predicate VARCHAR2(400);
BEGIN
    -- Get current tenant from session context
    predicate := 'tenant_id = SYS_CONTEXT(''TENANT_CTX'', ''TENANT_ID'')';
    RETURN predicate;
END;
/

-- Apply policy to bookings table
BEGIN
    DBMS_RLS.ADD_POLICY(
        object_schema   => 'EASE',
        object_name     => 'BOOKINGS',
        policy_name     => 'TENANT_ISOLATION',
        function_schema => 'EASE',
        policy_function => 'tenant_security_policy',
        statement_types => 'SELECT,INSERT,UPDATE,DELETE'
    );
END;
/

Application Code:

public class VPDRepository<T> : BaseRepository<T>
{
    public override async Task<List<T>> GetAllAsync()
    {
        // Set tenant context BEFORE query
        await SetTenantContext(_tenantContext.CurrentTenantId);

        // VPD automatically filters by tenant_id
        var result = await _db.QueryAsync<T>("SELECT * FROM bookings");
        return result.ToList();
    }

    private async Task SetTenantContext(int tenantId)
    {
        // Set Oracle session context for VPD
        var sql = "BEGIN DBMS_SESSION.SET_CONTEXT('TENANT_CTX', 'TENANT_ID', :tenantId); END;";
        await _db.ExecuteAsync(sql, new { tenantId });
    }
}

Pros:

Most cost-effective
Simple schema management
Easy cross-tenant analytics
Automatic filtering (can't forget!)

Cons:

Less isolation
Harder to customize per tenant
VPD performance overhead (minimal)

Tenant Context Management

How does the application know which tenant to use?

Method 1: Subdomain-based

https://acme.yoursaas.com  → tenant_id = acme
https://contoso.yoursaas.com → tenant_id = contoso

Implementation:

public class TenantResolver : ITenantResolver
{
    public int ResolveTenantId(HttpContext context)
    {
        var host = context.Request.Host.Value;
        var subdomain = host.Split('.')[0];
        return _tenantService.GetTenantIdBySubdomain(subdomain);
    }
}

Method 2: JWT Claim-based

public class JwtTenantResolver : ITenantResolver
{
    public int ResolveTenantId(HttpContext context)
    {
        var claims = context.User.Claims;
        var tenantClaim = claims.FirstOrDefault(c => c.Type == "tenant_id");
        return int.Parse(tenantClaim?.Value ?? "0");
    }
}

Method 3: Header-based

GET /api/bookings
X-Tenant-Id: 123
Authorization: Bearer <jwt>

Data Isolation Testing

Critical: Always test data isolation!

[Fact]
public async Task Tenant_Cannot_Access_Other_Tenant_Data()
{
    // Arrange
    var tenant1 = new Tenant { Id = 1, Name = "Acme" };
    var tenant2 = new Tenant { Id = 2, Name = "Contoso" };

    var booking1 = new Booking { Id = 1, TenantId = 1, ActivityId = 100 };
    var booking2 = new Booking { Id = 2, TenantId = 2, ActivityId = 200 };

    await _db.InsertAsync(booking1);
    await _db.InsertAsync(booking2);

    // Act - Query as Tenant 1
    SetCurrentTenant(1);
    var results = await _repository.GetAllAsync();

    // Assert - Should only see Tenant 1 data
    Assert.Single(results);
    Assert.Equal(1, results[0].TenantId);
    Assert.DoesNotContain(results, b => b.TenantId == 2);
}

Performance Considerations

Index tenant_id!

-- CRITICAL for performance
CREATE INDEX idx_bookings_tenant ON bookings(tenant_id);
CREATE INDEX idx_users_tenant ON users(tenant_id);
CREATE INDEX idx_activities_tenant ON activities(tenant_id);

Use Composite Indexes

-- If you often filter by tenant + status
CREATE INDEX idx_bookings_tenant_status ON bookings(tenant_id, status);

Partition by tenant_id

-- For very large tables
CREATE TABLE bookings (
    -- columns
)
PARTITION BY LIST (tenant_id) (
    PARTITION p_tenant_1 VALUES (1),
    PARTITION p_tenant_2 VALUES (2),
    PARTITION p_others VALUES (DEFAULT)
);

Migration Between Strategies

You can migrate tenants between strategies:

Small → Medium Tenant

-- Move from Shared Schema to Schema Per Tenant
CREATE SCHEMA tenant_acme;
INSERT INTO tenant_acme.bookings
SELECT * FROM bookings WHERE tenant_id = 123;

Medium → Large Tenant

-- Move from Schema to PDB
CREATE PLUGGABLE DATABASE tenant_acme;
-- Use Data Pump to export/import schema

Best Practices

Always validate tenant_id
- Don't trust client input
- Validate against JWT claims
- Check user belongs to tenant
Use framework abstractions
- Never write raw SQL with tenant_id
- Use BaseRepository which handles filtering
Test isolation thoroughly
- Write integration tests
- Verify VPD policies work
- Test cross-tenant scenarios
Monitor per-tenant metrics
- Track usage by tenant
- Identify "noisy neighbors"
- Plan capacity
Document tenant strategy
- Make it clear which strategy is used
- Document migration path
- Keep architectural decision records

Comparison Matrix

Feature	PDB	Schema	Shared + VPD
Cost	High	Medium	Low
Isolation	Maximum	High	Medium
Complexity	High	Medium	Low
Scalability	Excellent	Good	Good
Customization	Easy	Medium	Hard
Maintenance	Complex	Medium	Simple
Best For	Enterprise	Mid-market	Small business

Real-World Example

SaaS Company with 1000 Tenants:

5 enterprise clients → PDB (5 PDBs)
50 mid-market → Schema Per Tenant (50 schemas)
945 small businesses → Shared Schema + VPD

Cost Savings: 70% vs. giving every tenant a PDB

Next Steps

Implement RBAC - Add role-based access
Database Standards - Schema design
Testing Multi-Tenancy - Test isolation

Questions? Press Ctrl+/ to ask BACH about multi-tenancy!