Project Review Scheduler – Real-World Implementation Report

Executive Summary

Project accountability, transparency, and timely reviews are necessary for business success in technical environments. However, our company still relies on spreadsheets and manual reminders and is prone to error, delay, and miscommunication. The Project Review Scheduler is a Python-based solution developed in Jupyter Notebook to automate the review process, assign reviewers based on department and workload, and notify them ahead of due dates via email. With scalability in mind, it simulated but realistic sample data, secure credential storage, and structured outputs. This report outlines the system’s motivation, structure, data model, script logic, and benefits.

Problem Statement / Motivation

Manual project review workflows are inefficient. In our tech company, reviews often fall through the cracks because there is no centralized scheduling system. Department leads manually track which projects need reviews and email potential reviewers, often at the last minute. This leads to reviewer fatigue, uneven workloads, missed deadlines, and a lack of audit history. We need a repeatable, automated, and scalable process that ensures project reviews happen on time and that responsibilities are assigned and documented.

Project Goals

- Automate the identification of projects that require review based on review frequency 
  and last review date.

- Assign reviewers based on current workload and department alignment.

- Send review assignment emails to reviewers with due dates and relevant notes.

- Maintain logs of all review assignments for accountability and audit.s

- Store reviewer and project data in structured CSV files for interoperability.

- Generate professional documentation and entity-relationship diagrams (ERDs).

System Overview / Architecture

This system is implemented using Python in a Jupyter Notebook environment. Project and reviewer data are stored in CSV files. The core script uses Pandas for data manipulation, Faker for generating realistic demo data, dotenv for secure email credential loading, and smtplib to send emails to reviewers.

Tools:

-  Python 3.x

-  Jupyter Notebook

-  pandas, faker, smtplib, dotenv

-  Draw.io for ERD creation

Data Flow

image.png

Entity-Relationship Diagram (ERD)

This system follows a normalized relational structure:

Tables:

    Projects: Each project is reviewed on a scheduled frequency.

        - project_id (PK)

        - project_name, start_date, last_review_date, review_frequency_years, next_review_date, status

        - Reviewers: Users eligible to review projects.

        - reviewer_id (PK), name, email, department_id (FK), current_load

    Departments: Organizational groups reviewers belong to.

        - department_id (PK), department_name

    Reviews: Core junction table linking projects and reviewers.

        - review_id (PK), project_id (FK), reviewer_id (FK), status_id (FK), scheduled_date, 
          completion_date,review_notes, assigned_date

    ReviewStatus: Lookup values for review status.

        - status_id (PK), status_name

    ReviewHistory: Audit trail of review actions.

        - history_id (PK), review_id (FK), action, timestamp, performed_by

image.png

Key Libraries

# Import Libraries
import pandas as pd
from faker import Faker
import random
from datetime import timedelta, datetime
from dotenv import load_dotenv
import smtplib
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import os


fake = Faker()
Faker.seed(42)
random.seed(42)

Generate Projects, Reviewers dataFrameCSV files

# === Generate Projects ===
def generate_projects(n=50):
    projects = []
    departments = ['Engineering', 'QA', 'HR', 'Finance', 'IT']
    for i in range(1, n + 1):
        start_date = fake.date_between(start_date='-2y', end_date='-6m')
        last_review = start_date + timedelta(days=random.randint(180, 365))
        frequency = random.choice([1, 2])
        projects.append({
            "Project ID": f"P{i:03}",
            "Project Name": fake.bs().title(),
            "Start Date": start_date,
            "Last Review Date": last_review,
            "Review Frequency (Years)": frequency,
            "Department": random.choice(departments)
        })
    return pd.DataFrame(projects)

# === Generate Reviewers ===
def generate_reviewers(n=10):
    departments = ['Engineering', 'QA', 'HR', 'Finance', 'IT']
    reviewers = []
    for i in range(1, n + 1):
        reviewers.append({
            "Reviewer ID": f"R{i:03}",
            "Name": fake.name(),
            "Email": fake.email(),
            "Department": random.choice(departments),
            "Current Load": random.randint(0, 5)
        })
    return pd.DataFrame(reviewers)



# Generate Data
projects_df = generate_projects()
reviewers_df = generate_reviewers()


# Save to CSV
projects_df.to_csv("projects.csv", index=False)
reviewers_df.to_csv("reviewers.csv", index=False)


print("Data generated and saved as CS")


Data generated and saved as CS

Display reviewers_df

reviewers_df
Reviewer ID Name Email Department Current Load
0 R001 Jennifer Bass IT 0
1 R002 Julie Ramos IT 2
2 R003 Bradley Robertson Engineering 1
3 R004 Brittany Williams HR 0
4 R005 Stephen Johnston Engineering 5
5 R006 Abigail Potter IT 1
6 R007 Amber Wright HR 2
7 R008 Kristin Stewart IT 1
8 R009 Terry Griffin HR 1
9 R010 Sara Ramirez HR 4

Display projects_df

projects_df['Last Review Date'] = pd.to_datetime(projects_df['Last Review Date'])

projects_df
Project ID Project Name Start Date Last Review Date Review Frequency (Years) Department
0 P001 Mesh Granular Markets 2025-03-01 2026-02-26 2 Finance
1 P002 Evolve Viral Partnerships 2025-03-31 2026-03-17 2 Finance
2 P003 Innovate Web-Enabled Interfaces 2024-07-07 2025-05-15 2 Engineering
3 P004 Matrix Interactive Applications 2023-07-26 2024-03-25 1 Engineering
4 P005 Re-Intermediate Strategic E-Markets 2024-07-06 2025-03-29 1 IT
5 P006 Maximize Turn-Key Portals 2024-08-21 2025-07-08 1 IT
6 P007 Unleash Turn-Key Action-Items 2023-09-26 2024-05-19 1 Engineering
7 P008 Enable Seamless Bandwidth 2023-11-09 2024-11-04 1 QA
8 P009 Engineer Intuitive Synergies 2023-12-16 2024-06-30 1 HR
9 P010 Maximize Mission-Critical Partnerships 2024-03-29 2024-10-13 1 HR
10 P011 Deliver Back-End Channels 2024-05-26 2025-05-12 2 QA
11 P012 Matrix Innovative Action-Items 2025-03-11 2026-01-23 1 IT
12 P013 Leverage Visionary Users 2024-01-24 2024-12-16 2 QA
13 P014 Re-Intermediate Value-Added Web Services 2024-09-24 2025-07-22 2 QA
14 P015 Monetize Sticky Channels 2024-12-16 2025-07-08 1 Finance
15 P016 Matrix Sticky Schemas 2023-11-09 2024-08-05 2 Finance
16 P017 Envisioneer Best-Of-Breed Supply-Chains 2023-08-21 2024-06-15 1 Engineering
17 P018 Whiteboard Back-End Mindshare 2023-09-25 2024-04-07 2 HR
18 P019 Productize Scalable Infrastructures 2024-04-20 2024-11-13 1 QA
19 P020 E-Enable Efficient Paradigms 2024-08-05 2025-03-21 2 QA
20 P021 Revolutionize End-To-End Functionalities 2023-05-02 2024-02-14 1 HR
21 P022 Leverage Visionary Schemas 2025-04-14 2026-02-06 1 Engineering
22 P023 Engage Value-Added Initiatives 2024-09-26 2025-07-16 1 Engineering
23 P024 Matrix B2C Users 2023-06-23 2024-06-03 1 Engineering
24 P025 Disintermediate B2B E-Commerce 2023-05-26 2024-01-21 1 Finance
25 P026 Monetize Bricks-And-Clicks Paradigms 2023-08-01 2024-05-31 2 QA
26 P027 Architect Interactive Web Services 2025-02-14 2025-11-23 1 QA
27 P028 Revolutionize Transparent E-Business 2023-07-14 2024-04-16 1 Finance
28 P029 Re-Contextualize Web-Enabled Communities 2024-10-28 2025-07-02 2 HR
29 P030 Exploit Virtual E-Markets 2025-04-11 2026-01-24 2 QA
30 P031 Extend Open-Source E-Markets 2023-05-18 2024-01-01 2 QA
31 P032 Cultivate Interactive Niches 2024-12-05 2025-06-17 1 HR
32 P033 Scale Sticky Architectures 2023-08-24 2024-03-05 1 IT
33 P034 Iterate Collaborative E-Services 2025-01-25 2025-11-23 1 Engineering
34 P035 Redefine Ubiquitous Convergence 2023-09-22 2024-07-28 1 QA
35 P036 Generate Bleeding-Edge E-Services 2023-12-08 2024-06-22 1 QA
36 P037 Aggregate Wireless Supply-Chains 2023-05-17 2024-02-24 1 IT
37 P038 Optimize Efficient Convergence 2023-11-19 2024-07-19 1 IT
38 P039 Strategize Open-Source Web-Readiness 2024-08-10 2025-02-26 2 IT
39 P040 Morph Virtual Applications 2023-07-23 2024-06-11 2 HR
40 P041 Morph Robust Platforms 2025-04-01 2025-11-19 2 QA
41 P042 Seize Turn-Key E-Commerce 2024-07-08 2025-03-12 2 QA
42 P043 Deliver One-To-One Architectures 2024-07-15 2025-07-01 2 Finance
43 P044 Aggregate Dot-Com Paradigms 2025-02-14 2025-11-01 1 Engineering
44 P045 Implement 24/365 Relationships 2024-09-28 2025-07-22 1 Engineering
45 P046 Repurpose Open-Source E-Business 2024-03-09 2025-01-20 1 IT
46 P047 Iterate 24/7 Content 2023-12-15 2024-08-18 1 HR
47 P048 Repurpose Transparent Vortals 2024-05-04 2024-11-17 1 HR
48 P049 Envisioneer End-To-End Solutions 2024-02-17 2024-10-26 1 Finance
49 P050 Synergize Virtual E-Business 2024-03-01 2025-01-14 2 IT

Script Walkthrough

Optimized Reviewer Assignment Function:

The system uses a custom reviewer assignment algorithm to manage the review process. This algorithm evaluates each project’s next required review date and matches it to the reviewer with the lowest current workload in the same department. The algorithm selects from the full reviewer pool if no departmental reviewer is available. The goal is to balance reviewer load while ensuring department relevance and review timeliness.

def assign_reviewers_optimized(projects_df, reviewers_df):
    assignments = []
    today = pd.to_datetime("today")

    # Calculate next review date
    projects_df['Next Review Date'] = projects_df['Last Review Date'] + pd.to_timedelta(
        projects_df['Review Frequency (Years)'] * 365, unit='D')

    # Filter projects that are due for review
    due_projects = projects_df[projects_df['Next Review Date'] <= today].copy()
    due_projects = due_projects.sort_values(by='Next Review Date')

    # Sort reviewers by current load
    reviewers_df = reviewers_df.sort_values(by='Current Load').copy().reset_index(drop=True)
    assigned_reviewers = set()

    for _, project in due_projects.iterrows():
        # Filter reviewers by department and not already assigned
        dept_reviewers = reviewers_df[
            (reviewers_df['Department'] == project['Department']) &
            (~reviewers_df['Reviewer ID'].isin(assigned_reviewers))
        ]

        # Fallback to unassigned from all departments
        if dept_reviewers.empty:
            dept_reviewers = reviewers_df[~reviewers_df['Reviewer ID'].isin(assigned_reviewers)]

        if dept_reviewers.empty:
            continue  # No available reviewers left

        selected = dept_reviewers.iloc[0]
        assigned_reviewers.add(selected['Reviewer ID'])

        assignments.append({
            'Project ID': project['Project ID'],
            'Project Name': project['Project Name'],
            'Reviewer ID': selected['Reviewer ID'],
            'Reviewer Name': selected['Name'],
            'Email': selected['Email'],
            'Department': selected['Department'],
            'Scheduled Date': project['Next Review Date'],
            'Status': "Due",
            'Review Notes': f"Scheduled review for {project['Project Name']}"
        })

    return pd.DataFrame(assignments)

Main Workflow:

The script loads environment variables, generates project and reviewer data, and executes the assign_reviewers_optimized function. This step connects due projects to the most appropriate available reviewers. The resulting assignments DataFrame is then displayed for inspection. If no projects are due, a message is shown instead. This provides immediate feedback and ensures that the pipeline works end-to-end before any emails are sent or CSVs are generated.

# Assign reviewers to due projects
assignments_df = assign_reviewers_optimized(projects_df, reviewers_df)
from IPython.display import display
if not assignments_df.empty:
    display(assignments_df.head(10))
else:
    print("No projects are due for review.")
Project ID Project Name Reviewer ID Reviewer Name Email Department Scheduled Date Status Review Notes
0 P025 Disintermediate B2B E-Commerce R001 Jennifer Bass IT 2025-01-20 Due Scheduled review for Disintermediate B2B E-Com…
1 P021 Revolutionize End-To-End Functionalities R004 Brittany Williams HR 2025-02-13 Due Scheduled review for Revolutionize End-To-End …
2 P037 Aggregate Wireless Supply-Chains R006 Abigail Potter IT 2025-02-23 Due Scheduled review for Aggregate Wireless Supply…
3 P033 Scale Sticky Architectures R008 Kristin Stewart IT 2025-03-05 Due Scheduled review for Scale Sticky Architectures
4 P004 Matrix Interactive Applications R003 Bradley Robertson Engineering 2025-03-25 Due Scheduled review for Matrix Interactive Applic…
5 P028 Revolutionize Transparent E-Business R009 Terry Griffin HR 2025-04-16 Due Scheduled review for Revolutionize Transparent… 
# Create a CSV file for assignments

assignments_df.to_csv("reviewer_assignments.csv", index=False)
print("reviewer_assignments.csv created")

reviewer_assignments.csv created

Security

To manage sensitive credentials securely, this project uses the python-dotenv library. This package allows developers to store environment variables (like email addresses and app passwords) in a separate .env file rather than hardcoding them into the script. By using load_dotenv(), these values are loaded into the runtime environment, protecting credentials from exposure in source control and making the script more portable and secure.

from dotenv import load_dotenv
import os

load_dotenv()
print(os.getenv("EMAIL_ADDRESS"))


cynthiamcginnis2020@gmail.com


from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
import smtplib
import os
from dotenv import load_dotenv

# Load credentials from .env
load_dotenv(dotenv_path=".env")
EMAIL_ADDRESS = os.getenv("EMAIL_ADDRESS")
EMAIL_PASSWORD = os.getenv("EMAIL_PASSWORD")
AUDIT_EMAIL = os.getenv("AUDIT_RECIPIENT")


from dotenv import load_dotenv
load_dotenv(".env")  # or load_dotenv() if .env is in the same folder
import os

print("Audit email:", os.getenv("AUDIT_RECIPIENT"))

Audit email: cynthiamcginnis2020@gmail.com
from dotenv import load_dotenv
import os
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

# === Load environment variables ===
load_dotenv(".env")
EMAIL_ADDRESS = os.getenv("EMAIL_ADDRESS")
EMAIL_PASSWORD = os.getenv("EMAIL_PASSWORD")
AUDIT_EMAIL = os.getenv("AUDIT_RECIPIENT")

# === Start SMTP session ===
with smtplib.SMTP_SSL("smtp.gmail.com", 465) as server:
    server.login(EMAIL_ADDRESS, EMAIL_PASSWORD)

    # === 1. Send audit/test email ===
    audit_subject = "📋 Audit Review Assignment Notification"
    audit_body = f"""
Hi,

This is a test notification to confirm that the project review email system is working correctly.

Regards,  
Project Review Scheduler
"""

    audit_msg = MIMEMultipart()
    audit_msg["From"] = EMAIL_ADDRESS
    audit_msg["To"] = AUDIT_EMAIL
    audit_msg["Subject"] = audit_subject
    audit_msg.attach(MIMEText(audit_body, "plain"))

    server.sendmail(EMAIL_ADDRESS, AUDIT_EMAIL, audit_msg.as_string())
    print(f" Audit email sent to {AUDIT_EMAIL}\n---\n{audit_body}\n---")

    # === 2. Send reviewer assignment emails from assignments_df ===
    for _, row in assignments_df.iterrows():
        try:
            subject = " Review Assignment Notification"
            body = f"""
Hi {row['Reviewer Name']},

You have been assigned to review the following project:

- Project: {row['Project Name']}
- Scheduled Review Date: {row['Scheduled Date'].strftime('%Y-%m-%d')}
- Notes: {row['Review Notes']}

Please complete your review by the due date.

Regards,  
Project Review Scheduler
"""

            msg = MIMEMultipart()
            msg["From"] = EMAIL_ADDRESS
            msg["To"] = row["Email"]
            msg["Subject"] = subject
            msg.attach(MIMEText(body, "plain"))

            server.sendmail(EMAIL_ADDRESS, row["Email"], msg.as_string())

            # Print message body for review
            print(f"\n Email sent to {row['Reviewer Name']} <{row['Email']}>\n---\n{body}\n---")

        except Exception as e:
            print(f" Failed to send to {row['Email']}: {e}")

 Audit email sent to cynthiamcginnis2020@gmail.com
---

Hi,

This is a test notification to confirm that the project review email system is working correctly.

Regards,  
Project Review Scheduler

---

 Email sent to Jennifer Bass <justin69@example.net>
---

Hi Jennifer Bass,

You have been assigned to review the following project:

- Project: Disintermediate B2B E-Commerce
- Scheduled Review Date: 2025-01-20
- Notes: Scheduled review for Disintermediate B2B E-Commerce

Please complete your review by the due date.

Regards,  
Project Review Scheduler

---

 Email sent to Brittany Williams <xcarr@example.org>
---

Hi Brittany Williams,

You have been assigned to review the following project:

- Project: Revolutionize End-To-End Functionalities
- Scheduled Review Date: 2025-02-13
- Notes: Scheduled review for Revolutionize End-To-End Functionalities

Please complete your review by the due date.

Regards,  
Project Review Scheduler

---

 Email sent to Abigail Potter <osbornejeffery@example.net>
---

Hi Abigail Potter,

You have been assigned to review the following project:

- Project: Aggregate Wireless Supply-Chains
- Scheduled Review Date: 2025-02-23
- Notes: Scheduled review for Aggregate Wireless Supply-Chains

Please complete your review by the due date.

Regards,  
Project Review Scheduler

---

 Email sent to Kristin Stewart <usalazar@example.net>
---

Hi Kristin Stewart,

You have been assigned to review the following project:

- Project: Scale Sticky Architectures
- Scheduled Review Date: 2025-03-05
- Notes: Scheduled review for Scale Sticky Architectures

Please complete your review by the due date.

Regards,  
Project Review Scheduler

---

 Email sent to Bradley Robertson <clarence34@example.org>
---

Hi Bradley Robertson,

You have been assigned to review the following project:

- Project: Matrix Interactive Applications
- Scheduled Review Date: 2025-03-25
- Notes: Scheduled review for Matrix Interactive Applications

Please complete your review by the due date.

Regards,  
Project Review Scheduler

---

 Email sent to Terry Griffin <juliawells@example.com>
---

Hi Terry Griffin,

You have been assigned to review the following project:

- Project: Revolutionize Transparent E-Business
- Scheduled Review Date: 2025-04-16
- Notes: Scheduled review for Revolutionize Transparent E-Business

Please complete your review by the due date.

Regards,  
Project Review Scheduler

---