# Quality Checker — Resume & Portfolio Technical Brief ## User-confirmed context — 2026-10-18 > Source: direct user answers in the portfolio review. These are user-confirmed statements, not independently rerun repository or production measurements. - **Ownership:** independently built the entire application. - **Operational adoption:** more than 5 people (6+ users), including managers and team leaders; used at least 2-3 times weekly to check Excel tracking files. - **Typical scope:** hundreds of rows and more than 20 columns per workbook. Manual checks took hours and Excel formulas alone did not cover the required checks. - **Timing:** approximately 5-10 minutes per file with the application, depending on file size, compared with hours of manual checking. These are user-reported estimates, not a controlled benchmark. - **Observed result:** routinely finds actual data-quality issues. No issue count or measured error-reduction percentage is available. > Existing draft variants and unanswered placeholders are retained. This confirmed context governs personal ownership, adoption and outcomes; unresolved draft claims are not newly verified. > Every claim below is taken from the repository at commit `df8f328` (`main`). File references point to > `quality_checker/src/quality_checker/...` unless stated otherwise. Numbers marked **[verified]** were > measured by running the project's own test suite and coverage configuration. --- ## 1. High-Level Project Overview ### One-Line Pitch A Python/PyQt6 desktop application that runs data-quality checks on the large Excel workbooks used in automotive supplier onboarding and EDI setup. It annotates every row with a failure count and an explanation, writes back to the original workbook without losing its formulas, styles, tables, or Excel extensions, and records every run in a local SQLite audit history. ### Problem Supplier onboarding is tracked in shared Excel workbooks maintained by hand. The **SOM** tracker is a single sheet. The **eDCT** workbook has more than 11 worksheets, with `Supplier Level`, `PN Level`, `Open Task`, and `Template-Cofor-Creation` linked by punch codes. These workbooks contain: - Free-text email lists, `DD/MM/YYYY` dates that are sometimes typed and sometimes native Excel dates, and COFOR supplier codes in a strict `6 chars + 2 spaces + 2 chars` format. Excel non-breaking spaces make that format harder to check. - Cross-sheet invariants. Examples: a PN triplet must belong to its seller's allowed set on another sheet, and `OPEN TASK` must be `YES` exactly when the punch code appears on the `Open Task` sheet. - Formula columns that users can overwrite or break by accident. - Table definitions, data-validation extensions, and cached formula results that normal Python Excel libraries drop when they save a file. Manual review was slow and inconsistent, and it left no audit trail. ### Solution & Architecture - **Pluggable checker modules.** `checkers/som/` is a vectorized pandas pipeline. `checkers/edct/` is an openpyxl cell-level pipeline that works across sheets. They share one result contract, `Check` (an integer failure count) and `Comment` (a readable reason), and one run-history repository (`db/`). - **Lossless export.** The eDCT exporter saves only the annotated worksheets, repairs their XML, and splices them back into the original `.xlsx` ZIP package. Everything else is copied from the source byte for byte. - **Responsive desktop UX.** Heavy work runs on `QThread` workers, and eDCT work goes further into a separate OS process (`ProcessPoolExecutor`). Progress messages are streamed back to the UI through a cross-process queue. - **Configurable header mapping.** A JSON settings file and a Settings screen map business column names to the physical headers in each workbook. Header resolution is case- and whitespace-insensitive, supports aliases, and rejects ambiguous matches. - **Ships as a single Windows `.exe`** via PyInstaller (`build_exe.ps1`). It is frozen-aware: data goes to `%LOCALAPPDATA%`, bundled assets are found through `sys._MEIPASS`, and `multiprocessing.freeze_support()` is called. --- ## 2. Comprehensive Tech Stack & Tooling ### Languages & Runtimes | Item | Evidence | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------- | | **Python 3.12** (`requires-python >=3.12`, pinned in `.python-version`) | `pyproject.toml`, `quality_checker/pyproject.toml` | | Modern Python features: `from __future__ import annotations`, `@dataclass(slots=True, frozen=True)`, PEP 604 unions, walrus operator, `datetime.UTC`, parenthesized multi-`with` | Throughout `checkers/`, `db/` | | **PowerShell** (build automation) | `build_exe.ps1` | | **Node.js** (developer tooling only) | `package.json` | | **Jupyter Notebook** (original prototype of the SOM rules) | `som_analyzer.ipynb` | ### Frontend / UI (desktop) | Item | Usage | | -------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **PyQt6 ≥ 6.7.1** | `QMainWindow` with a `QStackedWidget` that routes between pages, `QListWidget` sidebar menus, `QTableWidget` previews and history grids, `QFileDialog`, `QMessageBox` | | **Qt signals/slots + `QThread`** | `pyqtSignal`-based `AnalysisWorker` / `InspectionWorker` moved to worker threads (`gui/workers.py`) | | **Custom widgets** | `CheckableComboBox` (multi-select filter with "All" sync logic) and `ResponsiveColumns` (switches layout direction at a 900 px breakpoint) in `gui/widgets.py` | | **QSS theming** | 268-line centralized stylesheet (`gui/styles.py`) with state-driven status styling via `objectName` swaps (`statusNeutral/Progress/Success/Error`) | | **Navigation** | Project selector → per-checker shell (sidebar + workspace header + stacked pages) built by the `_create_checker_shell` factory | ### Backend / Processing Engine | Item | Usage | | ---------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **pandas ≥ 3.0.2** | SOM loading, string normalization, boolean fail masks, `groupby().nunique()` conflict detection, scope masks, `to_excel` export | | **openpyxl ≥ 3.1.5** | eDCT cell-level reads, a dual `data_only=False` / `data_only=True` load for cached formula values, `read_only=True` inspection, `openpyxl.formula.translate.Translator` for formula translation, table (`TableColumn`) manipulation | | **Standard library OOXML tooling** | `zipfile` + `xml.etree.ElementTree` for raw package and part surgery (`checkers/edct/ooxml.py`, `export.py`) | | **Concurrency** | `concurrent.futures.ProcessPoolExecutor`, `multiprocessing.Manager().Queue()`, `QThread` | | **Interfaces** | No network API. Interfaces are a GUI plus two console entry points: `quality-checker` (GUI) and `quality-checker-smoke` (an `argparse` CLI) | ### Database & Storage | Item | Usage | | --------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------- | | **SQLite** (`sqlite3`, raw SQL, no ORM) | Run history: `runs` and `run_columns` tables with an `ON DELETE CASCADE` foreign key and an index on `run_columns(run_id)` (`db/schema.py`) | | **Hand-written schema migrations** | `PRAGMA table_info` introspection, `ALTER TABLE ADD COLUMN`, and a full table-rebuild migration to relax a `NOT NULL` constraint (`db/repository.py`) | | **JSON settings store** | `edct_header_mappings.json` with merge-over-defaults deserialization (`checkers/edct/settings.py`) | | **Environment-aware storage paths** | `quality_checker/data/` during development, `%LOCALAPPDATA%\Quality Checker\` when frozen (`application.py`) | ### DevOps, Testing & Tooling | Item | Usage | | --------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------ | | **uv** (+ `uv.lock`) | Reproducible environments and command runner. Also used for ephemeral build dependencies (`uv run --with pyinstaller --with pillow`) | | **Hatchling** | PEP 517 build backend. `src/` layout packaging | | **PyInstaller** | `--onefile` / `--onedir`, `--windowed` Windows executable with an icon and bundled data (`build_exe.ps1`) | | **unittest** (+ `unittest.mock.patch`, `MagicMock`) | 112 tests across 10 modules. GUI tests run headless via `QT_QPA_PLATFORM=offscreen` | | **coverage.py** | Branch coverage enabled, `fail_under = 100` configured | | **Ruff** | Linter (rule sets `E4, E7, E9, F, I, B, UP`) and formatter, line length 100 | | **ty** (Astral type checker) | `npm run typecheck` over the checkers, db, application, and smoke modules | | **Prettier** | Formatting for Markdown, JSON, TOML, and YAML | | **Husky + lint-staged** | Pre-commit hook runs lint-staged (Ruff fix + format, Prettier), then the type check, then the full test suite | | **CI/CD** | No hosted CI workflow is in the repo. Quality gates are enforced locally through the pre-commit hook and `npm run check` | | **Docs-as-code** | `CONTEXT.md` (ubiquitous-language glossary), `docs/EDCT_QUALITY_CHECKER.md`, `docs/SOM_QUALITY_CHECKER.md` | --- ## 3. Architecture & Technical Highlights ### 3.1 Lossless OOXML round-trip export (`checkers/edct/export.py`, `checkers/edct/ooxml.py`) openpyxl cannot preserve everything in a real-world workbook. It drops unknown `` extensions (for example, modern data validations), rewrites namespaces, and loses cached formula results. The exporter works around this with a **package-level splice**: 1. Add the `Check`/`Comment` columns. On `Supplier Level`, this grows table `Tabella2` by appending `TableColumn`s and updating `ref`/`autoFilter.ref`. On `PN Level`, the columns are placed after the rightmost data column or table, stale annotations are cleared, and existing tables are not resized. 2. Save **only the 3 annotated worksheets** to a temporary workbook by temporarily swapping `workbook._sheets`, restoring it in a `finally` block. 3. Resolve the physical part names (`xl/worksheets/sheetN.xml`, table parts) by walking `workbook.xml.rels` and per-sheet `.rels` files. 4. Repair each regenerated XML part from the source: - restore `extLst` blocks structurally, and also by `id`/`name` identity; - restore cached formula `` values and the `t` type attributes; - re-register the original namespace prefixes and prune `mc:Ignorable` to the namespaces still in use; - keep the original table `id`. 5. Rebuild the final ZIP by copying **every source part verbatim** except the replaced ones. 6. **Verify** the output by re-opening it read-only and touching every cell. A corrupt output is deleted rather than handed to the user. Recorded result in `docs/EDCT_QUALITY_CHECKER.md`: on the sample file, the export kept all 11 worksheets and all 16 XML parts that contain extension lists, and the source file hash was unchanged. ### 3.2 Two-tier concurrency: QThread + subprocess with progress streaming (`gui/workers.py`, `gui/pages/edct.py`) - openpyxl parsing is pure-Python and CPU-bound, so threads alone would still compete with the UI for the GIL. eDCT inspection and analysis therefore run in a **`ProcessPoolExecutor(max_workers=1)`**, which is driven from a **`QThread`** worker. - Progress is a `Callable[[str], None]` that is passed through every engine layer (`runner`, `export`, `workbook`). In the subprocess it is bound to a `multiprocessing.Manager().Queue().put`. The thread polls the queue with a 100 ms timeout, then drains it, and re-emits each message as a Qt signal. Stage messages: _Loading workbook → Checking PN Level → Checking Supplier Level → Preparing results → Exporting workbook → Verifying export_. - An `openpyxl.Workbook` cannot be pickled, so the subprocess returns a small frozen DTO, `EdctDisplayResult` (run id, counts, the first `PREVIEW_ROWS = 25` rows), and closes the workbook in a `finally` block. - Lifecycle safety: re-entrancy guards (`if self._run_thread is not None: return`), `deleteLater` cleanup, and `MainWindow.closeEvent → wait_for_workers()`. `freeze_support()` keeps multiprocessing working inside the PyInstaller executable. ### 3.3 Strategy-pattern rule engine with a vectorized evaluation path (`checkers/som/validator.py`, `runner.py`) - `ValidationRule(ABC)` has 8 concrete strategies, which are instantiated as **10 named rules** by `build_default_rules()`. Every rule returns the same `RuleResult(fail_counts, row_messages, column_fail_counts)`. - The runner applies a **scope mask** (`build_scope_mask`, which can normalize and case-fold), evaluates only in-scope rows, **sums the fail series** into `Check`, builds `Comment` by joining non-empty rule messages with `|`, and tags the remaining rows `Out of filters`. - Cross-row integrity: `CoforAddressRule` normalizes keys (trim, collapse whitespace, case-fold) and uses `groupby("cofor")["address"].nunique() > 1` to flag every row of a COFOR code that has conflicting addresses. - Time-relative rule: `RelanceRule` takes an injectable `reference_date`. It extracts every `DD/MM/YYYY` date from free-text `NOTE` and enforces an inclusive **3-day** freshness window, which keeps the rule deterministic under test. ### 3.4 Cross-sheet relational validation and formula-integrity checking (`checkers/edct/validator.py`, `pn_validator.py`, `template_validator.py`) - **Formula drift detection.** The first assessed row is the reference row. For each of the 9 formula columns, the reference formula is translated to each later row with openpyxl's `Translator`. Both formulas are then compared after canonicalization: case-folding outside string literals, removing whitespace outside quotes, and treating a leading `=+` as `=`. - **Allowed-set authorization.** `defaultdict(set)` builds punch code → allowed `Triplet COFOR`s from assessed supplier rows only. Each PN row's triplet must belong to its seller's set, and the failure message lists the sorted allowed values. - **Cached-value resolution.** The workbook is loaded twice, once with formulas and once with `data_only` cached values. The raw sheet XML is also scanned to tell a _saved empty string_ (`t="str"` with an empty ``) apart from a _missing cache_. Error results such as `#REF!` stop the run early with the exact `Sheet!A1` location and how to fix it. - **Identifier normalization.** Excel stores numeric codes as floats, so `normalized_punch` turns `"12345.0"` back into `"12345"`. Non-breaking spaces are converted to regular spaces in COFOR and triplet values. Leading zeros are never coerced. - Bidirectional consistency rules: `OPEN TASK` is cross-checked against the `Open Task` sheet, the request date against `Template-Cofor-Creation`, and the template company name against the supplier name. - **18 tracked rule IDs**, each recorded per `(rule, column)` in a `Counter`. ### 3.5 Schema-validated, alias-aware workbook boundary (`checkers/edct/workbook.py`, `settings.py`, `gui/pages/edct.py`) - `_resolve_workbook_structure` checks 4 required sheets and their required headers on sheet-specific header rows. Matching is case- and whitespace-insensitive, uses configured mappings and explicit aliases (`Index↔Line`, `Supplier Confimation↔Supplier Confirmation`), and **rejects ambiguous matches**. All problems are reported together in one message (missing sheets, missing columns, ambiguous headers). - `EdctHeaderSettings.validate()` checks the mapping itself for collisions before any workbook is opened. `from_dict` merges onto the defaults and ignores keys with the wrong type, which makes the settings file forward-compatible. - Workbook selection runs a **read-only, out-of-process pre-flight inspection**. The Run button is enabled only after it succeeds, and the UI shows how each business column was resolved, for example `canonical → physical (alias)`. - The Settings screen reads the headers of a sample workbook and pre-selects the best match in each editable `QComboBox` (exact, then current value, then case-insensitive). ### 3.6 Auditable run history with self-healing migrations and failure semantics (`db/`, `checkers/*/runner.py`) - Every analysis writes a `runs` row (project, timings, row counts, status, error) and per-rule `run_columns` totals. Failed runs are recorded too: the `except` branch writes `status="failed"` before re-raising. - Export is a separate phase. A successful export updates `exported_file`. A failed export marks the run `failed` through a connection-strategy helper (`_update_result_history`), which supports both injected and default connections. - `initialize_schema` is idempotent. It runs `CREATE IF NOT EXISTS`, then two in-place migrations: it adds `project` to support multiple checkers, and it **rebuilds** `runs` and `run_columns` with foreign keys temporarily disabled to make `exported_file` nullable. Data and indexes are preserved. - Dependency injection: every runner accepts an optional `sqlite3.Connection`. Tests use this to run against an isolated database. --- ## 4. Quantifiable Metrics & Scope (From the Codebase) | Metric | Value | Source | | ------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------- | | Application source | **~4,350 LOC** across 36 Python modules (2,742 coverage statements) | `quality_checker/src/` | | Test code | **~3,240 LOC** across 11 files | `quality_checker/tests/` | | Automated tests | **112 tests, all passing** [verified] (`Ran 112 tests ... OK`) | `npm test` equivalent | | Coverage (branch mode) | **~95% statement coverage of application source** [verified]; **100%** on `checkers/som/validator.py`, `som/runner.py`, `edct/runner.py`, `db/repository.py`, `gui/history.py`, `gui/widgets.py`. The configured gate is `fail_under = 100`. `gui/workers.py` reads 56% only because code running in subprocesses is not instrumented | `coverage report` | | API endpoints | **0 HTTP endpoints** (desktop app). **2 console entry points** (`quality-checker`, `quality-checker-smoke`) and **5 npm scripts** (`lint`, `format`, `typecheck`, `test`, `check`) | `pyproject.toml`, `package.json` | | Database tables | **2** (`runs` 1→N `run_columns`, `ON DELETE CASCADE`), **1** index, **2** migrations | `db/schema.py`, `db/repository.py` | | UI pages / views | **5 page classes → 6 screens** (Project selection, SOM analysis, eDCT analysis, eDCT Settings, History ×2) | `gui/pages/` | | Reusable UI components | **2 custom widget classes** + **5 UI factory functions** + **2 Qt worker classes** | `gui/widgets.py`, `gui/workers.py` | | Validation rules | **SOM: 10 rules** (8 strategy classes). **eDCT: 18 tracked rule IDs** | `som/validator.py`, `edct/*validator.py` | | Workbook schema | **4** required eDCT sheets, **47** configured `Supplier Level` columns, **6** PN columns, **45** explicitly unchecked legacy columns, **13** SOM columns | `edct/config.py`, `som/config.py` | | Real-data smoke result | **112 assessed rows / 33 failed**. **11 worksheets** and **16 extension-bearing XML parts** preserved, with the source hash unchanged | `docs/EDCT_QUALITY_CHECKER.md` | | Tunables visible in code | Preview `PREVIEW_ROWS = 25`. Progress-queue poll timeout **0.1 s**. History query `LIMIT 200`. Responsive breakpoint **900 px**. Relance window **3 days**. Subprocess pool `max_workers=1`. Window 1180×760 (min 980×660) | `application.py`, `workers.py`, `repository.py`, `widgets.py`, `som/validator.py`, `main_window.py` | | Commit history | **50 commits**, Conventional-Commit style (`feat(edct): ...`) | `git log` | --- ## 5. Ready-to-Use Resume Bullet Points (XYZ Format) ### Variation 1 — Full-Stack / General Software Engineering - **Independently built** a PyQt6 data-quality platform used by **6+ managers and team leaders**, **2-3 times weekly**, to check Excel trackers with **hundreds of rows and 20+ columns**. Checking takes approximately **5-10 minutes per file**, depending on size, compared with **hours of manual review**; it routinely detects real data-quality issues. It applies **28 business rules** (10 SOM, 18 eDCT) across 4 linked Excel worksheets and annotates every row with a failure count and a readable explanation. - **Shipped** the tool as a single-file Windows executable used by **6+ managers and team leaders**, with no install step. I automated a PyInstaller build through uv and made the runtime frozen-aware (`sys._MEIPASS`, `%LOCALAPPDATA%` storage, multiprocessing `freeze_support`). - **Maintained** a **112-test suite with ~95% branch-mode coverage** of application source. I enforced quality locally with a Husky pre-commit pipeline (Ruff lint and format, Prettier, Astral `ty` type checking, full unittest run), with headless Qt GUI tests. - **Created** an auditable SQLite run history that logs **100% of analysis runs, including failures**, with per-rule and per-column failure totals. This lets QA leads track data-quality trends **[e.g., failure rate down X% over N weeks]**. ### Variation 2 — Backend / Systems Architecture - **Engineered** a lossless Excel export that preserved **all 11 worksheets and 16 extension-bearing XML parts** of production workbooks, with an unchanged source hash. I spliced the openpyxl-generated sheet XML into the original OOXML ZIP package and restored the `extLst` blocks, namespaces, and cached formula values that openpyxl drops. - **Designed** a pluggable validation architecture with two checker modules (pandas-vectorized SOM, openpyxl cell-level eDCT) on one shared `Check`/`Comment` result contract and history repository. This made a new checker **[X]× faster to add** through a Strategy-pattern `ValidationRule` hierarchy and centralized rule configuration. - **Implemented** cross-sheet relational validation over **[N thousand] rows**. This included set-based authorization of PN triplets per supplier, bidirectional `Open Task` and template consistency checks, and formula-drift detection that translates the reference formula to each row and compares canonical forms. - **Built** self-healing SQLite schema migrations (column addition and a table rebuild that relaxes a `NOT NULL` constraint with foreign keys temporarily disabled), so **[N] existing installs** upgraded with **zero data loss**. ### Variation 3 — Frontend / Product Engineering - **Kept the UI responsive during [X-second] workbook analyses** (**0 frozen frames**). I moved CPU-bound openpyxl work into a subprocess driven by a `QThread`, and streamed **6 progress stages** back to the UI through a multiprocessing queue bridged to Qt signals. - **Reduced workbook-rejection support requests by [X%]** with a read-only pre-flight inspection that validates sheets and headers before analysis is enabled. It reports every missing or ambiguous header in one message and shows how each business column was resolved, including aliases. - **Built** a self-service header-mapping Settings screen and reusable PyQt components (`CheckableComboBox` multi-select filters, `ResponsiveColumns` with a 900 px breakpoint, a themed QSS design system). This let users adapt the tool to **[N] workbook variants** without code changes. --- ## 6. Interview Prep: Deep-Dive Talking Points ### Problem 1 — "openpyxl corrupts our workbooks": lossless Excel round-tripping **Files:** `checkers/edct/export.py` (`_preserve_ooxml_extensions`, `_merge_annotated_parts`, `_validate_analysis_workbook`), `checkers/edct/ooxml.py` - **Situation:** Users needed the checker's `Check`/`Comment` columns written back **into their real workbook**, not a flattened copy. The SOM path uses `DataFrame.to_excel`, which loses formulas and formatting. A plain openpyxl `save()` silently dropped unsupported `` extensions such as data validations, rewrote namespace prefixes, and lost cached formula values. Excel would then show repair prompts, or the next analysis run would fail. - **Task:** Annotate 3 worksheets, including growing an Excel table (`Tabella2`), and leave every other byte of the package untouched. The output also had to be safe to analyze again. - **Action:** I treated the `.xlsx` as a ZIP of XML parts rather than trusting the library's save: - Let openpyxl write **only** the annotated sheets to a temporary workbook, by temporarily swapping `workbook._sheets` and restoring it in `finally`. - Map logical sheets and tables to physical parts by resolving `workbook.xml.rels` and the sheet `.rels` files. - Patch each regenerated part from the source with `ElementTree`: restore `extLst` both structurally and by `id`/`name` identity, restore cached `/` formula values and type attributes, re-register the original namespace prefixes, and prune `mc:Ignorable`. - Rebuild the final ZIP by copying every non-replaced part verbatim, re-open the result read-only as a verification pass, and delete the output on any failure, using temp files and `finally` cleanup throughout. - **Trade-off:** This depends on OOXML internals and touches a private openpyxl attribute. I accepted that cost because a corrupted workbook was not acceptable, and I covered the behavior with 16 export tests. - **Result:** On the production sample, all **11 worksheets** and all **16 extension-bearing XML parts** were preserved, the **source hash was unchanged**, and the exported file **can be analyzed again**. ### Problem 2 — Keeping a PyQt UI responsive during CPU-heavy Excel processing **Files:** `gui/workers.py` (`_run_edct`, `_inspect_edct`, `EdctDisplayResult`), `gui/pages/edct.py`, `gui/main_window.py`, `gui/app.py` - **Situation:** Loading a large multi-sheet workbook twice (formulas plus cached values), validating it, and exporting it took long enough to freeze the window. A `QThread` alone was not enough, because openpyxl is pure Python and holds the GIL. - **Task:** Keep the UI responsive, show truthful stage-by-stage progress, survive window close mid-run, and keep working inside a PyInstaller one-file executable. - **Action:** - Two tiers: a `QThread` worker owns a `ProcessPoolExecutor(max_workers=1)`, and the engine runs in that separate process. - A plain `progress: Callable[[str], None]` is passed through the engine layers, so the engine has no Qt dependency. In the subprocess it is bound to `Manager().Queue().put`. The thread polls with a 0.1 s timeout, drains the queue at the end, and re-emits each message as a Qt signal. - The `Workbook` cannot be pickled, so the subprocess returns a frozen DTO (`EdctDisplayResult`) with only the preview rows. Double-click re-runs are guarded, workers are cleaned up with `deleteLater`, `closeEvent` calls `wait_for_workers()`, and `freeze_support()` is called for the frozen build. - **Trade-off:** Process start-up and IPC add overhead, which I accepted to get real CPU isolation. `max_workers=1` keeps it simple, since one analysis runs at a time. - **Result:** The UI stays interactive through 6 visible stages (_Loading → PN Level → Supplier Level → Preparing → Exporting → Verifying_). The same pattern also serves the pre-flight inspection and the Settings sample-header reader. Measured improvement: **[X s] perceived freeze → 0 s**. ### Problem 3 — Validating formulas and cross-sheet identifiers without a calculation engine **Files:** `checkers/edct/validator.py` (`_formula_key`, the `Translator` block), `checkers/edct/pn_validator.py` (`resolved_value`, the empty-cache XML scan), `checkers/edct/workbook.py` (`normalized_punch`) - **Situation:** Two business rules depended on formulas. First, 9 computed columns had to keep the same formula on every row, but users overwrite cells or paste values. Second, PN triplets had to be checked against suppliers' allowed sets, and those identifiers are themselves **formula results**. Python cannot evaluate Excel formulas, and Excel stores numeric codes as floats (`12345.0`). - **Task:** Detect formula drift and validate formula-derived identifiers reliably, without reporting false failures or silently passing bad data. - **Action:** - **Formula drift:** Take the first assessed row as the reference. Use openpyxl's `Translator` to shift the reference formula's relative references to each target cell. Compare canonical forms: case-fold outside string literals, strip whitespace outside quotes, and normalize a leading `=+`. If the reference cell has no formula, every row fails with a clear message instead of the rule being skipped. - **Cached values:** Load the workbook a second time with `data_only=True`. Scan the raw sheet XML to tell a saved empty string apart from a missing cache. Stop early on `#REF!`-style errors or missing caches with the exact `Sheet!Cell` and the fix ("recalculate and save in Excel"). The check is lazy, so unknown sellers never require a cached value. - **Identifier normalization:** `12345.0 → 12345`, NBSP → space, case-insensitive matching. Internal whitespace and leading zeros are kept on purpose, so no numeric coercion can corrupt an identifier. - **Trade-off:** I chose to trust Excel's saved results rather than embed a formula engine. The cost is a fail-fast requirement that the user recalculates before analysis, which I made explicit in the error message. - **Result:** Deterministic, explainable failures. Each comment names the seller, the rejected triplet, and the sorted allowed set, and each PN row fails at most once. The behavior is covered by the PN (6) and rule (13) test modules, and the full suite has **112 tests**. --- ### Quick-reference: likely follow-up questions - _Why raw SQL instead of an ORM?_ There are 2 tables, it runs on a single-user desktop, and there are no extra dependencies. Migrations use `PRAGMA table_info` introspection and stay small and explicit. - _Why pandas for SOM but openpyxl for eDCT?_ SOM is a flat, single-sheet result, so vectorized masks are fast and simple. eDCT needs cell coordinates, formulas, tables, and lossless write-back, which a DataFrame cannot represent. - _How do you keep rules, docs, and history consistent?_ Each rule writes to `Check`, to `Comment`, and to a `(rule, column)` `Counter` that is stored in `run_columns`. `CONTEXT.md` defines the shared vocabulary, for example "Assessed row", "Validation failure", and "Formula reference row".