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
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
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
Display projects_df
projects_df['Last Review Date'] = pd.to_datetime(projects_df['Last Review Date'])
projects_df.head(5)
|
|
Project ID
|
Project Name
|
Start Date
|
Last Review Date
|
Review Frequency (Years)
|
Department
|
|
0
|
P001
|
Utilize Efficient Action-Items
|
2024-08-09
|
2025-07-18
|
1
|
Engineering
|
|
1
|
P002
|
Extend Sticky Methodologies
|
2023-10-10
|
2024-06-16
|
1
|
QA
|
|
2
|
P003
|
Incubate E-Business Applications
|
2024-10-22
|
2025-05-25
|
1
|
IT
|
|
3
|
P004
|
Utilize E-Business E-Tailers
|
2024-03-04
|
2024-09-22
|
2
|
Engineering
|
|
4
|
P005
|
Expedite Proactive Schemas
|
2023-10-17
|
2024-04-21
|
1
|
QA
|
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
|
P041
|
Matrix Strategic Infrastructures
|
R003
|
Brent Jordan
|
ujenkins@example.org
|
Engineering
|
2025-02-10
|
Due
|
Scheduled review for Matrix Strategic Infrastr…
|
|
1
|
P010
|
Transform Synergistic Convergence
|
R010
|
Lauren Daniels
|
brian97@example.net
|
QA
|
2025-03-14
|
Due
|
Scheduled review for Transform Synergistic Con…
|
|
2
|
P005
|
Expedite Proactive Schemas
|
R008
|
Carmen Smith
|
ybaker@example.com
|
QA
|
2025-04-21
|
Due
|
Scheduled review for Expedite Proactive Schemas
|
# 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
Python Email Configuration Code
This Python code is part of an email automation setup. It uses modules to build and send an email and loads sensitive credentials securely using environment variables.
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")
Loading Environment Variables with python-dotenv
This code snippet demonstrates how to load and access environment variables stored in a .env file using the python-dotenv package.
A .env file is a plain text file that stores environment variables—key-value pairs that configure your application without hardcoding sensitive or configurable data directly into your source code.
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
📧 Python Email Automation with .env Credentials and SMTP
This script demonstrates how to securely send emails using Gmail’s SMTP server, environment variables, and pandas DataFrame iteration for bulk email assignments.
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 Brent Jordan <ujenkins@example.org>
---
Hi Brent Jordan,
You have been assigned to review the following project:
- Project: Matrix Strategic Infrastructures
- Scheduled Review Date: 2025-02-10
- Notes: Scheduled review for Matrix Strategic Infrastructures
Please complete your review by the due date.
Regards,
Project Review Scheduler
---
Email sent to Lauren Daniels <brian97@example.net>
---
Hi Lauren Daniels,
You have been assigned to review the following project:
- Project: Transform Synergistic Convergence
- Scheduled Review Date: 2025-03-14
- Notes: Scheduled review for Transform Synergistic Convergence
Please complete your review by the due date.
Regards,
Project Review Scheduler
---
Email sent to Carmen Smith <ybaker@example.com>
---
Hi Carmen Smith,
You have been assigned to review the following project:
- Project: Expedite Proactive Schemas
- Scheduled Review Date: 2025-04-21
- Notes: Scheduled review for Expedite Proactive Schemas
Please complete your review by the due date.
Regards,
Project Review Scheduler
---
