Python rebuild of ApprovalFlow — intern timesheet approval automation
This is a ground-up rebuild of approvalflow-vba, a production VBA system that automates timesheet approval routing for an off-site internship program at a multi-campus community college system. The VBA version has been running in production since January 2024 and has reduced processing time by 75%, saving approximately 576 hours annually.
This rebuild exists because the VBA system has real architectural limits: it requires Outlook to stay open on one Windows machine, stores all data in Excel, has no API surface, and cannot scale to multiple institutions. This repo is the path to a deployable, maintainable, multi-tenant version.
Status: Active development. Database layer complete and verified; email parsing, OAuth2 integration, and reply matching are next.
All development uses synthetic data generated by this repo. No real institutional data is used outside of the production work system.
The VBA system taught specific lessons that drive this architecture:
- Excel as a database causes silent data corruption. The append bug in
PopulateSubmissionTrackingdoubled row counts with no error. PostgreSQL with proper constraints makes that class of bug structurally impossible, not just less likely. - Outlook dependency means single-machine, synchronous processing. A COM freeze during
FastRebuildSentLoglocked the application until restarted. Python with async email handling removes that dependency entirely. - Hardcoded paths break on successor handoff. Environment-based config and a proper database remove all of that.
- OAuth2 is required for modern Microsoft 365 integration. Basic auth is deprecated. The VBA system works around this; the Python version handles it correctly from the start.
An intern submits a timesheet in the HR/payroll system. That system sends a notification email to a shared payroll mailbox. ApprovalFlow:
- Parses the notification email to extract intern name, hours, pay period, and ID
- Looks up the assigned supervisor from the database
- Forwards a formatted approval request to the supervisor (intern CC'd)
- Parses the supervisor's reply for APPROVE, REJECT, or CORRECTIONS
- Matches the reply back to the correct intern record
- Updates submission status in the database
- Generates a dashboard and sends deadline reminders on a schedule
approvalflow-python/
├── README.md
├── requirements.txt
├── docker-compose.yml # Local disposable Postgres instance
├── db/
│ ├── schema.sql # PostgreSQL schema, source of truth
│ ├── models.py # SQLAlchemy models
│ ├── queries.py # Database query functions
│ ├── apply_schema.py # Applies schema.sql via Python (no psql needed)
│ ├── seed_synthetic_data.py # Faker-based synthetic data generator
│ └── demo_report.py # Prints a readable pay period summary
├── data/
│ └── generate_synthetic_data.py # Synthetic HTML notification email data
├── core/
│ ├── email_parser.py # Parse Banner-style HTML notification emails
│ ├── email_sender.py # OAuth2 email sending via Microsoft Graph API
│ ├── reply_parser.py # Classify and match supervisor replies
│ └── pay_period.py # Pay period date logic
└── tests/
├── test_email_parser.py
└── test_reply_parser.py
| Component | Status |
|---|---|
| PostgreSQL schema | Done |
| Database layer (SQLAlchemy models + queries) | Done |
| Synthetic data generator | Done |
| Local Docker Compose setup | Done |
| Email parser (HTML notification) | In progress |
| OAuth2 / Microsoft Graph integration | Not started |
| Supervisor reply parser | Not started |
| Reply matching algorithm | Not started |
| Reminder scheduler | Not started |
| Dashboard / reporting | Not started |
These are not hypothetical design decisions, each one was learned from a production incident in the VBA system.
Reply matching must weight name over time proximity, and a real foreign key removes most of the need for scoring at all.
The VBA system originally matched supervisor replies using time proximity as the primary signal. When one supervisor managed multiple interns, all replies were matched to the same student. The fix in VBA was to reweight the scoring: name match at 2, time score capped at 1. In this rebuild, sent_log_id is a real foreign key on reply_log, so once a reply is associated with its originating submission, there is no scoring left to do.
Never append to a tracking table blindly, enforce it structurally.
A silent append bug in the VBA system caused a 2x row overcounting incident (368 rows vs. 181 actual students) with no error raised. submission_tracking has a UNIQUE (pay_period_id, intern_id) constraint, and all writes go through an upsert (ON CONFLICT ... DO UPDATE). A duplicate is structurally impossible, not just handled carefully.
Quoted email body contaminates keyword classification.
The VBA DetermineResponseType function misclassified APPROVE replies as REJECTED because the quoted original message contained the word "REJECTED" in the instructions. The Python reply parser will strip quoted content before classification.
The HR/payroll system stamps submission date, not pay period date.
A date filter in FastRebuildSentLog dropped timesheets submitted on deadline day because the status date fell outside the pay period window. This filter was removed in VBA and will not be implemented in Python.
Internal Exchange users return Distinguished Names, not SMTP addresses.
SenderEmailAddress returns /O=EXCHANGELABS/... strings for internal users. The Python version will resolve addresses via Microsoft Graph API from the start, which returns SMTP addresses natively.
No Azure account, credentials, or cloud setup required. This spins up a disposable local Postgres instance and populates it with synthetic data, so you can see the full pipeline run end to end on your own machine.
| Requirement | Notes |
|---|---|
| Docker | Used to run a local, disposable Postgres 16 instance |
| Python 3.11+ | Matches the version used in CI |
| pip | For installing dependencies from requirements.txt |
git clone https://github.com/aprie06/approvalflow-python
cd approvalflow-python
docker compose up -d
pip install -r requirements.txtdocker compose up -d starts a local Postgres 16 container with a database named approvalflow_dev, isolated from anything else on your machine, and safe to discard at any time.
export DATABASE_URL="postgresql://approvalflow:approvalflow_local@localhost:5432/approvalflow_dev"
python3 -m db.apply_schema
python3 -m db.seed_synthetic_data
python3 -m db.demo_report| Command | What it does |
|---|---|
db.apply_schema |
Applies db/schema.sql to the local database |
db.seed_synthetic_data |
Generates and inserts a small synthetic dataset: 3 organizations, 10 supervisors, 40 interns, 2 pay periods, with realistic (fake) submission and approval history |
db.demo_report |
Prints a readable summary of each pay period, similar in spirit to the original VBA system's GeneratePayPeriodSummary |
Applied schema.sql successfully.
Seeded 3 organizations, 10 supervisors, 40 interns, 2 pay periods.
==================================================
PAY PERIOD SUMMARY: Jun 16-30 2026
==================================================
Total Interns 40
Submitted 39
Not Submitted 1
Approved 23
Rejected 6
Corrections Requested 4
Pending Approval 6
Response Rate 84.6%
==================================================
PAY PERIOD SUMMARY: Jul 1-15 2026
==================================================
Total Interns 40
Submitted 38
Not Submitted 2
Approved 29
Rejected 5
Corrections Requested 1
Pending Approval 3
Response Rate 92.1%
All names, emails, and organizations in the synthetic dataset are generated by Faker with a fixed seed, no real institutional or personal data is used anywhere in this repository.
docker compose down -vThis stops the container and removes its data volume, so the next docker compose up -d starts from a completely clean database.
This project is a Python rebuild of a production Outlook and Excel-based timesheet approval system originally built in VBA. The original, anonymized VBA source is published separately: approvalflow-vba.
The VBA system automated timesheet approval for 200 student interns across 85+ off-site placement locations, reducing processing time from roughly 32 hours to roughly 8 hours per pay period. This Python rebuild reimplements that workflow as an independent, original codebase, with no institutional data, credentials, or proprietary logic carried over.
Alexis Prieto HR Systems and Automation Analyst LinkedIn · GitHub