القائمة
WHEN: Council is reasoning about a PRD. You are the infra perspective (MySQL/Redis/Kafka/ES). Analyze for database, caching, events, search, monitoring, scaling.
التثبيت باستخدام Codex أو Claude انسخ هذا Prompt والصقه في Codex أو Claude أو مساعد آخر ليراجع صفحة Skill ويثبّتها لك.
استنادا إلى تصنيف SOC المهني
| name | reasoning-as-infra |
| description | WHEN: Council is reasoning about a PRD. You are the infra perspective (MySQL/Redis/Kafka/ES). Analyze for database, caching, events, search, monitoring, scaling. |
| type | rigid |
| requires | ["brain-read"] |
| version | 1.0.0 |
| preamble-tier | 1 |
| triggers | ["reasoning for infra","how should infrastructure work","infra architecture"] |
| allowed-tools | ["Write"] |
| Rationalization | Why It Fails |
|---|---|
| "No infra changes needed for this PRD" | Every PRD that touches data touches infra. Silence is not analysis — it is a missed dependency that surfaces as a production incident. |
| "We'll tune the cache later" | Cache TTL and invalidation strategy must match consistency requirements. Post-launch tuning creates production incidents when stale data causes silent failures. |
| "Schema is flexible, we'll adjust" | Every schema adjustment after launch is a migration. Unplanned migrations block rolling deploys and risk data loss without a rollback plan. |
| "Monitoring can wait until launch" | Post-launch monitoring means no baseline. Without a baseline, you cannot distinguish anomaly from normal. Instrument during build. |
| "Kafka config is just operational detail" | Partition count and retention cannot be changed after messages start flowing. Lock topic decisions before spec freeze or rebuild later under load. |
INFRA PRODUCES ANALYSIS ON EVERY PRD. EVERY SCHEMA CHANGE HAS A MIGRATION PLAN. EVERY CACHE KEY HAS A NAMING PATTERN AND TTL. EVERY KAFKA TOPIC HAS PARTITION COUNT AND RETENTION LOCKED BEFORE SPEC FREEZE.
If you notice any of these, STOP and do not proceed:
Before reasoning about any infrastructure component (Dockerfile, nginx config, terraform, CI pipeline, port allocation): Read the scan-codebase output for this repo:
~/forge/brain/prds/<task-id>/codebase/<role>/structure.txt — full file inventory including existing Dockerfiles, configs, and scripts~/forge/brain/prds/<task-id>/codebase/<role>/code-style.md — existing naming conventions for services, volumes, networks, and environment variablesSCAN.json hub scores (if present) — identifies shared infrastructure files referenced by multiple servicesNever assume service names, port numbers, or environment variable names — always derive from existing infra files. If code-style.md is absent, run /scan-codebase first.
You are the infrastructure team (database, caching, events, search, observability). Given a locked PRD, reason about:
What schema changes? What migrations? What safety gates?
Example:
What indexes? What partitioning?
What gets cached? What are the keys? What's the TTL? When does it invalidate?
Example:
user:{user_id} → expires 1huser:{user_id}:favorites → expires 30m, invalidates on POST/DELETEproduct:{product_id}:summary → expires 10mcache.invalidated topic, listeners refreshWhat about thundering herd? What about stale-while-revalidate?
What events? What's the schema? What about idempotency and ordering?
Example:
favorites.changed{ event_id, user_id, product_id, action: "added"|"removed", timestamp, idempotency_key }user_id partition key (all one user's events are ordered){idempotency_key}, consume-deduplicate patternWhat's the publish guarantee? (at-most-once, at-least-once, exactly-once)?
What gets indexed? How does it stay consistent with the database?
Example:
products{ id, name, description, category, price, availability, last_updated }What metrics? What alerts? What SLOs?
Example:
Write to ~/forge/brain/prds/<task-id>/council/infra.md:
# Infra Perspective
## Database (MySQL)
### Schema Changes
```sql
CREATE TABLE favorites (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
UNIQUE KEY uk_user_product (user_id, product_id),
KEY idx_user_id (user_id),
KEY idx_product_id (product_id)
);
user:{user_id} → user profile, TTL 1huser:{user_id}:favorites → user's favorite IDs (array), TTL 30mproduct:{product_id}:summary → product view-model, TTL 10muser:{user_id}:favorites, publish to cache.invalidated topicproduct:{product_id}:summary, publish eventSET key value EX 1 NX with exponential backoff on cache missfavorites.changed
user_id (ordered per user){
"event_id": "uuid",
"user_id": 123,
"product_id": 456,
"action": "added",
"timestamp": "2026-04-10T12:00:00Z",
"idempotency_key": "req-abc123"
}
dedup:{idempotency_key} with EX 86400products{
"id": { "type": "keyword" },
"name": { "type": "text", "analyzer": "standard" },
"description": { "type": "text" },
"category": { "type": "keyword" },
"price": { "type": "float" },
"availability": { "type": "keyword" },
"last_updated": { "type": "date" }
}
refresh_interval: 1s (near real-time, balance latency/load)Ready for: Council negotiation
## Anti-Pattern: "We'll figure it out in Prod"
Do NOT write:
- "Schema TBD"
- "We'll cache something"
- "Events later"
- "Search can be best-effort"
Every detail must be decided and written down BEFORE code starts. Infra changes are the hardest to roll back.
---
## Edge Cases & Failure Scenarios
### Edge Case 1: Database Connection Pool Exhaustion
**Scenario:** Slow queries block new connections. A single bad query holds 20 connections for 30s. All 100 pool connections are claimed. New requests queue infinitely.
**Detection:**
- Metric: Connection pool utilization > 95% for 1m
- Metric: Queued requests > 10
- Metric: Average query latency p99 > 2s (vs baseline 50ms)
- Alert: "DB connection pool > 90% utilization" (warn at 90%, page at 95%)
**Immediate Action:**
1. Kill slow queries: identify queries in state "running" > 30s via `SHOW PROCESSLIST`
2. Lower connection pool size gracefully: set max_connections temporarily to 80, drain excess
3. Degrade features: disable non-critical queries (search filters, analytics)
4. Page oncall: escalate to database team
**Recovery:**
1. Root cause: find slow query in logs, check query plan with EXPLAIN
2. Fix: add missing index, rewrite query, or update statistics
3. Monitor: verify query latency returns to baseline before reopening feature
4. Prevent: add query timeout (max 5s for user-facing reads)
---
### Edge Case 2: Cache Stampede / Thundering Herd
**Scenario:** User's profile cached at key `user:{user_id}`, TTL 1h. At exactly 1h, 1000 concurrent requests hit. Cache miss triggers 1000 database queries simultaneously. DB CPU spikes to 95%.
**Detection:**
- Metric: Cache hit rate drops from 85% to 20% in 1m
- Metric: DB query count spike > 500 QPS (vs baseline 100 QPS)
- Metric: DB CPU > 80%
- Alert: "Cache hit rate < 70% for 5m" (page)
**Immediate Action:**
1. Increase TTL: from 1h to 2h to spread misses
2. Enable stale-while-revalidate: serve stale cache for 5m, refresh async
3. Implement jitter: add ±10% random offset to TTL so misses don't align
4. Rate limit refresh: use SET key EX TTL NX to prevent duplicate writes
**Recovery:**
1. Monitor: verify cache hit rate returns to 80%+
2. Prevent: implement cache warming job (refresh hot keys every 50m, before expiry)
3. Long-term: use consistent hashing or predictive refresh
---
### Edge Case 3: Event Consumer Lag Spike
**Scenario:** Kafka topic `orders.created` has 10k msg/s producer. Consumer processes at 8k msg/s. Lag grows 2k msg every second. After 5m, lag = 600k messages. Consumer takes 10m to catch up.
**Detection:**
- Metric: Consumer lag seconds = (latest_offset - consumer_offset) / 1000 msg/s
- Alert threshold: lag > 1m (60s) → warn, > 5m (300s) → page
- Metric: Consumer processing latency p99 > baseline by 50%
- Alert: "Kafka consumer lag > 5m" (page)
**Immediate Action:**
1. Scale consumer: add 2 more consumer instances (if parallelizable by partition key)
2. Check for rebalancing: look for "consumer group rebalancing" in logs (stop/start cycle)
3. Check processor: if avg processing time > 1s, find blocking operation
4. Degrade features: if order processing is non-critical, pause and resume later
5. Page: escalate to platform team
**Recovery:**
1. Root cause: identify why throughput dropped (code change, database slow, external API timeout)
2. Fix: revert code, optimize query, increase external API timeout
3. Backfill: consumer will catch up once lag is flowing
4. Prevent: add pre-deploy load test, alert on consumer latency p99
---
### Edge Case 4: Elasticsearch Reindex Timeout
**Scenario:** Products index has 500M documents. Reindex operation (blue-green migrate) starts. After 2h, only 60% reindexed. 4h timeout approaches. Heap memory at 85%. JVM GC pauses hit 2s every 30s.
**Detection:**
- Metric: Reindex progress < 50% after 2h (expected: 100M docs/h)
- Metric: ES heap memory > 80%
- Metric: GC pause time > 1s (indicates memory pressure)
- Alert: "ES reindex lag > 2h for 500M docs" (page)
**Immediate Action:**
1. Pause reindex: stop reindex task, keep intermediate index
2. Increase heap: if allowed, bump JVM heap from 16GB to 24GB
3. Tune reindex: reduce batch size from 5000 to 2000, add throttle (slice_max_concurrent=1)
4. Extend timeout: modify reindex request timeout from 4h to 8h
**Recovery:**
1. Resume reindex with tuned parameters
2. Monitor progress: should hit 20-30M docs/h now
3. Root cause: data model changed (larger docs), need to adjust expectations
4. Prevent: pre-test reindex on production-scale data, measure throughput
---
### Edge Case 5: MySQL Replication Lag During Write Spike
**Scenario:** E-commerce site flash sale. 50k writes/s hit primary. Secondary replica can only handle 40k/s. Replication lag grows 10k/s. After 2m, lag = 1.2M events. Readers on secondary see stale inventory (false "out of stock").
**Detection:**
- Metric: Replication lag seconds > 5 → warn, > 30 → page
- Metric: Write latency on primary p99 > baseline by 100%
- Metric: Apply time on secondary > 2s/s
- Alert: "DB replication lag > 30s" (page)
**Immediate Action:**
1. Route reads to primary: disable read-only replicas, failover read traffic
2. Scale secondary: increase replica instance size (CPU, disk I/O)
3. Degrade inventory checks: cache inventory, disable real-time updates
4. Throttle writes: add client-side backpressure (rate limit to 40k/s)
**Recovery:**
1. Monitor replica: apply lag should decrease as writes normalize
2. Wait for convergence: once lag < 5s, safe to route reads back to secondary
3. Root cause: replica hardware undersized for write volume
4. Prevent: capacity plan for 2x peak load on replicas
---
### Edge Case 6: Partition Key Skew in Kafka
**Scenario:** Events published to `user-actions` topic partitioned by `user_id`. One influencer user has 1M followers. All their events go to partition 0. Partition 0 gets 50k msg/s, partition 9 gets 100 msg/s. Consumer group has 10 instances but partition 0 falls behind, lag = 5m while partition 9 is current.
**Detection:**
- Metric: Partition lag varies by > 10x (partition 0 lag=300s, partition 9 lag=10s)
- Metric: Consumer instance for partition 0 has latency p99 > 5s
- Alert: "Kafka partition skew > 10x" (warn)
**Immediate Action:**
1. Add sub-partitioning: change partition key from `user_id` to hash(`user_id` + `timestamp/60`) to distribute hot user
2. Add dedicated consumer: assign 3 instances to partition 0, 1 to others
3. Degrade features: if follower events non-critical, sample (process 1 in 10)
**Recovery:**
1. Rebalance: after code deploy, rebalance consumer group
2. Monitor: verify partition lag converges
3. Prevent: monitor partition size distribution, alert if any partition > 2x average
---
### Edge Case 7: Redis Out-of-Memory (OOM) Eviction
**Scenario:** Redis max memory 64GB. Cache fills to 95%. New cache writes trigger LRU eviction. Hit rate drops from 90% to 70%. Application latency increases 3x.
**Detection:**
- Metric: Redis memory > 95% of max
- Metric: Evictions per minute > 1000
- Metric: Cache hit rate drops > 20% within 5m
- Alert: "Redis memory > 90%" (warn), "> 95%" (page)
**Immediate Action:**
1. Reduce TTLs: from 1h to 30m cache expiration
2. Selective purge: delete low-value caches (product recommendations TTL → 5m)
3. Scale Redis: add new node, migrate shards
4. Feature degrade: disable optional caches (analytics)
**Recovery:**
1. Monitor: memory should drop to 70-80% after TTL reduction
2. Root cause: data size grew 50%, cache strategy didn't scale
3. Prevent: implement cache eviction budget (never exceed 80% memory)
---
### Edge Case 8: Migration Blocking Issue
**Scenario:** Add `status` column to `orders` table. Migration locks table for 2h on 5B row table. All read/write traffic blocks. User-facing latency increases to 30s. Site functionally down.
**Detection:**
- Metric: Table lock detected (via `SHOW OPEN TABLES WHERE In_use > 0`)
- Metric: Query queue > 100 (queries waiting for lock)
- Metric: User latency p99 > 5s (vs 100ms baseline)
- Alert: "Query queue > 50" (page)
**Immediate Action:**
1. Kill migration: cancel ALTER TABLE
2. Rollback: table lock released, traffic normalizes
3. Route traffic: temporarily route to read replica for reads
**Recovery:**
1. Use online migration tool: MySQL 5.7+ supports instant ADD COLUMN for some cases
2. Use gh-ost: ghost tool for online migrations (no table lock)
3. Dark rollout: deploy code to read new column, backfill async, cutover later
4. Schedule: run migration during maintenance window (low traffic)
---
### Edge Case 9: Disk Space Running Out
**Scenario:** MySQL data directory 2TB. Database grows 100GB/month. After 20m, 2.05TB used. Disk full. Writes start failing. Replication breaks.
**Detection:**
- Metric: Disk usage > 90% → warn, > 98% → page
- Metric: Write failures (error "disk full")
- Metric: Replication lag > 60s (replication fails on secondary)
- Alert: "Disk usage > 90%" (warn)
**Immediate Action:**
1. Emergency cleanup: delete old transaction logs, temporary tables
2. Expand volume: increase EBS/disk size (if on cloud, resize online)
3. Pause writes: if disk still full, degrade to read-only
4. Page: escalate immediately
**Recovery:**
1. Add capacity: scale to 5TB (2.5x current)
2. Prevent: set up alerting at 80%, weekly monitoring
3. Root cause: data retention policy too long, need to archive old data
---
## Common Pitfalls in Infrastructure Reasoning
### Pitfall 1: "Assume Cache Hit Rate Will Be 95%+"
**Reality:** Typical cache hit rates for diverse workloads are 70-85%. New features often start at 40-50%.
**Why it matters:** If you design assuming 95% hit rate, your database will be undersized for the real 75% rate. You'll hit connection exhaustion or slow query problems.
**Right approach:**
- Design for realistic hit rates: 70-80% for user-specific data, 85-90% for hot products
- Monitor actual hit rate in staging: test with real traffic patterns
- Set alert thresholds at 75% (page if below), to catch misses before latency degrades
- Over-provision database: assume worst-case 60% cache hit rate
---
### Pitfall 2: "Connection Pool Large Enough to Handle 10x Traffic"
**Reality:** Connection pools have hard limits (MySQL default max_connections = 151). Oversizing causes memory bloat, GC pauses, and eventual exhaustion under spike.
**Why it matters:** A pool sized for 10x will use 10x memory, cause context switching, and still exhaust under 50x spike. Better to fail fast with a right-sized pool than slowly degrade with an oversized one.
**Right approach:**
- Size pool for 2x expected peak: if 100 QPS, use pool of 20-30 (200ms avg latency per connection)
- Use queue with timeout: new requests wait max 5s, then fail gracefully
- Alert when utilization > 80%: gives 5m to scale before exhaustion
- Add circuit breaker: if connection wait > 5s, degrade features rather than queue infinitely
---
### Pitfall 3: "Ignoring Replication Lag for Eventual Consistency"
**Reality:** Even with "eventual consistency", users see inconsistencies. User updates password, immediately logs in, gets 404 on secondary replica (password not synced yet).
**Why it matters:** Reads on stale replicas cause application errors, user confusion, data loss (if they retry and create duplicates).
**Right approach:**
- After writes: route reads to primary for 10s (window where replication completes)
- For user-specific data: always read from primary if write < 1m ago
- Measure replication lag: alert if > 5s, page if > 30s
- Accept lower QPS: never push replica to limits, keep headroom for lag
---
### Pitfall 4: "Retry Logic Without Idempotency"
**Reality:** Network request fails. Client retries. Server processes the same request twice. Duplicate charges, duplicate orders, data corruption.
**Why it matters:** Retries are essential for reliability, but they create duplicates without idempotency keys. The system appears to work in happy path (95% of traffic), fails in retry path (5% of traffic). Hard to debug.
**Right approach:**
- Every API request has idempotency_key (UUID): request + key = atomic
- Deduplication window ≥ 24h: server stores key → result, returns cached result on retry
- Kafka events have idempotency_key: dedup consumer tracks key in Redis/DB
- Document contract: "Retries are safe, guaranteed exactly-once"
---
### Pitfall 5: "Single Availability Zone is Fine (Save Cost)"
**Reality:** Zone goes down (network issue, hardware failure, AWS maintenance). All databases, caches, and services in that zone are unreachable.
**Why it matters:** Single-zone architecture causes complete downtime in event of zone failure. Recovery from backup takes hours. Users lose data.
**Right approach:**
- Distribute across ≥ 2 zones: primary in zone A, replica in zone B
- Replication must be cross-zone: allow one zone to fail completely
- Test failover quarterly: simulate zone failure, ensure automatic failover works
- Accept cost: HA costs 50% more (2 zones, 2 databases), but prevents catastrophic failure
---
### Pitfall 6: "Elasticsearch Schema Design is Flexible (Ship Fast)"
**Reality:** After 3 months, realize you need a field you didn't index. Need to reindex 2B documents (takes 8h, site slow). Or document structure changes, breaks existing queries.
**Why it matters:** ES schema changes are painful and slow. Early design mistakes compound.
**Right approach:**
- Design schema upfront: list all fields that might be searched/filtered/sorted
- Index everything: disk space is cheap, indexing time is expensive
- Use versioning: if schema must change, create new index, switch alias gradually
- Review with backend: coordinate schema with application query patterns
---
### Pitfall 7: "No Monitoring Until Post-Launch"
**Reality:** Launch the feature. Users report slowness. You don't have latency metrics. You can't find the bottleneck (is it DB? Cache? Network?). Site is down, you're debugging in dark.
**Why it matters:** Monitoring during normal operation is 100x easier than during crisis. You need baselines to detect anomalies.
**Right approach:**
- Deploy monitoring code with feature code: instrument every critical path
- Set SLOs before launch: p99 latency < 200ms, cache hit > 80%, replication lag < 5s
- Alert on deviation from baseline: not just absolute thresholds
- Weekly metrics review: spot trends before they become incidents
---
## Scaling Decision Tree
**Question 1: What's the bottleneck?**
| Bottleneck | Signal | Solution |
|---|---|---|
| CPU | DB CPU > 80% | Optimize queries (add index, rewrite), vertical scale (bigger instance) |
| Memory | Cache evictions increasing, hit rate dropping | Scale cache (more nodes), reduce TTL, optimize cache key strategy |
| I/O (disk) | DB disk util > 90%, slow queries latency > 5s | Add replicas (read scaling), partition data (write scaling), vertical scale |
| Network | Bandwidth > 80% capacity | Compress data (cache compression), reduce batch size, add more nodes |
| Connections | Pool utilization > 95% | Increase pool size, add connection pooler (PgBouncer), optimize app connection usage |
**Question 2: Vertical vs Horizontal Scaling?**
| Axis | Vertical (Bigger) | Horizontal (More Nodes) |
|---|---|---|
| Database | Works up to 2-4TB data. Beyond needs sharding. | Not possible for single-node (no horizontal MySQL). Use replicas for read scaling only. |
| Cache | Works up to 1TB per instance. Beyond ~256GB, memory cost high. | Distribute cache across 10+ nodes (Redis Cluster, Memcached). |
| Kafka | Single broker: up to 50k msg/s. Beyond needs more brokers. | Add brokers (scales linearly), partition data (parallelism). |
| Elasticsearch | Single shard: 50M-200M docs, up to 200GB. | Add shards (parallelism) or nodes (replication). |
**Question 3: Partitioning Strategy?**
| Strategy | When to Use | Trade-offs |
|---|---|---|
| **By user_id** (most common) | User-centric data (profiles, preferences, orders). Ensures all user data on same shard. | Load skew if some users >> others. Hot users bottleneck single shard. |
| **By time** (time-series) | Logs, events, metrics. New data in new partition. | Hard to query across time ranges. Need to union results from multiple partitions. |
| **By hash** (consistent hash) | Distribute evenly regardless of data. | All user data scattered across shards. Need to query all shards for user. |
| **By range** (range-based) | Customer ID ranges (1-1M, 1M-2M, ...). | Requires manual rebalancing as ranges grow/shrink. |
| **By geography** | Multi-region deployment. | Cross-region queries slow. Data residency compliance. |
**Question 4: Connection Pool Sizing Formula**
Pool Size = (Num Connections Needed) × (Avg Query Time ms) / 1000 ms
Example:
Conservative (2x buffer):
**Question 5: Cache TTL Tuning Strategy**
| Data Freshness Need | Suggested TTL | Rationale |
|---|---|---|
| Real-time (< 1s stale) | 10-30s | Frequent misses. Expensive. Use for critical data. |
| Near real-time (< 1m stale) | 1-5m | Balance. Most user-facing data. |
| Eventually consistent (< 1h stale) | 10m-1h | Low freshness need. Long TTL saves DB. |
| Static (doesn't change) | 24h+ or never expire | Product info, reference data. Invalidate on update only. |
| Hot data (read 1000x/s) | 5-10m | Even 1m misses cause DB spike. Shorter TTL. |
| Cold data (read 1x/min) | 30m-1h | Longer TTL saves space. Misses rare. |
**Trade-off: Shorter TTL = more cache misses = more DB load. Longer TTL = stale data = poor UX.**
---
## Failure Scenario Handbook
### Database Failures
#### Failure: Connection Exhaustion
**Metrics to Watch:**
- `db.active_connections` (current connections)
- `db.connection_pool.utilization_percent` (pool fullness)
- `db.connection_pool.queued_requests` (requests waiting)
**Immediate Action (< 5 min):**
1. Identify slow queries: `SHOW FULL PROCESSLIST WHERE time > 30`
2. Kill suspects: `KILL QUERY process_id` (stops query, keeps connection)
3. Set max_connections lower: `SET GLOBAL max_connections = 80` (stops new connections, prevents crash)
4. Degrade features: stop non-critical queries (search, analytics)
**Recovery (5-30 min):**
1. Root cause: add logging to identify slow queries
2. Add index or optimize query: re-run, verify latency < 100ms
3. Increase pool size: if legitimate load, adjust pool from 20 → 30
4. Test: load test to verify no regression
**Prevention:**
- Alert at 80% utilization (pool of 20: alert at 16 connections)
- Add query timeout: `SET SESSION max_execution_time = 5000` (5s max)
- Monitor slow query log: queries > 1s logged, reviewed daily
---
#### Failure: Replication Lag
**Metrics to Watch:**
- `db.replication.lag_seconds` (replication lag in seconds)
- `db.replication.lag_seconds > 5` (warn), `> 30` (page)
- `db.replication.apply_lag_seconds` (time to apply events)
**Immediate Action (< 5 min):**
1. Check secondary status: `SHOW SLAVE STATUS\G` → look for `Seconds_Behind_Master`
2. Check for slow query on secondary: `SHOW FULL PROCESSLIST` → identify blocking apply
3. Route reads to primary: disable secondary in connection pool
4. Page: escalate to database team
**Recovery (5-30 min):**
1. Kill slow query on secondary (if safe): `KILL QUERY process_id`
2. Increase replica resources: bigger CPU/memory for binary log processing
3. Wait for lag to converge: monitor until < 5s
4. Root cause: was secondary undersized? Was there a data sync issue?
**Prevention:**
- Capacity plan: replica CPU = primary CPU (can't be slower at same throughput)
- Monitor replica lag continuously: alert at > 5s
- Test failover: quarterly failover to ensure replicas can take over
---
#### Failure: Slow Query Spike
**Metrics to Watch:**
- `db.query_latency_ms.p99` (99th percentile query time)
- `db.queries_per_second` (throughput)
- `db.slow_queries_count` (queries > 1s)
**Immediate Action (< 5 min):**
1. Identify slow queries: tail slow query log or query `performance_schema.events_statements_summary`
2. Check EXPLAIN: `EXPLAIN SELECT ...` → look for full table scan (rows >> expected)
3. Add missing index: identify columns in WHERE/JOIN that lack indexes
4. Rewrite query: if index doesn't help, rewrite (push filter earlier, add covering index)
**Recovery (5-30 min):**
1. Deploy index change: `CREATE INDEX idx_name ON table(column)` (online in MySQL 5.7+)
2. Verify improvement: re-run slow query, latency should drop
3. Test on staging: ensure query plan is stable
4. Monitor: ensure no regression in other queries
**Prevention:**
- Review query patterns before code ship: backend reasoning discusses query plan
- Monitor index creation success: alert if `Creating index ... ` runs > 10m
- Weekly slow query review: top 10 slow queries analyzed
---
### Cache Failures
#### Failure: Cache Miss Spike
**Metrics to Watch:**
- `cache.hit_rate_percent` (should be 80%+, alert if < 70%)
- `cache.miss_count_per_minute` (sudden increase = spike)
- `db.queries_per_second` (should drop when cache hits increase)
**Immediate Action (< 5 min):**
1. Check cache connection: `redis-cli PING` → should respond PONG
2. Check cache memory: `redis-cli INFO memory` → look for `used_memory_human`
3. Check hit rate trend: spike today vs yesterday?
4. If memory full: evictions_per_minute > 1000 → scale cache
**Recovery (5-30 min):**
1. If cache process dead: restart Redis instance
2. If memory full: add new cache node, migrate data
3. If query pattern changed: rebuild cache keys (full database scan)
4. Warm cache: pre-fill hot keys from database before traffic spike
**Prevention:**
- Monitor cache memory: alert at 80%, page at 95%
- Alert on hit rate drop: page if < 75% for 5+ minutes
- Auto-scaling: trigger cache scale-out when memory > 80%
---
#### Failure: Cache Stampede
**Metrics to Watch:**
- `cache.hit_rate_percent` drops 20%+ in < 1m
- `db.queries_per_second` spikes 5x+ suddenly
- `db.cpu_percent` spikes from 30% → 80%
**Immediate Action (< 1 min):**
1. Enable stale-while-revalidate: serve expired keys for 5s while refreshing async
2. Increase cache TTL: from 1h → 2h (spreads expirations)
3. Add jitter: TTL = base_ttl + random(0, base_ttl * 0.1) (±10% randomness)
4. Implement distributed lock: use Redis SET key EX 1 NX to ensure only 1 refresh
**Recovery (1-10 min):**
1. Verify cache hit rate returns to 80%+
2. Root cause: was there a cache flush? A code deploy that cleared keys?
3. Prevent: implement cache warming (refresh hot keys every 50m before expiry)
**Prevention:**
- Use consistent TTLs: avoid multiple keys expiring at same time
- Monitor hit rate volatility: alert if variance > 20%
- Load test: simulate cache eviction under load
---
### Event Bus Failures
#### Failure: Consumer Lag Spike
**Metrics to Watch:**
- `kafka.consumer.lag_offset` (how many messages behind)
- `kafka.consumer.lag_seconds` = lag_offset / producer_rate (seconds to catch up)
- `kafka.consumer.processing_latency_ms.p99` (time per message)
**Immediate Action (< 5 min):**
1. Check consumer group status: `kafka-consumer-groups --group group_name --describe` → see lag per partition
2. Check for rebalancing: `consumer group rebalancing` in logs (stops processing during rebalance)
3. Check processing latency: if p99 > 1s, find blocking operation (DB query, external API)
4. Scale consumers: if lag growing, add consumer instances
**Recovery (5-30 min):**
1. If rebalancing: check for crashes/network issues, restart consumers
2. If processing slow: optimize code (reduce database queries, cache external API)
3. If producer rate spiked: add more consumer instances to parallelize
4. Monitor lag: should decrease once processing normalizes
**Prevention:**
- Alert on consumer lag: warn at > 1m, page at > 5m
- Monitor processing latency: alert if p99 > 1s (baseline should be < 100ms)
- Capacity plan: ensure consumer throughput ≥ producer throughput * 1.2x (20% headroom)
- Weekly lag review: check max lag per partition, identify skew
---
#### Failure: Message Loss
**Metrics to Watch:**
- `kafka.producer.failures_count` (messages that failed to send)
- `kafka.broker.under_replicated_partitions` (replicas not in-sync)
- `kafka.consumer.committed_offset` vs `broker.latest_offset` (gaps indicate loss)
**Immediate Action (< 5 min):**
1. Check broker status: are all brokers healthy? (broker logs for errors)
2. Check replication: `kafka-topics --describe --topic topic_name` → in-sync replicas < expected = loss risk
3. Check producer: is producer sending with acks=all? (required for durability)
4. Page: escalate immediately
**Recovery (1-60 min):**
1. If broker down: restart or failover to replica
2. If replication broken: repair replica (may require re-sync)
3. If producer bug: fix code to use acks=all (default is acks=1, can lose messages)
4. Assess damage: how many messages lost? Can we replay from backup?
**Prevention:**
- Configure durability: acks=all (wait for all replicas), min.insync.replicas=2
- Monitor under-replicated partitions: alert if any partition < 2 replicas
- Test failover: kill broker, ensure replicas take over without message loss
- Backup events: store events in S3 for recovery
---
### Search Failures
#### Failure: Indexing Lag
**Metrics to Watch:**
- `elasticsearch.indexing_lag_ms` (time from event to indexed)
- Alert: lag > 30s (warn), > 2m (page)
- `elasticsearch.documents_indexed_per_second` (should match producer rate)
**Immediate Action (< 5 min):**
1. Check consumer status: is ES consumer running? (check process, logs)
2. Check indexing latency: `_stats` endpoint → look for indexing rate
3. Check index size: if huge, indexing will be slow
4. Check ES health: `_cluster/health` → look for unassigned shards
5. Degrade feature: if lag > 5m, disable search features temporarily
**Recovery (5-30 min):**
1. If consumer crashed: restart consumer, lag will catch up
2. If indexing slow: check heap memory (> 90% causes GC), reduce batch size
3. If shard failed: ES will re-allocate, wait for recovery
4. Monitor: verify lag drops back to < 30s
**Prevention:**
- Alert on indexing lag: page if > 2m
- Monitor ES heap: alert if > 85%
- Capacity plan: ES should handle 2x expected indexing rate
- Test reindex: pre-test large reindex operations for time estimate
---
#### Failure: Shard Allocation Failure
**Metrics to Watch:**
- `elasticsearch.unassigned_shards_count` (shards without home)
- `elasticsearch.active_shards_percent_as_number` (should be 100%)
- Alert: unassigned_shards > 0 (warn immediately)
**Immediate Action (< 5 min):**
1. Check cluster health: `GET _cluster/health` → see which index has unassigned
2. Check node status: are all nodes up? (nodes join/leave cluster)
3. Try to allocate: `POST _cluster/reroute --retry-failed` (retry failed allocations)
4. If disk full: free space on nodes (delete old indices, increase capacity)
5. Page: escalate to search team
**Recovery (5-60 min):**
1. Root cause: node crash, disk full, network partition?
2. Fix: bring node back online, free disk space, resolve network issue
3. Rebalance: ES will automatically re-allocate shards
4. Verify: wait for all shards to be assigned
**Prevention:**
- Monitor shard allocation: alert if unassigned_shards > 0
- Capacity plan: keep disk usage < 85%, headroom for shard allocation
- Multi-zone deployment: distribute shards across zones
---
## Consistency Model Decision Tree
**Question 1: What's the user expectation?**
| User Expectation | Consistency Model | Example |
|---|---|---|
| "Immediate" (write then read same value) | Strong | Bank transfer, account balance, inventory |
| "Soon" (read within seconds of write) | Causal | User profile update, preference change |
| "Eventually" (read may be stale for minutes) | Eventual | Product recommendations, view count |
| "Best effort" (read may be old or missing) | Weak | Analytics, audit logs |
**Question 2: How to implement each model?**
### Strong Consistency
**Definition:** Read always sees the latest write, even by other users.
**How:**
- Always read from primary database (never replicas)
- Serialize writes (transactions)
- Cache TTL = 0 (no cache, or cache only after read-your-write)
**Cost:** High latency (no replicas for read scaling), high database load
**Example:**
POST /account/transfer (write) → INSERT transaction in primary → Commit (durable)
GET /account/balance (read) → Query primary (always latest) → Return balance
---
### Causal Consistency
**Definition:** If B depends on A, reads must see A before B. But independent events can be stale.
**How:**
- Write to primary, wait for replication to ≥1 replica (typically < 100ms)
- Read from primary for 1m after write, then replicas
- Use vector clocks or version numbers
**Cost:** Moderate latency, moderate database load
**Example:**
POST /profile/update (user updates name) → Write to primary → Wait for replication (replica ack) → Return to client (took 50ms)
GET /profile (user immediately reads own profile) → Read from primary for next 60s (reads own write)
GET /profile/:user_id (another user reads the profile) → Can read from replica (after 60s window)
---
### Eventual Consistency
**Definition:** Reads may be stale. All events eventually propagate.
**How:**
- Write to primary, return immediately (async replication)
- Replication lag typically < 5s
- Cache aggressively (long TTL)
- Replicas lag by 5-30 seconds
**Cost:** Low latency, low database load, stale reads
**Example:**
POST /product/:id/view (user views product) → Increment counter in primary (async) → Return immediately
GET /product/:id (user reads product, may see old view count) → Read from replica (fast, but view count lag 5-30s)
---
### Weak Consistency
**Definition:** Reads may be arbitrarily old. Fire-and-forget.
**How:**
- Write to cache only (no database)
- No replication
- Data loss acceptable
**Cost:** Minimal latency, high data loss risk
**Example:**
POST /analytics/event (log user click) → Write to Redis only (async flush to DB) → Return immediately → Data loss OK (analytics, not critical)
---
**Question 3: Cache TTL by Consistency Model**
| Model | Cache TTL | Rationale |
|---|---|---|
| Strong | 0 or invalidate on write | No stale cache. Defeats purpose of caching. Rarely used. |
| Causal | 1m for read-your-write, then 10m | Cache writes for 1m (user sees own write), then longer for others |
| Eventual | 5-30m | Stale cache acceptable, long TTL saves DB |
| Weak | 1h+ or no expiry | Data loss OK, maximize cache benefit |
---
**Question 4: Choosing Consistency for Common Features**
| Feature | Consistency | Why |
|---|---|---|
| Account balance | Strong | User expects immediate accuracy, errors cause complaints |
| Inventory count | Strong | Prevent overselling, customer trust |
| User profile | Causal | User expects own edits immediate, others see within 1m |
| Product recommendations | Eventual | Stale OK, user doesn't expect perfect freshness |
| Order status | Eventual | May lag 30s, user refreshes manually |
| Analytics | Weak | Complete accuracy not required, speed matters |
| Search results | Eventual | Indexing lag OK (1-2m), user refreshes if needed |
---
## Deployment & Migration Patterns
### Pattern 1: Zero-Downtime Database Migrations
**Scenario:** Add new column to `users` table. Migrate data. Keep service running.
**Timeline:**
Phase 1: Prepare (pre-deployment, 30 min)
Phase 2: Code Deploy (0-5 min downtime if needed)
Phase 3: Backfill (1-2 hours, running in background)
Phase 4: Cutover (5 min)
Phase 5: Cleanup (next release, 5 min)
**Safety:**
- Rollback safe: old column exists, code checks both, prefers new
- Replication safe: backfill is slow to not overload secondary
- Feature flag safe: if new column broken, flip flag OFF, revert reads to old
---
### Pattern 2: Blue-Green Elasticsearch Reindex
**Scenario:** Elasticsearch index schema changes. Reindex 2B documents without downtime.
**Timeline:**
Phase 1: Create green index
Phase 2: Verify green
Phase 3: Switch alias
Phase 4: Cleanup
**Safety:**
- No downtime: alias switch is atomic
- Rollback easy: alias points back to blue if green broken
- Parallel: reindexing doesn't affect blue (normal read/write traffic continues)
---
### Pattern 3: Feature Flag Driven Rollout
**Scenario:** Add new caching layer. Gradually increase traffic without full deployment.
**Code:**
```python
def get_user_profile(user_id):
if feature_flag_enabled('use_cache_v2'):
try:
return redis_v2.get(f'user:{user_id}')
except Exception:
# fall back to database
pass
return database.query(f'SELECT * FROM users WHERE id={user_id}')
Rollout:
Canary (5% of traffic):
- Feature flag: use_cache_v2 = 5%
- Monitor: cache hit rate, latency, errors
- Wait 30m: ensure stable
Ramp (25% of traffic):
- Feature flag: use_cache_v2 = 25%
- Monitor: cache memory, evictions
- Wait 1h: ensure stable
Production (100%):
- Feature flag: use_cache_v2 = 100%
- All traffic uses v2
- Continue monitoring for 24h
Rollback:
Scenario: Deploy new MySQL connection pool logic. Test on 10% of replicas first.
Strategy:
Canary (1 replica, 10% traffic):
- Deploy new code to 1 replica instance
- Route 10% of read traffic to this replica
- Monitor latency, errors, CPU
- Threshold: if p99 latency > baseline + 20%, auto-rollback canary
Ramp (3 replicas, 30% traffic):
- If canary stable for 30m: deploy to 3 more replicas
- Route 30% of traffic to these 4
Production (all replicas, 100%):
- Deploy to all replicas
- Monitor for 24h for regression
Metrics to Monitor:
Scenario: Consumer code has bug (doesn't handle certain event types). Fix code, deploy with new consumer group.
Timeline:
Phase 1: Deploy new consumer group
- New code in parallel branch: consumer_group_v2
- Both v1 (old, in prod) and v2 (new, in staging) read same topic
- v2 doesn't commit offsets yet (run in shadow mode)
Phase 2: Validate new consumer
- v2 runs for 24h without committing
- Compare v2 output with v1: ensure same messages processed
- If v2 correct: proceed
Phase 3: Switch
- v1: stop consuming (stop deployment, don't crash)
- v2: start consuming from v1's last offset (resume processing)
- If v2 breaks: kill v2, restart v1 (only lost 1-2m of messages)
Phase 4: Cleanup
- After 7 days: delete v1 consumer group (stop alerting)
Safety:
Scenario: Primary database failing. Failover to replica. Repair primary. Switchback.
Emergency Failover (< 5 min):
Step 1: Detect failure
- Alert: primary not responding
- Confirm: can't connect from multiple regions
Step 2: Promote replica
- Replica becomes new primary
- DNS: primary → replica (updates in 5-30s)
- App: automatically reconnects (connection pooler does retry)
Step 3: Disable old primary (prevent split-brain)
- Firewall: block old primary from cluster
- Or: stop MySQL process
Step 4: Monitor new primary
- Verify writes working: insert test record
- Verify replicas replicating from new primary
- Alert: page oncall team
Repair & Switchback (1-4 hours):
Step 1: Repair old primary
- Hardware: replace disk, reboot
- MySQL: `RESET MASTER` (clear binary logs), start fresh
Step 2: Resync old primary as replica
- Configure old primary to replicate from new primary
- Monitor: replication lag until caught up
Step 3: Switchback (optional)
- If old primary healthy: switchback (requires downtime)
- Or: keep new primary in place, old as replica
Metrics to Monitor:
scan-codebase:
structure.txt, code-style.md, and SCAN.json for the target repocode-style.md is the authoritative source for service names, env var naming patterns, and existing Dockerfile conventionsreasoning-as-backend:
reasoning-as-web-frontend:
reasoning-as-app-frontend:
brain-read:
brain-read product={product_id} → returns prior infra decisionsbrain-write:
brain-write key=infra.database.schema value={decision} → locks decisionWhen negotiating with other surfaces:
Causal reasoning: "If we use strong consistency, every read hits primary → we lose read scaling. With eventual consistency + 30s cache, we can read from replica → 5x faster, but data may lag 30s. Cost-benefit: users see slightly old data for 5x speedup."
Constraint acknowledgment: "Web frontend needs p99 latency < 100ms. With database alone (50ms avg, 200ms p99), we need caching. Cache TTL = 5m gives 90% hit rate, keeps latency < 30ms."
Risk clarity: "Single-zone deployment saves 40% cost but risks complete downtime if zone fails. Multi-zone costs 40% more but protects against zone failure. Trade-off: cost vs availability."
Before launching:
When reviewing other surfaces' proposals:
To Backend:
To Web/App:
To Self:
Before submitting infra reasoning to council:
WHEN: You need to detect performance regressions before merge — TTFB, response time, bundle size. Run as part of forge-eval-gate or standalone before raising a PR.
WHEN: A decision is being superseded, deprecated, or has aged out. Archive it without deletion — marks as warm→cold→archived with full audit trail.
WHEN: You are writing a new decision, superseding an old one, or querying relationships across decisions. Create semantic edges between decisions and link concepts across products/projects/time.
WHEN: You are about to make a decision and need to check if prior art or past learnings exist. Recall decisions, patterns, and gotchas from the brain before proceeding.
WHEN: You need to trace the full provenance of a specific decision — who made it, when, why, and what alternatives were considered. Shows why, when, by whom, evidence, alternatives, outcome.
WHEN: You need to record a decision, lock a spec, log an eval run, or document learnings in the brain.