Stiekema StudioNederlands

Case sheet

Commission processing

A web application that reads the monthly commission statements from insurers and banks in 45 delivery formats and only passes them on to the data warehouse after checking and approval. It replaces a PowerShell process and a back-office package due to be retired at the start of 2027.

XBAS Business Intelligence B.V. · KVK 89736478 · Almere NL · Case provisieverwerking

Diagram of the commission processing: 45 delivery formats (Excel and CSV, PDF, scans of paper post, password-protected zips, ADN industry files) go through one upload button into the ingestion engine, which reads each file without a language model using a versioned profile per format; then come reconciliation on three totals (specification total, current account and bank receipt), the mapping of agent number to branch number, and the separate roles of processor (release) and approver (sign-off), after which read views take the month to the data warehouse. The last step, the journal entry to the accounting package, is dotted because it has not yet produced a real posting.
Plate · Commission processing

Summary

A Dutch financial services firm receives commission from insurers and banks every month, and almost every one of them delivers its statement differently: Excel, PDFs with and without a text layer, scans of paper post, password-protected zips, industry message files running to millions of lines. That processing ran through a PowerShell application on stored procedures and a back-office package that is being phased out, and much of the logic behind it lived in the heads of one or two people. The new application reads each format through a profile of its own, sets the delivery against the current account statement and the bank receipt, links every line to the right branch and stages what has been approved for the data warehouse, which turns it into the journal entry for the accounting package. The project started narrow on purpose at the end of June, with a single stream from start to finish: four days after the first line of code, a full month of that stream went through the ingestion engine, 110 batches and 40,639 lines, all validated. After that it grew, in consultation with the client, to cover almost every revenue group.

The brief

Replace the old commission processing before the back-office package stops on 1 January 2027. Notice on it had to go out around 1 October, and by then there had to be enough confidence that the new application could take over. There were no firm requirements at the start, and none could be derived from the old application either. Expectations within the finance team were cautious: at a team meeting in early August it was doubted whether parallel running would happen in the fourth quarter, and one estimate within the team put the switchover about a year later.

What I built

  • An ingestion engine with a versioned YAML profile per delivery format for recognition, extraction and validation; 45 profiles for Excel (including the old .xls format), CSV, digital and scanned PDFs, password-protected files, zips, Outlook messages and ADN industry message files
  • Deterministic extraction with no language model in the monthly run, a requirement of the control framework: every reading rule sits in the profile, and a change to a profile gets a new version
  • OCR for statements that arrive by post and are scanned, with a pinned model version, a review screen for scanned sources and reading corrections tied to the file's hash, so a correction never lands silently on a different delivery
  • Readers that are careful with memory: the ADN reader streams from the zip and keeps only the fields the profile names, and the PDF reader closes each page after use (for one delivery the peak went from 573 to 57 MB)
  • Reconciliation per payment stream and month on three totals: the sum of the specifications, the current account statement and the bank receipt
  • Mapping of the agent number that each insurer uses differently for the same branch onto the internal branch number, through an append-only mapping table that logs every remapping
  • A server-rendered web interface (FastAPI, Jinja2, htmx) with a dashboard and numbered stations from upload to approval, with a clickable process demo in HTML as the first test with users
  • An append-only data layer in SQL Server with 40 numbered, idempotent migrations, read views per insurer, period and stream as the interface to the data warehouse, and more than 2,400 automated tests
  • Hosting on Azure App Service in the same network as the data warehouse server, with sign-in through Entra ID, the database password in Key Vault and a deployment recipe with a check afterwards

In detail

  • One upload button for every delivery: the application recognises the file by name, column headers or content, reads it in the background and shows per file what happened to it
  • Columns are read by name, not by position: an extra column disturbs nothing, and a renamed column that is needed stops the run before a single line is in the database
  • Wrongly delivered files are refused, such as a file that has already been read in or a delivery for a month that has already been approved; a delivery can be withdrawn and offered again
  • A discrepancy does not block anything: the processor records it with a reason from a fixed list and a description that fits the description field of the posting, and the discrepancy travels on to the data warehouse
  • A worksheet per insurer with the particulars of that source, so that knowledge held by one or two people now sits in the application
  • Separate roles through Entra ID groups: a processor releases a payment stream per month and an approver signs it off. An approved month does not reopen; a correction goes into the following month
  • Full traceability: who read in, released or approved which file and when, including attempts that failed

Outcome

Just over three weeks after that team meeting, on 31 August, the finance team was working in the application with real monthly files; May, June and July have since been processed and approved. In testing, a delivery without discrepancies for one revenue group went through the application in ten minutes. According to the process owner that processing currently takes about three days, a saving of three to four days a month for that group alone. The finance team then wanted to run that group through the application straight away; that was deliberately put off so as not to delay the rest of the project. In mid-September the project lead noted that the work was moving faster than first expected. The last step is not yet proven: the chain through the data warehouse to the accounting package has not yet produced a real posting.

Back to the work