Sales Reporting Pipeline

A reliability case study in ingesting nightly sales reports from a system that offers no integration to us.

Part of Residents Platform: House Pub.

A bar still needs to know how a night went. Sales numbers help schedule the right number of people and feed a weekly summary, but the pub’s point-of-sale system offers no integration available to us, and the data belongs to another organization. I built a pipeline that turns nightly sales reports into trustworthy numbers on an admin page and in a weekly email. This page is about making that pipeline reliable, not about any one data source.

At a glance

  • Status: in production. At the last write-up, acceptance for a full assigned-service cycle was tracked separately.
  • Feeds: per-night shift reports, a morning email with aggregates only, a weekly summary, and a “usual night” baseline for staffing.
  • Principle: any source hands a file to one validating intake.
  • Safety: nothing is deleted, nothing is guessed, and a missing report is never shown as a zero night.

How it works

  1. A report arrives by a scheduled or on-demand import, or by authenticated email.
  2. One intake checks it, parses it, and decides whether it becomes the night’s current report.
  3. The admin page, the morning email, and the weekly summary read only the current, validated reports.
flowchart TD
  A[Scheduled or on-demand import] --> I[One validating intake]
  B[Authenticated email] --> I
  I --> S[(Stored reports and facts)]
  S --> V[Admin views]
  S --> M[Morning and weekly emails]

Problems I had to solve

Sources change, but trust must not

Separating transport from intake means a new source never touches the rules. Every file, from any source, goes through the same checks: for emailed files, sender authentication; for every file, a container check, a header check (report kind, business date, location), and a layout-aware parser with a version number. A row the parser does not recognize is held for review instead of guessed.

The same report arrives twice, or late, or out of order

  • Stored once. Files are stored by content hash, and a receipt is unique per message and file.
  • One current report. Each night and report kind has exactly one “current” receipt. Source rank decides first, then the later as-of time, so a late copy of old data cannot overwrite newer data.
  • History is immutable. Superseded receipts keep their files. Nothing is deleted.

Reports are late, partial, or wrong

A run is complete when every expected report kind has arrived, and the night stays provisional until every report is validated. A report held for review keeps it provisional. One bad report is recorded and the run continues with the others. A valid empty report is a zero-sales night, while a missing summary is never shown as zero. Held discount and void reports can be reprocessed later by an admin from the original, hash-verified file.

Some nights are not real nights

Some nights are only a test ring. A permitted admin can exclude such a night with an optional reason. Nothing is deleted: the night’s reports are superseded, a row records who excluded it and why, a report that arrives later is stored but does not become current, and restoring brings the best stored report of each kind back.

Imports should follow the business, not the clock

A planner works from the assigned, approved, or published shifts, not from a fixed schedule. It has separate modes for during service, just after close, the nightly pass, and a gap-filling pass, each with bounded attempts or spacing. A run takes its lease before it touches the source, so a failure in the first step still consumes an attempt.

Be careful with every import

Imports share one guard. It writes a pause before any attempt and clears it only on a confirmed completion. An uncertain result pauses everything until a person resumes it, attempts have a minimum spacing, and the guard fails closed if its own record is unreadable.

A phone loses the response, but the work finished

On 2026-10-07, a manual import of a past night showed “Load failed” on a phone while the server had actually finished. The fix separated acceptance from execution: the request is accepted immediately and a durable job runs on a schedule, and tests cover recovering the result by asking for the job’s status. A later job completed all eight report kinds in about a minute.

Nobody notices a quiet failure

Alerts cover a schedule that has been silent for more than 30 minutes and a staffed night that is still incomplete at noon. A pause sends a plain-language email, a “why it paused” line is kept for 14 days, and a “needs you” card appears on the admin overview. That last one exists because a pause once showed “Nothing needs you” on the overview while it was active.

Lessons from incidents and reviews

  • The morning email went out too early. It could send before the hourly and item reports landed, so it now waits for ten minutes of quiet.
  • Manual requests starved the scheduled pull. Frequent manual requests could suppress the scheduled pull. I found it by reading the code, and live data was not read for that finding. Manual jobs now yield to a due scheduled pull.
  • Review caught problems before deploy. Among them, one was a first step that preceded the lease. Another was a first failure that aborted the whole run. The third was a “yesterday” import accepted during service, which would have frozen partial data as final.

Privacy and ownership

The morning email contains aggregates only. Discount and void facts carry no employee identities, per-employee rows are stored only for the admin views and are backed up without names, and access is permissioned by role. The data belongs to another organization, so the pipeline only reads it and keeps it private.

What I did not build

An earlier design relied on a dedicated always-on machine, and I dropped it for cost and operational burden. The pipeline only reads reports, and bulk history loads are deliberate, supervised steps.

What each test layer proves

Each stage has dedicated test files: parsers, intake, planner, and guard. Beyond that, I checked parsers against real files, ran local integration tests, and ran smoke checks in production. The whole-repository test total is context, not pipeline coverage, and I do not quote it as such.

Tools

Cloudflare Workers and Cron Triggers, D1, R2, JavaScript, a workbook parser, and email authentication checks.

Glossary

  • Intake: the one place where every incoming report is checked and accepted or rejected.
  • Receipt: the record of a report that arrived, tied to its file hash.
  • Idempotent: safe to run twice, with the same result.
  • Superseded: replaced by a better or newer report, but still kept.
  • Provisional: not final yet, because some reports are still missing.
  • Fail closed: when unsure, stop and ask rather than guess.