MSP time-compliance portal
Billable hours were leaking. The API could not answer the question being asked.
A managed service provider could not tell whether its engineers were logging time. We built a near-real-time compliance portal on top of a PSA API that times out on any query broad enough to be useful.
Ask for more history. Get nothing at all.
Roughly 243,000 tickets exist. The failure is not slowness. It is a total loss.
20,920
result ok
An unbounded query times out server-side and returns nothing. No partial result, no cursor, no clue about the shape of the problem. The fix is not a cleverer query. It is accepting that you now own a synchronisation problem, and building the per-entity state that makes it resumable.
The problem
- Engineers were not consistently logging time, and billable hours were leaking.
- The PSA holds the data but cannot express the question: who has not logged, for how long.
- Roughly 243,000 lifetime tickets: any query broad enough to be useful times out.
What we built
- A Python ingestion service doing bounded historical backfill plus continuous delta sync on last-activity, with per-entity sync state so an interrupted run resumes rather than restarts.
- A FastAPI portal with a colour-coded compliance dashboard. Hours logged against target, days with zero entries, tickets worked with no time recorded, last-entry timestamp per engineer.
- Per-engineer drill-down: daily time chart, customers worked, time by billing code, ticket classifications, full ticket list.
- Roster classification as a self-serve screen, with every metric downstream obeying it.
Outcome
- Full history backfilled in around 70 seconds; 20,920 tickets and 5,216 time entries in continuous sync.
- Compliance visible per engineer per day, with red/amber/green thresholds instead of a spreadsheet.
- Roster changes handled by the team, not by a code change.
- A failed sync raises a banner across the app rather than quietly serving stale numbers.
What something like this costs
It took 19 working days, counted out of the repository rather than estimated afterwards.
A rate comparison, not a bill. Our figure is the published ladder from the pricing page, for the closest match to this shape of work. Working days regenerate from the repository on every deploy.
The full write-up problem, constraints, and the whole debugging story. About a 4-minute read
01 The problem
Engineers were not consistently recording time entries, and leadership had no visibility into daily work. Billable hours were being lost somewhere between the work happening and the work being recorded, and nobody could point at where.
The PSA holds the data, but its reporting cannot express the question that matters: which engineer has not logged time, for how long, against which tickets.
02 Constraints
- The account holds roughly 243,000 tickets across its lifetime. Any unbounded query against that history times out server-side. There is no "just fetch it all" path.
- Refresh had to feel live. Minutes, not a nightly batch, because the point is catching a gap while it is still today's problem.
- Compliance thresholds are per-person and depend on whether someone is an engineer, management, or excluded entirely. The API has no such concept.
03 What we built
- A Python ingestion service doing bounded historical backfill plus continuous delta sync on last-activity, with per-entity sync state so an interrupted run resumes rather than restarts.
- A FastAPI portal with a colour-coded compliance dashboard. Hours logged against target, days with zero entries, tickets worked with no time recorded, last-entry timestamp per engineer.
- Per-engineer drill-down: daily time chart, customers worked, time by billing code, ticket classifications, full ticket list.
- Roster classification as a self-serve screen, with every metric downstream obeying it.
- A settings screen holding API credentials and sync interval, so a rotated secret is a form submission rather than a redeploy.
Designing around an API that cannot answer the question
The obvious approach is to pull everything and compute locally. It dies immediately. An all-time ticket query against 243,000 records times out server-side and returns nothing at all, so there is no partial result to work with and no error that tells you the shape of the problem.
The ingestion was rebuilt as two distinct mechanisms with different jobs. A bounded backfill fetches history from a fixed start date, which makes the initial load a known, finite, resumable amount of work. On top of that, a delta sync polls on last-activity and keeps the window fresh. Sync state is tracked per entity, so extending the history horizon later means clearing one row rather than re-running the whole import.
The second problem was subtler and mattered more. The metric leadership actually wanted, *is this person compliant*, depends on a roster classification the PSA does not model. The obvious approach is a hard-coded list of engineer IDs. That list is wrong the first time somebody joins, and being silently wrong is exactly the failure this portal exists to prevent.
So classification became a first-class, editable concept in the product, and every metric derives from it. Roster curation is self-serve; it stopped being an engineering dependency and stopped being a source of quiet drift.