The MEGA CHECK sheet was meant to answer "which section do I look at". It
was quietly failing to, in four separate ways, each of which made the file
look more finished than it was.
A line with no row to go to was dropped on the floor. reconcile() collected
it, tagged it with sheet "-", and then only attached rows to a section whose
SHEET matched - so "-" matched nothing and the line vanished. It was the one
thing most worth telling a person, and it was the only thing not shown. Every
unplaced line now appears with its qty, unit and rate.
Sections were paired on a bare number, and Candy restarts its numbering inside
every schedule. Blueberry has a "1" in Schedule A, another "1" under Schedule
B and a "4" under a third. Keying on that merged unrelated sections: 6 of
Blueberry's 8 matched nothing, and the sheet showed 2 clean rows that read as
"this book is checked". Now keyed on the section title - 8 of 8 and 18 of 18.
"WORKBOOK SAYS" summed the whole sheet. Blueberry's SCHEDULE B holds sections
1 to 6, so "1 SITE CLEARANCE" was reported as R4,422,018 against a printed
R160,057.99 - the right section with a figure that is not that section's.
Each section is now summed from its own rows only, and a row that cannot be
attributed counts for nothing rather than for every section at once. That
last one had 141, 142 and 143 all reporting the same R100,722.77.
Untested sections are now stated as untested. A table of clean sections implies
the rest balance, so the sheet says plainly which ones could not be matched.
A section the PDF prints as 0.00 is a fill-in for the tenderer, not a
disagreement. Blueberry's "4 CABLE DUCTS" is twelve lines that all carry
qty 0.0, and the consultant then fills in their own quantities; reporting that
as a R348,746.25 difference points at our work when the source simply has
nothing to compare against. It now reads "no figure in PDF".
"every write landed" only checked that a cell was not empty, then stopped
after 600. A rate overwritten with a wrong number passed silently - proved by
planting one and watching the gate stay green. It now compares every value,
and the 600-cell cap is gone: a check that covers part of the file and reports
as if it covered all of it is worse than no check.
Damage still blocks the download. Incomplete or misplaced work is handed over
with the punch list, and needs_review now fires on unplaced lines too - it
used to stay quiet on Blueberry, which had one.
Verified by fault injection, not by reading the code: corrupting a written
cell is now caught, two rates swapped between rows are flagged without
blocking, and no unplaced line is ever dropped. All four behave.
The merge guard that stops a priced tail row being folded into the line above
only excluded 'a)'/'b)' labels, not '.01'-style references. So '.01 0.5 - 1.0m
Deep' was swallowed by the sub-group heading above it, and the first depth line
of EVERY group never entered the candidate pool. The source line then had
nowhere sensible to go and drifted onto the next group's rows.
1200LE before: r37 heading, r39 '.02 1.0-2.0m', r41 '.03', r43 '.04'
- the '.01 0.5-1.0m' slot was absent entirely
1200LE after: r37 heading, r37 '.01 0.5-1.0m', r39 '.02', r41, r43
Central Towers 190 -> 198 written, 25 -> 17 unplaced. The 1200LE depth-band
misplacements are gone.
Also fixed two false positives in the placement check itself: rows whose
description cell holds a formula ('=Measurement!A18') are unverifiable rather
than mismatched, and must not cry wolf. The check has been confirmed to fail
when the bugs are re-introduced and to stay quiet on a clean build.
What remains is reported rather than hidden: 15 misplaced lines on Central
Towers, 14 carrying a value. A gap-penalty sweep from -0.20 to -1.50 moves it
not at all, so this is not score-chasing - both documents contain four
separate near-identical depth-band groups in 1200DB alone, and one global
alignment distributes them wrongly. Closing that needs anchoring on confident
matches and interpolating within blocks.
Blueberry: 103 written, 1 unplaced, 0 misplaced, not blocked.
Central Towers: 198 written, 17 unplaced, 15 misplaced, BLOCKED - the app
knows it is wrong and refuses to produce a file, which is correct.
Asked whether this needs AI or whether code can do it. The answer turned out to
be neither, exactly: what was missing was not a better matcher, it was a check
the matcher could not fake.
Every existing verification asks 'did the value land in the cell I aimed at?' -
and the writer answers that for itself. So a confidently misaligned write sails
straight through. That is the hole all three of the last bugs went through.
populate() now records which line it placed on which row, and verify() re-reads
that row out of the written file and asks whether it really is that line. The
test is CONTAINMENT, not similarity: a line split across two rows legitimately
puts only a fragment in the cell, and a similarity cut-off flagged every one of
those as an error. Case, double spaces and the row's own leading label are
normalised first.
Verified to actually fail when it should: re-introducing the repeated-header
bug and the split-row bug both trip it, and it stays quiet on a clean build.
It pays for itself immediately. Against Central Towers it finds 18 misplaced
lines, 17 of which had a rate written into the wrong row - the aligner drifts
one or two rows inside blocks of near-identical lines ('.02 Over 2,0 m up to
3,0 m' / '.03 Over 3,0 m up to 4,0 m'). Nothing else could see it: counts
were healthy, every write landed, every formula was intact, the total
reconciled, and 17 rates were still priced against the wrong descriptions.
The check is FATAL, so the download is removed while it is outstanding.
Blueberry passes and is unaffected. Central Towers is now correctly BLOCKED -
a file that refuses to download beats one that quietly misprices 17 lines.
The underlying alignment drift is NOT fixed yet. That is the next piece of
work, and it is better done measured end to end than patched line by line.
The engineer's screenshot showed L.Sum / 1 / 5750 sitting on the row reading
'DESCRIPTION: CENTRAL TOWERS'.
These sheets repeat their header block partway down so a printout stays
readable, and the repeat sits in the middle of the data:
r65 I='SECTION 1200 A'
r66 DESCRIPTION: | CENTRAL TOWERS <- the app wrote a rate here
r68 REFERS | ITEM NO | DESCRIPTION | UNIT | QUANTITY | RATE | AMOUNT
A scan that finds one header and treats everything after it as data makes the
repeat a placeable line. Rows that score like a header, or carry a header
marker (description:, payment, refers, item no, section 1200, ...), are now
excluded along with the two title rows above them.
This is not one cell - it is every long sheet in the template, which is why
one consultant's file shows the same fault in a dozen places at once.
The block in question is now correct in full:
r66 repeating header (clean)
r75 .02 The use of equipment L.Sum 1.00 5 750.00
r78 .03 Excavation by hand m3 50.00 764.75
r81 .04 Temporary protection L.Sum 1.00 25 000.00
Central 191 -> 190 written, 25 unplaced; Blueberry unchanged at 103 / 1.
Zero formulas damaged, every write landed, gate not blocked.
The engineer found it: '.02 The use of equipment referred to in item
110.04.01' had no rate, while the row above it had been given R764.75 - which
is '.03 Excavation by hand's rate. A wrong figure, in the wrong line, that
reads as a normal price.
The line is split across two rows - the label and the first part of the
description on one, the figures on the next:
r74 C=.02 The use of equipment referred to in item
r75 110.04.01 above L.Sum 1.00 =+G75*H75
Those halves were separate candidates, so a neighbouring line could match the
wrong one. The figures row now becomes the slot's row - which is where the
columns are - while keeping the joined description so matching still sees the
whole line.
Also found while in there: 'Carried forward' rows carry text in the item
column, so they were being treated as placeable lines and the real line above
merged down into one. They are now never placement targets.
The counts look slightly worse and the file is actually better:
written unplaced the block in question
before 196 22 '.02' row carried R764.75 - wrong
after 191 24 '.02' blank and listed; '.03' R764.75 and
'.04' R25 000 both correct
Four more lines need a click. One wrong rate in a neighbouring line is worth
more than four clicks - a blank is visible and safe, a wrong price is not.
Blueberry is unchanged at 103 written / 1 unplaced, so the simpler template is
untouched. Zero formulas damaged on both, gate not blocked.
Caught by the engineer: '.04 Appointment of Community Liaison Officer' showed
RATE ONLY in the spreadsheet while the PDF has Month, 8, R20 000.00,
R160 000.00. The values had landed on the row ABOVE it.
Two causes, both the same underlying mistake.
1. A BOQ row here is often TWO rows - the one with the item number, unit and
quantity, and a following row carrying the wrapped tail of the description:
r41 C=.04 Appointment of Community Liaison Officer Month 8 20 000
r42 for the duration of the contract period
Those tails were separate candidates, and the aligner matched the line to
the PREVIOUS line's tail. A row with no item number of its own and nothing
in the priced columns is now joined onto the line above.
2. The row classifier still tested the single column the header claimed for
DESCRIPTION (E) while the text is in D - the same header-lies problem I had
fixed for matching but not for classification. Row 41 was never counted as
a line, so it was never even a candidate. Both now use the description RUN.
Also worth being precise about: the 'RATE ONLY' in that cell was the
consultant's own pre-existing text in his template, not something this tool
wrote. The bug was the values going to the wrong row.
Central Towers 192 written / 23 unplaced -> 196 / 22, and four more genuine
disagreements are now visible because values land on the right rows.
Blueberry unchanged at 103 / 1. Zero formulas damaged on both, gate not
blocked.
The engineer reported that some of his tariffs on Blueberry Square were saying
'included' and did not pull through. He was right, and it was not a big issue
for the wrong reason - it was a real gap.
Candy prints a WORD in the rate column where a tariff is carried inside a sum
above rather than priced separately - INCL, incl VAT, PROV. Those lines have a
quantity but no figure, so there was nothing numeric to write and the cells
arrived BLANK. To the consultant that is indistinguishable from a failed
conversion.
The source's own wording is now written into the rate cell and the line is
listed as a note:
F15 = 'INCL' a) Office buildings
F17 = 'INCL' b) Survey Equipment
20 such lines on the Blueberry pair. The amount column is left alone because
there genuinely is no figure for that line - the money is in the sum above it.
Caught a placement bug doing it: the write sat after the early return for lines
whose quantity already matched, which is most of them, so it fired zero times.
Moved it before the quantity block.
Both pairs re-verified after: formulas intact, 0 damaged, gate not blocked.
Asked what the 160 items under Disagreements were. Almost none were
disagreements, and a panel that cries wolf at 160 teaches people to scroll
past it - worse than having no panel at all.
147 of the 160 were the formula guard firing once per row. The consultant's
amount column is a formula on nearly every line, so 'I protected your model'
was being reported as 'a person must choose'. It is now its own quiet note and
cannot crowd out findings that genuinely need a decision.
12 more were unit formatting - 'PC Sum' vs 'PCSum' vs 'PC.Sum' is one unit
written three ways. Unit comparison now collapses case, spacing and dots, so
only genuinely different units are flagged.
160 -> 19, and all 19 are real. The ones worth a human's eye: seven quantity
disagreements (template 47.5 vs source 95.0) and eleven unit mismatches, five
of which are 'm' vs 'm3' - pricing a cubic metre as a linear metre is a real
error, and only comparing the two documents finds it.
Also: a conversion takes 30-90 seconds and a frozen page reads as a broken
one, so the run now narrates its stages in a st.status panel.
The marker read 'dev' on the live site, so it was decoration rather than
evidence. A commit SHA is the wrong thing here twice over:
- Coolify does not pass a commit SHA into a Docker Compose build, so the
ARG fell back to 'dev' and would have done so forever.
- A file naming the commit has to live inside that commit, so it can only
ever name the parent. Amending the commit to fix that just moved the
discrepancy.
Replaced with a hash of the .py/.css/.toml actually copied into the image,
computed during the Docker build. It is true by construction, cannot go
stale, and answers the real question: is this the code that was built.
Two build-order bugs found and fixed while proving it (both caught by
building, which is the point):
- Docker's ENV does not evaluate $(cat file)
- the chown referenced a user created later in the file
Verified: build ok, fingerprint d7d427a2e770 in the image, sidebar renders
it, Central Towers converts inside the image (192 written, 23 unplaced, gate
not blocked).
It read 'dev' on the live site, so it was decoration rather than evidence.
Cause: Coolify does not pass a commit SHA into a Docker Compose build, so my
${BUILD_SHA:-dev} fallback always won.
Read from build.txt in the repo instead, which cannot drift from what is
deployed. Two things that took a try each:
- Docker's ENV does not evaluate $(cat file), so the file is copied into the
image and read at startup.
- Streamlit executes app.py in an exec context where __file__ is not
guaranteed; a bare reference to it inside build_sha() would have taken the
whole app down. It now walks a list of candidate paths and never raises.
Verified: docker build ok; build.txt inside the image reads e1f4127;
build_sha() resolves to e1f4127 in the container; the sidebar renders it.
The deploy failed:
dockerfile parse error on line 50: unknown instruction: BOQ_BUILD_SHA=dev
I appended BOQ_BUILD_SHA=dev after an ENV block that had no trailing
continuation, so it became a standalone Dockerfile instruction. It was also
wrong-headed: it would have overridden the real SHA baked in by ARG with the
literal string 'dev'. The ARG/ENV pair further up already does the job.
This one is on me. I changed a Dockerfile, pushed it, and told the user to
redeploy, without ever building the image. The earlier deploy was fine
precisely because that image HAD been built and run first.
Verified before pushing this time:
docker build --build-arg BUILD_SHA=51e4600 -> ok
container reports BOQ_BUILD_SHA=51e4600
/_stcore/health -> ok
Central Towers converts inside the image: 192 written, 167 correct,
23 unplaced, 0 formulas clobbered, gate not blocked
The profile store was write-only: the app saved each consultant's detected
column map and displayed it, but every run re-detected from scratch. So the
original promise - drop their file in and it knows the format - wasn't kept,
and worse, the sidebar could show a stale map that didn't match the file in
front of you.
Now the saved map is applied, with two guards:
- it is only used when the columns it names still exist and still make sense
for THIS file, so a template that changed shape falls back to detection
- 'Re-detect from scratch' in the sidebar overrides it deliberately
The hint path runs the same data verification and description-run derivation as
the fresh path. Without that a warm run placed 194 lines and a cold one placed
192 - both defensible, but a converter that gives two answers to the same pair
of files cannot be trusted on a tender. Cold and warm are now identical on both
sample pairs.
Also: the build SHA is baked in at image build time and shown in the sidebar,
because 'is my deploy current?' is otherwise unanswerable from outside -
Streamlit serves a client-rendered shell, so curl cannot see which commit is
serving.
Automatic coverage is now 203 of 226 on Central Towers (90%), up from 65. The
Unplaced tab lists every remaining line with its five best candidate rows,
ranked and scored, because where a description repeats in the document only a
person can say which occurrence is which. Pick one or leave it out; chosen
lines are written on the next run through the same verification gate.
Two engine fixes found while building it:
- only a real reference may be carried down a blank item cell. The wrapped
header 'ITEM'/'NO' left a literal NO in that column and every row beneath
inherited it, so all of 1200LE rows 73-120 read as a mismatch.
- the '.NN' is not part of a line's identity. The source reuses one number
space for a parent heading and its first child, and prints '.01' twice in a
row; the consultant's template copies that duplication while its group
column stays sequential.
Also fixed: two tab registrations that had silently never applied, and a
NameError in the Verification tab.
Tried and reverted: per-section alignment. Blueberry 1 unmatched -> 14,
Central 23 -> 34. One sheet can span several sections, so splitting is worse.
A converter that writes hundreds of cells into someone else's spreadsheet has
to prove it afterwards, reading the OUTPUT back off disk rather than trusting
what the writer believes it did.
boq_engine/verify.py
- every formula the consultant had is still there (fatal)
- no sheet went missing (fatal)
- every write actually landed in the cell it claims (fatal)
- every source line accounted for (warn)
- nothing replaced without a human decision (warn)
- the workbook's own arithmetic recomputes (warn, optional)
populate() now records every cell it wrote, so the fidelity check reads back
real coordinates instead of sampling a guess.
A failed fatal check removes the download button outright. Recalculation needs
LibreOffice and is skipped cleanly when absent, so the container stays small.
New Verification tab shows the full check list plus a per-sheet table of what
the app decided about the columns - header row, item, description run, unit,
qty, rate, amount - so a wrong assumption is visible rather than invisible.
Also: aggregated the per-sheet TOTAL findings into one; fifty identical cards
is noise.
Second real pair (Central Towers: 20pp Candy PDF into a 53-sheet SANS 1200
workbook) exposed four failures. All fixed and verified against both pairs.
- item numbering: accept 1.1.1, 110...., .01 and rebuild the running counter
Candy restarts under each repeated heading. 0% -> 86% of lines numbered.
- wrapped headers: join 'QUAN-' + 'TITY'. qty was missing on 49 of 51 sheets.
- description is a RUN between ITEM and UNIT, not one column: the header
claims E, the data sits in D, and it moves within a single sheet.
- carry item numbers down blank cells, and score a missing number as neutral
rather than as disagreement.
Mapping: 1/108 unmatched (Blueberry), 33/226 (Central Towers).
Safety: the consultant's workbook is never damaged. Per-write guard plus a
final pass that restores any formula disturbed - 0 of 3633 formulas clobbered.
Writing a literal over a derived quantity would have destroyed their model
while looking like it worked.
Reads our Candy PDF (deterministically, no OCR or model needed) and populates
the consultant's own workbook using their formulas, column layout and PAYMENT
column. Nothing is ever silently overwritten: every disagreement is reported,
can be settled inside the app, and regenerated.
- extract Candy text PDF -> tree (x-position column detection)
- template layout detect, sequence align, write, totals, overrides
- validate qty x rate and children-vs-subtotal reconciliation
- profiles per-consultant layout profiles, stored on a volume
Shipped with a Dockerfile + docker-compose for Coolify.
