Construction contract administrators spend 15-20 hours per week manually comparing contractor daily work reports against inspector records. For a typical highway project with 50+ change orders, this translates to:
Ontario MTO requires ±5% variance threshold enforcement and complete audit trails for all Time & Materials claims. Current manual processes struggle to meet these standards consistently.
This pipeline reduces reconciliation time from 20 minutes per DWR pair → 5 seconds, with:
An automated reconciliation engine leveraging LLM orchestration (Llama 3.2) and Pydantic V2 validation to standardize disparate data sources, with deterministic Python logic for financial calculations to ensure trustworthy, reproducible results.
This reconciliation project addresses a critical challenge in construction contract administration: ensuring accurate alignment between contractor daily work reports (DWRs) and contract administrator (CA) records. The Ministry of Transportation requires precise documentation of labor hours, equipment usage, and material quantities to maintain compliance with regulatory standards and cost control objectives.
The automated reconciliation pipeline processes multiple DWR pairs, standardizes data using Pydantic V2 validation, and applies variance analysis with a ±5% threshold. Items exceeding this threshold are flagged for manual review, ensuring that only legitimate variances are recorded and that cost control measures remain effective throughout the project lifecycle.
Key Design Principle: AI extracts the data (Layer 2), but Python does the math (Layer 3). Financial reconciliation requires trustworthy, reproducible calculations—variance analysis is 100% deterministic, not AI-driven, ensuring the same inputs always produce the same outputs for audit compliance.
Docling + PyMuPDF fallback for extracting multi-column DWR layouts. Preserves table structures from complex MTO-standard PDF reports with encoding resilience (UTF-8, Windows-1252).
Ollama (local inference) with Llama 3.2 for structured data extraction. No cloud dependencies—all processing on-premises. 3-attempt retry with validation feedback for robustness.
Pydantic V2 enforces type safety, range checks (0-24 hrs/day), and business rule validation. Cross-field validation ensures totals match (number × hours_each = total_man_hours).
SQLite with ACID compliance and parameterized queries (SQL injection prevention). Complete audit trails: timestamps, source PDF filenames, model versions, and human review decisions.
Note: CO-99 is synthetically generated test data with deliberate discrepancies (Foreman +25%, Dump Truck +33%, Granular +15.6%, plus NEW items) to validate the pipeline's variance detection capabilities. Real-world DWR pairs (CO-21, CO-56) demonstrate expected MATCH results from actual MTO project data.
| Status | Category | Description | CA Value | Contractor Value | Variance |
|---|---|---|---|---|---|
| FLAG | Labour | Foreman | 2.00 hrs | 2.00 hrs | 0.0% |
| FLAG | Labour | Flagperson | — | 8.00 hrs | NEW |
| MATCH | Equipment | CAT 320 Excavator | 8.00 hrs | 8.00 hrs | 0.0% |
| FLAG | Equipment | INTL Tandem Dump | 6.00 hrs | 8.00 hrs | +33.3% |
| NEW | Equipment | Chev Pickup | — | 8.00 hrs | NEW |
| FLAG | Material | Granular B Type II | 45.00 t | 52.00 t | +15.6% |