# Runbook: Flowthrough Debugging

SQL queries, log analysis techniques, and troubleshooting procedures for diagnosing issues in the flowthrough adjustment processing system.

**Last Updated:** 2026-05-13

---

## Table of Contents

1. [Database Queries: Finding Data](#1-database-queries-finding-data)
2. [Database Queries: Status and State](#2-database-queries-status-and-state)
3. [Database Queries: Flowthrough Configuration](#3-database-queries-flowthrough-configuration)
4. [Database Queries: Ledger and Payment Allocation](#4-database-queries-ledger-and-payment-allocation)
5. [Log Analysis](#5-log-analysis)
6. [Common Failure Scenarios](#6-common-failure-scenarios)
7. [Debugging Decision Tree](#7-debugging-decision-tree)

---

## 1. Database Queries: Finding Data

All queries run against the `royalty_accounting` database.

### Find an Adjustment File by ID

```sql
SELECT
    spaf.statement_period_adjustment_file_id,
    spaf.statement_period_id,
    spaf.source_file_upload_id,
    spaf.file_name,
    spaf.batch_type,
    spaf.valid_file_location,
    spaf.invalid_file_location,
    spaf.valid_row_count,
    spaf.invalid_row_count,
    spaf.total_file_amount_multicurrency,
    spaf.total_rounded_amount_multicurrency,
    spaf.md5sum,
    spaf.error_type,
    spaf.created_at,
    spaf.created_by,
    spaf.deleted_at
FROM statement_period_adjustment_file spaf
WHERE spaf.statement_period_adjustment_file_id = {id};
```

### Find Adjustment Files for a Statement Period

```sql
SELECT
    spaf.statement_period_adjustment_file_id,
    spaf.file_name,
    spaf.batch_type,
    spaf.valid_row_count,
    spaf.invalid_row_count,
    spaf.total_file_amount_multicurrency,
    spaf.error_type,
    spaf.created_at,
    spaf.created_by,
    spaf.deleted_at
FROM statement_period_adjustment_file spaf
WHERE spaf.statement_period_id = {statement_period_id}
  AND spaf.deleted_at IS NULL
ORDER BY spaf.created_at DESC;
```

### Find a File Upload Record

```sql
SELECT
    fu.file_upload_id,
    fu.file_upload_config_id,
    fu.upload_type,
    fu.upload_status,
    fu.s3_bucket,
    fu.s3_key,
    fu.original_filename,
    fu.md5sum,
    fu.file_size_bytes,
    fu.created_at,
    fu.created_by
FROM file_upload fu
WHERE fu.file_upload_id = {id};
```

### Find File Upload by S3 Key

```sql
SELECT *
FROM file_upload fu
WHERE fu.s3_key LIKE '%{partial_key}%'
ORDER BY fu.created_at DESC
LIMIT 10;
```

### Find Worksheet Adjustments for a File

```sql
SELECT
    wa.worksheet_adjustment_id,
    wa.account_id,
    wa.contract_id,
    wa.adjustment_amount,
    wa.adjustment_currency_code,
    wa.apply_to_flowthrough_payment,
    wa.reference_adjustment_type_id,
    wa.note,
    wa.created_by
FROM worksheet_adjustment wa
WHERE wa.statement_period_adjustment_file_id = {id}
ORDER BY wa.worksheet_adjustment_id
LIMIT 100;
```

### Count Adjustments per File

```sql
SELECT
    wa.statement_period_adjustment_file_id,
    COUNT(*) AS adjustment_count,
    SUM(wa.adjustment_amount) AS total_amount,
    COUNT(DISTINCT wa.account_id) AS unique_accounts,
    COUNT(DISTINCT wa.contract_id) AS unique_contracts,
    SUM(CASE WHEN wa.apply_to_flowthrough_payment = 1 THEN 1 ELSE 0 END) AS flowthrough_count,
    SUM(CASE WHEN wa.apply_to_flowthrough_payment = 0 THEN 1 ELSE 0 END) AS non_flowthrough_count
FROM worksheet_adjustment wa
WHERE wa.statement_period_adjustment_file_id = {id}
GROUP BY wa.statement_period_adjustment_file_id;
```

### Find Worksheet Adjustment Details

```sql
SELECT
    wad.worksheet_adjustment_detail_id,
    wad.worksheet_adjustment_id,
    wad.account_id,
    wad.contract_id,
    wad.currency_code,
    wad.amount,
    wad.upc,
    wad.distribution_type,
    wad.apply_to_flowthrough_payment
FROM worksheet_adjustment_detail wad
WHERE wad.statement_period_adjustment_file_id = {id}
ORDER BY wad.worksheet_adjustment_detail_id
LIMIT 100;
```

### Find Stuck Outbox Events

Events stuck in `pending` indicate the outbox processor (Kafka CDC or scheduled backup) is not processing.

```sql
SELECT
    abacus_outbox_id,
    event_type,
    event_source,
    status,
    created_at,
    TIMESTAMPDIFF(MINUTE, created_at, NOW()) AS age_minutes
FROM abacus_outbox
WHERE status = 'pending'
  AND created_at < DATE_SUB(NOW(), INTERVAL 5 MINUTE)
ORDER BY created_at ASC
LIMIT 20;
-- Non-empty result = outbox processor is not running. Check Kafka CDC health and the scheduled backup.
```

### Find Stuck File Uploads

File uploads stuck in `init` or `scanning` suggest the file-upload step function or AV scan Lambda has an issue.

```sql
SELECT
    file_upload_id,
    original_filename,
    upload_status,
    upload_type,
    created_at,
    TIMESTAMPDIFF(MINUTE, created_at, NOW()) AS age_minutes
FROM file_upload
WHERE upload_status IN ('init', 'scanning')
  AND created_at < DATE_SUB(NOW(), INTERVAL 10 MINUTE)
ORDER BY created_at ASC;
-- Non-empty result = file upload pipeline is stuck. Check file-upload step function.
```

### Find Duplicate Adjustment Files for Same Upload

The initialize Lambda could theoretically create duplicates on retry.

```sql
SELECT source_file_upload_id, COUNT(*) AS cnt
FROM statement_period_adjustment_file
WHERE deleted_at IS NULL
  AND source_file_upload_id IS NOT NULL
GROUP BY source_file_upload_id
HAVING COUNT(*) > 1;
-- Should return 0 rows.
```

### Find Missing Exchange Rates for a File's Currencies

Run this before applying adjustments to detect missing exchange rates. The `exchange_rate` table is directional — each row maps `from_currency_code` -> `to_currency_code` at a given `rate` for a statement period.

```sql
SELECT DISTINCT
    wa.adjustment_currency_code AS from_currency,
    ap.payee_currency_code AS to_currency
FROM worksheet_adjustment wa
JOIN statement_period_adjustment_file spaf
    ON spaf.statement_period_adjustment_file_id = wa.statement_period_adjustment_file_id
JOIN account_payee ap
    ON ap.account_id = wa.account_id
WHERE spaf.statement_period_adjustment_file_id = {id}
  AND wa.adjustment_currency_code != ap.payee_currency_code
  AND NOT EXISTS (
      SELECT 1 FROM exchange_rate er
      WHERE er.statement_period_id = spaf.statement_period_id
        AND er.from_currency_code = wa.adjustment_currency_code
        AND er.to_currency_code = ap.payee_currency_code
  );
-- Non-empty result = apply will fail for these currency pairs. Populate exchange rates first.
```

If you don't know the payee currencies, check which `from_currency_code` values are present:

```sql
SELECT DISTINCT er.from_currency_code, er.to_currency_code
FROM exchange_rate er
WHERE er.statement_period_id = {statement_period_id}
ORDER BY er.from_currency_code, er.to_currency_code;
```

### Find Auto-Generated Batch Criteria

```sql
SELECT
    spabc.statement_period_adjustment_batch_criteria_id,
    spabc.statement_period_adjustment_file_id,
    spabc.batch_criteria
FROM statement_period_adjustment_batch_criteria spabc
WHERE spabc.statement_period_adjustment_file_id = {id};
```

### Find Who Uploaded a File

```sql
-- Get the identity ID from the created_by field
SELECT
    spaf.statement_period_adjustment_file_id,
    spaf.file_name,
    spaf.created_by AS identity_id,
    spaf.created_at
FROM statement_period_adjustment_file spaf
WHERE spaf.statement_period_adjustment_file_id = {id};

-- Then look up the user via ows-users:
-- curl https://{env}-ows-users.theorchard.io/users/identity/{identity_id}/application/frontend-royalties/profiles
```

---

## 2. Database Queries: Status and State

### Get Full Adjustment File Status (Derived from abacus_state)

The adjustment file status is **not stored directly** - it's derived from the `abacus_state` table. This query replicates the logic:

```sql
SELECT
    spaf.statement_period_adjustment_file_id,
    spaf.file_name,
    upload_state.state AS upload_state,
    validate_state.state AS validate_state,
    import_state.state AS import_state,
    approve_state.state AS approve_state,
    apply_state.state AS apply_state,
    CASE
        WHEN upload_state.state = 'error' OR validate_state.state = 'error' OR import_state.state = 'error'
             OR approve_state.state = 'error' OR apply_state.state = 'error' THEN 'error'
        WHEN upload_state.state = 'running' OR validate_state.state = 'running' OR import_state.state = 'running'
             OR approve_state.state = 'running' OR apply_state.state = 'running' THEN 'processing'
        WHEN apply_state.state = 'complete' THEN 'applied'
        WHEN approve_state.state = 'complete' AND (apply_state.state IS NULL OR apply_state.state != 'complete') THEN 'approved'
        WHEN upload_state.state = 'complete' AND validate_state.state = 'complete' AND import_state.state = 'complete'
             AND (approve_state.state IS NULL OR approve_state.state != 'complete') THEN 'not_approved'
        WHEN upload_state.state = 'complete' AND (validate_state.state IS NULL OR validate_state.state = 'init')
             THEN 'processing'
        ELSE 'unknown'
    END AS derived_status
FROM statement_period_adjustment_file spaf
LEFT JOIN abacus_state upload_state
    ON upload_state.target_id = spaf.statement_period_adjustment_file_id
    AND upload_state.target_type = 'statement_period_adjustment_file'
    AND upload_state.action = 'upload_file'
LEFT JOIN abacus_state validate_state
    ON validate_state.target_id = spaf.statement_period_adjustment_file_id
    AND validate_state.target_type = 'statement_period_adjustment_file'
    AND validate_state.action = 'validate_file'
LEFT JOIN abacus_state import_state
    ON import_state.target_id = spaf.statement_period_adjustment_file_id
    AND import_state.target_type = 'statement_period_adjustment_file'
    AND import_state.action = 'import_file'
LEFT JOIN abacus_state approve_state
    ON approve_state.target_id = spaf.statement_period_adjustment_file_id
    AND approve_state.target_type = 'statement_period_adjustment_file'
    AND approve_state.action = 'approve_file'
LEFT JOIN abacus_state apply_state
    ON apply_state.target_id = spaf.statement_period_adjustment_file_id
    AND apply_state.target_type = 'statement_period_adjustment_file'
    AND apply_state.action = 'apply_file'
WHERE spaf.statement_period_adjustment_file_id = {id};
```

### Get All States for an Adjustment File

```sql
SELECT
    as2.abacus_state_id,
    as2.target_id,
    as2.target_type,
    as2.action,
    as2.state,
    as2.created_at,
    as2.updated_at
FROM abacus_state as2
WHERE as2.target_id = {statement_period_adjustment_file_id}
  AND as2.target_type = 'statement_period_adjustment_file'
ORDER BY as2.action;
```

### Get All Events for an Adjustment File

```sql
SELECT
    ae.abacus_event_id,
    ae.target_id,
    ae.target_type,
    ae.event_type,
    ae.event_data,
    ae.created_at
FROM abacus_event ae
WHERE ae.target_id = {statement_period_adjustment_file_id}
  AND ae.target_type = 'statement_period_adjustment_file'
ORDER BY ae.created_at DESC;
```

### Find Files Stuck in Processing

```sql
SELECT
    spaf.statement_period_adjustment_file_id,
    spaf.file_name,
    spaf.created_at,
    as2.action,
    as2.state,
    as2.updated_at,
    TIMESTAMPDIFF(MINUTE, as2.updated_at, NOW()) AS minutes_since_update
FROM statement_period_adjustment_file spaf
JOIN abacus_state as2
    ON as2.target_id = spaf.statement_period_adjustment_file_id
    AND as2.target_type = 'statement_period_adjustment_file'
WHERE as2.state = 'running'
  AND as2.updated_at < DATE_SUB(NOW(), INTERVAL 30 MINUTE)
  AND spaf.deleted_at IS NULL;
```

### Find Files with Errors

```sql
SELECT
    spaf.statement_period_adjustment_file_id,
    spaf.file_name,
    spaf.error_type,
    spaf.invalid_row_count,
    spaf.invalid_file_location,
    as2.action AS failed_action,
    as2.updated_at AS error_time
FROM statement_period_adjustment_file spaf
JOIN abacus_state as2
    ON as2.target_id = spaf.statement_period_adjustment_file_id
    AND as2.target_type = 'statement_period_adjustment_file'
    AND as2.state = 'error'
WHERE spaf.deleted_at IS NULL
  AND spaf.statement_period_id = {statement_period_id}
ORDER BY as2.updated_at DESC;
```

### Get Current Statement Period

```sql
SELECT
    sp.statement_period_id,
    sp.status,
    sp.start_date,
    sp.end_date
FROM statement_period sp
WHERE sp.status = 'current';
```

### Check Close Balance Status for Payment Entities

```sql
SELECT
    sppe.statement_period_payment_entity_id,
    sppe.statement_period_id,
    sppe.reference_payment_entity_id,
    as2.state AS close_balance_state,
    as2.updated_at
FROM statement_period_payment_entity sppe
LEFT JOIN abacus_state as2
    ON as2.target_id = sppe.statement_period_payment_entity_id
    AND as2.target_type = 'close_balance'
WHERE sppe.statement_period_id = {statement_period_id};
```

---

## 3. Database Queries: Flowthrough Configuration

### Get Flowthrough Config for a Contract

```sql
SELECT
    cf.contract_flowthrough_id,
    cf.contract_id,
    c.contract_name,
    cf.flowthrough_rate,
    cf.flowthrough_status,
    cf.previous_flowthrough_status,
    cf.has_automatic_shutoff,
    cf.recoupment_cap,
    rfc.flowthrough_calculation_name,
    rfc.flowthrough_calculation,
    cf.calculation_comment,
    cf.status_last_modified_by,
    cf.status_last_modified,
    cf.deleted_at
FROM contract_flowthrough cf
JOIN contract c ON c.contract_id = cf.contract_id
LEFT JOIN reference_flowthrough_calculation rfc
    ON rfc.reference_flowthrough_calculation_id = cf.reference_flowthrough_calculation_id
WHERE cf.contract_id = {contract_id};
```

### List All Active Flowthrough Contracts

```sql
SELECT
    cf.contract_flowthrough_id,
    cf.contract_id,
    c.contract_name,
    c.account_id,
    cf.flowthrough_rate,
    cf.flowthrough_status,
    rfc.flowthrough_calculation_name
FROM contract_flowthrough cf
JOIN contract c ON c.contract_id = cf.contract_id
LEFT JOIN reference_flowthrough_calculation rfc
    ON rfc.reference_flowthrough_calculation_id = cf.reference_flowthrough_calculation_id
WHERE cf.flowthrough_status = 'active'
  AND cf.deleted_at IS NULL
ORDER BY cf.contract_id;
```

### Find Paythrough Contracts (Legacy - to be deprecated)

```sql
SELECT
    c.contract_id,
    c.contract_name,
    c.is_paythrough_contract,
    c.account_id
FROM contract c
WHERE c.is_paythrough_contract = 1;
```

### Compare Paythrough vs Flowthrough Contracts

Use this to verify migration coverage:

```sql
SELECT
    c.contract_id,
    c.contract_name,
    c.is_paythrough_contract,
    cf.contract_flowthrough_id,
    cf.flowthrough_status,
    cf.flowthrough_rate,
    CASE
        WHEN c.is_paythrough_contract = 1 AND cf.contract_flowthrough_id IS NOT NULL THEN 'migrated'
        WHEN c.is_paythrough_contract = 1 AND cf.contract_flowthrough_id IS NULL THEN 'paythrough_only'
        WHEN c.is_paythrough_contract = 0 AND cf.contract_flowthrough_id IS NOT NULL THEN 'flowthrough_only'
        ELSE 'neither'
    END AS migration_status
FROM contract c
LEFT JOIN contract_flowthrough cf
    ON cf.contract_id = c.contract_id
    AND cf.deleted_at IS NULL
WHERE c.is_paythrough_contract = 1
   OR cf.contract_flowthrough_id IS NOT NULL
ORDER BY c.contract_id;
```

### List All Flowthrough Calculation Methods

```sql
SELECT
    rfc.reference_flowthrough_calculation_id,
    rfc.flowthrough_calculation_name,
    rfc.flowthrough_calculation,
    rfc.flowthrough_calculation_example,
    rfc.flowthrough_calculation_example_summary
FROM reference_flowthrough_calculation rfc;
```

---

## 4. Database Queries: Ledger and Payment Allocation

### Find Applied Adjustments for a File

```sql
SELECT
    laa.ledger_adjustment_applied_id,
    laa.statement_period_adjustment_file_id,
    laa.account_id,
    laa.contract_id,
    laa.adjustment_amount,
    laa.adjustment_currency_code,
    laa.payee_amount,
    laa.payee_currency_code,
    laa.apply_to_flowthrough_payment,
    laa.created_by
FROM ledger_adjustment_applied laa
WHERE laa.statement_period_adjustment_file_id = {id}
ORDER BY laa.ledger_adjustment_applied_id;
```

### Summarize Applied Adjustments by File

```sql
SELECT
    laa.statement_period_adjustment_file_id,
    COUNT(*) AS total_applied,
    SUM(laa.adjustment_amount) AS total_adjustment_amount,
    SUM(laa.payee_amount) AS total_payee_amount,
    COUNT(DISTINCT laa.adjustment_currency_code) AS currencies,
    SUM(CASE WHEN laa.apply_to_flowthrough_payment = 1 THEN 1 ELSE 0 END) AS flowthrough_count,
    SUM(CASE WHEN laa.apply_to_flowthrough_payment = 1 THEN laa.payee_amount ELSE 0 END) AS flowthrough_amount,
    SUM(CASE WHEN laa.apply_to_flowthrough_payment = 0 THEN 1 ELSE 0 END) AS non_flowthrough_count
FROM ledger_adjustment_applied laa
WHERE laa.statement_period_adjustment_file_id = {id}
GROUP BY laa.statement_period_adjustment_file_id;
```

### Find Flowthrough Ledger Entries

```sql
SELECT
    lcf.ledger_contract_flowthrough_id,
    lcf.account_id,
    lcf.contract_id,
    lcf.statement_period_id,
    lcf.amount,
    lcf.currency_code,
    lcf.source
FROM ledger_contract_flowthrough lcf
WHERE lcf.statement_period_id = {statement_period_id}
ORDER BY lcf.contract_id;
```

### Find Payment Allocations for a Period

```sql
SELECT
    pa.payment_allocation_id,
    pa.contract_id,
    pa.payee_type,
    pa.payee_id,
    pa.statement_period_id,
    pa.payment_allocation_type,
    pa.amount_to_payment,
    pa.payment_status,
    pa.amount_to_ledger,
    pa.ledger_status,
    pa.currency_code,
    pa.description,
    pa.created_by,
    pa.created_at
FROM payment_allocation pa
WHERE pa.statement_period_id = {statement_period_id}
ORDER BY pa.contract_id;
```

### Find Adjustments Linked to a Payment Allocation

```sql
SELECT
    pa.payment_allocation_id,
    pa.contract_id,
    pa.amount_to_payment,
    pa.currency_code,
    pala.ledger_adjustment_applied_id,
    laa.adjustment_amount,
    laa.adjustment_currency_code,
    laa.account_id
FROM payment_allocation pa
JOIN payment_allocation_ledger_adjustment pala
    ON pala.payment_allocation_id = pa.payment_allocation_id
JOIN ledger_adjustment_applied laa
    ON laa.ledger_adjustment_applied_id = pala.ledger_adjustment_applied_id
WHERE pa.payment_allocation_id = {id}
ORDER BY pala.ledger_adjustment_applied_id;
```

### Find Unlinked Flowthrough Adjustments (Not Yet Allocated)

```sql
SELECT
    laa.ledger_adjustment_applied_id,
    laa.account_id,
    laa.contract_id,
    laa.adjustment_amount,
    laa.adjustment_currency_code,
    laa.statement_period_id
FROM ledger_adjustment_applied laa
WHERE laa.statement_period_id = {statement_period_id}
  AND laa.apply_to_flowthrough_payment = 1
  AND NOT EXISTS (
      SELECT 1
      FROM payment_allocation_ledger_adjustment pala
      WHERE pala.ledger_adjustment_applied_id = laa.ledger_adjustment_applied_id
  )
ORDER BY laa.contract_id;
```

### Count Unlinked Adjustments by Contract

```sql
SELECT
    laa.contract_id,
    COUNT(*) AS unlinked_count,
    SUM(laa.adjustment_amount) AS unlinked_amount
FROM ledger_adjustment_applied laa
WHERE laa.statement_period_id = {statement_period_id}
  AND laa.apply_to_flowthrough_payment = 1
  AND NOT EXISTS (
      SELECT 1
      FROM payment_allocation_ledger_adjustment pala
      WHERE pala.ledger_adjustment_applied_id = laa.ledger_adjustment_applied_id
  )
GROUP BY laa.contract_id
ORDER BY unlinked_count DESC;
```

### Verify Payment Allocation Totals Match

```sql
-- Compare payment_allocation amounts vs. sum of linked adjustments
SELECT
    pa.payment_allocation_id,
    pa.contract_id,
    pa.amount_to_payment AS allocation_amount,
    pa.currency_code,
    SUM(laa.payee_amount) AS sum_linked_adjustments,
    pa.amount_to_payment - SUM(laa.payee_amount) AS difference
FROM payment_allocation pa
JOIN payment_allocation_ledger_adjustment pala
    ON pala.payment_allocation_id = pa.payment_allocation_id
JOIN ledger_adjustment_applied laa
    ON laa.ledger_adjustment_applied_id = pala.ledger_adjustment_applied_id
WHERE pa.statement_period_id = {statement_period_id}
GROUP BY pa.payment_allocation_id, pa.contract_id, pa.amount_to_payment, pa.currency_code
HAVING ABS(pa.amount_to_payment - SUM(laa.payee_amount)) > 0.01;
```

---

## 5. Log Analysis

### Datadog Log Queries

**Find all logs for a specific adjustment file:**
```
service:lambda-abacus-* "statement_period_adjustment_file_id" "{id}"
```

**Find validation errors:**
```
service:lambda-abacus-adjustment-file-validation status:error
```

**Find import deadlocks:**
```
service:lambda-abacus-adjustment-file-import "deadlock" OR "OperationalError" OR "1213"
```

**Find payment allocation runs:**
```
service:lambda-abacus-payment-allocation "statement_period_payment_entity_id" "{id}"
```

**Find initialization failures:**
```
service:lambda-abacus-adjustment-file-initialize status:error
```

**Find all processing for a correlation ID:**
```
"correlation_id" "{correlation_id}"
```

### CloudWatch Insights Queries

**Step Function executions in last 24 hours:**
```
fields @timestamp, @message
| filter @logStream like /adjustment-file-ingest/
| sort @timestamp desc
| limit 100
```

**Failed executions:**
```
fields @timestamp, @message
| filter @message like /FAILED/ or @message like /Error/
| sort @timestamp desc
| limit 50
```

---

## 6. Common Failure Scenarios

### Scenario: "File Upload Stuck in Processing"

**Symptoms:** Frontend shows spinner indefinitely, status remains "processing"

**Diagnosis:**
1. Check `abacus_state`:
   ```sql
   SELECT * FROM abacus_state
   WHERE target_id = {spaf_id}
     AND target_type = 'statement_period_adjustment_file';
   ```
2. Look for a state stuck in `running` for > 30 minutes
3. Check if the corresponding Lambda timed out (Datadog/CloudWatch)
4. Check if the Airflow DAG task is stuck

**Resolution:**
- If Lambda timed out: Fix the root cause (large file, slow Snowflake, DB issues), then reset the state to `init` and re-trigger
- If Airflow sensor timed out: Check `adjustment_file_validation_check_result` (15-min timeout) - the validation Lambda may still be running or may have failed silently

### Scenario: "Validation Returns Content Errors"

**Symptoms:** File shows error_type = 'content_error', invalid_row_count > 0

**Diagnosis:**
1. Download the error report:
   ```sql
   SELECT invalid_file_location FROM statement_period_adjustment_file
   WHERE statement_period_adjustment_file_id = {id};
   ```
   ```bash
   aws s3 cp {invalid_file_location} ./error_report.xlsx
   ```
2. Open the error report - the last column ("Validation Errors") shows per-row errors
3. Common content errors:
   - Invalid account_id or contract_id (not found in Snowflake)
   - Invalid currency code
   - Invalid adjustment type
   - Amount not parseable as decimal
   - Invalid activity month/year or statement month/year

**Resolution:** Fix the source file and re-upload

### Scenario: "Import Lambda Deadlock"

**Symptoms:** Import Lambda errors with OperationalError 1213

**Diagnosis:**
1. Check Datadog logs:
   ```
   service:lambda-abacus-adjustment-file-import "deadlock" OR "1213"
   ```
2. The import Lambda retries 3 times on deadlock

**Resolution:**
- If all 3 retries failed: Check for concurrent imports on the same statement period
- Reduce `MYSQL_BATCH_SIZE` if files are very large
- Re-trigger the import Lambda

### Scenario: "Adjustments Not Applied After Approval"

**Symptoms:** Batch shows "Approved" but ledger entries are missing

**Diagnosis:**
1. Check abacus_state for the apply action:
   ```sql
   SELECT * FROM abacus_state
   WHERE target_id = {spaf_id}
     AND target_type = 'statement_period_adjustment_file'
     AND action = 'apply_file';
   ```
2. Check if the `adjustments_apply` Lambda was triggered (look for the abacus_event)
3. Check if any payment entity has `close_balance = complete` (Lambda skips if so)

**Resolution:**
- If apply_file state is missing: Trigger `apply_pending_adjustments` event
- If close_balance is complete: Adjustments cannot be applied after close - coordinate with finance

### Scenario: "Payment Allocations Not Created After Balance Close"

**Symptoms:** `close_balance.completed` fired but no `payment_allocation` records exist

**Diagnosis:**
1. Check if the EventBridge event was received:
   ```
   service:lambda-abacus-payment-allocation "close_balance.completed"
   ```
2. Check for `BalancesNotClosedError`:
   ```sql
   SELECT * FROM abacus_state
   WHERE target_id = {sppe_id}
     AND target_type = 'close_balance';
   ```
3. Check if there are any unlinked flowthrough adjustments:
   ```sql
   SELECT COUNT(*) FROM ledger_adjustment_applied
   WHERE statement_period_id = {sp_id}
     AND apply_to_flowthrough_payment = 1
     AND NOT EXISTS (
       SELECT 1 FROM payment_allocation_ledger_adjustment pala
       WHERE pala.ledger_adjustment_applied_id = ledger_adjustment_applied.ledger_adjustment_applied_id
     );
   ```

**Resolution:**
- If no events found: Check EventBridge rule is active and DLQ for dropped events
- If `BalancesNotClosedError`: The close_balance abacus_state is not `complete` - investigate
- If no unlinked adjustments: Everything was already allocated (idempotent)
- Manually invoke the Lambda with the correct `statement_period_payment_entity_id`

### Scenario: "Auto-Generated Batch Shows 'Failed to Generate'"

**Symptoms:** Status shows `failed_to_generate` in the UI

**Diagnosis:**
1. Check the batch criteria:
   ```sql
   SELECT spabc.batch_criteria
   FROM statement_period_adjustment_batch_criteria spabc
   JOIN statement_period_adjustment_file spaf
     ON spaf.statement_period_adjustment_file_id = spabc.statement_period_adjustment_file_id
   WHERE spaf.statement_period_adjustment_file_id = {id};
   ```
2. Check `generate-flowthrough-adjustments` Lambda logs
3. Check Snowflake connectivity and view availability

**Resolution:**
- If Snowflake error: Check Snowflake credentials and `VW_ABACUS_AUTOMATED_FLOWTHROUGH` view
- If no data returned: Check if the view has data for the given payment entities and schedules
- User can retry from the UI (click retry icon)

### Scenario: "Currency Conversion Error in Apply"

**Symptoms:** `adjustments_apply` Lambda logs show exchange rate not found

**Diagnosis:**

The `exchange_rate` table is directional — each row maps `from_currency_code` -> `to_currency_code` at a `rate` for a statement period.

1. Check which exchange rate pairs exist for the statement period:
   ```sql
   SELECT from_currency_code, to_currency_code, rate
   FROM exchange_rate
   WHERE statement_period_id = {sp_id}
   ORDER BY from_currency_code, to_currency_code;
   ```
2. Identify which currency pairs are needed but missing:
   ```sql
   SELECT DISTINCT
       wa.adjustment_currency_code AS from_currency,
       ap.payee_currency_code AS to_currency
   FROM worksheet_adjustment wa
   JOIN account_payee ap ON ap.account_id = wa.account_id
   WHERE wa.statement_period_adjustment_file_id = {id}
     AND wa.adjustment_currency_code != ap.payee_currency_code
     AND NOT EXISTS (
         SELECT 1 FROM exchange_rate er
         WHERE er.statement_period_id = {sp_id}
           AND er.from_currency_code = wa.adjustment_currency_code
           AND er.to_currency_code = ap.payee_currency_code
     );
   ```

**Resolution:** Ensure exchange rates are populated for all currency pairs in the current statement period before applying adjustments.

---

## 7. Debugging Decision Tree

```mermaid
flowchart TD
    START["User reports adjustment issue"] --> Q1{"File upload\nfailed?"}
    START --> Q2{"Validation\nfailed?"}
    START --> Q3{"Import\nfailed?"}
    START --> Q4{"Approval\nblocked?"}
    START --> Q5{"Apply\nfailed?"}
    START --> Q6{"Payment allocation\nmissing?"}
    START --> Q7{"Auto-generation\nfailed?"}
    START --> Q8{"Data\ndiscrepancy?"}

    Q1 -->|quarantined| A1["Virus detected\nAsk user to re-upload"]
    Q1 -->|error| A2["Check Lambda logs\nfor file_upload_complete"]
    Q1 -->|init/scanning| A3["Upload didn't complete\nCheck S3 + file-upload SF"]
    Q1 -->|format| A4["Check .xlsx validity\nnot .xls, no merged cells"]

    Q2 -->|format_error| B1["File structure wrong\nCheck template"]
    Q2 -->|content_error| B2["Download error report\nfrom invalid_file_location"]
    Q2 -->|row_count_error| B3["Too many/few rows"]
    Q2 -->|stuck| B4["Check abacus_state\nfor validate_file action"]

    Q3 --> C1["Check Datadog for\ndeadlock errors 1213"]
    Q3 --> C2["Check DB connectivity"]
    Q3 --> C3["Compare worksheet_adjustment\ncount vs valid_row_count"]

    Q4 --> D1["Check ABACUS_MANUAL_ADJUSTMENTS\n_APPROVED_USERS flag"]
    Q4 --> D2["Check user permissions"]

    Q5 --> E1["Check if close_balance\nalready complete"]
    Q5 --> E2["Check exchange_rate\navailability"]
    Q5 --> E3["Check abacus_state\nfor apply_file action"]

    Q6 --> F1["Verify close_balance.completed\nevent fired"]
    Q6 --> F2["Check payment-allocation\nLambda logs"]
    Q6 --> F3["Check for unlinked\nflowthrough adjustments"]

    Q7 --> G1["Check batch_criteria\nin DB"]
    Q7 --> G2["Check Snowflake\nconnectivity + view"]
    Q7 --> G3["User can retry\nfrom UI"]

    Q8 --> H1["Compare worksheet vs\nledger counts"]
    Q8 --> H2["Check currency conversion\nadjustment → payee amount"]
    Q8 --> H3["Verify payment_allocation\ntotals match linked sums"]
    Q8 --> H4["Check for soft-deleted\nrecords"]
```