Workforce Data Audit and Validation Tool

A professional Python portfolio project for auditing fictional workforce records, schedules, and time entries for data-quality and operational issues.

Audit Report Preview

Synthetic-data notice

All employee and workforce data in this project is completely synthetic and fictional. No record is sourced from or intended to represent a real person, employer, department, schedule, or time entry. The data exists solely for software demonstration and testing.

Skills demonstrated

• Python application design
• pandas data processing
• CSV ingestion and validation
• SQLite data persistence
• Automated testing with pytest
• Excel report generation with openpyxl
• Data-quality and workforce-scheduling analysis
• Modular code, type hints, docstrings, and error handling

Project structure

data/                  Synthetic department, employee, shift, and time-entry CSV files
output/                Generated CSV and Excel audit reports
src/config.py          Default paths and required CSV schemas
src/database.py        SQLite connection and schema management
src/data_loader.py     CSV validation and transactional loading
src/models.py          Typed audit-finding model
src/validators.py      Employee, department, shift, and time-entry audit rules
src/audit_engine.py    Database-backed audit orchestration
src/reporting.py       CSV, Excel, and console reporting
src/main.py            Command-line workflow
tests/                 Pytest test suite
workforce_audit.db     Generated local SQLite database

Setup

Python 3.11 or newer is required. On Windows PowerShell, run these commands from the project root to create and activate a virtual environment and install the pinned dependencies:

python -m venv .venv
.\.venv\Scripts\Activate.ps1
python -m pip install -r requirements.txt

Usage

Run the complete workflow with:

python -m src.main

Show command help with:

python -m src.main --help

Available options:

Option	Behavior
--data-dir PATH	Read the four required CSV files from another directory.
--output-dir PATH	Write the CSV and Excel reports to another directory.
--database PATH	Use another SQLite database file.
--no-reset	Preserve the existing database file and unrelated tables before replacing imported source rows and current findings.
--debug	Include a traceback for unexpected programming errors.

Example using custom paths:

python -m src.main --data-dir data --output-dir output --database workforce_audit.db

By default, the workflow recreates workforce_audit.db, validates and loads all source CSVs, runs every audit rule, stores findings, replaces both reports, prints the calculated summary, and returns exit code zero. Expected user errors are written to stderr without a traceback and return a nonzero exit code.

Audit rules

Stage 3 implements these employee and department rules:

Rule	Severity
Duplicate employee ID	High
Missing required employee field	High
Invalid employee ID format	Medium
Invalid email format	Medium
Employee references a nonexistent department	High
Missing required department field	Medium
Duplicate department ID	High

Employee IDs are case-sensitive and must contain EMP- followed by exactly four ASCII digits, for example EMP-0001. Employee required fields are employee ID, first name, last name, email, department ID, and employment status. The source CSV and SQLite schema store employment status in the status column.

Email validation intentionally uses a readable business-format check. An address must contain one @, a nonempty local part, and a dotted domain, with no spaces. It is not intended to implement every address form allowed by the full email RFC specifications.

Department ID and department name are required. Null values, empty strings, and whitespace-only strings are treated as missing for all required-field rules.

Stage 4 implements these shift rules:

Rule	Severity
Shift references a nonexistent employee	High
Shift references a nonexistent department	High
Shift end occurs before shift start	High
Overlapping shifts for one employee	High
Shift duration exceeds 12 hours	Medium
Scheduled hours exceed 60 in one calendar week	High
Missing required shift field	High
Duplicate shift ID	High
Invalid shift datetime format	Medium

Shift timestamps must be real calendar values in the exact 24-hour format YYYY-MM-DD HH:MM, for example 2026-07-06 08:00. Overnight shifts must include the correct next-day date; the audit does not infer an overnight rollover when an end value precedes its start.

Shift overlap is evaluated separately for each employee. A later shift overlaps when its start is earlier than the prior active shift's end; shifts that touch at one boundary do not overlap. Durations greater than 12 hours are flagged, while exactly 12 hours is accepted.

Weekly hours use Monday through Sunday calendar weeks and include each valid positive-duration employee/shift-ID combination once. A shift that crosses into a new week is split at the Monday midnight boundary. Totals greater than 60 hours are flagged, while exactly 60 hours is accepted. Missing or unparseable timestamps and end-before-start shifts are excluded from overlap and weekly-hours calculations.

Stage 5 implements these time-entry rules:

Rule	Severity
Time entry references a nonexistent employee	High
Clock-out occurs before clock-in	High
Worked duration exceeds 16 hours	High
No reasonably matching scheduled shift	Medium
Clock-in differs from scheduled start by more than 60 minutes	Medium
Clock-out differs from scheduled end by more than 60 minutes	Medium
Missing required time-entry field	High
Duplicate time-entry ID	High
Invalid clock datetime format	Medium

Clock-in and clock-out values use the same strict YYYY-MM-DD HH:MM format as shifts. Worked durations greater than 16 hours are flagged; exactly 16 hours is accepted. Duration and matching checks are skipped when either clock timestamp is missing or unparseable.

Time-entry matching considers only shifts for the same employee with valid, nonnegative timestamps. Daytime entries and shifts must start on the same date; overnight entries and shifts must both cross midnight with the same start and end dates. If no such candidate exists, the entry is unmatched. When several candidates exist, exactly one is selected by the smallest combined absolute start and end difference, with scheduled start and shift ID used as deterministic tie-breakers. A clock boundary differing by exactly 60 minutes is accepted; a difference greater than 60 minutes creates the corresponding variance finding. One time entry is never matched to multiple shifts.

Sample output

The current synthetic dataset produces this summary:

WORKFORCE DATA AUDIT SUMMARY

Records reviewed:
Departments: 5
Employees: 25
Shifts: 50
Time Entries: 40

Findings:
Total: 23
Critical: 0
High: 14
Medium: 9
Low: 0

Reports created:
output/audit_findings.csv
output/audit_report.xlsx

Audit completed successfully.

The workflow replaces these reports on every successful run:

• output/audit_findings.csv — UTF-8 detailed findings in a stable column order.
• output/audit_report.xlsx — formatted workbook containing Executive Summary, Audit Findings, Employees, Departments, Shifts, and Time Entries worksheets.

The executive summary contains the audit timestamp, finding totals by severity, rule, and affected table, source-record counts, and a synthetic-data notice. The detailed findings are sorted using the explicit severity order Critical, High, Medium, and Low. Source worksheets preserve the values loaded into SQLite.

Testing

Run the complete test suite from the project root with:

python -m pytest -v

Tests use temporary data directories, databases, and report folders. They do not modify the repository's sample CSV files or real output folder.

Limitations

• The project uses a single local SQLite file and is designed for demonstration-scale data, not concurrent multi-user workloads.
• Input schemas and accepted timestamp formats are intentionally strict.
• Email validation checks a practical business format rather than every RFC-valid email variation.
• Overnight-shift dates must be explicit; implicit day rollover is not inferred.
• Time-entry matching uses deterministic date and boundary proximity rules and does not perform probabilistic reconciliation.
• Audit thresholds are fixed in code and are not yet configurable from the command line.
• The current database retains one audit result rather than historical audit runs or reviewer sign-off workflow.
• The application demonstrates workforce-data auditing but does not integrate with NICE IEX, live contact-center queues, service-level metrics, VTO/overtime workflows, payroll, or production HR systems.
• This synthetic portfolio project does not implement production security controls such as authentication, authorization, encryption, retention policies, or access logging.

Future improvements

• Add configurable audit thresholds and severity policies.
• Add audit-run history instead of replacing the current finding set.
• Add structured logging and optional machine-readable console output.
• Add type-checking and linting automation in continuous integration.
• Support additional input formats and larger database platforms.
• Add interactive dashboards and trend analysis across audit runs.