OTR Issue Report Tool
Full-stack web application that automates Over-the-Road financial adjustment processing. Three input methods (email parsing, file upload, manual entry), multi-tier approval workflow, automatic SQL generation, and real-time metrics dashboard. Built iteratively over 4 months through 6 major versions.
The Problem
Business Context
Finance Operations team processing Over-the-Road (OTR) financial adjustments for carrier and supplier transactions. Each adjustment required manual email parsing, Excel template population, SQL generation, and database execution—taking 15 minutes per adjustment with a 5% error rate and no audit trail.
The Manual Process
| Issue | Impact |
|---|---|
| Time-consuming | 15 min/adjustment × 50/day = 11.67 hours daily team time |
| Error-prone | 5% error rate—typos, wrong amounts, incorrect IDs |
| No audit trail | No approval workflow, no tracking, compliance risk |
| Scaling ceiling | 50 adjustments/day maximum capacity |
| Training burden | Need to know Excel tabs, SQL syntax, database access |
The Solution
A full-stack web application built on Google Apps Script with modular service architecture. Three input methods accommodate different user workflows, while automated validation and SQL generation eliminate manual errors.
Three Input Methods
Email Parser
Paste email text and the system automatically extracts all fields
File Upload
Drag-and-drop Excel files with adjustment data
Manual Entry
Form-based input for individual adjustments
Key Features
Email Parser
Automatically extracts structured data from pasted email text—paste and it figures everything out
Multi-Tier Approval Workflow
Routes adjustments through configurable approval chain with delegation and escalation
SQL Generation Engine
Generates INSERT and verification queries for TCH and Imperial databases automatically
Real-Time Validation
Validates amounts and IDs against business rules before processing
Batch Processing Queue
Manages pending adjustments with batch processing capability for volume handling
Metrics Dashboard
Real-time visibility into processing status, approval rates, and team performance
Technical Architecture
| Component | Technology | Notes |
|---|---|---|
| Backend | Google Apps Script | JavaScript runtime, modular services |
| Frontend | HTML5/CSS3/JavaScript | Custom component library |
| Data Layer | Google Sheets | Processing logs, audit trail |
| File Processing | SheetJS (xlsx.js) | Excel file parsing |
| Visualization | Chart.js | Dashboard metrics |
| Integrations | Gmail, Drive APIs | Workspace integration |
Results
| Metric | Before | After | Improvement |
|---|---|---|---|
| Time per Adjustment | 15 minutes | < 1 minute | 93% |
| Error Rate | 5% | < 0.1% | 98% |
| Daily Team Hours | 11.67 hours | ~1 hour | 91% |
| Processing Capacity | 50/day | 1,000+/day | 20x |
| Annual Cost Savings | Baseline | $210,300 | Calculated |
| System Uptime | N/A | 99.8% | Production |