What died in transit: offline-first field data in Peshawar

I am the only engineer at the Peshawar office of a US-registered multinational nonprofit. Its field staff work in markets, villages and schools where the signal comes and goes. The organisation runs on two kinds of record: what staff enter on their phones in the field, and a delivery log of every handover of school supplies since November 2020, kept in Excel.

Both lost data in transit. One lost it on the way from a phone to the server. The other lost it on the way from a person’s keyboard into a spreadsheet cell. Neither failure threw an error.

1. The phone that said “saved”

Staff install donation boxes in shops and come back to collect them. Each install and collection is logged on a phone: the amount, a GPS fix, photos. The app is an offline-first web app with a service worker I wrote by hand. Writes go into an IndexedDB queue on the device and flush when the connection returns.

An internal logic audit of the app found the queue had two quiet ways to lose work. Only two outcomes ended a queued item: success, and a 409 conflict. Everything else went back in the queue. A request the server would never accept, such as a malformed payload after an app update or a box deleted in the meantime, was retried on every reconnect, forever. The “Pending sync” banner never cleared, and the phone kept spending battery on a request that could not succeed.

The 409s were worse. A conflict, such as a second collection from the same box inside the 24-hour cooldown, removed the item from the phone with a toast that disappeared in seconds. The server kept a record that the request had failed. The collector’s phone no longer had the amount they had typed in.

The fix was to treat every response as one of four outcomes, and never to drop anything:

export const classifySyncFlushStatus = (status: number) => {
  if (status >= 200 && status < 300) return 'synced'
  if (status === 409) return 'rejected'     // business rule: keep it, show it
  if (status >= 400 && status < 500)
    return TRANSIENT.has(status)            // 401 403 408 425 429
      ? 'transient'                         // retry, e.g. after re-login
      : 'permanent'                         // stop retrying, keep it, show it
  return 'transient'                        // 5xx, network: the server will recover
}

• Rejected and permanent items move to a failed store on the device, with the status code and the server’s reason. The collector can see them, retry them, or discard them deliberately.
• Replays are safe. Every queued item carries its own id. The server keeps a unique index on that id and the action, so a record that arrives twice is acknowledged as a duplicate, not written twice.
• One flush at a time. The reconnect listener and the manual retry button could fire together and upload the same photo twice. Flushes now run in a single chain.
• Photos before records. Photos taken offline upload first; if that fails partway, the progress is carried forward instead of repeated.

2. The spreadsheet that rewrote itself

The delivery log is the system of record: an audited, form-by-form sheet of every handover. I was importing it into a database so donors could see where supplies went.

Every quantity cell follows one convention: quantity-special request, either half optional. Excel doesn’t know that. It reads -20 as minus twenty, and 10-5 as the fifth of October.

cell as typed     what Excel stored        what it means
"30-10"           "30-10"   (text)         30 delivered, 10 special request
"-20"             -20       (number)       20 delivered, quantity half left blank
"10-5"            2026-10-05 (date)        10 delivered, 5 special request

One cell had become a date. Its neighbours on the same row read 50-25, 25-15 and 15-10, which confirms the reading: ten delivered, five requested. The parser now handles all three forms, plus cells where someone wrote the unit out (“2 cartons”, “1Box”). Whenever a cell is not a plain number, the record’s notes say how it was read, and its detail page shows that note.

Two more things were worth refusing to do:

• Inventing value. One item, a box of slates, looked like it could be priced at the per-pen rate. The cells that spell the unit out say carton or box, never pen, and the ratio is one per twelve slates. Pricing it per pen would have put roughly ₨670,000 of value that never existed into donor reporting. It stays unpriced.
• Keeping a flattering number. A headline “students reached” figure in the six digits came from an older aggregate sheet with no source in the forms. I withdrew it, including from my own portfolio. The forms record about 7,500 students across those 1,245 deliveries, so that is the figure the dashboard reports.

3. Where did it go?

182 rows record no district. Most of them say where they went somewhere else on the row: an address, a known locality, a school that appears elsewhere under one district, or a same-day batch of deliveries that all went to one place. The importer fills in a district only when every clue on the row agrees. If the clues disagree, it sets nothing and lists the candidates in the notes.

The last-resort clue is the regional prefix of a national ID number. It applies only when at least three rows share the prefix and 90% of them are in one district. A prefix says where someone is registered, not where they took delivery, so it never decides on its own.

Every inferred district is flagged District estimated, and estimated rows are counted separately in the district table. The flag clears only when a person changes the district or ticks Confirmed. An unrelated save doesn’t clear it.

What I took from it

Both failures looked like success. The phone said saved; the spreadsheet showed a number. On bad networks with messy inputs, the job isn’t to make errors disappear. It’s to make sure nothing is dropped, overwritten or estimated without a visible mark saying so. If I can’t verify a number, it carries a label, or I leave it out.