Multi-Tenant Architecture Overview

Lager Guru implements a comprehensive multi-tenant SaaS architecture that allows multiple organizations (tenants) to use the same application instance while maintaining complete data isolation and security.

What is Multi-Tenancy?

Multi-tenancy is an architecture pattern where a single instance of software serves multiple customers (tenants). Each tenant's data is isolated and remains invisible to other tenants, even though they share the same database and application infrastructure.

Benefits

• Cost Efficiency: Shared infrastructure reduces operational costs
• Scalability: Easy to add new tenants without additional infrastructure
• Maintenance: Single codebase to maintain and update
• Resource Optimization: Better utilization of server resources

Challenges Addressed

• Data Isolation: Complete separation of tenant data
• Security: Preventing cross-tenant data access
• Performance: Efficient queries with tenant filtering
• Compliance: Meeting data residency and privacy requirements

How Lager Guru Implements Multi-Tenancy

Lager Guru uses a shared database, shared schema approach with Row-Level Security (RLS) for data isolation. Every data table includes a tenant_id column that acts as the isolation boundary.

Core Principles

1. Tenant ID Everywhere: All business data tables include tenant_id
2. RLS Enforcement: Database-level security policies enforce tenant isolation
3. Automatic Filtering: Application code automatically filters by tenant
4. Service Role Bypass: Background jobs use service role to bypass RLS when needed

Architecture Layers

flowchart TD
    A[Application Layer<br/>React Frontend + Supabase Client] --> B[Database Layer<br/>PostgreSQL + Supabase]
    A --> C[Automatic tenant_id injection]
    A --> D[Route guards for tenant switching]
    B --> E[Row-Level Security RLS policies]
    B --> F[tenant_id foreign keys]
    B --> G[Helper functions for tenant checks]
    
    style A fill:#3b82f6,color:#fff
    style B fill:#10b981,color:#fff
    style E fill:#f59e0b,color:#fff

Multi-Tenant Architecture Diagram

flowchart TB
    subgraph "Tenant A"
        TA1[User A1] --> TA2[Tenant A Data]
        TA3[User A2] --> TA2
        TA2 --> TA4[RLS Policy A]
    end
    
    subgraph "Tenant B"
        TB1[User B1] --> TB2[Tenant B Data]
        TB3[User B2] --> TB2
        TB2 --> TB4[RLS Policy B]
    end
    
    subgraph "Shared Database"
        TA4 --> DB[(PostgreSQL)]
        TB4 --> DB
        DB --> RLS[Row-Level Security<br/>Enforces Isolation]
    end
    
    subgraph "Application"
        APP[React App] --> TA1
        APP --> TA3
        APP --> TB1
        APP --> TB3
    end
    
    style TA2 fill:#3b82f6,color:#fff
    style TB2 fill:#10b981,color:#fff
    style DB fill:#f59e0b,color:#fff
    style RLS fill:#ef4444,color:#fff

Key Components

1. Tenant Table

The tenants table stores basic information about each organization:

• id: Unique tenant identifier (UUID)
• name: Tenant/organization name
• company: JSONB field for additional company metadata
• active: Whether the tenant is active
• created_at, updated_at: Timestamps

2. Tenant Users Table

The tenant_users table links users to tenants:

• tenant_id: Reference to tenant
• user_id: Reference to user profile
• role: Either tenant_admin or tenant_user
• Unique constraint on (tenant_id, user_id)

3. Tenant ID in All Tables

Every business data table includes tenant_id:

• inventory_items.tenant_id
• shipments.tenant_id
• pick_lists.tenant_id
• zones.tenant_id (optional, can be shared)
• stock_movements.tenant_id
• And many more...

Data Isolation Strategy

Row-Level Security (RLS)

RLS policies automatically filter queries based on the authenticated user's tenant:

-- Example: Users can only see their tenant's shipments
CREATE POLICY shipments_tenant_isolation ON shipments
  FOR SELECT
  USING (
    tenant_id IN (
      SELECT tenant_id FROM tenant_users
      WHERE user_id = auth.uid()
    )
  );

Application-Level Filtering

The application code automatically includes tenant filtering:

// Automatically filters by current user's tenant
const { data } = await supabase
  .from('shipments')
  .select('*')
  // tenant_id is automatically filtered by RLS

User Roles and Permissions

Super Admin

• Role: admin in user_roles table
• Access: Can see and manage ALL tenants
• Use Case: Platform administrators

Tenant Admin

• Role: tenant_admin in tenant_users table
• Access: Full access to their tenant's data
• Use Case: Organization administrators

Tenant User

• Role: tenant_user in tenant_users table
• Access: Read/write access to their tenant's data (with module-specific restrictions)
• Use Case: Regular employees

Tenant Lifecycle

1. Creation: Super admin creates new tenant
2. User Assignment: Users are assigned to tenant via tenant_users
3. Data Isolation: All data automatically scoped to tenant
4. Deactivation: Tenant can be deactivated without data deletion
5. Deletion: Cascade deletion removes all tenant data (if needed)

Security Guarantees

1. Database-Level: RLS policies prevent cross-tenant queries
2. Application-Level: Code always includes tenant context
3. Service Role: Only used for background jobs, never for user requests
4. Audit Trail: All operations logged with tenant context

Performance Considerations

• Indexes: tenant_id columns are indexed for fast filtering
• Query Optimization: RLS policies use efficient subqueries
• Caching: Tenant context cached in application state
• Batch Operations: Service role used for bulk operations

Related Documentation

• Database Structure - Detailed table schemas
• RLS Policies - Security policy details
• Auth Flow - How authentication works with tenants
• Service Role - When and how to use service role