Designing a Multi-Tenant Database Schema for a B2B Wellness SaaS

In the rapidly growing HealthTech sector, building a B2B Wellness SaaS platform presents a unique set of challenges. Corporate clients demand robust security and absolute certainty that their employees' sensitive wellness data is completely isolated from other clients. As the lead database architect for "WellnessCorp," a fictional B2B wellness provider, I was tasked with designing a PostgreSQL database that could serve hundreds of corporate clients, each with thousands of employees, without compromising on security, scalability, or performance.

This case study breaks down the architectural decisions, trade-offs, and practical implementation details of our multi-tenant database design. We'll explore why we chose the Schema-per-Tenant model and how it provides a powerful solution for B2B SaaS platforms.

Prerequisites:

• A solid understanding of relational databases and SQL.
• Basic familiarity with PostgreSQL.
• An interest in software architecture and SaaS application design.

For a B2B Wellness SaaS, data privacy isn't just a feature; it's a foundational requirement. A data breach that exposes one company's employee wellness data to another would be catastrophic, leading to a loss of trust and potentially severe legal repercussions. Therefore, our primary challenge was to enforce strict data isolation between tenants.

We evaluated three common multi-tenancy models:

1. Database-per-Tenant: Each tenant gets their own dedicated database.

   • Pros: Maximum isolation and security.
   • Cons: High operational overhead, especially with a large number of tenants. Managing migrations, backups, and connections for hundreds or thousands of databases can become a nightmare.

2. Shared Database, Shared Schema with a tenant_id Column: All tenants share the same database and tables, with a tenant_id column to distinguish data.

   • Pros: Simple to set up and manage, with lower resource consumption.
   • Cons: Prone to human error in the application layer. A single missing WHERE tenant_id = ? clause in a query could lead to a massive data leak. While PostgreSQL's Row-Level Security (RLS) can mitigate this, it adds complexity to every query.

3. Shared Database, Schema-per-Tenant: All tenants share the same database instance, but each tenant has its own dedicated schema.

   • Pros: Strong logical data isolation. It's impossible to accidentally query another tenant's data. It also allows for tenant-specific customizations.
   • Cons: Can have higher operational complexity than a shared schema, and there can be performance implications with a very large number of schemas.

For "WellnessCorp," the Schema-per-Tenant model hit the sweet spot. It offered the strong data isolation we needed without the overwhelming operational cost of a database-per-tenant approach.

Before we dive into the implementation, here's what you'll need to follow along:

• PostgreSQL: Version 12 or higher.
• A PostgreSQL client: psql or a GUI tool like DBeaver.

We'll be using standard PostgreSQL features, so no special extensions are required for this core setup.

Our first step is to establish a central place to manage our tenants. The public schema is perfect for this.

What we're doing

We'll create a tenants table in the public schema. This table will store information about each of our corporate clients, including a unique identifier that will also serve as the name of their dedicated schema.

Implementation

-- src/sql/01_setup_public_schema.sql

-- The public schema will hold our tenant management table.
CREATE TABLE public.tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_name VARCHAR(255) NOT NULL,
    schema_name VARCHAR(63) NOT NULL UNIQUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Create a unique index on the schema_name for fast lookups.
CREATE UNIQUE INDEX idx_tenants_schema_name ON public.tenants(schema_name);

How it works

This tenants table acts as our master record for all clients. The schema_name is crucial; it programmatically links a tenant to their dedicated data schema. Using a UUID for the primary key is a good practice for distributed systems and hides any sequential information about our tenants.

Manually creating a new schema and all its tables for every new client is tedious and error-prone. We need to automate this process.

What we're doing

We'll create a PostgreSQL function that triggers whenever a new tenant is added to our public.tenants table. This function will dynamically create a new schema for the tenant and replicate our core application tables within it.

Implementation

-- src/sql/02_automate_schema_creation.sql

-- Function to create a new schema and the necessary tables for a new tenant.
CREATE OR REPLACE FUNCTION create_tenant_schema()
RETURNS TRIGGER AS $$
BEGIN
    -- Create a new schema with the name specified in the new tenant record.
    EXECUTE format('CREATE SCHEMA %I', NEW.schema_name);

    -- Replicate the core application tables into the new schema.
    EXECUTE format('CREATE TABLE %I.employees (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        email VARCHAR(255) NOT NULL UNIQUE,
        full_name VARCHAR(255),
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )', NEW.schema_name);

    EXECUTE format('CREATE TABLE %I.wellness_logs (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        employee_id UUID REFERENCES %I.employees(id) ON DELETE CASCADE,
        activity_type VARCHAR(50) NOT NULL,
        duration_minutes INTEGER,
        log_date DATE NOT NULL,
        notes TEXT,
        created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )', NEW.schema_name, NEW.schema_name);

    -- You can add more tables here as your application grows.

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create a trigger that calls our function after a new tenant is inserted.
CREATE TRIGGER on_new_tenant_created
    AFTER INSERT ON public.tenants
    FOR EACH ROW
    EXECUTE FUNCTION create_tenant_schema();

How it works

This trigger-function combination is the heart of our automation. When our application inserts a new client into public.tenants, this trigger fires, creating a fully provisioned, isolated environment for the new tenant. This approach ensures consistency across all tenant schemas.

With separate schemas, we need a secure way for our application to connect and interact with the correct tenant's data.

What we're doing

We'll create a dedicated, non-superuser role for our application. When a user from a specific company logs in, our application will connect to the database with this role and then set the search_path to that tenant's schema for the duration of the session. This is a critical security measure.

Implementation

-- src/sql/03_security_setup.sql

-- Create a role for our application with login privileges.
-- In a real-world scenario, use a more secure password!
CREATE ROLE wellness_app LOGIN PASSWORD 'supersecretpassword';

-- Grant usage on the public schema so the app can query the tenants table.
GRANT USAGE ON SCHEMA public TO wellness_app;
GRANT SELECT ON public.tenants TO wellness_app;

-- We will grant permissions to tenant schemas dynamically.
-- Let's add a function to do this.
CREATE OR REPLACE FUNCTION grant_tenant_permissions()
RETURNS TRIGGER AS $$
BEGIN
    -- Grant usage on the new schema to our application role.
    EXECUTE format('GRANT USAGE ON SCHEMA %I TO wellness_app', NEW.schema_name);
    -- Grant all privileges on the tables within the new schema.
    EXECUTE format('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA %I TO wellness_app', NEW.schema_name);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Update our trigger to also grant permissions.
-- We'll drop the old trigger and create a new one.
DROP TRIGGER on_new_tenant_created ON public.tenants;

CREATE TRIGGER on_new_tenant
    AFTER INSERT ON public.tenants
    FOR EACH ROW
    EXECUTE FUNCTION create_tenant_schema();

CREATE TRIGGER on_new_tenant_permissions
    AFTER INSERT ON public.tenants
    FOR EACH ROW
    EXECUTE FUNCTION grant_tenant_permissions();

How it works

When a user logs in, the application backend authenticates them, identifies their tenant, and then executes the following SQL command immediately after establishing a database connection:

SET search_path TO 'tenant_schema_name', public;

From this point on, any query like SELECT * FROM employees; will automatically resolve to tenant_schema_name.employees. This is transparent to the application's data access layer and provides a strong guarantee of data isolation at the database level.

Let's walk through the process of onboarding a new corporate client, "Innovate Inc."

1. A new client signs up. Our application backend inserts their information into the tenants table.

   code

   INSERT INTO public.tenants (company_name, schema_name)
   VALUES ('Innovate Inc.', 'innovate_inc');
   

   Code collapsed

2. The triggers fire.

   • create_tenant_schema creates the innovate_inc schema with the employees and wellness_logs tables.
   • grant_tenant_permissions grants the wellness_app role the necessary permissions on the new schema and its tables.

3. An employee from Innovate Inc. logs in.

   • Our application connects to PostgreSQL using the wellness_app role.
   • It then sets the session's search_path:
     code

     SET search_path TO 'innovate_inc', public;
     

     Code collapsed

4. The application queries data.

   • A query like SELECT * FROM wellness_logs; is executed.
   • PostgreSQL, following the search_path, directs this query to innovate_inc.wellness_logs, ensuring that only data from Innovate Inc. is returned.

• Connection Pooling: With many tenants, managing database connections is vital. A connection pooler like PgBouncer is essential to prevent connection overhead from overwhelming the database.
• Indexing: Each tenant's tables are smaller, which generally leads to faster index scans. However, it's still crucial to have appropriate indexes on foreign keys and frequently queried columns within each tenant's schema.
• Monitoring: Monitor database performance and resource usage. If a particular tenant grows significantly larger than others (a "noisy neighbor"), this model allows you to potentially move them to a dedicated database instance in the future.

While the Schema-per-Tenant model is a strong choice, it's worth noting the primary alternative for robust security:

• Shared Schema with Row-Level Security (RLS): RLS is a powerful PostgreSQL feature that allows you to define policies on a table to control which rows can be accessed by which users. This can also provide strong data isolation but requires careful policy management and can add a slight performance overhead to every query. It's an excellent option if you have a massive number of tenants and the operational overhead of managing many schemas is a concern.

For "WellnessCorp," the Schema-per-Tenant model provided the ideal balance of strong data isolation, operational scalability, and performance. By automating schema creation and carefully managing permissions, we built a robust, secure, and scalable multi-tenant database architecture on PostgreSQL. This approach gives our corporate clients the confidence they need to entrust us with their sensitive employee wellness data.

This case study demonstrates a practical and effective solution for a common challenge in B2B SaaS development. The principles discussed here can be adapted to a wide range of multi-tenant applications.

• PostgreSQL Documentation on Schemas: https://www.postgresql.org/docs/current/ddl-schemas.html
• Crunchy Data Blog: Designing Your Postgres Database for Multi-tenancy: https://www.crunchydata.com/blog/designing-your-postgres-database-for-multi-tenancy
• AWS Database Blog: Multi-tenant data isolation with PostgreSQL Row Level Security: https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/