A case study in consolidating four unrelated data sources into one reporting system, and the architectural decisions that turned out to matter.
No employer, partner, or borrower is named here. Volumes are given in round numbers, dollar amounts are omitted, and nothing in this repository identifies anyone. What's left is the architecture and the reasoning, which generalize to any organization assembling a report from systems that were never designed to talk to each other.
A lending fund deploys capital through partner organizations. Around a dozen of them send a spreadsheet every quarter. Those spreadsheets get combined with a CRM database, a folder of compliance documents, and an advertising account, and the result goes into board packs and funder reports.
Before there was a platform, "combining" meant a person opening workbooks and retyping totals into a master spreadsheet. That took about a week per quarter, the master file was the only copy of the logic, and the only way to answer "why is this number different from last quarter" was to remember.
I built the thing that replaced it. Roughly 15,000 lines: 8,800 of Python across 22 modules, and 6,300 of front end.
Four sources, none of which agreed with each other about anything:
flowchart LR
W["partner workbooks<br/>Excel, one per org, quarterly"] --> R
C["CRM database<br/>Postgres, reached over SSH"] --> R
D["document store<br/>PDF, Word, Excel"] --> R
A["advertising API"] --> R
R["refresh<br/>collect, derive, validate"] --> J["one JSON payload"]
R --> S["SQLite mirror<br/>25 tables"]
J --> U["10-page dashboard"]
S --> X["analysts' own tools"]
style R fill:#e8f4ea,stroke:#4a7c59
style J fill:#fdf0e3,stroke:#b5761f
style S fill:#fdf0e3,stroke:#b5761f
The workbooks have inconsistent column names and sheet layouts. The CRM lives behind an SSH hop. The document store is a folder tree where compliance status has to be inferred from file contents. The ad account is a rate-limited API with its own vocabulary.
Each one is slow, and each one can fail on its own schedule.
The dashboard reads one JSON payload. It never touches a database, a network share, or an API.
That sounds like a limitation and it is the single reason the thing was usable. Live-querying four sources means the page is as slow as the slowest one and as available as the least available one. A shared drive that goes unreachable during a board meeting is not a hypothetical.
Decoupling the read path from the refresh path means the page always renders, instantly, from whatever was last known good. The cost is that the data is as old as the last run, and the fix for that is to put the age on the screen rather than to make the page faster.
Each collector returns its own block with its own availability flag. When the CRM query fails, the pipeline loads the last good snapshot, serves that, and marks it stale with the timestamp of when it was actually fresh.
This is the decision I'd defend hardest. The tempting alternative — fail the whole refresh if any source fails — produces a dashboard that is frequently unavailable. The dangerous alternative — substitute zero and carry on — produces a dashboard that is confidently wrong, because on a chart a zero and a missing value look identical and mean opposite things.
Serving stale data with its age attached is the only one of the three that never lies.
Source age, refresh status, last successful run and staleness thresholds all ship inside the payload. The front end renders them because they're fields, not because someone remembered to check a log.
There are 14 thresholds in a config file — how old a submission can be before it's flagged, what delinquency rate raises a warning, what counts as too much concentration in one partner. They live in configuration because they are business judgments, and a business judgment hardcoded in Python is a business judgment nobody will ever revisit.
Alongside the payload, every refresh writes a 25-table relational mirror.
The dashboard is one view of the data. It is not the only question anyone will ever ask. Analysts have their own tools and their own questions, and the choice is between them going through me every time or them connecting Excel to a table.
It took an afternoon and removed most of the ad-hoc requests. Nothing else in the system did that much good for that little work, and nothing else is as invisible, because its success looks like nobody asking.
I audited the reporting pipeline partway through and found eleven places where a label and its calculation disagreed. Writing that up produced a separate case study. Fixing it produced most of the interesting code in this one.
Coverage sits next to every demographic share. A field a partner never collected renders as Missing, never as 0%. Rolling twelve-month windows are built on a complete calendar spine so a quiet month can't stretch them to fifteen. Charge-offs are attributed to the quarter a loan was originated in rather than the quarter it was written off. Estimates say they're estimates.
None of that is visible as a feature. All of it is the difference between a report that is arithmetically correct and one that is also true.
| Python | 8,800 lines, 22 modules |
| Front end | 6,300 lines, no framework, no build step |
| Orchestrator | 2,885 lines, four collectors |
| Payload | 21 top-level sections |
| SQLite mirror | 25 tables |
| Dashboard | 10 pages |
| Configurable thresholds | 14 |
| Refresh | scheduled, unattended |
The orchestrator is too big. 2,885 lines in one module, and a single function inside it runs to 706. It grew that way because each source was added when it was needed, and the seams between collection, derivation, and validation blurred every time. Everything I've extracted from it since has been an exercise in finding those seams after the fact, which is harder than drawing them at the start.
Testing came last, and it shows. The platform has one small test file. Every piece I've since pulled out of it has a real suite, and writing those suites is where I found bugs that had been in production for months — a reserve ratio computed against the wrong denominator, a parser silently returning zero for a currency symbol. Those were live the whole time and nothing caught them, because nothing was looking.
If I built this again, the collectors would have tests before the dashboard had pages.
It's a batch job wearing a web app. Single machine, scheduled refresh, no queue and no retry beyond the cached-snapshot fallback. That was the right call for the size of the problem and it's the first thing that breaks if the number of sources doubles.
No incremental refresh. Every run redoes all four sources from scratch, including the ones that couldn't possibly have changed. Fine at this scale, wasteful at any larger one.
Six pieces have been extracted, cleaned up, and published on their own. Each works standalone, which is why they're separate repositories rather than one:
| Repository | What it was in the platform |
|---|---|
| lending-portfolio-dashboard | the front end, running on generated data |
| statement-extract | reading figures out of the compliance documents |
| entity-match-pipeline | matching CRM records against the loan book |
| impact-data-quality | checking partner submissions before they count |
| google-ads-connector | the advertising collector |
| snapshot-diff | comparing one quarter's export against the last |
The orchestration that ties them together isn't published and won't be. It's specific to one organization's file layouts and credentials, and it's the least reusable code in the system.
The hard part was never the analysis. It was that four systems each held part of the answer, none of them agreed on what a partner was called, and all of them could be unavailable at the moment somebody needed a number.
Most of what made the platform work was deciding what to do when a source was missing, stale, or wrong — and choosing, every time, to show that state rather than paper over it. A dashboard that admits it's twelve hours old is more useful than one that looks current and isn't.