Document Workflow System

Objective

Create a document workflow system with approval routing, task assignment, and audit trails. This enables managing document lifecycles through review and approval processes.

Step 1: Create Workflow Templates Table

Define workflow templates.

CREATE TABLE workflow_templates (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    document_type VARCHAR(50),
    total_steps INTEGER DEFAULT 1,
    sla_hours INTEGER,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Workflow Steps Table

Define workflow steps.

CREATE TABLE workflow_steps (
    id INTEGER PRIMARY KEY,
    workflow_id INTEGER NOT NULL,
    step_order INTEGER NOT NULL,
    step_name VARCHAR(100) NOT NULL,
    step_type VARCHAR(50) NOT NULL,
    approver_role VARCHAR(100),
    is_required BOOLEAN DEFAULT TRUE,
    timeout_hours INTEGER,
    FOREIGN KEY (workflow_id) REFERENCES workflow_templates(id)
);

Step 3: Create Document Instances Table

Track documents in workflow.

CREATE TABLE workflow_documents (
    id INTEGER PRIMARY KEY,
    workflow_id INTEGER NOT NULL,
    document_pdf PDF,
    document_title VARCHAR(255) NOT NULL,
    submitted_by VARCHAR(100) NOT NULL,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    current_step INTEGER DEFAULT 1,
    status VARCHAR(20) DEFAULT 'pending',
    priority VARCHAR(20) DEFAULT 'normal',
    due_date TIMESTAMP,
    completed_at TIMESTAMP,
    FOREIGN KEY (workflow_id) REFERENCES workflow_templates(id)
);

Step 4: Create Tasks Table

Track workflow tasks.

CREATE TABLE workflow_tasks (
    id INTEGER PRIMARY KEY,
    document_id INTEGER NOT NULL,
    step_id INTEGER NOT NULL,
    assigned_to VARCHAR(100) NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    action VARCHAR(50),
    action_date TIMESTAMP,
    comments TEXT,
    status VARCHAR(20) DEFAULT 'pending',
    FOREIGN KEY (document_id) REFERENCES workflow_documents(id),
    FOREIGN KEY (step_id) REFERENCES workflow_steps(id)
);

Step 5: Insert Workflow Templates

Add sample workflows.

INSERT INTO workflow_templates (id, name, description, document_type, total_steps, sla_hours) VALUES
    (1, 'Contract Approval', 'Approval workflow for new contracts', 'contract', 4, 72),
    (2, 'Expense Approval', 'Expense report approval process', 'expense', 2, 48),
    (3, 'Policy Review', 'Policy document review and approval', 'policy', 3, 120),
    (4, 'Purchase Requisition', 'Purchase request approval chain', 'purchase', 3, 24);

Step 6: Insert Workflow Steps

Define steps for each workflow.

INSERT INTO workflow_steps (id, workflow_id, step_order, step_name, step_type, approver_role, is_required, timeout_hours) VALUES
    -- Contract Approval steps
    (1, 1, 1, 'Legal Review', 'review', 'legal_team', TRUE, 24),
    (2, 1, 2, 'Finance Review', 'review', 'finance_team', TRUE, 24),
    (3, 1, 3, 'Department Approval', 'approval', 'department_head', TRUE, 24),
    (4, 1, 4, 'Executive Approval', 'approval', 'executive', TRUE, 24),
    -- Expense Approval steps
    (5, 2, 1, 'Manager Approval', 'approval', 'manager', TRUE, 24),
    (6, 2, 2, 'Finance Processing', 'process', 'finance_team', TRUE, 24),
    -- Policy Review steps
    (7, 3, 1, 'Subject Matter Review', 'review', 'sme', TRUE, 48),
    (8, 3, 2, 'Compliance Review', 'review', 'compliance', TRUE, 48),
    (9, 3, 3, 'Executive Approval', 'approval', 'executive', TRUE, 24),
    -- Purchase Requisition steps
    (10, 4, 1, 'Budget Verification', 'review', 'finance_team', TRUE, 8),
    (11, 4, 2, 'Manager Approval', 'approval', 'manager', TRUE, 8),
    (12, 4, 3, 'Procurement Processing', 'process', 'procurement', TRUE, 8);

Step 7: Insert Document Instances

Add documents in workflow.

INSERT INTO workflow_documents (id, workflow_id, document_title, submitted_by, submitted_at, current_step, status, priority, due_date) VALUES
    (1, 1, 'Vendor Agreement - TechCorp', 'sales_rep', '2024-01-15 09:00:00', 2, 'in_progress', 'high', '2024-01-18 09:00:00'),
    (2, 1, 'Service Contract - ClientXYZ', 'account_mgr', '2024-01-16 14:00:00', 1, 'in_progress', 'normal', '2024-01-19 14:00:00'),
    (3, 2, 'Expense Report - Q1 Travel', 'john_emp', '2024-01-17 10:00:00', 2, 'in_progress', 'normal', '2024-01-19 10:00:00'),
    (4, 2, 'Expense Report - Conference', 'jane_emp', '2024-01-18 08:00:00', 1, 'pending', 'low', '2024-01-20 08:00:00'),
    (5, 3, 'Remote Work Policy Update', 'hr_manager', '2024-01-10 11:00:00', 3, 'in_progress', 'high', '2024-01-15 11:00:00'),
    (6, 4, 'Server Equipment Purchase', 'it_manager', '2024-01-18 15:00:00', 1, 'pending', 'urgent', '2024-01-19 15:00:00');

Step 8: Insert Workflow Tasks

Add task records.

INSERT INTO workflow_tasks (id, document_id, step_id, assigned_to, assigned_at, action, action_date, comments, status) VALUES
    -- Contract 1 tasks
    (1, 1, 1, 'legal_counsel', '2024-01-15 09:30:00', 'approved', '2024-01-15 16:00:00', 'Terms are acceptable', 'completed'),
    (2, 1, 2, 'finance_analyst', '2024-01-15 16:30:00', NULL, NULL, NULL, 'pending'),
    -- Contract 2 tasks
    (3, 2, 1, 'legal_counsel', '2024-01-16 14:30:00', NULL, NULL, NULL, 'pending'),
    -- Expense 1 tasks
    (4, 3, 5, 'dept_manager', '2024-01-17 10:30:00', 'approved', '2024-01-17 14:00:00', 'Approved for reimbursement', 'completed'),
    (5, 3, 6, 'finance_processor', '2024-01-17 14:30:00', NULL, NULL, NULL, 'pending'),
    -- Expense 2 tasks
    (6, 4, 5, 'dept_manager', '2024-01-18 08:30:00', NULL, NULL, NULL, 'pending'),
    -- Policy tasks
    (7, 5, 7, 'hr_specialist', '2024-01-10 11:30:00', 'approved', '2024-01-11 15:00:00', 'Content reviewed and updated', 'completed'),
    (8, 5, 8, 'compliance_officer', '2024-01-11 15:30:00', 'approved', '2024-01-13 10:00:00', 'Meets regulatory requirements', 'completed'),
    (9, 5, 9, 'ceo', '2024-01-13 10:30:00', NULL, NULL, NULL, 'pending');

Step 9: Get Pending Tasks

View all pending workflow tasks.

SELECT
    wd.document_title,
    wt.name as workflow,
    ws.step_name,
    wk.assigned_to,
    wk.assigned_at,
    wd.priority,
    wd.due_date
FROM workflow_tasks wk
INNER JOIN workflow_documents wd ON wk.document_id = wd.id
INNER JOIN workflow_templates wt ON wd.workflow_id = wt.id
INNER JOIN workflow_steps ws ON wk.step_id = ws.id
WHERE wk.status = 'pending'
ORDER BY
    CASE wd.priority
        WHEN 'urgent' THEN 1
        WHEN 'high' THEN 2
        WHEN 'normal' THEN 3
        ELSE 4
    END,
    wd.due_date;

Step 10: Document Status Overview

Get workflow progress for all documents.

SELECT
    wd.document_title,
    wt.name as workflow,
    wd.current_step || '/' || wt.total_steps as progress,
    wd.status,
    wd.priority,
    wd.submitted_by,
    wd.due_date
FROM workflow_documents wd
INNER JOIN workflow_templates wt ON wd.workflow_id = wt.id
ORDER BY
    CASE wd.status
        WHEN 'pending' THEN 1
        WHEN 'in_progress' THEN 2
        ELSE 3
    END,
    wd.due_date;

Step 11: Overdue Items

Find documents past due date.

SELECT
    wd.document_title,
    wt.name as workflow,
    ws.step_name as current_step,
    wd.due_date,
    CURRENT_TIMESTAMP as now_time,
    'OVERDUE' as alert
FROM workflow_documents wd
INNER JOIN workflow_templates wt ON wd.workflow_id = wt.id
INNER JOIN workflow_steps ws ON wt.id = ws.workflow_id AND ws.step_order = wd.current_step
WHERE wd.status IN ('pending', 'in_progress')
  AND wd.due_date < CURRENT_TIMESTAMP
ORDER BY wd.due_date;

Step 12: Workflow Performance

Analyze workflow completion times.

SELECT
    wt.name as workflow,
    COUNT(*) as total_documents,
    COUNT(CASE WHEN wd.status = 'completed' THEN 1 END) as completed,
    COUNT(CASE WHEN wd.status IN ('pending', 'in_progress') THEN 1 END) as in_progress,
    AVG(CASE
        WHEN wd.completed_at IS NOT NULL
        THEN EXTRACT(EPOCH FROM (wd.completed_at - wd.submitted_at)) / 3600
    END) as avg_hours_to_complete
FROM workflow_templates wt
LEFT JOIN workflow_documents wd ON wt.id = wd.workflow_id
GROUP BY wt.id, wt.name
ORDER BY total_documents DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS workflow_tasks;
DROP TABLE IF EXISTS workflow_documents;
DROP TABLE IF EXISTS workflow_steps;
DROP TABLE IF EXISTS workflow_templates;

Expected Outcomes

• Workflows defined with steps
• Documents routed automatically
• Tasks tracked per approver
• Overdue items identified
• Performance metrics available

Task Statuses

Key Concepts Learned

• Multi-step workflow design
• Task assignment routing
• SLA monitoring
• Audit trail tracking
• Performance analysis