Skip to content
juancespedes.dev
ES: Leer esta página en español
All projects
Case studyAnonymized

Rescuing an e-invoicing ETL that failed silently

I inherited a pipeline that lost invoices while reporting success. I audited it against production and put it under tests, CI and tag-based deployment; today it fails loudly, alerts on its own and can be audited invoice by invoice.

Project facts

Role
Data engineer in charge of the rescue and of running the system
Period
Aug 2026 to present
Public links
Internal system: the code is private, and names, data and diagrams were left out or recreated.

  • 74

    audit findings, each one backed by evidence measured against productionSource: Technical audit of the system, August 2026

  • 0 to 151

    automated tests, in a codebase that arrived with noneSource: Repository pytest suite, September 2026

  • 532

    portal rows cross-checked against three databases to size an incident the same daySource: Month-change incident record, September 2026

The problem

A pipeline built by a third party downloaded supplier e-invoices from the invoicing operator's portal, converted them and loaded them into the financial ERP of two companies, three times a day. It arrived with no version control, no tests and a serious defect, because it could lose invoices and still finish green.

My role

  • Audited the system end to end and documented 74 findings, with a summary written in the business team's language.
  • Put it under git, tests, CI with branch protection and tag-based deployment, without stopping the daily runs.
  • Fixed the failures that lost invoices and migrated the primary key of the target tables, rehearsed first in development.
  • Run the system, including incidents, reconciliations, deployments and communication with the finance team.

Constraints

  • The process could not stop, because the accounts payable of two companies depend on it.
  • The source portal belongs to a third party and changes without notice, and downloading changes the state of the real process.
  • The target databases are shared with other systems, and every change is coordinated with their administrator.
  • The orchestrator is shared with six other workflows.

Decisions

  1. 01

    Audit and measure before fixing

    A baseline measured against production made it possible to prioritize by business impact and prove each improvement with numbers, instead of rewriting on instinct.

    Ruled out

    • Rewrite the pipeline from scratch
  2. 02

    Extract a pure, tested domain from the scraping code

    Calendar, pagination and classification rules moved into modules with no network or secrets, with doctests as the contract. They run in under a second without touching the portal.

    Ruled out

    • Test only end to end against the real portal
  3. 03

    A run that did nothing finishes red

    The original defect was reporting success without loading anything. Now every task verifies its result and, if the count does not add up, fails and sends an email alert.

    Ruled out

    • Add more retries and leave the status green
  4. 04

    An MCP server to diagnose the orchestrator

    It exposes runs, failures grouped by signature and root-cause analysis of logs as tools for an AI agent. The two tools that write simulate by default and require explicit confirmation.

    Ruled out

    • Read the logs by hand in the orchestrator UI

Architecture

A three-task Apache Airflow flow downloads invoices from the portal with Selenium, converts the XML into MongoDB documents and inserts them into each company's Oracle database, where the ERP picks them up. A reconciler cross-checks the four stores and an MCP server exposes the orchestrator for diagnosis.

  1. Source

    • Invoicing operator portalPDF and XML for every invoice

  2. Orchestration

    • DownloadSelenium Grid, calendar and paginated grid
    • ConversionUBL XML to document
    • LoadRouted by receiving company

  3. Stores

    • Shared diskOriginal files
    • MongoDBTraceability for every document
    • Oracle per companyTables the ERP reads

  4. Consumer

    • Financial ERPAccounts payable for two companies

  5. Operations

    • ReconcilerCross-checks portal, disk, MongoDB and Oracle
    • Diagnostic MCP serverRoot cause and simulated reprocessing

How data moves

  1. Invoicing operator portaltoDownloadThree times a day
  2. DownloadtoShared diskPDF and XML
  3. Shared disktoConversionFiles for the period
  4. ConversiontoMongoDBOne document per invoice
  5. MongoDBtoLoadPending invoices
  6. LoadtoOracle per companyInvoices per company
  7. Oracle per companytoFinancial ERPReceived in the ERP
  8. ReconcilertoOracle per companyVerifies invoice by invoice
  9. Diagnostic MCP servertoDownloadReads runs and logs through the orchestrator API

Results

  • The process that reported success while losing invoices now fails red and sends an alert.
  • A month-change incident was sized the same day, with exactly 63 pending invoices, a reply to the business team before noon and a root fix tested live.
  • About 1,050 invoices from one month were verified one by one against the target database.
  • Every production log maps to its commit, because deployment is tag-based and each run prints its version.

What I would do differently

  • I would build the cross-store reconciler before any fix; it is the tool that turns a suspicion into a number.
  • I would negotiate a test environment for the source portal from the start, because much of the risk came from not being able to rehearse downloads without touching the real process.

Technologies

  • Python
  • Apache Airflow
  • Selenium
  • Oracle
  • MongoDB
  • Docker
  • pytest
  • GitHub Actions
  • Model Context Protocol
  • Legacy code rescue
  • Incident forensics