Skip to content

SETUP.md

Latest commit

 

History

History
134 lines (86 loc) · 5.84 KB

SETUP.md

File metadata and controls

134 lines (86 loc) · 5.84 KB
Raw

Setup

About 40 minutes end to end, most of it in the Google Sheet.

What you need

  • An n8n instance (self-hosted or cloud)
  • A Twilio account with an SMS-capable number, or the Twilio sandbox for testing
  • An OpenAI API key
  • A Google account for Sheets and Gmail
  • A PDFBolt API key, for the weekly digest only

1. Build the registry

cd data
node build_registry.js

Reads the cleaned tables from the companion analysis repo and writes pumpwatch_registry.csv. If that repo sits somewhere other than C:/Users/All/Desktop/sdg6-zambia-water-points, change the SRC constant at the top of the script.

Expected output: 4,026 points across 61 districts.

2. Create the Google Sheet

The workbook is prebuilt. Run python data/build_workbook.py (or use the committed Pump Watch - Google Sheet.xlsx), upload it to Google Drive, and open it with Google Sheets. It arrives with all six tabs, the exact headers, the 4,026 registry rows, and a Legend tab explaining which tabs a person writes to.

Building it by hand instead means typing 90 column headers without a spelling mistake, because the workflows match on column names. Column headers for each tab are in SHEET_SCHEMA.md if you want to check them.

Add at least one row to Officers for the district you are testing, with a phone number you control.

Copy the spreadsheet ID out of the URL. It is the long string between /d/ and /edit.

3. Import the workflows

Import all four JSON files from workflows/. Import pumpwatch-0-error-handler first.

4. Fill in the Config nodes

Every value that changes lives in one Config node per workflow. Nothing is hardcoded elsewhere.

Workflow Field Value
0 sheet_id Your spreadsheet ID
0 operator_email Where failures should be emailed
1 sheet_id Your spreadsheet ID
1 sms_from Your Twilio number, +260... format
1 llm_model gpt-4o-mini
1 alert_threshold Score at which the officer is texted immediately. Default 60
2 sheet_id, sms_from As above
2 province_escalation_phone Provincial office number
3 sheet_id As above
3 digest_recipients Comma separated emails

The five scoring weights and the two Critical override thresholds also sit in workflow 1's Config. They should be agreed with the district before go-live rather than left at my defaults.

5. Credentials

Credential type Used by Notes
Header Auth Parse report with GPT Name Authorization, value Bearer sk-...your OpenAI key
Header Auth Render PDF Name APIKey, value your PDFBolt key. This must be a second header credential, not the OpenAI one
Twilio all SMS nodes Account SID and auth token
Google Sheets OAuth2 all Sheets nodes
Gmail OAuth2 Email the operator, Email the digest

6. Attach the error workflow

Open workflows 1, 2 and 3. In each: Settings > Error Workflow > Pump Watch 0 - Error Handler. Save.

A workflow that can fail silently will.

7. Point Twilio at the intake webhook

Activate workflow 1 and copy its production webhook URL. It ends in /webhook/pumpwatch-sms.

In the Twilio console, open your number, and under Messaging > A message comes in set the webhook to that URL with method HTTP POST.

If n8n is running locally, Twilio cannot reach it directly. Use a tunnel:

cloudflared tunnel --url http://localhost:5678 --protocol http2

The tunnel URL changes every time it restarts, so the Twilio webhook has to be updated each session. Without --protocol http2 the tunnel tends to fail on restrictive networks.

8. Activate and test

Activate workflows 1, 2 and 3.

Pick a real point code out of the Registry tab, then text your Twilio number:

CHI014 no water since monday

Expected within a few seconds:

  1. A row appears in Reports with a triage score, a priority band, and a plain-language reason.
  2. You receive a confirmation SMS quoting the report ID and the priority.
  3. If the score cleared alert_threshold, the officer number receives an alert.

Then try these:

Text Expected
the pump in mumbwa is broken A reply saying how many points are on the register in Mumbwa and asking for the plate code
ZZZ999 no water A reply saying that code is not on the register
CHI014 still no water from a different number The existing report's corroboration count and score go up. No second row
CHI014 no water from the same number again Nothing changes

To test the verification loop without waiting a week, set a report's status to Repaired and put a date eight days ago in repaired_at, then run workflow 2 manually. You should get the "is water flowing now?" text. Reply yes, and a row should appear in FunctionalityRecords.

9. Run the digest once by hand

Execute workflow 3 manually. Check the PDF arrives and the numbers on it match what is in the sheet.

Costs at pilot scale

Per inbound report: one OpenAI call at roughly 700 tokens on gpt-4o-mini, plus one outbound SMS. The verification sweep adds one more SMS per repair, and escalations add one per overdue report.

SMS dominates. A district pilot is affordable on a commercial gateway. National scale is not, and the honest answer there is a mobile network operator agreement or a USSD short code rather than a bigger Twilio bill.

Known gaps

  • The Nyanja, Bemba and Tonga handling is prompt-level only and untested against real speakers.
  • repaired_at depends on someone at the district office updating a spreadsheet. If they do not, the verification loop never fires and reports escalate instead, which is the intended failure mode but not a substitute.
  • Workflow 2 reads the whole Reports tab once per day. Fine for a district. At national scale, filter the read by date and batch with Loop Over Items before the SMS nodes.