Designing a Multi-Tenant Data Model

Introduction

Welcome to our new module on Multi-Tenant Architecture. In our previous lesson, you designed a MemoryManager interface that elegantly handles conversation state. A key component of that design was the MemoryScope, which partitions memory using tenantId, userId, and sessionId. That tenantId was a crucial piece of abstraction, allowing us to conceptually separate the data of different clients.

This lesson brings that abstraction into reality. We will focus on designing the foundational data model for our tenants, including their specific configurations and metadata. Your goal is to design a schema that not only defines what a tenant is but also establishes the rules for how every piece of data in our system—from agents to prompts to conversation logs—is securely associated with one. This is the bedrock upon which our entire multi-tenant AI service will be built. As a developer building a SaaS application, you'll find these principles directly applicable to creating a scalable and secure architecture.

What is Multi-Tenancy?

At its core, a multi-tenant system serves multiple customers (tenants) from a single instance of the software and its underlying infrastructure. The key challenge is to maintain strict data isolation so that it appears to each tenant that they are the sole user of the system.

For most SaaS applications, the most efficient way to achieve this is with a shared database, shared schema model. All tenants' data resides in the same set of tables, but every row is tagged with a tenant_id to denote ownership. This approach is cost-effective and simplifies maintenance, as updates are applied to a single database schema.

The following video provides an excellent high-level introduction to this concept, using a simple blog application as an example to illustrate how shared resources can serve multiple organizations while keeping their data separate.

The key takeaway is that tenancy should not be an afterthought. It must be a "first-class dimension" of your domain model. The following article from WorkOS provides a superb technical walkthrough of this principle.

Designing the Core Tenant Schema

Now, let's design the database schema. The goal is to make tenant ownership explicit and unavoidable in our data model. A common and robust pattern separates the concepts of users and tenants, linking them through a membership table. This allows a single user account to be part of multiple tenants (e.g., different company workspaces), a standard feature in modern SaaS products.

Our core model will consist of three main tables:

1. tenants: The central table representing a customer organization.
2. users: A global table of all user accounts in the system.
3. memberships: A join table that connects users to tenants and defines their role or status within that tenant.

The WorkOS article provides a perfect, clear schema for this model.

Following this model, any other table in our system that stores tenant-specific data—such as conversation history, agent configurations, or prompt versions—must include a tenant_id column that is a foreign key referencing tenants.id. This enforces data ownership at the database level.

To see how these relationships are built in practice, the video you watched earlier continues with a detailed walkthrough of creating an Entity-Relationship Diagram (ERD). This will help you visualize the process of adding foreign keys and defining relationships.

Modeling Tenant Configurations and Metadata

Our system needs to store more than just core business data. Each tenant will have unique configurations: API keys for external tools, custom behavior thresholds for agents, selected foundation models, or even UI theme preferences.

Adding a new column to the tenants table for every possible configuration is not scalable. It leads to a bloated, rigid schema that is difficult to maintain. A far better approach, especially with a database like PostgreSQL, is to use a JSONB column.

A JSONB column allows you to store a flexible, nested JSON object within a single database field. It stores data in a decomposed binary format, which is highly efficient to query and can be indexed. This gives you the flexibility of a NoSQL document store within your relational database.

The article "Building a Multi-Tenant AI Agent Platform" offers a compelling real-world example of using PostgreSQL and JSONB for this exact purpose.

To understand the practicalities of working with JSONB, the following short video from Supabase is an excellent primer. Given your experience with PostgreSQL, this should be a straightforward concept.

A Proposed Data Model for Our System

Synthesizing these concepts, here is a proposed initial data model for our agentic infrastructure. This schema is written in SQL compatible with PostgreSQL.

-- Represents a customer organization (a tenant)
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    -- e.g., 'free', 'pro', 'enterprise'
    plan_type TEXT NOT NULL DEFAULT 'free',
    -- Flexible storage for tenant-specific settings like API keys,
    -- custom prompts, feature flags, or UI preferences.
    configurations JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Global store of all users who can log in
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email TEXT NOT NULL UNIQUE,
    -- Other user details like name, avatar_url, etc.
    name TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Join table to link users to tenants with a specific role
CREATE TABLE memberships (
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    -- e.g., 'owner', 'admin', 'member'
    role TEXT NOT NULL DEFAULT 'member',
    PRIMARY KEY (user_id, tenant_id)
);

-- Example of a business object: storing versioned prompts
-- Every business object table MUST have a tenant_id
CREATE TABLE prompt_versions (
    id SERIAL PRIMARY KEY,
    -- Foreign key to enforce tenant ownership
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    prompt_name TEXT NOT NULL,
    version INT NOT NULL,
    prompt_text TEXT NOT NULL,
    model_id TEXT, -- e.g., 'gpt-4o', 'claude-3-sonnet'
    is_active BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    -- Ensures a prompt name and version is unique within a tenant
    UNIQUE (tenant_id, prompt_name, version)
);

-- Add composite indexes on foreign keys for performance
CREATE INDEX ON memberships (tenant_id);
CREATE INDEX ON prompt_versions (tenant_id);

This schema establishes the non-negotiable rule: every piece of business logic data is owned by a tenant. The prompt_versions table is just one example; the same tenant_id column and index would be applied to tables for agents, tools, conversation_history, and so on.

Conclusion

In this lesson, you've designed the data model that forms the architectural foundation of our multi-tenant system. By treating tenancy as a primary dimension of the data model from the outset, you've set the stage for a secure, scalable, and maintainable application.

Key Takeaways:

• Tenant Data Model: A robust pattern uses tenants, users, and a memberships join table. This allows users to belong to multiple organizations.
• Data Isolation: The "shared database, shared schema" approach is a common, efficient starting point for SaaS. Isolation is enforced by including a non-nullable tenant_id foreign key on every business object's table.
• Mandatory Indexing: The tenant_id column should always be indexed, often as part of a composite index with the primary key or other frequently queried columns, to ensure high performance.
• Flexible Metadata: A JSONB column is the ideal solution for storing tenant-specific configurations, offering schema flexibility without sacrificing query performance or relational integrity.

The schema you've designed today is the blueprint. In our next lesson, you will bring this design to life by implementing a TenantStore with CRUD (Create, Read, Update, Delete) operations, creating the first active component of our multi-tenant infrastructure.