Selected work.

Challenge

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.

Approach

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

Outcome

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

Challenge

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.

Approach

1. Modelled the existing behavior 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

Outcome

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

Challenge

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

Approach

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

Outcome

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

Challenge

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.

Approach

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

Outcome

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

Challenge

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

Approach

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

Outcome

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