Marshall Fire – Fire & Security Services

The challenge

Marshall Fire's project history sat in roughly 3,000 to 5,000 candidate records spread across a project-management Excel spreadsheet, an Azure SQL database, and a pile of SharePoint documents (emails, Word files, PDFs), and no single source held the truth. The data itself was very dirty: the same project appeared under different names in different places, fields disagreed or were simply missing, and values the new system treats as a fixed vocabulary (status, sector, project type) existed as years of accumulated free text. The agreed migration scope was the roughly 1,000 records that fell inside the project window. The destination system gave no direct database access, so every record had to go in through its web forms. Done by hand, each record meant cross-checking several sources, resolving where they disagreed, cleaning and standardising every value, and re-keying the result into a multi-step form. At that volume it is hundreds of hours of careful, error-prone work.

The approach

I built a pipeline that does the corroboration and submission automatically but keeps a person as the final gate on every record. The parts that made it non-trivial are the cross-source matching, the sheer amount of cleaning the data needed before the destination would accept it, and the fact that the AI and the human are two separate checkpoints rather than one.

1. Ingest: five sources into one

• Project-management Excel spreadsheet
• Azure SQL backup
• Azure SQL live scrape for records added after the backup cut-off
• SharePoint files: emails, Word documents, PDFs
• A scrape of the new system to detect what has already been migrated

All five land in a single local SQLite database, the working store for everything downstream.

2. Match and reconcile

rapidfuzz token-scoring matches the same project across Excel, Azure SQL and SharePoint, needed because names rarely matched exactly from one source to the next, and a three-way pass flags where the sources disagree. This is the step that turns five noisy inputs into one candidate record per project.

3. Local LLM (on-prem)

Qwen 2.5 7B runs locally through Ollama, on-premises, so none of the client's data leaves the machine. It runs two advisory passes: a judge and veto on the Azure SQL matches, and a verdict on fee lines before submission.

4. Validation and normalisation

This is where the state of the source data showed most, and where a large share of the project's time and care went. Years of free-text entry meant the same status, sector or project type turned up in many different spellings and phrasings, none of which the new system would accept as-is. Pydantic schemas validate every record, and canonical-value maps fold the mess into the exact values the destination system will accept. Records could not go in as they were; they had to go in as they should have been.

5. Human review

A Flask and HTMX review queue puts a person in control: a SharePoint evidence panel alongside each record, per-field overrides, and explicit approval before anything is submitted. The LLM is advisory; the human is the gate.

6. Submission

Playwright drives the destination's web forms in order, Contact then Company then Project, because direct database access was not available. It runs a list-page duplicate probe before each insert and re-queries after Save and Exit to catch silent failures, so the automation does not quietly skip or double-enter records. A post-submission audit and live-patch CLI verifies what landed and fixes records after the fact, so it is not fire-and-forget.

Outcome

By the agreed cut-off at the end of June 2026, roughly 900 of the 1,000 records in scope had been migrated into the new system, each one reconciled across the five sources, cleaned and standardised to the destination's vocabulary, validated, and approved by a human reviewer before submission. The client's own review of the database at handover found the majority had transferred accurately, and they took the remaining records in-house for manual entry and clean-up. The post-submission audit meant that handover happened from a known position: what had landed, and what remained, was explicit rather than guessed.

More of the work was in the state of the data than in the volume of it; getting each record clean enough to submit safely was the bulk of the effort. Running the model locally kept a confidential migration confidential, and the two-gate design, an AI veto plus human approval, gave the speed of automation without giving up control over what actually got written into the destination system.