From monthly safety forms to a governed, verified dashboard
A contractor safety reporting system for a university capital-projects group: two forms in, four defensible KPIs out, with every number checked by an automated test rather than trusted.
- Client
- University of Alberta
- Sector
- Post-secondary, capital projects
- In production
- 2021
- Platform
- Google Workspace, no paid add-ons
- Scope
- 2 forms · 4 KPIs · 5 data rules
- Verification
- 91 automated tests, all passing
Contractors already filed a monthly safety report. The trouble started after submission. Answers were rekeyed into spreadsheets, injury rates were averaged month by month — which gives the wrong answer — and small projects produced alarming rates from a single incident. The events a project manager actually needed to act on, such as a lost-time injury or an expired Workers' Compensation Board clearance, surfaced late or not at all.
One submission, five automated steps
Every calculation lives in a visible spreadsheet formula, where anyone can audit it. The script only moves data, applies rules and sends email — it never computes a number.
Google Forms
Intake
Onboarding once per project, then a validated monthly report.
Google Sheets
Calculate
Every KPI in formulas, per contractor and per project.
Apps Script
Check
Data rules, flags and resubmissions on every submit.
Gmail
Notify
Acknowledgement to the contractor, warnings to the PM.
Looker Studio
Decide
Monthly summary and a filterable dashboard.
Four KPIs, calculated the defensible way
Rates are stated per 200,000 hours worked — the industry base, roughly 100 full-time workers for a year. How you compute them matters more than which ones you pick.
| KPI | Calculation | Why it matters |
|---|---|---|
| TRIF | recordable injuries × 200,000 ÷ hours | Overall injury frequency: fatalities, lost-time, modified-work and medical-aid cases. |
| LTIF | lost-time injuries × 200,000 ÷ hours | The serious end of the spectrum, comparable with published lost-time rates. |
| Near-miss rate | near misses × 200,000 ÷ hours | A leading indicator: more reporting usually means a healthier culture, not a worse site. |
| WCB claims | count, and the same rate | Ties safety performance to claims and cost exposure. |
Ratio of sums
Add up the injuries and the hours first, then divide. Never an average of monthly rates — that weights a quiet month the same as a busy one.
Minimum exposure
Below 10,000 hours the rate reads “insufficient exposure”, so one incident on a small project cannot mislead.
Two windows
Every rate is shown project-to-date and across each contractor’s last 12 months.
Limits with context
Project-to-date TRIF above 3.0 or LTIF above 2.0 warns the PM. Set against public benchmarks — no jurisdiction sets a legal limit — and stored as settings, not code.
Edge cases the system handles on its own
Each case was defined in the specification first, then demonstrated with a live form submission. A system that only handles the happy path is a prototype.
| Submission | What the system does | Outcome |
|---|---|---|
| Normal monthly report | Counts it and acknowledges with the updated KPIs. | Counted |
| Lost-time injury | Warns the PM about the injury, and again because project TRIF and LTIF crossed their limits. | 2 warnings |
| Expired WCB clearance | Counts the report and asks the PM to obtain a current certificate. | Warning |
| Contradictory answers | Sets the report aside, keeps it out of the KPIs, asks for a correction. | Quarantined |
| Corrected resubmission | The new report counts; the earlier one stays on record, marked replaced. | Superseded |
One system mailbox
Every email comes from a single admin mailbox no individual controls, and every send is logged.
No silent edits
Submitted values are never changed. Corrections arrive as resubmissions, so the history stays complete.
Every number checked, not trusted
These figures describe the 2026 rebuild, where the calculation logic was put under test. They are exact.
Verification
91
automated tests (47 Python, 44 JavaScript), all passing
100%
of KPI cells matched an independent recomputation
5 / 5
edge-case scenarios passed in a live end-to-end run
< 5 min
to reset a clean demonstration copy, measured
After the end-to-end run, the dashboard totals were reconciled against a separate script written for the purpose, so the dashboard was checked against something other than the system that produced it.
How it was delivered
The 2021 implementation was conventional consulting work: define the metric standards, agree the edge cases with the people who would be acting on the warnings, build it, hand it over.
The 2026 rebuild followed a spec-first process — project rules, a specification, a plan and a task list before any code. Claude Code wrote the Apps Script and the tests in VS Code and, through browser control, submitted test forms, checked the resulting rows, flags and emails, and built and reconciled the dashboard.
The part that did not change is the part AI cannot own: deciding what counts as a duplicate record, choosing which edge cases matter on a live construction site, setting warning limits against public benchmarks, and reviewing every change before it shipped. The speed came from the tooling. The judgement stayed with people.
What a further rollout would add
- An incident detail form and corrective-action tracking
- Reminders and escalations for late reports
- Role-based access on a managed Workspace or Microsoft 365 domain
This is what Reporting Automation & Data Engineering looks like delivered.