Skip to content
allanninal.dev/build
Collaborate

Part 1 of 7 · Timesheet validator series ~6 min read

A timesheet validator on AWS for a few dollars a month

Payroll week has a particular texture in a small business. Somebody prints the timesheets. Three are missing. Two have a Thursday with no hours on it. One says fourteen hours on a day the roster says was a half shift. Now it is the 28th, the people who could explain any of that have gone home, and payroll is Friday. The problem is not that the sheets are wrong. It is that nobody looked at them for three weeks. This post walks through a small system that looks at every sheet the minute it arrives.

a desk with a pen, notebook and a laptop on it
Photo by Anil Jose Xavier on Unsplash

Key takeaways

  • Sheets are checked on submission, not at payroll. The gap between error and question is minutes.
  • Three sources to check against: the roster, the job log, and a rules tab you already keep.
  • Every sheet ends in one of three states: clean, asked about, or waiting on a manager.
  • The system never edits an hour. It asks the person, or it flags it for someone with authority.
  • Designed on AWS for about $3 a month at typical small-business volume.

The whole system on one page

Before any code, here is the shape of what we are designing.

System: timesheets in, roster and jobs as reference, three pieces inside AWSThree boxes across the top sit outside the AWS account. On the left, Timesheets: submitted through an app, a form, or as a photo of a paper sheet. In the middle, Roster and jobs: the records saying who was expected on which shift and what work was actually logged. On the right, Manager: the person who sees only the sheets that need authority. Each connects by an arrow to the AWS account container below. Sheets flow down into the account. The roster and job records feed in to say what was expected. The manager receives only what needs authority. Inside the AWS account are three components in a row. On the left, the Reader, which turns whatever arrived into one row per day. In the middle, the Comparer, which sets those rows against the roster and the job log and applies the break and overtime rules. On the right, the Asker, which sends one question to the person who filled the sheet in. Arrows flow left to right. A note at the bottom says a clean sheet is never mentioned to anybody, and that this is most of them.AWS ACCOUNTTimesheetsapp, form, or photoRoster + jobswho was on, what ranManagersees only the odd onesReaderone row per day,whatever came inComparerroster, jobs,break and OT rulesAskerone question to theperson who knowssheets inwhat was expectedonly what needsauthorityA clean sheet is never mentioned to anybody. That is most of them.
Fig 1. Three things outside the account, three pieces inside it. Sheets arrive however people already submit them, the roster and the job log say what was expected, and only what genuinely needs authority reaches a manager.
  • App integration
  • Machine learning
  • Management
  • Analytics
  • People

What you set up once (the outside)

  • A way to submit. Whatever people already use. A web form for most, a photo of a paper sheet for the ones who will never use a form, and a spreadsheet upload for a supervisor who collects a crew’s hours. All three are covered in Part 2, and all three become the same thing: one row per person per day.
  • A roster. Who was expected, when, and where. Most small businesses have this somewhere — a shared calendar, a scheduling app, a whiteboard photographed on Monday. It does not need to be authoritative. It needs to be roughly right, because its only job is to notice that Thursday is blank on a sheet for somebody who was rostered on Thursday.
  • A rules tab. One sheet holding the things a payroll clerk carries in their head: the unpaid break after six hours, the point where overtime starts, the maximum day nobody should be working past without a conversation, the rounding rule. Writing them down once is most of the value of this project, independent of any code.

What runs on every sheet (the inside)

  • The reader. Turns whatever arrived into rows. A form is already rows. A spreadsheet is nearly rows. A photo of a paper sheet is the hard case, and it is the one place a model earns its keep: reading a handwritten grid into start times, finish times and a job reference, and marking anything it could not read confidently as unreadable rather than as a number.
  • The comparer. Five checks against the three sources, all arithmetic. Does every rostered day have hours? Does every day with hours have a job, or a reason it does not? Do the breaks satisfy the rule? Is any day longer than the maximum? Does the week’s total cross the overtime threshold, and if so has the sheet marked it as overtime?
  • The asker. Turns a failed check into one message to the person who submitted it, with the day named and the discrepancy stated. Almost all of them are closable in one tap. The few that are not — an override, an unusual overtime week, a day past the maximum — go to a manager, because those need authority rather than memory.

One sheet, end to end

One timesheet from submission to clean, in five stagesA horizontal row of five boxes joined by arrows. Submitted: on Friday afternoon. Read: turned into one row per day. Compared: set against the roster, the job log and the rules. Asked: a question goes back if anything is odd. Clean: the sheet is ready for the 28th. A note says the whole loop closes on Friday and payroll week has nothing left to discover.ONE TIMESHEET, END TO ENDSubmittedFriday afternoonReadone row per dayComparedroster, jobs, rulesAskedif anything is oddCleanready for the 28thThe whole loop closes on Friday. Payroll week has nothing left to discover.
Fig 2. The same system as one line. The important property is not any single step; it is that all five happen on the day the sheet is submitted rather than three weeks later.
  • App integration
  • Machine learning
  • Management
  • Analytics

In plain words

Your site supervisor submits her week on Friday at four. The reader turns it into five rows. The comparer opens the roster: she was on Monday to Friday, and there are five rows, so nothing is missing. Four of the five days have a job reference that matches a job that ran that day. Tuesday says nine and a half hours with a thirty-minute break, and the rule says a break of thirty after six hours, so that is fine. The week totals 44 hours against a 40-hour threshold, and the sheet has marked four hours as overtime. Everything lines up. The sheet is marked clean, and nobody hears anything.

Her colleague submits at the same time with Thursday blank. The roster says he was on. Twenty seconds later he gets one message: “Thursday 9th is blank and the roster had you on the Aldershot job — hours, or were you off?” Two buttons: “I worked, here are the hours” and “I was off”. He taps the first, types 7 to 3.30, and the sheet is clean before he has left the car park. On the 28th, payroll opens twenty-two sheets and every one of them is right, which is a completely different week from the one that starts with printing them out and finding three gaps.

Design rules that shaped every decision

  • Check on submission, never at payroll. The value is entirely in the gap between the error and the question, and that gap should be minutes.
  • The system never edits an hour. It asks the person who wrote it, or it flags for someone with the authority to change it.
  • Unreadable is a legitimate answer. A handwritten 7 that might be a 1 is marked unreadable and asked about; it is never resolved by guessing.
  • The roster is a hint, not a law. People swap shifts. A mismatch is a question, not a correction.
  • The rules live in a sheet. Changing the overtime threshold is an edit, not a deploy.
  • A clean sheet generates no message to anybody, ever. Most sheets are clean.

Why this shape

Nearly every timesheet problem is a timing problem wearing a data costume. The information needed to fix a blank Thursday exists in one person’s head on Friday and has largely evaporated by the 28th. A validator that runs at payroll time is therefore solving the problem at the worst possible moment: maximum cost to fix, minimum memory available, and a deadline.

So the design puts almost nothing clever in the checks and everything into when they run. Five arithmetic comparisons against records you already keep, executed within a minute of submission, closing the loop with the one person who can answer. The result is not a smarter payroll process. It is a payroll process with nothing in it to discover.

The next four posts walk through each piece: how a timesheet arrives, how it gets compared, how a question reaches the person who filled it in, and how a week gets closed. One diagram per post, a cost breakdown, and an engineering reference at the end.

All posts