CPM Project Scheduler

A Critical Path Method implementation for project scheduling. This mock stressed parameterized queries and relationship traversal, driving the execute_with_args and query_with_args features.

Domain Overview

The CPM (Critical Path Method) scheduler implements project management concepts:

• Projects - Top-level container with start/end dates
• Activities - Tasks with duration and effort estimates
• Dependencies - Predecessor relationships between activities
• Critical Path - Longest path determining minimum project duration
• Slack/Float - Available delay time for non-critical activities

Schema

projects (id, name, start_date, end_date, status)
activities (id, project_id, name, duration, early_start, early_finish, late_start, late_finish, slack)
dependencies (id, predecessor_id, successor_id, dependency_type)

Friction Points & Solutions

Parameterized Queries

Building dependency graphs required many queries with different parameters. String concatenation was error-prone:

-- Before: String concatenation (error-prone, SQL injection risk)
db.query ("SELECT * FROM activities WHERE project_id = " + proj_id.out)

-- After: Parameterized (safe, clean)
db.query_with_args ("SELECT * FROM activities WHERE project_id = ?", <<proj_id>>)

Dependency Graph Traversal

predecessors (a_activity_id: INTEGER_64): SIMPLE_SQL_RESULT
        -- All activities that must complete before this one
    do
        Result := db.query_with_args ("[
            SELECT a.* FROM activities a
            JOIN dependencies d ON d.predecessor_id = a.id
            WHERE d.successor_id = ?
        ]", <<a_activity_id>>)
    end

successors (a_activity_id: INTEGER_64): SIMPLE_SQL_RESULT
        -- All activities that depend on this one
    do
        Result := db.query_with_args ("[
            SELECT a.* FROM activities a
            JOIN dependencies d ON d.successor_id = a.id
            WHERE d.predecessor_id = ?
        ]", <<a_activity_id>>)
    end

CPM Algorithm Implementation

Forward Pass (Early Start/Finish)

forward_pass (a_project_id: INTEGER_64)
        -- Calculate early start and finish for all activities
    local
        activities: SIMPLE_SQL_RESULT
        max_predecessor_finish: INTEGER
    do
        -- Process in topological order
        activities := db.query_with_args ("SELECT * FROM activities WHERE project_id = ? ORDER BY id", <<a_project_id>>)

        across activities as act loop
            -- Early start = max(early_finish of all predecessors)
            max_predecessor_finish := max_predecessor_early_finish (act.integer_64_value ("id"))

            db.update ("activities")
                .set ("early_start", max_predecessor_finish)
                .set ("early_finish", max_predecessor_finish + act.integer_value ("duration"))
                .where ("id = ?", act.integer_64_value ("id"))
                .execute
        end
    end

Backward Pass (Late Start/Finish)

backward_pass (a_project_id: INTEGER_64)
        -- Calculate late start and finish, then slack
    local
        activities: SIMPLE_SQL_RESULT
        min_successor_start, slack: INTEGER
    do
        -- Process in reverse topological order
        activities := db.query_with_args ("SELECT * FROM activities WHERE project_id = ? ORDER BY id DESC", <<a_project_id>>)

        across activities as act loop
            -- Late finish = min(late_start of all successors)
            min_successor_start := min_successor_late_start (act.integer_64_value ("id"))

            slack := min_successor_start - act.integer_value ("duration") - act.integer_value ("early_start")

            db.update ("activities")
                .set ("late_finish", min_successor_start)
                .set ("late_start", min_successor_start - act.integer_value ("duration"))
                .set ("slack", slack)
                .where ("id = ?", act.integer_64_value ("id"))
                .execute
        end
    end

Critical Path Extraction

critical_path (a_project_id: INTEGER_64): SIMPLE_SQL_RESULT
        -- Activities with zero slack (on critical path)
    do
        Result := db.query_with_args ("SELECT * FROM activities WHERE project_id = ? AND slack = 0 ORDER BY early_start", <<a_project_id>>)
    end

project_duration (a_project_id: INTEGER_64): INTEGER
        -- Total project duration (max early_finish)
    do
        Result := db.query_with_args ("SELECT MAX(early_finish) as duration FROM activities WHERE project_id = ?", <<a_project_id>>).first.integer_value ("duration")
    end

Lessons Learned

Test Data

The CPM mock includes a realistic project with:

• 51 activities across multiple phases
• 65 dependency relationships
• Multiple parallel paths
• Clear critical path for validation

Source Code

• CPM_APP - Main application class
• CPM_PROJECT - Project entity
• CPM_ACTIVITY - Activity entity with timing calculations
• CPM_DEPENDENCY - Dependency relationships