Pump Watch

An SMS-first fault reporting and repair triage system for rural water points in Zambia.

A villager texts that a borehole has stopped working. The system identifies the point, scores how urgent the fault is against the population it serves, places it in a ranked queue the district water office can act from, and texts the reporter back. Seven days after a repair is logged it asks the reporter whether water is actually flowing, and records their answer.

That last step is the point of the project.

Status: working prototype, not field deployed. The water point registry is real. The workflows run end to end. No community has used it yet. See Honest status.

---

The problem this addresses

Roughly a third of rural handpumps in sub-Saharan Africa are non-functional at any given time. That figure is not really a maintenance statistic. It is an information statistic. Nobody with a repair budget reliably knows which pumps are broken, how long they have been broken, or how many people are walking further because of it.

The companion analysis repo, sdg6-zambia-water-points, examined 6,643 Zambian water points from the Water Point Data Exchange and found the mechanism behind that gap. Of the fifteen programmes contributing data, only one ever records a point as "Functional". The other fourteen upload at handover and never return. The national dataset's status_clean field is not a status. It is an absence written as a category.

You cannot model your way out of that. There is no feedback loop to model. So this repo builds one.

The analysis found	Pump Watch supplies
No dated functionality records after handover	A verification SMS seven days after every logged repair, written to FunctionalityRecords
No village or ward names anywhere in the Zambian extract	A village_label learned from what reporters actually call the place
Failures known only when someone happens to survey	A reporting channel that works on a $15 feature phone
Pump age predicts failure better than a tuned model	Age used as a small, declared prior in a transparent score, and the model left out

---

How it works

  villager's SMS
        |
        v
  [1] Intake and triage  ------> Reports sheet ------> SMS to reporter
        |                                              SMS to district officer
        |                                                   (when score is high)
        v
  [2] Daily sweeps  -----------> verification requests at day 7
        |                        escalation to province past the response window
        v
  reporter answers YES / NO
        |
        v
  FunctionalityRecords  -------> [3] Weekly digest PDF to the district office

Four n8n workflows, in workflows/:

Workflow	Trigger	Does
pumpwatch-0-error-handler	Error Trigger	Catches any failure, logs it, emails the operator
pumpwatch-1-intake-and-triage	Twilio SMS webhook	Parses, resolves, deduplicates, scores, replies, alerts
pumpwatch-2-daily-sweeps	Daily 08:00	Verification requests and overdue escalations
pumpwatch-3-weekly-district-digest	Monday 07:00	One printable page for the district water office

Google Sheets is the database. That is a deliberate choice, not a shortcut: a district water officer can open, sort, and correct a spreadsheet without training or a login they will forget.

Design decisions worth defending

Plain language in, not a rigid SMS format. Reporting schemes that demand REPORT#CODE#FAULT#DAYS fail because people text how people text. An LLM parses free text, including messages that mix English with Nyanja, Bemba or Tonga words, and returns structured fields in JSON mode. The point code is validated against the registry format before it is trusted.

Corroboration, not duplication. Twenty people reporting the same dry borehole is twenty pieces of evidence about one fault. Each new reporter raises that report's priority. They do not create a twentieth ticket. The same person texting twice changes nothing, which also makes the whole workflow safe to re-run.

The reply to the reporter is not politeness. It is the mechanism. Community reporting schemes die of silence: people report once, hear nothing, and conclude nobody is listening. Every inbound message gets an answer, including the ones the system could not resolve, which get told exactly what was missing.

A transparent score, not a model. Priority is a weighted sum of population served, days without water, distance to the nearest alternative point, number of independent reporters, and pump age. Every weight sits in one Config node so a district can retune it, and every report carries a plain-language reason string the officer can read. The companion analysis is the reason: on held-out districts a tuned Random Forest scored ROC-AUC 0.613, while sorting by pump age alone scored 0.642. Shipping the model would have been worse and harder to explain.

A hard override for isolation. A point serving 500 or more people with no alternative within 2 km is flagged Critical regardless of what the weighted sum says. Weighted sums are good at ranking and bad at recognising an emergency.

---

The registry

data/pumpwatch_registry.csv, built by data/build_registry.js from the cleaned analysis table.

Water points	4,026
Districts	61
Provinces	10
Population served (sum of local_population)	4,166,539
Median distance to nearest alternative point	0.64 km
Pump age risk bands	Low 2,221, Medium 1,091, High 430, Unknown 284

Every row is a real water point recorded in WPdx. Three fields are added by the builder:

• point_code, a six-character code such as CHI014, short enough to paint on a pump plate and text. Each of the 61 districts gets a distinct three-letter prefix, resolved for collisions (Chibombo and Chipata both want CHI, so one of them does not get it).
• nearest_alt_km and nearest_alt_code, computed by haversine over a 0.25 degree spatial grid.
• age_risk_band, from age_years.

Source: Water Point Data Exchange via UN OCHA HDX, https://data.humdata.org/dataset/wpdx_zmb, CC BY-SA.

---

Verification

node workflows/validate.js     # structural check on the four exports
node workflows/test-logic.js   # runs the decision logic against fixed inputs

validate.js checks that every connection and every $('Node name') reference inside an expression resolves to a node that exists, that no default node names survived, that no node is orphaned, and that every external call has retries configured. n8n imports a broken workflow happily and only fails at run time, usually on a mistyped node name, so this catches it on the desk.

test-logic.js lifts the Code nodes verbatim out of the workflow JSON and runs them inside a small stand-in for the n8n runtime, so the tested logic cannot drift from the shipped logic. 33 assertions covering score monotonicity in each input, the Critical override firing and not misfiring, missing fields, the 0 to 100 bound, corroboration counting, repeat-sender suppression, the seven-day and escalation windows, digest arithmetic, and a quiet week rendering without inventing numbers.

It also writes docs/sample-digest.html, the weekly digest rendered from the test fixtures.

---

Setup

See docs/SETUP.md for the full walkthrough, and docs/SHEET_SCHEMA.md for the six sheet tabs.

In short: create the Google Sheet, paste in the registry CSV, import the four workflows, fill in the Config node in each, connect Twilio, OpenAI, Google Sheets and Gmail credentials, set workflow 0 as the error workflow on the other three, point your Twilio number's inbound webhook at workflow 1, and text it.

Running costs at pilot scale are dominated by SMS. The parse call is roughly 700 tokens per inbound message on gpt-4o-mini.

---

Honest status

What is true:

• The registry is real WPdx data, 4,026 Zambian water points.
• The four workflows are complete and import into n8n.
• The decision logic is tested, and the tests run against the shipped code.
• The digest renders.

What is not true yet:

• No community has used this. There are no real reports in it and no real repairs.
• It has not been shown to a Zambian district water office, and no district has agreed to the response windows in the Config node.
• The Nyanja, Bemba and Tonga handling is prompt-level only. It has not been tested against real messages from real speakers, and it will need to be before anyone relies on it.
• SMS costs money per message. At national scale the verification sweep alone becomes a budget line that needs a real answer, most likely a mobile network operator agreement rather than a commercial SMS gateway.

Any figure quoted anywhere in this repo is either counted from the registry or produced by the test fixtures, and it says which.

---

Licence

Code: MIT. Water point data: CC BY-SA, per the WPdx licence. See DATA_LICENCE.md.