# Accounting Run — Legacy Workflow

> Source: [Accounting Run Workflow (Notion)](https://www.notion.so/e2f641f40d314a43abd1e0b448e6cf5d)  
> Last updated in Notion: June 2025

This documents the **legacy** accounting run workflow that uses Docker containers, ActiveMQ, PHP scripts, and Aurora MySQL. This is the infrastructure used for the traditional monthly distribution run (hundreds of millions of rows), as opposed to the Abacus DAG-based system.

## Infrastructure Components

| Component | Description |
|-----------|-------------|
| **S3** | Sales files, data exports/imports between StatementDB and art_relations |
| **Aurora MySQL (art_relations)** | `art_relations` and `accountingflat` databases; heavily partitioned for parallelism |
| **Memcached (Elasticache)** | `prod-cron-accounting` — caches vendor contract/payment data during calculation |
| **ActiveMQ** | Standalone EC2 instance; manages accounting message queues (requires Java 8, ActiveMQ 5.8.0) |
| **Accounting Workers** | EC2 ASG (check current instance count in AWS console); run Docker containers with PHP scripts |

### Monitoring

- [RDS Dashboard (Datadog)](https://app.datadoghq.com/dashboard/czh-59d-w7i/prod-art-relations-rds-timeboard)
- [RDS Monitors (Datadog)](https://app.datadoghq.com/monitors/manage?q=prod-art-relations)
- [Load/Unload Logs (Datadog)](https://app.datadoghq.com/logs?saved-view-id=3115880)

## Phase 1: StatementDB Prep

1. **Create EBS snapshot** of StatementDB data volume (prefix: `acc-run-<PERIOD_ID>`)
2. **Export raw data** — `SELECT INTO OUTFILE` from `dig_sales_testfile` (~120M rows, ~3.5 min)
3. **Split** into 1M-row chunks
4. **Upload to S3** — `s3://prod-royalties-sales-files/statement-db-export/<PERIOD_ID>/dig_sales_detail/`

## Phase 2: Art_Relations Cluster Prep/Load

### Currency Exchange Rates
Run `currency_rate_inserts.sql` from James Kass's zip file. Ensure numerical currency IDs, zero warnings.

### dig_sales
Upload `dig_sales.txt` to S3, then `LOAD DATA FROM S3` into `art_relations.dig_sales`.

### dig_sales_detail
1. Rename existing table to `dig_sales_detail_{autoincrement}`
2. Create new table with partitions = `num_rows / 1,000,000`

### TEMP_dig_sales_statements
Truncate and repartition: `sum(rows) / 10,000,000` partitions (rounded up).

### Load and Unload Data

Run the [`python-accounting-run-utils`](https://github.com/theorchard/python-accounting-run-utils) Docker container:

```bash
docker pull 086679231553.dkr.ecr.us-east-1.amazonaws.com/python-accounting-run-utils:latest
docker run -it -d --name python-accounting-run-utils \
  -e Environment -e MYSQL_HOST -e MYSQL_DATABASE \
  -e MYSQL_USER -e MYSQL_PASSWORD -e PERIOD_ID \
  -e S3_BUCKET -e S3_DIM_IMPORTS_BUCKET -e S3_ABACUS_OUTFILE_BUCKET \
  --entrypoint=/bin/bash \
  086679231553.dkr.ecr.us-east-1.amazonaws.com/python-accounting-run-utils
```

Scripts perform:
1. Load S3 → `dig_sales_detail` (parallel)
2. Validate row count against checksum
3. Select into S3 outfiles by partition (parallel)
4. Load S3 → `TEMP_dig_sales_statements` (parallel)
5. Validate `in_content` flag
6. Validate ISRCs
7. Select into `TEMP_dig_sales_processed`

### Post-Load Validation
- Check `total_qty`, `total_amt`, `total_rows` against checksum file and Slack
- Check for warnings (only `Data truncated for column 'isrc'` is expected)
- Run `CALL art_relations.sp_get_missing_exchange_rates(<period_id>)` — null values in rate columns are blockers

## Phase 3: Accounting Calculation

### Pre-Calculation
1. Create RDS snapshot (prefix: `acc-run-<PERIOD_ID>-pre-load`)
2. Start ActiveMQ — ensure `ldb-data` directory is empty; ~400GB disk holds ~35 batches
3. Mute Maxwell CPU monitors in Datadog for 12 hours

### Run Docker Containers

On all 6 accounting worker instances:

```bash
docker pull 086679231553.dkr.ecr.us-east-1.amazonaws.com/orchard-repo-cron-worker:latest
docker run -it -d --name cron-accounting \
  -v /opt/cron-accounting/configs:/tmp/cron-accounting/configs \
  -v /opt/cron-accounting/public:/tmp/cron-accounting/public \
  -v /opt/cron-accounting/reports:/var/www/html/data/temp \
  --env APPLICATION_ENV=production --env Environment=prod \
  --entrypoint=/bin/bash \
  086679231553.dkr.ecr.us-east-1.amazonaws.com/orchard-repo-cron-worker
```

### Publish Accounting Queue
On ONE instance, seed ActiveMQ in batches (each batch = 10M records):

```bash
nohup php /var/www/html/scripts/Accounting/publishAccountingQueue.php -m <MONTH> -y <YEAR> -b 1 &
# ... up to batch 35 (ActiveMQ disk limit)
```

### Prep Accounting
**Run exactly once.** Must run on the same day as process workers.

```bash
nohup php /var/www/html/scripts/Accounting/prepAccounting.php -m <MONTH> -y <YEAR> &
```

### Process Accounting Queue
On ALL worker instances, run up to 10 scripts per container (50-60 total):

```bash
nohup php /var/www/html/scripts/Accounting/processAccountingQueue.php -m <MONTH> -y <YEAR> &
# (repeat up to 10x per instance)
```

After completion, check for unprocessed records:
```sql
SELECT COUNT(*) FROM TEMP_dig_sales_processed WHERE processed = 'N';
```

If non-zero, re-run publish + process for all batches.

### Vendor Accounting

Before running, export `processed_dig_sales` to Snowflake and check for duplicates:

```sql
SELECT statement_detail_id FROM FACTS.PROD.STAGING_FACT_SALES
WHERE period_id = <PERIOD_ID>
GROUP BY statement_detail_id HAVING COUNT(*) > 1;
```

If duplicates exist, stop and ask in `#accounting-run-priv`. Then run vendor accounting:

```bash
nohup php /var/www/html/scripts/Accounting/processSummaryQueue.php -m <MONTH> -y <YEAR> &
# (up to 24 concurrent, respawn as they exit)
```

### Owner Accounting
**Run exactly once. Not safe to re-run.**

```bash
nohup php /var/www/html/scripts/Accounting/processOwnerAccounting.php -m <MONTH> -y <YEAR> &
```

### Accounting Reports
**Run exactly once. Takes hours.**

```bash
nohup php /var/www/html/scripts/Accounting/processAccountingReports.php -m <MONTH> -y <YEAR> &
nohup php /var/www/html/scripts/Accounting/processWithholdingReports.php -m <MONTH> -y <YEAR> &
```

## Phase 4: Post-Calculation

### Abacus Export
Export `TEMP_dig_sales_statements` in Abacus format:

```bash
docker exec -it python-accounting-run-utils bash
export PYTHONPATH='.':$PYTHONPATH
nohup bash -c "${VIRTUAL_ENV}/bin/python accounting_run_utils/select_temp_dig_sales_statements_into_outfile.py >> /proc/1/fd/1" &
```

### Quarterly Tasks
At the beginning of each calendar quarter:
- Rename `processed_dig_sales` to `processed_dig_sales_p{start}to{end}`
- Create new partitioned table for the next quarter