Claims read-model rebuild for a document-heavy back office

Assessors worked a queue of claims, and opening one meant the application assembled the case view from eleven tables plus two remote services, live, on every request. It took anywhere from six to twenty-five seconds, and assessors had learned to open several tabs in advance to hide the wait. The write side was fine; nobody wanted to touch it. The brief was to make the reading fast without a rewrite of the transaction model, which had been audited and was not up for discussion.

Senior engineer on the platform team, assigned to this for about five months. I was the only person working on the read model, with the workflow team reviewing my changes.

I designed and built the denormalised read model and the projection process that keeps it current, including the reconciliation job that compares projections against source rows and repairs drift. I owned the cache invalidation, which is where most of the real complexity lived. I rewrote the case-view endpoint against the new model and wrote the load test we used to prove the improvement. The runbook and the alerting on projection lag are mine.

I chose asynchronous projections with an explicit staleness budget rather than synchronous updates inside the write transaction. Synchronous would have been simpler to reason about and would have slowed every write to satisfy a read problem, which is backwards. Accepting eventual consistency meant the interface had to be honest about it, so anything projected shows a subtle updated-at and the assessor's own edits read through to the source — a compromise that took two rounds with the workflow team to land. I also kept the projections in the same PostgreSQL instance rather than introducing a separate store: one backup, one failover story, and the table is small enough that it was never the bottleneck.

The write path and its audit trail were frozen — I could read from it and nothing else. The environment was a private data centre with a fixed hardware budget, so scaling out was not available; whatever I built had to fit the machines already there. Releases went out fortnightly on a fixed train, so each change had two weeks to be wrong in production before I could fix it, which made me much more careful about the reconciliation job.

The case view went from a six-to-twenty-five second range down to a median of about four hundred milliseconds, with the ninety-fifth percentile under a second. Assessors stopped pre-opening tabs, and the workflow team measured roughly fifteen percent more cases closed per assessor per day over the following two months — I would not claim all of that was the read model, but the timing lines up. Projection lag has stayed under two seconds outside of bulk imports, and the reconciliation job has repaired drift eleven times, each time silently.

The workflow team's lead engineer reviewed every projection change and caught two places where I had silently changed a business rule. A business analyst mapped which fields assessors actually looked at, which cut the projected surface almost in half. Our DBA tuned the final indexes and was right about all of them.