Chapter 06: Multi-Tenancy Design
Schema-Per-Tenant Architecture
This chapter details the multi-tenancy strategy for the POS Platform. We use schema-per-tenant isolation - the gold standard for enterprise SaaS applications requiring strong data isolation.
Multi-Tenancy Strategies Comparison
Before diving into our chosen approach, let’s understand the alternatives:
Multi-Tenancy Strategies
========================
Strategy 1: Shared Tables (Row-Level)
+----------------------------------+
| products |
| +--------+--------+------------+ |
| | tenant | id | name | |
| +--------+--------+------------+ |
| | nexus | 1 | T-Shirt | |
| | acme | 2 | Jacket | |
| | nexus | 3 | Jeans | |
| +--------+--------+------------+ |
+----------------------------------+
Pros: Simple, low overhead
Cons: Risk of data leakage, complex queries, no isolation
Strategy 2: Separate Databases
+-------------+ +-------------+ +-------------+
| nexus_db | | acme_db | | beta_db |
| +--------+ | | +--------+ | | +--------+ |
| |products| | | |products| | | |products| |
| +--------+ | | +--------+ | | +--------+ |
| |sales | | | |sales | | | |sales | |
| +--------+ | | +--------+ | | +--------+ |
+-------------+ +-------------+ +-------------+
Pros: Complete isolation
Cons: Connection overhead, backup complexity, cost at scale
Strategy 3: Schema-Per-Tenant [CHOSEN]
+-----------------------------------------------------+
| pos_platform database |
| |
| +-----------+ +--------------+ +--------------+ |
| | shared | | tenant_nexus | | tenant_acme | |
| +-----------+ +--------------+ +--------------+ |
| | tenants | | products | | products | |
| | plans | | sales | | sales | |
| | features | | inventory | | inventory | |
| +-----------+ | customers | | customers | |
| +--------------+ +--------------+ |
+-----------------------------------------------------+
Pros: Isolation + efficiency, easy backup/restore per tenant
Cons: More complex migrations (but manageable)
Why Schema-Per-Tenant?
Decision Matrix
| Requirement | Shared Tables | Separate DBs | Schema-Per-Tenant |
|---|---|---|---|
| Data Isolation | Poor | Excellent | Excellent |
| Performance | Good | Excellent | Very Good |
| Backup/Restore | Complex | Simple | Simple |
| Connection Overhead | Low | High | Low |
| Query Complexity | High | Low | Low |
| Compliance (SOC2) | Difficult | Easy | Easy |
| Cost at Scale | Low | High | Medium |
| Migration Complexity | Low | Low | Medium |
Our Choice: Schema-Per-Tenant because:
- Strong isolation for compliance and trust
- Easy per-tenant backup and restore
- Single database connection per API server
- Clean data model without tenant_id on every table
Database Structure
The Platform Database
Database: pos_platform
======================
+-- Schema: shared (Platform-wide)
| +-- tenants (tenant registry)
| +-- subscription_plans (pricing plans)
| +-- feature_flags (feature toggles)
| +-- platform_settings (global config)
| +-- api_keys (external integrations)
|
+-- Schema: tenant_nexus (Nexus Clothing tenant)
| +-- products
| +-- product_variants
| +-- categories
| +-- sales
| +-- sale_line_items
| +-- payments
| +-- inventory_items
| +-- stock_levels
| +-- customers
| +-- employees
| +-- locations
| +-- (... all tenant tables)
|
+-- Schema: tenant_acme (Acme Retail tenant)
| +-- (same structure as tenant_nexus)
|
+-- Schema: tenant_beta (Beta Store tenant)
+-- (same structure as tenant_nexus)
Shared Schema Tables
The shared schema contains platform-level data accessible to all tenants:
-- Schema: shared
-- Tenant Registry
CREATE TABLE shared.tenants (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
slug VARCHAR(50) UNIQUE NOT NULL, -- 'nexus', 'acme'
name VARCHAR(255) NOT NULL, -- 'Nexus Clothing'
subdomain VARCHAR(100) UNIQUE NOT NULL, -- 'nexus.pos-platform.com'
schema_name VARCHAR(100) NOT NULL, -- 'tenant_nexus'
plan_id UUID REFERENCES shared.subscription_plans(id),
status VARCHAR(20) DEFAULT 'active', -- active, suspended, trial
trial_ends_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Subscription Plans
CREATE TABLE shared.subscription_plans (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(100) NOT NULL, -- 'Starter', 'Professional'
code VARCHAR(50) UNIQUE NOT NULL, -- 'starter', 'pro', 'enterprise'
price_monthly DECIMAL(10,2),
price_yearly DECIMAL(10,2),
max_locations INTEGER DEFAULT 1,
max_registers INTEGER DEFAULT 2,
max_employees INTEGER DEFAULT 5,
max_products INTEGER DEFAULT 1000,
features JSONB DEFAULT '{}', -- Feature flags
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Feature Flags
CREATE TABLE shared.feature_flags (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
key VARCHAR(100) UNIQUE NOT NULL, -- 'loyalty_program'
name VARCHAR(255) NOT NULL,
description TEXT,
default_enabled BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Platform Settings
CREATE TABLE shared.platform_settings (
key VARCHAR(100) PRIMARY KEY,
value JSONB NOT NULL,
description TEXT,
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Insert default plans
INSERT INTO shared.subscription_plans (name, code, price_monthly, max_locations, max_registers, max_employees, max_products) VALUES
('Starter', 'starter', 49.00, 1, 2, 5, 1000),
('Professional', 'pro', 149.00, 3, 10, 25, 10000),
('Enterprise', 'enterprise', 499.00, -1, -1, -1, -1); -- -1 = unlimited
Tenant Schema Template
Each tenant gets an identical schema structure:
-- Template for creating a new tenant schema
-- Replace {tenant_slug} with actual tenant slug (e.g., 'nexus')
CREATE SCHEMA tenant_{tenant_slug};
-- Set search path for this session
SET search_path TO tenant_{tenant_slug};
-- Core lookup tables
CREATE TABLE locations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
code VARCHAR(10) UNIQUE NOT NULL,
name VARCHAR(255) NOT NULL,
address_line1 VARCHAR(255),
address_line2 VARCHAR(255),
city VARCHAR(100),
state VARCHAR(50),
postal_code VARCHAR(20),
country VARCHAR(2) DEFAULT 'US',
phone VARCHAR(20),
email VARCHAR(255),
timezone VARCHAR(50) DEFAULT 'America/New_York',
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE employees (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
employee_number VARCHAR(20) UNIQUE,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE,
phone VARCHAR(20),
pin_hash VARCHAR(255), -- Hashed PIN for clock-in
role VARCHAR(50) NOT NULL, -- 'admin', 'manager', 'cashier'
home_location_id UUID REFERENCES locations(id),
hourly_rate DECIMAL(10,2),
is_active BOOLEAN DEFAULT TRUE,
last_login_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE categories (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name VARCHAR(255) NOT NULL,
slug VARCHAR(255) UNIQUE NOT NULL,
parent_id UUID REFERENCES categories(id),
sort_order INTEGER DEFAULT 0,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE products (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
sku VARCHAR(50) UNIQUE NOT NULL,
barcode VARCHAR(50),
name VARCHAR(255) NOT NULL,
description TEXT,
category_id UUID REFERENCES categories(id),
brand VARCHAR(100),
vendor VARCHAR(100),
cost DECIMAL(10,2) DEFAULT 0,
price DECIMAL(10,2) NOT NULL,
compare_at_price DECIMAL(10,2),
tax_code VARCHAR(20),
is_taxable BOOLEAN DEFAULT TRUE,
track_inventory BOOLEAN DEFAULT TRUE,
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE product_variants (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
sku VARCHAR(50) UNIQUE NOT NULL,
barcode VARCHAR(50),
name VARCHAR(255) NOT NULL, -- e.g., 'Large / Blue'
option1_name VARCHAR(50), -- 'Size'
option1_value VARCHAR(100), -- 'Large'
option2_name VARCHAR(50), -- 'Color'
option2_value VARCHAR(100), -- 'Blue'
option3_name VARCHAR(50),
option3_value VARCHAR(100),
cost DECIMAL(10,2),
price DECIMAL(10,2),
weight DECIMAL(10,2),
is_active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE inventory_items (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
product_id UUID REFERENCES products(id),
variant_id UUID REFERENCES product_variants(id),
location_id UUID NOT NULL REFERENCES locations(id),
quantity_on_hand INTEGER DEFAULT 0,
quantity_committed INTEGER DEFAULT 0, -- Reserved for orders
quantity_available INTEGER GENERATED ALWAYS AS (quantity_on_hand - quantity_committed) STORED,
reorder_point INTEGER DEFAULT 0,
reorder_quantity INTEGER DEFAULT 0,
last_counted_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
UNIQUE (product_id, variant_id, location_id)
);
CREATE TABLE customers (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
customer_number VARCHAR(20) UNIQUE,
first_name VARCHAR(100),
last_name VARCHAR(100),
email VARCHAR(255) UNIQUE,
phone VARCHAR(20),
company VARCHAR(255),
tax_exempt BOOLEAN DEFAULT FALSE,
tax_exempt_id VARCHAR(50),
notes TEXT,
loyalty_points INTEGER DEFAULT 0,
total_spent DECIMAL(12,2) DEFAULT 0,
visit_count INTEGER DEFAULT 0,
last_visit_at TIMESTAMPTZ,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE sales (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
sale_number VARCHAR(50) UNIQUE NOT NULL,
location_id UUID NOT NULL REFERENCES locations(id),
register_id VARCHAR(20),
employee_id UUID REFERENCES employees(id),
customer_id UUID REFERENCES customers(id),
status VARCHAR(20) DEFAULT 'completed', -- draft, completed, voided, refunded
subtotal DECIMAL(12,2) NOT NULL,
discount_total DECIMAL(12,2) DEFAULT 0,
tax_total DECIMAL(12,2) DEFAULT 0,
total DECIMAL(12,2) NOT NULL,
payment_status VARCHAR(20) DEFAULT 'paid', -- pending, partial, paid, refunded
notes TEXT,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE sale_line_items (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
sale_id UUID NOT NULL REFERENCES sales(id) ON DELETE CASCADE,
product_id UUID REFERENCES products(id),
variant_id UUID REFERENCES product_variants(id),
sku VARCHAR(50) NOT NULL,
name VARCHAR(255) NOT NULL,
quantity INTEGER NOT NULL,
unit_price DECIMAL(10,2) NOT NULL,
discount_amount DECIMAL(10,2) DEFAULT 0,
tax_amount DECIMAL(10,2) DEFAULT 0,
total DECIMAL(12,2) NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE payments (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
sale_id UUID NOT NULL REFERENCES sales(id) ON DELETE CASCADE,
payment_method VARCHAR(50) NOT NULL, -- cash, credit, debit, gift_card
amount DECIMAL(12,2) NOT NULL,
reference VARCHAR(100), -- Card last 4, check #, etc.
status VARCHAR(20) DEFAULT 'completed',
processor_response JSONB, -- Payment gateway response
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Additional tables: refunds, inventory_adjustments, etc.
-- (Full schema in Chapter 12)
Tenant Resolution Flow
How the system determines which tenant schema to use:
Tenant Resolution Flow
======================
+---------------------------+
| Incoming Request |
| nexus.pos-platform.com |
+-------------+-------------+
|
v
+---------------------------+
| Extract Subdomain |
| subdomain = "nexus" |
+-------------+-------------+
|
v
+---------------------------+
| Lookup in shared.tenants|
| WHERE subdomain = ? |
+-------------+-------------+
|
+----------------------+----------------------+
| |
[Found] [Not Found]
| |
v v
+---------------------------+ +---------------------------+
| Set PostgreSQL | | Return 404 |
| search_path TO | | "Tenant not found" |
| tenant_nexus, shared | +---------------------------+
+-------------+-------------+
|
v
+---------------------------+
| Continue with request |
| All queries now use |
| tenant_nexus schema |
+---------------------------+
Tenant Middleware Implementation
ASP.NET Core Middleware
// TenantMiddleware.cs
public class TenantMiddleware
{
private readonly RequestDelegate _next;
private readonly ILogger<TenantMiddleware> _logger;
public TenantMiddleware(RequestDelegate next, ILogger<TenantMiddleware> logger)
{
_next = next;
_logger = logger;
}
public async Task InvokeAsync(HttpContext context, ITenantService tenantService, IDbContextFactory<PosDbContext> dbFactory)
{
// 1. Extract subdomain from host
var host = context.Request.Host.Host;
var subdomain = ExtractSubdomain(host);
if (string.IsNullOrEmpty(subdomain))
{
context.Response.StatusCode = 400;
await context.Response.WriteAsJsonAsync(new { error = "Invalid tenant" });
return;
}
// 2. Lookup tenant in shared schema
var tenant = await tenantService.GetBySubdomainAsync(subdomain);
if (tenant == null)
{
context.Response.StatusCode = 404;
await context.Response.WriteAsJsonAsync(new { error = "Tenant not found" });
return;
}
if (tenant.Status == "suspended")
{
context.Response.StatusCode = 403;
await context.Response.WriteAsJsonAsync(new { error = "Account suspended" });
return;
}
// 3. Store tenant in HttpContext for downstream use
context.Items["Tenant"] = tenant;
context.Items["TenantSchema"] = tenant.SchemaName;
_logger.LogDebug("Resolved tenant: {TenantSlug} -> {Schema}", tenant.Slug, tenant.SchemaName);
// 4. Continue pipeline
await _next(context);
}
private string? ExtractSubdomain(string host)
{
// nexus.pos-platform.com -> nexus
// localhost:5000 -> null (development fallback)
var parts = host.Split('.');
if (parts.Length >= 3)
{
return parts[0];
}
// Development fallback: check header
return null;
}
}
// ITenantService.cs
public interface ITenantService
{
Task<Tenant?> GetBySubdomainAsync(string subdomain);
Task<Tenant?> GetBySlugAsync(string slug);
Task<string> CreateTenantAsync(CreateTenantRequest request);
}
// TenantService.cs
public class TenantService : ITenantService
{
private readonly IDbContextFactory<SharedDbContext> _dbFactory;
private readonly ILogger<TenantService> _logger;
public TenantService(IDbContextFactory<SharedDbContext> dbFactory, ILogger<TenantService> logger)
{
_dbFactory = dbFactory;
_logger = logger;
}
public async Task<Tenant?> GetBySubdomainAsync(string subdomain)
{
await using var db = await _dbFactory.CreateDbContextAsync();
return await db.Tenants
.AsNoTracking()
.FirstOrDefaultAsync(t => t.Subdomain == subdomain);
}
public async Task<string> CreateTenantAsync(CreateTenantRequest request)
{
var schemaName = $"tenant_{request.Slug}";
await using var db = await _dbFactory.CreateDbContextAsync();
// 1. Create tenant record
var tenant = new Tenant
{
Slug = request.Slug,
Name = request.Name,
Subdomain = request.Subdomain,
SchemaName = schemaName,
PlanId = request.PlanId,
Status = "active"
};
db.Tenants.Add(tenant);
await db.SaveChangesAsync();
// 2. Create schema (raw SQL)
await db.Database.ExecuteSqlRawAsync($"CREATE SCHEMA {schemaName}");
// 3. Run migrations on new schema
await RunMigrationsAsync(schemaName);
_logger.LogInformation("Created tenant: {Slug} with schema {Schema}", request.Slug, schemaName);
return tenant.Id.ToString();
}
private async Task RunMigrationsAsync(string schemaName)
{
// Apply all tenant schema tables
// This would run the full schema creation script from Chapter 12
}
}
DbContext with Dynamic Schema
// PosDbContext.cs
public class PosDbContext : DbContext
{
private readonly string _schemaName;
public PosDbContext(DbContextOptions<PosDbContext> options, IHttpContextAccessor httpContextAccessor)
: base(options)
{
// Get schema from HttpContext (set by TenantMiddleware)
_schemaName = httpContextAccessor.HttpContext?.Items["TenantSchema"]?.ToString()
?? "tenant_default";
}
public DbSet<Product> Products => Set<Product>();
public DbSet<Sale> Sales => Set<Sale>();
public DbSet<Customer> Customers => Set<Customer>();
public DbSet<Employee> Employees => Set<Employee>();
public DbSet<Location> Locations => Set<Location>();
// ... other DbSets
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
// Set default schema for all entities
modelBuilder.HasDefaultSchema(_schemaName);
// Apply entity configurations
modelBuilder.ApplyConfigurationsFromAssembly(typeof(PosDbContext).Assembly);
}
}
Connection String with search_path
Alternative approach using connection string:
// TenantDbContextFactory.cs
public class TenantDbContextFactory : IDbContextFactory<PosDbContext>
{
private readonly IConfiguration _config;
private readonly IHttpContextAccessor _httpContextAccessor;
public TenantDbContextFactory(IConfiguration config, IHttpContextAccessor httpContextAccessor)
{
_config = config;
_httpContextAccessor = httpContextAccessor;
}
public PosDbContext CreateDbContext()
{
var schemaName = _httpContextAccessor.HttpContext?.Items["TenantSchema"]?.ToString()
?? throw new InvalidOperationException("No tenant context");
var baseConnectionString = _config.GetConnectionString("DefaultConnection");
// Append search_path to connection string
var connectionString = $"{baseConnectionString};Search Path={schemaName},shared";
var optionsBuilder = new DbContextOptionsBuilder<PosDbContext>();
optionsBuilder.UseNpgsql(connectionString);
return new PosDbContext(optionsBuilder.Options);
}
}
Tenant Provisioning Workflow
New Tenant Signup Flow
======================
[Admin Portal] [API] [Database]
| | |
| 1. POST /tenants | |
| { name, slug, plan } | |
|------------------------------>| |
| | |
| | 2. Validate slug uniqueness |
| |--------------------------------->|
| | |
| | 3. Insert into shared.tenants |
| |--------------------------------->|
| | |
| | 4. CREATE SCHEMA tenant_{slug} |
| |--------------------------------->|
| | |
| | 5. Run schema migrations |
| | (create all tables) |
| |--------------------------------->|
| | |
| | 6. Seed default data |
| | (roles, permissions) |
| |--------------------------------->|
| | |
| | 7. Create admin user |
| |--------------------------------->|
| | |
| 8. Return tenant details | |
| { id, subdomain, status } | |
|<------------------------------| |
| | |
| 9. Redirect to tenant portal | |
| nexus.pos-platform.com | |
| | |
Tenant Isolation Benefits
1. Data Isolation
Tenant A cannot access Tenant B's data
======================================
-- Query from Tenant A's context (search_path = tenant_nexus)
SELECT * FROM products;
-- Returns only Nexus products
-- Even if someone tries:
SELECT * FROM tenant_acme.products;
-- ERROR: permission denied for schema tenant_acme
2. Easy Backup/Restore
# Backup single tenant
pg_dump -h localhost -U postgres -n tenant_nexus pos_platform > nexus_backup.sql
# Restore tenant to new database
psql -h localhost -U postgres -d pos_platform_restore < nexus_backup.sql
# Clone tenant for testing
pg_dump -h localhost -U postgres -n tenant_nexus pos_platform | \
sed 's/tenant_nexus/tenant_nexus_test/g' | \
psql -h localhost -U postgres -d pos_platform
3. Per-Tenant Maintenance
-- Vacuum single tenant
VACUUM ANALYZE tenant_nexus.products;
VACUUM ANALYZE tenant_nexus.sales;
-- Reindex single tenant
REINDEX SCHEMA tenant_nexus;
-- Drop tenant (complete removal)
DROP SCHEMA tenant_nexus CASCADE;
DELETE FROM shared.tenants WHERE slug = 'nexus';
4. Compliance
SOC 2 / GDPR Compliance
=======================
Requirement: "Customer data must be logically separated"
With schema-per-tenant:
- Each customer's data in isolated schema
- No risk of WHERE clause forgetting tenant_id
- Clear audit trail per schema
- Easy data export for GDPR requests
- Simple data deletion for "right to be forgotten"
Performance Considerations
Connection Pooling
Connection Pool Strategy
========================
+------------------+
| Connection Pool |
| (PgBouncer) |
+--------+---------+
|
+--------------------+--------------------+
| | |
v v v
+-------+-------+ +--------+------+ +---------+-----+
| Connection 1 | | Connection 2 | | Connection 3 |
| search_path: | | search_path: | | search_path: |
| tenant_nexus | | tenant_acme | | tenant_nexus |
+---------------+ +---------------+ +---------------+
Note: search_path is set per-connection, not per-query.
Use transaction pooling mode in PgBouncer.
Query Performance
-- Index per schema (automatically namespaced)
CREATE INDEX idx_products_sku ON tenant_nexus.products(sku);
CREATE INDEX idx_products_sku ON tenant_acme.products(sku);
-- No tenant_id in WHERE clause needed
-- Simpler, faster queries:
SELECT * FROM products WHERE sku = 'NXP0001';
-- vs (row-level tenancy):
SELECT * FROM products WHERE tenant_id = ? AND sku = 'NXP0001';
Migration Strategy
Applying Migrations to All Tenants
// TenantMigrationService.cs
public class TenantMigrationService
{
private readonly SharedDbContext _sharedDb;
private readonly ILogger<TenantMigrationService> _logger;
public async Task ApplyMigrationToAllTenantsAsync(string migrationScript)
{
var tenants = await _sharedDb.Tenants.ToListAsync();
foreach (var tenant in tenants)
{
try
{
_logger.LogInformation("Applying migration to {Schema}", tenant.SchemaName);
await _sharedDb.Database.ExecuteSqlRawAsync(
$"SET search_path TO {tenant.SchemaName}; {migrationScript}"
);
_logger.LogInformation("Migration complete for {Schema}", tenant.SchemaName);
}
catch (Exception ex)
{
_logger.LogError(ex, "Migration failed for {Schema}", tenant.SchemaName);
// Continue with other tenants or abort based on policy
}
}
}
}
Migration Script Example
-- Migration: Add loyalty_tier to customers
-- File: 2025-01-15_add_loyalty_tier.sql
DO $$
DECLARE
tenant_schema TEXT;
BEGIN
FOR tenant_schema IN
SELECT schema_name FROM shared.tenants WHERE status = 'active'
LOOP
EXECUTE format('ALTER TABLE %I.customers ADD COLUMN IF NOT EXISTS loyalty_tier VARCHAR(20) DEFAULT ''bronze''', tenant_schema);
END LOOP;
END $$;
Summary
The schema-per-tenant architecture provides:
- Strong isolation - Complete data separation without row-level complexity
- Simple queries - No tenant_id required in every WHERE clause
- Easy operations - Per-tenant backup, restore, and maintenance
- Compliance ready - Clear boundaries for SOC 2, GDPR, HIPAA
- Scalable - PostgreSQL handles thousands of schemas efficiently
The tenant middleware automatically resolves the correct schema for every request, making multi-tenancy transparent to the application code.
Next: Chapter 07: Domain Model