3.3Writing RCA Reports

RCA Report Template

# Root Cause Analysis: [Incident Title]
Date: [Date of incident]
Author: [Name]
Severity: [Sev-1 / Sev-2 / Sev-3]
Status: [Draft / In Review / Final]

## Summary
One paragraph: what happened, when, how long it lasted, what was affected.

## Timeline

| Time (UTC) | Event |
|---|---|
| 14:00 | Monitoring alert: database connection pool at 90% |
| 14:05 | On-call engineer acknowledged alert |
| 14:12 | Database connection pool exhausted; application returning 503 |
| 14:15 | Incident declared; war room opened |
| 14:25 | Slow query identified via database monitoring |
| 14:30 | Query killed manually; connections began recovering |
| 14:35 | Application fully recovered |
| 14:40 | Root cause identified: missing index on orders.created_at |
| 14:45 | Index added to production database |
| 15:00 | Monitoring confirmed stable; incident resolved |

## Impact
- Duration: 23 minutes (14:12 - 14:35)
- Users affected: approximately 3,200 (all users attempting checkout)
- Revenue impact: estimated $8,500 in lost transactions
- Support tickets: 47

## Root Cause
The migration that should have added an index to the `orders.created_at`
column failed silently during the v2.3.0 deployment (2 weeks prior).
Without the index, a new report query introduced in v2.4.0 performed a
full table scan on 50 million rows, consuming database connections
for 30+ seconds each.

## Contributing Factors
1. **Migration monitoring gap:** Migration failures log to a file but
   do not trigger alerts. The team was unaware of the failed migration.
2. **No query performance testing:** The CI pipeline does not test
   query performance against production-scale data volumes.
3. **Staging data mismatch:** Staging has 1,000 rows in the orders
   table; production has 50 million. The query performed well in staging.
4. **No connection pool circuit breaker:** When the pool fills, the
   application queues requests indefinitely instead of failing fast.

## Corrective Actions

| Action | Owner | Priority | Due Date | Status |
|---|---|---|---|---|
| Add alerting for migration failures | DevOps | P1 | 2026-02-21 | In progress |
| Add query performance tests to CI | QA | P2 | 2026-03-07 | Not started |
| Configure connection pool circuit breaker | Backend | P2 | 2026-03-07 | Not started |
| Create staging data seeding script (production-scale) | QA + DevOps | P3 | 2026-03-21 | Not started |
| Add slow query monitoring dashboard | DevOps | P2 | 2026-02-28 | Not started |

## Lessons Learned
- Silent failures are the most dangerous kind. If something can fail,
  the failure must be visible.
- Testing against unrealistic data volumes gives false confidence.
- Connection pool exhaustion cascades: one slow query can take down
  the entire application. Defense in depth (circuit breakers,
  timeouts, connection limits) is essential.