Multi-Tenant Database Structure

Complete overview of the database schema for multi-tenant architecture.

Core Tables

tenants

The master table for all tenant organizations.

CREATE TABLE public.tenants (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,
  company jsonb DEFAULT '{}'::jsonb,
  active boolean DEFAULT true,
  created_at timestamp with time zone DEFAULT now(),
  updated_at timestamp with time zone DEFAULT now()
);

Columns:

• id: Unique tenant identifier (UUID)
• name: Organization/company name
• company: JSONB field for flexible company metadata (address, contact info, etc.)
• active: Whether tenant is active (can be deactivated without deletion)
• created_at, updated_at: Audit timestamps

Indexes:

• idx_tenants_active: For filtering active tenants
• idx_tenants_name: For name lookups

tenant_users

Links users to tenants and defines their role within that tenant.

CREATE TABLE public.tenant_users (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE,
  user_id uuid NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  role text NOT NULL CHECK (role IN ('tenant_admin', 'tenant_user')),
  created_at timestamp with time zone DEFAULT now(),
  UNIQUE(tenant_id, user_id)
);

Columns:

• id: Primary key
• tenant_id: Foreign key to tenants
• user_id: Foreign key to profiles (auth users)
• role: Either tenant_admin or tenant_user
• created_at: When user was assigned to tenant

Constraints:

• Unique constraint on (tenant_id, user_id) - a user can only have one role per tenant
• CASCADE delete: If tenant is deleted, all tenant_users are deleted
• CASCADE delete: If user is deleted, their tenant_users records are deleted

Indexes:

• idx_tenant_users_tenant_id: For finding all users in a tenant
• idx_tenant_users_user_id: For finding all tenants for a user
• idx_tenant_users_role: For filtering by role

Module Tables with Tenant ID

All business data tables include tenant_id for isolation:

Inventory Module

-- inventory_items
tenant_id uuid REFERENCES public.tenants(id) ON DELETE CASCADE

-- stock_movements
tenant_id uuid REFERENCES public.tenants(id) ON DELETE CASCADE

Shipments Module

-- shipments
tenant_id uuid REFERENCES public.tenants(id) ON DELETE CASCADE

-- transport_details
tenant_id uuid REFERENCES public.tenants(id) ON DELETE CASCADE

Pick & Pack Module

-- pick_lists
tenant_id uuid REFERENCES public.tenants(id) ON DELETE CASCADE

-- pick_list_items
tenant_id uuid REFERENCES public.tenants(id) ON DELETE CASCADE

-- pack_sessions
tenant_id uuid REFERENCES public.tenants(id) ON DELETE CASCADE

Warehouse Floor Plan Module

-- floorplans
tenant_id uuid REFERENCES public.tenants(id) ON DELETE CASCADE

-- floorplan_zones
tenant_id uuid REFERENCES public.tenants(id) ON DELETE CASCADE

Maintenance Module

-- maintenance_tasks
tenant_id uuid REFERENCES public.tenants(id) ON DELETE CASCADE

-- maintenance_schedules
tenant_id uuid REFERENCES public.tenants(id) ON DELETE CASCADE

Safety Module

-- safety_incidents
tenant_id uuid REFERENCES public.tenants(id) ON DELETE CASCADE

-- safety_checks
tenant_id uuid REFERENCES public.tenants(id) ON DELETE CASCADE

Equipment Tracking Module

-- equipment
tenant_id uuid REFERENCES public.tenants(id) ON DELETE CASCADE

-- equipment_locations
tenant_id uuid REFERENCES public.tenants(id) ON DELETE CASCADE

System Settings

-- system_settings
tenant_id uuid UNIQUE NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE

Auto Assignment

-- auto_assign_settings
tenant_id uuid UNIQUE REFERENCES public.tenants(id) ON DELETE CASCADE

Enterprise SSO

-- tenant_sso_providers
tenant_id uuid NOT NULL REFERENCES public.tenants(id) ON DELETE CASCADE

Shared Tables (No Tenant ID)

Some tables are shared across all tenants:

zones

Zones can be shared or tenant-specific. The table does not have tenant_id by design, allowing for:

• Shared warehouse zones (common across tenants)
• Tenant-specific zones (managed via other mechanisms)

Note: In practice, zones are typically tenant-specific through application logic, but the schema allows flexibility.

user_roles

Global user roles (admin, driver, worker) are not tenant-specific:

CREATE TABLE public.user_roles (
  user_id uuid PRIMARY KEY REFERENCES public.profiles(id),
  role text NOT NULL CHECK (role IN ('admin', 'driver', 'worker'))
);

profiles

User profiles are global, but users are assigned to tenants via tenant_users.

Foreign Key Relationships

tenants (1) ──< (many) tenant_users (many) >── (1) profiles
   │
   └──< (many) inventory_items
   └──< (many) shipments
   └──< (many) pick_lists
   └──< (many) floorplans
   └──< (many) system_settings (1:1)
   └──< (many) auto_assign_settings (1:1)
   └──< (many) tenant_sso_providers
   └──< (many) [all other module tables]

Cascade Deletion

When a tenant is deleted:

1. All tenant_users records are deleted (CASCADE)
2. All data in tenant-scoped tables is deleted (CASCADE)
3. User profiles remain (users can be reassigned)
4. Global roles remain (user_roles unaffected)

Important: Tenant deletion is permanent and removes all data. Consider deactivating (active = false) instead.

Indexes for Performance

All tenant_id columns are indexed for fast filtering:

-- Example index pattern
CREATE INDEX idx_<table>_tenant_id ON <table>(tenant_id);

Common query patterns:

• Filter by tenant: WHERE tenant_id = ?
• Join with tenant_users: JOIN tenant_users ON tenant_id = ?
• Count per tenant: GROUP BY tenant_id

Helper Functions

get_user_tenant_id()

Returns the current user's tenant ID:

SELECT public.get_user_tenant_id();
-- Returns: uuid | null

is_tenant_admin()

Checks if current user is a tenant admin:

SELECT public.is_tenant_admin();
-- Returns: boolean

is_super_admin()

Checks if current user is a super admin:

SELECT public.is_super_admin();
-- Returns: boolean

Data Migration Strategy

When adding tenant_id to existing tables:

1. Add nullable tenant_id column
2. Backfill with default tenant (if applicable)
3. Make column NOT NULL
4. Add foreign key constraint
5. Add index
6. Update RLS policies

Example Migration:

-- Step 1: Add column (nullable)
ALTER TABLE shipments ADD COLUMN tenant_id uuid;

-- Step 2: Backfill (assign to default tenant)
UPDATE shipments SET tenant_id = '<default-tenant-id>';

-- Step 3: Make NOT NULL
ALTER TABLE shipments ALTER COLUMN tenant_id SET NOT NULL;

-- Step 4: Add foreign key
ALTER TABLE shipments 
  ADD CONSTRAINT shipments_tenant_id_fkey 
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE;

-- Step 5: Add index
CREATE INDEX idx_shipments_tenant_id ON shipments(tenant_id);

Related Documentation

• RLS Policies - How security policies work
• Tenant Bootstrap - Creating new tenants
• Service Role - Bypassing RLS when needed