Problem
I built this for a real need. A small business was managing multiple company agreements through E-conomic (a Danish cloud accounting platform), and the reporting situation was painful. E-conomic’s built-in reports are limited — fine for single-agreement bookkeeping, but useless when you need to aggregate data across multiple agreements, cross-reference accounts with budget plans, or feed structured data into PowerBI for custom dashboards. The API exists, but manually pulling data from it for every reporting cycle was eating hours per week.
I wanted a system that would pull all accounting data from multiple E-conomic agreements into a local database, keep it synchronized automatically, and expose it in a format that PowerBI could consume efficiently. As a personal project, I also used it to teach myself full-stack development patterns I don’t use in my day job — Node.js, Express routing, MySQL schema design, Python Flask for secondary services.
Constraints
- Multi-agreement architecture. E-conomic’s API requires a separate authentication token per agreement. The system needs to manage multiple agreement configurations and sync each independently.
- API rate limits. E-conomic’s REST API has rate limits and paginated responses. The sync process must handle pagination, respect rate limits, and implement retry logic with exponential backoff.
- PowerBI optimization. PowerBI performs best when it can query pre-aggregated data rather than running complex joins against normalized tables. The database schema needed both normalized storage (for accuracy) and denormalized aggregation tables (for performance).
- Reliability. Daily syncs at 3AM must complete without intervention. If a sync fails, it needs to retry and alert on repeated failures.
Architecture
The system has three main components:
Node.js/Express Backend. The primary API server handles all interactions with the E-conomic REST API. It’s organized into modules per entity type — invoices, accounts, customers, suppliers, products, journals, vouchers, departments, payment terms, VAT accounts — each with its own model, service, controller, and routes. The API client handles authentication, pagination (following E-conomic’s nextPage links), and rate limit compliance.
MySQL Database. 43 migration files define the schema evolution: from initial entity tables (invoices, accounts, customers) through PowerBI optimization views (020-powerbi-optimizations.js) to voucher PDF availability tracking and fact table augmentation. The migration system runs on startup, applying any pending migrations automatically.
Pre-computed aggregation tables (created in migration 020-powerbi-optimizations.js) flatten the normalized schema into wide fact tables that PowerBI can query directly. These include fact_invoice_lines (joining invoice headers, lines, customers, products, departments), fact_journal_entries (joining journals with accounts and departments), and fact_accounting (combining all accounting events into a single queryable surface). These tables are rebuilt after each sync cycle.
Python Flask Sync Service. A separate Python service (sync_api.py, 3,600+ lines) handles specialized sync operations: SharePoint integration for budget files and account mapping spreadsheets, CSV import processing, and an enhanced monitoring dashboard. It connects to the same MySQL database and provides its own health monitoring endpoints.
The Flask service includes two embedded HTML dashboards (yes, inline HTML in Python — pragmatic, not pretty) for sync status monitoring and system health checks.
The shape is hub-and-spoke: the E-conomic REST API and SharePoint feed in on one side, PowerBI consumes on the other, and MySQL sits in the middle as the central data store both services read and write.
Key Engineering Decisions
Daily automated sync at 3AM. The sync process runs as a PM2-managed Node.js process with cron scheduling. Each sync cycle iterates through all configured agreements, pulls incremental updates from the E-conomic API (filtering by last-modified date where the API supports it), upserts records into MySQL, and rebuilds the aggregation tables. Winston logging with daily rotation provides audit trails.
Error recovery with exponential backoff. API calls can fail for transient reasons (rate limits, timeouts, E-conomic maintenance windows). The API client implements exponential backoff with jitter: first retry after 1 second, then 2, 4, 8, up to a configurable maximum. If all retries fail for an entity type, the sync continues with other entities and logs the failure for the next cycle.
SharePoint integration for budget mapping. Budget plans and account mapping files are maintained as Excel spreadsheets in SharePoint. The Python sync service authenticates to SharePoint, downloads the latest files, parses them, and merges the data into MySQL. This keeps the budget and actual data in the same database for PowerBI to compare without manual file transfers.
PDF document management. The system tracks voucher PDF availability and generates download URLs. A migration adds pdf_availability tracking and augments the fact accounting view with PDF status — allowing PowerBI dashboards to indicate which accounting entries have supporting documentation attached.
Challenges and Trade-offs
Two languages, two services. The Node.js backend handles all E-conomic API interactions, and the Python Flask service handles SharePoint integration and specialized sync operations. This split happened organically — the SharePoint client library was more mature in Python, and the CSV processing was easier with pandas. The trade-off is operational complexity: two processes to manage, two sets of dependencies, two deployment configurations. PM2 manages both, but it’s not ideal.
Inline HTML dashboards. The Python sync service embeds full HTML dashboards as multi-line string literals in the Python source. This is the opposite of good architecture, and I know it. The pragmatic reason: I needed monitoring dashboards quickly, and splitting them into a separate frontend felt like over-engineering for a personal project used by two people. If this were a production service, the dashboards would be their own application.
No ORM for PowerBI tables. The aggregation tables are built with raw SQL in migration files rather than through an ORM. PowerBI queries benefit from very specific table structures (wide, denormalized, with particular column naming conventions), and expressing that through an ORM abstraction would have been more work than just writing the SQL.
Outcome
- Multi-agreement E-conomic data sync running unattended in production, pulling every configured agreement on its own daily schedule
- Each cycle ingests the full spread of accounting entities — invoices, journal entries, and accounting records — across all agreements in one pass
- PowerBI dashboards query the pre-aggregated fact tables directly instead of joining normalized data at report time
- Automated PDF document tracking surfaces which accounting entries have supporting documentation attached
- Budget vs. actual reporting powered by SharePoint-to-database sync, with no manual file shuffling
- Health monitoring with uptime tracking, sync history, and error alerting via PM2
Tech Stack
- Backend: Node.js, Express.js
- Database: MySQL (43 migrations, normalized + aggregation tables)
- Sync Service: Python 3, Flask
- API Integration: E-conomic REST API (multi-agreement)
- File Integration: SharePoint API (budget/mapping files)
- Reporting: PowerBI (pre-computed fact tables)
- Process Management: PM2 with cron scheduling
- Logging: Winston (daily rotation)