multi-tenancy-patterns

-- Every tenant-scoped table has tenant_id
CREATE TABLE projects (
  id UUID PRIMARY KEY,
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ALWAYS filter by tenant_id
CREATE INDEX idx_projects_tenant ON projects(tenant_id);

-- Enable RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

-- Policy: Users can only see their tenant's data
CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- Policy for insert (ensure new rows have correct tenant)
CREATE POLICY tenant_insert ON projects
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

// Middleware to set tenant context
async function tenantMiddleware(req, res, next) {
  const tenantId = req.user.tenantId;
  
  // Set PostgreSQL session variable
  await db.query(`SET app.current_tenant = '${tenantId}'`);
  
  next();
}

-- Use SECURITY DEFINER functions for complex lookups
CREATE FUNCTION get_user_tenant_id() RETURNS uuid AS $$
  SELECT tenant_id FROM users WHERE id = current_setting('app.current_user')::uuid
$$ LANGUAGE sql SECURITY DEFINER STABLE;

CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = get_user_tenant_id());

CREATE TABLE tenants (
  id UUID PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  slug VARCHAR(63) UNIQUE NOT NULL,  -- For subdomain/URL
  plan VARCHAR(50) DEFAULT 'free',
  settings JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE memberships (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  tenant_id UUID REFERENCES tenants(id),
  role VARCHAR(50) NOT NULL DEFAULT 'member',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, tenant_id)
);

-- Users can belong to multiple tenants
CREATE INDEX idx_memberships_user ON memberships(user_id);
CREATE INDEX idx_memberships_tenant ON memberships(tenant_id);

const roles = {
  owner: ['*'],  // All permissions
  admin: ['members:read', 'members:write', 'settings:read', 'settings:write', 'projects:*'],
  member: ['projects:read', 'projects:write'],
  viewer: ['projects:read'],
};

function hasPermission(membership: Membership, permission: string): boolean {
  const rolePerms = roles[membership.role];
  
  return rolePerms.some(perm => {
    if (perm === '*') return true;
    if (perm.endsWith(':*')) {
      return permission.startsWith(perm.replace(':*', ':'));
    }
    return perm === permission;
  });
}

// React context for current tenant
const TenantContext = createContext<Tenant | null>(null);

function TenantProvider({ children }) {
  const [tenant, setTenant] = useState<Tenant | null>(null);
  
  // Load from URL slug or stored preference
  useEffect(() => {
    const slug = window.location.pathname.split('/')[1];
    loadTenant(slug).then(setTenant);
  }, []);
  
  return (
    <TenantContext.Provider value={tenant}>
      {children}
    </TenantContext.Provider>
  );
}

// Resolve tenant from subdomain, path, or header
function resolveTenant(req: Request): string | null {
  // Option 1: Subdomain (acme.yourapp.com)
  const subdomain = req.hostname.split('.')[0];
  if (subdomain !== 'www' && subdomain !== 'app') {
    return subdomain;
  }
  
  // Option 2: Path prefix (/orgs/acme/...)
  const pathMatch = req.path.match(/^\/orgs\/([^/]+)/);
  if (pathMatch) return pathMatch[1];
  
  // Option 3: Header
  return req.headers['x-tenant-id'] as string | null;
}

CREATE TABLE invitations (
  id UUID PRIMARY KEY,
  tenant_id UUID REFERENCES tenants(id),
  email VARCHAR(255) NOT NULL,
  role VARCHAR(50) NOT NULL DEFAULT 'member',
  token VARCHAR(255) UNIQUE NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL,
  accepted_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

async function inviteMember(tenantId: string, email: string, role: string) {
  const token = generateSecureToken();
  
  await db.invitations.create({
    data: {
      tenant_id: tenantId,
      email,
      role,
      token,
      expires_at: addDays(new Date(), 7),
    },
  });
  
  await sendEmail('invitation', email, {
    inviteUrl: `https://app.yourapp.com/invite/${token}`,
  });
}

async function acceptInvitation(token: string, userId: string) {
  const invite = await db.invitations.findUnique({ where: { token } });
  
  if (!invite || invite.expires_at < new Date()) {
    throw new Error('Invalid or expired invitation');
  }
  
  await db.memberships.create({
    data: {
      user_id: userId,
      tenant_id: invite.tenant_id,
      role: invite.role,
    },
  });
  
  await db.invitations.update({
    where: { id: invite.id },
    data: { accepted_at: new Date() },
  });
}

Shared (no tenant_id):
- users (shared identity across tenants)
- plans (pricing tiers)
- features (feature flags)

Tenant-Scoped (has tenant_id):
- projects
- documents
- team_settings
- audit_logs

// User exists once, belongs to many tenants
interface User {
  id: string;
  email: string;
  name: string;
}

// Membership ties user to tenant with role
interface Membership {
  userId: string;
  tenantId: string;
  role: 'owner' | 'admin' | 'member' | 'viewer';
}

// Current context includes both
interface AuthContext {
  user: User;
  membership: Membership;
  tenant: Tenant;
}