Boekjaar Data Warehouse

Context

Boekjaar sells automated annual reporting. That work starts as bookkeeping data sitting in whatever accounting system the customer already uses, and it has to end as a fiscal year report somebody can file. I worked directly with the co founder, which in a startup means the business question and the engineering answer are one conversation.

The challenge

A report is only automatic if the data underneath it is already in the right shape, and bookkeeping data is not. It arrives per customer and per accounting system, each with its own idea of what a ledger line is. The harder problem is the second report rather than the first: a warehouse modelled around today's questions needs a schema change for tomorrow's, and a schema change in the middle of a reporting platform is expensive.

What I did

• Built the integrations with Exact Online and e-Boekhouden, which is where the bookkeeping data actually lives, and normalised what each of them sends into one shape the platform can reason about.
• Designed the data warehouse behind the automated annual reporting, 25 MySQL tables modelling the fiscal year data, fed by those integrations on one side and read by the platform's own APIs on the other.
• Modelled it so new analyses compose on top of it, rather than requiring a schema change, which is the difference between a warehouse that grows and one that gets rebuilt.
• Handled the authorisation with OAuth 2.0, because this is a customer's financial administration and access to it has to be theirs to grant and theirs to withdraw.
• Ran it on AWS Serverless, so the cost follows the work, which for reporting that runs in bursts rather than continuously is the shape that fits.
• Covered the flows with Playwright, end to end, on the paths where a wrong number is worse than a missing page.
• Worked directly with the co founder, on what the reporting had to answer, so the model was built against real questions rather than assumed ones.

The outcome

• Annual reporting that runs on data the platform already holds in the right shape.
• Fiscal year analysis that users compose themselves instead of requesting.
• A model that took new analyses without being reopened, which is what makes the automation last.

Technologies used

• Node.js: The integration services and the APIs over the warehouse.
• AWS Serverless: How those services run, so the cost follows the work.
• MySQL: The warehouse itself, modelled so new analyses compose on top of it without a schema change.
• Data warehousing: The fiscal year model behind the automated reporting.
• OAuth 2.0: Authorisation behind the Exact Online and e-Boekhouden integrations.
• API development: The APIs the platform reads the warehouse through.
• Playwright: End to end coverage of the reporting flows.

Reporting people trust is a data modelling problem before it is a reporting one, and I would rather talk about the model than the dashboard.