# PRD: Flowthrough Payment Automation & Paythrough Contract Deprecation

**Status:** Shippable (ACC-9366) / In Development (ACC-9335)

**Owner:** Michael Rojas

**Epics:** [ACC-9366](https://theorchard.atlassian.net/browse/ACC-9366), [ACC-9335](https://theorchard.atlassian.net/browse/ACC-9335)

**Feature Flag:** `abacus_flowthrough_automation`

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

---

## 1. Executive Summary

This initiative replaces the legacy "paythrough" contract model with a modern "flowthrough" payment system in Abacus. The old paythrough model used a boolean flag on contracts (`is_paythrough_contract`) with manual, error-prone processing. The new flowthrough system introduces automated file-based adjustment ingestion, configurable calculation methods, granular per-adjustment payment allocation, and automated batch generation.

The work spans two epics:
- **ACC-9366 (Deprecate Contracts):** New data capture pipeline - file upload, validation, import, and batch processing via AWS Step Functions and Lambdas
- **ACC-9335 (Payment Automation):** Automated flowthrough payment generation, calculation methods, and payment allocation

---

## 2. Problem Statement

### Current State (Paythrough)

The legacy paythrough system has critical limitations:

1. **Binary flag model:** Contracts are marked `is_paythrough_contract = true/false` with no granularity - no rate configuration, no calculation method selection, no status lifecycle
2. **Manual processing:** Paythrough adjustments are created and managed entirely through manual spreadsheet uploads with no automation
3. **No payment allocation:** There is no automated mechanism to allocate flowthrough adjustments to specific payments; this is handled outside the system
4. **No audit trail:** Status transitions and calculation decisions are not tracked
5. **Error-prone:** Manual file handling leads to validation errors, duplicate imports, and missed payments

### Desired State (Flowthrough)

A fully automated, configurable flowthrough system that:
- Supports multiple calculation methods (percentage of revenue, manual, etc.)
- Provides per-adjustment control over payment allocation (`apply_to_flowthrough_payment`)
- Automates batch generation based on configurable criteria
- Processes files through a validated, auditable pipeline (upload -> validate -> import -> approve -> apply)
- Allocates payments automatically after balance close

---

## 3. Goals and Non-Goals

### Goals

1. **Replace paythrough with flowthrough** - Deprecate the `is_paythrough_contract` column and migrate all paythrough logic to the `contract_flowthrough` model with configurable rates and statuses
2. **Automated file processing** - Replace the Airflow-only pipeline with AWS Step Functions + Lambda for file initialization, validation, import, and application
3. **Payment allocation** - Automatically create `payment_allocation` records linking flowthrough adjustments to payments after balance close
4. **Auto-generation** - Allow users to trigger automatic generation of flowthrough adjustment batches based on payment entity and schedule criteria
5. **Audit and observability** - Track all state transitions via `abacus_state` and `abacus_event` tables with full monitoring (Datadog, CloudWatch, DLQ)

### Non-Goals

- Migrating historical paythrough data to the new model (handled separately)
- Changing the core ledger accounting engine
- Modifying the statement period lifecycle
- Real-time payment processing (batch-oriented by design)

---

## 4. User Stories

### Adjustment File Upload (Manual)

**As an** accounting operations user,
**I want to** upload an Excel spreadsheet of flowthrough adjustments,
**So that** I can efficiently process bulk adjustments without manual data entry.

**Acceptance Criteria:**
- User can upload `.xlsx` files from the Adjustments page
- Files are virus-scanned before processing
- File format and content are validated (account IDs, contract IDs, amounts, currencies, dates)
- Invalid rows are flagged in a downloadable error report
- Valid files proceed to import automatically
- User can see processing status in real-time (polling every 2 seconds)

### Adjustment Approval and Application

**As a** finance manager,
**I want to** review and approve imported adjustments before they are applied to the ledger,
**So that** I can verify correctness before financial impact.

**Acceptance Criteria:**
- Imported batches appear with "Not Approved" status
- Authorized users can approve batches (gated by `ABACUS_MANUAL_ADJUSTMENTS_APPROVED_USERS` flag)
- Approved batches can be applied to the current statement period
- Applied adjustments create `ledger_adjustment_applied` records with proper currency conversion
- Flowthrough adjustments (`apply_to_flowthrough_payment = true`) create `ledger_contract_flowthrough` entries

### Auto-Generated Flowthrough Batches

**As an** accounting operations user,
**I want to** automatically generate flowthrough adjustment batches based on predefined criteria,
**So that** I don't have to manually create adjustment files every period.

**Acceptance Criteria:**
- User can select batch type (flowthrough), payment entities, and payment schedules
- System generates adjustment file automatically using Snowflake data view
- Generated batches appear in the adjustments list with "Generating" status
- Failed generations show "Failed to Generate" with retry option
- Batches with no matching records show "No Records" status

### Payment Allocation

**As the** system,
**I want to** automatically allocate flowthrough adjustments to payments after balance close,
**So that** flowthrough payments are processed without manual intervention.

**Acceptance Criteria:**
- Triggered automatically when `close_balance.completed` event fires
- Groups adjustments by contract, payee, and currency
- Creates `payment_allocation` records for each group
- Links each adjustment to its allocation via `payment_allocation_ledger_adjustment`
- Idempotent - already-linked adjustments are skipped
- Validates balances are closed before proceeding

### Contract Flowthrough Configuration

**As a** contract manager,
**I want to** configure flowthrough settings on a contract,
**So that** the system knows the rate and calculation method for automated processing.

**Acceptance Criteria:**
- Each contract can have one `contract_flowthrough` record
- Configurable fields: `flowthrough_rate`, `flowthrough_status` (active/shutoff/paused), `reference_flowthrough_calculation_id`, `recoupment_cap`
- Status transitions are tracked with `previous_flowthrough_status`, `status_last_modified_by`, `status_last_modified`
- Soft delete support for deactivation

---

## 5. Scope of Work

### ACC-9366: Flowthrough Data Capture (50 tickets, mostly Closed)

| Category | Key Tickets | Status |
|----------|-------------|--------|
| **Database** | ACC-9371 (file_upload_config), ACC-9372 (file_upload), ACC-9375 (worksheet_flowthrough_batch\*), ACC-9376 (reference_worksheet_flowthrough_error\*), ACC-9377 (worksheet_flowthrough\*), ACC-9378 (worksheet_flowthrough_file\*), ACC-9417 (Liquibase changelog) | All Closed |

> \* Tables marked with \* are **deprecated** and should be torn down. `worksheet_flowthrough_batch` is written to by `adjustment-file-initialize` but never read downstream. The others (`worksheet_flowthrough`, `worksheet_flowthrough_file`, `reference_worksheet_flowthrough_error`) are never populated or queried. Remove the `worksheet_flowthrough_batch` write from `adjustment-file-initialize` first, then drop all four tables.
| **Infrastructure** | ACC-9368 (File Upload Step Functions), ACC-9369 (S3 bucket), ACC-9441 (DLQ access), ACC-9450 (Adjustment Processing Step Functions) | All Closed |
| **Backend - ows-royalties** | ACC-9379 (file_upload scaffold), ACC-9380 (flowthrough scaffold), ACC-9381-9384 (file upload endpoints), ACC-9386 (generic upload), ACC-9425 (event trigger), ACC-9436 (outbox table), ACC-9437 (batch endpoints), ACC-9451 (outbox model), ACC-9452 (approve endpoint) | All Closed |
| **Backend - Lambdas** | ACC-9387 (file_upload_complete), ACC-9454 (adjustment-file-initialize), ACC-9461 (outbox-processor), ACC-9463 (adjustment-file-prepare\*\*), ACC-9464 (process-batch scaffold\*\*), ACC-9486 (file_upload_initialize), ACC-9491 (adjustment-file-complete\*\*) | All Closed |

> \*\* Lambdas marked with \*\* (`adjustment-file-prepare`, `adjustment-file-process-batch`, `adjustment-file-complete`) are **deprecated**. They were deployed to QA but never integrated into the active pipeline. They should be torn down: remove the Terraform resources, then delete the Lambda source code.
| **Frontend** | ACC-9426 (GraphQL file upload), ACC-9442 (generic upload UI) | All Closed |
| **Cleanup** | ACC-9336 (deprecate is_paythrough_contract column), ACC-9370 (remove old S3 bucket), ACC-9388 (remove FT config), ACC-9444 (remove old S3 bucket) | ACC-9336 in Backlog, rest Closed |

### ACC-9335: Payment Automation (21 tickets, mixed status)

| Category | Key Tickets | Status |
|----------|-------------|--------|
| **Feature flag** | ACC-9367 (Split flowthrough FF) | Closed |
| **Backend - Paythrough** | ACC-9244 (update contract to paythrough) | Closed |
| **Backend - Auto-generation** | ACC-9820 (generate lambda scaffold), ACC-9904 (POST /generate), ACC-9905 (generate lambda), ACC-9907 (import lambda), ACC-9908 (auto-generate DAG) | ACC-9820 Backlog, rest Closed |
| **Data** | ACC-9385 (Snowflake view), ACC-10056 (fix currency field) | ACC-10056 In Testing |
| **Validation** | ACC-9862 (validation logic), ACC-10057 (UPC validation), ACC-10310 (flowthrough payment validation) | ACC-10310 In Testing, rest Closed |
| **Frontend** | ACC-10048 (restrict inputs by calculation), ACC-10090 (design updates), ACC-10243 (manual calculation option) | All In Testing (QA) |
| **Testing** | ACC-10058 (flowthrough automation), ACC-10308 (auto-generated adjustments) | ACC-10308 In Development |
| **Cleanup** | ACC-9997 (.gz migration), ACC-9999 (move lambda to QA), ACC-10305 (reverse UAT batches) | All Closed |

### Remaining Work

| Ticket | Summary | Status |
|--------|---------|--------|
| ACC-9336 | Deprecate `is_paythrough_contract` column | Backlog |
| ACC-9453 | Design automated FT adjustment process (spike) | Backlog |
| ACC-9820 | Scaffold `generate_flowthrough_adjustments` lambda | Backlog |
| ACC-10048 | UI: Restrict inputs based on calculation selection | In Testing (QA) |
| ACC-10056 | Fix currency field in Snowflake view | In Testing (QA) |
| ACC-10090 | FE: Design updates to generate payment flow | In Testing (QA) |
| ACC-10243 | Add "Manual" flowthrough calculation option | In Testing (QA) |
| ACC-10308 | Test auto-generated flowthrough adjustments | In Development |
| ACC-10310 | Validate falsy "Apply to Flowthrough Payment" | In Testing (QA) |

### Deprecated — Teardown Required

These resources were built but never integrated into the active pipeline. They should be torn down.

| Resource | Type | Location | Teardown Steps |
|----------|------|----------|---------------|
| `adjustment-file-prepare` | Lambda | `lambda-abacus/lambda/adjustment_file_prepare/` + `terraform-infra/accounting/qa/lambda-abacus/adjustment-file-prepare.tf` | Remove TF resource, delete Lambda code |
| `adjustment-file-complete` | Lambda | `lambda-abacus/lambda/adjustment_file_complete/` + `terraform-infra/accounting/qa/lambda-abacus/adjustment-file-complete.tf` | Remove TF resource, delete Lambda code |
| `adjustment-file-process-batch` | Lambda | `lambda-abacus/lambda/adjustment_file_process_batch/` + `terraform-infra/accounting/qa/lambda-abacus/adjustment-file-process-batch.tf` | Remove TF resource, delete Lambda code |
| `PrepareFile` + `ReshapeAfterInit` states | Step Function (QA) | `terraform-infra/accounting/qa/ows-royalties-workflows/adjustment_file_ingest_state_machine.tf` | Remove from QA state machine definition; simplify to match Prod |
| `worksheet_flowthrough_batch` write | Lambda code | `lambda-abacus/lambda/adjustment_file_initialize/` | Remove the batch creation logic from initialize Lambda |
| `worksheet_flowthrough_batch` | DB table | `royalty_accounting` | Drop table after removing the write above |
| `worksheet_flowthrough` | DB table | `royalty_accounting` | Drop table (never populated) |
| `worksheet_flowthrough_file` | DB table | `royalty_accounting` | Drop table (never populated) |
| `reference_worksheet_flowthrough_error` | DB table | `royalty_accounting` | Drop table (never queried) |
| `CreateStatementPeriodAdjustmentFile` mutation | Legacy code path | `frontend-royalties/src/components/adjustments/` | Remove after `ABACUS_APPLY_FLOWTHROUGH_PAYMENT` <!-- flag torn down 2026-06; see ACC-10470 --> flag is permanently ON |

---

## 6. Success Metrics

| Metric | Target |
|--------|--------|
| Manual adjustment processing time | Reduced by 80% vs. legacy paythrough |
| File validation error rate | < 5% of uploads require resubmission |
| Auto-generation adoption | > 50% of flowthrough adjustments generated automatically within 6 months |
| Payment allocation accuracy | 100% of flowthrough adjustments correctly allocated |
| System reliability | < 3 step function failures per month |
| Processing latency | Adjustment file end-to-end < 20 minutes |

---

## 7. Risks and Mitigations

| Risk | Impact | Mitigation |
|------|--------|------------|
| Feature flag rollout causes partial state | Adjustments processed differently before/after FF enable | Gate all new logic behind `ABACUS_APPLY_FLOWTHROUGH_PAYMENT` <!-- flag torn down 2026-06; see ACC-10470 -->; old path still works |
| Large files cause Lambda timeouts | Files with >10K rows may exceed 15-min Lambda limit | Validation and import Lambdas have 10GB memory and 15-min timeout |
| Currency conversion errors | Incorrect ledger entries | Exchange rate validation in `adjustments_apply`; Lambda errors if rate not found for a `(from_currency, to_currency)` pair. Ensure exchange rates are populated before applying. |
| Close balance race condition | Payment allocation runs before all adjustments applied | Payment allocation validates `close_balance` status is `complete` before proceeding |
| Deadlock on bulk imports | Import Lambda fails on concurrent writes | Retry logic (3 attempts) on MySQL deadlock errors in `adjustment_file_import` |
| Paythrough deprecation breaks existing flows | Contracts lose paythrough flag before flowthrough is ready | ACC-9336 remains in Backlog until full migration is verified |

---

## 8. Timeline

| Phase | Description | Status |
|-------|-------------|--------|
| Phase 1: Infrastructure | S3 buckets, Step Functions, IAM, EventBridge triggers | Complete |
| Phase 2: Data Capture | File upload, validation, import pipeline | Complete |
| Phase 3: Batch Processing | Initialize, prepare, process-batch Lambdas | Complete |
| Phase 4: Payment Automation | Auto-generation, payment allocation | In Progress |
| Phase 5: UI Polish | Calculation-based input restrictions, design updates | In QA Testing |
| Phase 6: Cleanup | Deprecate `is_paythrough_contract`, remove legacy code | Backlog |

---

## 9. Dependencies

| Dependency | Owner | Description |
|------------|-------|-------------|
| Snowflake view `VW_ABACUS_AUTOMATED_FLOWTHROUGH` | Data (Harmanpreet) | Source data for auto-generated batches |
| Split.io feature flags | Platform | Feature gating for rollout |
| EventBridge (`abacus.outbox`) | Shared infra | Event bus for triggering step functions |
| Close balance workflow | Accounting | Must complete before payment allocation runs |
| Exchange rates | Royalties | Must be populated for statement period before `adjustments_apply` |