HSA Gaurdrail

🔍 Problem Statement

During the plan year, employees may update their benefits due to Qualifying Life Events (QLEs). These updates can generate new payroll rows — including employer HSA contributions (HSAER) — even if that contribution was already made earlier in the year.

This creates a risk of duplicate employer contributions, which can:

• Inflate benefit costs unnecessarily
• Cause compliance and tax issues
• Require manual clawbacks or adjustments
• Frustrate employees and administrators

The challenge:

There was no reliable process in place to prevent duplicate HSAER deductions during mid-year benefit updates.

This project solves that by:

• Scanning the latest payroll file
• Comparing against a full file of already-paid HSAER contributions
• Removing duplicate entries
• Creating an audit trail
• Safely appending new contributions for future reference

🧑🏾‍💻 My Role

Designed and built a system that detects and removes duplicate HSAER (employer HSA contribution) rows from payroll files to prevent overpayment. Also implemented a mechanism to track and append new entries to the HSA full file for audit continuity.

🛠️ What It Does

• Extracts latest payroll and full file from dated folders
• Identifies and removes duplicate HSAER contributions
• Logs removed entries for transparency
• Detects new HSAER contributions and adds them to a full file (test version)
• Outputs a cleaned payroll file and a report of removed rows

🔍 Why This Matters

This automation ensures HSA employer contributions are issued only once per year per employee. Without it, HR teams could unknowingly send duplicate contributions, especially when employees make qualifying life events (QLEs) like address or birth changes.

🎥 Demo

Here's a breakdown of what a successful run looks like:

1. Payroll and Full File loaded
2. Duplicate HSAER entries removed from payroll
3. Log shows SSNs of removed entries
4. New HSAER adds written to a test full file
5. Cleaned payroll and report CSVs saved to outputs/ and reports/

🧪 Real-World Use

This project mirrors a real issue faced at Benefit Aministration firm, where a lack of filtering logic led to duplicate employer HSA contributions during qualifying life events. The system implemented here provided a quick and scalable solution.

⭐ Why It Stands Out

• Modular and extensible structure
• Audit trail for removed entries
• Test-mode safe updates to full file
• Clean logic using boolean masks and set comparisons

🧠 Key Technologies

Python · Pandas · CSV Parsing · File I/O · Data Auditing · Logging · Regex · Modular Design