Case Study 1: Designing a Multi-Tenant Database

Overview

SyncBoard is a project management SaaS application serving multiple organizations (tenants). Each tenant has its own users, projects, tasks, and data -- but all tenants share the same database infrastructure. The challenge is designing a schema that keeps tenant data isolated, performs well at scale, and supports the inevitable growth from 10 tenants to 10,000.

This case study walks through the design decisions, trade-offs, and implementation of a multi-tenant database schema using PostgreSQL and SQLAlchemy.

The Business Requirements

SyncBoard must support the following:

Organizations (tenants): Each organization signs up independently and manages its own workspace.
Users: Belong to one or more organizations. Have roles within each organization (admin, manager, member).
Projects: Belong to one organization. Have a name, description, status, and due date.
Tasks: Belong to a project. Assigned to users. Have status, priority, due dates, and descriptions.
Comments: Attached to tasks. Written by users. Contain text and timestamps.
Audit log: Track who changed what and when, for compliance purposes.

Non-functional requirements: - Tenant data must be completely isolated -- Organization A must never see Organization B's data. - The system must support 10,000 organizations with 100 users each. - Common queries (list tasks for a project, list projects for an organization) must execute in under 50ms.

Multi-Tenancy Strategy Selection

There are three common approaches to multi-tenancy in databases:

Option 1: Separate Database Per Tenant

Each tenant gets its own database. Maximum isolation, but operationally expensive. Managing migrations across 10,000 databases is a nightmare.

Verdict: Too expensive for a startup. Reserved for industries with extreme regulatory requirements (healthcare, finance).

Option 2: Shared Database, Separate Schemas

Each tenant gets a PostgreSQL schema within the same database. Good isolation with moderate operational overhead.

Verdict: A reasonable middle ground, but schema management across thousands of schemas adds complexity.

Option 3: Shared Database, Shared Schema

All tenants share the same tables. Each row has a tenant_id column that identifies its owner. Simplest to manage, but requires careful application logic to enforce isolation.

Verdict: The best fit for SyncBoard. The most common approach for SaaS applications. We will use this with robust safeguards.

Schema Design

The Tenant Table

from datetime import datetime
from sqlalchemy import String, Text, ForeignKey, Index, CheckConstraint, func
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship


class Base(DeclarativeBase):
    pass


class TimestampMixin:
    """Adds created_at and updated_at to all models."""
    created_at: Mapped[datetime] = mapped_column(server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(
        server_default=func.now(), onupdate=func.now()
    )


class TenantMixin:
    """Adds tenant_id to all tenant-scoped models."""
    tenant_id: Mapped[int] = mapped_column(
        ForeignKey("tenants.id", ondelete="CASCADE"),
        index=True,
    )


class Tenant(TimestampMixin, Base):
    __tablename__ = "tenants"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    slug: Mapped[str] = mapped_column(String(100), unique=True)
    plan: Mapped[str] = mapped_column(String(20), server_default="free")
    is_active: Mapped[bool] = mapped_column(server_default="true")

    memberships: Mapped[list["TenantMembership"]] = relationship(
        back_populates="tenant", cascade="all, delete-orphan"
    )
    projects: Mapped[list["Project"]] = relationship(
        back_populates="tenant", cascade="all, delete-orphan"
    )

    def __repr__(self) -> str:
        return f"<Tenant(id={self.id}, slug='{self.slug}')>"

The TenantMixin is the critical piece. Every table that holds tenant-scoped data includes a tenant_id foreign key. This column appears on every query to ensure data isolation.

Users and Memberships

Users exist globally (they can belong to multiple tenants), but their membership and role are tenant-specific:

class User(TimestampMixin, Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(255), unique=True)
    username: Mapped[str] = mapped_column(String(50), unique=True)
    password_hash: Mapped[str] = mapped_column(String(255))

    memberships: Mapped[list["TenantMembership"]] = relationship(
        back_populates="user", cascade="all, delete-orphan"
    )

    def __repr__(self) -> str:
        return f"<User(id={self.id}, username='{self.username}')>"


class TenantMembership(TimestampMixin, Base):
    """Associates users with tenants and defines their role."""
    __tablename__ = "tenant_memberships"

    id: Mapped[int] = mapped_column(primary_key=True)
    tenant_id: Mapped[int] = mapped_column(
        ForeignKey("tenants.id", ondelete="CASCADE")
    )
    user_id: Mapped[int] = mapped_column(
        ForeignKey("users.id", ondelete="CASCADE")
    )
    role: Mapped[str] = mapped_column(String(20), server_default="member")

    tenant: Mapped["Tenant"] = relationship(back_populates="memberships")
    user: Mapped["User"] = relationship(back_populates="memberships")

    __table_args__ = (
        Index("ix_membership_tenant_user", "tenant_id", "user_id", unique=True),
        CheckConstraint(
            "role IN ('admin', 'manager', 'member')",
            name="valid_role",
        ),
    )

The unique constraint on (tenant_id, user_id) ensures a user can only have one membership (and thus one role) per tenant.

Projects and Tasks

class Project(TenantMixin, TimestampMixin, Base):
    __tablename__ = "projects"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(200))
    description: Mapped[str | None] = mapped_column(Text, default=None)
    status: Mapped[str] = mapped_column(String(20), server_default="active")
    owner_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    due_date: Mapped[datetime | None] = mapped_column(default=None)

    tenant: Mapped["Tenant"] = relationship(back_populates="projects")
    owner: Mapped["User"] = relationship()
    tasks: Mapped[list["Task"]] = relationship(
        back_populates="project", cascade="all, delete-orphan"
    )

    __table_args__ = (
        Index("ix_projects_tenant_status", "tenant_id", "status"),
        CheckConstraint(
            "status IN ('active', 'archived', 'completed')",
            name="valid_project_status",
        ),
    )

    def __repr__(self) -> str:
        return f"<Project(id={self.id}, name='{self.name}')>"


class Task(TenantMixin, TimestampMixin, Base):
    __tablename__ = "tasks"

    id: Mapped[int] = mapped_column(primary_key=True)
    project_id: Mapped[int] = mapped_column(
        ForeignKey("projects.id", ondelete="CASCADE")
    )
    assignee_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id"), default=None
    )
    title: Mapped[str] = mapped_column(String(200))
    description: Mapped[str | None] = mapped_column(Text, default=None)
    status: Mapped[str] = mapped_column(String(20), server_default="todo")
    priority: Mapped[int] = mapped_column(server_default="0")
    due_date: Mapped[datetime | None] = mapped_column(default=None)

    project: Mapped["Project"] = relationship(back_populates="tasks")
    assignee: Mapped["User | None"] = relationship()
    comments: Mapped[list["Comment"]] = relationship(
        back_populates="task", cascade="all, delete-orphan"
    )

    __table_args__ = (
        Index("ix_tasks_tenant_project", "tenant_id", "project_id"),
        Index("ix_tasks_tenant_assignee", "tenant_id", "assignee_id"),
        Index("ix_tasks_tenant_status", "tenant_id", "status"),
        CheckConstraint(
            "status IN ('todo', 'in_progress', 'in_review', 'done')",
            name="valid_task_status",
        ),
        CheckConstraint(
            "priority BETWEEN 0 AND 5",
            name="valid_priority",
        ),
    )

    def __repr__(self) -> str:
        return f"<Task(id={self.id}, title='{self.title}')>"


class Comment(TenantMixin, TimestampMixin, Base):
    __tablename__ = "comments"

    id: Mapped[int] = mapped_column(primary_key=True)
    task_id: Mapped[int] = mapped_column(
        ForeignKey("tasks.id", ondelete="CASCADE")
    )
    author_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    body: Mapped[str] = mapped_column(Text)

    task: Mapped["Task"] = relationship(back_populates="comments")
    author: Mapped["User"] = relationship()

    __table_args__ = (
        Index("ix_comments_tenant_task", "tenant_id", "task_id"),
    )

The Audit Log

For compliance, we track every change:

class AuditLog(Base):
    __tablename__ = "audit_log"

    id: Mapped[int] = mapped_column(primary_key=True)
    tenant_id: Mapped[int] = mapped_column(
        ForeignKey("tenants.id", ondelete="CASCADE"), index=True
    )
    user_id: Mapped[int | None] = mapped_column(
        ForeignKey("users.id", ondelete="SET NULL")
    )
    action: Mapped[str] = mapped_column(String(20))  # create, update, delete
    entity_type: Mapped[str] = mapped_column(String(50))  # project, task, etc.
    entity_id: Mapped[int] = mapped_column()
    changes: Mapped[str | None] = mapped_column(Text)  # JSON of changed fields
    timestamp: Mapped[datetime] = mapped_column(server_default=func.now())

    __table_args__ = (
        Index("ix_audit_tenant_entity", "tenant_id", "entity_type", "entity_id"),
        Index("ix_audit_tenant_timestamp", "tenant_id", "timestamp"),
    )

Enforcing Tenant Isolation

The most critical aspect of multi-tenant design is ensuring that queries always include the tenant_id filter. There are several strategies:

Strategy 1: Repository-Level Enforcement

Every repository method requires a tenant_id parameter:

class TaskRepository:
    def __init__(self, session: Session, tenant_id: int) -> None:
        self.session = session
        self.tenant_id = tenant_id

    def list_for_project(self, project_id: int) -> list[Task]:
        stmt = (
            select(Task)
            .where(Task.tenant_id == self.tenant_id)
            .where(Task.project_id == project_id)
            .order_by(Task.priority.desc())
        )
        return list(self.session.scalars(stmt).all())

    def get_by_id(self, task_id: int) -> Task | None:
        stmt = (
            select(Task)
            .where(Task.tenant_id == self.tenant_id)
            .where(Task.id == task_id)
        )
        return self.session.scalars(stmt).first()

Strategy 2: SQLAlchemy Event Listeners

Automatically add tenant filters to all queries:

from sqlalchemy import event

@event.listens_for(Session, "do_orm_execute")
def add_tenant_filter(execute_state):
    """Automatically filter queries by tenant_id."""
    if (
        execute_state.is_select
        and not execute_state.execution_options.get("skip_tenant_filter", False)
    ):
        tenant_id = execute_state.session.info.get("tenant_id")
        if tenant_id is not None:
            execute_state.statement = execute_state.statement.options(
                with_loader_criteria(
                    TenantMixin,
                    lambda cls: cls.tenant_id == tenant_id,
                    include_aliases=True,
                )
            )

This approach is more robust because it is impossible to accidentally forget the filter. However, it adds complexity and can be surprising for developers unfamiliar with the pattern.

Strategy 3: PostgreSQL Row-Level Security

PostgreSQL supports Row-Level Security (RLS), which enforces tenant isolation at the database level:

ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON tasks
    USING (tenant_id = current_setting('app.current_tenant_id')::int);

This is the most secure approach because even raw SQL queries are filtered. However, it requires PostgreSQL-specific features and careful session configuration.

Performance Considerations

Indexing Strategy

Notice that every index on a tenant-scoped table starts with tenant_id. This is intentional. The composite index (tenant_id, project_id) on tasks supports two query patterns:

"All tasks for this tenant" -- uses the first column of the index
"All tasks in this project for this tenant" -- uses both columns

Without tenant_id as the leading column, queries that filter by tenant would need a separate index or fall back to a full table scan.

Query Patterns and Performance

The most common queries and their index support:

Query Pattern	Index Used
List projects for a tenant	`ix_projects_tenant_status`
List tasks for a project	`ix_tasks_tenant_project`
List tasks assigned to a user	`ix_tasks_tenant_assignee`
List comments on a task	`ix_comments_tenant_task`
Audit log for an entity	`ix_audit_tenant_entity`

Scaling Considerations

As the tenant count grows, consider these strategies:

Connection pooling: Use PgBouncer to manage database connections efficiently.
Read replicas: Route read queries to replicas to reduce load on the primary.
Table partitioning: Partition large tables by tenant_id using PostgreSQL's native partitioning. This keeps each tenant's data physically co-located, improving cache performance and enabling partition pruning in queries.
Tenant sharding: For extreme scale (millions of tenants), shard tenants across multiple database clusters. This is a major architectural change and should be deferred until actually needed.

Testing the Multi-Tenant Schema

Testing multi-tenant systems requires verifying that tenant isolation actually works:

def test_tenant_isolation(session: Session) -> None:
    """Verify that one tenant cannot access another tenant's data."""
    # Create two tenants
    tenant_a = Tenant(name="Tenant A", slug="tenant-a")
    tenant_b = Tenant(name="Tenant B", slug="tenant-b")
    session.add_all([tenant_a, tenant_b])
    session.flush()

    # Create a project in Tenant A
    project = Project(
        tenant_id=tenant_a.id,
        name="Secret Project",
        owner_id=1,
    )
    session.add(project)
    session.flush()

    # Query as Tenant B -- should find nothing
    repo = ProjectRepository(session, tenant_id=tenant_b.id)
    results = repo.list_all()
    assert len(results) == 0, "Tenant B should not see Tenant A's projects"

    # Query as Tenant A -- should find the project
    repo = ProjectRepository(session, tenant_id=tenant_a.id)
    results = repo.list_all()
    assert len(results) == 1
    assert results[0].name == "Secret Project"

Lessons Learned

The TenantMixin pattern is powerful. Adding tenant_id via a mixin ensures consistency. Every new table automatically gets tenant scoping.
Lead composite indexes with tenant_id. Since every query filters by tenant, tenant_id should be the first column in every composite index on tenant-scoped tables.
Enforce isolation at multiple layers. Repository-level filtering is the minimum. Event listeners or RLS add defense in depth.
Users are global, memberships are scoped. Users exist outside the tenant boundary (they might belong to multiple organizations), but their roles and permissions are tenant-specific.
The audit log is a lifesaver. For SaaS applications, the ability to answer "who changed what and when" is not a nice-to-have; it is a compliance requirement.
Start with shared schema, migrate if needed. The shared-schema approach handles most SaaS workloads. You can always add partitioning or sharding later -- but you cannot easily merge separate databases back together.
Test isolation explicitly. Write tests that create multiple tenants and verify that data from one is never visible to the other. These tests prevent the most critical class of bugs in a multi-tenant system.