# Tip Top PAC Reports — project guide for Claude Code

Status: **v4, 12 Oct 2026.** Rebuilt from scratch after a data investigation showed v1–v3 figures were wrong.
Read this whole file before touching code.

## 0. How to work on this project

- **Get Chad's approval before any build step.** Plan, wait for his yes, then show diffs. No silent changes.
- Chad is a non-coder. Explain findings in business terms: which number, by how much, why it matters.
- **Investigate the raw DBF files before writing code.** Every real bug was found by querying data, not by reading code.
  Use throwaway scripts outside the package; only move logic into `tiptop/` once proven on data.
- Say plainly what is confirmed vs unverified. v3 over-claimed fixes it never tested — don't repeat that.
- Chad's father runs PAC. Field-meaning questions go to him via Chad (list in §6).
- Data is confidential (customer names, balances). `data/` and `reports/` are git-ignored. Never commit DBFs or outputs.
- After any change: `TIPTOP_DATA=<data> python -m pytest -q`, rebuild, open `TipTop_Reconciliation.html` — 0 FAIL required.

## 1. What this is

Tip Top: Sri Lankan textile wholesaler (sarongs, readymade, textiles), LKR. Accounting runs on **PAC**, a ~20-year-old
Visual FoxPro/DBF system that writes one folder per accounting period (`Tip Top 35`, `Tip Top 36`, …).
We read the DBFs and produce reports PAC can't. Chad uploads new folders every few months; each run rebuilds the **whole bundle**.

Chad's requirements:
1. Trial balance in fixed half-years **Apr–Sep / Oct–Mar**.
2. Cheques in hand from the **latest** register only.
3. Show customers with **no sales / no collections / no movement** — never hide accounts.
4. **No double counting** when periods are combined.
5. Point out bias and flawed assumptions.

## 2. How PAC stores the books (CONFIRMED on TT35 + TT36 unless marked)

| File | Meaning |
|---|---|
| `actracas` | **Cash-book ledger.** One row per posting to an account; the other side is the **cash book, which PAC never stores**. `AC_D_C` True = debit. `AC_TYPE='R'` = receipt voucher (customer payment), numbered by `AC_NO`. `AC_ALTER` blank = system-generated (cheque deposits + receipts), True = entered by hand. |
| opening block | The **first rows of each folder's `actracas`**: contiguous rows on the first date with blank `AC_DIS` (TT35: 500 rows on 1 Nov 2024; TT36: 702 rows on 4 May 2026). They are that folder's opening balances. `acmaster.AC_BALANCE` is always 0 — not used. |
| `sales1` / `sales2` | Invoices and lines. **Not posted in `actracas`.** S = Dr customer / Cr `S_AC_CODE` (SL000); R = return (reverse); P = purchase (Cr supplier / Dr PU000). **Invoice numbers restart at 1 in every folder** → key = (folder, `S_I_NO`, `S_TYPE`). Line totals equal header totals 100% on that key. |
| `actrajun` | Journals, self-balancing. |
| `actraban` | **PAC's bank-reconciliation view** (`AB_OK` tick). 99.9% of rows mirror an `actracas` row. **Never add it to the ledger** (v3 did → bank movements double counted). |
| `chqreg` | Cheque register. `CHQ_OUT` blank = **still in hand**. `CHQ_OUT` True + `CHQ_O_CU` = BNxx → **deposited** on `CHQ_O_DATE` (matching "Chq.<bank7>-<no>" deposit row in `actracas` >99%). `CHQ_R_V_NO` = receipt voucher no. (100% match to `AC_NO` of R rows). Each folder's register **carries forward** cheques still in hand. Cheque key = (`CHQ_NO`, `CHQ_B_CODE`, amount, `CHQ_I_CU`); numbers alone are not unique. |
| `bankref` | Bank book codes → BN accounts (`BC_TYPE='BN'`) **and product class names** (`BC_TYPE='IT'`). |
| `acmaster`, `bamaster`, `stmaster`, `storest`, `cuadd` | Masters; take from the **latest** folder. `storest.ST_QTY` = `stmaster.IT_S_BAL`. |
| `PAC_Sys/` | Program folder with blank template companies (ACCSYS, BLANK) that also contain `actracas`. **Must be ignored** (loader does). |

Key consequences:
- **Customers are credited when a cheque is received**, not when it clears → Exposure = balance + cheques in hand.
- **Roll-forward proof:** TT35 closing (actracas + sales1 + actrajun) = TT36 opening to the rupee for 548/568 customers,
  28/30 banks, 111/123 suppliers. The rest are period-close entries (sales/purchases/returns/expenses/profit zeroed into
  capital/current/factory accounts, plus round-number transfers: CP001 −690M, SU160 +400M, DC194/195 −72M …).
- **Cash book is derived** = negative of all `actracas`-based rows. 203.4M at 22 May 2026; 313.1M at 30 Apr 2026 (unconfirmed — Q1).
- **Bank outflows are posted in periodic lump sums** (6 Mar 2026 ≈1,035M across 3 banks, desc "6/3"; 29 Apr "up to 29/4"), so
  PAC bank balances between sweeps are not statement balances.
- **PAC list prices (`IT_S_PRICE`) are stale**: 93/153 stocked items list below cost; actual invoiced prices are above cost.
- Data quality: 1 invoice dated year 0202 (repaired from neighbours), 2 zero rows with blank date/code, 17 items negative stock.

## 3. Architecture (`tiptop/`)

```
load.py        discover folders (skip PAC_Sys, empty), read DBFs, clean strings, impossible dates → NaT + logged
ledger.py      chained ledger: first folder's opening block; later folders → per-account "close_adj" = new opening − running close;
               movements = actracas (after opening) + sales1 both sides + actrajun; cash-book side derived (code CASHBK)
periods.py     Apr–Sep / Oct–Mar half-years generated from data; partial flags
reports/
  trial_balance.py   Opening | half-year moves | Period-close | Closing (+ debit totals for cards)
  parties.py         customers/suppliers: categories, status (90-day window), FIFO open-item aging, PDC by party
  pdc.py             cheques in hand (latest register), bands vs as-of, changeover exceptions, deposit history, RC entries
  dashboard.py       monthly sales/returns/purchases/receipts, class mix, top customers/items, stock, cash & banks, KPIs
  recon.py           pass/check/fail checks + questions for the PAC operator
snapshot.py    history/snapshot_<as_of>.json and "what changed since last run"
render/        shell.py (CSS, table component, SVG charts – data only via <script type="application/json">),
               pages.py, guide.py, excel.py (values + live SUBTOTAL/SUM formulas)
build.py       orchestrates everything; __main__.py = CLI
tests/         test_logic.py (synthetic: periods, FIFO, chaining) · test_real_data.py (needs TIPTOP_DATA)
```

Rules the code relies on — keep them true:
- `as_of` = last transaction date in the latest folder (actracas/sales1). **Never `date.today()`** for aging/overdue.
- Every column of the trial balance nets to 0; invoices and journals each net to 0 (tests assert this).
- Opening block used **once** (first folder). Never add `actraban`.
- Every CU in the latest `acmaster` appears in customer outputs; aging buckets − unapplied = balance.
- No Python value is ever interpolated into JS source; pages read `JSON.parse(#data)`.
- Counts on buttons/cards are computed in the page, never hardcoded.
- Customer statuses (window `--quiet-days`, default 90): Active · Buying, not paying · Paying, not buying · Gone quiet ·
  Settled · Balance only, no activity · Never traded. Suppliers use the equivalent labels.
- "RC" customer debits (718 rows, 253M) are **not** labelled as bounced cheques: only 41% match a same-customer,
  same-amount cheque deposited ≤30 days earlier. Shown as "RC entries (meaning unconfirmed)".
- Stock valued at cost and at the median price invoiced in the last 12 months (list price only if no sales; flagged).

## 4. Verified figures (TT35 + TT36, as of 22 May 2026) — regression anchors

Postings 77,013 · books net 0.00 · cheques in hand 1,135 = LKR 211,260,920.50 (3 overdue = 697,000) ·
TT35→TT36 cheques carried 1,049/1,096, missing 47 = 7,502,030 · customer balances total 1,026,649,997 ·
customers 831 (Active 155, Buying-not-paying 76, Paying-not-buying 77, Gone quiet 107, Settled 51, Balance only 104,
Never traded 261) · derived cash book 203,365,491.83 · net sales last 90 days 410.7M · Excel: 1,244 formulas, 0 errors.

## 5. Bugs in v1–v3 (now fixed — don't reintroduce)

1. Added TT36's opening block on top of TT35 history → every balance double counted.
2. Added `actraban` → bank movements double counted.
3. Ignored `sales1` in balances → customer/supplier balances, aging, "no collections" all missing invoices.
4. Deduped invoices on (`S_I_NO`,`S_TYPE`) across folders → 199 real invoices dropped; `sales2` joined without type/folder.
5. Aged against `date.today()` → everything ~5 months too old, May–Oct cheques "overdue".
6. Net-movement aging (not FIFO); three different "dormant" definitions; hardcoded counts (61/158/831).
7. Hardcoded period list silently dropped out-of-range dates.
8. Python generator didn't generate anything; guide claimed it did.
9. JS built by string interpolation → `|||` and apostrophe (`Apr '26`) syntax errors.
10. Stock "margin" from stale list prices (showed selling below cost).

## 6. Open questions for the PAC operator (also on the Reconciliation page)

1. Cash book balance in PAC at end of the latest period (model says 313.1M at 30 Apr 2026; 203.4M at 22 May 2026).
2. Printout of period-close / year-end entries (explain the Period-close column).
3. The 47 cheques (7.5M) in hand at TT35 close but missing from TT36's register.
4. 680 cheques "out" with no bank — passed to suppliers or returned?
5. Meaning of "RC" on customer debits (several "R/C" customer accounts exist, e.g. SUMANGALEE R/C).
6. Are bank payments posted in periodic lump sums by design?

## 7. Ideas not yet built (ask Chad first)

- Days-to-collect per customer; cheque bounce history once RC is confirmed.
- Alerts from `summary.json` / snapshot diff (e.g. WhatsApp/email on new overdue cheques) once on the VPS.
- Customer drill-down page (ledger lines per customer). Supplier payment planning from cheques issued (not in register?).
- Small-multiples for class mix if more than 4 classes become material (palette validated for 4 slots).