Designing a Practical HR-to-Finance Data Workflow Across Two Organizations

A note on evidence labeling for this case study: claims below are marked Documented (backed by an artifact — a diagram, template, or code snippet), Self-reported (Georgewilston’s own account, not independently verified), or Anonymized demonstration (a recreation using synthetic data, not the real organizational system). Nothing here is labeled “Verified” — that word is reserved for independently confirmed evidence, which doesn’t yet exist for this project. See docs/CONTENT-INVENTORY.md for the site-wide evidence policy.

1. Context

Two organizations — MCODE Uganda (roughly 20–30 staff) and Frutrac Limited (roughly 50–100 staff) — needed working HR, payroll, and attendance processes. Self-reported. Georgewilston built a system for MCODE Uganda first, then independently developed a substantially similar system at Frutrac Limited, adapting it to that organization’s scale and needs.

2. The Challenge

Before these systems existed, HR and payroll processes at both organizations relied on manual methods: Self-reported.

• Paper-based employee records
• Manual payroll calculations
• Inefficient attendance tracking
• Difficulty consolidating HR information across staff and projects
• Reporting delays
• Inconsistencies between HR records and financial records kept in QuickBooks

The central technical challenge was not building a database in isolation — it was keeping HR/payroll data consistent with the financial records already being maintained in QuickBooks, without the two systems talking to each other automatically.

3. Georgewilston’s Role

At both organizations, Georgewilston held a finance-management role (Finance Manager at MCODE Uganda; Financial Manager at Frutrac Limited) and, within that role, personally designed and built the HR/payroll systems described here — this wasn’t a separate IT function, but something he built himself alongside his financial-management responsibilities. Self-reported.

4. System Design

Georgewilston personally: Self-reported.

• Designed the database structure
• Created tables for employees, payroll, attendance, contracts, and related HR data
• Wrote SQL queries for data storage and retrieval
• Created employee registration and profile forms
• Developed payroll and attendance reporting templates
• Developed project-monitoring reports
• Implemented salary calculations and deductions
• Developed validation checks to improve data consistency

5. Technical Architecture

Primary HR system: PHP and MySQL, with SQL for querying. Documented (database diagrams and code snippets exist as artifacts).

Database technologies used across the systems’ development: MS Access, MySQL, PostgreSQL — reflecting iteration and adaptation between the two organizational implementations rather than one fixed stack. Self-reported.

Reporting and analytics layer: Excel, SPSS, STATA. Self-reported.

Financial system: QuickBooks, customized where required — chart-of- accounts configuration, payroll templates, and custom fields. Self-reported.

6. HR System Capabilities

The systems supported: Self-reported.

• Employee registration and profiles
• Attendance tracking
• Leave management
• Payroll automation, salary calculation, and deductions
• Payslip generation
• Monthly and quarterly HR reporting
• Project allocation and staff tracking
• Basic performance monitoring
• Contract management
• Digital HR document filing
• Management reporting dashboards (built in Excel)

7. HR → Excel → QuickBooks Workflow

This is the part of the project most worth understanding correctly. There was no direct API integration between the HR database and QuickBooks — this is a deliberate, practical hybrid workflow, not a real-time synchronization:

 HR Database (PHP / MySQL)
        │
        ▼
 Payroll export
        │
        ▼
 Excel  (cleaning & validation)
        │
        ▼
 Manual import / transfer into QuickBooks
        │
        ▼
 Financial reporting

QuickBooks itself was customized to make that manual step more reliable — chart-of-accounts configuration, payroll templates, and custom fields built to match the exported HR data — but the transfer between systems was a validated manual process, not an automated pipeline. Documented (workflow structure) / Self-reported (implementation detail).

8. Technical Challenge

The hardest problem was maintaining consistency across multiple systems that didn’t talk to each other automatically: payroll figures needed to match employee records, attendance data, HR records, and QuickBooks financial records, all without a shared database. Custom scripts and validation checks were built to catch mismatches before they reached financial reporting. Self-reported.

9. Outcome

The strongest documented improvement was better accuracy in payroll and reporting. Reported improvements included: Self-reported — no percentages, time savings, or financial figures are claimed, because none were measured.

• More consistent salary calculations
• Improved handling of deductions
• More reliable reports
• Improved record consistency across HR and finance
• Smoother audit preparation
• Reduced dependence on manual HR processes

10. Evidence

Available now: database diagrams, sample forms, Excel dashboards, report templates, and PHP/MySQL code snippets. There is no public GitHub repository, since these were internal organizational systems, not open-source projects. Documented — these artifacts exist but are not yet published on this site.

Possible future evidence, once prepared: recreated anonymized screenshots, recreated database diagrams, and sanitized report examples using synthetic data. Anonymized demonstration — not yet built. Real employee names, salaries, phone numbers, national IDs, bank information, tax information, or any other organizational data will never be published in any form.

11. Lessons / Approach

The real value of this project isn’t “built an HR system” — it’s the practical intersection of HR, data management, payroll, finance, and reporting, solved with a workflow that acknowledged its own limits: rather than promising an automated integration that didn’t exist, the design used a disciplined manual step (export → clean in Excel → import to QuickBooks) with validation checks to keep it honest. Building essentially the same system twice, independently, at two organizations of different scale, is itself part of the evidence that the underlying design held up. Self-reported.

12. Confidentiality Note

No real employee names, salaries, phone numbers, national IDs, bank information, tax information, private employment records, credentials, or internal database records appear anywhere in this case study or will be added later. Any future screenshots or diagrams will use synthetic or clearly-labeled anonymized demonstration data only.