# Runbook: Flowthrough Data Verification

Prerequisite checks, data integrity queries, and end-to-end verification procedures for the flowthrough adjustment system.

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

---

## Table of Contents

1. [Pre-Upload Verification](#1-pre-upload-verification)
2. [Post-Upload Verification](#2-post-upload-verification)
3. [Post-Import Verification](#3-post-import-verification)
4. [Post-Apply Verification](#4-post-apply-verification)
5. [Post-Allocation Verification](#5-post-allocation-verification)
6. [End-to-End Reconciliation](#6-end-to-end-reconciliation)
7. [Data Integrity Checks](#7-data-integrity-checks)
8. [Period-Level Health Checks](#8-period-level-health-checks)

---

## 1. Pre-Upload Verification

Run these checks before uploading an adjustment file to ensure prerequisites are met.

### Verify Statement Period is Current

Adjustments can only be uploaded to the 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';
-- If no rows returned: No active period. Contact accounting.
-- If status != 'current': Period is not ready for adjustments.
```

### Verify Exchange Rates are Populated

Required for the `adjustments_apply` step. Not needed for upload/validation/import.

```sql
SELECT
    er.from_currency_code,
    er.to_currency_code,
    er.rate,
    er.statement_period_id
FROM exchange_rate er
WHERE er.statement_period_id = (
    SELECT sp.statement_period_id
    FROM statement_period sp
    WHERE sp.status = 'current'
)
ORDER BY er.from_currency_code, er.to_currency_code;
-- Verify all (from_currency, to_currency) pairs needed by the adjustment file are present.
```

### Verify Flowthrough Feature Flags are Enabled

Check Split.io dashboard for:
- `abacus_flowthrough_automation` (required for new pipeline)
- **[DEPRECATED 2026-06 — flag torn down per ACC-10470]** ~~`ABACUS_APPLY_FLOWTHROUGH_PAYMENT` (required for flowthrough fields)~~
- `abacus_auto_generate_adjustments_flowthrough` (required for auto-generation)

### Verify S3 Bucket is Accessible

```bash
aws s3 ls s3://{env}-abacus-adjustments/ --max-items 1
# Should return at least the templates/ prefix
```

### Verify No Duplicate File

```sql
SELECT
    spaf.statement_period_adjustment_file_id,
    spaf.file_name,
    spaf.md5sum,
    spaf.created_at
FROM statement_period_adjustment_file spaf
WHERE spaf.statement_period_id = {statement_period_id}
  AND spaf.md5sum = '{md5sum_of_new_file}'
  AND spaf.deleted_at IS NULL;
-- If rows returned: Same file was already uploaded.
```

---

## 2. Post-Upload Verification

Run after a file has been uploaded to verify it was received correctly.

### Verify File Upload Record

```sql
SELECT
    fu.file_upload_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
FROM file_upload fu
WHERE fu.file_upload_id = {file_upload_id};
-- upload_status should be 'complete'
-- s3_key should point to the uploaded file
```

### Verify Adjustment File Record Created

```sql
SELECT
    spaf.statement_period_adjustment_file_id,
    spaf.source_file_upload_id,
    spaf.file_name,
    spaf.batch_type,
    spaf.valid_file_location,
    spaf.created_at
FROM statement_period_adjustment_file spaf
WHERE spaf.source_file_upload_id = {file_upload_id}
  AND spaf.deleted_at IS NULL;
-- Should return exactly 1 row
-- valid_file_location should have S3 path
```

### Verify Abacus States Initialized

```sql
SELECT
    as2.action,
    as2.state,
    as2.created_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;
-- Expected states after upload:
-- upload_file: init or running
-- validate_file: init (if FF enabled)
-- import_file: init (if FF enabled)
```

### Verify Step Function Was Triggered

```sql
-- Check abacus_outbox for the event
SELECT
    ao.abacus_outbox_id,
    ao.event_type,
    ao.event_source,
    ao.status,
    ao.created_at,
    ao.published_at
FROM abacus_outbox ao
WHERE ao.event_detail LIKE '%{file_upload_id}%'
ORDER BY ao.created_at DESC
LIMIT 5;
-- status should be 'published'
```

### Verify File is Downloadable from S3

```bash
# Get the S3 location
# Then verify the file exists and is non-empty
aws s3 ls s3://{env}-abacus-adjustments/{s3_key}
# Should show file with expected size
```

---

## 3. Post-Import Verification

Run after validation and import have completed.

### Verify Validation Results

```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 should be > 0
-- invalid_row_count should be 0 for clean files
-- error_type should be NULL for valid files
```

### Verify Worksheet Adjustments Were Created

```sql
SELECT
    COUNT(*) AS total_adjustments,
    COUNT(DISTINCT wa.account_id) AS unique_accounts,
    COUNT(DISTINCT wa.contract_id) AS unique_contracts,
    SUM(wa.adjustment_amount) AS total_amount,
    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 OR wa.apply_to_flowthrough_payment IS NULL THEN 1 ELSE 0 END) AS non_flowthrough_count
FROM worksheet_adjustment wa
WHERE wa.statement_period_adjustment_file_id = {id};
-- total_adjustments should match valid_row_count (approximately)
```

### Verify Worksheet Adjustment Details Were Created

```sql
SELECT
    COUNT(*) AS total_details,
    COUNT(DISTINCT wad.worksheet_adjustment_id) AS unique_adjustments
FROM worksheet_adjustment_detail wad
WHERE wad.statement_period_adjustment_file_id = {id};
-- Details are grouped/aggregated, so count may differ from adjustment count
```

### Cross-Check Amounts

```sql
-- File-level amount
SELECT total_file_amount_multicurrency, total_rounded_amount_multicurrency
FROM statement_period_adjustment_file
WHERE statement_period_adjustment_file_id = {id};

-- Worksheet-level sum
SELECT SUM(adjustment_amount) AS worksheet_total
FROM worksheet_adjustment
WHERE statement_period_adjustment_file_id = {id};

-- These should be approximately equal (rounding may cause small differences)
```

### Verify Abacus State Updated

```sql
SELECT action, state, updated_at
FROM abacus_state
WHERE target_id = {id}
  AND target_type = 'statement_period_adjustment_file'
ORDER BY action;
-- After successful import:
-- upload_file: complete
-- validate_file: complete
-- import_file: complete
```

---

## 4. Post-Apply Verification

Run after adjustments have been approved and applied.

### Verify Ledger Entries Created

```sql
SELECT
    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 source_currencies,
    COUNT(DISTINCT laa.payee_currency_code) AS payee_currencies
FROM ledger_adjustment_applied laa
WHERE laa.statement_period_adjustment_file_id = {id};
-- total_applied should match worksheet_adjustment count
```

### Verify Flowthrough Ledger Entries

```sql
-- Count flowthrough entries
SELECT
    COUNT(*) AS flowthrough_entries,
    SUM(laa.payee_amount) AS flowthrough_amount
FROM ledger_adjustment_applied laa
WHERE laa.statement_period_adjustment_file_id = {id}
  AND laa.apply_to_flowthrough_payment = 1;

-- Verify ledger_contract_flowthrough was created
SELECT
    COUNT(*) AS lcf_entries,
    SUM(lcf.amount) AS lcf_total
FROM ledger_contract_flowthrough lcf
WHERE lcf.statement_period_id = (
    SELECT statement_period_id
    FROM statement_period_adjustment_file
    WHERE statement_period_adjustment_file_id = {id}
);
```

### Verify Non-Flowthrough Ledger Entries

```sql
-- Verify ledger_account_contract was created for non-flowthrough
SELECT
    COUNT(*) AS lac_entries,
    SUM(lac.amount) AS lac_total
FROM ledger_account_contract lac
WHERE lac.statement_period_id = (
    SELECT statement_period_id
    FROM statement_period_adjustment_file
    WHERE statement_period_adjustment_file_id = {id}
)
AND lac.source = 'adjustment';
```

### Verify Currency Conversion Accuracy

The `exchange_rate` table is directional: each row has `from_currency_code`, `to_currency_code`, and `rate`. The formula is `payee_amount = adjustment_amount * rate` where the rate maps from the adjustment currency to the payee currency.

```sql
SELECT
    laa.ledger_adjustment_applied_id,
    laa.adjustment_amount,
    laa.adjustment_currency_code,
    laa.payee_amount,
    laa.payee_currency_code,
    er.rate,
    ROUND(laa.adjustment_amount * er.rate, 2) AS expected_payee_amount,
    ABS(laa.payee_amount - ROUND(laa.adjustment_amount * er.rate, 2)) AS difference
FROM ledger_adjustment_applied laa
JOIN exchange_rate er
    ON er.from_currency_code = laa.adjustment_currency_code
    AND er.to_currency_code = laa.payee_currency_code
    AND er.statement_period_id = laa.statement_period_id
WHERE laa.statement_period_adjustment_file_id = {id}
  AND laa.adjustment_currency_code != laa.payee_currency_code
HAVING difference > 0.01
LIMIT 20;
-- Should return 0 rows (all conversions within 1 cent tolerance)
```

### Verify Abacus State After Apply

```sql
SELECT action, state, updated_at
FROM abacus_state
WHERE target_id = {id}
  AND target_type = 'statement_period_adjustment_file'
ORDER BY action;
-- After successful apply:
-- upload_file: complete
-- validate_file: complete (if FF enabled)
-- import_file: complete (if FF enabled)
-- approve_file: complete
-- apply_file: complete
```

---

## 5. Post-Allocation Verification

Run after payment allocation has completed.

### Verify Payment Allocations Created

```sql
SELECT
    COUNT(*) AS total_allocations,
    COUNT(DISTINCT pa.contract_id) AS unique_contracts,
    SUM(pa.amount_to_payment) AS total_allocated
FROM payment_allocation pa
WHERE pa.statement_period_id = {statement_period_id}
  AND pa.created_by LIKE '%payment-allocation%';
```

### Verify All Flowthrough Adjustments Are Linked

```sql
-- This should return 0 rows after successful allocation
SELECT
    laa.ledger_adjustment_applied_id,
    laa.contract_id,
    laa.adjustment_amount,
    laa.adjustment_currency_code
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
  );
-- 0 rows = all allocated. Non-zero = some adjustments missed.
```

### Verify Allocation Grouping

```sql
-- Each allocation should group adjustments by (contract, payee, currency)
SELECT
    pa.payment_allocation_id,
    pa.contract_id,
    pa.payee_id,
    pa.currency_code,
    pa.amount_to_payment,
    COUNT(pala.payment_allocation_ledger_adjustment_id) AS linked_adjustments,
    SUM(laa.payee_amount) AS sum_linked_amounts
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.payee_id, pa.currency_code, pa.amount_to_payment
ORDER BY pa.contract_id;
```

### Verify Allocation Amount Consistency

```sql
-- Allocation amount should equal sum of linked adjustment payee amounts
SELECT
    pa.payment_allocation_id,
    pa.amount_to_payment AS allocation_amount,
    SUM(laa.payee_amount) AS linked_total,
    pa.amount_to_payment - SUM(laa.payee_amount) AS discrepancy
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.amount_to_payment
HAVING ABS(pa.amount_to_payment - SUM(laa.payee_amount)) > 0.01;
-- Should return 0 rows
```

---

## 6. End-to-End Reconciliation

Complete reconciliation from file upload through payment allocation.

### Full Pipeline Reconciliation for a Single File

```sql
-- Step 1: File metadata
SELECT 'File' AS stage,
       spaf.statement_period_adjustment_file_id AS id,
       spaf.file_name AS detail,
       spaf.valid_row_count AS row_count,
       spaf.total_file_amount_multicurrency AS amount
FROM statement_period_adjustment_file spaf
WHERE spaf.statement_period_adjustment_file_id = {id}

UNION ALL

-- Step 2: Worksheet adjustments
SELECT 'Worksheet' AS stage,
       {id} AS id,
       CONCAT(COUNT(*), ' adjustments') AS detail,
       COUNT(*) AS row_count,
       SUM(wa.adjustment_amount) AS amount
FROM worksheet_adjustment wa
WHERE wa.statement_period_adjustment_file_id = {id}

UNION ALL

-- Step 3: Applied to ledger
SELECT 'Applied' AS stage,
       {id} AS id,
       CONCAT(COUNT(*), ' ledger entries') AS detail,
       COUNT(*) AS row_count,
       SUM(laa.adjustment_amount) AS amount
FROM ledger_adjustment_applied laa
WHERE laa.statement_period_adjustment_file_id = {id}

UNION ALL

-- Step 4: Flowthrough allocated
SELECT 'Allocated (FT)' AS stage,
       {id} AS id,
       CONCAT(COUNT(DISTINCT pa.payment_allocation_id), ' allocations') AS detail,
       COUNT(pala.payment_allocation_ledger_adjustment_id) AS row_count,
       SUM(laa.payee_amount) AS amount
FROM ledger_adjustment_applied laa
JOIN payment_allocation_ledger_adjustment pala
    ON pala.ledger_adjustment_applied_id = laa.ledger_adjustment_applied_id
JOIN payment_allocation pa
    ON pa.payment_allocation_id = pala.payment_allocation_id
WHERE laa.statement_period_adjustment_file_id = {id};
```

### Period-Level Reconciliation

> **Currency note:** Rows 1-4 report amounts in **source (adjustment) currency** — these are directly comparable to each other. Row 5 (Payment Allocations) and Row 6 (Applied FT in payee currency) report in **payee currency** — compare these two with each other, not with rows 1-4.

```sql
SELECT
    'Files (source currency)' AS category,
    COUNT(*) AS count,
    SUM(spaf.valid_row_count) AS rows,
    SUM(spaf.total_file_amount_multicurrency) AS amount
FROM statement_period_adjustment_file spaf
WHERE spaf.statement_period_id = {sp_id}
  AND spaf.deleted_at IS NULL

UNION ALL

SELECT
    'Worksheet Adjustments (source currency)' AS category,
    COUNT(*) AS count,
    COUNT(*) AS rows,
    SUM(wa.adjustment_amount) AS amount
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.statement_period_id = {sp_id}
  AND spaf.deleted_at IS NULL

UNION ALL

SELECT
    'Applied - All (source currency)' AS category,
    COUNT(*) AS count,
    COUNT(*) AS rows,
    SUM(laa.adjustment_amount) AS amount
FROM ledger_adjustment_applied laa
WHERE laa.statement_period_id = {sp_id}

UNION ALL

SELECT
    'Applied - Flowthrough (source currency)' AS category,
    COUNT(*) AS count,
    COUNT(*) AS rows,
    SUM(laa.adjustment_amount) AS amount
FROM ledger_adjustment_applied laa
WHERE laa.statement_period_id = {sp_id}
  AND laa.apply_to_flowthrough_payment = 1

UNION ALL

SELECT
    'Applied - Flowthrough (payee currency)' AS category,
    COUNT(*) AS count,
    COUNT(*) AS rows,
    SUM(laa.payee_amount) AS amount
FROM ledger_adjustment_applied laa
WHERE laa.statement_period_id = {sp_id}
  AND laa.apply_to_flowthrough_payment = 1

UNION ALL

SELECT
    'Payment Allocations (payee currency)' AS category,
    COUNT(*) AS count,
    COUNT(*) AS rows,
    SUM(pa.amount_to_payment) AS amount
FROM payment_allocation pa
WHERE pa.statement_period_id = {sp_id};
```

**How to read this:** Compare rows 1-4 against each other (source currency amounts should roughly match). Compare row 5 (Applied FT payee) against row 6 (Payment Allocations) — these should roughly match as both are in payee currency.

---

## 7. Data Integrity Checks

Run periodically to catch any inconsistencies.

### Orphaned Worksheet Adjustments (No Parent File)

```sql
SELECT wa.worksheet_adjustment_id, wa.statement_period_adjustment_file_id
FROM worksheet_adjustment wa
LEFT JOIN statement_period_adjustment_file spaf
    ON spaf.statement_period_adjustment_file_id = wa.statement_period_adjustment_file_id
WHERE spaf.statement_period_adjustment_file_id IS NULL;
-- Should return 0 rows
```

### Orphaned Ledger Entries (No Parent File)

```sql
SELECT laa.ledger_adjustment_applied_id, laa.statement_period_adjustment_file_id
FROM ledger_adjustment_applied laa
LEFT JOIN statement_period_adjustment_file spaf
    ON spaf.statement_period_adjustment_file_id = laa.statement_period_adjustment_file_id
WHERE spaf.statement_period_adjustment_file_id IS NULL;
-- Should return 0 rows
```

### Applied Adjustments Without Worksheet Source

```sql
SELECT
    laa.ledger_adjustment_applied_id,
    laa.statement_period_adjustment_file_id,
    laa.account_id,
    laa.contract_id
FROM ledger_adjustment_applied laa
WHERE NOT EXISTS (
    SELECT 1 FROM worksheet_adjustment wa
    WHERE wa.statement_period_adjustment_file_id = laa.statement_period_adjustment_file_id
      AND wa.account_id = laa.account_id
      AND wa.contract_id = laa.contract_id
);
-- Should return 0 rows (every applied adjustment should have a worksheet source)
```

### Duplicate Payment Allocation Links

```sql
SELECT
    pala.ledger_adjustment_applied_id,
    COUNT(*) AS link_count
FROM payment_allocation_ledger_adjustment pala
GROUP BY pala.ledger_adjustment_applied_id
HAVING COUNT(*) > 1;
-- Should return 0 rows (each adjustment linked to exactly 1 allocation)
```

### Flowthrough Adjustments Applied to Non-Flowthrough Ledger

```sql
SELECT
    laa.ledger_adjustment_applied_id,
    laa.apply_to_flowthrough_payment,
    lac.ledger_account_contract_id
FROM ledger_adjustment_applied laa
JOIN ledger_account_contract lac
    ON lac.account_id = laa.account_id
    AND lac.contract_id = laa.contract_id
    AND lac.statement_period_id = laa.statement_period_id
    AND lac.source = 'adjustment'
WHERE laa.apply_to_flowthrough_payment = 1;
-- Flowthrough adjustments should go to ledger_contract_flowthrough, not ledger_account_contract
-- The source = 'adjustment' filter avoids false positives from non-adjustment ledger entries
-- Non-zero results may indicate a bug in adjustments_apply
```

### Deleted Files with Active Worksheet Data

```sql
SELECT
    spaf.statement_period_adjustment_file_id,
    spaf.file_name,
    spaf.deleted_at,
    COUNT(wa.worksheet_adjustment_id) AS active_adjustments
FROM statement_period_adjustment_file spaf
JOIN worksheet_adjustment wa
    ON wa.statement_period_adjustment_file_id = spaf.statement_period_adjustment_file_id
WHERE spaf.deleted_at IS NOT NULL
GROUP BY spaf.statement_period_adjustment_file_id, spaf.file_name, spaf.deleted_at;
-- Soft-deleted files shouldn't normally have active worksheet data
-- If found: data cleanup may be needed
```

### Flowthrough Config on Inactive Contracts

```sql
SELECT
    cf.contract_flowthrough_id,
    cf.contract_id,
    cf.flowthrough_status,
    c.is_paythrough_contract
FROM contract_flowthrough cf
JOIN contract c ON c.contract_id = cf.contract_id
WHERE cf.flowthrough_status = 'active'
  AND cf.deleted_at IS NULL
  AND c.deleted_at IS NOT NULL;
-- Active flowthrough on deleted contracts is likely a data issue
```

---

## 8. Period-Level Health Checks

Run at the end of a statement period to ensure all data is consistent.

### Summary Dashboard Query

```sql
SELECT
    sp.statement_period_id,
    sp.status AS period_status,

    -- Adjustment files
    (SELECT COUNT(*)
     FROM statement_period_adjustment_file spaf
     WHERE spaf.statement_period_id = sp.statement_period_id
       AND spaf.deleted_at IS NULL) AS total_files,

    (SELECT COUNT(*)
     FROM statement_period_adjustment_file spaf
     WHERE spaf.statement_period_id = sp.statement_period_id
       AND spaf.deleted_at IS NULL
       AND spaf.error_type IS NOT NULL) AS files_with_errors,

    -- Adjustments
    (SELECT COUNT(*)
     FROM worksheet_adjustment wa
     JOIN statement_period_adjustment_file spaf2
         ON spaf2.statement_period_adjustment_file_id = wa.statement_period_adjustment_file_id
     WHERE spaf2.statement_period_id = sp.statement_period_id
       AND spaf2.deleted_at IS NULL) AS total_adjustments,

    -- Applied
    (SELECT COUNT(*)
     FROM ledger_adjustment_applied laa
     WHERE laa.statement_period_id = sp.statement_period_id) AS total_applied,

    -- Flowthrough
    (SELECT COUNT(*)
     FROM ledger_adjustment_applied laa
     WHERE laa.statement_period_id = sp.statement_period_id
       AND laa.apply_to_flowthrough_payment = 1) AS flowthrough_applied,

    -- Allocations
    (SELECT COUNT(*)
     FROM payment_allocation pa
     WHERE pa.statement_period_id = sp.statement_period_id) AS total_allocations,

    -- Unlinked flowthrough
    (SELECT COUNT(*)
     FROM ledger_adjustment_applied laa
     WHERE laa.statement_period_id = sp.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
       )) AS unlinked_flowthrough

FROM statement_period sp
WHERE sp.statement_period_id = {statement_period_id};
```

### Verify All Files Reached Terminal State

```sql
SELECT
    spaf.statement_period_adjustment_file_id,
    spaf.file_name,
    MAX(CASE WHEN as2.action = 'upload_file' THEN as2.state END) AS upload,
    MAX(CASE WHEN as2.action = 'validate_file' THEN as2.state END) AS validate,
    MAX(CASE WHEN as2.action = 'import_file' THEN as2.state END) AS import_step,
    MAX(CASE WHEN as2.action = 'approve_file' THEN as2.state END) AS approve,
    MAX(CASE WHEN as2.action = 'apply_file' THEN as2.state END) AS apply_step
FROM statement_period_adjustment_file spaf
LEFT JOIN abacus_state as2
    ON as2.target_id = spaf.statement_period_adjustment_file_id
    AND as2.target_type = 'statement_period_adjustment_file'
WHERE spaf.statement_period_id = {statement_period_id}
  AND spaf.deleted_at IS NULL
GROUP BY spaf.statement_period_adjustment_file_id, spaf.file_name
HAVING MAX(CASE WHEN as2.action = 'apply_file' THEN as2.state END) != 'complete'
   OR MAX(CASE WHEN as2.action = 'apply_file' THEN as2.state END) IS NULL;
-- Returns files that haven't been fully applied yet
-- At period close, this should be empty (all files either applied or deleted)
```

### Verify All Close Balances Complete

```sql
SELECT
    sppe.statement_period_payment_entity_id,
    sppe.reference_payment_entity_id,
    as2.state AS close_balance_state
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}
  AND (as2.state IS NULL OR as2.state != 'complete');
-- Should return 0 rows if all balances are closed
```

### Final Reconciliation Report

```sql
-- Grand totals for the period
SELECT
    'worksheet_adjustment' AS source,
    SUM(CASE WHEN wa.apply_to_flowthrough_payment = 1 THEN wa.adjustment_amount ELSE 0 END) AS flowthrough_amount,
    SUM(CASE WHEN wa.apply_to_flowthrough_payment = 0 OR wa.apply_to_flowthrough_payment IS NULL THEN wa.adjustment_amount ELSE 0 END) AS non_flowthrough_amount,
    SUM(wa.adjustment_amount) AS total_amount
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.statement_period_id = {sp_id}
  AND spaf.deleted_at IS NULL

UNION ALL

SELECT
    'ledger_adjustment_applied' AS source,
    SUM(CASE WHEN laa.apply_to_flowthrough_payment = 1 THEN laa.adjustment_amount ELSE 0 END) AS flowthrough_amount,
    SUM(CASE WHEN laa.apply_to_flowthrough_payment = 0 OR laa.apply_to_flowthrough_payment IS NULL THEN laa.adjustment_amount ELSE 0 END) AS non_flowthrough_amount,
    SUM(laa.adjustment_amount) AS total_amount
FROM ledger_adjustment_applied laa
WHERE laa.statement_period_id = {sp_id}

UNION ALL

SELECT
    'payment_allocation' AS source,
    SUM(pa.amount_to_payment) AS flowthrough_amount,
    0 AS non_flowthrough_amount,
    SUM(pa.amount_to_payment) AS total_amount
FROM payment_allocation pa
WHERE pa.statement_period_id = {sp_id};
-- worksheet total should ≈ ledger_adjustment total (same source currency)
-- ledger flowthrough total (in payee currency) should ≈ payment_allocation total
```