# Database monthly reports

`monthly_reports` is an ordinary deterministic program. It does not call an Agent/Skill and does not read a prior monthly workbook.

Automatic flow:

1. A dedicated worker leases one successful `arr.daily_version_committed` outbox event with `FOR UPDATE SKIP LOCKED`.
2. It looks up that version's retained `ARRIVAL` values in PostgreSQL. The affected month and report watermark come from those facts; the XML filename and wall clock are ignored.
3. It opens a repeatable-read snapshot of current Finance facts and daily-version pins for that month. `as_of_date` is the greatest `ARRIVAL` actually included in the snapshot.
4. It reserves an idempotent publication identity in `reporting.monthly_runs` with immutable daily lineage and channel manifest rows.
5. It generates the XLSX/result JSON with Python/openpyxl, reopens the workbook, validates all values and formulas, and rechecks that the Finance pins are still current.
6. It uploads both artifacts to the existing private OSS through the immutable object-store adapter and atomically activates the report. Only then is the outbox event marked `published`; transient failures are retried and exhausted events become `dead`.

PostgreSQL stores publication metadata, version identity, lineage and artifact identities—not duplicate monthly business or guest rows. The portal lists `active`/`superseded` runs and downloads only registered artifacts after path, size and SHA-256 checks.

Run the dedicated worker:

```bash
python3 -m monthly_reports.worker \
  --db-config /absolute/path/to/booking-test-db.env \
  --output-root /app/outputs/monthly_reports
```

The CLI generation command remains a controlled recovery/diagnostic entrypoint:

```bash
python3 -m monthly_reports generate \
  --month 2026-07 \
  --as-of 2026-07-31 \
  --output-root /app/outputs/monthly_reports
```

The program reads `MONTHLY_REPORT_DATABASE_URL`, falling back to `ARR_DATABASE_URL`, and uses the existing `ARR_OSS_REGION`, `ARR_OSS_BUCKET` and optional `ARR_OSS_ENDPOINT` settings. The configured database must be `booking_test`; SuperAgent must never receive the DSN. `output-root` is only staging/legacy-cache space; new downloads use the OSS identity.

Every XLSX data-row `TOTAL PRICE` cell contains the exact row-relative formula `=R[row]*C[row]*G[row]`, meaning `REAL PRICE × NIGHTS × NO_OF_ROOMS`. The stored Finance `total_price` remains an independent audit expectation. `Booking Room` enrichment must not be used to recalculate price, dates or actual room count.
