+-------------------------------------------------------------+
|                        USER IDENTITY                        |
|           (Auth Provider: Clerk / Supabase / WorkOS)        |
|              ID: usr_101  |  Email: alex@acme.com           |
+-------------------------------------------------------------+
                               |
        +----------------------+----------------------+
        |                                             |
        v                                             v
+-----------------------+                 +-----------------------+
|   MEMBERSHIP RECORD   |                 |   MEMBERSHIP RECORD   |
|  Org: Acme Corp       |                 |  Org: Beta Logistics  |
|  Role: Workspace Admin|                 |  Role: Auditor/Viewer |
+-----------------------+                 +-----------------------+
        |                                             |
        v                                             v
+-----------------------+                 +-----------------------+
|  ORGANIZATION ASSETS  |                 |  ORGANIZATION ASSETS  |
|  - Projects           |                 |  - Invoices           |
|  - API Keys           |                 |  - Reports            |
|  - Billing / Seats    |                 |  - Audit Logs         |
+-----------------------+                 +-----------------------+

-- 1. Organizations (The Tenant Boundary)
CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug VARCHAR(64) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    billing_plan VARCHAR(32) DEFAULT 'starter' NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

-- 2. Users (Global Human Identity)
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    full_name VARCHAR(255),
    is_superadmin BOOLEAN DEFAULT FALSE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

-- 3. Workspace Memberships & Granular RBAC
CREATE TYPE org_role AS ENUM ('owner', 'admin', 'member', 'viewer');
CREATE TYPE invite_status AS ENUM ('active', 'invited', 'suspended');

CREATE TABLE memberships (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role org_role DEFAULT 'member' NOT NULL,
    status invite_status DEFAULT 'invited' NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    CONSTRAINT uq_user_organization UNIQUE (organization_id, user_id)
);

-- 4. Sample Tenant Resource
CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    created_by_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
    name VARCHAR(255) NOT NULL,
    encrypted_payload JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL
);

-- Ensure fast composite index lookups for organization filtering
CREATE INDEX idx_projects_org_created ON projects(organization_id, created_at DESC);
CREATE INDEX idx_memberships_user ON memberships(user_id, status);

1. Path-Based:        api.saas.com/v1/orgs/:org_id/projects
2. Header-Based:      api.saas.com/v1/projects  [Header: x-organization-id: org_123]
3. Subdomain-Based:   acme.saas.com/api/v1/projects

import { Request, Response, NextFunction } from 'express';
import { db } from '../db';
import { memberships, organizations } from '../db/schema';
import { eq, and } from 'drizzle-orm';

export interface AuthenticatedTenantRequest extends Request {
  user: { id: string; email: string };
  tenant: {
    organizationId: string;
    role: 'owner' | 'admin' | 'member' | 'viewer';
  };
}

export function requireTenantMembership(allowedRoles?: Array<'owner' | 'admin' | 'member' | 'viewer'>) {
  return async (req: Request, res: Response, next: NextFunction) => {
    try {
      const userId = req.headers['x-user-id'] as string; // Set by upstream JWT Auth Gate
      const requestedOrgId = req.headers['x-organization-id'] as string;

      if (!userId || !requestedOrgId) {
        return res.status(401).json({ error: 'Missing authenticated user or organization context' });
      }

      // Query the active membership join
      const [membership] = await db
        .select({
          orgId: memberships.organizationId,
          role: memberships.role,
          status: memberships.status,
        })
        .from(memberships)
        .where(
          and(
            eq(memberships.userId, userId),
            eq(memberships.organizationId, requestedOrgId),
            eq(memberships.status, 'active')
          )
        )
        .limit(1);

      if (!membership) {
        return res.status(403).json({
          error: 'Access Denied: You do not have an active membership in this organization'
        });
      }

      // RBAC Gate Check
      if (allowedRoles && !allowedRoles.includes(membership.role)) {
        return res.status(403).json({
          error: `Insufficient Permissions: Requires one of [${allowedRoles.join(', ')}]`
        });
      }

      // Attach tenant context to the request pipeline
      (req as AuthenticatedTenantRequest).tenant = {
        organizationId: membership.orgId,
        role: membership.role,
      };

      return next();
    } catch (error) {
      return res.status(500).json({ error: 'Internal tenancy resolution failure' });
    }
  };
}

+-----------------------+
| Corporate Employee    |
| logs into Okta        |
+-----------+-----------+
            |
            v (SAML Assertion with Work Email)
+-------------------------------------------------------------+
| SaaS Authentication Router                                 |
| 1. Checks Email Domain (@acme.com)                          |
| 2. Resolves Organization: Acme Corp (org_987)               |
| 3. Verifies SAML Certificate & Signature                    |
| 4. JIT (Just-In-Time) Provisions User & Active Membership   |
+-------------------------------------------------------------+
            |
            v
+-------------------------------------------------------------+
| Logged in with 'Member' Role into Acme Corp Workspace       |
+-------------------------------------------------------------+