pipeline-hygiene

PUBLISHED

137COMMITS

37PULL REQUESTS

4BUILD DAYS

SOURCE: GITHUB COMMIT HISTORY · 2026-08-25

Build cadence — commits per active day

Aug 11 Heaviest day: 59 commits · Aug 13 Aug 14

Overview

Read-only sales pipeline inspection agent: ingests CRM CSV exports into a SQLite snapshot store, runs 11 deterministic hygiene rules (H1–H11) — stale opportunities, slipped close dates, vague next steps, forecast-category mismatches — and scores every opportunity, owner, and desk. Outputs a dated Markdown desk brief with since-last-run diffs, per-owner coaching digests, a pre-forecast-call commit-scrub sheet, and a seven-tab FastHTML dashboard. All JS and CSS are vendored; the dashboard runs fully offline with zero CDN dependencies. 137 commits · 37 PRs · 4 active days.

Project Design

Spec-first: SPEC.md defined the 11 rules, the scoring model, and the data contract before any engine code. The stack — Python, SQLite, FastHTML — was validated via a zero-CDN offline spike before committing to it (headless network capture, zero external requests confirmed). Each major task shipped in its own PR: ingest, snapshot store, rules engine, scoring, desk brief, Streamlit dashboard — then Streamlit was retired and replaced via a parity-gated FastHTML migration. The org simulator (60 synthetic sellers, 4 pathology personas) generated ground-truth data for the manifest integration test that checks the engine against an independent oracle rather than against itself.

CRM CSV → SQLite snapshot store → deterministic rules engine → desk brief + FastHTML dashboard

Ingest

ingest.py (493 LOC)config-driven stage_map — Dynamics and HubSpot presets in config.yamlStrict validation: exits nonzero on missing columns, mixed currency, all-rows-rejectedValidationReport persisted to snapshots table

Storage

SQLite: snapshots, opportunities, runs tablessnapshots.py (388 LOC)Derives close_date_changes and stage_entered_date from consecutive snapshotsEnables H3 serial slippage, H6 stage aging, H11 push analytics

Rules + scoring

11 pure-function hygiene rules H1–H11 (rules.py 257 LOC)Explicit as_of date throughout — date.today() banned inside engineOpportunity / owner / desk scoring — one coverage multiple, one source of truthSandbagging / happy-ears detector (patterns.py 258 LOC)

Outputs

Markdown desk brief with since-last-run diffs (brief.py 1996 LOC)Per-owner coaching digests — private, one file per ownerSeven-tab FastHTML dashboard — binds 127.0.0.1, zero CDN, all JS/CSS vendored

Test infrastructure

24 test files — pytest + hypothesisPer-rule boundary tests + non-circular manifest integration testGolden-file desk brief (byte-for-byte comparison)Streamlit → FastHTML view-model parity gate

Key modules

Data

Ingest

CSV validation and ingestion: enforces required columns, config-driven stage_map resolution (Dynamics and HubSpot presets ship in config.yaml), forecast enum check, date parsing, duplicate opp_id rejection, and single-currency-per-file invariant. Fatal on missing columns, mixed currency, or all-rows-rejected. Per-snapshot ValidationReport is persisted to the snapshots table so the brief and dashboard surface it without re-validating.

Data

Snapshot store

SQLite snapshot store with three tables: snapshots, opportunities, runs. Derives close_date_changes and stage_entered_date from consecutive stored snapshots when source columns are absent — enabling H3 (serial slippage), H6 (stage aging), and H11 (push analytics) from data no single CRM export can provide.

Engine

Rules engine

Eleven pure functions H1–H11, each (row, config, as_of) → Violation | None | InsufficientHistory. date.today() is banned inside the engine — all functions take an explicit as_of date so the same snapshots at the same as_of always produce the same output, enabling byte-for-byte golden-file comparison and a defensible audit trail.

Engine

Scoring

Opportunity, owner, and desk scoring with one required_coverage_multiple source of truth. Coverage = open pipeline / (remaining quota × required multiple); low_coverage fires exactly when the ratio falls below 1.00x, so the shown ratio and the flag cannot contradict each other. The basis string carries the exact fraction to prevent rounding errors from corrupting napkin-check math.

Output

Desk brief

Markdown desk brief with since-last-run diff derived from the runs table. Per-owner coaching digests (--digests) write one private file per owner with only that owner's data. Pre-forecast-call commit-scrub sheet (--commit-scrub) is generated separately. Filtered and scrub runs are never recorded, preventing them from corrupting deal-streak history.

FastHTML dashboard

Seven-tab read-only dashboard built on python-fasthtml 0.14.11 with Altair/Vega-Lite charts and inline SVG sparklines. A pure view model (pipeline_hygiene_view.py, 954 LOC) owns all formatting and chart specs with no UI framework import — the parity boundary that made the Streamlit-to-FastHTML migration a renderer swap, not a rewrite. Binds 127.0.0.1 by default; container mode requires an explicit env var.

Testing

Org simulator

Generates a synthetic 3-level org (60 sellers, 4 personas: clean operator, sandbagger, happy-ears, ghost) with 8+ pathology injectors and a ground-truth manifest. The manifest integration test compares engine output against manifest fields built field-by-field by the generator — never by running the engine — so the oracle is genuinely non-circular.

Key features

CRM-agnostic ingest with a config-driven stage map

Sales teams run different CRM vocabularies — Salesforce stages aren't HubSpot stages aren't Dynamics stages. The ingest layer resolves this through a config-driven stage_map: opportunity stage labels from the CSV are mapped to the engine's canonical stages at ingest time, with presets for Dynamics and HubSpot shipping in config.yaml. Any unrecognised label is a validation error, not a guess. Validation is strict throughout: missing required columns, mixed currencies, and all-rows-rejected all exit nonzero, and the per-snapshot ValidationReport is persisted to the snapshots table so the brief and dashboard can surface it without re-validating. The design rationale in ingest.py:6 is blunt: 'A hygiene tool that silently mis-parses produces false violations and dies of distrust.'

Load CSV and check required columnsResolve stage labels via config stage_mapValidate forecast enum, date fields, currency uniformityExit nonzero on missing columns, mixed currency, or all-rows-rejectedPersist ValidationReport to snapshots tableStore snapshot; derive delta columns (close_date_changes, stage_entered_date) from prior snapshot

Any validation failure exits nonzero before data reaches the snapshot store

Two independent correctness oracles

A test suite that checks code against itself proves consistency, not correctness. The rules engine has two oracles that share no code path. Per-rule unit tests (test_rules.py) pin each rule's exact boundary — at-threshold, one-past, null and empty — without invoking the engine's orchestration layer. The manifest integration test (test_manifest_consistency.py) runs the full engine over a generated org at a known as_of date and compares per-opportunity violations against the seed manifest, which was built field-by-field by the org generator without running the rules. The generator and the engine are independent implementations of the same spec — if they agree, the rules are correct. A third layer adds property tests (hypothesis, test_properties.py): monotonicity asserts that degrading any single opportunity field never increases its score.

Security & ops decisions

Synthetic data only: every name, account, and opportunity in the repo — including the four weekly CSV snapshots committed to data/ — was generated by the org simulator. A grep-based verification checklist in README.md confirms no real CRM data reached the repo.
Upload session isolation: CSVs uploaded via the dashboard are parsed in memory and never written to the snapshot store; upload session expiry was hardened at commit a4556ab (2026-08-12).
Per-owner coaching digests are private by design: --digests writes one file per owner containing only that owner's data, isolated to out/digests/<as_of>/<owner_slug>.md. Published rankings were explicitly rejected to avoid attrition risk.
Azure deploy gates on Entra Easy Auth (Microsoft sign-in) before real data is loaded; the demo mode serves synthetic data and is open by default. No API keys or secrets are committed — the LLM layer is a deferred feature flag (LLM_ENABLED=1) with no implementation in the tracked sessions.

Data isolation layers — upload → validation → snapshot store

User-supplied CSV upload

Ingest validation in memory — rejected files exit nonzero before touching the snapshot store

Upload session expiry (commit a4556ab) — in-process only, never persisted to SQLite

Snapshot store contains synthetic seed data in the open repo; real data requires Entra Easy Auth gate on Azure App Service

Builder notes

The parity gate between Streamlit and FastHTML (tests/parity/test_pipeline_hygiene_parity.py) let me retire one UI framework and introduce another without a regression hunt. The key was extracting pipeline_hygiene_view.py as a pure view model with no UI imports before touching either framework — once that boundary existed, the migration was a diff, not a rewrite.
The coverage math broke a persona simulation by roughly $285K when I used a rounded required multiple instead of the exact fraction. Required coverage multiple is now a single source of truth in scoring.py, and the basis string carries the exact fraction so the shown ratio and the low_coverage flag are derived from identical arithmetic.

Lessons learned

Determinism is a design choice, not a default. Banning date.today() inside the engine and threading explicit as_of dates through every function costs almost nothing to implement and makes the difference between 'it passed yesterday' and 'here is the byte-for-byte output for 2026-08-10.'
Two independent oracles are worth building. A test suite that checks code against itself proves consistency. Checking the engine against a manifest built by an independent generator — without running the engine — proves correctness. The extra work is a one-time cost; the confidence is permanent.
A migration parity gate makes the scary rewrite tractable. The Streamlit-to-FastHTML switch could have been a week of regressions. It wasn't, because the pure view model had no UI imports and diffing its output against frozen Streamlit goldens was a deterministic, automatable check. Extract the parity boundary first, then swap the renderer.
Owner scorecards need a coaching frame, not a ranking. Publishing worst-first league tables raises attrition. Alphabetical sort, a minimum sample threshold before a score is shown, and a 'coaching signal, not a comp input' label are small design choices that change how the output gets used.

What carried forward

Two patterns: the non-circular oracle (independent generator and engine, each checked against the same spec, never against each other) and the parity boundary (a pure view model with no UI imports, so a framework swap becomes a renderer swap, not a rewrite).

Posts from this project

Case study in progress.