> For AI agents: the complete documentation index is available at /llms.txt, the full documentation bundle is available at /llms-full.txt.

# 数据对账系统 Runbook

> **last-verified: 2026-07-14**（对照 infra PR #1459;发现和代码不符以代码为准）
>
> ⚠️ **两处以代码为准的关键更正**（见对应 section 内联说明）：
>
> 1. **Metric 已改名**（#775）：`ReconciliationCase{1..4,D}` → `CallsMissingInDB` / `CallsRecordingNotDownloaded` / `CallsTranscriptionNotStarted` / `CallsTranscriptionNotCompleted` / `CallsOrphaned`。下文旧名保留作历史对照，**查 CloudWatch 用新名**。
> 2. **对账不用 AWS Transcribe**：所有修复都是「重置 flag + 往 transcribe-queue 重投 synthetic webhook」，走正常 Deepgram 路径（`reconciliation-worker` 无任何 `transcribe:*` SDK 调用）。旧文档里 `aws transcribe get/delete-transcription-job` 命令已废弃。
> 3. **Schedule 当前不是自动运行**：live CDK 在所有 environments 都配置 `state: 'DISABLED'`。本页下方的 “Every 3 hours / auto-enabled” 是历史设计说明，不得当作当前 safety net。另见 [RingCentral Call Log、录音就绪与 Rate Limit 调查](/system-design/ringcentral-call-log-readiness-and-rate-limits.md)。

## Quick Links

- **Troubleshooting Guide**: [reconciliation/troubleshooting.md](reconciliation/troubleshooting.md)
- **Dashboard**: [CloudWatch Dashboard](https://console.aws.amazon.com/cloudwatch/home#dashboards:name=call-analytics-\{env})
- **Monitoring Guide**: [../01-MONITORING/README.md](../01-MONITORING/README.md)
- **Alerts Guide**: [../02-ALERTING/README.md](../02-ALERTING/README.md)

***

## System Overview

### Purpose

The reconciliation system detects and fixes data inconsistencies between RingCentral (source of truth) and DynamoDB (local state) by periodically scanning for missing or incomplete call records.

**What It Does**:

- Identifies calls that exist in RingCentral but are missing from DynamoDB
- Detects calls stuck at various processing stages (recording download, transcription)
- Fixes issues automatically by replaying the processing pipeline
- Prevents data loss from webhook delivery failures or transient errors

**When It Runs**:

- **Automated**: Every 3 hours via EventBridge schedule
- **Manual**: On-demand via Lambda test event or CLI
- **Emergency**: After major incidents or data integrity concerns

### Architecture Evolution

#### V2.5.0 (November 2025): SQS Fan-Out Architecture

**Before (V2.4)**: Orchestrator processed all actions in single invocation

- **Problem**: 37+ RingCentral API calls per client in single Lambda
- **Impact**: Rate limit storms, 429 errors, failed reconciliations

**After (V2.5.0)**: SQS fan-out with wide-spread retry

```
EventBridge (Every 3h)
       ↓
Orchestrator Lambda
  ├─ Scan DynamoDB for active clients
  ├─ Fetch RingCentral calls (1 API call per client)
  └─ Generate SQS messages (1 message per action)
       ↓
SQS Queue (reconciliation-worker-queue)
  ├─ Random delays: 0-10 min (State 1)
  └─ Random delays: 5-15 min (State 2-4)
       ↓
Worker Lambda (Concurrency: 2)
  ├─ Process 1 action at a time
  └─ Send synthetic webhooks to transcribe-queue
```

**Performance Impact**:

- **97% API Call Reduction**: 37+ calls → 1 call per client
- **Rate Limit Prevention**: Wide-spread retry (2-15 min random delays)
- **Independent Retry**: Each action can retry independently via SQS
- **Concurrency Control**: Reserved concurrency = 2 prevents API storms

#### V2.5.3 (November 2025): Auto-Enable in Dev/Test

**Schedule State**:

- **Production**: Disabled by default (manual enable required for safety)
- **Dev/Test**: Auto-enabled (no manual CLI step needed)
- **Benefit**: Eliminates "enable schedule after deploy" operational step

**How to Override**:

```bash
# Disable in dev/test (emergency stop)
aws scheduler update-schedule \
  --name call-analytics-pre-reconciliation \
  --state DISABLED

# Enable in prod (after validation)
aws scheduler update-schedule \
  --name call-analytics-prod-reconciliation \
  --state ENABLED
```

### Reconciliation States (V2.4.1 Migration)

The system uses a **4-state model** to classify call record issues:

| State       | Condition                                                    | Action                                | Metric Name           | Dashboard Widget |
| ----------- | ------------------------------------------------------------ | ------------------------------------- | --------------------- | ---------------- |
| **State 1** | Call exists in RingCentral but missing in DynamoDB           | Create DB record + download recording | `ReconciliationCase1` | Row 9 Widget 1   |
| **State 2** | Call in DB but recording not downloaded (>15 min)            | Download recording from RingCentral   | `ReconciliationCase2` | Row 9 Widget 2   |
| **State 3** | Recording downloaded but transcription not started (>30 min) | Start AWS Transcribe job              | `ReconciliationCase3` | Row 9 Widget 3   |
| **State 4** | Transcription started but not completed (>24 hours)          | Check status, retry if failed         | `ReconciliationCase4` | Row 9 Widget 4   |
| **Case D**  | Record in DynamoDB but deleted from RingCentral (>90 days)   | Mark as orphaned (no action)          | `ReconciliationCaseD` | Row 9 Widget 5   |

**Legacy Model (Deprecated in V2.4.1)**:

- **Case A** → State 1 (missing in DB)
- **Case B + C** → State 4 (transcription stuck)
- **Case D** → Unchanged (orphaned records)

**Migration Timeline**:

- **Code Updated**: V2.4.1 (November 28, 2025)
- **Metrics Changed**: `CallAnalytics` → `CallAnalytics/RingCentral` namespace
- **Dashboard Updated**: Row 9 added for per-client case breakdown

### Key Characteristics

#### Orchestrator Lambda

- **Runtime**: Node.js 20
- **Memory**: 256 MB
- **Timeout**: 1 minute
- **Concurrency**: Unreserved (auto-scales with schedule)
- **Trigger**: EventBridge schedule (cron: `0 */3 * * ? *`)
- **Schedule State**: Auto-enabled (dev/test), Disabled (prod)
- **Metrics Namespace**: `CallAnalytics/RingCentral`

#### Worker Lambda

- **Runtime**: Node.js 20
- **Memory**: 512 MB
- **Timeout**: 15 minutes
- **Concurrency**: Reserved 2 (prevents RingCentral 429 errors)
- **Trigger**: SQS (reconciliation-worker-queue)
- **Batch Size**: 1 message at a time (sequential processing)
- **DLQ**: reconciliation-worker-dlq (3 retries before DLQ)
- **Metrics Namespace**: `CallAnalytics/RingCentral`

#### Reconciliation Window

- **Default**: 6 hours (configurable via `RECONCILIATION_WINDOW_HOURS` env var)
- **Rationale**:
  - 2x processing SLA headroom (normal processing: \~3 hours)
  - Minimizes duplicate API calls (50% reduction vs 7-day window)
  - Balances freshness vs API cost
- **Frequency**: Every 3 hours (2 overlapping scans per window)
- **Maximum**: 90 days (RingCentral retention period)

***

## Dashboard Integration

### Primary Monitoring (Row 9: Per-Client Reconciliation)

Dashboard Row 9 provides per-client visibility into reconciliation case breakdown:

| Widget             | Metric                | What It Measures                | Healthy State       | Warning Threshold | Critical Threshold |
| ------------------ | --------------------- | ------------------------------- | ------------------- | ----------------- | ------------------ |
| **Row 9 Widget 1** | `ReconciliationCase1` | Calls missing in DynamoDB       | 0 or \<5 per client | >10 per client    | >50 per client     |
| **Row 9 Widget 2** | `ReconciliationCase2` | Recordings not downloaded       | 0                   | >5 per client     | >20 per client     |
| **Row 9 Widget 3** | `ReconciliationCase3` | Transcriptions not started      | 0                   | >5 per client     | >20 per client     |
| **Row 9 Widget 4** | `ReconciliationCase4` | Transcriptions stuck/failed     | 0 or \<3 per client | >5 per client     | >15 per client     |
| **Row 9 Widget 5** | `ReconciliationCaseD` | Orphaned records                | 0                   | >10 per client    | >20 per client     |
| **Row 9 Widget 6** | Total (all states)    | Total issues across all clients | 0                   | >50 total         | >100 total         |

**Dashboard Features**:

- **Stacked Area Charts**: Visualize trends over time (last 3 hours by default)
- **ClientId Dimension**: Drill down to specific clients (e.g., `orangeTheory-2c9fc00886d14b9a9a24a12d337c438c`)
- **Color Coding**: GREEN (State 1-3), BROWN (State 4, Case D)
- **Auto-Refresh**: Updates every minute

### Supporting Metrics (Other Dashboard Rows)

| Row        | Widget | Metric              | Purpose                    |
| ---------- | ------ | ------------------- | -------------------------- |
| **Row 11** | Left   | Lambda Errors       | Orchestrator/Worker errors |
| **Row 11** | Right  | Lambda Throttles    | Concurrency limit reached  |
| **Row 12** | Left   | `RateLimitCapacity` | RingCentral API headroom   |
| **Row 12** | Right  | `ApiCallsPerMinute` | API call trending          |

### Health Check Guide

#### ✅ Healthy System

```
Row 9 Widget 6: Total issues = 0 or trending down
Row 11 Left: No errors in last 3 hours
Row 12 Left: RateLimitCapacity >5
Row 12 Right: ApiCallsPerMinute stable (<10/min)
```

#### ⚠️ Warning (Investigate)

```
Row 9 Widget 1: ReconciliationCase1 >10 (backlog forming)
Row 9 Widget 4: ReconciliationCase4 >5 (transcription failures)
Row 12 Left: RateLimitCapacity <3 (approaching rate limit)
Row 11 Left: 1-2 errors in last hour (isolated failures)
```

**Actions**:

1. Check CloudWatch Logs Insights for error patterns
2. Review per-client breakdown (click widget → "View in metrics")
3. Monitor for trend continuation (is it getting worse?)
4. Prepare for manual intervention if critical threshold reached

#### 🚨 Critical (Immediate Action Required)

```
Row 9 Widget 1: ReconciliationCase1 >50 (major sync gap)
Row 9 Widget 6: Total issues >100 (widespread problem)
Row 9 Widget 5: CaseD >20 (data integrity issue)
Row 11 Left: >3 errors in 1 hour (systemic failure)
Row 12 Left: RateLimitCapacity <1 (rate limit imminent)
```

**Actions**:

1. Disable schedule immediately (prevent more failures)
2. Check RingCentral service status (outage?)
3. Review last deployment (recent change?)
4. Follow runbook for specific error pattern
5. Escalate to engineering if root cause unclear

### Per-Client Drill-Down

Dashboard Row 9 widgets include `ClientId` dimension for client-specific investigation:

#### Step 1: Identify Affected Clients

```
1. Navigate to CloudWatch Dashboard → Row 9
2. Click any widget (e.g., ReconciliationCase1)
3. Click "View in metrics"
4. Add dimension filter: ClientId
5. Sort by "Maximum" value descending
```

**Example Output**:

```
ClientId: orangeTheory-2c9fc00886d14b9a9a24a12d337c438c → 23 issues
ClientId: planetFitness-5f8a7b9c0d1e2f3g4h5i6j7k8l9m0n1o → 12 issues
ClientId: golds-gym-a1b2c3d4e5f6g7h8i9j0k1l2m3n4o5p6 → 5 issues
```

#### Step 2: Check Time-Series Trend

```
1. Select specific ClientId dimension
2. Change statistic to "Sum" (total issues)
3. Adjust time range to "Last 24 hours"
4. Look for patterns:
   - Sudden spike? → Recent incident
   - Gradual increase? → Slow degradation
   - Flat/decreasing? → Self-healing
```

#### Step 3: Cross-Reference with Logs

```
Use CloudWatch Logs Insights to investigate specific client:

fields @timestamp, reconciliationCase, action, reason
| filter client_id = "orangeTheory-2c9fc00886d14b9a9a24a12d337c438c"
| filter @message like /Reconciliation completed/
| sort @timestamp desc
| limit 20
```

***

## 告警语义（谁 page 人）

日志级别 ≠ 告警级别。PROD 页人靠 **CloudWatch alarm → SNS → alarm-dispatcher Lambda → Lark**，不靠 Lambda `logger.error`（`logger.error` 只到 CloudWatch + Sentry，**不发 Lark**；只有 `logger.alert` 直接发 Lark）。以 `lib/stacks/monitoring-stack.ts` 为准。

Reconciliation 相关 alarm：

| Alarm                                                             | Trigger                | Pages 人？ | 说明                                                                    |
| ----------------------------------------------------------------- | ---------------------- | -------- | --------------------------------------------------------------------- |
| **ReconciliationDLQAlarm**（`reconciliation-dlq-alarm`）            | DLQ ≥5 条               | ✅ **是**  | client 处理 3 次重试后仍失败才进 DLQ                                             |
| **ReconciliationQueueAgeAlarm**（`reconciliation-queue-age-alarm`） | 队列最老消息 >2h             | ✅ **是**  | worker 卡住但没报错（并发耗尽 / Neon 慢 / AI 限流）—— DLQ 告警只抓「失败」，这个抓「停滞」           |
| **ReconciliationWorkerErrorRateAlarm**                            | error rate >10%（30min） | ❌ 否      | 仅 dashboard/ticket 诊断信号，**不 page**（源码：queue age 和 DLQ 才是 pageable 症状） |

**收到告警怎么办** → DLQ / queue-age 的响应 step 统一在 infra repo 的 [Pipeline Monitoring Runbook](https://github.com/retaintive/callytics-infrastructure/blob/main/docs/observability/runbook-pipeline-monitoring.md#dlq) + [Queue age](https://github.com/retaintive/callytics-infrastructure/blob/main/docs/observability/runbook-pipeline-monitoring.md#queue-age) 维护，这里不重复。

***

## Common Issues

### Issue 1: ReconciliationCase1 Increasing (Missing Calls)

#### Symptoms

- **Dashboard**: Row 9 Widget 1 showing ReconciliationCase1 >10
- **Dashboard**: Row 9 Widget 6 total issues trending up
- **Metrics**: `ReconciliationCase1` dimension `ClientId` identifies specific clients
- **Logs**: Messages like `State 1: Missing in DB` in worker logs

#### Investigation

**Step 1: Identify Affected Clients**

```
# CloudWatch Logs Insights - Find clients with State 1 issues
fields @timestamp, client_id, reconciliationCase
| filter @message like /State 1: Missing/
| stats count() as MissingCallCount by client_id
| sort MissingCallCount desc
| limit 20
```

**Expected Output**:

```
client_id                                            MissingCallCount
orangeTheory-2c9fc00886d14b9a9a24a12d337c438c       23
planetFitness-5f8a7b9c0d1e2f3g4h5i6j7k8l9m0n1o      12
```

**Step 2: Check RingCentral API Health**

```
# Dashboard Row 12: Rate limit capacity
# Look for: RateLimitCapacity trending down or <3

# CloudWatch Logs Insights - Check for 429 errors
fields @timestamp, client_id, error
| filter @message like /429/ or @message like /rate limit/
| stats count() by client_id, bin(1h)
```

**Step 3: Check Webhook Delivery**

```
# TranscribeProcessor logs - Verify webhooks are being received
fields @timestamp, client_id
| filter @message like /webhook received/
| stats count() as WebhookCount by client_id, bin(1h)
| sort @timestamp desc
```

#### Root Cause Analysis

**Scenario A: Webhook Delivery Failure**

- **Symptoms**: Calls made but webhooks not received → Reconciliation detects and backfills
- **Dashboard**: Row 1 Left (TranscribeProcessor) shows low throughput
- **Investigation**: Check API Gateway metrics for 4xx/5xx errors on webhook endpoint
- **Expected Behavior**: Reconciliation self-heals by creating missing records
- **Fix**: Usually self-heals within 3 hours. If persistent, contact RingCentral support for webhook delivery investigation.

**Scenario B: TranscribeProcessor Errors**

- **Symptoms**: Webhooks received but processing failed → DLQ accumulation
- **Dashboard**: Row 1 Left shows DLQ depth >0
- **Investigation**:
  ```
  # Check TranscribeProcessor errors
  fields @timestamp, client_id, error
  | filter ispresent(error)
  | filter @message like /TranscribeProcessor/
  | stats count() by error
  ```
- **Fix**: Follow [transcribe-processor.md#dlq-recovery](lambda/transcribe-processor.md#dlq-recovery)

**Scenario C: Calls Without Recordings**

- **Symptoms**: RingCentral calls exist without recordings (e.g., voicemail, abandoned calls)
- **Dashboard**: ReconciliationCase1 count matches calls without recordings
- **Investigation**:
  ```
  # Check hasRecording field in logs
  fields @timestamp, client_id, hasRecording
  | filter @message like /Call exists.*without recording/
  | stats count() by hasRecording
  ```
- **Expected Behavior**: V2.5.1+ creates audit records with `hasRecording: false` (not an error)
- **Fix**: No action needed - these are processed correctly

**Scenario D: EventBridge Schedule Disabled**

- **Symptoms**: ReconciliationCase1 increasing over multiple days (no automatic cleanup)
- **Investigation**: Check schedule state
  ```bash
  aws scheduler get-schedule \
    --name call-analytics-prod-reconciliation \
    --query 'State'
  ```
- **Fix**: Enable schedule (see [Operational Procedures](#enabledisable-reconciliation-schedule))

#### Resolution

**Automatic (Recommended)**:

- Monitor Row 9 Widget 1 for trend reversal (should decrease within 3-6 hours)
- Reconciliation runs every 3 hours and auto-fixes State 1 issues
- If trending down, no action needed

**Manual (Emergency)**:

```bash
# Trigger immediate reconciliation for specific client
aws lambda invoke \
  --function-name ReconciliationOrchestrator \
  --payload '{"mode":"execute"}' \
  /tmp/response.json

# Monitor progress
tail -f /tmp/response.json
```

**Preventive**:

- Ensure EventBridge schedule is enabled (auto-enabled in dev/test as of V2.5.3)
- Monitor webhook delivery success rate
- Set up alerts for ReconciliationCase1 >10 per client

***

### Issue 2: ReconciliationCase4 Persistent (Transcription Stuck)

#### Symptoms

- **Dashboard**: Row 9 Widget 4 showing ReconciliationCase4 >5
- **Metrics**: `ReconciliationCase4` indicates transcriptions not completing
- **Duration**: Issues persisting >24 hours
- **Logs**: Messages like `State 4: Transcription not completed`

#### Investigation

**Step 1: Identify Stuck Transcriptions**

```
# CloudWatch Logs Insights - Find State 4 issues
fields @timestamp, client_id, transcriptionJobName, reason
| filter @message like /State 4: Transcription not completed/
| sort @timestamp desc
| limit 50
```

**Expected Output**:

```
@timestamp                    client_id                  transcriptionJobName              reason
2025-11-30T14:30:00Z         orangeTheory-abc123        transcribe-job-xyz123             Stuck for >24h
```

**Step 2: 确认转录卡在哪一步（不是查 AWS Transcribe job）**

> ⚠️ 转录早已不是 AWS Transcribe，**没有** `aws transcribe get-transcription-job` 可查。State 4 的修复方式是 worker 重置 flag + 往 transcribe-queue 重投 synthetic webhook，重新走 Deepgram（`handleCaseB`，`reconciliation-worker/src/handler.ts:752`）。State 4 的 `transcriptionJobName` 只是内部标识符。

```
# CloudWatch Logs Insights — 看这个 session 在 transcribe-processor 侧的转录记录
fields @timestamp, session, provider, @message
| filter session = "xyz123"
| filter @message like /Transcription/ or @message like /Deepgram/ or @message like /Gemini/
| sort @timestamp desc
```

**Step 3: Check DynamoDB State**

```
# CloudWatch Logs Insights - DB 里 transcriptionStarted / transcriptionCompleted 状态
fields @timestamp, telephonySessionId, transcriptionStarted, transcriptionCompleted
| filter @message like /transcriptionJobName/
| filter transcriptionJobName = "xyz123"
```

#### Root Cause Analysis

**Scenario A: 转录反复失败（Deepgram）**

- **Symptoms**: transcribe-processor 侧持续 `Deepgram failed` / `DEEPGRAM_SUSTAINED_FAILURE`
- **Dashboard**: `CallsTranscriptionNotCompleted`（旧名 ReconciliationCase4）与失败量对应
- **Common Failure Reasons**:
  - Deepgram API key 失效 / secret 缺失
  - 录音无法访问（S3 IAM）或音频损坏
  - Deepgram 服务 outage
- **Fix**:
  - 排查见 [transcribe-processor.md Issue 5](/operations/runbooks/transcribe-processor.md#issue-5-deepgram-转录失败与二次重写)
  - 修好根因后，reconciliation 每 3 小时会自动重投 webhook 重试；无需手动碰 AWS Transcribe

**Scenario B: DynamoDB State Desync（transcript 已产出但 DB 没更新）**

- **Symptoms**: S3 里已有 transcript，但 `transcriptionCompleted` = false in DB
- **Investigation**: Check ai-analysis-processor (result processor) logs
  ```
  fields @timestamp, telephonySessionId, error
  | filter telephonySessionId = "xyz123"
  | filter @message like /ResultProcessor/ or @message like /ai-analysis/
  ```
- **Fix**: Manual DB update or re-trigger downstream processing
  ```bash
  # Option A: Manual DB update (emergency only)
  aws dynamodb update-item \
    --table-name call-analysis \
    --key '{"telephonySessionId": {"S": "xyz123"}}' \
    --update-expression "SET transcriptionCompleted = :true" \
    --expression-attribute-values '{":true": {"BOOL": true}}'

  # Option B: Re-inject webhook 让 transcribe-processor 重跑（safer）
  ```

**Scenario C: Deepgram 服务 outage**

- **Symptoms**: 多个 session 同时卡住，transcribe-processor 侧 `DEEPGRAM_SUSTAINED_FAILURE`
- **Dashboard**: `CallsTranscriptionNotCompleted` 跨多 client spike
- **Investigation**: 查 Deepgram 服务状态（[https://status.deepgram.com）](https://status.deepgram.com）)
- **Fix**: 等服务恢复；reconciliation 会自动重投重试

#### Resolution

**Automatic (Recommended)**:

- Reconciliation Worker 每 3 小时重试 State 4（重置 flag + 重投 webhook → 重新 Deepgram 转录）
- Monitor Row 9 Widget 4（`CallsTranscriptionNotCompleted`）for trend (should decrease within 6-9 hours)

**Manual (If persistent >12 hours)**:

```bash
# Re-trigger reconciliation for specific client
aws lambda invoke \
  --function-name ReconciliationOrchestrator \
  --payload '{"mode":"execute"}' \
  /tmp/response.json

# 无需 cancel AWS Transcribe job（不存在）—— reconciliation 检测到未完成会自动重投 webhook 重跑
```

***

### Issue 3: CaseD Spike (Orphaned Records)

#### Symptoms

- **Dashboard**: Row 9 Widget 5 showing ReconciliationCaseD >20
- **Metrics**: `ReconciliationCaseD` indicating orphaned records
- **Logs**: Messages like `Case D: Orphaned Record`

#### Investigation

**Step 1: Identify Orphaned Records**

```
# CloudWatch Logs Insights - Find Case D issues
fields @timestamp, client_id, telephonySessionId, reason
| filter @message like /Case D: Orphaned/
| stats count() by client_id
| sort count desc
```

**Step 2: Verify RingCentral Deletion**

```bash
# Check if call still exists in RingCentral (should return 404)
aws lambda invoke \
  --function-name ReconciliationWorker \
  --payload '{"client_id": "orangeTheory-abc123", "mode": "dry_run"}' \
  /tmp/response.json

# Check logs for RingCentral API response
# Expected: "Call not found" or 404 error
```

**Step 3: Check Record Age**

```
# CloudWatch Logs Insights - Check how old orphaned records are
fields @timestamp, telephonySessionId, lastUpdated, callStartTime
| filter @message like /Case D: Orphaned/
| sort @timestamp desc
```

#### Root Cause Analysis

**Scenario A: Normal RingCentral Retention (Expected)**

- **Symptoms**: Records >90 days old being marked as orphaned
- **Dashboard**: CaseD count stable, not increasing rapidly
- **Explanation**: RingCentral deletes calls after 90 days (retention policy)
- **Fix**: No action needed - this is expected behavior
- **Note**: DynamoDB records are marked as orphaned but NOT deleted (audit trail)

**Scenario B: Bulk RingCentral Account Deletion**

- **Symptoms**: Sudden spike in CaseD across all clients or specific client
- **Dashboard**: CaseD increased by >50 in single reconciliation run
- **Investigation**: Contact RingCentral support to verify account status
- **Fix**: If account was deleted intentionally, mark all records as orphaned (permanent)

**Scenario C: DynamoDB Test Data**

- **Symptoms**: CaseD spike after dev/test environment migration
- **Dashboard**: CaseD only in dev/test, not prod
- **Explanation**: Test data created manually without corresponding RingCentral calls
- **Fix**: Purge test data from DynamoDB
  ```bash
  # List orphaned records
  aws dynamodb scan \
    --table-name call-events \
    --filter-expression "attribute_exists(reconciliationAttempted) AND reconciliationSuccess = :false" \
    --expression-attribute-values '{":false": {"BOOL": false}}' \
    --projection-expression "telephonySessionId"

  # Delete in batches (carefully!)
  # Use batch-write-item with DeleteRequest
  ```

#### Resolution

**Normal Behavior**:

- CaseD count \<20 is normal (gradual accumulation from 90-day retention)
- No action needed - records are marked as orphaned for audit trail

**Abnormal Spike**:

```
1. Investigate sudden increase (check RingCentral account status)
2. Verify records are truly orphaned (cross-check with RingCentral)
3. If confirmed, mark as permanently failed in DynamoDB
4. Update alert threshold if baseline changed
```

***

### Issue 4: ReconciliationWorker DLQ Messages

#### Symptoms

- **Dashboard**: Row 1 (SQS) shows DLQ depth >0 for reconciliation-worker-dlq
- **Alarms**: `reconciliation-dlq-alarm` triggered (>5 messages)
- **Logs**: Messages failing after 3 retries

#### Investigation

See detailed troubleshooting guide: [reconciliation/troubleshooting.md#scenario-1-messages-stuck-in-dlq](reconciliation/troubleshooting.md#scenario-1-messages-stuck-in-dlq)

**Quick Diagnosis**:

```bash
# Check DLQ depth
aws sqs get-queue-attributes \
  --queue-url https://sqs.{region}.amazonaws.com/{account}/call-analytics-{env}-reconciliation-worker-dlq \
  --attribute-names ApproximateNumberOfMessages

# Read DLQ messages
aws sqs receive-message \
  --queue-url https://sqs.{region}.amazonaws.com/{account}/call-analytics-{env}-reconciliation-worker-dlq \
  --max-number-of-messages 10 > dlq-messages.json

# Analyze error patterns
cat dlq-messages.json | jq '.Messages[].Body | fromjson'
```

**Common Error Patterns**:

| Error                            | Cause                                    | Fix                               |
| -------------------------------- | ---------------------------------------- | --------------------------------- |
| `Secret not found`               | Missing `secret_name` in DynamoDB config | Add field to client config        |
| `AccessDeniedException`          | IAM policy missing secret pattern        | Update IAM policy                 |
| `429 Too Many Requests`          | Rate limit exceeded                      | Already handled by 2-10 min retry |
| `Invalid JSON in secret`         | Corrupted OAuth token                    | Run token refresher Lambda        |
| `Client configuration not found` | DynamoDB record missing                  | Verify client in config table     |

#### Resolution

**Option A: Fix Root Cause and Re-drive**

```bash
# After fixing root cause, re-drive messages back to main queue
aws sqs start-message-move-task \
  --source-arn arn:aws:sqs:{region}:{account}:call-analytics-{env}-reconciliation-worker-dlq \
  --destination-arn arn:aws:sqs:{region}:{account}:call-analytics-{env}-reconciliation-worker-queue
```

**Option B: Manual Reprocessing**

```bash
# Trigger reconciliation for specific client
aws lambda invoke \
  --function-name ReconciliationOrchestrator \
  --payload '{"mode":"execute"}' \
  /tmp/response.json
```

**Option C: Delete Invalid Messages**

```bash
# CAUTION: Only if messages are truly invalid
aws sqs purge-queue \
  --queue-url https://sqs.{region}.amazonaws.com/{account}/call-analytics-{env}-reconciliation-worker-dlq
```

***

### Issue 5: Rate Limit Errors During Reconciliation

#### Symptoms

- **Dashboard**: Row 12 Left shows `RateLimitCapacity` \<3
- **Metrics**: `RateLimitErrors` increasing
- **Logs**: `429 Too Many Requests` errors in worker logs
- **Impact**: Reconciliation jobs failing, retrying after 2-10 minutes

#### Investigation

**Step 1: Check Rate Limit Capacity**

```
# CloudWatch Logs Insights - Track rate limit headers
fields @timestamp, client_id, rateLimitRemaining, rateLimitLimit
| filter @message like /RingCentral rate limit/
| stats avg(rateLimitRemaining) as AvgRemaining by client_id, bin(5m)
| sort @timestamp desc
```

**Step 2: Identify High-Volume Clients**

```
# CloudWatch Logs Insights - Count API calls per client
fields @timestamp, client_id
| filter @message like /Fetching.*RingCentral/
| stats count() as ApiCallCount by client_id
| sort ApiCallCount desc
```

**Step 3: Check Concurrency**

```bash
# Verify worker Lambda reserved concurrency
aws lambda get-function-concurrency \
  --function-name ReconciliationWorker \
  --query 'ReservedConcurrentExecutions'

# Expected: 2 (prevents rate limit storms)
```

#### Root Cause Analysis

**Scenario A: Reserved Concurrency Too High**

- **Symptoms**: Multiple workers processing simultaneously → API storm
- **Dashboard**: ApiCallsPerMinute >10
- **Investigation**: Check Lambda metrics for concurrent executions
- **Fix**: Reduce reserved concurrency from 10 → 2
  ```typescript
  // lib/stacks/lambda-stack.ts
  reconciliationWorker.addFunctionUrl({
    reservedConcurrentExecutions: 2, // Changed from 10
  });
  ```

**Scenario B: SQS Batch Size Too Large**

- **Symptoms**: Worker processing multiple messages → multiple API calls
- **Dashboard**: RateLimitCapacity drops sharply during reconciliation runs
- **Investigation**: Check SQS event source configuration
  ```bash
  aws lambda list-event-source-mappings \
    --function-name ReconciliationWorker \
    --query 'EventSourceMappings[0].BatchSize'
  ```
- **Fix**: Ensure batch size = 1 (sequential processing)

**Scenario C: Normal High Volume (Not an Error)**

- **Symptoms**: RateLimitCapacity fluctuates but >3 most of the time
- **Dashboard**: Occasional dips to 2-3, then recovers
- **Explanation**: V2.5.0 wide-spread retry (2-10 min delays) prevents storms
- **Fix**: No action needed - system is self-regulating

#### Resolution

**Automatic (V2.5.0 Feature)**:

- Worker extends SQS visibility timeout by 2-10 minutes on 429
- Lambda throws error → SQS retries after random delay
- Prevents thundering herd retry storms

**Manual (If persistent)**:

```bash
# Temporarily disable schedule to reduce load
aws scheduler update-schedule \
  --name call-analytics-prod-reconciliation \
  --state DISABLED

# Wait for rate limit window to reset (typically 1 minute)

# Re-enable schedule
aws scheduler update-schedule \
  --name call-analytics-prod-reconciliation \
  --state ENABLED
```

**Preventive**:

- Monitor Row 12 Left (RateLimitCapacity) in dashboard
- Set up alarm for RateLimitCapacity \<3 for >5 minutes
- Review reserved concurrency settings after client onboarding

***

## Metrics Reference

### Reconciliation Case Metrics

#### ReconciliationCase1

- **Namespace**: `CallAnalytics/RingCentral`
- **Dashboard**: Row 9 Widget 1
- **Dimensions**: `ClientId`
- **Unit**: Count
- **Meaning**: Calls exist in RingCentral but missing in DynamoDB
- **Healthy**: 0 or \<5 per client
- **Warning**: >10 per client (backlog forming)
- **Critical**: >50 per client (major sync gap)
- **Troubleshoot**: Webhook delivery failure, TranscribeProcessor errors, EventBridge schedule disabled

#### ReconciliationCase2

- **Namespace**: `CallAnalytics/RingCentral`
- **Dashboard**: Row 9 Widget 2
- **Dimensions**: `ClientId`
- **Unit**: Count
- **Meaning**: Call in DB but recording not downloaded (>15 minutes)
- **Healthy**: 0
- **Warning**: >5 per client
- **Critical**: >20 per client
- **Troubleshoot**: RingCentral API errors, network issues, IAM permissions for S3 upload

#### ReconciliationCase3

- **Namespace**: `CallAnalytics/RingCentral`
- **Dashboard**: Row 9 Widget 3
- **Dimensions**: `ClientId`
- **Unit**: Count
- **Meaning**: Recording downloaded but transcription not started (>30 minutes)
- **Healthy**: 0
- **Warning**: >5 per client
- **Critical**: >20 per client
- **Troubleshoot**: AWS Transcribe quota limits, IAM permissions, S3 bucket access

#### ReconciliationCase4

- **Namespace**: `CallAnalytics/RingCentral`
- **Dashboard**: Row 9 Widget 4
- **Dimensions**: `ClientId`
- **Unit**: Count
- **Meaning**: Transcription started but not completed (>24 hours)
- **Healthy**: 0 or \<3 per client
- **Warning**: >5 per client (transcription failures)
- **Critical**: >15 per client (systemic issue)
- **Troubleshoot**: AWS Transcribe job failures, audio format issues, service backlog

#### ReconciliationCaseD

- **Namespace**: `CallAnalytics/RingCentral`
- **Dashboard**: Row 9 Widget 5
- **Dimensions**: `ClientId`
- **Unit**: Count
- **Meaning**: Record in DynamoDB but deleted from RingCentral (>90 days)
- **Healthy**: \<20 total (gradual accumulation from retention policy)
- **Warning**: >20 total or sudden spike
- **Critical**: >50 total (potential account deletion)
- **Troubleshoot**: RingCentral account status, test data cleanup, retention policy changes

### Operational Metrics

#### ProcessingDuration

- **Namespace**: `CallAnalytics/RingCentral`
- **Dashboard**: Not visualized (use CloudWatch Metrics Explorer)
- **Dimensions**: `ClientId`
- **Unit**: Milliseconds
- **Meaning**: Time taken to process single reconciliation action
- **Healthy**: \<30,000 ms (30 seconds)
- **Warning**: >60,000 ms (1 minute)
- **Critical**: >180,000 ms (3 minutes, approaching timeout)
- **Troubleshoot**: Slow RingCentral API, large recording downloads, network latency

#### RateLimitErrors

- **Namespace**: `CallAnalytics/RingCentral`
- **Dashboard**: Not visualized (use CloudWatch Metrics Explorer)
- **Dimensions**: `ClientId`, `Operation`
- **Unit**: Count
- **Meaning**: Number of RingCentral 429 rate limit errors
- **Healthy**: 0
- **Warning**: >1 per hour (occasional limits)
- **Critical**: >5 per hour (sustained rate limiting)
- **Troubleshoot**: High concurrency, large reconciliation windows, insufficient delays

#### RateLimitCapacity

- **Namespace**: `CallAnalytics/RingCentral`
- **Dashboard**: Row 12 Left
- **Dimensions**: `Source` (webhook vs reconciliation)
- **Unit**: Count (raw remaining requests)
- **Meaning**: RingCentral API requests remaining in current window
- **Healthy**: >5
- **Warning**: \<3 (approaching rate limit)
- **Critical**: \<1 (rate limit imminent)
- **Troubleshoot**: Reduce concurrency, increase delays, disable schedule temporarily

#### ApiCallsPerMinute

- **Namespace**: `CallAnalytics/RingCentral`
- **Dashboard**: Row 12 Right
- **Dimensions**: `Source` (webhook vs reconciliation)
- **Unit**: Count
- **Meaning**: RingCentral API calls per minute (CloudWatch sums automatically)
- **Healthy**: \<10 per minute
- **Warning**: >10 per minute (high load)
- **Critical**: >20 per minute (rate limit storm risk)
- **Troubleshoot**: Check reserved concurrency, SQS batch size, reconciliation frequency

***

## CloudWatch Logs Insights Queries

### Reconciliation Actions Taken (Last Run)

**Purpose**: See what actions reconciliation took in last run

```
fields @timestamp, client_id, reconciliationCase, action, reason
| filter @message like /Reconciliation action/
| sort @timestamp desc
| limit 100
```

**Expected Output**:

```
@timestamp                 client_id              reconciliationCase  action            reason
2025-11-30T14:30:00Z      orangeTheory-abc123    1                   inject_webhook    Missing in DB
2025-11-30T14:31:00Z      planetFitness-xyz789   4                   start_transcription   Stuck >24h
```

### Per-Client Reconciliation Summary

**Purpose**: Aggregate view of issues per client

```
fields client_id, reconciliationCase
| filter @message like /Reconciliation completed/
| stats sum(state1) as MissingCalls,
        sum(state2) as RecordingsNeeded,
        sum(state3) as TranscriptionsNeeded,
        sum(state4) as TranscriptionsStuck,
        sum(caseD) as OrphanedRecords
  by client_id
| sort MissingCalls desc
```

**Expected Output**:

```
client_id              MissingCalls  RecordingsNeeded  TranscriptionsNeeded  TranscriptionsStuck  OrphanedRecords
orangeTheory-abc123    23            0                 0                     5                    2
planetFitness-xyz789   12            0                 0                     3                    1
```

### Track State 1 Trend (Missing Calls Over Time)

**Purpose**: See if State 1 (missing calls) is increasing or decreasing

```
fields @timestamp, client_id
| filter @message like /State 1: Missing/
| stats count() as MissingCallCount by client_id, bin(3h)
| sort @timestamp desc
```

**Expected Output**:

```
@timestamp                 client_id              MissingCallCount
2025-11-30T12:00:00Z      orangeTheory-abc123    23
2025-11-30T09:00:00Z      orangeTheory-abc123    18  (decreasing - good!)
2025-11-30T06:00:00Z      orangeTheory-abc123    25
```

### Find Rate Limit Errors by Client

**Purpose**: Identify which clients are hitting rate limits

```
fields @timestamp, client_id, error
| filter @message like /429/ or @message like /rate limit/
| stats count() as RateLimitCount by client_id
| sort RateLimitCount desc
```

**Expected Output**:

```
client_id              RateLimitCount
orangeTheory-abc123    12
planetFitness-xyz789   5
```

### Reconciliation Processing Duration by Client

**Purpose**: Identify slow reconciliation runs

```
fields @timestamp, client_id, elapsedMs
| filter @message like /Reconciliation completed/
| stats avg(elapsedMs) as AvgDuration, max(elapsedMs) as MaxDuration by client_id
| sort MaxDuration desc
```

**Expected Output**:

```
client_id              AvgDuration  MaxDuration
orangeTheory-abc123    45000        180000  (3 minutes - slow!)
planetFitness-xyz789   12000        30000   (30 seconds - normal)
```

### Find Calls Without Recordings (V2.5.1)

**Purpose**: Distinguish calls without recordings (not an error)

```
fields @timestamp, client_id, hasRecording
| filter @message like /Call exists.*without recording/
| stats count() by client_id, hasRecording
```

**Expected Output**:

```
client_id              hasRecording  count
orangeTheory-abc123    false         4  (calls without recordings - expected)
```

### Identify DLQ Failure Patterns

**Purpose**: See why messages are ending up in DLQ

```
fields @timestamp, client_id, error
| filter ispresent(error)
| filter approximateReceiveCount >= 3
| stats count() as FailureCount by error
| sort FailureCount desc
```

**Expected Output**:

```
error                                          FailureCount
ResourceNotFoundException: Secret not found    8
AccessDeniedException (Secrets Manager)        5
429 Too Many Requests                          2
```

### Compare RingCentral Calls vs DynamoDB Records

**Purpose**: Identify data sync gaps

```
fields @timestamp, client_id, ringCentralCalls, dynamoDBStates
| filter @message like /Starting reconciliation engine/
| sort @timestamp desc
| limit 20
```

**Expected Output**:

```
@timestamp                 client_id              ringCentralCalls  dynamoDBStates  Gap
2025-11-30T14:30:00Z      orangeTheory-abc123    150               127             23 missing
```

### Track Reconciliation Success Rate

**Purpose**: Overall health check

```
fields @timestamp
| filter @message like /Reconciliation job completed/ or @message like /Reconciliation job failed/
| stats count(@message like /completed/) as Success,
        count(@message like /failed/) as Failed
| eval SuccessRate = Success * 100 / (Success + Failed)
```

**Expected Output**:

```
Success  Failed  SuccessRate
95       5       95.0%  (healthy)
```

### Find Stuck Transcription Jobs

**Purpose**: Identify transcriptions stuck in State 4

```
fields @timestamp, telephonySessionId, transcriptionJobName, reason
| filter @message like /State 4: Transcription not completed/
| sort @timestamp desc
| limit 50
```

**Expected Output**:

```
@timestamp                 telephonySessionId  transcriptionJobName      reason
2025-11-30T14:30:00Z      xyz123              transcribe-job-xyz123     Stuck for >24h
```

### Monitor SQS Visibility Timeout Extensions (429 Handling)

**Purpose**: See how often reconciliation is hitting rate limits and retrying

```
fields @timestamp, client_id, delaySeconds
| filter @message like /Extending visibility timeout/
| stats count() as RetryCount, avg(delaySeconds) as AvgDelay by client_id
| sort RetryCount desc
```

**Expected Output**:

```
client_id              RetryCount  AvgDelay
orangeTheory-abc123    12          360  (6 minutes avg delay)
planetFitness-xyz789   5           180  (3 minutes avg delay)
```

### Find Orphaned Records (Case D)

**Purpose**: Investigate orphaned records for data integrity

```
fields @timestamp, telephonySessionId, client_id, lastUpdated, reason
| filter @message like /Case D: Orphaned/
| sort @timestamp desc
| limit 50
```

**Expected Output**:

```
@timestamp                 telephonySessionId  client_id           lastUpdated           reason
2025-11-30T14:30:00Z      abc123              orangeTheory-123    2025-08-15T10:00:00Z  >90 days old
```

### Reconciliation Window Coverage

**Purpose**: Verify reconciliation window is appropriate

```
fields @timestamp, client_id, dateRange.start, dateRange.end
| filter @message like /Fetching RingCentral call logs/
| stats count() by client_id
```

**Expected Output**:

```
client_id              count  start                      end
orangeTheory-abc123    1      2025-11-30T08:30:00Z      2025-11-30T14:30:00Z  (6 hours - good)
```

### Action Execution Success/Failure

**Purpose**: Track action execution outcomes

```
fields @timestamp, actionType, callId, success
| filter @message like /Reconciliation action executed/ or @message like /Reconciliation action execution failed/
| stats count(@message like /executed successfully/) as Success,
        count(@message like /execution failed/) as Failed
  by actionType
```

**Expected Output**:

```
actionType            Success  Failed
inject_webhook        45       2
start_transcription   12       1
inject_s3_event       5        0
```

### SQS Message Processing Metrics

**Purpose**: Understand message retry patterns

```
fields @timestamp, messageId, approximateReceiveCount
| filter @message like /Processing reconciliation job/
| stats count() as TotalMessages,
        avg(approximateReceiveCount) as AvgRetries,
        max(approximateReceiveCount) as MaxRetries
  by bin(1h)
| sort @timestamp desc
```

**Expected Output**:

```
@timestamp             TotalMessages  AvgRetries  MaxRetries
2025-11-30T14:00:00Z  50             1.2         3
2025-11-30T13:00:00Z  48             1.1         2  (mostly first attempts - healthy)
```

***

## Operational Procedures

### Enable/Disable Reconciliation Schedule

#### Check Current State

```bash
# Get schedule state
aws scheduler get-schedule \
  --name call-analytics-{env}-reconciliation \
  --query 'State' \
  --output text

# Expected output: ENABLED or DISABLED
```

#### Enable Schedule

```bash
# Production (requires manual approval - safety measure)
aws scheduler update-schedule \
  --name call-analytics-prod-reconciliation \
  --state ENABLED

# Verify
aws scheduler get-schedule \
  --name call-analytics-prod-reconciliation \
  --query 'State'
```

**When to Enable**:

- After successful deployment and testing in dev/test
- After incident resolution when root cause is fixed
- When manual reconciliation is no longer needed

**Note**: V2.5.3 change - dev/test environments auto-enabled, prod disabled by default

#### Disable Schedule

```bash
# Emergency stop (any environment)
aws scheduler update-schedule \
  --name call-analytics-{env}-reconciliation \
  --state DISABLED

# Verify
aws scheduler get-schedule \
  --name call-analytics-{env}-reconciliation \
  --query 'State'
```

**When to Disable**:

- During major incidents (prevent more failures)
- Before risky deployments (pause reconciliation during change)
- When investigating data integrity issues (prevent auto-fixes)
- During RingCentral API outages (prevent rate limit storms)

### Manual Reconciliation Run

#### Trigger Orchestrator (Scan-Only Mode)

**Purpose**: See what issues exist without fixing them

```bash
# Dry run - generates report without taking action
aws lambda invoke \
  --function-name ReconciliationOrchestrator \
  --payload '{"mode":"dry_run"}' \
  /tmp/response.json

# View results
cat /tmp/response.json | jq
```

**Expected Output**:

```json
{
  "statusCode": 200,
  "body": "{\"clients_queued\":5,\"clients_failed\":0,\"failed_clients\":[]}"
}
```

#### Trigger Orchestrator (Execute Mode)

**Purpose**: Actually fix detected issues

```bash
# Execute mode - fixes issues immediately
aws lambda invoke \
  --function-name ReconciliationOrchestrator \
  --payload '{"mode":"execute"}' \
  /tmp/response.json

# View results
cat /tmp/response.json | jq
```

**When to Use**:

- After fixing root cause of reconciliation failures
- During off-peak hours (minimize RingCentral API load)
- For emergency data recovery
- When schedule is disabled but issues need fixing

**Caution**:

- Execute mode creates SQS messages with random delays (2-15 min)
- Actions process asynchronously (check logs for completion)
- Monitor dashboard Row 9 for case count decreases

#### Monitor Progress

**Step 1: Check Orchestrator Completion**

```bash
# Watch orchestrator logs
aws logs tail /aws/lambda/ReconciliationOrchestrator \
  --follow \
  --filter-pattern "Reconciliation orchestrator completed"
```

**Step 2: Check SQS Queue Depth**

```bash
# Monitor worker queue depth (should decrease over time)
aws sqs get-queue-attributes \
  --queue-url https://sqs.{region}.amazonaws.com/{account}/call-analytics-{env}-reconciliation-worker-queue \
  --attribute-names ApproximateNumberOfMessages \
  --query 'Attributes.ApproximateNumberOfMessages'

# Expected: Starts at ~5-50, decreases to 0 over 10-30 minutes
```

**Step 3: Monitor Dashboard**

```
1. Navigate to CloudWatch Dashboard → Row 9
2. Watch case counts decrease over time:
   - ReconciliationCase1: Should drop first (State 1 fixed quickly)
   - ReconciliationCase2-4: Gradual decrease (async processing)
3. Check for errors: Row 11 should show no errors
```

### Adjust Reconciliation Window

#### Temporary Override (via Environment Variable)

**Purpose**: Change reconciliation window for single run without code change

```bash
# Increase window to 12 hours (default: 6 hours)
aws lambda update-function-configuration \
  --function-name ReconciliationOrchestrator \
  --environment Variables={RECONCILIATION_WINDOW_HOURS=12}

# Verify
aws lambda get-function-configuration \
  --function-name ReconciliationOrchestrator \
  --query 'Environment.Variables.RECONCILIATION_WINDOW_HOURS'

# Trigger manual run with new window
aws lambda invoke \
  --function-name ReconciliationOrchestrator \
  --payload '{"mode":"execute"}' \
  /tmp/response.json
```

**When to Use**:

- After long outages (need wider window to catch up)
- For one-time historical data recovery
- Testing reconciliation with different windows

**Caution**:

- Larger windows increase RingCentral API calls (stay under rate limits)
- Maximum window: 90 days (RingCentral retention period)
- Revert to default (6 hours) after temporary use

#### Permanent Change (via CDK)

**Purpose**: Change default window for all future runs

1. Edit `lambda/reconciliation-worker/src/core/reconciliation-cases.ts`
   ```typescript
   /** 12 hours in milliseconds (changed from 6 hours) */
   const DEFAULT_WINDOW_MS = 12 * 60 * 60 * 1000;
   ```

2. Deploy change
   ```bash
   npm run build
   cdk deploy --context environment={env}
   ```

3. Verify in logs
   ```
   fields @timestamp, dateRange
   | filter @message like /Fetching RingCentral call logs/
   | limit 1

   # Check dateRange.start vs dateRange.end = 12 hours apart
   ```

**When to Use**:

- After changing reconciliation frequency (e.g., every 6 hours instead of 3)
- Based on observed data patterns (e.g., calls delayed >6 hours)
- To balance freshness vs API cost

**Best Practices**:

- Window should be 2x reconciliation frequency (safety margin)
- Don't exceed 24 hours (diminishing returns, higher API cost)
- Test in dev/test before production

### View Reconciliation History

#### Last 10 Runs

**Purpose**: Quick health check - are reconciliation runs succeeding?

```
fields @timestamp, totalIssues, state1, state2, state3, state4, caseD
| filter @message like /Reconciliation completed/
| sort @timestamp desc
| limit 10
```

**Expected Output**:

```
@timestamp                 totalIssues  state1  state2  state3  state4  caseD
2025-11-30T14:30:00Z      15           12      0       0       2       1
2025-11-30T11:30:00Z      18           15      0       0       2       1
2025-11-30T08:30:00Z      23           20      0       0       2       1  (improving!)
```

#### Trend Analysis (Last 7 Days)

**Purpose**: Long-term health - is backlog growing or shrinking?

```
fields @timestamp, totalIssues
| filter @message like /Reconciliation completed/
| stats avg(totalIssues) as AvgIssues, max(totalIssues) as MaxIssues by bin(1d)
| sort @timestamp desc
```

**Expected Output**:

```
@timestamp             AvgIssues  MaxIssues
2025-11-30T00:00:00Z  15.2       23
2025-11-29T00:00:00Z  18.5       30
2025-11-28T00:00:00Z  22.1       35  (trending down - good!)
```

#### Success Rate Over Time

**Purpose**: Reliability check - what percentage of runs succeed?

```
fields @timestamp
| filter @message like /Reconciliation/ and (@message like /completed/ or @message like /failed/)
| stats count(@message like /completed/) as Success,
        count(@message like /failed/) as Failed
  by bin(1d)
| eval SuccessRate = Success * 100 / (Success + Failed)
| sort @timestamp desc
```

**Expected Output**:

```
@timestamp             Success  Failed  SuccessRate
2025-11-30T00:00:00Z  8        0       100.0%  (perfect!)
2025-11-29T00:00:00Z  7        1       87.5%   (good)
```

***

## Code Reference

### Key Files

**Orchestrator**:

- Entry: `/lambda/reconciliation-orchestrator/src/handler.ts`
- Config scanner: `/lambda/reconciliation-orchestrator/src/infrastructure/config-scanner.ts`
- SQS publisher: `/lambda/reconciliation-orchestrator/src/infrastructure/sqs-publisher.ts`

**Worker**:

- Entry: `/lambda/reconciliation-worker/src/handler.ts`
- State detection: `/lambda/reconciliation-worker/src/core/reconciliation-cases.ts`
- Reconciliation engine: `/lambda/reconciliation-worker/src/core/reconciliation-engine.ts`
- Metrics: `/lambda/reconciliation-worker/src/utils/metrics.ts`
- RingCentral API: `/lambda/reconciliation-worker/src/infrastructure/ringcentral-client.ts`

**Shared**:

- Constants: `/lambda/shared/constants/reconciliation.ts`
- Models: `/lambda/reconciliation-worker/src/core/models.ts`

### Critical Code Sections

#### SQS Fan-Out (Orchestrator handler.ts:64-84)

**Purpose**: Scan DynamoDB for active clients, publish to SQS

```typescript
// Step 1: Scan DynamoDB for active clients
const scanner = createConfigScanner();
const scanResult = await scanner.scanActiveClients();

logger.info('Active clients found', {
  count: scanResult.count,
  clients: scanResult.clients.map((c) => c.client_id),
});

if (scanResult.count === 0) {
  logger.warn('No active clients found - nothing to reconcile');
  return createResponse(200, {
    clients_queued: 0,
    clients_failed: 0,
    failed_clients: [],
  });
}

// Step 2: Publish reconciliation jobs to SQS
const publisher = createSqsPublisher(mode);
const publishResult = await publisher.publishReconciliationJobs(scanResult.clients);
```

**Why It Matters**: V2.5.0 architecture - 1 SQS message per client (enables independent retry)

#### 4-State Detection (reconciliation-cases.ts:48-131)

**Purpose**: Classify call records into States 1-4 or Case D

```typescript
// State 1: Missing in DB
const missingInDB = ringCentralCalls.filter(
  (call) => !stateMap.has(call.sessionId)
);

// State 2: Recording not downloaded (>15 min)
const recordingNotDownloaded = dynamoDBStates.filter((state) => {
  if (!state.callStartTime) return false;
  const age = now - new Date(state.callStartTime).getTime();
  return (
    state.callLogFetched === true &&
    state.recordingAvailable === true &&
    state.recordingDownloaded === false &&
    state.transcriptionStarted === false &&
    age > RECORDING_DOWNLOAD_THRESHOLD_MS
  );
});

// State 3: Transcription not started (>30 min)
const transcriptionNotStarted = dynamoDBStates.filter((state) => {
  if (!state.callStartTime) return false;
  const age = now - new Date(state.callStartTime).getTime();
  return (
    state.recordingDownloaded === true &&
    state.transcriptionStarted === false &&
    age > TRANSCRIPTION_START_THRESHOLD_MS
  );
});

// State 4: Transcription not completed (>24 hours)
const transcriptionNotCompleted = dynamoDBStates.filter((state) => {
  if (!state.lastUpdated) return false;
  const age = now - new Date(state.lastUpdated).getTime();
  return (
    state.recordingDownloaded === true &&
    state.transcriptionStarted === true &&
    state.transcriptionCompleted !== true &&
    age > STUCK_THRESHOLD_MS
  );
});

// Case D: Orphaned records (>90 days old, deleted from RingCentral)
const orphanedRecords = dynamoDBStates.filter((state) => {
  if (!state.lastUpdated) return false;
  const age = now - new Date(state.lastUpdated).getTime();
  return !callMap.has(state.callSessionId) && age > ORPHAN_THRESHOLD_MS;
});
```

**Why It Matters**: Granular state detection enables targeted fixes (e.g., State 2 = download recording, State 3 = start transcription)

#### Per-Client Metrics (metrics.ts:120-156)

**Purpose**: Emit metrics with ClientId dimension for per-client visibility

```typescript
/**
 * Set the ClientId dimension for per-client metric visibility
 *
 * V2.4.2 Bugfix: Call this ONCE before emitting multiple metrics
 */
export function setClientIdDimension(clientId: string): void {
  metrics.addDimension('ClientId', clientId);
}

/**
 * Track reconciliation case breakdown
 *
 * V2.4.2: Removed dimension setting - caller must call setClientIdDimension() first
 */
export function emitReconciliationCaseMetric(
  caseType: '1' | '2' | '3' | '4' | 'D',
  count: number,
): void {
  metrics.addMetric(`ReconciliationCase${caseType}`, MetricUnit.Count, count);
}
```

**Usage in handler.ts**:

```typescript
// Set ClientId dimension ONCE
setClientIdDimension(jobPayload.client_id);

// Emit 5 metrics (all use same ClientId dimension)
emitReconciliationCaseMetric('1', result.cases.missingInDB.length);
emitReconciliationCaseMetric('2', result.cases.recordingNotDownloaded.length);
emitReconciliationCaseMetric('3', result.cases.transcriptionNotStarted.length);
emitReconciliationCaseMetric('4', result.cases.transcriptionNotCompleted.length);
emitReconciliationCaseMetric('D', result.cases.orphanedRecords.length);
```

**Why It Matters**: Enables Dashboard Row 9 per-client drill-down

#### RingCentral 429 Handling (ringcentral-client.ts:99-130)

**Purpose**: Extend SQS visibility timeout on rate limit, retry with random delay

```typescript
// Handle 429 rate limit
if (response.status === 429) {
  logger.warn('RingCentral rate limit exceeded', { telephonySessionId });

  // Extend SQS visibility timeout by 2-10 minutes (random)
  const delaySeconds = Math.floor(Math.random() * 481) + 120; // 120-600 seconds

  await config.sqsClient.send(new ChangeMessageVisibilityCommand({
    QueueUrl: config.queueUrl,
    ReceiptHandle: config.receiptHandle,
    VisibilityTimeout: delaySeconds,
  }));

  logger.info('Extended SQS visibility timeout', { delaySeconds });

  // Throw error to trigger SQS retry
  throw new Error('RingCentral rate limit exceeded (429). Extended visibility timeout for retry.');
}
```

**Why It Matters**: V2.5.0 wide-spread retry prevents thundering herd retry storms

***

## Testing & Validation

### Test Reconciliation Locally

**Purpose**: Validate reconciliation logic without deploying

```bash
# Run unit tests
cd lambda/reconciliation-worker
npm test

# Run specific test suite
npm test -- reconciliation-cases.test.ts

# Run with coverage
npm run test:coverage
```

**Key Test Files**:

- `/lambda/reconciliation-worker/__tests__/unit/reconciliation-cases.test.ts` - State detection logic
- `/lambda/reconciliation-worker/__tests__/unit/reconciliation-engine.test.ts` - Action generation
- `/lambda/reconciliation-worker/__tests__/unit/webhook-builder.test.ts` - Synthetic webhook creation

### Validate State Detection Logic

**Purpose**: Ensure 4-state model correctly classifies records

```typescript
// Example test from reconciliation-cases.test.ts
describe('detectReconciliationCases', () => {
  it('should detect State 1: Missing in DB', () => {
    const ringCentralCalls = [
      { sessionId: 'call-1', recording: { id: 'rec-1' } },
    ];
    const dynamoDBStates = [];

    const result = detectReconciliationCases(ringCentralCalls, dynamoDBStates);

    expect(result.missingInDB).toHaveLength(1);
    expect(result.missingInDB[0].sessionId).toBe('call-1');
  });

  it('should detect State 4: Transcription stuck >24h', () => {
    const ringCentralCalls = [];
    const dynamoDBStates = [
      {
        callSessionId: 'call-1',
        recordingDownloaded: true,
        transcriptionStarted: true,
        transcriptionCompleted: false,
        lastUpdated: new Date(Date.now() - 25 * 60 * 60 * 1000).toISOString(), // 25 hours ago
      },
    ];

    const result = detectReconciliationCases(ringCentralCalls, dynamoDBStates);

    expect(result.transcriptionNotCompleted).toHaveLength(1);
  });
});
```

### Load Testing

**Purpose**: Validate reconciliation at scale (production-like volume)

**Caution**: Only run in dev/test environments to avoid production impact

```bash
# Step 1: Create test data (50 clients, 100 calls each = 5000 calls)
aws lambda invoke \
  --function-name TestDataGenerator \
  --payload '{"clientCount":50,"callsPerClient":100}' \
  /tmp/response.json

# Step 2: Trigger reconciliation
aws lambda invoke \
  --function-name ReconciliationOrchestrator \
  --payload '{"mode":"execute"}' \
  /tmp/response.json

# Step 3: Monitor metrics
# - Watch Dashboard Row 9 for case counts
# - Check Row 12 for rate limit capacity
# - Verify no DLQ messages

# Step 4: Measure completion time
# Expected: ~30 minutes for 50 clients (with concurrency=2, random delays)

# Step 5: Cleanup test data
aws lambda invoke \
  --function-name TestDataCleanup \
  --payload '{"prefix":"test-"}' \
  /tmp/response.json
```

**Success Criteria**:

- All 5000 calls processed without DLQ messages
- Rate limit capacity stays >3 throughout
- No Lambda errors or throttles
- Completion time \<1 hour

***

## Architecture Decisions

### Why SQS Fan-Out? (V2.5.0)

**Problem (V2.4)**:

- Orchestrator made 37+ RingCentral API calls per client in single invocation
- Each reconciliation action (State 1-4) triggered immediate API call
- 10 clients × 37 calls = 370 API calls in \<60 seconds
- RingCentral rate limit: \~10 calls/minute → 429 errors

**Solution (V2.5.0)**:

- Orchestrator makes 1 API call per client (fetch call logs)
- Generates 1 SQS message per action (e.g., State 1 = inject\_webhook)
- Worker processes 1 message at a time (concurrency=2)
- Random delays (2-15 min) spread load over time

**Result**:

- 97% API call reduction (370 → 10 calls for 10 clients)
- Wide-spread retry prevents thundering herd
- Independent retry (each action can retry without reprocessing all)
- Better observability (per-action metrics, logs)

**Trade-off**:

- Longer completion time (10-30 min vs \<5 min)
- More complex architecture (orchestrator + worker + SQS)
- Acceptable: Reconciliation is background job, not time-critical

### Why 4-State Model? (V2.4.1)

**Problem (Legacy A/B/C)**:

- Case B: "Stuck in progress" - didn't distinguish recording vs transcription
- Case C: "Failed transcription" - overlapped with Case B
- Hard to troubleshoot: Is recording missing? Or transcription stuck?

**Solution (State 1-4)**:

- **State 1**: Missing in DB (was Case A) - unchanged
- **State 2**: Recording not downloaded (new) - network/S3 issue
- **State 3**: Transcription not started (new) - Transcribe quota/permissions
- **State 4**: Transcription stuck (merged B+C) - job failure/timeout
- **Case D**: Orphaned (unchanged) - RingCentral deleted

**Result**:

- Granular troubleshooting (State 2 = download issue, State 3 = Transcribe issue)
- Better dashboard visibility (5 widgets instead of 4)
- Targeted fixes (State 2 = retry download, State 3 = start transcription)
- Easier metrics (one metric per state)

**Migration**:

- Code updated: V2.4.1 (November 28, 2025)
- Backward compatible: Old metrics still work, new metrics added
- Dashboard updated: Row 9 shows 5 states (1-4 + D)

### Why 6-Hour Window? (V2.4)

**Problem (7-Day Window)**:

- Reconciliation runs every 3 hours
- 7-day window = 56 overlapping scans (7 days ÷ 3 hours)
- Each scan fetches same calls 56 times (99.98% duplication!)
- RingCentral API cost: 56× necessary calls

**Solution (6-Hour Window)**:

- 6-hour window = 2 overlapping scans (6 hours ÷ 3 hours)
- Each call fetched 2 times (50% duplication for safety margin)
- 2× processing SLA headroom (normal: 3 hours, window: 6 hours)

**Result**:

- 50% API call reduction vs 7-day window
- Faster issue detection (catches problems within 6 hours, not 7 days)
- Lower RingCentral API cost
- Still safe: 2× headroom for delayed processing

**Trade-off**:

- Misses calls >6 hours delayed (rare: \<0.1% based on metrics)
- Can increase window to 12/24 hours for historical recovery
- Acceptable: Focus on recent calls, not historical data

### Why Concurrency = 2? (V2.5.0)

**Problem (No Reserved Concurrency)**:

- Lambda auto-scales based on SQS queue depth
- 100 messages → 100 concurrent Lambdas
- Each Lambda makes RingCentral API calls
- 100 Lambdas × 5 calls = 500 calls/minute → 429 storm

**Solution (Reserved Concurrency = 2)**:

- Maximum 2 Lambdas processing simultaneously
- Each Lambda processes 1 message at a time (SQS batch size = 1)
- 2 Lambdas × 5 calls = 10 calls/minute (well under rate limit)
- SQS queue builds up, but processes slowly and safely

**Result**:

- Prevents rate limit storms
- Stable API call rate (\~10 calls/minute)
- Queue eventually drains (2 workers × 15 min timeout = 8 messages/hour)
- Better than concurrency=1 (too slow) or concurrency=10 (rate limit risk)

**Trade-off**:

- Slower processing (100 messages = 50 hours vs 1 hour)
- Acceptable: Reconciliation is background job, correctness > speed

***

## Related Documentation

- **Troubleshooting**: [reconciliation/troubleshooting.md](reconciliation/troubleshooting.md) - Detailed DLQ recovery, IAM issues, secrets management
- **Dashboard**: [../01-MONITORING/README.md](../01-MONITORING/README.md) - Complete dashboard reference (all 13 rows)
- **Alerts**: [../02-ALERTING/README.md](../02-ALERTING/README.md) - Reconciliation-related alarms (DLQ, errors, rate limits)
- **TranscribeProcessor**: [./lambda/transcribe-processor.md](lambda/transcribe-processor.md) - Primary data path, webhook processing
- **On-Call Guide**: [../04-INCIDENT-RESPONSE/on-call-guide.md](../04-INCIDENT-RESPONSE/on-call-guide.md) - Incident #4: Reconciliation failures

***

## Changelog

**V2.5.3 (2025-11-30)**: Auto-enable reconciliation schedule in dev/test

- Schedule state: Auto-enabled (dev/test), Disabled (prod)
- Eliminates manual enable step after deployment
- Updated operational procedures for schedule management

**V2.5.0 (2025-11-28)**: SQS fan-out architecture

- 97% API call reduction (37+ calls → 1 per client)
- Wide-spread retry (2-15 min random delays)
- Independent action retry via SQS
- Reserved concurrency = 2 (prevents rate limit storms)

**V2.4.1 (2025-11-28)**: 4-state reconciliation model

- New states: State 2 (recording not downloaded), State 3 (transcription not started)
- Merged Case B + C → State 4 (transcription stuck)
- Per-client metrics with ClientId dimension
- Dashboard Row 9 added (5 widgets for states 1-4 + D)

**V2.4 (2025-11-28)**: Schedule optimization

- Frequency: Daily → Every 3 hours
- Window: 7 days → 6 hours
- RingCentral API pagination support

**V2.3 (2025-11-27)**: Metrics namespace change

- Namespace: `CallAnalytics` → `CallAnalytics/RingCentral`
- Added `ApiCallsPerMinute` metric

**V2.2.5 (2025-11-24)**: Secrets Manager IAM fix

- IAM policy supports 3 secret naming patterns
- Uses `secret_name` from DynamoDB config

***

**Document Version**: 1.0
**Last Updated**: 2025-11-30
**Author**: Engineering Team
**Review Frequency**: Quarterly