Selected work

The problem

Search was a set of LIKE queries against the listings table. At three hundred thousand rows the median query passed four seconds and the database spent most of its time on sequential scans.

What we did

1. Measured the real query mix instead of the assumed one — 80% of traffic hit five filter shapes
2. Moved those shapes to a denormalised read model updated by change events
3. Added cursor pagination so deep pages stopped re-scanning from the start
4. Kept the old path behind a flag until the new one matched it on a replayed week of traffic

What changed

Median search latency fell below two hundred milliseconds and stayed there through a hundredfold catalogue increase.

The problem

Every balance was derived by summing a mutable transaction table, so a single corrected row silently rewrote history. Reconciliation took three days a month and no two reports agreed.

What we did

1. Modelled the existing behaviour as a set of invariants before changing anything
2. Introduced an append-only journal alongside the legacy table, dual-writing for six weeks
3. Backfilled two years of history and reconciled the two systems daily until they matched
4. Cut reads over to the ledger one report at a time, with a per-report kill switch

What changed

The legacy table was dropped without a maintenance window. Month-end reconciliation is now automated and finishes in minutes.

The problem

Position and diagnostic data landed in flat files processed overnight, so dispatchers were answering today's questions with yesterday's data.

What we did

1. Replaced file drops with a streaming ingest that tolerates duplicate and out-of-order events
2. Modelled watermarks explicitly so late data corrected earlier answers instead of being dropped
3. Built a query layer over pre-aggregated windows for the dispatch board's fixed questions
4. Kept the nightly batch running as a reconciliation check for the first quarter

What changed

Dispatchers see vehicle state within seconds, and the nightly batch was retired once six weeks of results matched.

The problem

Intake was one long form that validated only on submit. Losing a session lost everything, and the abandonment rate meant staff re-keyed most submissions from phone calls.

What we did

1. Split intake into resumable steps with server-side draft persistence
2. Validated each field at the point of entry, with plain-language messages
3. Added an audit trail so a partially completed record could be picked up by a colleague
4. Instrumented every step so drop-off could be attributed to a specific question

What changed

Completion rose from a third to nearly nine in ten, and phone re-keying stopped being part of the workflow.

The problem

Assignment, review and scheduling lived across a shared spreadsheet, three inboxes and a chat channel. Nobody could answer what was publishing on Thursday.

What we did

1. Shadowed the newsroom for a week before proposing a model
2. Built states that matched what editors already said out loud, not an idealised pipeline
3. Made the calendar the primary view, because that was the question being asked
4. Migrated in-flight work so the spreadsheet could be archived on day one

What changed

The spreadsheet was retired in the first week and the calendar became the newsroom's default screen.