JEI_Import_Documents-App/NOTES.md

JEI South Africa — Build Notes

This file captures decisions, surprises, and parser quirks as we build the JEI invoice tracker.

---

2026-09-17 — Discovery phase

Discovered while inspecting uploaded PDFs

1. One PDF can hold multiple invoices. 1.Invoice_29.05.pdf has 4 pages and contains 4 separate invoices (161453, 161454, 161611, 161852). The parser must split per page and detect a new "Invoice No" token.

2. The buyer's legal entity is constant. Every observed invoice shows K2023742851 SOUTH AFRICA (PTY) LTD as the legal buyer. Only the T/A line varies. The user mentioned 3 buyer-name variants — these map to T/A values on top of a constant legal entity.

3. Deliver-To can be a different company. On several invoices the "Deliver To" is SHIPLEY GLOBAL EVENTS (PTY) LTD, 678 MIRROR CARD ST, GARSFONTEIN, PRETORIA 0042 — a related entity where goods physically land. Captured as deliver_to_name + deliver_to_address.

4. 36 files in folder, not all are JEI. FedEx shipping docs and credit notes (in ZAR, not GBP) were moved to invoices/_non_jei/. Remaining: 33 JEI files (invoices, proforma, 1 quote).

5. Empty PDFs observed. Invoice.pdf and JEI Supplier Invoice.pdf have no extractable text (likely failed exports). Will be flagged in data/review/unparseable.txt for re-export.

Supplier details (captured during inspection)

Field	Value
Supplier legal name	JEI DRILLING & CUTTING SOLUTIONS LTD
Supplier address	Unit 21 Empire Business Park, Enterprise Way, Burnley, Lancashire, BB12 6LT
Supplier phone	01706 229490
Supplier email	sales@jeisolutions.co.uk
Supplier VAT	227257212
Supplier EORI	GB227257212000
Buyer legal name	K2023742851 SOUTH AFRICA (PTY) LTD
Buyer VAT	4450314382
Account ref	JEISA000 (constant)
Currency	GBP only
Latest invoice observed	164912 (date 2026-08-20)
Earliest invoice observed	147842 (date 2025-05-21)

Documents in scope vs out of scope (this build)

Document type	Action
JEI invoice	Import as document_type = invoice
JEI proforma (paid in full)	Import as document_type = proforma
JEI quote	Import as document_type = quote
FedEx shipping/credit note	Skip (in _non_jei/)

User decisions captured

• Deliver-to: Capture as separate fields.
• Quotes: Treat same as invoices; clean up later against JEI statement.
• Non-JEI files: Move to _non_jei/ subfolder; ignore in importer.

Parser heuristics (planned)

• Document boundary: New page containing Invoice No or Quotation No token.
• Document type: QUOTATION → quote; INVOICE → invoice; proforma detected via filename keyword or stamp on the page.
• Buyer variant: Match T/A line against three known strings.
• Stock code: Pull from the table column; if it's just SERIAL (e.g. for serial-number line items), skip that line — it's not a stock code.
• Deliver-to: Read from the Deliver To : block at the bottom of the invoice.

Open questions / things to watch during build

• Two empty PDFs need user attention (re-export from source).
• One large multi-invoice PDF (JEI Drilling Invoices.pdf, 6.4 MB) — verify parser doesn't choke on size.
• Some older invoices use 20% VAT column even though Total VAT is shown as £0.00 (reverse-charge mechanism — exporter doesn't charge VAT). Verify parser handles Total 20% VAT £ 0.00 gracefully.
• One invoice has both "Customer Order No" and "Sales Order No" fields; both are captured (PO = customer's PO; sales order = supplier's internal ref). The user only mentioned the customer one; we capture both for free.

Duplicate-file check (user-requested)

Searched all 33 JEI filenames for repeated invoice numbers. Found 4 logical duplicates:

Invoice No	Files	Hash
154923	154923 Supplier Invoice.pdf + JEI 154923 89.10+102.pdf	Different (different PDF render)
155655	155655 Supplier Invoice.pdf + JEI 155655.pdf	Different
156092	156092 Supplier Invoice.pdf + JEI 156092.pdf	Different
161453	Invoice 161453 from JEI... - 14_04_2026.pdf + Invoice 161453.pdf	Different

Verified by reading both files for invoice 154923 — same data, different PDF layout (one is flow-text, one is more grid-aligned). Not byte-identical files, but identical invoice data.

Dedup strategy: the importer relies on the JEI_invoices.invoice_no + document_type uniqueness — second occurrence skipped silently. The first one imported wins (its pdf_file is kept). User can later delete the duplicate filename's reference via PB admin if needed (the duplicate file's data is merged into the first record).

Impact: no schema change needed. Idempotency rule (§5.2) already covers this.

Additional observations from invoice 154923

• "Customer Order No" field on this invoice reads literally Email 28.07.25 (looks like a separate "Email [date]" reference the supplier uses). The importer will capture it as-is. User can clean later.
• The invoice is stamped PAID IN FULL. Some invoices are pre-paid (proforma-paid or settled-on-delivery). We don't track this in the schema yet (future phase) but it's visible on the original PDF.

2026-09-17 — Build phase

Major wins

• ✅ Next.js 14 + Tailwind app on http://localhost:5001 (changed from 6002 → 6003 → 5001 on 2026-09-18; 6002 was the Workflow CIPC service, 6003 conflicted briefly with user's other services, 5001 was the user's preferred port)
• ✅ PB collections: JEI_invoices (15 fields + id), JEI_invoice_lines (8 fields + relations), JEI_stock_codes (4 fields + id) — 30 fields total
• ✅ PDF extractor parses 38 records from 33 JEI supplier PDFs (skips _non_jei/)
• ✅ Importer pushed 24 invoices + 70 stock codes + 118 lines into PB (idempotent — re-runs skip duplicates)
• ✅ All 5 UI pages return 200 with real data

Critical PB v0.39.8 API quirks discovered

1. POST /api/collections uses key fields not schema. Docs show schema but the API silently accepts schema and stores under fields. Always send fields: [...].

2. Field options go at the FIELD ROOT, not inside options. This is the opposite of what docs for older PB versions show. e.g.

   • ✅ WORKS: { name: "foo", type: "select", maxSelect: 1, values: ["a","b"] }
   • ❌ FAILS: { name: "foo", type: "select", options: { maxSelect: 1, values: ["a","b"] } } — returns fields.X.values.code: validation_required, Cannot be blank. even though the values array is non-empty.
   • Same applies to relation fields: collectionId and cascadeDelete go at the field root, not in options.

3. GET response hides field options. After creating a collection, GET /api/collections/<id> returns fields with options: {} (empty) for select/relation fields. The data IS stored — just not returned. PATCH the same field with explicit options to confirm (it succeeds).

4. PATCH round-trip "wakes up" newly created collections. Same trick as the existing memory entry — if a collection 403s on read or returns empty fields, PATCHing any rule field (even with the existing value) often fixes it.

Parser quirks remaining

• 4 unparseable PDFs (empty text — likely failed exports: Invoice.pdf, JEI Supplier Invoice.pdf, JEI SA Invoice 147842.pdf, JEI SA Proforma 148931.pdf).
• Some multi-page PDFs (151778, 151779) have page-boundary issues — the parser creates multiple records from the same physical invoice when it shouldn't.
• "JEISA000" got imported as a stock code (it's actually the buyer's account reference) — minor data issue, can be deleted via admin UI.
• 161453, 161852: lines parsed as 0 (one of the page variants has a different layout that the parser doesn't recognize).

Token expiry

PB token expires 2026-09-18 19:10 SAST (~22h from now). Re-grab from DevTools → Application → Local Storage → pocketbase_auth.token on the PB admin UI when needed.

File layout (final)

JEI/
├── .env.local                    # PB_URL, PB_TOKEN, NEXT_PUBLIC_PB_URL
├── .gitignore
├── NOTES.md
├── package.json                  # next 14, pocketbase, tailwind, etc.
├── tsconfig.json
├── next.config.js
├── tailwind.config.ts
├── postcss.config.js
├── app/
│   ├── layout.tsx                # header with nav
│   ├── page.tsx                  # dashboard
│   ├── invoices/page.tsx         # list with search
│   ├── invoices/[id]/page.tsx    # detail with PDF link
│   ├── stock-codes/page.tsx      # list with stats
│   ├── stock-codes/[code]/page.tsx  # detail with avg/min/max
│   ├── globals.css
│   └── lib/{pb.ts, types.ts}
├── scripts/
│   ├── create_collections.py     # creates 3 JEI_* collections
│   ├── extract_invoices.py       # PDF → data/review/all.json
│   ├── import_to_pb.py           # all.json → PB (idempotent)
│   └── patch_collections.py      # legacy workaround (not needed anymore)
├── invoices/                     # user drops PDFs here
├── data/review/all.json          # parsed records
└── docs/superpowers/specs/...    # design spec

How to use

1. Server runs on http://localhost:5001 via pnpm dev (or node_modules/.bin/next.cmd dev -p 5001).
2. To re-extract: python scripts/extract_invoices.py (reads all PDFs in invoices/, writes data/review/all.json).
3. To re-import: python scripts/import_to_pb.py (skips existing invoice_no + document_type pairs).
4. To refresh PB token: visit https://pb.agent.apphub.co.za/_/, copy pocketbase_auth.token from DevTools → Application → Local Storage, paste into JEI/.env.local.

2026-09-21 — Multi-invoice splitting

User requested that every PDF in invoices/ end up as one invoice per file. Prior behaviour: multi-invoice PDFs (e.g. 1.Invoice_29.05.pdf with 4 invoices, Supplier Invoice.pdf with 2 invoices) were kept as-is and parsed per-page — which worked for extraction but left ugly multi-invoice files in invoices/ and caused only the first invoice's PDF to be attached in PB.

Added scripts/split_invoices.py

Runs BEFORE extract_invoices.py. For each PDF in invoices/ (skipping _non_jei/):

1. Detect distinct invoice numbers across pages (regex on extracted text, with OCR fallback from data/review/ocr/<stem>/page_NNN.txt).
2. If 0 or 1 distinct numbers → leave alone (rename_invoices.py handles single-invoice naming).
3. If 2+ → group consecutive pages by invoice number (so a multi-page proforma like 151779 with 6 pages stays as ONE output), write each group to invoices/<invoice_no>.pdf via PyMuPDF, move source to invoices/_non_jei/.
4. Idempotent: skips re-generating outputs whose page count already matches. On collision with an existing file of the same name but different page count, appends _2, _3, etc.

One-off PATCH done this turn

For invoices 164788 and 164789 (just split out of CORR875774841036.pdf), PATCHed JEI_invoices records to:

• attach the new single-invoice PDF (164788_1u9nlg1233.pdf, 164789_ysfqx8zljx.pdf)
• update source_filename to the new filename 164788 previously had the multi-invoice PDF attached; 164789 had no PDF attached (was page 2 of the multi-invoice file).

Remaining multi-invoice PDFs NOT YET split

The script can handle these, but not run yet (waiting on user):

File	Invoices	PB status
1.Invoice_29.05.pdf	161453, 161454, 161611, 161852	161454 and 161611 have empty pdf_file; 161453/161852 reference other source files
Supplier Invoice.pdf	163841, 163842	163842 has empty pdf_file; 163841 references this file

Splitting these will need either: (a) a follow-up PATCH pass to attach the new per-invoice PDFs to existing PB records, or (b) running import_to_pb.py once it has a PATCH-on-exists option. Currently import_to_pb.py only POSTs new records.

Other parser bugs found this turn (not fixed, per user's "frozen" rule)

1. extract_invoices.py lines 267-282 — sales_order_no parsed but never stored in return dict. Affects every extracted invoice; field always empty. Fix: add "sales_order_no": sales_order_no, to the return dict.
2. import_to_pb.py lines 303-307 — lines_fail lumps "exists" and real "fail" together. No separate lines_exists counter. The reported lines_fail: 215 is mostly idempotent skips, not actual failures.
3. extract_invoices.py deliver_to_address regex — bleeds into footer text on PDFs where pdfplumber puts the deliver-to block and the footer on one continuous span. Cosmetic.
4. PB collection JEI_invoices does NOT expose standard created/updated system fields on records (schema appears custom-imported). Per-record timestamps unavailable for the 19 historically-imported invoices; new system fields would only apply going forward.