Multi Tenant Saas Architecture

Multi-Tenant SaaS Architecture

Acknowledgement: Shared by Peter Bamuhigire, techguypeter.com, +256 784 464178.

<!-- dual-compat-start -->

Use When

• Designing tenancy for a new SaaS, or hardening an existing SaaS against cross-tenant leakage.
• Splitting a SaaS into three panels (super admin, tenant/franchise admin, end user) with distinct scopes.
• Designing per-tenant role overrides, permission priority ordering, and audit trails for privileged access.
• Planning the session, JWT, and tenant_id plumbing that downstream database, API, security, and delivery skills rely on.

Do Not Use When

• The task is a single-tenant app with no plan for tenancy — this skill's constraints add overhead with no benefit.
• The work is about business-module composition rather than tenant boundaries — use modular-saas-architecture.
• The work is pure schema shaping inside an already-defined tenant model — use database-design-engineering and mysql-best-practices.

Required Inputs

• Context map and critical-flow table from system-architecture-design.
• Access-pattern list from database-design-engineering (so isolation model matches real queries).
• Threat model / abuse cases from vibe-security-skill.
• Panel and user-type list from product requirements (super admin, tenant admin, end user, and their sub-types).

Workflow

• Read this SKILL.md first, then load only the referenced deep-dive files that are necessary for the task.
• Apply the ordered guidance, checklists, and decision rules in this skill instead of cherry-picking isolated snippets.
• Produce the tenant-isolation map, panel layout, permission model, and audit plan as the deliverables named in Outputs.

Quality Standards

• Keep outputs execution-oriented, concise, and aligned with the repository's baseline engineering standards.
• Preserve compatibility with existing project conventions unless the skill explicitly requires a stronger standard.
• Prefer deterministic, reviewable steps over vague advice or tool-specific magic.

Anti-Patterns

• Treating examples as copy-paste truth without checking fit, constraints, or failure modes.
• Loading every reference file by default instead of using progressive disclosure.

Outputs

• Tenant-isolation map, panel definitions, permission priority model, and audit plan (see the Outputs table below).
• Clear assumptions, tradeoffs, or unresolved gaps when the task cannot be completed from available context alone.
• References used, companion skills, or follow-up actions when they materially improve execution.

Evidence Produced

References

• Use the references/ directory for deep detail after reading the core workflow below.
• Use the documentation/ directory for supporting implementation detail or migration notes.

<!-- dual-compat-end -->

Production-grade multi-tenant SaaS architecture with three-panel separation, zero-trust enforcement, strict tenant isolation, and comprehensive audit trails. This skill defines the tenant and auth boundaries that every downstream module, API, and data query inherits.

Prerequisites

Load these first, in order:

1. world-class-engineering — repository-wide quality bar. 2. system-architecture-design — produces the context map and critical flows this skill consumes. 3. database-design-engineering — produces the access-pattern list that shapes the isolation model. 4. vibe-security-skill — produces the threat model and auth rules this skill encodes.

When this skill applies

• Designing tenancy for a new SaaS from scratch.
• Converting a single-tenant app to multi-tenant (see documentation/migration.md).
• Auditing an existing SaaS for cross-tenant leakage, missing tenant_id filters, or un-audited admin actions.
• Planning the three-panel file and route layout (/, /adminpanel/, /memberpanel/).
• Designing the permission priority ladder (user deny > user grant > tenant override > role > default deny).
• Defining what actions must be audited and how long audit records are retained.

Inputs

Outputs

Non-negotiables

• Every franchise/tenant-scoped table has tenant_id (or franchise_id) NOT NULL with a foreign key to tenants.
• Every query on a tenant-scoped table includes tenant_id in the WHERE clause.
• tenant_id is always extracted from session or JWT — never from client input (body, query, header).
• Default authorisation decision is deny. Every action requires an explicit permission check.
• Every super-admin action that touches tenant data is audited with actor, target tenant, and justification.
• Cross-tenant access that should not exist returns 404 Not Found, never 403 Forbidden (403 confirms the resource exists).

Decision rules

Isolation model

Tenants share data schema AND tenants < ~10k AND regulated data not tenant-partitioned
    -> Shared DB, row-level tenant_id (default choice for this skill)

Strong regulatory boundary (HIPAA/PCI) per tenant
    -> Schema-per-tenant OR DB-per-tenant; app injects connection by tenant

Single very large tenant needs isolation from the rest
    -> Hybrid: shared DB for small tenants, dedicated DB for large tenant

Wrong choice failure modes:
- Shared DB without tenant_id discipline -> data leakage, impossible to audit
- DB-per-tenant at small scale -> migration and operational cost explodes
- Schema-per-tenant without tooling -> migrations drift across schemas

Panel placement of a new feature

Feature controls platform config, billing, tenant lifecycle, or impersonation
    -> /adminpanel/ (super_admin only, audited)

Feature is tenant-admin workspace (manage own tenant, staff, catalogue)
    -> /public/ root (tenant owners/staff, scoped by tenant_id)

Feature is self-service for end user (customer/member/student/patient)
    -> /memberpanel/ (own records only, scoped by tenant_id AND user_id)

Wrong choice failure modes:
- Admin feature in /public/ -> tenant users see platform levers
- Tenant feature in /memberpanel/ -> end users escalate to admin-only data
- End-user feature in /public/ -> collides with tenant admin routes

Permission resolution priority

1. actor is super_admin            -> ALLOW (always audit)
2. user_permissions.denied match   -> DENY
3. user_permissions.granted match  -> ALLOW
4. tenant_role_overrides match     -> ALLOW/DENY per is_enabled
5. role -> permission via template -> ALLOW
6. no match                        -> DENY

Wrong ordering failure mode: if role is checked before user deny, an
explicit revocation never takes effect. Always deny-before-grant at each tier.

Session prefix vs no prefix

One SaaS codebase hosts multiple apps on the same origin  -> prefix required
Only one app on origin AND no shared subdomain sessions   -> prefix optional
SSO / impersonation across apps planned                   -> prefix required

Without a prefix, two SaaS apps on the same host collide in $_SESSION and the
bug only appears in production when both apps are logged in.

Three-panel architecture

+-------------------------------------------------------------+
|                Shared Infrastructure Layer                  |
|  Data (tenant isolated) | Business Logic | Session system   |
+-------------------------------------------------------------+
         |                       |                    |
+--------v--------+    +---------v------+   +---------v-------+
|   /public/      |    |  /adminpanel/  |   |  /memberpanel/  |
| (ROOT)          |    |                |   |                 |
| Tenant Admin    |    |  Super Admin   |   |  End User       |
|  Workspace      |    |  System        |   |  Portal         |
| owner, staff    |    |  super_admin   |   |  member/student |
+-----------------+    +----------------+   +-----------------+

Important: /public/ root is the tenant-admin workspace, not an end-user panel. Confusing the two is the most common mistake.

File layout:

public/
  index.php           # Landing
  sign-in.php         # Login
  dashboard.php       # Tenant admin dashboard
  adminpanel/         # Super admin panel
    includes/
  memberpanel/        # End user portal
    includes/
  includes/           # Shared includes
  assets/

Panel definitions

Tenant isolation model

tenant_id rules per user type

Table pattern

CREATE TABLE students (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id     BIGINT UNSIGNED NOT NULL,
    first_name    VARCHAR(100) NOT NULL,
    last_name     VARCHAR(100) NOT NULL,
    email         VARCHAR(100),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    INDEX idx_tenant (tenant_id),
    INDEX idx_tenant_email (tenant_id, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Composite indexes lead with tenant_id. utf8mb4_unicode_ci everywhere. Match file case exactly (Linux file systems are case-sensitive).

Query enforcement

// Extract tenant from session/JWT. Never from client input.
$tenantId = getSession('tenant_id');

// Regular users: filter by tenant_id
$stmt = $db->prepare('SELECT * FROM students WHERE tenant_id = ? AND id = ?');
$stmt->execute([$tenantId, $studentId]);

// Super admin: cross-tenant access is allowed but always audited
if (getSession('user_type') === 'super_admin') {
    auditLog('CROSS_TENANT_ACCESS', [
        'admin_user_id'      => getSession('user_id'),
        'target_tenant_id'   => $requestedTenantId,
        'action'             => 'VIEW_STUDENTS',
        'justification'      => $request['justification'] ?? null,
    ]);
}

Session and JWT plan

Session prefix system

define('SESSION_PREFIX', 'saas_app_');  // e.g. 'school_', 'restaurant_'

setSession('user_id', $userId);      // writes $_SESSION['saas_app_user_id']
$userId = getSession('user_id');     // reads $_SESSION['saas_app_user_id']

Session cookie settings

$isHttps = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off')
    || (int) $_SERVER['SERVER_PORT'] === 443;
ini_set('session.cookie_secure', $isHttps ? '1' : '0');

JWT for mobile and API

Permission model

Priority (highest first): user deny > user grant > tenant override > role permission > default deny. See references/permission-model.md for full schema, resolution algorithm, caching strategy, and seed data.

function hasPermission(userId, tenantId, permission) {
  if (user.type === 'super_admin') {
    auditLog('PERMISSION_BYPASS', { userId, permission });
    return true;
  }
  if (userPermissions.denied(userId, tenantId, permission)) return false;
  if (userPermissions.granted(userId, tenantId, permission)) return true;
  for (const role of getUserRoles(userId, tenantId)) {
    const override = tenantRoleOverride(tenantId, role.id, permission);
    if (override !== null) return override.isEnabled;
    if (roleHasPermission(role, permission)) return true;
  }
  return false; // default deny
}

Zero-trust checklist

• tenant_id in every query on a tenant-scoped table (except super_admin with audit).
• Prepared statements only — no string concatenation into SQL.
• Permission check before every mutating operation.
• MFA required for super-admin access.
• Passwords stored as Argon2ID with per-user salt (32 chars) and server-wide pepper (64+ chars).
• Account lockout after 5 failed attempts with exponential backoff.
• Rate limiting: tenant 1000 req/min, user 100 req/min.
• HTTPS + HSTS on all panels.
• Super-admin actions audited with justification.
• Cross-tenant access returns 404, not 403.

API design

# Tenant-scoped (default)
GET    /api/v1/orders          # list within tenant
POST   /api/v1/orders          # create within tenant
GET    /api/v1/orders/{id}     # show; 404 if wrong tenant
DELETE /api/v1/orders/{id}     # delete; 404 if wrong tenant

# Super admin (cross-tenant, audited)
GET    /api/v1/admin/tenants
POST   /api/v1/admin/impersonate

Response envelope:

{ "success": true, "data": {}, "meta": { "page": 1, "total": 100 } }

Error envelope:

{ "success": false, "error": { "code": "PERMISSION_DENIED", "message": "..." } }

Audit and compliance

Always audit: all super-admin actions, impersonation, permission changes, tenant creation/suspension, data exports, failed auth, cross-tenant access attempts.

{
  "id": "uuid",
  "timestamp": "2026-04-07T10:30:00Z",
  "actor_user_id": 123,
  "actor_type": "super_admin",
  "action": "IMPERSONATE_USER",
  "target_tenant_id": 456,
  "justification": "Support request #12345",
  "ip_address": "203.0.113.1",
  "changes": { "before": {}, "after": {} }
}

Retention: security logs 1 year, audit trails 7 years, operational logs 90 days.

Monitoring alerts

• Cross-tenant access attempt (target = zero).
• Super-admin login from new IP.
• Failed auth spike > 100/min.
• Database query without tenant_id on a tenant-scoped table.
• API error rate > 5%.

Tenant lifecycle

PENDING -> ACTIVE -> SUSPENDED -> ARCHIVED

Archival is reversible until a configured retention cut-off; archived tenants are then hard-deleted via cascade.

Anti-patterns

• Client-supplied tenant identifier. $franchiseId = $_POST['franchise_id']; — any user can set this to another tenant. Fix: always getSession('tenant_id') or read from the verified JWT claim.
• Missing tenant scope on a query. SELECT * FROM students WHERE id = ? — returns any student across any tenant. Fix: SELECT * FROM students WHERE tenant_id = ? AND id = ? and treat a missing row as 404.
• Super-admin mutation without audit. deleteStudent($studentId); run from the super-admin panel with no log. Fix: wrap every super-admin mutation with auditLog('ADMIN_DELETE_STUDENT', [...]) capturing actor, target tenant, and justification.
• Returning 403 for cross-tenant access. Confirms the resource exists and enables tenant-ID enumeration. Fix: map cross-tenant misses to 404 so the existence of the resource is hidden.
• `tenant_id` nullable on a business table. tenant_id BIGINT NULL — allows rows with no owner, which every tenant query then excludes silently. Fix: tenant_id BIGINT UNSIGNED NOT NULL with a foreign key; backfill before flipping NOT NULL.
• Session keys without a prefix in a multi-app host. Two apps on the same origin overwrite each other's $_SESSION['user_id']. Fix: SESSION_PREFIX per app, always go through setSession() / getSession().
• Index order `(status, tenant_id)` instead of `(tenant_id, status)`. Query planner cannot use the composite index to isolate one tenant's slice. Fix: lead every composite index with tenant_id.
• Big-bang migration of every table at once. Raises rollback risk and produces hours of downtime. Fix: migrate one domain at a time with the six-step pattern in documentation/migration.md and verify no NULL tenant_id before flipping the column to NOT NULL.

Read next

• database-design-engineering — schema, indexes, migration discipline once the isolation model is chosen.
• api-design-first — REST conventions, auth model, and error envelope that this skill's panels depend on.
• vibe-security-skill — threat modelling, abuse cases, and auth/authz matrix that feed this skill.
• kubernetes-saas-delivery — per-tenant deploy, namespace isolation, and progressive rollout on top of this model.
• modular-saas-architecture — when the platform also composes pluggable business modules inside the tenancy defined here.
• mysql-best-practices — MySQL engine-specific execution of the schema patterns here.

References

• references/database-schema.md — tenant, user, audit, and tenant-scoped table schemas with indexes and partitioning.
• references/permission-model.md — RBAC schema, permission resolution algorithm, caching, middleware, hierarchical and conditional permissions.
• documentation/migration.md — adding tenant_id safely, zero-downtime migration, single-to-multi-tenant phases, rollback.

AI Services Isolation Addendum

Tenant isolation patterns apply one level deeper when AI is in play. The transactional patterns in this skill (RLS / schema-per-tenant / DB-per-tenant) extend to AI asset classes — vector stores, prompts, fine-tunes, eval datasets, conversation logs, retrieval caches, audit log payloads. The dedicated skill is ai-tenant-isolation-patterns.

Cross-references:

• ai-on-saas-architecture — unifying AI+SaaS architecture.
• ai-tenant-isolation-patterns — vector-store partitioning, defence-in-depth, BYOK, data-bleed test suite.
• ai-rag-multi-tenant — RAG-specific isolation.
• ai-model-gateway — enforces tenant scope at the request boundary.
• ai-prompt-injection-and-tenant-safety — prompt-layer adversarial complement.

Migration Patterns for Multi-Tenant SaaS

Adding tenant_id to Existing Tables

Safe Migration Process

-- Step 1: Add column (nullable, no default)
ALTER TABLE orders ADD COLUMN tenant_id BIGINT NULL;

-- Step 2: Backfill data from related table
UPDATE orders SET tenant_id = (
  SELECT tenant_id FROM users WHERE users.id = orders.user_id
);

-- Step 3: Verify no NULLs remain
SELECT COUNT(*) FROM orders WHERE tenant_id IS NULL;
-- Must return 0 before proceeding

-- Step 4: Make NOT NULL
ALTER TABLE orders MODIFY tenant_id BIGINT NOT NULL;

-- Step 5: Add index (critical for performance)
CREATE INDEX idx_orders_tenant ON orders(tenant_id);

-- Step 6: Add foreign key constraint
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_tenant
  FOREIGN KEY (tenant_id) REFERENCES tenants(id)
  ON DELETE CASCADE;

Zero-Downtime Migration (Large Tables)

For tables with millions of rows:

-- Step 1: Add column with default (uses metadata-only change in MySQL 8.0.13+)
ALTER TABLE large_table ADD COLUMN tenant_id BIGINT NULL;

-- Step 2: Batch backfill (avoid locking entire table)
DO $$
DECLARE
    batch_size INT := 1000;
    last_id BIGINT := 0;
BEGIN
    LOOP
        UPDATE large_table
        SET tenant_id = (
            SELECT tenant_id FROM users WHERE users.id = large_table.user_id
        )
        WHERE id > last_id AND id <= last_id + batch_size
          AND tenant_id IS NULL;

        EXIT WHEN NOT FOUND;
        last_id := last_id + batch_size;
        COMMIT; -- Release locks between batches
        PERFORM pg_sleep(0.1); -- Throttle to avoid overwhelming DB
    END LOOP;
END $$;

-- Step 3-6: Same as above

Application Code Migration

Before (no tenant isolation):

// ❌ Dangerous: No tenant filter
const order = await db.orders.findOne({id: orderId});

After (tenant-scoped):

// ✅ Safe: Tenant context required
const order = await db.orders.findOne({
  id: orderId,
  tenant_id: req.user.tenant_id
});

Migration Verification

Test checklist: 1. All tables have tenant_id column 2. All queries include tenant_id filter 3. Cross-tenant access returns 404 (not 403) 4. Foreign keys cascade properly 5. Indexes exist on tenant_id 6. No NULL tenant_id values

Query to find missing tenant_id columns:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_db'
  AND table_type = 'BASE TABLE'
  AND table_name NOT IN (
    SELECT table_name
    FROM information_schema.columns
    WHERE column_name = 'tenant_id'
  )
  AND table_name NOT IN ('tenants', 'migrations', 'system_tables');

Migrating from Single-Tenant to Multi-Tenant

Phase 1: Add Tenant Model

CREATE TABLE tenants (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    slug VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    status ENUM('PENDING', 'ACTIVE', 'SUSPENDED', 'ARCHIVED') DEFAULT 'ACTIVE',
    settings JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create first tenant for existing data
INSERT INTO tenants (slug, name, status) VALUES ('default', 'Default Tenant', 'ACTIVE');

Phase 2: Add tenant_id to Users

ALTER TABLE users ADD COLUMN tenant_id BIGINT NULL;

-- Assign all existing users to default tenant
UPDATE users SET tenant_id = (SELECT id FROM tenants WHERE slug = 'default');

ALTER TABLE users MODIFY tenant_id BIGINT NOT NULL;
CREATE INDEX idx_users_tenant ON users(tenant_id);
ALTER TABLE users ADD FOREIGN KEY (tenant_id) REFERENCES tenants(id);

Phase 3: Cascade to All Tables

Repeat migration process for each table that needs tenant isolation.

Dependency order:

1. Core entities (users, roles)
2. Transactional data (orders, invoices)
3. Reference data (products, categories)
4. Audit/logs (optional, can be global)

Phase 4: Update Application Code

Add tenant middleware:

app.use((req, res, next) => {
  if (!req.user?.tenant_id) {
    return res.status(401).json({error: 'Missing tenant context'});
  }
  req.tenantId = req.user.tenant_id;
  next();
});

Update query builder:

class TenantRepository {
  constructor(tenantId) {
    this.tenantId = tenantId;
  }

  find(conditions) {
    return db.query({
      ...conditions,
      tenant_id: this.tenantId  // Always inject
    });
  }
}

Phase 5: Testing Migration

Critical tests:

describe('Tenant Isolation', () => {
  it('prevents cross-tenant data access', async () => {
    const tenant1Order = await createOrder(tenant1);
    const tenant2User = authenticate(tenant2.user);

    const response = await tenant2User.get(`/orders/${tenant1Order.id}`);
    expect(response.status).toBe(404); // Not found (not 403)
  });

  it('requires tenant context', async () => {
    const userWithoutTenant = {id: 123}; // No tenant_id
    expect(() => getOrders(userWithoutTenant)).toThrow('Missing tenant context');
  });
});

Rolling Back Migrations

Emergency rollback procedure:

-- 1. Remove foreign key
ALTER TABLE orders DROP FOREIGN KEY fk_orders_tenant;

-- 2. Remove index
DROP INDEX idx_orders_tenant ON orders;

-- 3. Drop column
ALTER TABLE orders DROP COLUMN tenant_id;

-- 4. Restore from backup if data corruption
-- (Always test rollback in staging first!)

Safer: Feature flag rollback

Instead of dropping columns, use application-level feature flags:

const ENABLE_TENANT_ISOLATION = process.env.ENABLE_TENANT_ISOLATION === 'true';

function scopeQuery(query, user) {
  if (ENABLE_TENANT_ISOLATION && user.tenant_id) {
    return query.where('tenant_id', user.tenant_id);
  }
  return query; // Old behavior
}

Common Migration Mistakes

❌ Forgetting to verify no NULLs before making column NOT NULL

ALTER TABLE orders MODIFY tenant_id BIGINT NOT NULL;
-- Error: Column contains NULL values!

❌ Not adding indexes

-- Missing index = slow queries
SELECT * FROM orders WHERE tenant_id = 123; -- Full table scan!

❌ Trusting client-provided tenant_id during migration

// ❌ Client could fake tenant_id
const tenantId = req.body.tenant_id;

// ✅ Always from auth token
const tenantId = req.user.tenant_id;

❌ Migrating all tables at once

// BAD: Big bang migration
Migrate 50 tables overnight → High risk

// GOOD: Gradual rollout
Migrate 5 tables per week → Low risk, easier rollback

Database Schema Reference

Core Tables

Tenants

CREATE TABLE tenants (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    slug VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    status ENUM('PENDING', 'ACTIVE', 'SUSPENDED', 'ARCHIVED') NOT NULL DEFAULT 'PENDING',
    settings JSON,
    subscription_tier VARCHAR(50),
    subscription_expires_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_status (status),
    INDEX idx_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Users

CREATE TABLE users (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    tenant_id BIGINT UNSIGNED NULL COMMENT 'NULL for super_admin',
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    user_type ENUM('super_admin', 'tenant_owner', 'tenant_manager', 'tenant_staff', 'customer') NOT NULL,
    status ENUM('active', 'inactive', 'locked', 'pending') NOT NULL DEFAULT 'pending',
    failed_login_attempts SMALLINT UNSIGNED DEFAULT 0,
    last_login DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_email_tenant (email, tenant_id),
    INDEX idx_tenant_status (tenant_id, status),
    INDEX idx_user_type (user_type),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Audit Logs

CREATE TABLE audit_logs (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    tenant_id BIGINT UNSIGNED NULL COMMENT 'NULL for platform-level actions',
    actor_user_id BIGINT UNSIGNED NOT NULL,
    actor_type VARCHAR(50) NOT NULL,
    action VARCHAR(100) NOT NULL,
    target_type VARCHAR(50),
    target_id BIGINT UNSIGNED,
    justification TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_tenant_created (tenant_id, created_at),
    INDEX idx_actor (actor_user_id, created_at),
    INDEX idx_action (action, created_at),
    FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Tenant-Scoped Tables Pattern

Every business entity table follows this pattern:

CREATE TABLE orders (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    tenant_id BIGINT UNSIGNED NOT NULL,
    order_number VARCHAR(50) NOT NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    status ENUM('pending', 'confirmed', 'shipped', 'delivered', 'cancelled') NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_order_number_tenant (order_number, tenant_id),
    INDEX idx_tenant_status (tenant_id, status),
    INDEX idx_tenant_created (tenant_id, created_at),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Key patterns:

• tenant_id BIGINT UNSIGNED NOT NULL on every table
• Composite unique keys include tenant_id
• Indexes start with tenant_id for query performance
• Foreign key ON DELETE CASCADE for tenant cleanup

Indexes for Multi-Tenant Queries

-- PRIMARY pattern: (tenant_id, other_columns)
CREATE INDEX idx_tenant_status ON orders(tenant_id, status);
CREATE INDEX idx_tenant_created ON orders(tenant_id, created_at);
CREATE INDEX idx_tenant_customer ON orders(tenant_id, customer_id);

-- For queries: WHERE tenant_id = ? AND status = ?
-- Index is used efficiently

-- WRONG: (status, tenant_id) - Less efficient for multi-tenant
CREATE INDEX idx_status_tenant ON orders(status, tenant_id);

Composite Primary Keys (Alternative Pattern)

-- Instead of AUTO_INCREMENT, use composite PK
CREATE TABLE order_items (
    tenant_id BIGINT UNSIGNED NOT NULL,
    order_id BIGINT UNSIGNED NOT NULL,
    item_id BIGINT UNSIGNED NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,

    PRIMARY KEY (tenant_id, order_id, item_id),
    FOREIGN KEY (tenant_id, order_id) REFERENCES orders(tenant_id, id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Advantage: Enforces tenant isolation at PK level Disadvantage: More complex foreign keys

Data Types Best Practices

-- IDs: BIGINT UNSIGNED (never run out)
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT

-- Slugs/Codes: VARCHAR with reasonable limits
slug VARCHAR(100)
code VARCHAR(50)

-- Money: DECIMAL (exact precision)
amount DECIMAL(10,2)  -- Up to 99,999,999.99

-- Booleans: BOOLEAN or TINYINT(1)
is_active BOOLEAN DEFAULT TRUE

-- Timestamps: TIMESTAMP (UTC storage)
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

-- JSON: JSON type (MySQL 5.7.8+)
metadata JSON

-- Enums: ENUM (predefined values)
status ENUM('active', 'inactive')

Partitioning for Scale

For very large tables (100M+ rows), partition by tenant_id:

CREATE TABLE large_analytics_table (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    tenant_id BIGINT UNSIGNED NOT NULL,
    event_type VARCHAR(50),
    data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_tenant_created (tenant_id, created_at)
) ENGINE=InnoDB
PARTITION BY HASH(tenant_id)
PARTITIONS 16;

Benefit: Queries filtering by tenant_id only scan relevant partition

Common Schema Mistakes

❌ Missing tenant_id

CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255)
    -- Missing tenant_id!
);

❌ tenant_id nullable

tenant_id BIGINT NULL  -- Should be NOT NULL

❌ Wrong index order

CREATE INDEX idx_status_tenant ON orders(status, tenant_id);
-- Should be: (tenant_id, status)

❌ No CASCADE on tenant FK

FOREIGN KEY (tenant_id) REFERENCES tenants(id);
-- Missing: ON DELETE CASCADE

✅ Correct pattern

CREATE TABLE products (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(255) NOT NULL,

    INDEX idx_tenant (tenant_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

RBAC Permission Model

Database Schema

-- Global role definitions (reusable across tenants)
CREATE TABLE global_roles (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    is_system BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Permission definitions
CREATE TABLE permissions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    module VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_module (module)
);

-- Role → Permission mapping (global template)
CREATE TABLE global_role_permissions (
    global_role_id BIGINT NOT NULL,
    permission_id BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (global_role_id, permission_id),
    FOREIGN KEY (global_role_id) REFERENCES global_roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

-- User → Role assignments (tenant-scoped)
CREATE TABLE user_roles (
    user_id BIGINT NOT NULL,
    global_role_id BIGINT NOT NULL,
    tenant_id BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, global_role_id, tenant_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (global_role_id) REFERENCES global_roles(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

-- Direct user permission overrides (tenant-scoped)
CREATE TABLE user_permissions (
    user_id BIGINT NOT NULL,
    permission_id BIGINT NOT NULL,
    tenant_id BIGINT NOT NULL,
    allowed BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, permission_id, tenant_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
);

-- Tenant-level role permission overrides
CREATE TABLE tenant_role_overrides (
    tenant_id BIGINT NOT NULL,
    global_role_id BIGINT NOT NULL,
    permission_id BIGINT NOT NULL,
    is_enabled BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (tenant_id, global_role_id, permission_id),
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (global_role_id) REFERENCES global_roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
);

Permission Resolution Algorithm

/**
 * Check if user has permission within tenant context
 *
 * Priority:
 * 1. User denial (explicit) → DENY
 * 2. User grant (explicit) → ALLOW
 * 3. Tenant override → ALLOW/DENY
 * 4. Role permission → ALLOW
 * 5. Super admin → ALLOW
 * 6. Default → DENY
 */
async function hasPermission(userId, tenantId, permissionCode) {
  // Super admin bypass
  const user = await getUser(userId);
  if (user.type === 'super_admin') {
    await auditLog('PERMISSION_BYPASS', {userId, permissionCode, reason: 'super_admin'});
    return true;
  }

  // Check explicit user denial
  const userDenial = await db.user_permissions.findOne({
    user_id: userId,
    tenant_id: tenantId,
    permission_id: getPermissionId(permissionCode),
    allowed: false
  });
  if (userDenial) return false;

  // Check explicit user grant
  const userGrant = await db.user_permissions.findOne({
    user_id: userId,
    tenant_id: tenantId,
    permission_id: getPermissionId(permissionCode),
    allowed: true
  });
  if (userGrant) return true;

  // Get user's roles within tenant
  const roles = await getUserRoles(userId, tenantId);

  // Check role-based permissions with tenant overrides
  for (const role of roles) {
    const roleHasPermission = await checkRolePermission(role.id, permissionCode);
    if (!roleHasPermission) continue;

    // Check if tenant has overridden this permission for this role
    const tenantOverride = await db.tenant_role_overrides.findOne({
      tenant_id: tenantId,
      global_role_id: role.id,
      permission_id: getPermissionId(permissionCode)
    });

    if (tenantOverride) {
      return tenantOverride.is_enabled;
    }

    return true; // Role grants permission, no override exists
  }

  return false; // Default deny
}

Permission Caching

/**
 * Cache permissions for 15 minutes to reduce DB load
 */
class PermissionCache {
  constructor() {
    this.cache = new Map();
    this.TTL = 15 * 60 * 1000; // 15 minutes
  }

  getCacheKey(userId, tenantId) {
    return `${userId}:${tenantId}`;
  }

  async get(userId, tenantId, permissionCode) {
    const key = this.getCacheKey(userId, tenantId);
    const cached = this.cache.get(key);

    if (cached && Date.now() - cached.timestamp < this.TTL) {
      return cached.permissions.includes(permissionCode);
    }

    // Cache miss: Load from DB
    const permissions = await loadAllPermissions(userId, tenantId);
    this.cache.set(key, {
      permissions,
      timestamp: Date.now()
    });

    return permissions.includes(permissionCode);
  }

  invalidate(userId, tenantId) {
    const key = this.getCacheKey(userId, tenantId);
    this.cache.delete(key);
  }

  invalidateAll() {
    this.cache.clear();
  }
}

const permissionCache = new PermissionCache();

// Invalidate on permission changes
eventBus.on('user.role.changed', ({userId, tenantId}) => {
  permissionCache.invalidate(userId, tenantId);
});

eventBus.on('role.permission.changed', () => {
  permissionCache.invalidateAll(); // Affects all users with this role
});

Seed Data

-- Insert default roles
INSERT INTO global_roles (code, name, description, is_system) VALUES
('SUPER_ADMIN', 'Super Administrator', 'Platform-wide access', TRUE),
('TENANT_OWNER', 'Tenant Owner', 'Full tenant management', TRUE),
('MANAGER', 'Manager', 'Operational management', TRUE),
('STAFF', 'Staff', 'Basic access', TRUE),
('VIEWER', 'Viewer', 'Read-only access', TRUE);

-- Insert default permissions
INSERT INTO permissions (code, name, description, module) VALUES
-- User Management
('USER_VIEW', 'View Users', 'View user list and details', 'users'),
('USER_CREATE', 'Create Users', 'Add new users', 'users'),
('USER_EDIT', 'Edit Users', 'Modify user details', 'users'),
('USER_DELETE', 'Delete Users', 'Remove users', 'users'),
('USER_ASSIGN_ROLES', 'Assign Roles', 'Manage user roles', 'users'),

-- Sales
('SALE_VIEW', 'View Sales', 'View sales transactions', 'sales'),
('SALE_CREATE', 'Create Sales', 'Process sales', 'sales'),
('SALE_VOID', 'Void Sales', 'Cancel sales', 'sales'),
('SALE_REFUND', 'Process Refunds', 'Issue refunds', 'sales'),

-- Inventory
('INVENTORY_VIEW', 'View Inventory', 'View stock levels', 'inventory'),
('INVENTORY_ADJUST', 'Adjust Inventory', 'Modify stock', 'inventory'),

-- Reports
('REPORT_SALES', 'Sales Reports', 'View sales reports', 'reports'),
('REPORT_FINANCIAL', 'Financial Reports', 'View financial reports', 'reports'),

-- Settings
('SETTINGS_VIEW', 'View Settings', 'View settings', 'settings'),
('SETTINGS_EDIT', 'Edit Settings', 'Modify settings', 'settings');

-- Assign permissions to MANAGER role
INSERT INTO global_role_permissions (global_role_id, permission_id)
SELECT r.id, p.id
FROM global_roles r
CROSS JOIN permissions p
WHERE r.code = 'MANAGER'
  AND p.code IN (
    'USER_VIEW', 'SALE_VIEW', 'SALE_CREATE', 'SALE_VOID',
    'INVENTORY_VIEW', 'REPORT_SALES', 'SETTINGS_VIEW'
  );

Middleware Implementation

/**
 * Express middleware for permission checks
 */
function requirePermission(permissionCode) {
  return async (req, res, next) => {
    const {userId, tenantId} = req.auth; // From JWT/session

    const allowed = await hasPermission(userId, tenantId, permissionCode);

    if (!allowed) {
      return res.status(403).json({
        success: false,
        error: {
          code: 'PERMISSION_DENIED',
          message: `Permission required: ${permissionCode}`
        }
      });
    }

    next();
  };
}

// Usage
app.delete('/api/v1/users/:id', requirePermission('USER_DELETE'), async (req, res) => {
  // User has permission, proceed
});

Common Patterns

Hierarchical Permissions

// Grant higher permission implies lower
const PERMISSION_HIERARCHY = {
  'USER_DELETE': ['USER_EDIT', 'USER_VIEW'],
  'USER_EDIT': ['USER_VIEW'],
  'SALE_REFUND': ['SALE_VOID', 'SALE_VIEW'],
  'SALE_VOID': ['SALE_VIEW']
};

function hasPermissionOrHigher(userId, tenantId, permissionCode) {
  if (hasPermission(userId, tenantId, permissionCode)) {
    return true;
  }

  // Check if user has higher permission
  for (const [higher, lowers] of Object.entries(PERMISSION_HIERARCHY)) {
    if (lowers.includes(permissionCode) && hasPermission(userId, tenantId, higher)) {
      return true;
    }
  }

  return false;
}

Conditional Permissions

// Permission based on data ownership
async function canEditOrder(userId, tenantId, orderId) {
  const order = await db.orders.findOne({id: orderId, tenant_id: tenantId});
  if (!order) return false;

  // Basic permission check
  if (!await hasPermission(userId, tenantId, 'SALE_EDIT')) {
    return false;
  }

  // Own orders only (for staff role)
  const user = await getUser(userId);
  if (user.role === 'STAFF' && order.created_by !== userId) {
    return false;
  }

  return true;
}

Bulk Permission Checks

/**
 * Check multiple permissions at once (more efficient)
 */
async function hasAnyPermission(userId, tenantId, permissionCodes) {
  const permissions = await loadAllPermissions(userId, tenantId);
  return permissionCodes.some(code => permissions.includes(code));
}

async function hasAllPermissions(userId, tenantId, permissionCodes) {
  const permissions = await loadAllPermissions(userId, tenantId);
  return permissionCodes.every(code => permissions.includes(code));
}

// Usage
if (await hasAnyPermission(userId, tenantId, ['SALE_VIEW', 'SALE_CREATE'])) {
  // User can view OR create sales
}

SaaS Deployment Models — Decision Tree (Reference)

For full coverage, see the dedicated skill saas-deployment-models. This file is the quick-reference table.

Five Models

Decision Rules (in order)

1. Any tenant has strict per-tenant regulatory boundary (HIPAA, PCI-per-tenant, sovereign EU data) → Full Stack Silo for those tenants (Hybrid if mixed; Mixed if only certain services).

2. Migrating a legacy single-customer codebase to SaaS quickly → Full Stack Silo as starting point; refactor toward Mixed Mode over 12-24 months.

3. > 10,000 tenants expected → Pool or Pod (Pool if no geography/residency; Pod if either).

4. Clear premium tier ($50K+ ACV) with isolation expectations → Hybrid Full Stack OR Mixed Mode with siloed premium services.

5. Default for new B2B SaaS → Mixed Mode — start pooled, silo as compliance / noisy-neighbor demands.

Per-Service Silo/Pool Map (Default for B2B Mixed Mode)

What the Choice Drives

• Routing layer: silo needs per-tenant ingress; pool routes by JWT/subdomain.
• Deployment automation: silo = rolling waves; pool = single shot + canary.
• Cost attribution: silo = native cloud per-tenant; pool = apportionment.
• Onboarding latency: silo = minutes (provisioning); pool = seconds.
• Blast radius: silo = naturally bounded; pool = global; pod = per-pod.

Migration Paths

• Pool → Mixed: silo noisy/regulated services; rest stay pool.
• Pool → Hybrid: add premium tier with siloed stacks.
• Mixed → Pod: split big pool by tenant size / geography.
• Silo → Pool: rare; usually needs better isolation primitives first.

See Also

• saas-deployment-models — full skill.
• Golding, Building Multi-Tenant SaaS Architectures, Ch.3.

Tenant Context Propagation — Reference

Tenant context is the spine of a multi-tenant SaaS — it travels with every request, every log line, every metric, every queue message, every cache key. Get this right and tenant isolation falls out naturally. Get it wrong and isolation bugs are inevitable.

What "Tenant Context" Is

The runtime carrier of which tenant is being served right now. At minimum:

• tenant_id — unique tenant identifier.
• user_id — the user acting on behalf of the tenant.
• role / permissions — what the user can do in this tenant.
• plan / tier / entitlements — what the tenant is allowed.
• correlation_id — request-scoped tracing identifier.
• region / pod (if multi-region/pod) — physical placement of the data.

Token Format (JWT)

Header
  alg: RS256 | EdDSA
  kid: rotation-key-id
Payload
  sub: user_id
  aud: api.example.com
  iss: auth.example.com
  iat, exp (15 min)
  tenant_id: ten_456
  user_id: usr_789
  role: admin
  plan: pro
  entitlements: { features: [...], limits: { ... } }
  pod: us-west-2 (if applicable)
  correlation_id: req_abc123 (optional; usually a separate header)
Signature

Issued at login or tenant switch; verified by every service on every request.

Critical rule: tenant_id comes from the verified JWT claim. Never from request body, query string, or unverified header. The most common SaaS isolation bug is reading tenant_id from POST body — any user can then forge it.

Propagation Map

Middleware Patterns

HTTP middleware (extracts tenant context, stores per-request)

def tenant_context_middleware(request, call_next):
    token = extract_bearer(request)
    claims = verify_jwt(token)            # raises on invalid
    ctx = TenantContext(
        tenant_id=claims['tenant_id'],
        user_id=claims['user_id'],
        role=claims['role'],
        plan=claims['plan'],
        entitlements=claims['entitlements'],
        correlation_id=request.headers.get('X-Correlation-Id') or generate(),
    )
    TenantContext.set(ctx)              # contextvar / threadlocal
    try:
        response = call_next(request)
    finally:
        TenantContext.clear()
    return response

Database middleware (auto-inject tenant_id filter)

# ORM / query layer
def add_tenant_predicate(query):
    ctx = TenantContext.current()
    if not ctx.is_super_admin:
        query = query.filter(tenant_id=ctx.tenant_id)
    return query

Or use Postgres Row-Level Security:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders USING (tenant_id = current_setting('app.tenant_id')::int);

-- On each request:
SET LOCAL app.tenant_id = '456';

Logging

def log_format(record):
    ctx = TenantContext.current()
    record['tenant_id'] = ctx.tenant_id if ctx else None
    record['user_id'] = ctx.user_id if ctx else None
    record['correlation_id'] = ctx.correlation_id if ctx else None

Queue envelope

{
  "tenant_id": "ten_456",
  "user_id": "usr_789",
  "correlation_id": "req_abc",
  "idempotency_key": "evt_xyz",
  "occurred_at": "2026-05-11T10:23:00Z",
  "event_type": "invoice.created",
  "payload": { ... }
}

Super-Admin Caveat

Super-admin actions touch cross-tenant data. They have a different propagation rule:

• JWT has super_admin: true and no tenant_id (or has a working tenant_id chosen explicitly).
• DB middleware skips the tenant predicate (or RLS uses a bypass policy).
• Every access writes an audit log entry with actor_user_id, target_tenant_id, action, justification.

Anti-Patterns

• Reading `tenant_id` from POST body — forgeable.
• Storing `tenant_id` in app-level globals (not request-scoped) — leaks across requests in async runtimes.
• Forgetting to propagate `tenant_id` into async work — worker logs and metrics are tenantless.
• Omitting `tenant_id` from cache keys — cross-tenant cache hits.
• No `correlation_id` — debugging one request across services becomes archaeology.
• `tenant_id` as a string in some places, int in others — silent type-coercion bugs.
• Logging full JWT — secret leakage. Log claims minus sub/exp or hash.