Turning 26 GB of scanned drawings into an equipment database

A 20-building public school district has no institutional record of what mechanical equipment it owns. Every answer to “what heat pumps do we have” or “when does this rooftop unit age out” lives inside a 26.3 GB pile of about 890 scanned drawing PDFs, spanning buildings built between 1921 and 2014, most of which have had their mechanical equipment replaced two or three times since. Nobody can answer those questions without opening drawings one at a time.

Two halves, two very different maturities. The extraction pipeline is running and producing verified data right now. The database and web application are architected and reviewed, but not built. There is no contract, SOW, or hosting agreement with the district. This post is about the half that is real.

The extraction half

Drawings become structured equipment records: tag, category, manufacturer, model, capacity, location, install date, electrical nameplate load, and the source document and schedule table each value came from. Measured against the files rather than against the runs’ own summaries:

1,713 equipment records and 720 sub-component records transcribed from the drawing sets.
15 of 20 extractable buildings fully resolved, with several more running concurrently.
131-row drawing inventory across three separate document batches, every PDF identified, dated, and assigned to a building.
Three buildings have no drawings anywhere in the corpus. That itself is a finding the district did not have.

The interesting engineering: concurrent workers, file-based coordination

The extraction runs as independent concurrent AI sessions, one per building, coordinating through a shared claim table so no two sessions write the same shared workbook at once. Peak observed: five concurrent sessions across two people’s workspaces.

The concurrency control is deliberately boring. Claim before you write. Per-item detail in per-item files. One dashboard line per building. Verified under real contention: four sessions raced the same workbook and every one of them detected the conflict, re-read, and merged rather than clobbering.

The verification problem, which is the genuinely hard part

Automated checks catch structural damage: row counts, duplicate keys, missing source attribution, formula integrity. They provably cannot catch a wrong value that is structurally perfect. Three real cases, each caught by luck rather than by process:

A record stamp read as 2004 at low resolution that actually says 2009.
A duplicate-detection test that was invalid on paper scans and returned a confident, meaningless result.
An off-by-one where a legible, plausible value from a real schedule was attached to the wrong equipment tag because the crop clipped the tag column.

The fixes are procedural, not just code: every transcription crop must contain the printed tag for its own rows, no value is ever read off a low-resolution locating pass, and each finding is written back into the procedure file so the next run inherits it.

The transferable lesson: at scale, a validation suite that only checks structure will report green on corrupt data. The catch has to sit at the same abstraction level as the mistake, and structural verification does not.

The database half

Multi-tenant schema, org_id on every table, behind a single-tenant UI so a second district is a row rather than a fork. Roughly 20 tables. Cloudflare D1 as the store, Pages Functions for the API, R2 for photos, Cloudflare Access for auth. Design went through three independent reviews and changed substantially each time; the current version computes replacement due dates in views rather than storing them, which removes an entire class of backfill work and eliminates the need for a batch scheduler.

That half exists on paper. Building it is the next scope. A follow-up post will cover the schema in detail once the code catches up.