Multi-Tenant Database Architecture Patterns for SaaS Platforms

Introduction: Multi-Tenancy in SaaS

Every SaaS platform faces a fundamental architectural decision: how do you isolate customer data while maintaining operational efficiency? The wrong choice can lead to data leaks, performance bottlenecks, or operational nightmares.

This article explores a production-tested multi-tenancy pattern that enforces tenant isolation at compile time while providing the flexibility needed for admin operations and cross-tenant analytics. After processing billions of multi-tenant queries, this architecture has maintained 100% tenant isolation with zero data leakage incidents.

Choosing a Multi-Tenancy Strategy

Three main approaches exist:

1. Database Per Tenant

Tenant A → Database A
Tenant B → Database B
Tenant C → Database C

Pros: Maximum isolation, easy backup/restore per tenant Cons: Expensive, connection pool exhaustion, difficult migrations

2. Schema Per Tenant

Shared Database
  → Schema A (Tenant A)
  → Schema B (Tenant B)
  → Schema C (Tenant C)

Pros: Good isolation, easier than separate databases Cons: Still expensive, schema explosion, complex migrations

3. Shared Database with Row-Level Isolation (Our Choice)

Shared Database
  → Users Table
    → Row: (id=1, tenant_id=A, ...)
    → Row: (id=2, tenant_id=B, ...)
    → Row: (id=3, tenant_id=A, ...)

Pros: Cost-effective, simple operations, easy migrations Cons: Requires discipline, potential for data leaks if done wrong

We chose #3 with compiler-enforced tenant boundaries to get the cost benefits without the safety risks.

Implementing Query Context Abstraction

The core pattern is a query context that carries tenant information:

sealed trait QueryContext {
  def tenantIdOpt: Option[Long]
  def shouldIncludeDeleted: Boolean
  def queryingService: QueryingService
  def accessLevel: AccessLevel
}

// Admin context: can query across tenants
case class AllScopedQueryContext(
  queryingService: QueryingService,
  accessLevel: AccessLevel = AccessLevel.SystemLevel,
  shouldIncludeDeleted: Boolean = false
) extends QueryContext {
  override def tenantIdOpt: Option[Long] = None
}

// Tenant context: restricted to single tenant
case class TenantScopedQueryContext(
  tenantId: Long,
  queryingService: QueryingService,
  accessLevel: AccessLevel = AccessLevel.UserLevel,
  shouldIncludeDeleted: Boolean = false
) extends QueryContext {
  override def tenantIdOpt: Option[Long] = Some(tenantId)
}

Access Levels

Different operations require different permission levels:

sealed trait AccessLevel {
  def level: Int
  def canAccessTenant(tenantId: Long, queryContext: QueryContext): Boolean
}

object AccessLevel {
  case object SystemLevel extends AccessLevel {
    override def level: Int = 100
    override def canAccessTenant(tenantId: Long, queryContext: QueryContext): Boolean = true
  }

  case object AdminLevel extends AccessLevel {
    override def level: Int = 50
    override def canAccessTenant(tenantId: Long, queryContext: QueryContext): Boolean =
      queryContext.tenantIdOpt.isEmpty || queryContext.tenantIdOpt.contains(tenantId)
  }

  case object UserLevel extends AccessLevel {
    override def level: Int = 10
    override def canAccessTenant(tenantId: Long, queryContext: QueryContext): Boolean =
      queryContext.tenantIdOpt.contains(tenantId)
  }
}

Querying Service Enum (Audit Trail)

Track which service is making queries:

sealed trait QueryingService {
  def serviceName: String
}

object QueryingService {
  case object WebApi extends QueryingService {
    override def serviceName: String = "web-api"
  }

  case object BackgroundWorker extends QueryingService {
    override def serviceName: String = "background-worker"
  }

  case object MigrationScript extends QueryingService {
    override def serviceName: String = "migration"
  }

  case object AdminTool extends QueryingService {
    override def serviceName: String = "admin-tool"
  }
}

Enforcing Tenant Boundaries at Compile Time

Model Hierarchy

Define base traits that enforce multi-tenancy:

// Base model for all entities
abstract class BaseModel {
  def id: String
  def createdAt: DateTime
  def updatedAt: DateTime
}

// Models that belong to a tenant
abstract class TenantScopedModel extends BaseModel {
  def tenantId: Long
}

// Models that optionally belong to a tenant (e.g., system-wide settings)
abstract class OptionalTenantScopedModel extends BaseModel {
  def tenantIdOpt: Option[Long]
}

// Global models (no tenant association)
abstract class GlobalModel extends BaseModel

Repository Pattern with Context

Repositories require query context for all operations:

abstract class ReadRepository[M <: BaseModel](collection: MongoCollection[M]) {

  // CRITICAL: All query methods require QueryContext
  def where[T](
    field: Field[M, T],
    operator: Operator,
    value: T
  )(implicit queryContext: QueryContext): ReadQuery[M]

  def findById(id: String)(implicit queryContext: QueryContext): Future[Option[M]] = {
    applyQueryContext(
      new ReadQuery(collection).where(BaseModel.IdField, Operator.Equal, id)
    ).fetchOne
  }

  def findAll(implicit queryContext: QueryContext): Future[Seq[M]] = {
    applyQueryContext(new ReadQuery(collection)).fetchList
  }

  // Apply tenant filtering based on model type and context
  protected def applyQueryContext(query: ReadQuery[M])(
    implicit queryContext: QueryContext
  ): ReadQuery[M] = {
    // Implement in subclasses based on model type
    query
  }
}

Tenant-Scoped Repository

class TenantScopedRepository[M <: TenantScopedModel](
  collection: MongoCollection[M]
) extends ReadRepository[M](collection) {

  override protected def applyQueryContext(query: ReadQuery[M])(
    implicit queryContext: QueryContext
  ): ReadQuery[M] = {
    queryContext.tenantIdOpt match {
      case Some(tenantId) =>
        // User/service scoped to specific tenant
        query.where(TenantScopedModel.TenantIdField, Operator.Equal, tenantId)

      case None if queryContext.accessLevel == AccessLevel.SystemLevel =>
        // System-level access: no tenant filter (admin tools, analytics)
        query

      case None =>
        // Safety: Non-system access without tenant ID is forbidden
        throw new SecurityException(
          "Tenant-scoped query requires either tenantId or SystemLevel access"
        )
    }
  }
}

Usage Examples

// User request: automatically scoped to their tenant
def getUserOrders(userId: String)(implicit userContext: TenantScopedQueryContext): Future[Seq[Order]] = {
  // tenantId automatically added to query
  orderRepository
    .where(Order.UserIdField, Operator.Equal, userId)
    .fetchList
}

// Admin request: requires explicit admin context
def getAllTenantsOrderCount()(implicit adminContext: AllScopedQueryContext): Future[Map[Long, Long]] = {
  // No tenant filter applied
  orderRepository
    .aggregate
    .groupBy(Order.TenantIdField, "count" -> sumAccumulator(1))
    .execute[TenantOrderCount]
    .map(_.map(t => t.tenantId -> t.count).toMap)
}

// Attempting user query without context: COMPILE ERROR
def brokenQuery(userId: String): Future[Seq[Order]] = {
  orderRepository
    .where(Order.UserIdField, Operator.Equal, userId)
    .fetchList  // ❌ Compile error: missing implicit QueryContext
}

Handling Cross-Tenant Operations

Support/Admin Access Patterns

Support teams need to access any tenant’s data:

case class SupportContext(
  supportUserId: String,
  targetTenantId: Long,
  reason: String  // Audit trail
) extends QueryContext {
  override def tenantIdOpt: Option[Long] = Some(targetTenantId)
  override def queryingService: QueryingService = QueryingService.AdminTool
  override def accessLevel: AccessLevel = AccessLevel.AdminLevel
  override def shouldIncludeDeleted: Boolean = true  // Support can see deleted records
}

// Usage
def supportViewUserOrders(tenantId: Long, userId: String, supportUserId: String): Future[Seq[Order]] = {
  implicit val ctx: SupportContext = SupportContext(
    supportUserId = supportUserId,
    targetTenantId = tenantId,
    reason = s"Support ticket #12345"
  )

  // Audit log entry created automatically
  auditLogger.log(ctx, "Viewed user orders", Map("userId" -> userId))

  orderRepository
    .where(Order.UserIdField, Operator.Equal, userId)
    .fetchList
}

Cross-Tenant Analytics

Analytics queries need unrestricted access:

def dailyActiveUsers(date: DateTime): Future[Map[Long, Int]] = {
  implicit val ctx: AllScopedQueryContext = AllScopedQueryContext(
    queryingService = QueryingService.BackgroundWorker,
    accessLevel = AccessLevel.SystemLevel
  )

  userRepository
    .aggregate
    .matchStage(BsonDocument("lastActiveAt" -> BsonDocument("$gte" -> date.toBson)))
    .groupBy(User.TenantIdField, "count" -> sumAccumulator(1))
    .execute[TenantUserCount]
    .map(_.map(t => t.tenantId -> t.count).toMap)
}

Batch Operations Across Tenants

Background jobs processing all tenants:

def sendMonthlyReports(): Future[Unit] = {
  implicit val ctx: AllScopedQueryContext = AllScopedQueryContext(
    queryingService = QueryingService.BackgroundWorker,
    accessLevel = AccessLevel.SystemLevel
  )

  for {
    allTenants <- tenantRepository.findAll

    _ <- FutureUtil.groupedSequence(allTenants, concurrentPoolSize = 10) { tenant =>
      // Each tenant processed with its own context
      implicit val tenantCtx: TenantScopedQueryContext = TenantScopedQueryContext(
        tenantId = tenant.id,
        queryingService = QueryingService.BackgroundWorker
      )

      generateAndSendReport(tenant.id)
    }
  } yield ()
}

Performance and Scalability

Index Strategy

Proper indexing is critical for multi-tenant queries:

-- CRITICAL: Compound index with tenant_id first
CREATE INDEX idx_orders_tenant_user ON orders(tenant_id, user_id);
CREATE INDEX idx_orders_tenant_created ON orders(tenant_id, created_at);

-- ❌ BAD: These won't be used efficiently for tenant-scoped queries
CREATE INDEX idx_orders_user ON orders(user_id);  -- Missing tenant_id
CREATE INDEX idx_orders_created ON orders(created_at);  -- Missing tenant_id

MongoDB example:

// Create compound indexes with tenantId first
collection.createIndex(
  BsonDocument("tenant_id" -> 1, "user_id" -> 1)
)

collection.createIndex(
  BsonDocument("tenant_id" -> 1, "created_at" -> -1)
)

// Cover query optimization
collection.createIndex(
  BsonDocument(
    "tenant_id" -> 1,
    "status" -> 1,
    "created_at" -> -1
  )
)

Query Performance Monitoring

Track query performance per tenant:

trait QueryMetrics {
  def recordQuery(
    tenantIdOpt: Option[Long],
    operation: String,
    durationMs: Long,
    rowCount: Int
  ): Unit
}

class MetricsCollectingRepository[M](
  underlying: ReadRepository[M],
  metrics: QueryMetrics
) extends ReadRepository[M] {

  override def findById(id: String)(implicit queryContext: QueryContext): Future[Option[M]] = {
    val start = System.currentTimeMillis()

    underlying.findById(id).map { result =>
      val duration = System.currentTimeMillis() - start
      metrics.recordQuery(
        queryContext.tenantIdOpt,
        "findById",
        duration,
        if (result.isDefined) 1 else 0
      )
      result
    }
  }
}

Database Partitioning

For very large tenants, consider partitioning:

-- PostgreSQL partitioning by tenant_id range
CREATE TABLE orders (
  id BIGSERIAL,
  tenant_id BIGINT NOT NULL,
  user_id BIGINT,
  ...
) PARTITION BY RANGE (tenant_id);

CREATE TABLE orders_0_1000 PARTITION OF orders
  FOR VALUES FROM (0) TO (1000);

CREATE TABLE orders_1000_2000 PARTITION OF orders
  FOR VALUES FROM (1000) TO (2000);

Security Considerations

Preventing Tenant Leakage

Rule 1: Never trust client-provided tenant IDs

// ❌ BAD: Trusting client input
def getOrders(request: Request): Future[Seq[Order]] = {
  val tenantId = request.body.as[GetOrdersRequest].tenantId  // ❌ Dangerous!
  implicit val ctx: TenantScopedQueryContext = TenantScopedQueryContext(tenantId, ...)
  orderRepository.findAll
}

// ✅ GOOD: Derive tenant from authenticated session
def getOrders(request: AuthenticatedRequest): Future[Seq[Order]] = {
  val tenantId = request.user.tenantId  // ✅ From session, not client
  implicit val ctx: TenantScopedQueryContext = TenantScopedQueryContext(tenantId, ...)
  orderRepository.findAll
}

Rule 2: Audit all AllScopedQueryContext usage

object QueryContextFactory {
  def createAllScopedContext(
    service: QueryingService,
    reason: String
  )(implicit requestContext: RequestContext): AllScopedQueryContext = {
    // Log every all-scoped context creation
    auditLogger.warn(
      s"AllScopedQueryContext created",
      Map(
        "service" -> service.serviceName,
        "reason" -> reason,
        "userId" -> requestContext.userId,
        "ip" -> requestContext.ipAddress
      )
    )

    AllScopedQueryContext(service, AccessLevel.SystemLevel)
  }
}

Row-Level Security in PostgreSQL

Use PostgreSQL’s built-in RLS:

-- Enable RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy for tenant isolation
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant_id')::bigint);

-- Set tenant in session
SET app.current_tenant_id = 123;

-- Now all queries automatically filtered
SELECT * FROM orders;  -- Only returns orders for tenant 123

Testing Multi-Tenant Systems

Unit Tests with Mock Contexts

class OrderServiceSpec extends AnyFlatSpec {
  "OrderService" should "only return orders for specified tenant" in {
    implicit val ctx: TenantScopedQueryContext = TenantScopedQueryContext(
      tenantId = 123L,
      queryingService = QueryingService.WebApi
    )

    val orders = Await.result(orderService.getUserOrders("user-456"), 5.seconds)

    // Verify all returned orders belong to tenant 123
    orders.foreach { order =>
      assert(order.tenantId == 123L)
    }
  }

  it should "prevent access without tenant context" in {
    assertDoesNotCompile("""
      orderService.getUserOrders("user-456")
    """)  // Missing implicit context
  }
}

Integration Tests for Isolation

class TenantIsolationSpec extends AnyFlatSpec {
  "System" should "enforce tenant isolation" in {
    // Create test data for two tenants
    val tenant1Order = createOrder(tenantId = 1L, userId = "user-a")
    val tenant2Order = createOrder(tenantId = 2L, userId = "user-b")

    // Tenant 1 context
    implicit val ctx1: TenantScopedQueryContext = TenantScopedQueryContext(1L, QueryingService.WebApi)
    val tenant1Results = Await.result(orderRepository.findAll, 5.seconds)

    assert(tenant1Results.contains(tenant1Order))
    assert(!tenant1Results.contains(tenant2Order))  // MUST NOT see tenant 2 data

    // Tenant 2 context
    implicit val ctx2: TenantScopedQueryContext = TenantScopedQueryContext(2L, QueryingService.WebApi)
    val tenant2Results = Await.result(orderRepository.findAll, 5.seconds)

    assert(tenant2Results.contains(tenant2Order))
    assert(!tenant2Results.contains(tenant1Order))  // MUST NOT see tenant 1 data
  }
}

When to Use This Pattern

✅ Use When:

• Building multi-tenant SaaS with cost efficiency goals
• Need compile-time safety for tenant isolation
• Tens to thousands of tenants (not millions)
• Tenants have similar resource usage patterns
• Admin/support needs cross-tenant access

❌ Avoid When:

• Regulatory requirements mandate physical isolation
• Tenants have wildly different sizes (1 tenant = 90% of data)
• Building single-tenant software
• Database doesn’t support efficient compound indexes
• Team cannot maintain discipline around query contexts

Conclusion

Multi-tenant database architecture requires careful balance between cost efficiency and data isolation. The query context pattern provides compile-time safety while maintaining operational flexibility:

1. Compiler-enforced isolation - Impossible to query without context
2. Flexible access control - Support, admin, analytics all handled
3. Audit trails - Every query knows its originating service
4. Type safety - Wrong context types caught at compile time
5. Performance - Proper indexing keeps queries fast

This architecture has served production systems with thousands of tenants, billions of records, and zero tenant data leakage incidents. The initial investment in type-safe abstractions pays dividends through reduced bugs, faster feature development, and confident scaling.

The key insight: tenant isolation is too important to leave to runtime checks. Make the compiler enforce it.

Further Reading

• AWS Multi-Tenant SaaS Architecture
• PostgreSQL Row-Level Security
• MongoDB Multi-Tenancy Patterns
• OWASP Multi-Tenancy Cheat Sheet