Use when implementing tenant data isolation in databases. Covers Row-Level Security (RLS), schema-per-tenant, database-per-tenant patterns with EF Core, SQL Server, PostgreSQL, and Cosmos DB implementations.
Implements tenant data isolation patterns including Row-Level Security, schema-per-tenant, and database-per-tenant with EF Core, SQL Server, PostgreSQL, and Cosmos DB. Use when building multi-tenant SaaS applications to prevent cross-tenant data access and choose appropriate isolation strategies.
/plugin marketplace add melodic-software/claude-code-plugins/plugin install saas-patterns@melodic-softwareThis skill is limited to using the following tools:
references/database-per-tenant.mdreferences/row-level-security.mdreferences/schema-per-tenant.mdImplementation patterns for isolating tenant data at the database level, from logical to physical isolation.
Isolation Level vs Cost/Complexity:
Logical (Shared) Physical (Isolated)
◄──────────────────────────────────────────────────────►
┌──────────────┬──────────────┬──────────────┬──────────────┐
│ Row-Level │ Schema │ Database │ Server │
│ Security │ per Tenant │ per Tenant │ per Tenant │
├──────────────┼──────────────┼──────────────┼──────────────┤
│ Lowest Cost │ │ │ Highest Cost │
│ Shared DB │ Shared DB │ Dedicated DB │ Dedicated │
│ Shared Schema│ Dedicated │ │ Server │
│ │ Schema │ │ │
├──────────────┼──────────────┼──────────────┼──────────────┤
│ Scale: 1M+ │ Scale: 10K │ Scale: 1K │ Scale: 100s │
│ tenants │ tenants │ tenants │ tenants │
└──────────────┴──────────────┴──────────────┴──────────────┘
Single Database, Shared Schema, Logical Isolation:
┌─────────────────────────────────────────────────────────────┐
│ Shared Database │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Orders Table │ │
│ │ ┌─────┬──────────┬─────────────┬────────┬─────────────┐ │ │
│ │ │ id │ tenant_id│ customer │ amount │ created_at │ │ │
│ │ ├─────┼──────────┼─────────────┼────────┼─────────────┤ │ │
│ │ │ 1 │ acme │ John Doe │ 99.00 │ 2025-01-15 │ │ │
│ │ │ 2 │ contoso │ Jane Smith │ 149.00 │ 2025-01-15 │ │ │
│ │ │ 3 │ acme │ Bob Wilson │ 49.00 │ 2025-01-16 │ │ │
│ │ │ 4 │ startup │ Alice Brown │ 199.00 │ 2025-01-16 │ │ │
│ │ └─────┴──────────┴─────────────┴────────┴─────────────┘ │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ RLS Policy: Only show rows where tenant_id = current_tenant │
└─────────────────────────────────────────────────────────────┘
Tenant "acme" sees only rows 1 and 3
Tenant "contoso" sees only row 2
Tenant "startup" sees only row 4
Step 1: Add TenantId to all tables
┌────────────────────────────────────────────────────────────┐
│ ALTER TABLE Orders ADD TenantId NVARCHAR(50) NOT NULL; │
│ CREATE INDEX IX_Orders_TenantId ON Orders(TenantId); │
└────────────────────────────────────────────────────────────┘
Step 2: Create security predicate function
┌────────────────────────────────────────────────────────────┐
│ CREATE FUNCTION dbo.fn_TenantAccessPredicate │
│ (@TenantId NVARCHAR(50)) │
│ RETURNS TABLE │
│ WITH SCHEMABINDING │
│ AS RETURN │
│ SELECT 1 AS AccessGranted │
│ WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') │
│ AS NVARCHAR(50)) │
│ OR SESSION_CONTEXT(N'TenantId') IS NULL; │
└────────────────────────────────────────────────────────────┘
Step 3: Create security policy
┌────────────────────────────────────────────────────────────┐
│ CREATE SECURITY POLICY dbo.TenantSecurityPolicy │
│ ADD FILTER PREDICATE │
│ dbo.fn_TenantAccessPredicate(TenantId) ON dbo.Orders, │
│ ADD BLOCK PREDICATE │
│ dbo.fn_TenantAccessPredicate(TenantId) ON dbo.Orders │
│ AFTER INSERT, AFTER UPDATE; │
└────────────────────────────────────────────────────────────┘
Step 4: Set context per request
┌────────────────────────────────────────────────────────────┐
│ EXEC sp_set_session_context @key=N'TenantId', │
│ @value=@CurrentTenantId; │
└────────────────────────────────────────────────────────────┘
Step 1: Enable RLS on table
┌────────────────────────────────────────────────────────────┐
│ ALTER TABLE orders ENABLE ROW LEVEL SECURITY; │
│ ALTER TABLE orders FORCE ROW LEVEL SECURITY; │
└────────────────────────────────────────────────────────────┘
Step 2: Create policy
┌────────────────────────────────────────────────────────────┐
│ CREATE POLICY tenant_isolation_policy ON orders │
│ USING (tenant_id = current_setting('app.current_tenant'))│
│ WITH CHECK (tenant_id = │
│ current_setting('app.current_tenant')); │
└────────────────────────────────────────────────────────────┘
Step 3: Set context per connection
┌────────────────────────────────────────────────────────────┐
│ SET app.current_tenant = 'acme'; │
└────────────────────────────────────────────────────────────┘
Application-Level RLS (Defense in Depth):
DbContext Configuration:
┌────────────────────────────────────────────────────────────┐
│ public class AppDbContext : DbContext │
│ { │
│ private readonly ITenantContext _tenantContext; │
│ │
│ protected override void OnModelCreating(ModelBuilder mb) │
│ { │
│ // Apply filter to all tenant entities │
│ mb.Entity<Order>() │
│ .HasQueryFilter(o => │
│ o.TenantId == _tenantContext.TenantId); │
│ │
│ mb.Entity<Customer>() │
│ .HasQueryFilter(c => │
│ c.TenantId == _tenantContext.TenantId); │
│ } │
│ } │
└────────────────────────────────────────────────────────────┘
Key Points:
- EF Core filters applied BEFORE SQL generation
- Cannot be bypassed by LINQ queries
- Use IgnoreQueryFilters() only for admin scenarios
- Combine with database RLS for defense in depth
✅ Ideal For:
- B2C SaaS with many tenants (10,000+)
- Homogeneous tenant requirements
- Cost-sensitive deployments
- Simple compliance requirements
- Minimal schema customization needs
❌ Avoid When:
- Strict compliance (HIPAA, PCI with audit requirements)
- Tenants need schema customization
- Performance isolation critical
- Data sovereignty requirements
Single Database, Separate Schemas:
┌─────────────────────────────────────────────────────────────┐
│ Shared Database │
│ │
│ ┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐ │
│ │ Schema: acme │ │ Schema: contoso │ │ Schema: startup │ │
│ │ │ │ │ │ │ │
│ │ ┌─────────────┐ │ │ ┌─────────────┐ │ │ ┌─────────────┐ │ │
│ │ │ Orders │ │ │ │ Orders │ │ │ │ Orders │ │ │
│ │ │ Customers │ │ │ │ Customers │ │ │ │ Customers │ │ │
│ │ │ Products │ │ │ │ Products │ │ │ │ Products │ │ │
│ │ │ (Custom tbl)│ │ │ └─────────────┘ │ │ │ (Custom tbl)│ │ │
│ │ └─────────────┘ │ │ │ │ └─────────────┘ │ │
│ └─────────────────┘ └─────────────────┘ └─────────────────┘ │
│ │
│ Each schema is isolated, can have custom tables/columns │
└─────────────────────────────────────────────────────────────┘
Connection/Schema Selection:
┌────────────────────────────────────────────────────────────┐
│ Option A: Schema in Connection String │
│ - Set default schema per connection │
│ - Works with connection pooling │
│ │
│ Option B: Schema per Query │
│ - Prefix table names: [acme].Orders │
│ - More flexible, slightly more complex │
│ │
│ Option C: EF Core Schema Configuration │
│ - Set HasDefaultSchema() in OnModelCreating │
│ - Dynamic based on tenant context │
└────────────────────────────────────────────────────────────┘
Migration Strategy:
┌────────────────────────────────────────────────────────────┐
│ Approach 1: Apply to All Schemas (Homogeneous) │
│ - Loop through all tenant schemas │
│ - Apply same migration to each │
│ - Must be backward compatible │
│ │
│ Approach 2: Per-Tenant Migrations (Heterogeneous) │
│ - Track migration state per tenant │
│ - Allows tenant-specific schema variations │
│ - More complex to manage │
│ │
│ Recommendation: Start homogeneous, allow customization │
│ only for enterprise tier │
└────────────────────────────────────────────────────────────┘
✅ Ideal For:
- Medium number of tenants (100-10,000)
- Some schema customization needed
- Better isolation than RLS, lower cost than DB-per-tenant
- Easier backup/restore per tenant than RLS
❌ Avoid When:
- Very large number of tenants (schema count limits)
- Strict performance isolation needed
- Complete infrastructure isolation required
- Per-tenant backup/restore at database level needed
Dedicated Database per Tenant:
┌──────────────────────────────────────────────────────────────────┐
│ SQL Server Instance │
│ │
│ ┌────────────────┐ ┌────────────────┐ ┌────────────────┐ │
│ │ DB: db_acme │ │ DB: db_contoso │ │ DB: db_startup │ ... │
│ │ │ │ │ │ │ │
│ │ Full schema │ │ Full schema │ │ Full schema │ │
│ │ Own backups │ │ Own backups │ │ Own backups │ │
│ │ Own security │ │ Own security │ │ Own security │ │
│ │ Custom config │ │ Custom config │ │ Custom config │ │
│ └────────────────┘ └────────────────┘ └────────────────┘ │
│ │
│ Elastic Pool for cost optimization (optional) │
└──────────────────────────────────────────────────────────────────┘
Tenant Catalog → Connection String (SQL Server - PascalCase):
┌────────────────────────────────────────────────────────────┐
│ TenantCatalog Table: │
│ ┌──────────┬───────────────────────────────────────────┐ │
│ │ TenantId │ ConnectionString │ │
│ ├──────────┼───────────────────────────────────────────┤ │
│ │ acme │ Server=sql1;Database=db_acme;... │ │
│ │ contoso │ Server=sql1;Database=db_contoso;... │ │
│ │ startup │ Server=sql2;Database=db_startup;... │ │
│ └──────────┴───────────────────────────────────────────┘ │
│ │
│ Resolution Flow: │
│ 1. Request arrives with TenantId │
│ 2. Lookup connection string in catalog (cached) │
│ 3. Create/reuse connection from tenant-specific pool │
│ 4. Execute query on tenant's database │
└────────────────────────────────────────────────────────────┘
Cost Optimization with Elastic Pools:
┌────────────────────────────────────────────────────────────┐
│ Without Elastic Pool: │
│ - 10 tenants × S2 ($75/month) = $750/month │
│ - Each DB has dedicated 50 DTU │
│ - Underutilized during off-peak │
│ │
│ With Elastic Pool: │
│ - 1 pool with 200 eDTU = $300/month │
│ - 10 databases share the pool │
│ - Burst to 50 eDTU per DB when needed │
│ - Average utilization across tenants │
│ │
│ Savings: 60% ($450/month) │
│ │
│ Best For: Variable, unpredictable workloads │
└────────────────────────────────────────────────────────────┘
✅ Ideal For:
- Enterprise customers with strict compliance
- Tenants needing complete data isolation
- Per-tenant backup/restore requirements
- Custom schema per tenant
- Data sovereignty requirements
- Tenants with significantly different workloads
❌ Avoid When:
- Many small tenants (cost prohibitive)
- Fast provisioning needed (<1 minute)
- Minimal compliance requirements
- Cost-sensitive market
Cosmos DB Isolation Levels:
Level 1: Partition Key per Tenant (Logical)
┌────────────────────────────────────────────────────────────┐
│ Container: orders │
│ Partition Key: /tenantId │
│ │
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ Partition: │ │ Partition: │ │ Partition: │ │
│ │ acme │ │ contoso │ │ startup │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ │
│ │
│ Pros: Lowest cost, automatic scaling │
│ Cons: Shared RU/s, noisy neighbor possible │
└────────────────────────────────────────────────────────────┘
Level 2: Container per Tenant
┌────────────────────────────────────────────────────────────┐
│ Database: myapp │
│ │
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ Container: │ │ Container: │ │ Container: │ │
│ │ acme-orders │ │contoso-orders│ │startup-orders│ │
│ └──────────────┘ └──────────────┘ └──────────────┘ │
│ │
│ Pros: Per-tenant RU/s limits, RBAC per container │
│ Cons: Container limits (500K), higher management │
└────────────────────────────────────────────────────────────┘
Level 3: Database per Tenant
┌────────────────────────────────────────────────────────────┐
│ Cosmos Account: myapp │
│ │
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ Database: │ │ Database: │ │ Database: │ │
│ │ acme │ │ contoso │ │ startup │ │
│ │ └─containers │ │ └─containers │ │ └─containers │ │
│ └──────────────┘ └──────────────┘ └──────────────┘ │
│ │
│ Pros: Full isolation, RBAC per database │
│ Cons: Higher cost, more management overhead │
└────────────────────────────────────────────────────────────┘
Multi-Layer Isolation:
┌────────────────────────────────────────────────────────────┐
│ Layer 1: Network (outer) │
│ - VNet isolation for Silo tenants │
│ - Private endpoints │
│ - Firewall rules │
├────────────────────────────────────────────────────────────┤
│ Layer 2: Database │
│ - RLS policies (SQL Server/PostgreSQL) │
│ - Partition isolation (Cosmos DB) │
│ - Connection-level authentication │
├────────────────────────────────────────────────────────────┤
│ Layer 3: Application │
│ - EF Core global query filters │
│ - Tenant context validation middleware │
│ - Business logic checks │
├────────────────────────────────────────────────────────────┤
│ Layer 4: API (inner) │
│ - Request-level tenant validation │
│ - Authorization checks │
│ - Audit logging │
└────────────────────────────────────────────────────────────┘
Principle: No single layer failure should expose data
Tenant Count and Requirements → Pattern:
< 100 tenants + Compliance needed → Database-per-tenant
< 100 tenants + Cost-sensitive → Schema-per-tenant
100-10,000 tenants → Schema-per-tenant or RLS
> 10,000 tenants → RLS (mandatory)
Customization needed?
- None: RLS
- Some: Schema-per-tenant
- Full: Database-per-tenant
Compliance level?
- SOC 2/GDPR basic: RLS with audit logging
- HIPAA/PCI: Database-per-tenant minimum
- Government/Financial: Database-per-tenant + network isolation
| Anti-Pattern | Problem | Solution |
|---|---|---|
| No TenantId index | Slow queries, table scans | Add composite indexes |
| Trusting client TenantId | Data leakage | Server-side validation |
| Missing RLS on some tables | Partial isolation | Apply to ALL tenant tables |
| Hard-coded connection strings | No per-tenant isolation | Dynamic resolution |
| Ignoring query filters in admin | Admin bypass leaks data | Audit admin operations |
tenancy-models - Pool/Silo/Bridge architecture patternstenant-context-propagation - Passing context across servicesnoisy-neighbor-prevention - Resource isolation techniquesreferences/row-level-security.md for detailed RLS implementationsreferences/schema-per-tenant.md for schema isolation patternsreferences/database-per-tenant.md for dedicated database patternsLast Updated: 2025-12-26
Use when working with Payload CMS projects (payload.config.ts, collections, fields, hooks, access control, Payload API). Use when debugging validation errors, security issues, relationship queries, transactions, or hook behavior.
Applies Anthropic's official brand colors and typography to any sort of artifact that may benefit from having Anthropic's look-and-feel. Use it when brand colors or style guidelines, visual formatting, or company design standards apply.
Creating algorithmic art using p5.js with seeded randomness and interactive parameter exploration. Use this when users request creating art using code, generative art, algorithmic art, flow fields, or particle systems. Create original algorithmic art rather than copying existing artists' work to avoid copyright violations.