Guard Hours to Invoice: Turning a Weekly Timesheet Into a Client-Ready Bill Without Rekeying

The fix is not a better spreadsheet. It is a contract layer. Store guard shifts as clean, validated rows (guard, site, post, start, end, break, pay code), attach a rate resolution step that turns each row into a billable line, and let the invoice be a pure function of those two things. Once that exists, generating a client-ready bill is a render step, not a data entry step. Everything below is how to build that in a way that survives real payroll weeks.

Most security firms, staffing agencies, and small facilities contractors do not have a data problem. They have a boundary problem. Hours are captured in one shape (a guard's shift, sometimes handwritten, sometimes from a scheduling app export) and billed in another shape (a client's PO, their site codes, their overtime rules, their approval format). Every week, somebody manually translates between the two. That person is the bug.

Why rekeying keeps coming back

Rekeying survives because each individual translation feels small. Copy 40 rows, fix the site names, apply the OT multiplier, drop it into the invoice template. Twenty minutes. But it recurs weekly, it scales linearly with headcount, and it has no audit trail. When a client disputes an invoice three weeks later, the only artifact is the invoice itself. The reasoning is gone.

The deeper reason is that most teams try to automate the wrong step. They automate invoice formatting (a nicer PDF, a template in their accounting tool) while leaving hour interpretation manual. Interpretation is where the real work is: which shifts count as overtime, which post commands a premium, whether a 12-hour shift gets an unpaid meal break, whether a holiday shift bills at a different rate than it pays.

If interpretation stays in a human's head, the PDF generator does not save you anything.

Step one: make the timesheet a real data structure

Before anything else, define the shift row and refuse to accept anything that does not conform. A workable minimum:

shift_id, guard_id, client_id, site_id, post_code,
start_ts, end_ts, unpaid_break_minutes,
pay_code, bill_code, notes, source, approved_by

A few things earn their place here.

start_ts and end_ts as full timestamps, not a date plus two clock times. Overnight guard shifts cross midnight constantly, and date-plus-time schemas produce negative durations at 23:00. Store them with a timezone, because a firm covering multiple states will eventually bill a shift that straddles a daylight saving change.

pay_code and bill_code as separate fields. This is the single most important structural decision. What you pay a guard and what you bill a client are different questions with different rules. A guard may earn overtime after 40 hours in a week, while the client contract bills straight time for all hours and pays a flat premium only on federal holidays. Collapsing these into one "rate" field is the mistake that forces permanent manual correction.

source so you can tell an app export from a manual entry from a phone-in correction. When numbers are questioned, the first thing you want to know is where the row came from.

Step two: validate before you calculate

Run a validation pass on the raw week before any rate is applied. Cheap checks that catch most real errors:

• Overlap: no guard has two shifts whose time ranges intersect. Overlaps almost always mean a double entry or a wrong date.
• Duration bounds: flag anything under 1 hour or over roughly 16 hours. Both are usually typos, sometimes genuine, always worth a look.
• Orphan references: every site_id and client_id resolves to an active record. A site that was renamed mid-month silently kills a week of billing otherwise.
• Coverage gaps: if a post is contracted for 24/7 coverage, compare scheduled hours to actual and surface the delta. This is the check that finds unbilled hours, which is the one that pays for the whole system.
• Missing approval: any shift without approved_by is held, not billed.

Emit these as a short exceptions list, not a blocking error. The week should still process. A human resolves fifteen flagged rows instead of re-reading four hundred.

Step three: rate resolution as its own layer

This is the part worth building carefully. A rate resolver takes a validated shift row plus the client's contract and returns billable lines. Keep it deterministic and keep it separate from both the timesheet and the invoice.

Contracts differ, but the resolution order is usually stable:

1. Find the contract in effect on the shift date. Contracts have effective dates, and last quarter's invoice must not change when this quarter's rates load.
2. Find the most specific rate: post-level first, then site, then client default.
3. Apply modifiers in a fixed order: holiday, then premium post, then overtime, then any minimum-billing rule (many contracts carry a 4-hour minimum for call-outs).
4. Split the shift if it crosses a rate boundary. A shift from 20:00 December 24 to 04:00 December 25 may be two lines, not one.

Write this as a pure function: shift plus contract in, lines out, no database writes, no side effects. That makes it trivially testable, and you will want tests, because rate logic is where quiet money loss lives. Take your three most complicated real invoices from the past year and turn them into fixtures. If the resolver reproduces them exactly, you are in good shape.

Store the resolved lines with a reference back to the source shift_id and the contract version used. That reference is what lets you answer a dispute in thirty seconds.

Step four: the invoice is a render, not a rebuild

Once lines exist, the invoice is grouping and formatting. Group by whatever the client wants to see (usually site, sometimes post, occasionally guard), sum, apply any agreed fees, attach the backup detail.

Two practical notes.

Most clients want a summary page and a detail annex, not one flat list. The summary is what their AP person approves. The annex is what their site manager checks. Generate both from the same lines so they cannot disagree.

Match their reference numbers. If the client tracks work by PO number and cost center, those fields belong on the invoice header and often on each line. An invoice that does not carry the client's own identifiers gets held in their queue while somebody figures out where to code it. That is a payment delay you caused yourself.

What this looks like in practice

A realistic weekly run: import or enter the shifts, run validation, resolve fifteen to thirty exceptions by hand, approve, generate. The manual part is now the exceptions, which are genuinely ambiguous, rather than the transcription, which never was.

The measure of whether it worked is not that the invoice looks nicer. It is that the same week processed twice produces byte-identical output, and that you can trace any line on any invoice back to a specific shift and a specific contract version.

Common mistakes

Building the PDF first. The output is the easy part and the least valuable. Build the shift schema and the resolver first; render last.

Letting the invoice mutate the source. If generating an invoice writes back corrections to the timesheet, you lose the ability to regenerate and compare. Corrections belong in the timesheet, then regenerate.

One rate table for pay and bill. Covered above, worth repeating. Separate them from day one, because splitting them later means migrating historical data.

Floating point for money. Use integer cents or a decimal type. Rounding drift across four hundred lines is small but it is exactly the kind of thing an AP department notices.

Rounding at the wrong step. Decide once whether you round each line or round the total, write it down, and match what the contract says. Do not let it vary by code path.

FAQ

Do I need a full application for this? No. A validated CSV schema, a small script with the resolver as a tested function, and a template renderer will handle a surprising amount of volume. Reach for a database when you need multi-user approval or contract history, not before.

How do I handle a client whose contract rules are genuinely unusual? Encode it as a named rule in the resolver rather than as a manual adjustment after the fact. If it is written down as code with a test, it survives the person who knows about it leaving.

What about clients who insist on their own portal? Generate your canonical lines first, then export to their format. Never let their portal be your source of truth, because you cannot audit or regenerate from a system you do not control.

Should past invoices change when rates change? Never. Version contracts by effective date and pin each invoice to the version used. A regenerated old invoice must reproduce the original exactly.

Where do approvals fit? Between validation and rate resolution. Approve hours, not dollars. Once hours are approved and rules are encoded, the dollars are not a separate decision.