Payroll Accounting Mapper

Accounting firms that manage payroll for multiple client companies receive monthly payroll reports — pay stubs, vacation provisions, and 13th salary provisions — in Excel format, generated by each client's HR system.

For each payroll event (salary, overtime, social security, etc.), the firm must generate accounting journal entries with the correct debit and credit accounts. These mappings vary by company and cost center, following the accounting plan and business rules of each client.

Before this system, the entire process was done manually in spreadsheets — once a month, for every client — relying on each analyst's implicit knowledge of each company's accounting rules.

The original manual workflow had several structural issues:

• Time-consuming: hours per company, repeated monthly across the entire client portfolio
• Error-prone: mapping decisions depended on individual analyst knowledge, not centralized rules
• No visibility: no way to quickly identify which payroll events lacked a valid accounting mapping
• Fragmented knowledge: rules scattered across spreadsheets and people, not centralized or auditable
• No compounding efficiency: each month required the same full manual effort with no accumulated benefit

I designed and built a complete web application to replace the entire manual accounting mapping workflow:

• File parsing: Dynamic reading and normalization of payroll Excel reports — pay stubs, vacation provisions, and 13th salary provisions — handling format variations across different HR systems.
• Accounting resolution: Priority-based mapping engine that resolves journal entry debit/credit accounts per company and cost center, cascading from specific rules to economic group defaults to global fallbacks.
• Mapping management: Browser-based interface to register payroll events and their accounting mappings, grouped by company and economic group — no spreadsheets or developer access needed.
• Review interface: Pre-export review screen with visual indicators for unmapped events, allowing analysts to identify and fix gaps before generating the final output.
• FPA export: Consolidated FPA export by economic group, including GPS entries and General Summary, formatted for direct import into FPA-ELEVOR — removing the entire manual data-entry step.

The system is structured as a decoupled full-stack application, deployed in a hybrid environment:

• Backend: Python 3.12 with FastAPI for async REST API. asyncpg for direct async PostgreSQL 17 access without ORM overhead, keeping query control explicit.
• Frontend: Next.js 15 with TypeScript and React, served as a static build from the Linux server.
• Database: PostgreSQL 17 running in the production Windows Server environment, accessed from the Linux app server via OpenVPN tunnel.
• Deployment: Linux server managing the FastAPI process with PM2 for process supervision and automatic restarts. Docker used during local development for environment consistency.
• Mapping resolution: Priority chain — company + cost center → economic group + cost center → global default — providing flexible overrides with safe fallbacks at each level.

• Dynamic parsing of multiple payroll Excel formats — pay stubs, vacation provisions, 13th salary provisions
• Multi-CNPJ company registration, grouped by economic group tags
• Cost center registration per company with mapping overrides by cost center
• Three-level priority mapping resolution engine
• Review screen with visual indicators for unresolved payroll events before export
• Consolidated FPA export with GPS and General Summary by economic group
• Full event and mapping management directly through the browser interface

• Eliminated a process that previously took hours per company — now completed in seconds
• Full traceability: analysts see exactly which events are mapped and which are missing, for every company in the portfolio
• Accounting rules are centralized and reusable, not locked in individual spreadsheets or analyst knowledge
• Consistent, repeatable output every month with no accumulated manual effort
• FPA export formatted for direct import, removing a full manual data-entry step from the accounting workflow

This project is documented as a private case study. Source code, accounting mappings, business rules, client data, and internal configurations are not included due to company policies and confidentiality requirements.