The situation
The client was a mid-sized accounting practice running a service line where every active client received a weekly "operations & cash" report on Monday morning. The report consolidated bank balances, cash forecasts, AR/AP aging, top deviations from budget, and a narrative paragraph that the senior accountant tweaked per client.
The data lived in four places: Xero (transactions, AR/AP), a Google Sheet (cash forecast model), a HubSpot CRM (client metadata), and a shared OneDrive (last week's reports for comparison). Three juniors rotated through "Monday morning report duty" — and each one assembled the report slightly differently. The senior accountant spent another 30–45 minutes per client on Tuesday "fixing" what was off.
The real problem
On paper, this looks like "automate the spreadsheet." It wasn't. The real problem was something we see often in accounting workflows: the team was treating the report as a document that gets assembled, when the underlying need was a system of record for client operating snapshots that happens to render as a document at the end.
Once you frame it that way, the work changes. You stop trying to make spreadsheet assembly faster. You start asking: what is the canonical snapshot, where does it live, how is it computed, and how do humans add the parts the data can't compute (the narrative)?
Our approach
Three principles, agreed in writing during scoping:
- The snapshot is the source of truth. The PDF, the email, and the dashboard are all renderings of the same underlying snapshot record. They cannot disagree because they all read from the same place.
- Numbers are auditable. Every number on the report can be traced back to the row(s) in the source system that produced it, in the version of the data at the time the snapshot was taken.
- Narrative stays human. The system pre-fills boilerplate based on the data, but the senior accountant always reviews and edits before the report sends. No "AI wrote your client's report" moments.
Architecture
1. The data layer
A small Python service runs every weekday morning. It pulls deltas from Xero, the Google Sheet (using the Sheets API), and HubSpot. Each delta is stored in a Postgres database with the timestamp it was pulled, the source, and the original record ID. We did not build a warehouse — this is operational, not analytical.
2. The snapshot engine
On Monday morning at 6am Manila time, the engine produces a snapshot per client: a single Postgres row with a JSON blob of all the structured fields the report needs, plus pointers to the source records that fed each computed number. Snapshots are immutable — re-running produces a new version, never overwriting the old one.
3. The dashboard (React + Vite)
A single-page web app the senior accountant uses on Monday mornings. Lists all clients, shows the snapshot for each, and lets them edit the narrative inline. Source-of-truth indicators show which numbers came from where, with a click-through to the source row in Xero or the Google Sheet for any number on the page.
4. The renderer
When the senior accountant clicks "Send", the system renders the snapshot to a branded PDF (using a template they could tweak themselves), attaches it to a templated email, and sends. The send action and the resulting message ID are logged.
Tech stack
- Python (FastAPI + SQLAlchemy) for the data ingestion and snapshot engine
- PostgreSQL for snapshots and the audit log
- React + Vite + TanStack Query for the dashboard SPA
- Tailwind for styling — the dashboard is internal, so we optimized for ship-speed over design polish
- WeasyPrint for HTML → PDF rendering with the client-branded template
- AWS — ECS Fargate for the API, RDS for Postgres, SES for the email send
- GitHub Actions for CI/CD with a staging environment that mirrors prod
- Sentry + CloudWatch for observability
Outcome
- Time to produce one report dropped from ~50 minutes of junior time + ~35 minutes of senior review time to ~90 seconds of senior review time for a typical client (more for edge cases — and that's the point).
- Total Monday morning for the team went from 4 hours of report assembly to roughly 35 minutes of review across the active book.
- Numbers agree across all renderings by construction. Discrepancies between "what's in the PDF" and "what's in Xero today" are now impossible — the snapshot is the timestamp, and we always render from the snapshot.
- Audit trail means any client question about "how did you arrive at this number?" is answered in 30 seconds, not 30 minutes of digging.
Build time: 6 weeks from Discovery to production cut-over, including a 2-week pilot where the new system ran in parallel with the manual flow so the team could trust the numbers before depending on them.
What we'd carry over to the next engagement
- Reframe before you build. The first version of the scope was "automate the report assembly." The version we shipped was "build a snapshot system that renders as a report." Different scope, much better outcome.
- Immutable snapshots are a gift to future you. When a client emails three weeks later asking "what was my cash position on the 14th?" — the answer is one query away.
- Source-of-truth indicators on every number create more user trust than any amount of "trust us, it's right" copy.
- Don't replace the senior's judgment with AI — make the senior's judgment 10× faster by removing what shouldn't have required judgment in the first place.