Client
A UK unitary local authority adult social care directorate, covering assessment, care planning and statutory review for several thousand open cases
Sector
Local Government & Social Care
Engagement
Consolidation of three legacy case management systems into a single platform, with automated statutory review tracking - phased delivery over three quarters.
What the client needed
Two council mergers over the preceding decade had left the adult social care directorate running three separate case management systems side by side, none of which had ever been properly retired because each still held case history nobody wanted to lose. A social worker picking up a new referral often had to log into two or three systems to build a full picture of a resident's history, and reviews required under the Care Act - the statutory check-ins that confirm a care plan still matches someone's needs - were tracked on a shared spreadsheet maintained by a duty officer, because none of the three legacy systems could produce a single reliable view of what was due and when. By the time we were engaged, internal audit had flagged a backlog of overdue reviews large enough to represent a genuine safeguarding risk, not just an administrative one, and the directorate's own leadership could not say with confidence how large that backlog actually was, because the three systems disagreed with each other and with the spreadsheet.
How we worked
- Migrated case records from all three legacy systems into a single platform, reconciling duplicate residents and conflicting history rather than simply importing each system's data as a separate silo.
- Built automated statutory review tracking directly against Care Act timescales, replacing the duty officer's spreadsheet with a system that flags a review as due, then overdue, without anyone needing to check.
- Designed the case view around a single resident timeline spanning all historic and current involvement, so a social worker sees one coherent picture regardless of which legacy system originally held each entry.
- Built a caseload dashboard for team managers showing overdue and at-risk-of-overdue reviews by worker and by team, replacing a monthly manual count that was frequently several weeks stale.
- Ran the migration in phases by locality team rather than as a single cutover, so any data reconciliation issues surfaced and were fixed on a smaller caseload before the next team migrated.
- Trained duty officers and team managers on the new review dashboard before decommissioning the spreadsheet, so the safety net it provided was never actually absent during the transition.
Measured results
All figures verified with the client. Specific team, locality and case detail withheld in line with our standard confidentiality terms and safeguarding sensitivity.
- Overdue statutory reviews fell by more than three quarters within two quarters of the dashboard replacing the spreadsheet, once managers could see the backlog in real time rather than in a monthly snapshot.
- Social workers report substantially faster case pickup on new referrals, no longer needing to search two or three legacy systems to establish a resident's history before a first visit.
- The caseload dashboard is now checked daily by team managers rather than monthly, catching reviews approaching their deadline before they become overdue rather than after.
- Two of the three legacy systems have since been fully decommissioned, removing their ongoing licensing cost and the manual reconciliation work they previously required.
- Internal audit's follow-up review found the backlog risk it had originally flagged had moved from red to green, citing the platform's review tracking specifically as the reason.
"We knew reviews were slipping. What we didn't know was how many, because our own systems couldn't agree on the answer. Now a manager can open one screen and see exactly what's due this week, and that's changed how the whole team works, not just how we report to audit."
Working on something similar?
If this engagement looks like the kind of problem you are facing, we would be glad to compare notes by email.
Why migration order mattered more than migration speed
The obvious way to consolidate three systems is to move fastest where the data is cleanest, leaving the messiest system for last once the team has practice. We did the opposite deliberately, starting with the locality team whose caseload carried the highest current safeguarding risk, because that was the team where a data reconciliation error would matter most if one occurred. Moving slower on the highest-stakes caseload first, rather than saving it for when the team was most experienced with the migration, meant any serious issues surfaced under close supervision on a smaller, closely-watched cohort rather than at scale once confidence had built up. That ordering cost us time relative to a cleanest-first approach. Given what these records represent, we'd make the same call again.
The spreadsheet wasn't the problem. The disagreement between systems was.
It would be easy to tell this story as "council replaces spreadsheet with proper system," but that undersells what the duty officer's spreadsheet had actually been doing: it was the only place anyone had tried to reconcile three systems that didn't talk to each other into one honest answer. The spreadsheet wasn't badly designed. It was a manual patch over a structural gap, maintained by someone doing their best with tools that weren't built for the job, and it had been quietly absorbing risk that should never have been one person's manual responsibility. Replacing it wasn't really the goal. Removing the need for anyone to do that reconciliation by hand was.
Getting the resident timeline right took longer than the technical migration
Merging three systems' worth of case history into one coherent timeline per resident sounds like a data engineering problem, and much of it was. But a meaningful share of the effort went into something less technical: agreeing, with practising social workers, what "coherent" actually meant for a case file that a colleague might need to understand quickly during an out-of-hours crisis. A timeline that's technically complete but poorly ordered or badly summarised doesn't actually solve the original problem. We iterated the case view directly with duty social workers across several sprints before wider rollout, which is time we'd budget for again on any project where the end user's ability to understand a record quickly, under pressure, is itself part of the safety case.
Lessons learned
The first lesson was that legacy system consolidation in a safeguarding-adjacent service needs a migration order driven by risk, not by ease, even when that's the slower path.
The second lesson was that a well-used manual workaround, like the duty officer's spreadsheet, is usually a symptom worth taking seriously rather than a habit to simply switch off - it's telling you exactly where the structural gap sits.
The third lesson was that a case management platform's real usability test is whether a social worker under time pressure can understand a resident's history quickly, and that test is worth running with real users well before go-live, not after.
If your organisation is managing case history across systems that don't agree with each other, and can't currently say with confidence what's overdue or at risk, we would be glad to discuss what a consolidation programme like this might look like for you. Email sales@halfteck.com.