SoftLife Invoice Pipeline

Problem

Distributor invoices arrived as Excel files: 5–15 per day, each with 15–20 line items. The pharmacy owner had to manually type every line into SoftLife—a Windows desktop billing app—before the system would accept a bulk import. This meant 1–2 hours daily of manual entry with no automation, no audit trail, and high error risk.

SoftLife’s bulk import feature demands that every item already exist in its master list. Missing items cause the entire import to fail. New medicines appeared in distributor invoices constantly, forcing the owner to stop, manually create each new item through a slow desktop form, then retry the import. The bottleneck wasn’t just volume—it was the unpredictable stoppage.

The deeper problem: distributor names for items rarely matched SoftLife’s master exactly. A distributor might send “AZTOR 20” while SoftLife’s master contained “AZTOR 20MG TAB 15S”. Simple string matching failed 40–60% of the time, making any automation tricky.

Constraints

SoftLife is a PowerBuilder legacy app built in the late 1990s. It has no API, no batch interface, and no programmatic way to create new items. All automation had to go through the desktop UI via GUI automation.

The physical infrastructure added complexity: the home server (Ubuntu) and the SoftLife machine (Windows) lived on two separate subnets with direct communication blocked. I bridged them with Tailscale, adding SSH as the transport layer.

The UI itself resisted automation. SoftLife uses PowerBuilder DataWindow controls, which aren’t individually addressable via Win32. No Win32 FindWindow/GetDlgItem would work. The only reliable path was keyboard Tab navigation through the form’s field sequence—and discovering that sequence meant reverse-engineering the form from live screenshots.

Architecture & Decisions

I built a three-tier matching cascade that avoids calling an LLM for every item:

Tier 1: Correction cache. Every time a user corrects a fuzzy match, I store it in SQLite. On the next invoice, cache hits run in <1ms at 100% confidence. For a business handling repeat suppliers, this compounds fast.

Tier 2: Fuzzy matching via rapidfuzz. I tokenize both the distributor name and all ~21K master items, then compute token-sort ratios. Score ≥95 auto-matches. Scores 80–94 trigger a manufacturer-name tiebreaker: if multiple candidates exist, I pick the one whose recorded manufacturer name is closest to the invoice’s supplier. This handles common variants like “AZTOR 20” vs “AZTOR 20MG” without LLM cost. Scores <80 become uncertain.

Tier 3: LLM (Google Gemini). Uncertain items and genuinely new items go to Gemini with the top-5 fuzzy candidates as context. The LLM gives a confidence (high/medium) or null. If the LLM fails or disagrees with fuzzy results, we fall back gracefully to the fuzzy tier and flag it for review.

Why not just call the LLM on everything? Cost, latency, and determinism. Most line items are repeats or near-matches that the cache and fuzzy tiers resolve in milliseconds for free, with reproducible results. The LLM only sees the genuinely ambiguous tail — a small fraction of daily volume — which keeps API spend near zero and means a Gemini outage degrades the pipeline to “more items in the review queue” instead of “pipeline down.”

The full pipeline lives on a Linux home server. n8n handles orchestration: webhook ingestion → parse invoice → run Python matching → if uncertain items exist, show a human review form → generate import CSV and new-items JSON → respond to the client. The HITL (human-in-the-loop) gate is critical: uncertain matches never auto-import; they wait for operator confirmation. That keeps data quality intact even when algorithms disagree.

Build Notes

Automating the SoftLife form required discovering its true tab order. I wrote a script that typed numbers into each field to map the sequence. The form has 11 fields: Manufacturer, Item Name, Packing, QtyPack, Major Content, Group, Category, Schedule, BarCode, Rack, and GST HSN. Many are optional or read-only; some require combo-box validation on Tab.

The pywinauto automation clicks the DataWindow to focus it, then Tabs through all 11 fields, typing values with space-to-{SPACE} escaping to preserve spacing in product names. A screenshot on failure captures what went wrong. The CLI command softlife create-items <json> loops through pending new items and fills the form end-to-end, auto-saving each.

I built 116 tests: parsing edge cases (duplicate invoices, malformed dates), matching logic (cache hits, fuzzy thresholds, LLM fallback), output CSV generation, and database schema. All pass. The test suite made refactoring safe and caught bugs like a casing mismatch in the LLM confidence comparison (== "high" vs .lower() == "high").

Outcome

The pipeline deployed to production in April 2026 and runs daily. The owner uploads invoices via an n8n webhook. The system returns a review report; uncertain matches wait for operator confirmation; then import CSV and new-item JSON are ready for bulk import. Typical flow: upload → 3–5 seconds to match → review gate → download files → 30 seconds to bulk import into SoftLife.

Measured impact: eliminated 1–2 hours of daily manual typing. New items no longer block the import; they queue as a JSON list for creation in SoftLife. Correction cache means recurring items match even faster on subsequent invoices. The business runs the pipeline 5–6 days per week without interruption.

What I’d Do Differently

I’d instrument the LLM tier with detailed logging from the start. Early in production, I caught a bug (casing mismatch) only because an uncertain match landed in the review queue and I happened to notice the LLM confidence was always “uncertain.” Better telemetry—LLM call counts, confidence distributions, fallback frequency—would have surfaced that issue immediately. I’d also add a photo-based invoice workflow sooner; the current design supports it (Gemini Vision is already tier 3), but I built it last rather than as part of the core flow. Separating photo and text invoices into two separate n8n workflows added operational complexity.