Automating the monthly ERP-to-consolidation chain for a multi-entity group

Context

A dealer group with several legal entities consolidates its accounts in CCH Tagetik. Every month, the trial balance of each entity has to leave the cloud ERP, be reworked according to the group’s accounting rules and land on the consolidation platform’s SFTP server as a flat file. Before FinSync, that chain lived in a set of cloud workflow automations: an API token kept in a file, refreshed by another workflow, and a CSV assembled step by step — workable, but opaque, hard to change and dependent on the person who had built it. I proposed to replace it with a dedicated application the finance team could operate itself: connect the ERP, maintain the entities, schedule the runs, read the logs and resend a file without calling a developer.

Challenge

Reproduce the existing flow exactly — the finance team had validated its output against the consolidation platform and would not accept a different file — while removing its fragilities. The ERP’s API issues single-use refresh tokens: one lost rotation and the whole chain stops. The consolidation platform only accepts SFTP connections from allow-listed addresses, which a serverless backend does not have. The accounting rules are not trivial: closing balances for some account classes, period movements for others, listed tax accounts, and a retained-earnings computation whose account differs from one entity to the next, notably for entities acquired after a desktop-to-cloud ERP migration. And the whole thing had to run unattended on a schedule, in the group’s time zone and fiscal calendar, with a summary in the finance team’s inbox.

My role

Founder and architect of the product: I defined the scope, designed the integration architecture and the data model, specified the accounting rules with the finance team and directed the technical work, which my team at SOFT M POWER CANADA carried out with an AI-assisted development platform. I also write the change plans for production adjustments — parallel profiles, comparison run, reversible switch, deferred cleanup — and remain the technical contact for the group’s finance team. The platform is operated by my team.

Architecture / approach

Own the chain end to end in one application rather than stitch services together. An ERP connector module handles OAuth authorization, token rotation and API calls in one place. A run is a first-class object: it records its parameters, the status of every entity and three levels of data — the raw API response, the normalized rows and the final rows after the rules — so that any file can be traced back to its source and recomputed. Transformation rules are deterministic code, versioned with the application, not settings a user could silently alter. Delivery is a separate transfer object with its own status, so a failed upload can be resent without re-extracting. The scheduler evaluates cron expressions per time zone and triggers runs without waiting for them; each run ends with an email summary per entity.

Technology

React and TypeScript front end; managed Postgres with a dedicated roles table checked by row-level policies and by a security-definer function; twelve serverless functions in TypeScript on Deno for extraction, token refresh, scheduling, SFTP upload, notifications and user administration; private object storage for the generated files; a database-side scheduler waking the orchestrator every minute; SFTP over SSH with an optional SOCKS5 tunnel to a static egress address; transactional email through a provider’s API; OAuth 2.0 against the Sage Business Cloud Accounting API. Twenty-eight migrations, nineteen tables and seven enumerated types describe the model; an embedded wiki documents the architecture, the OAuth procedure, the SFTP setup and the file format for administrators.

Execution

The bulk of the platform was built in March and April 2026 — more than 430 commits — starting with the ERP connector, where the single-use refresh token cost some thirty commits in four days before authorization, rotation and the recovery paths were right. The CSV pipeline mirrored the previous workflow step by step; the retained-earnings computation was then corrected six times in one week against the finance team’s figures. SFTP went through three designs in a week before settling on the static-address tunnel. Password recovery was rebuilt around a one-time code after the native flow proved unreliable. A second phase in July added notification options, tightened scheduling permissions and fixed two scheduler faults found in operation: runs left queued because the worker waited for the extraction, and a next-run computation that exceeded the function’s CPU budget.

Constraints

Output identical to the validated file, down to column order and encoding. No fixed address on the serverless side, and an SFTP target that requires one. Single-use refresh tokens with no tolerance for a missed rotation. Functions limited in wall-clock and CPU time, which shaped both the fire-and-forget trigger and the next-run algorithm. A fiscal calendar that does not follow the civil year. One dedicated instance per organization: the deployment is single-tenant, in French, and the accounting rules are versioned in code rather than exposed as user settings — a deliberate trade-off between flexibility and auditability. No production volumes, timings or amounts are published here.

Key takeaways

1. When replacing a working automation, reproduce its output first and improve its architecture second — the finance team’s trust is in the file, not in the code.
2. Single-use tokens turn every rotation into a transaction: store, rotate and retry in one place, and give operators a recovery path.
3. Serverless has no fixed address; a static egress tunnel is cheaper than a server.
4. Keep the raw, normalized and final data of every run — traceability is what makes a deterministic pipeline auditable.
5. A scheduler that waits for the job it triggers will eventually be killed by the platform: fire, forget, and record.