# Postmortem: Subscription Renewal and Fulfillment Outage

**Incident date:** July 22, 2026  
**Duration:** 2 hours 47 minutes  
**Severity:** SEV-1  
**Status:** Resolved

## Summary

On July 22, our subscription renewal service stopped processing scheduled coffee orders after a database migration introduced an incompatible index. Customers could still browse the site and manage their accounts, but renewals, payment capture, and fulfillment requests were delayed.

The outage affected 8,412 subscriptions. No orders were permanently lost or duplicated, and no customers were charged more than once. All delayed renewals were processed within six hours of recovery.

## Customer Impact

- 8,412 subscription renewals were delayed.
- 2,106 customers received shipment confirmation later than expected.
- 637 orders missed the day's warehouse cutoff and shipped one business day late.
- 184 customers encountered an error when manually requesting an immediate shipment.
- Customer support received 391 incident-related contacts.
- Browsing, new account creation, and one-time purchases remained available.

## Timeline

All times are in Eastern Daylight Time.

- **09:00** — A database migration was deployed to production as part of the subscription scheduling release.
- **09:07** — Renewal-job throughput began declining as workers encountered query timeouts.
- **09:14** — The first automated alert fired for elevated worker error rates.
- **09:18** — The on-call engineer acknowledged the alert and began investigating.
- **09:27** — The team identified database lock contention on the `subscription_schedule` table.
- **09:35** — The release was paused, and the renewal worker fleet was scaled down to reduce database pressure.
- **09:48** — Engineers determined that the new index was not being used by the production query planner.
- **10:06** — The migration was rolled back, but long-running transactions continued to hold locks.
- **10:19** — The incident was escalated to SEV-1, and customer support was notified.
- **10:32** — Blocking transactions were safely terminated, restoring normal database latency.
- **10:41** — Renewal workers were restarted at reduced concurrency.
- **11:03** — Successful renewal throughput returned to normal.
- **11:21** — Workers began processing the accumulated backlog.
- **11:47** — The system was declared stable, ending the active outage.
- **15:38** — The renewal backlog was fully processed.
- **17:10** — Operations confirmed that all affected fulfillment requests had reached the warehouse.

## Root Cause

The outage was caused by a database migration intended to improve renewal scheduling performance. The migration replaced a partial index on `subscription_schedule` with a composite index optimized for a new query pattern.

Production still had a legacy worker version using the previous query pattern. Because the deployment process started the database migration before all workers had been upgraded, those workers could not use the new index. Their queries degraded from indexed lookups to repeated scans of the scheduling table.

As worker concurrency increased during the morning renewal window, the scans consumed database connections and created lock contention with the migration transaction. Renewal queries began timing out and retrying. Those retries added further load, producing a feedback loop that exhausted the worker connection pool and halted renewal processing.

## Contributing Factors

- The migration assumed all renewal workers would be upgraded before processing production traffic.
- Application and database changes were deployed as one release without an explicit compatibility window.
- Staging contained significantly fewer subscription records and did not reproduce the production query plan.
- Retry behavior used fixed intervals without jitter or a global concurrency limit.
- The initial alert identified worker errors but did not directly identify database lock contention.
- The rollback procedure removed the index change but did not address transactions already holding locks.
- The morning renewal window concentrated a large percentage of daily processing into a short period.

## Resolution and Recovery

We rolled back the migration, terminated the remaining blocking transactions, and restarted renewal workers at reduced concurrency. Once database latency stabilized, workers processed the backlog in controlled batches.

Before increasing concurrency, we verified payment-provider idempotency records and order identifiers to ensure retries could not create duplicate charges or shipments. We then reconciled renewal, payment, and fulfillment records and confirmed that every affected subscription reached a terminal state.

## What Went Well

- Automated error-rate alerts detected the failure within seven minutes.
- Payment and fulfillment idempotency controls prevented duplicate charges and shipments.
- The renewal queue retained all scheduled work during the outage.
- Engineering, operations, and customer support coordinated through a single incident channel.
- Batch recovery allowed the backlog to be processed without triggering another load spike.

## What Went Poorly

- The migration was not backward-compatible with the worker version still running in production.
- Our staging dataset was too small to reveal the query-plan regression.
- Database lock visibility was not included in the primary incident dashboard.
- The incident was not escalated to SEV-1 until more than an hour after the first alert.
- Customers did not receive a proactive notification about delayed shipments.

## Action Items

| Action | Owner | Priority | Due Date |
|---|---|---:|---|
| Require backward-compatible database migrations across at least two application versions | Platform Engineering | P0 | August 7, 2026 |
| Add production-scale query-plan tests for renewal scheduling queries | Data Engineering | P0 | August 14, 2026 |
| Separate schema migrations from application deployments | Release Engineering | P0 | August 14, 2026 |
| Add exponential backoff, jitter, and global concurrency limits to renewal retries | Subscriptions Team | P0 | August 10, 2026 |
| Alert on database lock duration, blocked queries, and connection-pool saturation | Site Reliability Engineering | P1 | August 7, 2026 |
| Add automatic circuit breaking when renewal-query latency exceeds thresholds | Subscriptions Team | P1 | August 21, 2026 |
| Expand staging with anonymized production-scale subscription distributions | Data Engineering | P1 | August 28, 2026 |
| Distribute scheduled renewals more evenly throughout the morning window | Subscriptions Team | P1 | September 4, 2026 |
| Update the rollback runbook to include lock and transaction cleanup | Site Reliability Engineering | P1 | August 5, 2026 |
| Add proactive customer messaging for shipment delays exceeding two hours | Customer Experience | P2 | August 21, 2026 |
| Conduct a game day covering renewal backlog recovery and reconciliation | Site Reliability Engineering | P2 | September 11, 2026 |

## Lessons Learned

Schema changes must remain compatible with every application version that may be running during a deployment. A migration that is safe in isolation can still cause an outage when rollout order, production data volume, retries, and peak traffic interact.

We are updating our deployment controls, load testing, retry policies, and observability so that similar failures are detected earlier and remain contained.