21 KiB

Raw Permalink Blame History

Amazon A/R Aging Dashboard — System Guide

Complete reference for how the month-end Amazon Accounts Receivable system works: the flow, every module, every screen, every calculation, and every rule.

Benchmark: the engine reproduces the Finance team's manual workbook exactly — USA January 2026 = 11,110,433 (Detail!D11), and all 13 marketplaces reconcile to the penny.

1. What this system does

Finance downloads Amazon Custom Unified Transaction reports each month (one or more files per marketplace). Previously they hand-built a 283 MB Excel workbook to answer one question:

How much does Amazon still owe us at month-end?

This system automates that end to end: upload the files → it parses, classifies, calculates, reconciles → you get a dashboard and a downloadable audit workbook.

Scope: Amazon only (no Walmart/TikTok/Temu/Shein/eBay/Shopify). 13 marketplaces supported.

2. Quick start

# both servers (from the project root)
python3 -m uvicorn app.api.main:app --app-dir ar-aging-app/backend --reload --port 8000
npm --prefix ar-aging-app/frontend run dev        # http://localhost:5173

Or use the preview launcher, which starts both from .claude/launch.json.

# headless engine run
cd ar-aging-app/backend
python cli.py "/path/file1.xlsx" "/path/file2.xlsx" --month-end 2026-01-31 --lag 2

# tests
python -m pytest tests -m "not integration"   # 64 fast tests
python -m pytest tests -m integration         # 12 tests against the real sample files

3. End-to-end flow

flowchart TD
    A["Amazon reports<br/>(.xlsx per marketplace)"] --> B[Upload & validate]
    B --> C[Parse & normalize]
    C --> D[Aggregate by settlement]
    D --> E[Classify paid vs receivable]
    E --> F[Compute receivable]
    F --> G[Journal decomposition]
    G --> H[(SQLite<br/>persisted results)]
    H --> I[Dashboard tabs]
    H --> J[Excel exports]

    K[Finance inputs<br/>opening balance · FX · clearing-lag<br/>reserve · control amounts] --> E
    K --> F
    K --> I

    style H fill:#ECEAFC,stroke:#6D5DE8
    style I fill:#E7E5FB,stroke:#6D5DE8
    style J fill:#E7E5FB,stroke:#6D5DE8

The nine processing stages

Each stage reports live progress (rows done / total + ETA) to the dashboard.

flowchart LR
    S1[1 Reading<br/>workbook] --> S2[2 Validating<br/>columns]
    S2 --> S3[3 Combining<br/>transactions]
    S3 --> S4[4 Detecting<br/>duplicates]
    S4 --> S5[5 Calculating<br/>settlements]
    S5 --> S6[6 Creating<br/>receivable]
    S6 --> S7[7 Reconciling<br/>totals]
    S7 --> S8[8 Persisting<br/>transactions]
    S8 --> S9[9 Building<br/>journal entry]

#	Stage	What happens
1	Reading workbook	Opens each file, auto-detects the raw data sheet (ignores user-made pivot tabs)
2	Validating columns	Maps localized headers to canonical fields; flags unmapped/missing
3	Combining transactions	Streams all files into one normalized record set
4	Detecting duplicates	Fingerprints rows; counts repeats (never deletes them)
5	Calculating settlements	Groups by settlement id; marks each paid or receivable
6	Creating receivable	Sums open settlements; applies reserve, rounding and FX
7	Reconciling totals	Checks the identity `uploaded = receivable + paid + transfers`
8	Persisting transactions	Bulk-inserts every row with full source traceability
9	Building journal entry	Decomposes activity into GL accounts, per marketplace

4. The calculation — four rules

Everything the dashboard shows derives from these. total (column AD) is the net amount of each row and is the figure that gets summed.

Rule 1 — Settlement classification (which money is still owed)

Amazon pays in settlement batches. When a batch closes, the next opens and Amazon disburses the prior one via a Transfer row tagged with the next batch's id.

A settlement stays receivable until the payout that clears it has actually reached the bank by month-end.

flowchart TD
    T["Transfer row<br/>(a bank payout)"] --> D{"payout date ≤<br/>month_end − clearing_lag?"}
    D -->|Yes| R["RECEIVED<br/>→ sets the paid boundary"]
    D -->|No| I["IN TRANSIT<br/>→ its settlement stays receivable"]
    R --> B["paid_boundary = max settlement_id<br/>over received payouts"]
    B --> C{"settlement_id ≥ boundary?"}
    C -->|Yes| RECV[RECEIVABLE]
    C -->|No| PAID[PAID]

    style RECV fill:#DCFCE7,stroke:#2FA875
    style PAID fill:#F1F1F6,stroke:#868AA0
    style I fill:#FAF0DA,stroke:#B37A1B

clearing_lag defaults to 2 days and is adjustable per closing. Every payout is listed on the Settlement Reconciliation tab where you can override its received/in-transit status.

Rule 2 — The receivable base

additional_sales = Σ total
                   WHERE settlement is receivable
                     AND account_type ∈ {Standard Orders, Invoiced Orders, All Orders}
                     AND type ≠ Transfer

Rule 3 — Closing receivable

Two independent methods, which must agree:

# Settlement method (the workbook's method — the benchmark)
receivable_local = ROUND(reserve + additional_sales, 0)
receivable_usd   = receivable_local × fx_rate

# Roll-forward method (the AR Ledger)
closing = opening_balance + net_revenue − payouts_received

Opening balance — three modes

Chosen when the closing is created (and changeable any time on the AR Ledger tab):

Mode	Behaviour
Zero (default)	Every marketplace opens at 0 — correct for a first-ever closing
Carry forward	Copies each marketplace's closing receivable from a chosen prior closing. Falls back to zero if no processed prior exists
Manual	Opens at 0; you type each marketplace's balance on the AR Ledger tab after processing

Rule 4 — Reconciliation identity

Every uploaded row lands in exactly one bucket, so this must always hold:

uploaded_total = receivable_orders + paid_orders + transfers_total

Status shows Reconciled when the difference is within tolerance (default $0.01).

5. Architecture

flowchart TB
    subgraph FE["Frontend — React + TypeScript + Vite"]
        P1[Dashboard] --- P2[New Closing] --- P3[Closing tabs] --- P4[Settings]
    end
    subgraph API["Backend — FastAPI"]
        R1[sessions/files/processing] --- R2[results/ar/control] --- R3[analytics] --- R4[export/settings]
    end
    subgraph CORE["Engine — pure Python, no web deps"]
        C1[readers] --> C2[column_map + i18n]
        C2 --> C3[pipeline]
        C3 --> C4[settlements]
        C4 --> C5[receivable]
        C5 --> C6[movement + journal]
        C6 --> C7[reconciliation + validation]
        C7 --> C8[excel_export + summary_export]
    end
    DB[(SQLite / Postgres)]

    FE -->|"/api (Vite proxy)"| API
    API --> CORE
    API --> DB
    CORE --> DB

    style CORE fill:#ECEAFC,stroke:#6D5DE8

Key principle: the engine (app/core/) has no web or database dependencies. It can run headless via cli.py, which is what the integration tests exercise.

6. Backend modules — each one defined

`app/core/` — the engine

Module	Purpose
`readers.py`	Factory choosing the parser: `python-calamine` (fast, Rust) by default, streaming fallback via `AR_USE_CALAMINE=0`
`calamine_reader.py`	Fast parser. Detects the data sheet, skips pivot/helper rows, yields normalized records
`xlsx_reader.py`	Low-memory streaming parser (`xml.etree.iterparse`) + `FileMeta`. Used for very large files
`column_map.py`	Canonical schema and header aliases for 8 languages. Maps by normalized name, never position
`i18n.py`	Marketplace config: transaction-type translations, currencies, default FX, storage keywords
`dates.py`	Multilingual date parsing (11 formats) with a prefix cache — turns millions of parses into dozens
`regions.py`	Maps `amazon.de` → `Germany` etc.
`pipeline.py`	Orchestrates parse → enrich → aggregate → classify → receivable → reconcile, with progress callbacks
`settlements.py`	Groups rows by settlement; owner attribution; the paid/receivable classification (Rule 1)
`receivable.py`	Rules 2 and 3: additional sales, reserve, rounding, FX, aging bands
`movement.py`	Roll-forward: opening + net revenue − payouts, and the AR ledger rows
`journal.py`	GL decomposition into 9 journal lines + 17 granular components, per marketplace
`reconciliation.py`	Rule 4 identity check and status
`validation.py`	File coverage: wrong month, overlapping ranges, date gaps
`excel_export.py`	Full audit workbook (streaming `write_only`, live formulas)
`summary_export.py`	Compact Finance pack (5 sheets, generates in seconds)
`compare.py`	Diffs a generated workbook against the sample for the comparison test

`app/api/routes/` — the web layer

Route module	Responsibility
`sessions.py`	Create/list/update/delete month-end closings; seeds opening balances from the prior month
`files.py`	Chunked upload, filename sanitization, per-file parse validation
`processing.py`	Starts the background job; reports status, stage, rows done, ETA
`results.py`	Summary, settlements, transactions (filterable), aging, exceptions, journal
`ar.py`	Opening balances, `ar-movement` (roll-forward), `finance-summary`
`control.py`	Reconciliation control: dashboard vs Finance amounts, sign-off, completion gate
`analytics.py`	Date-filtered ledger, daily FX, sign-explained reconciliation detail, all-markets roll-up
`export.py`	Starts Summary/Full exports; lists and downloads them
`settings.py`	Reserves, FX rates, and global header-mapping rules

`app/services/`

Module	Responsibility
`jobs.py`	Background processing and export jobs; owns its DB session; writes progress + ETA
`store.py`	Bulk transaction inserts (batched raw DBAPI), persists aggregates, builds exceptions

7. Database tables

Table	What it holds
`sessions`	One month-end closing: month, month-end date, clearing-lag, tolerance, status, progress
`session_files`	Uploaded files: name, size, SHA-256, sheet, rows, date range, currency, status
`transactions`	Every parsed row with `source_file`/`source_row` traceability, canonical type, flags
`settlements`	Per (marketplace, account, settlement id): order total, transfers, dates, paid/receivable
`receivable_results`	Per marketplace and account: additional sales, reserve, local + USD receivable
`reconciliation`	Identity check figures and status
`market_payouts`	Per-marketplace received / total payouts, attributed to each settlement's owner
`opening_balances`	Opening AR per marketplace, with source (manual / carried-forward) and reason
`fx_rates`	Month FX rate + currency per marketplace
`fx_rates_daily`	Optional per-date FX override
`reserves`	Net Closing Balance per marketplace and account
`journal_entries`	The GL decomposition JSON (primary + `per_marketplace`) and entry number
`finance_control`	Finance's control-sheet amounts, tolerance, sign-off and comments
`exceptions`	Every validation finding: category, severity, detail, source
`mapping_rules`	Admin header aliases (global, applied to all future parses)
`exports`	Generated workbooks: kind, path, SHA-256, size, timestamp

8. The dashboard — each tab defined

flowchart LR
    OV[Overview] --> UP[Upload & Mapping]
    UP --> VE[Validation & Exceptions]
    VE --> FS[Finance Summary]
    FS --> AL[AR Ledger]
    AL --> SR[Settlement Reconciliation]
    SR --> AG[A/R Aging]
    AG --> TD[Transaction Details]
    TD --> RE[Reconciliation]
    RE --> JE[Journal Entry]
    JE --> EX[Excel Export]

Tab	What it shows	Key controls
Overview	KPI cards (closing receivable, reconciliation status, settlement counts, exceptions) and receivable by marketplace	View details → per marketplace; All markets →
Dashboard (home)	All closings with status and receivable	Delete removes the closing and every row/file it owns
Upload & Mapping	Drag-drop upload; per-file rows, date coverage, marketplace, status	Header mapping panel to classify unknown columns and save the rule
Validation & Exceptions	Every finding grouped by severity	—
Finance Summary	The Finance report: opening → revenue components → gross → fees → net revenue → closing, plus the ledger	Market switcher · All Markets sub-tab · Summary Excel
AR Ledger	Opening balance (Adjust · Carry forward · Set to zero), roll-forward statement, ledger movement, cross-check vs settlement method, movement by date, daily FX table	Daily/Weekly/Monthly + custom range · market switcher · All Markets
Settlement Reconciliation	Every settlement with orders, transfers, rows, period and status; the disbursement list	Clearing-lag control + re-process
A/R Aging	Aging matrix (Current / 1-30 / 31-60 / 61-90 / 91-Over) and chart	—
Transaction Details	Virtualized grid of every row with source file + row	Filters: settlement, type, marketplace, receivable, storage, search
Reconciliation	Sign-explained metrics (Revenue / Fees / Memo / Settlements), closing & variance, plus the Reconciliation Control	Finance amounts, tolerance, Verify, Mark month complete
Journal Entry	GL decomposition per period with account names	Journal Entry #
Excel Export	Generate/download Summary or Full workbook, with history	Two generate buttons

The All Markets sub-tab

Present on Finance Summary, AR Ledger and Reconciliation. Shows every marketplace in its own currency with its FX rate, plus the combined USD total, and a View details button per row. The selected market lives in the URL (?market=Germany, or ?market=__all__), so links are shareable.

9. Data model — the source columns

Amazon's report has a 7-row preamble, header on row 8, data from row 9. Column counts vary by marketplace (USA 30, Canada 29, UK 27, AU/IE 23, localized EU 28/24).

The five columns that drive everything:

Col	Header	Role
A	`date/time`	Aging, date filters, payout clearing
B	`settlement id`	Grouping; the paid/receivable boundary (monotonic — higher = newer)
C	`type`	Order / Refund / Adjustment / fees / Transfer
I	`account type`	`Standard Orders` / `Invoiced Orders` (USA only; others use one `All Orders` stream)
AD	`total`	The net amount summed for every figure

The other 25 columns (product sales, taxes, shipping, gift wrap, promos, selling fees, FBA fees, storage, other) feed the revenue/fee breakdown in the Finance Summary and Journal Entry.

10. Multi-marketplace handling

Aspect	How it works
Languages	Headers, transaction types and dates are localized in FR, DE, IT, ES, NL, PL, SV, TR. All normalized to canonical English
Currencies	USD, CAD, GBP, EUR, PLN, SEK, AUD, TRY — each with a default FX-to-USD rate, editable per closing
Account streams	Only the USA report has `account type`. Other marketplaces run a single `All Orders` stream
Settlement owner	A settlement belongs to the marketplace holding most of its rows. Payouts attach to their settlement's owner, not the file they appear in (Belgium's payouts sit inside the Germany report)
Blank marketplace rows	Inherit the file's dominant marketplace — never a filename guess (critical for combined files like "Belgium Germany")
Cross-market rows	Rows in a different marketplace than their settlement's owner are excluded and flagged
Helper rows	EU files carry a Finance-added translation row under the real header — detected and skipped

11. Validation & exception categories

No amount is ever silently excluded.

Category	Severity	Meaning
`missing_column`	error	A required field is absent — must be fixed
`unmapped_amounts`	error	An unmapped column contains money. Caught Australia's renamed FBA-fee column (−2,427.43)
`unmapped_column`	warning	An unrecognized header — classify it in Upload & Mapping
`wrong_month`	error/warning	File dates fall outside the reporting month
`overlapping_files`	warning	Two files of the same marketplace cover the same dates
`missing_dates`	warning	A gap in coverage within the month
`duplicate_rows`	warning	Identical row fingerprints — surfaced, never deleted
`undated_rows`	warning	Rows with no usable date (e.g. a manually appended total row)
`potential_storage_fee`	warning	Storage-like description booked under another type
`missing_storage_fees`	warning	A marketplace with no storage fees at all this month
`cross_market_settlement`	warning	Rows belonging to another marketplace's settlement
`in_transit_disbursement`	info	A payout not yet cleared — explains why a settlement stays receivable
`helper_header_row`	info	Translation rows skipped
`empty_file`	warning	Header only, no data rows

12. Excel exports

	Contents	Size / speed
Summary Excel	Finance Summary · AR Ledger · Reconciliation Control · Category Totals · Audit Trail	~11 KB, seconds
Full Excel	Summary · Detail · COA · AR Ledger · Journal Entry · per-marketplace transaction tabs · Reconciliation · Exceptions · Processing Audit Trail — with live formulas (`SUMIFS`, `ROUND`, cross-sheet refs)	~240 MB, a few minutes

The Full workbook mirrors the manual file's structure so an auditor can trace any total back to the transaction rows. The Processing Audit Trail sheet records generation time, inputs, filenames with SHA-256 hashes, row counts, FX rates, reserves and the app version.

13. Testing

Suite	Covers
`test_engine_unit.py`	Dates, column mapping, aging bands, settlement classification
`test_multimarket.py`	11 localized date formats, 22 type translations, storage detection, header mapping, 13-market benchmark
`test_per_market.py`	Journal resolution, payout owner attribution, per-market API
`test_reconciliation_jan2026.py`	USA = 11,110,433 to the penny, settlement split, reconciliation
`test_excel_export.py` / `test_summary_export.py`	Workbook structure, formulas, sheet splitting
`test_workbook_comparison.py`	Generated workbook vs the sample
`test_api.py` / `test_robustness.py`	Full API flow; corrupt/empty/bad-header files

64 fast + 12 integration tests. Run integration with the sample files present:

AR_SAMPLE_DIR="/path/to/samples" python -m pytest tests -m integration

14. Known data issues & assumptions

These came out of reviewing the real January and February files:

Australia renamed a column (Feb): fba fees → fulfilment by amazon fees. Aliased; the unmapped_amounts check caught the −2,427.43 before it reached a report.
Canada has a manual footer row (Feb): "Cost of advertising", −362,715.55, with no date, type or settlement id. Excluded from the receivable, flagged as undated_rows — needs a Finance decision.
New Amazon columns (Feb): Transaction Status, Transaction Release Date, low value goods. All mapped.
Duplicates are usually legitimate — components sum to totals and the receivable reconciles exactly with them included. Surfaced for review, never auto-removed.
Liquidations are a memo line — their amounts stay in the sales/fee buckets; re-bucketing would change gross revenue.
Settlement ids are assumed unique per Amazon account — true across all real data.
Month-boundary payouts: a payout dated Jan 30 clears the bank in early February but lives in the January file, so February never records receiving it. Explains USA/Canada roll-forward variances.

15. Where to look

I want to…	Go to
Understand the receivable maths	`accounts-receivable-logic.md`
See where each dashboard number comes from	`dashboard-guide.html`
Change the settlement cutoff	Settlement Reconciliation tab → clearing-lag
Map a new/renamed Amazon column	Upload & Mapping tab → Header mapping
Set the opening balance	AR Ledger tab → Adjust
Sign off the month	Reconciliation tab → Reconciliation Control → Verify → Mark month complete
Get the audit file	Excel Export tab → Generate full workbook

21 KiB Raw Permalink Blame History Unescape Escape