Progress Reports

Stage I Report — Discovery & data foundation (Weeks 1–4)

We started Stage I with no code, just a problem statement: PIs and grant administrators reconcile the same budget across an ISU One Page Budget spreadsheet, a Streamlyne PDF summary, and an agency-specific Word justification. Our first job was actually to understand what those artifacts contain. We collected four real proposal fixtures from the client (COMPLEX, PD5579, PD5639, PD7040), studied the row layout of the Excel summary sheet (header rows, year columns, sections A–I, and the Travel/Tuition detail sheets), and read enough of the PDF outputs to identify the section boundaries we'd need to match.

The bulk of Stage I was building two parsers from scratch. ExcelExtractor reads the One Page Budget into a normalized list of line items (description, section, per-period values, total). PDFExtractor does the same for the Streamlyne PDF using pdfplumber, with regex-driven section markers and row grouping. By the end of Stage I, both extractors produced consistent, comparable outputs for all four fixtures, and we wrapped a thin FastAPI application around them with /api/extract/excel and /api/extract/pdf endpoints so the client could see results immediately.

We also used Stage I to set up the engineering scaffold we'd lean on for the rest of the semester: a Pydantic model layer in app/models/, a pytest suite seeded with example-based regression tests, a Dockerfile, GitLab CI, and a MySQL schema for persisting attempts. The deliverable at the end of Stage I was a deployable, testable backend that could ingest both source documents and produce structured JSON — enough to demonstrate to the client that the data was tractable, even though we hadn't yet shipped any user-visible value.

Stage II Report — Validation pipeline (Weeks 5–7)

Stage II was about turning the parsed data into something useful: a validation report that tells you whether the Excel and the PDF agree. We designed the BudgetValidator as a three-phase matcher. Phase 1 looks for exact value fingerprints (description + period totals). Phase 2 relaxes to tolerance-aware comparisons that absorb small rounding differences. Phase 3 falls back to a similarity score driven by section bucketing and string distance. Each line item ends up classified as match, mismatch, or missing, with a per-period delta where applicable.

Once the algorithm was running, we wired it into a real end-to-end pipeline: validation_service.py orchestrates upload → extract Excel → extract PDF → match → report → persist, and /api/validate returns a structured JSON the frontend can render. Two adjacent services made the report substantially more useful: highlighter.py produces a highlighted Excel and a highlighted PDF that visually mark mismatches, and mismatch_report_generator.py emits a .docx review document that grant administrators can attach in their workflow without screen-grabbing anything.

The Stage II demo at Week 7 was the first time the client saw the system catch real bugs. Running our own algorithm against historical proposals surfaced two genuine inconsistencies that had been shipped before, which validated the approach and let us tune tolerances against ground-truth data. We finished the Stage with a green CI, ~75% line coverage on the matching code, and a stabilization release tagged for the next Stage to build on.

Stage III Report — Justification generation (Weeks 8–11)

Stage III shifted the project from "checking budgets" to "writing budgets." The goal was to generate the narrative budget justification Word document directly from the spreadsheet. We built the ExcelMetadataExtractor to lift the rich metadata the validator didn't need — key/other personnel, fringe benefit rates, travel detail rows, tuition blocks, IDC type, and per-year start/end dates — and we modeled it with a strongly-typed BudgetMetadata dataclass family in app/models/justification.py.

On top of that data, we wrote four template generators behind a common base class: Generic, NIH, USDA, and NSF. Each generator emits a complete .docx using python-docx, with the actual dollar amounts substituted in (Year 1 / Year 2 / Total breakdowns, escalation rates, tuition by class and term, travel by trip), and with placeholder narrative inserted everywhere a human is expected to author copy. The templates also enforce agency-specific structure: NIH's eight ODC sub-sections, NSF's lettered A–J layout with participant support, USDA's MATCH section, and so on. We added compliance advisories — most importantly, the NIH salary cap check and the NSF voluntary committed cost-sharing flag — that surface as "Compliance Notes" at the end of the document.

The hardest engineering work in Stage III was iterating with the client on what "good enough" copy looks like in each agency's voice. Brian provided a revised boilerplate (the 2026 revision) that introduced a Description(s) column on the summary sheet for per-personnel role descriptions and per-trip lead-in sentences. We extended the metadata extractor and all four template generators to read that column and prefer human-authored prose over our placeholder text when present. By the end of Stage III, the system could regenerate every fixture in our examples/ folder into all four agency formats, with regression tests that diff the output against checked-in golden documents.

Stage IV Report — Versioning, CLI, viewer, and polish (Weeks 12–14)

Stage IV was about taking the system from "demo" to "something the client can actually use day-to-day." The biggest piece was history and versioning: every validation and every justification attempt now persists in MySQL with a group_id that links related runs, an integer version, a version_state (draft / approved/archived), and a parent pointer. Users can re-run a prior attempt against a corrected Excel, and the system creates a new version inside the same thread; they can mark one version as "approved" and the others auto-archive; and we added a diff_versions API that produces a structured diff (summary deltas, report deltas, cell-level Excel deltas) between any two versions in the same thread.

We shipped two new frontends in Stage IV. The web viewer (app/static/, viewer.js) renders the Excel as a live grid with HyperFormula so users can edit cells, recalculate, and re-validate without leaving the browser. The CLI (budget-tool via app/cli.py, packaged as an editable Python package) wraps the same services for scripting and CI integration — extract, validate, justify, and download artifacts from the command line. Authentication is handled by Google OAuth (app/routes/auth.py, app/services/auth_service.py), gated to ISU email addresses, with audit logging and refresh middleware on the cookie path.

The final week was hardening: we modularised the four largest services (budget_validator, excel_metadata_extractor, history_service, justification_generator, pdf_extractor) into packages with backward-compatible shim modules, brought the test suite up to ~85% on the core paths, finished docs/ (this page included), and prepared the Azure deployment plan in docs/AZURE_HOSTING_PLAN.md. The Stage IV deliverable is a deployable web app + CLI with persistence, versioning, and a justification generator covering four agencies — the full system as it stands today.

 

Meeting minutes

Each meeting is a 30-minute sync with the client. Minutes are intentionally short — what we showed, what we agreed, and what's next.

Week 1 — Kickoff & scope (Jan 21)

  • Reviewed problem statement: Excel ↔ PDF reconciliation + narrative justification.
  • Client provided 4 real proposal fixtures (COMPLEX, PD5579, PD5639, PD7040).
  • Agreed on FastAPI backend + Python; client OK with MySQL for persistence.
  • Action items: stand up repo, set up CI/Docker, start Excel parser.

Week 2 — Excel parser walkthrough (Jan 28)

  • Demoed first pass of ExcelExtractor on the One Page Budget.
  • Client clarified Year column conventions, IDC labels, and the role of Section H/I.
  • Discussed which sheets matter (summary + Travel + Tuition).
  • Action items: handle missing periods; support .xlsm; start PDF parser.

Week 3 — PDF parser & joint output (Feb 4)

  • Walked through PDFExtractor on Detailed Summary SL.pdf.
  • Identified 3 layout edge cases (multi-line descriptions, total row at bottom of page).
  • Aligned on a common line-item shape for both extractors.
  • Action items: regression-test parser on all 4 fixtures; design validator API.

Week 4 — End of Stage I review (Feb 11)

  • Showed /api/extract/excel and /api/extract/pdf returning normalized JSON.
  • Stage I demo signed off. Agreed, Stage II is the matching algorithm.
  • Discussed acceptable rounding tolerance ($1, ~0.5%).
  • Action items: write Stage I report; start BudgetValidator.

Week 5 — Matching algorithm v1 (Feb 18)

  • Showed Phase 1 (exact fingerprint) and Phase 2 (tolerance) matching working on 3/4 fixtures.
  • PD5639 had two lines flagged that turned out to be a real client-side bug.
  • Client confirmed tolerance defaults; asked for a per-period delta.
  • Action items: add Phase 3 (similarity); generate report JSON; start highlighter.

Week 6 — Highlighting + report (Feb 25)

  • Demoed highlighted Excel + highlighted PDF outputs.
  • .docx mismatch report walkthrough — client asked for clearer section grouping.
  • Discussed how missing-in-Excel vs missing-in-PDF should be presented.
  • Action items: refine report layout; persist attempts to MySQL; basic history list.

Week 7 — End of Stage II review (Mar 4)

  • All 4 fixtures pass cleanly when budgets agree; mismatches are flagged correctly.
  • Showed the first version of the history view (list of past validation attempts).
  • Stage II demo signed off.
  • Action items: write Stage II report; start metadata extractor for justifications.

Week 8 — Justification kickoff (Mar 11)

  • Walked the client through a draft BudgetMetadata model — confirmed fields.
  • Reviewed what we extract per personnel, fringe, travel, and tuition.
  • Client provided sample justifications for Generic, NIH, USDA, and NSF.
  • Action items: implement ExcelMetadataExtractor; first Generic template.

Week 9 — Spring break check-in (Mar 18, async)

  • Short async update by email; no live meeting (spring break).
  • Confirmed the Generic generator is producing a structurally correct .docx.
  • Action items: NIH template next; agree on placeholder phrasing convention.

Week 10 — NIH + NSF templates (Mar 25)

  • Demoed NIH (8 ODC sub-sections, salary cap advisory) and NSF (A–J layout).
  • Client liked the salary-cap finding; asked for an NSF cost-share advisory, too.
  • Discussed escalation rate computed from Y1→Y2 salaries.
  • Action items: USDA template; cost-share check; tuition-by-term narrative.

Week 11 — End of Stage III review (Apr 1)

  • All four templates regenerate every fixture cleanly; golden-file regression in CI.
  • Client introduced 2026 boilerplate revision — adds a Description(s) column.
  • Stage III demo signed off, conditional on description-column support.
  • Action items: write Stage III report; integrate description column.

Week 12 — Versioning & re-run (Apr 8)

  • Showed group_id + version model in MySQL; re-running creates v2 in the same thread.
  • Walked through version_state (draft/approved/archived) and "approved auto-archives older."
  • Demoed diff_versions JSON output between v1 and v2.
  • Action items: viewer (live grid + recalc); CLI scaffold.

Week 13 — Viewer + CLI (Apr 15)

  • Demoed app/static/app.html viewer with HyperFormula recalc on edited cells.
  • budget-tool CLI runs extract / validate/justify end-to-end from the terminal.
  • Client tested both flows on COMPLEX; surfaced a small fringe-grouping nit.
  • Action items: fix fringe grouping; finalize description column on personnel; refactor large service files.

Week 14 — Final review & handoff (Apr 22)

  • All Stage IV deliverables green: viewer, CLI, versioning, diff, four templates with description-column support.
  • Walked the client through docs/ (PROJECT_INTRO, PROJECT_STRUCTURE, API, OUTPUTS, AZURE_HOSTING_PLAN, this PLANNING page).
  • Confirmed rc/v1.0.0 will track main for the release tag.
  • Action items: write Stage IV report; ship final release; record demo video.