Multi-Tenant CRM Schema Design: Typed Columns vs JSONB

In a multi-tenant CRM, each company (tenant) has its own data—employees, leads, deals, contacts. But here's the challenge: not every tenant stores the same kind of data.

For example:

• Company A might store clients with name, email, and budget
• Company B might store clients with name, address, and industry
• Company C might need name, phone, company_size, and last_contact_date

How do you design a database schema that's flexible enough to handle these varying requirements while maintaining performance, data integrity, and query efficiency?

There are two main approaches, each with distinct trade-offs.

The Challenge: Flexibility vs Structure

Traditional relational databases love structure. You define columns, types, constraints, and indexes upfront. But in a multi-tenant SaaS CRM, you can't predict what fields each tenant will need.

You could create a separate database per tenant, but that's operationally expensive and doesn't scale well. Instead, we need a shared schema that accommodates tenant-specific customization.

Approach 1: Typed Columns

In this design, you keep the base structure fixed and store flexible attributes in separate tables using the Entity-Attribute-Value (EAV) pattern.

Schema Structure

-- Core tenant identification
CREATE TABLE tenants (
  id UUID PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Base client table with common fields
CREATE TABLE clients (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  client_name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Define custom fields per tenant
CREATE TABLE client_fields (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  field_name VARCHAR(100) NOT NULL,
  field_type VARCHAR(50) NOT NULL, -- 'string', 'number', 'boolean', 'date'
  is_required BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(tenant_id, field_name)
);

-- Store actual field values
CREATE TABLE client_field_values (
  id UUID PRIMARY KEY,
  client_id UUID NOT NULL REFERENCES clients(id),
  field_id UUID NOT NULL REFERENCES client_fields(id),
  value_string TEXT,
  value_number NUMERIC,
  value_boolean BOOLEAN,
  value_date TIMESTAMP,
  UNIQUE(client_id, field_id)
);

Example Data

Company A's custom fields:

INSERT INTO client_fields (id, tenant_id, field_name, field_type) VALUES
  ('f1', 'tenant-a', 'email', 'string'),
  ('f2', 'tenant-a', 'budget', 'number');

Company A's client data:

-- Base client record
INSERT INTO clients (id, tenant_id, client_name) VALUES
  ('c1', 'tenant-a', 'Rushikesh');

-- Custom field values
INSERT INTO client_field_values (client_id, field_id, value_string) VALUES
  ('c1', 'f1', 'r@gmail.com');
  
INSERT INTO client_field_values (client_id, field_id, value_number) VALUES
  ('c1', 'f2', 10000);

Company B's custom fields:

INSERT INTO client_fields (id, tenant_id, field_name, field_type) VALUES
  ('f3', 'tenant-b', 'address', 'string'),
  ('f4', 'tenant-b', 'industry', 'string');

Advantages of Typed Columns

• Type Safety
• Efficient Indexing
• Analytics-Friendly
• Data Integrity

Disadvantages of Typed Columns

• Complex Queries
• Schema Complexity
• Write Overhead

Approach 2: JSONB Columns (Flexible Document Model)

In this design, you store variable tenant-specific data in a JSONB column, combining relational structure with document flexibility.

Schema Structure

-- Core tenant identification
CREATE TABLE tenants (
  id UUID PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Clients with JSONB for flexible data
CREATE TABLE clients (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  client_name VARCHAR(255) NOT NULL,
  data JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

Example Data

Company A's client:

INSERT INTO clients (id, tenant_id, client_name, data) VALUES (
  'c1',
  'tenant-a',
  'Rushikesh',
  '{
    "email": "r@gmail.com",
    "budget": 10000,
    "priority": "high"
  }'::jsonb
);

Company B's client:

INSERT INTO clients (id, tenant_id, client_name, data) VALUES (
  'c2',
  'tenant-b',
  'Rushikesh',
  '{
    "address": "Mumbai",
    "industry": "Retail",
    "employees": 50
  }'::jsonb
);

Advantages of JSONB

• No schema changes needed for new fields
• Each tenant can have completely different structure
• Easy to add/remove fields per record
• Simple Queries
• Schema changes are just new JSON keys
• No ALTER TABLE statements
• No migration downtime
• Perfect for rapid product development
• GIN Index Performance

Disadvantages of JSONB

• Loss of Type Safety
• Slower Aggregations
• Data Validation in Application

Conclusion

There's no one-size-fits-all answer for multi-tenant CRM schema design. The choice between typed columns and JSONB depends on your specific requirements:

Typed Columns (EAV) excel when you need strong typing, complex analytics, and data integrity, but come with query complexity and schema overhead.

JSONB excels when you need flexibility, rapid iteration, and simple queries, but sacrifices type safety and aggregation performance.

Hybrid approaches offer the best of both worlds for production systems, using typed columns for common fields and JSONB for tenant-specific customization.