A read-model projection has a bug that has been silently producing wrong denormalized data for three months. What is the safe way to fix and recover this in production, and what design decisions made earlier would have made this easier?

## The two halves of the recovery When a read-model projection has been silently wrong for months, the recovery has two separate parts: fixing the code that produced wrong data going forward, and repairing the historical data that's already wrong. Because a read model is, by design, a **derived and disposable copy** of the write side rather than a source of truth in its own right, the safe move for the historical repair is not to hand-edit the bad rows but to regenerate the read model from whatever the authoritative history is — a fully event-sourced event log, a change-data-capture stream retained long enough, or, at minimum, the current state of the write-side database if per-event history isn't available. ## The practical sequence The practical sequence looks like: 1. identify and fix the root cause in the projector code; 2. decide what to replay from and confirm that source actually goes back far enough to cover the three-month window; 3. build the corrected read model **out-of-band**, in a new table, index, or namespace, rather than in place, so the currently-serving (wrong) read model keeps answering live traffic while the rebuild runs; 4. validate the new build against a sample of hand-checked expected values, or against an independent reconciliation source, before trusting it; 5. and then atomically switch reads over to the new version — a **blue-green cutover** — and only decommission the old one once the new one has been observed correct under real traffic for a while. ## Why regenerating beats hand-editing This whole approach only works because the write side, or the event log, is still the ground truth throughout the incident: the read model's job was always to be a cheap, fast, rebuildable projection of that truth, never a place where facts are created. If a team instead tries to 'just UPDATE the bad rows' directly in the read store, they're now doing manual, ad hoc data surgery against a live table under production load, with: - no systematic way to know they found every affected row - no audit trail proving the fix was complete - a real risk of making things worse And the fix has to be re-derived by hand for every future bug of this kind, instead of 're-run the (now-correct) projector.' ## Rebuild everything, or repair a slice The main trade-off is the cost and blast radius of a full rebuild versus a narrower, targeted repair. | Full rebuild | Targeted repair | |---|---| | Replaying the entire event history or fully re-scanning the write database. Conceptually simple and provably correct if the projector logic is now right, but at scale it can take hours or days, requires double the storage during the cutover window, and needs the replay source to still exist and be complete going back far enough. | Compute exactly which read-model rows were affected by the bug (e.g., events of a specific type in a specific date range) and reprocess only those. Much faster and cheaper, but it's harder to prove correct: it requires being certain the bug's blast radius was fully and correctly characterized, and any mistake in that scoping leaves silently-wrong data behind, which is exactly the failure mode that caused the three-month incident in the first place. | Senior/principal-level judgment here is choosing based on data volume, replay-source availability, and confidence in the bug's scope, not defaulting to either option reflexively. ## Failure modes Several failure modes can make this materially harder than the clean version above. 1. **Retention.** If the event log has a retention policy or has been compacted (common in stream-based systems, where topics are often retained for days or weeks, not months), a full replay from three months ago may simply be impossible, forcing a hybrid strategy of 'restore from the oldest available snapshot, then replay whatever incremental history remains.' 2. **A moving write side.** If the rebuild takes long enough, the write side keeps moving during the rebuild, so a naive 'replay to now, then cut over' can miss events that arrived during the rebuild window itself — the rebuild process needs to either catch up in a tight loop until it's within an acceptably small lag, or the cutover needs to briefly pause writes or use a dual-write/backfill overlap strategy. 3. **The missing safety net.** The deepest failure mode is the one implied by 'silently producing wrong data for three months': there was no reconciliation process comparing the read model against the source of truth, so nothing caught the drift until a human noticed by accident — the bug being three months old is itself evidence of a missing safety net, not just a code defect. ## The decisions made long before the bug The design decisions that make this whole class of incident cheaper to recover from, and faster to detect, are ones made well before the bug ever ships: - retaining the write-side event history (or CDC log) for long enough to replay from, even if it means a cheap cold-storage tier rather than the live topic - writing projectors idempotently so any replay — full, partial, or repeated — produces the same result without needing careful sequencing - versioning read models (`read_model_v2` alongside `read_model_v1`) so a rebuild can happen entirely out-of-band with a clean cutover instead of a risky in-place migration - running a periodic reconciliation job that checksums or spot-checks the read model against the source of truth, so drift is caught in days, not quarters