# Walkthrough: Following an Adjustment File End-to-End

A step-by-step guide for manually tracing an adjustment file from upload through payment allocation. Follow along in the UI, AWS Console, and Airflow to see every stage of processing.

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

---

## Quick Reference: AWS Console Links

### Prod

| Resource | Account | Link |
|----------|---------|------|
| File Upload Step Function | `437795906767` | [prod-abacus-file-upload-workflow](https://437795906767-d4xbig47.us-east-1.console.aws.amazon.com/states/home?region=us-east-1#/statemachines/view/arn:aws:states:us-east-1:437795906767:stateMachine:prod-abacus-file-upload-workflow) |
| Outbox Process Lambda | `437795906767` | [prod-lambda-abacus-outbox-process](https://437795906767-d4xbig47.us-east-1.console.aws.amazon.com/lambda/home?region=us-east-1#/functions/prod-lambda-abacus-outbox-process?subtab=triggers&tab=monitoring) |
| Adjustment File Ingest Step Function | `437795906767` | [prod-adjustment-file-ingest-workflow](https://437795906767-d4xbig47.us-east-1.console.aws.amazon.com/states/home?region=us-east-1#/statemachines/view/arn:aws:states:us-east-1:437795906767:stateMachine:prod-adjustment-file-ingest-workflow) |
| Airflow: adjustment_file_upload DAG | | [adjustment_file_upload](https://19a7ad7c-ad6d-47a0-8e89-389ecf29a83b-vpce.c7.us-east-1.airflow.amazonaws.com/dags/adjustment_file_upload/grid) |
| Airflow: adjustment_file_import DAG | | [adjustment_file_import](https://19a7ad7c-ad6d-47a0-8e89-389ecf29a83b-vpce.c7.us-east-1.airflow.amazonaws.com/dags/adjustment_file_import/grid) |
| Abacus UI | | [abacus.theorchard.com](https://abacus.theorchard.com) |

### QA

| Resource | Account | Link |
|----------|---------|------|
| File Upload Step Function | `437795906767` | [qa-abacus-file-upload-workflow](https://437795906767-d4xbig47.us-east-1.console.aws.amazon.com/states/home?region=us-east-1#/statemachines/view/arn%3Aaws%3Astates%3Aus-east-1%3A437795906767%3AstateMachine%3Aqa-abacus-file-upload-workflow?type=standard) |
| Outbox Process Lambda | `989790945997` | [qa-lambda-abacus-outbox-process](https://989790945997-rqi2kw2r.us-east-1.console.aws.amazon.com/lambda/home?region=us-east-1#/functions/qa-lambda-abacus-outbox-process?tab=code) |
| Adjustment File Ingest Step Function | `989790945997` | [qa-adjustment-file-ingest-workflow](https://989790945997-rqi2kw2r.us-east-1.console.aws.amazon.com/states/home?region=us-east-1#/statemachines/view/arn%3Aaws%3Astates%3Aus-east-1%3A989790945997%3AstateMachine%3Aqa-adjustment-file-ingest-workflow?type=standard) |
| Airflow (all DAGs) | | [QA Airflow Home](https://9d5f0024-841b-4381-b39d-a0ea684b8a83-vpce.c74.us-east-1.airflow.amazonaws.com/home) |
| Airflow: adjustment_file_upload DAG | | [adjustment_file_upload](https://9d5f0024-841b-4381-b39d-a0ea684b8a83-vpce.c74.us-east-1.airflow.amazonaws.com/dags/adjustment_file_upload/grid) |
| Airflow: adjustment_file_import DAG | | [adjustment_file_import](https://9d5f0024-841b-4381-b39d-a0ea684b8a83-vpce.c74.us-east-1.airflow.amazonaws.com/dags/adjustment_file_import/grid) |
| Abacus UI | | [abacus.qaorch.com](https://abacus.qaorch.com) |

---

## The Journey of an Adjustment File

### Overview

```mermaid
flowchart LR
    A["1. Upload\n(UI)"] --> B["2. File Upload\nStep Function"]
    B --> C["3. Outbox\nProcessor"]
    C --> D["4. Ingest\nStep Function"]
    D --> E["5. Validation\nDAG"]
    E --> F["6. Review\n(UI)"]
    F --> G["7. Import\nDAG"]
    G --> H["8. Approve\n(UI)"]
    H --> I["9. Apply\n(Lambda)"]
    I --> J["10. Payment\nAllocation"]
```

---

### Step 1: Upload the File (UI)

**Where:** Abacus UI -> Adjustments page -> "+ Add Adjustments" button

1. Navigate to the **Adjustments** page
   - Prod: `https://abacus.theorchard.com/adjustments`
   - QA: `https://abacus.qaorch.com/adjustments`

2. Click **"+ Add Adjustments"** in the top right

3. The upload modal appears. You can download the template first by clicking **"Download Template"**

4. Drag and drop or browse for your `.xlsx` file

5. The modal shows a loading spinner — the frontend is:
   - Calling `useAbacusInitiateFileUpload('adjustments')` to create a `file_upload` record and get a signed S3 URL
   - Uploading the file directly to S3 via the signed URL
   - The `file_upload` record transitions: `init` -> `scanning` -> `complete`

**What to check in the DB at this point:**

```sql
-- Find your file upload record (use the filename or recent timestamp)
SELECT file_upload_id, upload_type, upload_status, original_filename, created_at
FROM file_upload
WHERE upload_type = 'adjustments'
ORDER BY created_at DESC
LIMIT 5;
```

You should see `upload_status = 'complete'` once the upload finishes.

---

### Step 2: Watch the File Upload Step Function

**Where:**
- Prod: [prod-abacus-file-upload-workflow](https://437795906767-d4xbig47.us-east-1.console.aws.amazon.com/states/home?region=us-east-1#/statemachines/view/arn:aws:states:us-east-1:437795906767:stateMachine:prod-abacus-file-upload-workflow)
- QA: [qa-abacus-file-upload-workflow](https://437795906767-d4xbig47.us-east-1.console.aws.amazon.com/states/home?region=us-east-1#/statemachines/view/arn%3Aaws%3Astates%3Aus-east-1%3A437795906767%3AstateMachine%3Aqa-abacus-file-upload-workflow?type=standard)

When the file upload completes, a separate file-upload step function runs. This handles generic file upload lifecycle (AV scanning, status updates).

1. Open the Step Functions Console link above
2. Click the **"Executions"** tab
3. Look for a **Running** or recently **Succeeded** execution matching your upload time
4. Click into the execution to see the state machine graph and which step it's on

**What to look for:**
- The execution input will contain your `file_upload_id`
- The execution should succeed, indicating the file passed AV scanning and is marked `complete`

---

### Step 3: Watch the Outbox Processor

**Where:**
- Prod: [prod-lambda-abacus-outbox-process](https://437795906767-d4xbig47.us-east-1.console.aws.amazon.com/lambda/home?region=us-east-1#/functions/prod-lambda-abacus-outbox-process?subtab=triggers&tab=monitoring)
- QA: [qa-lambda-abacus-outbox-process](https://989790945997-rqi2kw2r.us-east-1.console.aws.amazon.com/lambda/home?region=us-east-1#/functions/qa-lambda-abacus-outbox-process?tab=code)

When the file upload reaches `complete` status, ows-royalties writes an event to the `abacus_outbox` table. The outbox processor Lambda picks this up and publishes it to EventBridge.

**How it gets triggered (two paths):**
- **Kafka CDC (primary):** A Kafka CDC connector watches the `abacus_outbox` table. When a row is inserted, it triggers the Lambda via the `cdc.royaltyAccounting.abacusOutbox` topic
- **Scheduled backup:** An EventBridge schedule periodically invokes the Lambda to catch any events that CDC missed

1. Open the Lambda Console link above
2. Click the **"Monitor"** tab to see recent invocations
3. Click **"View CloudWatch logs"** to see the actual log output

**What to look for:**
- A log line showing it read your event from the outbox
- The event detail-type should be `file_upload.completed`
- The event should be published to EventBridge with source `abacus.outbox`

**What to check in the DB:**

```sql
-- Find the outbox event for your file upload
SELECT abacus_outbox_id, event_type, event_source, status, created_at, published_at
FROM abacus_outbox
WHERE event_detail LIKE '%file_upload_id_here%'
ORDER BY created_at DESC;
```

`status` should be `published` once the outbox processor runs.

---

### Step 4: Watch the Adjustment File Ingest Step Function

**Where:**
- Prod: [prod-adjustment-file-ingest-workflow](https://437795906767-d4xbig47.us-east-1.console.aws.amazon.com/states/home?region=us-east-1#/statemachines/view/arn:aws:states:us-east-1:437795906767:stateMachine:prod-adjustment-file-ingest-workflow)
- QA: [qa-adjustment-file-ingest-workflow](https://989790945997-rqi2kw2r.us-east-1.console.aws.amazon.com/states/home?region=us-east-1#/statemachines/view/arn%3Aaws%3Astates%3Aus-east-1%3A989790945997%3AstateMachine%3Aqa-adjustment-file-ingest-workflow?type=standard)

EventBridge routes the `file_upload.completed` event (with `upload_type: adjustments`) to this step function.

1. Open the Step Functions Console link above
2. Click **"Executions"** tab
3. Find the execution that started shortly after your outbox event published
4. Click into it to see the execution detail

**What to look for:**
- **Input:** Should contain your `file_upload_id` in `detail.metadata.target_id`
- **InitializeBatch state:** Should show **Succeeded** (green)
  - This Lambda (`adjustment-file-initialize`) creates the `statement_period_adjustment_file` record, `abacus_state` entries, and `abacus_event` entries
- **In Prod:** The execution ends after `InitializeBatch` succeeds — this is the complete flow
- **In QA:** The execution will show `PrepareFile` as **Failed** (red) after `InitializeBatch` succeeds — **this is expected**. The `PrepareFile` state calls a deprecated Lambda that doesn't work. The important thing is that `InitializeBatch` passed.

**What to check in the DB after this step:**

```sql
-- Find the statement_period_adjustment_file created by the initialize Lambda
SELECT
    spaf.statement_period_adjustment_file_id,
    spaf.file_name,
    spaf.source_file_upload_id,
    spaf.valid_file_location,
    spaf.created_at
FROM statement_period_adjustment_file spaf
WHERE spaf.source_file_upload_id = {file_upload_id};

-- Check the abacus_state entries were created
SELECT action, state, created_at
FROM abacus_state
WHERE target_id = {statement_period_adjustment_file_id}
  AND target_type = 'statement_period_adjustment_file'
ORDER BY action;
```

At this point you should see abacus_state entries for `upload_file`, `validate_file`, and `import_file`, all in `init` state.

---

### Step 5: Watch the Validation DAG

**Where:**
- Prod: [adjustment_file_upload DAG](https://19a7ad7c-ad6d-47a0-8e89-389ecf29a83b-vpce.c7.us-east-1.airflow.amazonaws.com/dags/adjustment_file_upload/grid)
- QA: [adjustment_file_upload DAG](https://9d5f0024-841b-4381-b39d-a0ea684b8a83-vpce.c74.us-east-1.airflow.amazonaws.com/dags/adjustment_file_upload/grid)

The `adjustment-file-initialize` Lambda (Step 4) creates an abacus event, which the `ows-abacus-event` service picks up and uses to trigger this Airflow DAG via the MWAA CLI. Despite the DAG's name (`adjustment_file_upload`), it runs *after* the file is already uploaded — it handles AV scanning, format validation, and content validation.

1. Open the Airflow link above
2. You'll see the **Grid view** showing recent DAG runs
3. Find the run triggered around the time of your upload (the DAG has `schedule_interval=None` so it's only triggered on demand)
4. Click on the run to see individual task status

**DAG tasks in order:**

| Task | What it does | What to look for |
|------|-------------|------------------|
| `notify_started` | Logs the start | Should be green (success) |
| `invoke_lambda_av_scan` | Virus scans the file | Green = clean file. Red = infected (file quarantined) |
| `validate_format` | Checks .xlsx structure | Green = valid format. Red = not a valid Excel file |
| `invoke_adjustment_file_validation_lambda` | Triggers the validation Lambda | Green = Lambda invoked. Check Lambda logs for details |
| `adjustment_file_validation_check_result` | Polls validation status (30s intervals, 15min timeout) | This is a **sensor** — it keeps checking until validation completes |
| `check_adjustment_file_error_report` | Checks if there were validation errors | Green = check complete (may still have errors in the file) |
| `notify_success` | Final success notification | Green = entire validation pipeline succeeded |
| `notify_failure` | Failure notification (trigger_rule: one_failed) | Only runs if an upstream task failed |

**What to check in the DB after validation:**

```sql
SELECT
    spaf.statement_period_adjustment_file_id,
    spaf.valid_row_count,
    spaf.invalid_row_count,
    spaf.total_file_amount_multicurrency,
    spaf.total_rounded_amount_multicurrency,
    spaf.error_type,
    spaf.invalid_file_location
FROM statement_period_adjustment_file spaf
WHERE spaf.statement_period_adjustment_file_id = {id};
```

- `valid_row_count > 0` and `error_type IS NULL` = clean file
- `invalid_row_count > 0` = some rows had errors (see `invalid_file_location` for the error report)
- `error_type = 'content_error'` or `'format_error'` = validation failed

---

### Step 6: Review Validation Results (UI)

**Where:**
- Prod: [abacus.theorchard.com/adjustments](https://abacus.theorchard.com/adjustments) (upload modal)
- QA: [abacus.qaorch.com/adjustments](https://abacus.qaorch.com/adjustments) (upload modal)

After validation completes, the UI (which has been polling every 2 seconds) will show one of:

- **Success screen:** Shows total amount, valid row count, and a comparison of raw vs. rounded amounts
  - You'll see an **"IMPORT DATA"** button
- **Content error screen:** Shows valid vs. invalid row counts
  - You can **download the error report** (Excel file with a "Validation Errors" column added)
  - You can click **"RE-UPLOAD"** to try again with a corrected file
- **Format error screen:** The file structure was invalid
- **Failed screen:** Something went wrong in the pipeline

**If the file is valid, click "IMPORT DATA" to proceed to the next step.**

---

### Step 7: Watch the Import DAG

**Where:**
- Prod: [adjustment_file_import DAG](https://19a7ad7c-ad6d-47a0-8e89-389ecf29a83b-vpce.c7.us-east-1.airflow.amazonaws.com/dags/adjustment_file_import/grid)
- QA: [adjustment_file_import DAG](https://9d5f0024-841b-4381-b39d-a0ea684b8a83-vpce.c74.us-east-1.airflow.amazonaws.com/dags/adjustment_file_import/grid)

Clicking "IMPORT DATA" triggers the `adjustment_file_import` DAG.

1. Open the Airflow link above
2. Find the new DAG run triggered by your import action

**DAG tasks:**

| Task | What it does | What to look for |
|------|-------------|------------------|
| `notify_started` | Logs the start | Should be green |
| `invoke_adjustment_file_import_lambda` | Triggers the import Lambda | This does the heavy lifting — inserts `worksheet_adjustment` and `worksheet_adjustment_detail` records |
| `notify_success` | Success notification | Green = import complete |
| `notify_failure` | Failure notification (trigger_rule: one_failed) | Only runs if import failed |

**What's happening during import:**
- The Lambda downloads the validated file from S3
- Reads it with pandas
- Inserts `worksheet_adjustment` records in batches
- Groups detail rows and inserts `worksheet_adjustment_detail` records
- Sets `apply_to_flowthrough_payment` on each record

**What to check in the DB after import:**

```sql
-- Verify worksheet adjustments were created
SELECT
    COUNT(*) AS total_adjustments,
    SUM(adjustment_amount) AS total_amount,
    SUM(CASE WHEN apply_to_flowthrough_payment = 1 THEN 1 ELSE 0 END) AS flowthrough_count
FROM worksheet_adjustment
WHERE statement_period_adjustment_file_id = {id};

-- Check the abacus_state was updated
SELECT action, state
FROM abacus_state
WHERE target_id = {id}
  AND target_type = 'statement_period_adjustment_file';
```

After successful import, the `upload_file`, `validate_file`, and `import_file` states should all be `complete`.

**Back in the UI:** The modal will close and you'll be taken back to the adjustments list. Your file should appear with status **"Not Approved"**.

---

### Step 8: Approve the Batch (UI)

**Where:** Adjustments page -> click on the batch row -> batch detail page
- Prod: `https://abacus.theorchard.com/adjustments/{batchId}`
- QA: `https://abacus.qaorch.com/adjustments/{batchId}`

1. Click on the batch row in the adjustments list to open the **batch detail page** (`/adjustments/{batchId}`)
2. You'll see all individual adjustments listed — account ID, contract ID, amount, currency, adjustment type, and the **"Apply To Flowthrough Payment"** column (Yes/No/None)
3. You can filter by account, contract, or flowthrough payment flag
4. Click the **"Approve"** button

> **Note:** Approval may be restricted to certain users if the `ABACUS_MANUAL_ADJUSTMENTS_APPROVED_USERS` feature flag is enabled.

**What to check in the DB after approval:**

```sql
SELECT action, state
FROM abacus_state
WHERE target_id = {id}
  AND target_type = 'statement_period_adjustment_file';
-- approve_file should now be 'complete'
```

The batch status in the UI should now show **"Approved"**.

---

### Step 9: Apply Adjustments (UI + Lambda)

**Where:** Batch detail page -> "Apply" button
- Prod: `https://abacus.theorchard.com/adjustments/{batchId}`
- QA: `https://abacus.qaorch.com/adjustments/{batchId}`

1. On the approved batch detail page, click **"Apply"**
2. This fires an `apply_pending_adjustments` event
3. The `adjustments-apply` Lambda processes the adjustments

**What the Lambda does:**
- Gets the current statement period
- Gets exchange rates for currency conversion
- For each pending adjustment:
  - Converts from `adjustment_currency_code` to `payee_currency_code`
  - Creates `ledger_adjustment_applied` record
- Non-flowthrough adjustments -> `ledger_account_contract` entries
- Flowthrough adjustments (`apply_to_flowthrough_payment = true`) -> `ledger_contract_flowthrough` entries
- Rounding differences -> `ledger_deposit` entries

**What to check in the DB after apply:**

```sql
-- Verify ledger entries were created
SELECT
    COUNT(*) AS total_applied,
    SUM(laa.adjustment_amount) AS total_adjustment_amount,
    SUM(CASE
	    WHEN laa.apply_to_flowthrough_payment = 1 THEN 1
		ELSE 0
	END) AS flowthrough_count
FROM ledger_adjustment_applied AS laa
	INNER JOIN worksheet_adjustment AS wa
	ON laa.worksheet_adjustment_id = wa.worksheet_adjustment_id
WHERE wa.statement_period_adjustment_file_id = {id};

-- Check all states are complete
SELECT action, state
FROM abacus_state
WHERE target_id = {id}
  AND target_type = 'statement_period_adjustment_file';
-- All five actions (upload_file, validate_file, import_file, approve_file, apply_file) should be 'complete'
```

The batch status in the UI should now show **"Applied"**.

---

### Step 10: Payment Allocation (Automatic)

**Where:** Automatic — triggered by EventBridge, not a UI action. Check the Lambda for invocations:
- Prod: search `prod-lambda-abacus-payment-allocation` in [Prod Lambda Console](https://437795906767-d4xbig47.us-east-1.console.aws.amazon.com/lambda/home?region=us-east-1#/functions)
- QA: search `qa-lambda-abacus-payment-allocation` in [QA Lambda Console](https://989790945997-rqi2kw2r.us-east-1.console.aws.amazon.com/lambda/home?region=us-east-1#/functions)

Payment allocation is triggered by an EventBridge event when the **close balance** process completes for a payment entity. You don't trigger this manually; it fires when the `close_balance.completed` event is emitted.

**What triggers it:**
- The accounting team runs the close balance process for a statement period
- When a `statement_period_payment_entity` has its `close_balance` abacus_state set to `complete`, the `close_balance.completed` event fires on EventBridge
- EventBridge invokes the `payment-allocation` Lambda

**What the Lambda does:**
1. Looks up the `statement_period_payment_entity`
2. Validates `close_balance` is `complete`
3. Finds all flowthrough adjustments (`apply_to_flowthrough_payment = 1`) that haven't been linked to a payment allocation yet
4. Groups them by `(contract_id, account_payee_id, currency_code)`
5. Creates a `payment_allocation` record for each group
6. Creates `payment_allocation_ledger_adjustment` links for each individual adjustment

**What to check in the DB after allocation:**

```sql
-- Verify payment allocations were created
SELECT
    pa.payment_allocation_id,
    pa.contract_id,
    pa.amount_to_payment,
    pa.currency_code,
    pa.payment_status,
    pa.created_at
FROM payment_allocation pa
WHERE pa.statement_period_id = {statement_period_id}
ORDER BY pa.created_at DESC;

-- Verify all flowthrough adjustments are now linked
SELECT COUNT(*) AS unlinked_count
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
  );
-- Should return 0
```

---

## Complete DB Trace: One Query to See Everything

After a file has been fully processed through payment allocation, run this to see the full pipeline trace for a single file:

```sql
SELECT 'Step 1: File Upload' AS stage,
       fu.file_upload_id AS id,
       fu.original_filename AS detail,
       fu.upload_status AS status
FROM file_upload fu
WHERE fu.file_upload_id = {file_upload_id}

UNION ALL

SELECT 'Step 2: Adjustment File' AS stage,
       spaf.statement_period_adjustment_file_id AS id,
       spaf.file_name AS detail,
       CONCAT(spaf.valid_row_count, ' valid / ', COALESCE(spaf.invalid_row_count, 0), ' invalid') AS status
FROM statement_period_adjustment_file spaf
WHERE spaf.source_file_upload_id = {file_upload_id}

UNION ALL

SELECT CONCAT('Step 3: State - ', as2.action) AS stage,
       as2.abacus_state_id AS id,
       as2.action AS detail,
       as2.state AS status
FROM abacus_state as2
JOIN statement_period_adjustment_file spaf
    ON spaf.statement_period_adjustment_file_id = as2.target_id
WHERE spaf.source_file_upload_id = {file_upload_id}
  AND as2.target_type = 'statement_period_adjustment_file'

UNION ALL

SELECT 'Step 4: Worksheet Adjustments' AS stage,
       COUNT(*) AS id,
       CONCAT(SUM(wa.adjustment_amount), ' total amount') AS detail,
       CONCAT(SUM(CASE WHEN wa.apply_to_flowthrough_payment = 1 THEN 1 ELSE 0 END), ' flowthrough') AS status
FROM worksheet_adjustment wa
JOIN statement_period_adjustment_file spaf
    ON spaf.statement_period_adjustment_file_id = wa.statement_period_adjustment_file_id
WHERE spaf.source_file_upload_id = {file_upload_id}

UNION ALL

SELECT 'Step 5: Ledger Applied' AS stage,
       COUNT(*) AS id,
       CONCAT(SUM(laa.adjustment_amount), ' source / ', SUM(laa.payee_amount), ' payee') AS detail,
       CONCAT(SUM(CASE WHEN laa.apply_to_flowthrough_payment = 1 THEN 1 ELSE 0 END), ' flowthrough') AS status
FROM ledger_adjustment_applied laa
JOIN statement_period_adjustment_file spaf
    ON spaf.statement_period_adjustment_file_id = laa.statement_period_adjustment_file_id
WHERE spaf.source_file_upload_id = {file_upload_id}

UNION ALL

SELECT 'Step 6: Payment Allocations' AS stage,
       pa_summary.allocation_count AS id,
       CONCAT(pa_summary.total_allocated, ' allocated') AS detail,
       CONCAT(pa_summary.adjustments_linked, ' adjustments linked') AS status
FROM (
    SELECT
        COUNT(DISTINCT pa.payment_allocation_id) AS allocation_count,
        (SELECT COALESCE(SUM(pa2.amount_to_payment), 0)
         FROM payment_allocation pa2
         WHERE pa2.payment_allocation_id IN (
             SELECT DISTINCT pala2.payment_allocation_id
             FROM payment_allocation_ledger_adjustment pala2
             JOIN ledger_adjustment_applied laa2
                 ON laa2.ledger_adjustment_applied_id = pala2.ledger_adjustment_applied_id
             JOIN statement_period_adjustment_file spaf2
                 ON spaf2.statement_period_adjustment_file_id = laa2.statement_period_adjustment_file_id
             WHERE spaf2.source_file_upload_id = {file_upload_id}
         )) AS total_allocated,
        COUNT(pala.payment_allocation_ledger_adjustment_id) AS adjustments_linked
    FROM ledger_adjustment_applied laa
    JOIN statement_period_adjustment_file spaf
        ON spaf.statement_period_adjustment_file_id = laa.statement_period_adjustment_file_id
    LEFT JOIN payment_allocation_ledger_adjustment pala
        ON pala.ledger_adjustment_applied_id = laa.ledger_adjustment_applied_id
    LEFT JOIN payment_allocation pa
        ON pa.payment_allocation_id = pala.payment_allocation_id
    WHERE spaf.source_file_upload_id = {file_upload_id}
) pa_summary;
```

Replace `{file_upload_id}` with your file upload ID from Step 1 to trace the entire journey.

---

## Timing Expectations

| Stage | Typical Duration | What to watch if slow |
|-------|-----------------|----------------------|
| Upload (UI -> S3) | Seconds | File size, network speed |
| File Upload Step Function | < 1 minute | AV scan Lambda |
| Outbox Processor | < 30 seconds | Check if Kafka CDC is healthy, or wait for scheduled backup |
| Ingest Step Function | < 30 seconds | InitializeBatch Lambda (30s timeout) |
| Validation DAG | 1-10 minutes | File size, Snowflake connectivity, sensor poll interval (30s) |
| Import DAG | 1-15 minutes | File size, DB write throughput, deadlock retries |
| Apply Lambda | 1-5 minutes | Number of adjustments, exchange rate lookups |
| Payment Allocation | 1-10 minutes | Number of contracts, batch size configuration |

---

## Troubleshooting Quick Hits

| Symptom | Where to look |
|---------|--------------|
| Upload modal spinner never stops | Check `file_upload.upload_status` in DB — stuck in `init` or `scanning`? |
| No Step Function execution appears | Check outbox: is `abacus_outbox.status = 'published'`? Check outbox-process Lambda invocations |
| Ingest Step Function shows "Failed" (QA) | Expected if `InitializeBatch` passed — the `PrepareFile` state is deprecated |
| Validation DAG sensor times out | Validation Lambda is still running or failed silently — check Lambda logs in Datadog |
| "Content Error" in UI | Download the error report from the UI — it shows per-row validation errors |
| Import DAG fails with deadlock | Concurrent import on same period — retry, or check Lambda logs for `OperationalError 1213` |
| Batch stuck on "Not Approved" | Someone needs to approve it — check if user has permission (feature flag gated) |
| Apply fails silently | Check if any payment entity has `close_balance = complete` — apply skips if so |
| No payment allocations after close | Check `payment-allocation` Lambda logs, verify `close_balance.completed` event fired |

For detailed debugging, see [debugging.md](debugging.md).