Windows Sharepoint Services v3 makes use of the Windows Workflow Foundation (WF) so that administrators can associate workflows with content items.  Microsoft Office Sharepoint Server 2007 sits on top of WSS and adds a few built-in workflows to cover common document management scenarios.

Because WF is underpinning this stuff, you can customize WSS or MOSS with your own workflow definitions.

For simple, declarative, rule-based workflow definitions you should use the Microsoft Office Sharepoint Designer 2007.  If you need something more sophisticated, and are prepared to get your hands dirty, you can also use the WF designer extensions for Visual Studio 2005.  Here's a rough outline of the process you'll need to follow if you choose that route...

1. Install the VS2005 Extensions for WF.
2. Install either the WSS3 Workflow Developer Starter Kit or MOSS2007 Enterprise Content Management Starter Kit.  This will install the necessary project templates in VS2005.
3. Create a new Sequential Workflow project.
4. Sequential workflows for Sharepoint must start with the OnWorkflowActivated activity.
5. The rest of the workflow will typically follow a pattern: Create a task, loop while that task changes, and mark the task as complete when the task's properties match the desired state.
6. In the course of the workflow, you can do anything enabled by WF, such as calling web services, invoking code, or involving any custom activities you might have written, bought, or found.
7. You need to create some InfoPath forms for the initiation of the task, and to allow people to update the state of the task when they complete their part of the workflow.  These forms need to be published to the server where the workflow will run.  More details here.
8. You have to deploy your workflow, which means strong-name signing it and creating a couple of XML files to describe your workflow.  See here for more details.

You can probably see that the freedom to do anything you like from a WF workflow comes at a price.  You definitely need to be a developer.  Most everyday workflow scenarios can be described perfectly well through the Sharepoint Designer tool, and it's possible for non-developers to use it.