Choosing Multi-Tenancy for Construction SaaS

What is Multi-Tenancy?

First, let's define our terms. A tenant is a group of users who share common access to a set of data, like a single company that subscribes to our SaaS. Multi-tenancy is an architecture where a single instance of the software serves multiple tenants. The key challenge, and the mark of a well-designed system, is ensuring that while tenants share infrastructure, their data is strictly isolated as if they were the only customer using the application.

To get a clear picture of this concept, a great video from the "Software Developer Diaries" channel explains it using Slack as a familiar example.

As the video highlights, the central conflict in multi-tenant design is balancing isolation against efficiency. Maximum isolation (giving every customer their own dedicated infrastructure) is secure but expensive and complex. Maximum efficiency (pooling all customers onto shared infrastructure) is cost-effective but introduces security and performance risks. Our job as architects is to find the right point on this spectrum for our product.

The Spectrum of Data Isolation Strategies

There are three primary patterns for implementing data isolation at the database level. Each represents a different point on the isolation-vs-efficiency spectrum.

Let's look at a high-level visual comparison of these models before we break them down.

This diagram illustrates the conceptual difference between Database, Schema, and Row-Level security, moving from physical separation to logical separation within a shared environment.

Now, let's analyze each one.

1. Shared Database, Shared Schema (Row-Level Isolation)

In this model, all tenants share a single database and the same set of tables. Data is segregated by adding a tenant_id column to every relevant table. Every query must then be filtered by this tenant_id to ensure it only accesses data for the correct tenant.

• Analogy: Think of a large library (the database) where books from all patrons (tenants) are stored on the same shelves (tables), but each book has a sticker indicating its owner.

This is the most common starting point for SaaS applications. The "Software Developer Diaries" video provides a good overview of this approach.

The biggest risk here is a data leak. A single programming error—forgetting a WHERE tenant_id = ? clause—could expose one tenant's data to another. The "noisy neighbor" effect, where one tenant's heavy usage degrades performance for everyone else, is also a significant concern.

However, modern databases like PostgreSQL offer a powerful tool to mitigate this risk: Row-Level Security (RLS). RLS allows you to define a security policy directly on a table, which acts as an implicit and automatic WHERE clause for every query that runs against it. This moves the security enforcement from the application layer (where a developer might forget) to the database layer, providing a crucial layer of defense.

Given your PostgreSQL experience, understanding RLS is key to making this model viable for a production system.

2. Shared Database, Separate Schemas

This model is a middle ground. You still use a single database instance, but each tenant receives their own dedicated schema inside it (e.g., company_a.projects, company_b.projects).

• Analogy: The library gives each patron their own dedicated bookshelf (schema) to store their books. All bookshelves are in the same room (database).

This provides stronger logical isolation than the shared schema model. Let's see what the "Software Developer Diaries" video has to say about this approach.

The main drawback is operational complexity. Imagine you need to add a column to the projects table. You now have to apply this migration to every single tenant's schema. For a system designed to support thousands of tenants, this quickly becomes unmanageable.

3. Database-per-Tenant

This is the most isolated model. Each tenant gets a completely separate, dedicated database instance.

• Analogy: Each patron gets their own private library.

This offers the highest level of security and eliminates the "noisy neighbor" problem.

While it seems ideal from a security standpoint, the cost and operational overhead are immense. You would be responsible for provisioning, monitoring, backing up, and migrating thousands of individual databases. This is typically reserved for high-value enterprise clients with strict compliance needs (e.g., in finance or healthcare).

Framework for Decision-Making

To make an informed choice, we need to analyze these patterns against the specific needs of our Construction Management SaaS. The article "Multi-Tenancy Database Patterns" by dasroot.net offers a superb and detailed breakdown.

Let's use the framework from the article to guide our decision:

1. Isolation vs. Cost: Construction data is business-critical, but it generally doesn't fall under the same stringent regulatory umbrella as healthcare (HIPAA) or financial data. We need strong, reliable isolation, but the cost of a database for every single construction company (many of which could be small businesses) would make our product prohibitively expensive. This pushes us away from the DB-per-tenant model for our primary offering.

2. Tenant Customization: Our goal is to provide a standardized, feature-rich platform. We don't anticipate enterprise clients needing to modify the core database schema. Any need for custom fields can be handled within a flexible table design (e.g., using JSONB data types in PostgreSQL), not by altering the schema itself. This weakens the case for the Schema-per-tenant and DB-per-tenant models.

3. Regulatory Compliance: While not as strict as some industries, compliance with privacy laws like GDPR is non-negotiable. Data leaks are unacceptable. The DB-per-tenant model is the strongest here, but the Shared DB/Shared Schema model, when fortified with RLS, provides a robust and defensible security posture that can satisfy auditors.

4. Long-Term Scalability: We aim to support thousands of companies.

   • DB-per-tenant: Fails at this scale due to cost and operational nightmare.
   • Schema-per-tenant: Fails due to the complexity of migrations across thousands of schemas and potential performance limits.
   • Shared DB/Shared Schema: This model is designed for horizontal scalability. With proper indexing and query optimization, a single, powerful database cluster can efficiently serve a vast number of tenants.

The Verdict

This table, while focused on a slightly different use case (RAG), perfectly summarizes the trade-offs for our PostgreSQL-based system. The "Row-Level" strategy is referred to here as the "Table" strategy.

Based on this analysis, the Shared Database, Shared Schema model is the clear winner for our SaaS platform.

It offers the best balance of cost-effectiveness, operational simplicity, and scalability to support a large number of tenants. Its primary weakness—the risk of data leaks—will be addressed head-on by making tenancy a "first-class citizen" in our application design and, most importantly, by implementing PostgreSQL's Row-Level Security (RLS). This creates the "defense-in-depth" posture we need for a production-grade system.

We are consciously choosing the most efficient model and engineering robust security into it, rather than starting with the most expensive model and struggling with its complexity.

Conclusion

In this lesson, you've taken the first and most critical step in designing our SaaS architecture. You've explored the fundamental trade-offs of multi-tenancy and made a reasoned, defensible decision.

Key Takeaways:

• Multi-tenancy is a spectrum balancing isolation and efficiency.
• The three main data isolation patterns are Database-per-tenant, Schema-per-tenant, and Shared Database/Shared Schema.
• For our Construction Management SaaS, the Shared Database, Shared Schema model fortified with Row-Level Security (RLS) provides the optimal balance of scalability, cost, and security.

Preview of the Next Lesson:

Now that we've decided how our data will be structured, our next lesson will focus on the system's overall structure. We will design a high-level architecture diagram, identifying all the major components of our system—the FastAPI backend, the database, asynchronous workers, and more—and mapping out how they interact. This will give us the blueprint for the entire project.