Case study · Open-source QS tooling

The interim payment cycle, out of the spreadsheet.

A lightweight Flask application that runs the monthly valuation on construction contracts: import the Bill of Quantities, record cumulative quantities, and issue the Interim Payment Certificate — retention, cap, and previous-payment carryover handled exactly, with a stored history of every certificate. Built for the small and mid-sized contractors priced out of enterprise systems.

View on GitHub See the arithmetic

Valuations live in fragile spreadsheets.

Enterprise systems for this exist — CCS Candy, RIB, Procore, Oracle Contract Management — but they're priced and scoped for tier-1 contractors who commit a whole project into their ecosystem. Everyone else runs interim valuations in ad-hoc Excel files, and the same three failures repeat on site after site.

Failure 01

No audit trail

Last month's certificate is whatever the file said before somebody overwrote it. There is no trustworthy history — only the current state of the spreadsheet and everyone's memory of how it got there.

Failure 02

Retention buried in formulas

The deduction logic lives in cell references nobody dares touch. When the figure looks wrong, checking it means reverse-engineering the workbook — and the limit-of-retention clause is usually implemented from memory.

Failure 03

Key-person risk

The whole payment process depends on the one engineer who built the file. When they leave, the project's commercial memory leaves with them.

Free & open-source One job, done well Auditable arithmetic History of every certificate

One monthly cycle, end to end.

The application does exactly what a working QS does each month — set up once, then measure, value, and certify, period after period, with each certificate building correctly on the last.

The workflow

Set up the contract. Parties, currency, contract value, retention % and an optional limit-of-retention cap — entered once, governing every certificate.

Import the bill. CSV or Excel, validated row by row. Anything ambiguous — like a locale decimal comma in 12,5 — is refused with the row number, never guessed. All-or-nothing: a half-loaded bill cannot exist.

Measure the period. A draft certificate opens prefilled with the previous certificate's cumulative quantities; enter this month's cumulatives and the valuation recomputes — overruns flagged, corrections shown in red.

Issue the IPC. The five summary figures freeze onto the record permanently, and the page prints as the certificate itself — signature blocks included, DRAFT watermark until issued.

An issued Interim Payment Certificate: the measured bill, retention, carryover and net payable — **IPC** An issued certificate — measured bill, retention, previously certified, net payable.

Project page with dashboard stats and the imported bill of quantities — **Project** Dashboard sourced from issued snapshots, above the priced bill.

Draft certificate with cumulative quantity entry and live valuation — **Draft** Quantity entry, prefilled from the previous certificate, revaluing on save.

The projects index — **Projects** Each contract with its value, retention terms and history.

Browser print preview of the certificate with signature blocks — **Print** The page is the document: print / save-as-PDF, watermarked while draft.

Arithmetic a checking QS can attack.

A commercial lead decides in thirty seconds whether a certificate can be trusted, and they test it three ways. The valuation engine guarantees all three — every figure is a Decimal, stored as exact scaled integers, rounded half-up; floats never touch money.

Rule 01

Casts down

Line values are rounded to 2 d.p. first; totals are the sum of the rounded lines — so adding the printed column reproduces the printed total exactly, to the smallest unit.

Rule 02

Casts across

A line's this-period value is the difference of its rounded cumulative and previous values — never an independently rounded qty × rate — so previous + this period = cumulative on every printed row.

Rule 03

Telescopes

Net payable = this certificate's cumulative due minus the previous certificate's cumulative due. Summing every net payable over the job equals the final cumulative due — nothing double-counted, nothing lost.

The one-fils trap · qty 100.5 cum / 50.25 prev · rate 0.33

Cumulative value33.165 → 33.17

Previous value16.5825 → 16.58

Period as qty × rate16.58 ✗

Period as difference16.59 ✓

Round the period independently and the row fails to cast: 16.58 + 16.58 ≠ 33.17. One fils out — exactly the fils a reviewing QS finds. The engine computes the difference, and a unit test pins the behaviour.

Certificate 2 of the golden test scenario · QAR

Cumulative work done	345,569.94
Less retention (10%)	(34,556.99)
Cumulative amount due	311,012.95
Less previously certified	(65,407.50)
Net amount payable this period	245,605.45

The same suite covers the awkward cases: an optional limit of retention (retention freezes at the cap, after which net payable receives the full growth in work done), downward re-measures producing negative this-period values, and — when a correction is large enough — a negative certificate, reported rather than hidden.

Engineering notes.

A small codebase with deliberate seams — each decision below is documented in the repository where it's made.

Pure valuation engine: No Flask, no database imports. One calculation path serves the live draft preview, the issue snapshot and the rendered document — the preview and the certificate cannot disagree.
Decimal-exact storage: SQLite has no true DECIMAL, so money is persisted as exact scaled integers via a SQLAlchemy TypeDecorator; 2.68 is stored as 268. Half-up rounding throughout, matching Excel.
Snapshot at issue: Issued certificates carry their five summary figures frozen on the row — the audit trail made literal — and a test asserts snapshot equals recompute, sounding the alarm on any drift.
Loud-failure import: Ambiguous input dies with a row number instead of a guess; all problems are reported in one pass, like a properly marked-up submittal; import is all-or-nothing in a single transaction.
Service layer: Workflow rules — one open draft, periods advance, issued records immutable — live in one module and raise domain exceptions; routes only translate HTTP. Rules tested twice: directly and through the browser-facing endpoints.
72 tests: Including a hand-checkable three-certificate golden scenario asserted twice — once against the pure engine, once through HTTP — with the same figures landing in both.

Scope, stated plainly.

The pitch is not "no one has built this." It's that the people who need it most are priced out of the ones that exist — so here is a free one that does the core job cleanly.

It does

The valuation-and-certificate workflow for straightforward contracts
Sequential certificates with correct previous-payment carryover
Retention, with an optional limit-of-retention cap
Overrun flags, downward corrections, negative certificates
A printable IPC and a stored history of every certificate

It does not

Replace a tier-1 contractor's full commercial suite
Handle complex claims or disputes
Do multi-currency contracts
Manage variations or re-rating of certified work