CONTEXT
The work started here.
A read-only pipeline inspector needed repeatable coaching outputs from CRM snapshots without turning the dashboard into a ranking tool.
BUILD
What was built.
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
How it was shaped.
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.
Key modules
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.
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.
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.
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.
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.
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.'
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
The operating 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.
BUILDER NOTES
What the build taught.
- 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
What changed afterward.
- 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 CHANGED
The rewrite acquired a parity boundary.
A pure view model and frozen Streamlit outputs made the FastHTML migration a renderer swap that could be checked deterministically.
CONTROL
The constraint that held.
Explicit as-of dates and an independent manifest oracle make output reproducible and test the engine against something other than itself.
EVIDENCE
Where to inspect it next.
- 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.
CARRIED FORWARD
What survived the project.
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).
WRITING
Field notes from this work.
No public field notes for this project yet.