Automated Inspection ETL

01 Problem

Site inspections were recorded by hand and reconciled into spreadsheets afterwards, so the reporting lag was measured in days and the numbers were only as good as the transcription.

Because entry was manual and unvalidated, the same field could be recorded three different ways across three sites — which made cross-site comparison unreliable.

Management needed trend reporting across sites, but assembling it meant someone rebuilding the same workbook every reporting cycle.

02 Approach

• Move data capture into the product itself: a Next.js inspection platform where the structure is enforced at entry rather than cleaned up afterwards.
• Validate on the way in, not on the way out — the checks that used to happen during reconciliation now happen at the point of capture.
• Automate the handoff into Google Drive/Sheets with metadata logging, so the reporting layer is fed continuously instead of rebuilt on request.
• Gate access by role with JWT so inspection data stays compliant and audit-ready.

03 Architecture

1. 01 · RAW

   Inspectors capture records and images in the platform. 300+ inspection records a month enter the system already structured.

2. 02 · CLEAN

   Validation runs at entry — required fields, controlled vocabularies, and consistency checks — removing the reconciliation pass entirely.

3. 03 · LOG

   An ETL pipeline writes records and automated metadata into Google Drive/Sheets, giving every entry a traceable origin.

4. 04 · SHIP

   KPI dashboards and cross-site trend reports read from that layer directly, so management sees current numbers instead of requesting a rebuild.

04 Result

05 Stack